LOGISTICS AND LATE POLICY REMINDER

• There is an important document relevant to the PDA part of this assignment: Interfacing with PostgreSQL. This document lists a variety of ways to interface with PostgreSQL in various languages.

• Turning in PDA: Your PDA work will again be submitted electronically. Use the "submit" command on teach, learn, or hawking, which share your home directory with linux.ic.ucsc.edu. The command is:

  submit cmps180-ark.w02 proj5 files...

  Reminder: please do not submit any files containing large data sets or query results. All results should be truncated to a few lines. Large submissions will be rejected, either automatically or by a (not too happy) grader.

  Programming work must be submitted electronically by midnight at the end of the day that it is due. Programming work submitted after the deadline but before the "late deadline" -- midnight 48 hours after the deadline -- will be accepted but penalized 50%, except for the first two late project parts handed in late and the first two homework assignments handed in late. No programming work will be accepted after the late deadline.

THE PROJECT

Personal Database Application, Part 5

1. A list of at least five alternative options is offered to the user. (An additional alternative should be quit.)
2. The user selects an alternative.
3. The system prompts the user for appropriate input values.
4. The system accesses the database to perform the appropriate queries and/or modifications.
5. Data or an appropriate acknowledgment is returned to the user.

You should include both queries and modifications among your options. As in your previous assignment, please include some "interesting" queries or modifications, i.e., operations that require some of the more complex SQL constructs such as subqueries, aggregates, set operators, etc.

As a general example, if your PDA is a UC campus applicant database, then your interface might include in its menu:

• A number of useful queries on the database, with both input and output in a format more convenient and pleasing than raw interactive SQL. Some queries perform statistical analysis requiring multiple levels of grouping, other queries are simpler.
• Insert a new student record.
• Insert a new application record.
• Update a student's address, GPA, or SAT.
• Update an application's decision field based on user input.
• Update application decision fields automatically, admitting students based on a combination of numeric values and quotas for each campus and/or major.
• Quit.

Your application code should interact with the database using embedded SQL for C or C++ programs, or using the JDBC call-level interface for Java programs. We are not expecting anything particularly fancy in terms of the interface itself. (In fact, ultimately this interface will be replaced by the Web interface you will create in the final part of the project.) For example, in C a menu printed via printf is fine. Also, handling of SQL errors can be quite simple. You can write a routine that just prints the error message from Oracle, or model your error handler after one of our sample programs.

Submission

• If you use your small database, be sure to test your program on your large database as well to make sure it is correct and reasonably efficient.
• If you use your large database, please do not submit listings showing hundreds of lines of query results.

For this assignment the following files should be turned in electronically using your cats account and the submit program:

README	Please give your name, project part number, course number, date, the title of your project, a list and description of the files you are submitting, and any other information that will be useful for the grader.
your source code name(s)	Your source code file(s).
execution-small.log	A script log illustrating a successful run of your program from the console using your small database. Your script should be sufficient to convince us that all of your commands run successfully.
execution-large.log	A script log illustrating a successful run of your programs using your large database. Your script should be sufficient to convince us that your commands run successfully, but you can and should truncate query results after a few lines. Please do not turn in query results that are hundreds of lines long.

Examples and references

Basically to get help I usually go to Google and enter in something like "Perl PostgreSQL Connect" or what ever set of words summarizes the current problem. That is how I found all of the links below.

Technically speaking most of these examples use call level interfaces (CLI's) rather than embedded SQL. I (jsnook) found the CLI's straight forward to use.

To run your c or perl programs preface the file name with ./ Linux doesn't know to look in your current directory for the executable unless you add set path = ($path .)

Programing in Perl

Here is a sample script that connects and does a query. It uses the DBI module and seems to be able to find every thing with the paths as currently set up on the Linux machine:

To run I entered: ./beers.pl

#!/usr/bin/perl
# beers.pl - my first perl data base script!
use DBI;
$dbh = DBI->connect('dbi:Pg:dbname=jsnook_beers', 'jsnook', '');
$sth = $dbh->prepare("SELECT * FROM Sells WHERE bar LIKE '%Joe''s%'");
$rv = $sth->execute;
while (my @row_ary  = $sth->fetchrow_array)
  {print " $row_ary[0]  $row_ary[1]\n";}
$sth->finish;
undef $sth;
$dbh->disconnect;
undef $dbh;

Embedded SQL in C using "ecpg"

"ecpg" will pre-process embedded SQL C programs. There's even a man page for it on Linux.ic '>man ecpg'. It adds several lines to the .c file:

/* Processed byecpg (2.7.1) */
/* These three include files are added by the preprocessor */
#include <ecpgtype.h>
#include <ecpglib.h>
#include <ecpgerrno.h>

"Along with Professor Keller's help, I've managed to write to my database. You need to tell your code which database to talk to and who you are in order to get write access. You'll need the following line:

EXEC SQL CONNECT TO <yourdatabasename> USER <yourusername>;

EXEC SQL COMMIT;

EXEC SQL INCLUDE sqlca;
EXEC SQL WHENEVER sqlerror sqlprint;

#define NO_MORE_TUPLES (sqlca.sqlcode == ECPG_NOT_FOUND)

% ecpg -I /usr/include/pgsql mycode.pgc
% gcc -c -I /usr/include/pgsql mycode.c
% gcc -lecpg -o mycode mycode.o
% ./mycode

mycode:   mycode.o
	gcc  -lecpg -o mycode mycode.o
mycode.o: mycode.c
	gcc -c -I /usr/include/pgsql mycode.c
mycode.c: mycode.pgc
	ecpg -I /usr/include/pgsql mycode.pgc
clean:
	rm *.o mycode mycode.c

Here is a simple sample program:

/*************************************************************
·	FILE:                   menu.pgc                     *
·	AUTHOR:                 Karl Brandt                  *
·	kbrandt@cats.ucsc.edu        *
·	DATE CREATED:           11/07/01                     *
·	DATE LAST CHANGED:      11/08/01                     *
  *************************************************************/
#include <stdio.h>
#define NO_MORE_TUPLES (sqlca.sqlcode == ECPG_NOT_FOUND)

/**********************
·	main ()
  */
int main(int argc, char* argv[])
{
   EXEC SQL BEGIN DECLARE SECTION;
      int mynumb;
      char mystr[30];
   EXEC SQL END DECLARE SECTION;

   /* initiation  stuff */
   EXEC SQL INCLUDE sqlca;
   EXEC SQL CONNECT TO ksbtest USER kbrandt;
   EXEC SQL WHENEVER sqlerror sqlprint;

   /* create a simple table to play with */
   EXEC SQL CREATE TABLE test1 ( numb INT, str CHAR(30));
   /* insert some tuples */
   EXEC SQL INSERT INTO test1 ( numb, str ) VALUES(1234, 'UCSC Slugs');
   EXEC SQL INSERT INTO test1 ( numb, str ) VALUES(3, 'bar');
   EXEC SQL COMMIT;
   /* modify a tuple */
   EXEC SQL UPDATE test1 set str = 'foo' where numb = 3;
   EXEC SQL COMMIT;
   /* read a tuple */
   EXEC SQL SELECT str INTO :mystr FROM test1 where numb = 3; mystr[29] = '\0'; puts(mystr);
   /* fun with cursors */
   EXEC SQL DECLARE mycursor CURSOR FOR SELECT numb FROM test1;
   EXEC SQL OPEN mycursor;
   while (1) {
      EXEC SQL FETCH FROM mycursor INTO :mynumb; 
      if (NO_MORE_TUPLES) break; 
      printf("mynumb = %i\n ", mynumb);
   }
   EXEC SQL CLOSE mycursor;
   return 0;
}

Programing in C using "libpq"

Here is a link to another reference C Language Interface . Here is a link to another reference libpq - C Library. Here is a link to a C++ reference C++ Language Interface . Here is a link to another C++ reference libpq++ - C++ Binding Library.

Here is a sample 'c' program that connects and does a query:
To compile I entered: gcc -o beers -I/usr/include/pgsql beers.c -lpq
To run I entered: ./beers

/* beers.c */
/* CMPS180 Project Part 5 Sample Program */		
/* Accesses Beers Data Base */

#include <stdlib.h>
#include <stdio.h>
#include <libpq-fe.h>

int display_result( PGresult *res );

int main()
{
   PGconn *dbconnection = NULL; 
   PGresult *res;

   /* first try to establish connection to DB and check for errors */
   dbconnection = PQconnectdb( "host=localhost dbname=jsnook_beers" );
   if (PQstatus( dbconnection ) == CONNECTION_BAD) {
      fprintf( stderr, "No connection: %s", PQerrorMessage( dbconnection ));
      return EXIT_FAILURE;
   } 

   /* attempt to execute a query */
   res = PQexec( dbconnection, "SELECT beer, 
   price FROM Sells WHERE bar LIKE '%Joe''s%'");

   /* check for results */
   if (!res) {
      printf("Query failed\n");
   } else if (PQresultStatus( res ) == PGRES_TUPLES_OK){
      printf("The following data was returned:\n");
      display_result( res );
   } else {
      printf("Query failed, code: %s, mess: %s\n", 
         PQresStatus(PQresultStatus(res)),
         PQresultErrorMessage(res));
   }

   /* clean up */
   if (res) 
      PQclear(res);
   if ( dbconnection == NULL) 
      return EXIT_FAILURE;
   PQfinish( dbconnection );
   return EXIT_SUCCESS;
}  
    

/* display a result set */
/* this is the easy way to print a result set if */
/* you don't want to use a cursor and do more */
int display_result( PGresult *res )
{
   PQprintOpt print_opts;
   FILE *out_stream = fopen( "/dev/tty", "w" );
   if (out_stream == NULL) {
      return 0;
   }

   /* set up print options or use defaults */
   memset( &print_opts, '\0', sizeof( print_opts ));
   print_opts.header = 1;       
   print_opts.align = 1;        
   print_opts.html3 = 0;        
   print_opts.fieldSep = "|";   
   
   /* print out the result set */                        
   PQprint( out_stream, res, &print_opts );  
   return 1;                    
}

Programing in Java

Here is a link to reference on PostgreSQL and Java . Here is a link to another on the JDBC Interface .

Java and Javac are located in /usr/java/jre1.3.1_02/bin. To add this directory to your PATH, use the command:

setenv PATH $PATH:/usr/java/jre1.3.1_02/bin

You can add this command to a .path file in your home directory. See below for more details.

Below is a sample Program that uses the JDBC classes and the driver for PostgreSQL:

To compile I entered: javac Beers.java in a console session in the BE105 lab.

To run I entered: java Beers from a console session on the Linux machine.

You also need to set up your classpath or use the classpath command line option to find the JDBC driver classes for PostgreSQL. Last quarter June Sison figured out that you can do this by adding the path /usr/lib/pgsql to the .path file in your home dir [you can create one if you don't have one - don't forget the . at the beginning of the file name]. If you create the file, you put in the line:

setenv CLASSPATH .:/usr/share/pgsql/jdbc7.1-1.2.jar:/usr/lib/pgsql

The example shows the execution of a query using st.executeQuery( String ), you can also execute updates using st.executeUpdate( String ).

It wasn't used in the sample but one way of reading input one line at a time from the console is to declare and create a BufferedReader that uses System.in Here is a snippet:

    BufferedReader rdr = null;
    try{
        rdr = new BufferedReader(new InputStreamReader( System.in ));
    }catch(Exception e){}
    String input = rdr.readLine();
 

// CMPS180 Project Part 5 Sample Program		
// Accesses Beers Data Base


import java.io.*;
import java.util.*;
import java.sql.*;

// A class to interface to the Beers data base
public class Beers
{
    static Connection db = null;
    static Statement st = null;
    static String dbName = "jsnook_beers";
    static String userNmae = "jsnook";

    // Execute an SQL query and then display up to n rows of the results 
    static public void ExecuteQueryAndDisplayResult(String query, int maxRows) 
    	throws SQLException
    {
    	boolean found = false;
    	int cols = 0;
    	int i, j;
    	
	System.out.println( query );
	ResultSet rs = st.executeQuery( query );
	j = 0;
	cols = rs.getMetaData().getColumnCount();
	// lable columns
	for ( i = 1; i <= cols; i++){
		System.out.print(rs.getMetaData().getColumnLabel( i ).trim() + " | ");
	}
	System.out.println(" ");
	while(rs.next() && j < maxRows) {
		for ( i = 1; i <= cols; i++){
			System.out.print(rs.getString(i).trim() + " | ");
		}
		System.out.println(" ");
		j++;
		found = true;
	}
	if (!found)
		System.out.println("Sorry No Matches Found.");
	rs.close();
	System.out.println( " " );
    }
 
 
    // Initialize SQL and IO objects used to access the data base and provide user IO
    static public void initialize()
    {
        
        try{
		Class.forName("org.postgresql.Driver");
	}catch(Exception e){
	        System.out.println( "Can't find JDBC Driver" );
	        System.out.println( e.getMessage() );
 		System.exit( 0 );
        } 
        try{
		db = DriverManager.getConnection("jdbc:postgresql:" + dbName, 
			userNmae, "");
	}catch(Exception e){
	        System.out.println( "Can't get Connection " );
	        System.out.println( e.getMessage() );
		System.exit( 0 );
        } 
        try{
	    st = db.createStatement();
	}catch(Exception e){
	        System.out.println( e.getMessage() );
        	try{
        		db.close();
		}catch(SQLException se){}
		System.exit( 0 );
        } 
    }
    
    public static void main(String [] argv)
    {
        initialize();
        try{
    	    ExecuteQueryAndDisplayResult( 
    		"SELECT beer, price FROM Sells WHERE bar LIKE '%Joe''s%'", 20 );
	    st.close();	
	    db.close();
	}catch(Exception e){
	        System.out.println( e.getMessage() );
        } 
	System.exit( 0 );
	
    }
    
 } // class DataMachine