Note:
- This file is for Post 11 , Post 12, Post 13 Post 14 and Post 15 If you have executed any one of these post, you don't need to repeat the procedure for other posts.
- It helps you create a database named "jasperdb2" & a table named "CustomerInvoices".
- It helps you populate table with sample data.
You need to follow the procedure described below to create a database instance,
create table, and copy sample data in it.
Run SQL Shell:
Click PostgreSQL 9.3 -> SQL Shell (psql), from Programs menu of your Windows installation, to run SQL Shell for PostgreSQL server.
You will see SQL Shell consol window. SQL Shell window will prompt you for server location, database, port and username, press Enter for each prompt.
- This file is for Post 11 , Post 12, Post 13 Post 14 and Post 15 If you have executed any one of these post, you don't need to repeat the procedure for other posts.
- It helps you create a database named "jasperdb2" & a table named "CustomerInvoices".
- It helps you populate table with sample data.
You need to follow the procedure described below to create a database instance,
create table, and copy sample data in it.
Run SQL Shell:
Click PostgreSQL 9.3 -> SQL Shell (psql), from Programs menu of your Windows installation, to run SQL Shell for PostgreSQL server.
You will see SQL Shell consol window. SQL Shell window will prompt you for server location, database, port and username, press Enter for each prompt.
You will be logged to postgres database.
Create Database instance
Copy following SQL command in SQL shell to create a database instance.
-> CREATE DATABASE jasperdb2;
Your jasperdb2 database instance will be created.
Now follow the steps in next section to copy data in the jasperdb2 database.
Create table and copy sample data into the database
1. Click the pgAdmin item in PostgreSQL menu entry in Startup->Programs bar of your Windows installation.
2. A pgAdmin window will open.
3. You will see a tree in the "Object Browser" window on the left half of pgAdmin window labelled "Server1".
A node named PostgreSQL9.X will appear below the Server1.
4. Double click the PostgreSQL9.X node and you will see a password prompt. Provide the database password
you set at the time of your PostgresSQL installation.
5. Double click the "Databases" child of PostgresSQL9.X node, You will see all database instances listed.
6. Select "jasperdb2"child node under "Databases". Now click "Plugins" menu and select "PSQL Consol" option.
7. A SQL Shell consol with your jasperdb2 root will open. Copy following SQL command into the consol.
CREATE TABLE "CustomerInvoices" (
"InvoiceID" integer NOT NULL,
"CustomerName" varchar(80) NOT NULL,
"ProductName" varchar(80) NOT NULL,
"InvoicePeriod" varchar(20) NOT NULL,
"InvoiceValue" real,
CONSTRAINT CustomerInvoices_pkey PRIMARY KEY ("InvoiceID")
);
INSERT INTO "CustomerInvoices" VALUES (1001,’Packt Publishing’,’Packing Material’,’Mar09’,5020.25);
INSERT INTO "CustomerInvoices" VALUES (1002,’Packt Publishing’,’Offset Paper’,’Apr09’,3000.5);
INSERT INTO "CustomerInvoices" VALUES (1003,’Packt Publishing’,’Offset Paper’,’Mar09’,4150.05);
INSERT INTO "CustomerInvoices" VALUES (1004,’Packt Publishing’,’Packing Material’,’Mar09’,2050);
INSERT INTO "CustomerInvoices" VALUES (1005,’Packt Publishing’,’Packing Material’,’Mar09’,5085);
INSERT INTO "CustomerInvoices" VALUES (1006,’Bob’,’JapserReports Cookbook’,’Mar09’,50);
INSERT INTO "CustomerInvoices" VALUES (1007,’Alice’,’JasperReport Cookbook’,’Apr09’,49.5);
INSERT INTO "CustomerInvoices" VALUES (1008,’Packt Publishing’,’Offset Paper’,’Jan09’,8058.5);
INSERT INTO "CustomerInvoices" VALUES (1009,’Packt Publishing’,’Offset Paper’,’Feb09’,5085);
INSERT INTO "CustomerInvoices" VALUES (1010,’ABC Publishing’,’Offset Paper’,’Feb09’,4552.2);
INSERT INTO "CustomerInvoices" VALUES (1011,’ABC Publishing’,’Packing Material’,’Jun09’,8745.5);
INSERT INTO "CustomerInvoices" VALUES (1012,’ABC Publishing’,’Offset Paper’,’Jun09’,6875);
INSERT INTO "CustomerInvoices" VALUES (1013,’ABC Publishing’,’Packing Material’,’Aug09’,9852);
INSERT INTO "CustomerInvoices" VALUES (1014,’Alice’,’WordPress Cookbook’,’Jan09’,45);
INSERT INTO "CustomerInvoices" VALUES (1015,’Bob’,’WordPress Cookbook’,’May09’,45);
INSERT INTO "CustomerInvoices" VALUES (1016,’ABC Publishing’,’Printing Ink’,’Jun09’,2440);
INSERT INTO "CustomerInvoices" VALUES (1017,’Packt Publishing’,’Printing Ink’,’Aug09’,6540);
INSERT INTO "CustomerInvoices" VALUES (1018,’Bob’,’Printing Ink’,’Sep09’,150);
INSERT INTO "CustomerInvoices" VALUES (1019,’Packt Publishing’,’Printing Ink’,’Jan09’,5240);
INSERT INTO "CustomerInvoices" VALUES (1020,’ABC Publishing’,’Printing Ink’,’Mar09’,3450);
INSERT INTO "CustomerInvoices" VALUES (1021,’Packt Publishing’,’Binding Material’,’Jan09’,2580);
INSERT INTO "CustomerInvoices" VALUES (1022,’Packt Publishing’,’Binding Material’,’Feb09’);
INSERT INTO "CustomerInvoices" VALUES (1023,’Packt Publishing’,’Packing Material’,’Jun09’,5870);
INSERT INTO "CustomerInvoices" VALUES (1024,’Packt Publishing’,’Binding Material’,’Jan09’,8770);
INSERT INTO "CustomerInvoices" VALUES (1025,’Packt Publishing’,’Printing Ink’,’Aug09’,5882);
INSERT INTO "CustomerInvoices" VALUES (1026,’Packt Publishing’,’Binding Material’,’Sep09’,4550);
INSERT INTO "CustomerInvoices" VALUES (1027,’Packt Publishing’,’Printing Ink’,’Oct09’,4440);
INSERT INTO "CustomerInvoices" VALUES (1028,’Packt Publishing’,’Offset Paper’,’May09’,8660);
INSERT INTO "CustomerInvoices" VALUES (1029,’Packt Publishing’,’Offset Paper’,’May09’,8660);
INSERT INTO "CustomerInvoices" VALUES (1030,’Packt Publishing’,’Printing Ink’,’Jan09’,5240);
INSERT INTO "CustomerInvoices" VALUES (1031,’Packt Publishing’,’Printing Ink’,’Jan09’,5240);
INSERT INTO "CustomerInvoices" VALUES (1032,’Packt Publishing’,’Packing Material’,’Mar09’,5020.25);
INSERT INTO "CustomerInvoices" VALUES (1033,’Packt Publishing’,’Offset Paper’,’Jan09’,3000.5);
INSERT INTO "CustomerInvoices" VALUES (1034,’Packt Publishing’,’Offset Paper’,’Jun09’);
INSERT INTO "CustomerInvoices" VALUES (1035,’Packt Publishing’,’Offset Paper’,’Aug09’,3000.5);
INSERT INTO "CustomerInvoices" VALUES (1036,’Packt Publishing’,’Offset Paper’,’Sep09’,3000.5);
INSERT INTO "CustomerInvoices" VALUES (1037,’Bob’,’JapserReports Cookbokk’,’Mar09’,50);
INSERT INTO "CustomerInvoices" VALUES (1038,’Alice’,’JasperReport Cookbook’,’Mar09’,49.5);
INSERT INTO "CustomerInvoices" VALUES (1039,’Packt Publishing’,’Offset Paper’,’Mar09’,8058.5);
INSERT INTO "CustomerInvoices" VALUES (1040,’Packt Publishing’,’Offset Paper’,’Mar09’,5085);
INSERT INTO "CustomerInvoices" VALUES (1041,’Packt Publishing’,’Offset Paper’,’Mar09’);
INSERT INTO "CustomerInvoices" VALUES (1042,’Packt Publishing’,’Printing Ink’,’Mar09’);
INSERT INTO "CustomerInvoices" VALUES (1043,’Packt Publishing’,’Binding Material’,’Mar09’,6522);
INSERT INTO "CustomerInvoices" VALUES (1044,’Packt Publishing’,’Printing Film’,’Mar09’,8999);
INSERT INTO "CustomerInvoices" VALUES (1045,’Packt Publishing’,’Printing Film’,’Mar09’);
INSERT INTO "CustomerInvoices" VALUES (1046,’Packt Publishing’,’Printing Film’,’Jan09’,2558);
INSERT INTO "CustomerInvoices" VALUES (1047,’Packt Publishing’,’Positive Plates’,’Mar09’,7532);
INSERT INTO "CustomerInvoices" VALUES (1048,’Packt Publishing’,’Positive Plates’,’Mar09’,8500);
INSERT INTO "CustomerInvoices" VALUES (1049,’Packt Publishing’,’Developer’,’Mar09’,1589);
INSERT INTO "CustomerInvoices" VALUES (1050,’Packt Publishing’,’Developer’,’Jun09’,4896);
INSERT INTO "CustomerInvoices" VALUES (1051,’Packt Publishing’,’Developer’,’Jun09’,3578.75);
INSERT INTO "CustomerInvoices" VALUES (1052,’Packt Publishing’,’Printing Film’,’Jun09’,1578.95);
INSERT INTO "CustomerInvoices" VALUES (1053,’Packt Publishing’,’Packing Material’,’Jun09’,9852.25);
INSERT INTO "CustomerInvoices" VALUES (1054,’Packt Publishing’,’Binding Material’,’Jan09’,1558.85);
INSERT INTO "CustomerInvoices" VALUES (1055,’Packt Publishing’,’Binding Material’,’’,7895.22);
8. Now press F5 to refresh your pgAdmin window. The CustomerInvoices table will be listed under the
jasperdb2->Schemas->public->Tables->CustomerInvoices. And all the sample data is also inserted.

9. After inserting the required data, you will connect iReport to your database hosted on PostgreSQL. If you are not sure how to connect iReport to your database, you can see the steps described in Post 7: iReport: Creating a report from relational data .Note that you have to enter jasperdb2 as name of the database instance in step 10 of the post
One Important Note
===================
While Coping SQL commands into the consol (SQL shell), use a simple text editor like Notepad to copy the commands. Avoid using WordPad or MsWord to copy the commands. Otherwise inverted-commas (“” and ‘’) can cause a syntax error.
When I was executing these recipes, I always used Notepad and the inverted comma problem never occurred. If you cannot use a simple editor like Notepad, you may follow the procedure described below, which although is a bit teasing but is a workaround to this problem.
If you face this problem then:
1. Copy one command at a time from the text file
2. While pasting the command into the Consol (SQL shell), edit the inverted comas manually after pasting there (i.e.by deleting the copied commas and then typing these commas using your keyboard manually)
Hopefully this will solve the problem.
One Important Tip
===================
If you want to verify weather PostgreSQL Server is running, type “services.msc” at command prompt. Windows services dialog will appear. Verify that PostgreSQL Server 9.3 service is started.
If its status is started then it is OK.
2. A pgAdmin window will open.
3. You will see a tree in the "Object Browser" window on the left half of pgAdmin window labelled "Server1".
A node named PostgreSQL9.X will appear below the Server1.
4. Double click the PostgreSQL9.X node and you will see a password prompt. Provide the database password
you set at the time of your PostgresSQL installation.
5. Double click the "Databases" child of PostgresSQL9.X node, You will see all database instances listed.
6. Select "jasperdb2"child node under "Databases". Now click "Plugins" menu and select "PSQL Consol" option.
7. A SQL Shell consol with your jasperdb2 root will open. Copy following SQL command into the consol.
CREATE TABLE "CustomerInvoices" (
"InvoiceID" integer NOT NULL,
"CustomerName" varchar(80) NOT NULL,
"ProductName" varchar(80) NOT NULL,
"InvoicePeriod" varchar(20) NOT NULL,
"InvoiceValue" real,
CONSTRAINT CustomerInvoices_pkey PRIMARY KEY ("InvoiceID")
);
INSERT INTO "CustomerInvoices" VALUES (1001,’Packt Publishing’,’Packing Material’,’Mar09’,5020.25);
INSERT INTO "CustomerInvoices" VALUES (1002,’Packt Publishing’,’Offset Paper’,’Apr09’,3000.5);
INSERT INTO "CustomerInvoices" VALUES (1003,’Packt Publishing’,’Offset Paper’,’Mar09’,4150.05);
INSERT INTO "CustomerInvoices" VALUES (1004,’Packt Publishing’,’Packing Material’,’Mar09’,2050);
INSERT INTO "CustomerInvoices" VALUES (1005,’Packt Publishing’,’Packing Material’,’Mar09’,5085);
INSERT INTO "CustomerInvoices" VALUES (1006,’Bob’,’JapserReports Cookbook’,’Mar09’,50);
INSERT INTO "CustomerInvoices" VALUES (1007,’Alice’,’JasperReport Cookbook’,’Apr09’,49.5);
INSERT INTO "CustomerInvoices" VALUES (1008,’Packt Publishing’,’Offset Paper’,’Jan09’,8058.5);
INSERT INTO "CustomerInvoices" VALUES (1009,’Packt Publishing’,’Offset Paper’,’Feb09’,5085);
INSERT INTO "CustomerInvoices" VALUES (1010,’ABC Publishing’,’Offset Paper’,’Feb09’,4552.2);
INSERT INTO "CustomerInvoices" VALUES (1011,’ABC Publishing’,’Packing Material’,’Jun09’,8745.5);
INSERT INTO "CustomerInvoices" VALUES (1012,’ABC Publishing’,’Offset Paper’,’Jun09’,6875);
INSERT INTO "CustomerInvoices" VALUES (1013,’ABC Publishing’,’Packing Material’,’Aug09’,9852);
INSERT INTO "CustomerInvoices" VALUES (1014,’Alice’,’WordPress Cookbook’,’Jan09’,45);
INSERT INTO "CustomerInvoices" VALUES (1015,’Bob’,’WordPress Cookbook’,’May09’,45);
INSERT INTO "CustomerInvoices" VALUES (1016,’ABC Publishing’,’Printing Ink’,’Jun09’,2440);
INSERT INTO "CustomerInvoices" VALUES (1017,’Packt Publishing’,’Printing Ink’,’Aug09’,6540);
INSERT INTO "CustomerInvoices" VALUES (1018,’Bob’,’Printing Ink’,’Sep09’,150);
INSERT INTO "CustomerInvoices" VALUES (1019,’Packt Publishing’,’Printing Ink’,’Jan09’,5240);
INSERT INTO "CustomerInvoices" VALUES (1020,’ABC Publishing’,’Printing Ink’,’Mar09’,3450);
INSERT INTO "CustomerInvoices" VALUES (1021,’Packt Publishing’,’Binding Material’,’Jan09’,2580);
INSERT INTO "CustomerInvoices" VALUES (1022,’Packt Publishing’,’Binding Material’,’Feb09’);
INSERT INTO "CustomerInvoices" VALUES (1023,’Packt Publishing’,’Packing Material’,’Jun09’,5870);
INSERT INTO "CustomerInvoices" VALUES (1024,’Packt Publishing’,’Binding Material’,’Jan09’,8770);
INSERT INTO "CustomerInvoices" VALUES (1025,’Packt Publishing’,’Printing Ink’,’Aug09’,5882);
INSERT INTO "CustomerInvoices" VALUES (1026,’Packt Publishing’,’Binding Material’,’Sep09’,4550);
INSERT INTO "CustomerInvoices" VALUES (1027,’Packt Publishing’,’Printing Ink’,’Oct09’,4440);
INSERT INTO "CustomerInvoices" VALUES (1028,’Packt Publishing’,’Offset Paper’,’May09’,8660);
INSERT INTO "CustomerInvoices" VALUES (1029,’Packt Publishing’,’Offset Paper’,’May09’,8660);
INSERT INTO "CustomerInvoices" VALUES (1030,’Packt Publishing’,’Printing Ink’,’Jan09’,5240);
INSERT INTO "CustomerInvoices" VALUES (1031,’Packt Publishing’,’Printing Ink’,’Jan09’,5240);
INSERT INTO "CustomerInvoices" VALUES (1032,’Packt Publishing’,’Packing Material’,’Mar09’,5020.25);
INSERT INTO "CustomerInvoices" VALUES (1033,’Packt Publishing’,’Offset Paper’,’Jan09’,3000.5);
INSERT INTO "CustomerInvoices" VALUES (1034,’Packt Publishing’,’Offset Paper’,’Jun09’);
INSERT INTO "CustomerInvoices" VALUES (1035,’Packt Publishing’,’Offset Paper’,’Aug09’,3000.5);
INSERT INTO "CustomerInvoices" VALUES (1036,’Packt Publishing’,’Offset Paper’,’Sep09’,3000.5);
INSERT INTO "CustomerInvoices" VALUES (1037,’Bob’,’JapserReports Cookbokk’,’Mar09’,50);
INSERT INTO "CustomerInvoices" VALUES (1038,’Alice’,’JasperReport Cookbook’,’Mar09’,49.5);
INSERT INTO "CustomerInvoices" VALUES (1039,’Packt Publishing’,’Offset Paper’,’Mar09’,8058.5);
INSERT INTO "CustomerInvoices" VALUES (1040,’Packt Publishing’,’Offset Paper’,’Mar09’,5085);
INSERT INTO "CustomerInvoices" VALUES (1041,’Packt Publishing’,’Offset Paper’,’Mar09’);
INSERT INTO "CustomerInvoices" VALUES (1042,’Packt Publishing’,’Printing Ink’,’Mar09’);
INSERT INTO "CustomerInvoices" VALUES (1043,’Packt Publishing’,’Binding Material’,’Mar09’,6522);
INSERT INTO "CustomerInvoices" VALUES (1044,’Packt Publishing’,’Printing Film’,’Mar09’,8999);
INSERT INTO "CustomerInvoices" VALUES (1045,’Packt Publishing’,’Printing Film’,’Mar09’);
INSERT INTO "CustomerInvoices" VALUES (1046,’Packt Publishing’,’Printing Film’,’Jan09’,2558);
INSERT INTO "CustomerInvoices" VALUES (1047,’Packt Publishing’,’Positive Plates’,’Mar09’,7532);
INSERT INTO "CustomerInvoices" VALUES (1048,’Packt Publishing’,’Positive Plates’,’Mar09’,8500);
INSERT INTO "CustomerInvoices" VALUES (1049,’Packt Publishing’,’Developer’,’Mar09’,1589);
INSERT INTO "CustomerInvoices" VALUES (1050,’Packt Publishing’,’Developer’,’Jun09’,4896);
INSERT INTO "CustomerInvoices" VALUES (1051,’Packt Publishing’,’Developer’,’Jun09’,3578.75);
INSERT INTO "CustomerInvoices" VALUES (1052,’Packt Publishing’,’Printing Film’,’Jun09’,1578.95);
INSERT INTO "CustomerInvoices" VALUES (1053,’Packt Publishing’,’Packing Material’,’Jun09’,9852.25);
INSERT INTO "CustomerInvoices" VALUES (1054,’Packt Publishing’,’Binding Material’,’Jan09’,1558.85);
INSERT INTO "CustomerInvoices" VALUES (1055,’Packt Publishing’,’Binding Material’,’’,7895.22);
8. Now press F5 to refresh your pgAdmin window. The CustomerInvoices table will be listed under the
jasperdb2->Schemas->public->Tables->CustomerInvoices. And all the sample data is also inserted.
9. After inserting the required data, you will connect iReport to your database hosted on PostgreSQL. If you are not sure how to connect iReport to your database, you can see the steps described in Post 7: iReport: Creating a report from relational data .Note that you have to enter jasperdb2 as name of the database instance in step 10 of the post
One Important Note
===================
While Coping SQL commands into the consol (SQL shell), use a simple text editor like Notepad to copy the commands. Avoid using WordPad or MsWord to copy the commands. Otherwise inverted-commas (“” and ‘’) can cause a syntax error.
When I was executing these recipes, I always used Notepad and the inverted comma problem never occurred. If you cannot use a simple editor like Notepad, you may follow the procedure described below, which although is a bit teasing but is a workaround to this problem.
If you face this problem then:
1. Copy one command at a time from the text file
2. While pasting the command into the Consol (SQL shell), edit the inverted comas manually after pasting there (i.e.by deleting the copied commas and then typing these commas using your keyboard manually)
Hopefully this will solve the problem.
One Important Tip
===================
If you want to verify weather PostgreSQL Server is running, type “services.msc” at command prompt. Windows services dialog will appear. Verify that PostgreSQL Server 9.3 service is started.
If its status is started then it is OK.
Comments
Post a Comment