Primefaces Chart Legend

Hi,

This is for those who generate Charts dynamicaly via java code.

Similar to the following;.....

   PieChart pie = new PieChart();

    pie.setId("pi_1");
    pie.setWidgetVar(pie.getId());
    pie.setDiameter(100);
    pie.setTitle("Test");
    pie.setDataFormat("value");            pie.setLegendToggle(true);
    pie.setLegendPosition("w"); 

it happened to my that whenever i set the legendPosition the chart never gets rendered.......

if u find urself in a similar position do what i did... (by the way this is not a hack !!!)

add the following code in the head section of ur XHTML file;-

<script language="javascript" type="text/javascript" src="./resources/js/jquery.jqplot.min.js">
<link  rel="stylesheet" type="text/css" href="./resources/css/jquery.jqplot.min.css"/>

....and let the magic continue....


Tested in primefaces 3.3

Avoid 'SQL LIKE' - Use Full Text Search

Hello random googler,

Welcome back

Do u often find your self needing to do 'dirty' SQL LIKEs like the following;

SELECT member_id 

FROM vw_members

WHERE upper(member_name) LIKE 'JAY%' OR

upper(member_county) LIKE 'NAI%' OR

upper(staff_no) LIKE 'S01%' OR 

upper(mobile_no) LIKE '072%' OR 'i am tired of this stuff and this is only one permutation assuming the first 3 characters hav been filled...ouuuch'

? (yes question mark... this is where the question ends!!!)


If you answer is yes OR 'Y%'  ;-) ;-) then you are ready for FULL TEXT SEARCH

ONE TECHNIQUE...
Make your SQL VIEWS read for FTS by always having a column called 'ts_doc' that should contain the 'searchable' columns(of ur choice) ??????????


EG (postgres)

CREATE OR REPLACE VIEW vw_members AS

SELECT members.member_id, members.member_name, member.staff_no, members.mobile_no, andanyothercolumnyouwhishcoziassumeuknowsql,

 to_tsvector(COALESCE(members.member_name,'')||' '||COALESCE(member.staff_no,'') ||' '|| COALESCE(members.mobile_no,'')) as ts_doc

So that in your queries just become

SELECT blahblah

FROM vw_members

WHERE ts_doc @@ to_tsquery(' searchQuery:*') ";

NB: searchQuery is nothing but a cleanup to create a valid FTS query:-
searchQuery = searchQuery.replace(" ",":* & ") - in java, but of course u can use ur prefered technique... php, sql,

CONCLUSION
With the above u can use more than one search text(in whatever order or case) to narrow down the result..
for example with searchQuery like 'mik DS1 nai' it will search in all the columns (defined in ts_doc) for words starting with mik or DS1 or nai... thereby matching all entries for nairobi, all names starting with mik eg mike,mikel etc etc

NB: approach is 'begins with....'
(those who do LIKE '%xyz%' look for another evangelist !!!!)

example is in postgres

adios

Oracle and Postgres Cummulative Sum aka Running Totals

Hello Random Googler !!!

You landed here today... u r welcome.

Now consider the following output.

The 'interesting' column is the last one

Name	Date	Amount	Cumulative Sum
Mike	01-JUN-13	1000	1000
John	01-JUN-13	1000	2000
Omar	01-JUL-13	1000	3000
Shamim	01-JUL-13	1000	4000
Abdul	01-JUL-13	1000	5000
Jim	01-AUG-13	1000	6000
ZeGuru	05-SEP-13	1000	7000
Duli	06-OCT-13	1000	8000

Another example
(grouped by date... sorry partitioned by date)

Name	Date	Amount	Cumulative Sum
Mike	01-JUN-13	1000	1000
John	01-JUN-13	1000	2000
Omar	01-JUL-13	1000	1000
Shamim	01-JUL-13	1000	2000
Abdul	01-JUL-13	1000	3000
Jim	01-AUG-13	1000	1000
ZeGuru	05-SEP-13	1000	1000
Duli	06-OCT-13	1000	1000

The first one was achieved by;

SELECT name, date, amount, sum(amount) over(order by payment_id) cummulative_sum
FROM vw_payment

THE second was achieved by;

SELECT name, date, amount, sum(amount) over(partition by date, order by payment_id) cummulative_sum
FROM vw_payment

(the partition by clause was used to restart the running totals for each date)


do i need to say that payment_id is just the PK (incremental/serial integer) of the original table ?

By the way this works for both PostgreSQL and Oracle

Again u r welcome

The LEGENDARY grep command

the man page starts as follows...
 
SYNOPSIS
       grep [OPTIONS] PATTERN [FILE...]
       grep [OPTIONS] [-e PATTERN | -f FILE] [FILE...]

DESCRIPTION
       grep  searches  the named input FILEs (or standard input if no files are named, or if a single hyphen-minus (-) is given as file name) for lines containing a match to the given PATTERN.  By default, grep prints the matching lines.

       In addition, two variant programs egrep and fgrep are available.  egrep is the same as  grep -E.   fgrep  is  the  same  as grep -F.   Direct  invocation as either egrep or fgrep is deprecated, but is provided to allow historical applications that rely on them to run unmodified.
..
..
..
..till the end of the page

The following are some practical uses of grep from my own experience  and some sourced from the net.

1. Search file contents

Search for the word PAGE (case sensitive) in the file application.xml;
$grep "PAGE" /opt/tomcat7/webapps.labs/primetest/WEB-INF/configs/application.xml

For case insensitive match use the -i switch as follows;

$grep -i "PAGE" /opt/tomcat7/webapps.labs/primetest/WEB-INF/configs/application.xml

2. Search inside a directory

Search for the word drvierClassName inside the directory META-INF

$ grep -R "driverClassName" META-INF/

You will see result on a separate line preceded by the name of the file in which it was found.

3. Find whole words only

When you search for class, grep will match class, className etc.

$ grep -w "class" WEB-INF/context.xml

4. Multiple word search

Use the egrep command as follows:
$ egrep -w 'word1|word2' /path/to/file

5. Count occurences

The grep can report the number of times that the pattern has been matched for each file using -c (count) switch:

$ grep -R -c "driverClassName" META-INF/

6. Show line numbers

Pass the -n switch to precede each line of output with the number of the line in the text file from which it was obtained:

$ grep -R -n "driverClassName" META-INF/

7. Names of matching files only

Use the -l (list) switch as follows;

$ grep -R -l "driverClassName" META-INF/


8. Grep invert match

You can use -v option to print inverts the match; that is, it matches only those lines that do not contain the given word. For example print all line that do not contain the word bar:

$ grep -v bar /path/to/file

NB: for coloured output (if not on by default) you can add --color=auto to your grep command

Other options:

• -A x or -B x (where x is a number) --- display “x” lines After or Before the section where the particular word is found.

Programaticaly Creating Primefaces DataExporter

The following is a utility method that creates a single HtmlCommandLink(h:commandLink NOT p:commandLink) that exports to PDF.

NB: please not that DataExporter is an ActionListener and not a UIComponent !

Modify the id and target variables accordingly

public static HtmlCommandLink createExportCommand(XMLElement wd){

    FacesContext fc = FacesContext.getCurrentInstance();
    Application application = fc.getApplication();
    ExpressionFactory ef = application.getExpressionFactory();
    ELContext elc = fc.getELContext();

    String table_id = "dt_mn_" + wd.getAttribute("key","x") + "_"  +    wd.getAttribute("keyfield","kf"); //id of parent datatable


          HtmlCommandLink command = new HtmlCommandLink();
          command.setId("cmd_pdf_" + table_id);
          command.setValue("PDF");
          command.setTitle("Click to Export");
          //command.addActionListener(new DataExporter(table.getId(),"pdf","Export","false","false","","utf-8",null,null));

          ValueExpression target = ef.createValueExpression(elc, ("mainForm:"+table_id), String.class);
          ValueExpression type = ef.createValueExpression(elc, "pdf", String.class);
          ValueExpression fileName = ef.createValueExpression(elc, "Export", String.class);
          ValueExpression pageOnly = ef.createValueExpression(elc, "true", String.class);
          ValueExpression selectionOnly = ef.createValueExpression(elc, "false", String.class);
          ValueExpression exludeColumns = ef.createValueExpression(elc, "", String.class);
          ValueExpression encoding = ef.createValueExpression(elc, "CP1252", String.class);
          MethodExpression preProcessor = FacesAccessor.createMethodExpression("#{eventManager.preProcessor}",Void.class, new Class[2]);
          MethodExpression postProcessor = FacesAccessor.createMethodExpression("#{eventManager.postProcessor}",Void.class, new Class[2]);

          DataExporter exporter = new DataExporter(target,type,fileName,pageOnly,selectionOnly,exludeColumns,encoding,null,null);

          command.addActionListener(exporter);
       // }

    return command;
      }

===============

Stack

===============

Primefaces 3.3

Tomcat 7

JSF 2.0

Oracle isNumeric function

AFAIK there is no in-built function to do such but there are lots of ways to achieve the same using the functions already available.

Here is a list of some;

Option 1: Using to_number()
Attempt to convert the input/variable to a anumber.. if it succeeds then isNumeric is true otherwise false

CREATE OR REPLACE FUNCTION isNumeric (var in varchar) return char is

   dummy number;

begin

     select to_number(trim(var)) into dummy from dual; 

   if (dummy is null) then

      return '0';

   else

      return '1';

  end if;  

exception

   when others then

       return '0';

end;

/

Option 2: Using regex

What is exponential growth ?

According to legend, the game of chess was invented by the Brahmin Sissa to amuse and teach his king.

Asked by the grateful monarch what he wanted in return, the wise
man requested that the king to place one grain of rice in the first square of the chessboard, two in the second, four in the third, and so on, doubling the amount of rice up to the 64th square.

The king agreed on the spot, and as a result he was the first person to learn the valuable (albeit humbling) lesson of exponential growth.

Sissa’s request amounted to 2⁶⁴ −1 = 18,446,744,073,709,551,615 grains of rice, enough rice to pave all of India several times over !

Qualities of Truly Confident People

This is it.... (*bare knuckles*)

Confidence is NOT bravado, or swagger, or an overt pretence of bravery. Confidence is NOT some bold or brash air of self-belief directed at others.
Confidence is quiet: It’s a natural expression of ability, expertise, and self-regard.



The above summary captures it all.. 
If not enough you can continue reading.....

1. They take a stand not because they think they are always right… but because they are not afraid to be wrong.
Cocky and conceited people tend to take a position and then proclaim, bluster, and totally disregard differing opinions or points of view. They know they’re right – and they want (actually they need) you to know it too.
Their behavior isn’t a sign of confidence, though; it’s the hallmark of an intellectual bully.
Truly confident people don’t mind being proven wrong. They feel finding out what is right is a lot more important than being right. And when they’re wrong, they’re secure enough to back down graciously.
Truly confident people often admit they’re wrong or don’t have all the answers; intellectual bullies never do.

2. They listen ten times more than they speak.
Bragging is a mask for insecurity. Truly confident people are quiet and unassuming. They already know what they think; they want to know what you think.
So they ask open-ended questions that give other people the freedom to be thoughtful and introspective: They ask what you do, how you do it, what you like about it, what you learned from it… and what they should do if they find themselves in a similar situation.
Truly confident people realize they know a lot, but they wish they knew more… and they know the only way to learn more is to listen more.

3. They duck the spotlight so it shines on others.
Perhaps it’s true they did the bulk of the work. Perhaps they really did overcome the major obstacles. Perhaps it’s true they turned a collection of disparate individuals into an incredibly high performance team.
Truly confident people don’t care – at least they don’t show it. (Inside they’re proud, as well they should be.) Truly confident people don’t need the glory; they know what they’ve achieved.
They don’t need the validation of others, because true validation comes from within.
So they stand back and celebrate their accomplishments through others. They stand back and let others shine – a confidence boost that helps those people become truly confident, too.

4. They freely ask for help.
Many people feel asking for help is a sign of weakness; implicit in the request is a lack of knowledge, skill, or experience.
Confident people are secure enough to admit a weakness. So they often ask others for help, not only because they are secure enough to admit they need help but also because they know that when they seek help they pay the person they ask a huge compliment.
Saying, “Can you help me?” shows tremendous respect for that individual’s expertise and judgment. Otherwise you wouldn't ask.

5. They think, “Why not me?”
Many people feel they have to wait: To be promoted, to be hired, to be selected, to be chosen... like the old Hollywood cliché, to somehow be discovered.
Truly confident people know that access is almost universal. They can connect with almost anyone through social media. (Everyone you know knows someone you should know.) They know they can attract their own funding, create their own products, build their own relationships and networks, choose their own path – they can choose to follow whatever course they wish.
And very quietly, without calling attention to themselves, they go out and do it.

6. They don't put down other people.
Generally speaking, the people who like to gossip, who like to speak badly of others, do so because they hope by comparison to make themselves look better.
The only comparison a truly confident person makes is to the person she was yesterday – and to the person she hopes to someday become.

7. They aren’t afraid to look silly…
Running around in your underwear is certainly taking it to extremes… but when you’re truly confident, you don’t mind occasionally being in a situation where you aren't at your best.
(And oddly enough, people tend to respect you more when you do – not less).

8. … And they own their mistakes.
Insecurity tends to breed artificiality; confidence breeds sincerity and honesty.
That’s why truly confident people admit their mistakes. They dine out on their screw-ups. They don’t mind serving as a cautionary tale. They don’t mind being a source of laughter – for others and for themselves.
When you’re truly confident, you don’t mind occasionally “looking bad.” You realize that that when you’re genuine and unpretentious, people don’t laugh at you.
They laugh with you.

9. They only seek approval from the people who really matter.
You say you have 10k Twitter followers? Swell. 20k Facebook friends? Cool. A professional and social network of hundreds or even thousands? That’s great.
But that also pales in comparison to earning the trust and respect of the few people in your life that truly matter.
When we earn their trust and respect, no matter where we go or what we try, we do it with true confidence – because we know the people who truly matter the most are truly behind us.


Read this from Dharmesh Shah's update

Find foreign table using a given CONSTRAINT NAME

Ever tried to delete a record only to be told get ORA-02292
"integrity constraint (CONSTRAINT_NAME) violated - child record found"

How do you identify the table that is related to the constraint mentioned above. Of course if you explicitly name ur FK constraints(very unlikely) you have no problem.

ORACLE




SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_name = 'SYS_C0011582'

Periodicaly restart dead process in Linux

The following is (one way of ) how to automaticaly check for and restart a dead process running in a linux host

Part A

/bin/netstat -ln | /bin/grep ":80 " | /usr/bin/wc -l | /bin/awk '{if ($1 == 0) system("/sbin/service httpd restart") }'

This checks if there is any service running on port 80, if not it runs the command to start the service (apache httpd in this example)

Part B
Create a cron job to periodicaly do the above

#monitors the service every 10 minutes
*/10 * * * * /usr/sesame/bash/service_monitor.sh> /dev/null 2>&1

NB: please note the trailing space after the port number


Tested in Redhat|Fedora|CentOS

DO NOT make products for Everyone

If your response to the title above is  

"Well, I’m really making it for everyone."....

Well, just stop because you’ve already lost. 
“Everyone” isn’t an audience. “Everyone” is a by product of an incredibly successful thing that was made for a far more specific bunch of people.

Don’t ever make something for “Everyone” make it for someone. And make that person love it.

Facebook was not originaly designed for “Everyone,” it started by targeting Harvard students, then Ivy League students, then more and more and more.
Go ask Google Plus how well building for “Everyone” from the start went.

When you begin with “Everyone” you’re just stuck: How do you make any honest decisions? How do you solve any real problems? You don’t.

You start to invent people and you start to invent their problems and it’s amazing because those people and those problems line up almost exactly with what you’re building and how you’re thinking about it—imagine that. Lying to yourself is amazing for productivity.

Real audience is hard. Solving real problems is coding madness. But it’s the only way you make something that lasts, because you made something that someone actually cared about.


Inspired by: http://tinyurl.com/m4h9n2j

A Plus: School Management System

Behold

The smartest, boldest and state-of-the-art Management System for Academic Institutions is here at last.

Accessible from Desktop, Browser and Mobile this is surely the best to invest in...

A system that integrates SMS & Email messaging plus Intelligent Reporting is what you need and nothing less...

A + School / College Information System from Mabura Ze Guru on Vimeo.

Screenshots for the impatient

Admission process begins here

Sample Output (Printable Report)

Capturing Exam Results is a breeze










And its nice-looking tooooo.... (looks exactly the same way in desktop and browser)

Core Modules:

a. Academics - includes Exams, Timetabling, Performance Statistics
b. Library
c. Boarding
d. Procurement
e. Finance and Accounts
f.  Banking - deposits, withdrawals, transfers and reconciliation
g. Messaging(SMS & Email) - optional plugin
h. Reporting and Analysis - Adhoc, printable and Exportable reports and charts

Part One

Contact the creators for more; Sesame Technologies

This Is Society

My two cents.... on social life

(Attn Nerds and Geeks)

Imagine three people naked and separate in the woods.  They have nothing with them, no one with them, no starting point.

One recognizes his coldness and gathers wood, and struggles for a spark, until he has a fire.

Another recognizes her hunger, sharpens a rock, and kills an animal for food.

A third recognizes his thirst, and searches far and wide for water, until he finds a spring.

The first is warm, but dies of thirst.  The second is full, but dies of exposure.  The third is slaked, but dies of starvation.

But if instead the three meet, and recognize what each the other can bring, what each the other needs, and what they themselves have and need, they can share one to the other and live.  The water carrier is fed, the hunter is warm, the fire-builder does not thirst: this is society. 

Artical inspired by/copied from http://alittlewideeyed.blogspot.com

JSF Action vs ActionListener

96 down vote accepted

Following is the difference btwn Action and ActionListener in JSF actionListener Use actionListener if you want have a hook before the real business action get executed, e.g. to log it, and/or to set an additional property (by ), and/or to have access to the component which invoked the action (which is available by ActionEvent argument). The actionListener method has by default the following signature: import javax.faces.event.ActionEvent; // ... public void actionListener(ActionEvent event) { // ... } Note that you can't pass additional arguments by EL 2.2. You can however override the ActionEvent argument altogether by passing and specifying custom argument(s). The following examples are valid: actionListener="#{bean.methodWithoutArguments()}" actionListener="#{bean.methodWithOneArgument(arg1)}" actionListener="#{bean.methodWithTwoArguments(arg1, arg2)}" (note the importance of the parentheses in the argumentless method, if they were absent, JSF would still expect a method with ActionEvent argument) action Use action if you want to execute a business action and if necessary handle navigation. The action method can return a String which will be used as navigation case outcome (the target view). A return value of null or void will let it return to the same page. A return value of empty string or the same view ID will also return to the same page, but destroy any view scoped beans. The action method can be any valid MethodExpression, also the ones which uses EL 2.2 arguments, e.g: value="submit" action="#{bean.edit(item)}" /> with public void edit(Item item) { // ... } Note that when your action method solely returns a string, then you can also just specify exactly that string in the action attribute. E.g. this is totally clumsy: value="Go to next page" action="#{bean.goToNextpage}" /> with public String goToNextpage() { return "nextpage"; } Instead just do value="Go to next page" action="nexpage" /> Please note that this in turn indicates a bad design: navigating by POST. This is not user nor SEO friendly. This all is explained in When should I use h:outputLink instead of h:commandLink? and is supposed to be solved as value="Go to next page" outcome="nextpage" /> Invocation order The actionListeners are always invoked before the action in the same order as they are been declared in the view and attached to the component. So, the following example value="submit" actionListener="#{bean.listener1}" action="#{bean.submit}"> type="com.example.SomeActionListener" /> target="#{bean.property}" value="some" /> will invoke Bean#listener1(), SomeActionListener#processAction(), Bean#setProperty() and Bean#submit() in this order. Exception handling The actionListener supports a special exception: AbortProcessingException. If this exception is thrown from an actionListener method, then JSF will skip any remaining action listeners and the action method and proceed to render response directly. You won't see an error/exception page, JSF will however log it. If you intend to block the page by an error page as result of a business exception, you should be performing the job in the action method.  DISCLAIMER: I dont take any credit for this explanation. (because it was 'shamelessly' copied from BalusC's answer at Stack Overflow)

ER diagram from an existing ORACLE DB

In SQL Developer 3, this is very straight forward;

View > Data Modeler > Browser.

The browser will show up as one of the tabs along the left-hand side.
Click on the Browser tab, expand the design, right-click Relational Models and select 'New Relational Model'.
Then just drag the tables you want onto the model.

To save/export the diagram to a PDF do;

File > Data Modeler > Print Diagram > PDF

Follow instructions to finish

happy modelling

Panorama - Dubai Palm Island

Credits: airpano.ru

TRUE/REAL data pagination

I was recently implementing Lazy Loading in Primefaces

One of the insights i would like to share is that real efficient pagination is achieved by fetching data from the database (backend) in chunks as opposed to loading everything in memory.

This saves both bandwidth and memory (client side).

The trick here is to use OFFSET and LIMIT sql clauses in conjuction with ORDER BY.

Example 1: Returning the first 100 rows from a table called employee:

SELECT employee_id, employee_name, remarks FROM employee ORDER BY employee_id ASC LIMIT 100;

Example 2: Returning 10 rows from the table employee starting at row 15

SELECT employee_id, employee_name, remarks FROM employee ORDER BY employee_id ASC OFFSET 15 LIMIT 10;

LMFAO - Looking Manualy for Abnormal Output

Dont use System.out() for debugging a serious java app. Use logs(runtime) and JUnit for testing.

The best explanation is (from a StackOverflow thread)

Question(part):
"Whether I write simple applications or larger ones I test them with the System.out statements and it seams quite easy to me. " and on he goes asking whay he should use JUnit.

Answer(part):

That's not testing, that's "looking manually at output" (known in the biz as LMAO). More formally it's known as "looking manually for abnormal output" (LMFAO).
Any time you change code, you must run the app and LMFAO for all code affected by those changes. Even in small projects, this is problematic and error-prone.
Now scale up to 50k, 250k, 1m LOC or more, and LMFAO any time you make a code change. Not only is it unpleasant, it's impossible: you've scaled up the combinations of inputs, outputs, flags, conditions, and it's difficult to exercise all possible branches.
Worse, LMFAO might mean visiting pages upon pages of web app, running reports, poring over millions of log lines across dozens of files and machines, reading generated and delivered emails, checking text messages, checking the path of a robot, filling a bottle of soda, aggregating data from a hundred web services, checking the audit trail of a financial transaction... you get the idea. "Output" doesn't mean a few lines of text, "output" means aggregate system behavior.

Lastly, unit and behavior tests define system behavior. Tests can be run by a continuous integration server and checked for correctness. Sure, so can System.outs, but the CI server isn't going to know if one of them is wrong–and if it does, they're unit tests, and you might as well use a framework.
No matter how good we think we are, humans aren't good unit test frameworks or CI servers.


Source StackOverflow

Conditional Triggers in Postgres

Since version 9.0 we can execute triggers conditionaly

Example

CREATE TRIGGER trGenerateSchedule
AFTER INSERT OR UPDATE OF is_confirmed ON job_location
    FOR EACH ROW
    WHEN (NEW.is_confirmed = true)
    EXECUTE PROCEDURE generateSchedule();

This is a column trigger that listens for a change (to true) of a single column in the job_location table

Google = a very very big number

The following is a google


5,316,911,983,139,663,491,615,228,241,121,378,304)

or in words;

five undicillion,
three hundred sixteen decillion,
nine hundred eleven nonillion,
nine hundred eighty-three octillion,
one hundred thirty-nine septillion,
six hundred sixty-three sextillion,
four hundred ninety-one quintillion,
six hundred fifteen quadrillion,
two hundred twenty-eight trillion,
two hundred fourty-one billion,
one hundred twenty-one million,
three hundred seventy-eight thousand,
three hundred four

Challenge of Software Development

Touchpad stops working on GNome3 - Solution

Assalam Aleikum all,

Last week i experienced the same problem on my Fedora 15 Dell Inspiron.
After searching the net i came across a solution which worked for me.

1. As root or using sudo create file "/etc/X11/xorg.conf.d/00-enable-taps.conf"
2. Enter the following in the file:

Code:

Section "InputClass"
    Identifier "tap-by-default"
    MatchIsTouchpad "on"
    Option "TapButton1" "1"
EndSection


Worked for me after restarting X (Ctrl+Alt+Backspace). no need to reboot the machine

Credits: http://www.fedoraforum.org/forum/showthread.php?t=263884

CrossTab query in Postgres

Hello,

Ever wondered how to display the following output via an SQL query?

Student	Calculus	Simmulation	Programming	Comm Skills
Ibrahim Itambo	67	98	83	59
Abdulswamad	68	94	77	40
Shamim	70	76	56	44
Samira	36	98	68	90

There are several ways of doing it but the most common and annoying hack is to use CASE...WHEN and/or aggregate functions with GROUP BY.

It may work but they are usualy overly complex, unreadable and hard to maintain.

A better solution in Postgres is to use crosstab function available via the tablefunc module.


Example code

CREATE TABLE student(
student_id serial primary key,
student_name varchar(50),
remarks test
);
INSERT INTO student(student_name) VALUES('Ibrahim');
INSERT INTO student(student_name) VALUES('Kilui');
INSERT INTO student(student_name) VALUES('Ali Babababa');
INSERT INTO student(student_name) VALUES('Buzi');

CREATE TABLE subject(
subject_id serial primary key,
subject_name varchar(50),
remarks text
);

INSERT INTO subject(subject_name) VALUES('Calculus I');
INSERT INTO subject(subject_name) VALUES('Simulation');
INSERT INTO subject(subject_name) VALUES('Adv Programming');
INSERT INTO subject(subject_name) VALUES('Comm Skills');

CREATE TABLE exam(
exam_id serial primary key,
exam_title varchar(50),
student_id integer references student,
subject_id integer references subject,
exam_mark real default 0 not null,
remarks text
);
//FINALY INSERT RESULTS
//INSERT INTO exam(exam_title, student_id, subject_id, exam_mark) VALUES(......); you know what to do here

SELECT * FROM crosstab(
    'select student.student_name::text, subject.subject_name, exam.exam_mark
    from exam
    inner join student on exam.student_id = student.student_id
    inner join subject on exam.subject_id = subject.subject_id
    order by 1,2',
    'SELECT subject_name FROM subject ORDER BY 1')
AS
  CT("Student" text, "Calculus" real, "Simulation" real, "Prog" real, "Comm Skills" real);


Adios

Photos from DB to JasperReport

In my previous post i mentioned byte array datatype.
Its a simple(or is it simplistic) way of saving binary data into a db table. One example immediately comes to mind... yes photos!! "i want to save photos and render them in a printable report"

POSTGRES SQL

TABLE CREATE TABLE student(
student_id serial primary key,
reg_no varchar(7) not null,
photo bytea,
..
..
..
remarks text
);

The only field of interest is the photo field which is a byte array type

Most of the time i use iReport to design JasperReports and in this example I will use iReport 4.0

[[]]


Never mind.... the most important part is the ImageExpression class in the dynamic image.

Make sure its class is java.io.InputStream.

This may not be available in the iReport properties section so what u will do in such a case is click on the XML tab and type java.io.InputStream save then have fun coding

Migrating from PostgreSQL 8.3 to 9.0 and beyond - Byte Array perspective

My Nerd migos,

Its bn quite sometime since my last post.

I want to share my agonies (and help u avoid the same) when migrating applications from Postgres 8.3 (or below) to 9.0 and beyond. If you had/have tables with bytea then they will/may JUST stop working.

Some guy(s) with infinite wisdom decided to change the representation of byte array from the default (escape) to hex !!! I googled my head silly until i found a solution at stackoverflow.com (forgot the thread sorry!!!)

Ok straight to the solution. Export the 8.3 db (i prefer pg_dump --inserts dbname > /path/to/dumpfile.sql) then import (just use psql or ur favorite tool to run the script created by the pg_dump command).

The next thing is to change a configuration flag in postgresql.conf. Change bytea_output to 'escape' instead of the default 'hex'. restart the server and hug yourself.

"At least the postgres guy with infinite wisdom never forgot to add this flag for backward compatibility"

Plz dont ask me what is a bytea datatype. If u dont know it means u dont have it in ur DB so relax

Hope this helps