Skip to main content

Copy Sample Data Into Postgress for Post 11, Post 12, Post 13, Post 14, Post 15

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.

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.

Comments

Popular posts from this blog

Index: JSF

Java Server Faces Basics Post 1: JSF : Introduction to JSF  Post 2: JSF:  configuration files  Post 3: JSF: Installation Post 4: JSF: Your first JSF project   Standard JSF Tags Post 1: JSF: textbox example Post 2: JSF: password example Post 3: JSF: textarea example JSF 2 GraphicImage Example Post 4: JSF: GraphicImage Example Post 101 : JSF : The valueChangeListener Attribute Example

Index: iReport

iReport for Java ------------------------------------------------------------ Titles and Headers ------------------------------------------------------------ Post 1: iReport : Downloading, installing, and running JasperReports and iReport Post 2: iReport : Creating your first "Hello World" report   Post 3: iReport : Understanding iReport Elements Post 4: iReport : Creating and sizing the title for your report Post 5: iReport: Inserting a company logo in the title of your report  Post 6: iReport : Installation of PostgreSQL Post 7: iReport : Creating a report from relational data Post 8: iReport : Using dynamic titles that can change during report processing. Post 9: iReport : Adding a simple header to your report Post 10: iReport : Setting margins for your report and aligning the report header relative to report margins  ------------------------------------------------------------ Body and Footer ---------------------------------------------------------...