JDBC and Pointbase

Documentation

• JDBC Tutorial
• Pointbase documentation
• Pointbase Console Graphical User Interface (GUI) database administration tool.

Simple Pointbase Example

This example demonstrate how to use JDBC to connect to Pointbase, create and delete tables, and add and query data.

• First, you need to create a pointbase.ini file in which you tell Pointbase where to store your database.
  
  A typical pointbase.ini file will have:
  database.home=<full-directory-path>
  
  A sample pointbase.ini is available here.
  
  
• Download the code for the 3 examples
  • DBTest - Demonstrated a simple and naive way to check that the database is working fine. Program prints the names of all tables in the database.
  • DBTable - Demonstrates how to add/delete tables and how to insert/query data.
  • DBInteractive - Interactive program that demonstrates how to add/delete tables and how to insert/query data.
  
  
• To compile the examples, execute the command:
  
  javac -classpath /u/csc309h/lib/pointbase/pbembedded41ev.jar:. filename.java


• Before running DBTest and DBTable, you need to create the sample database using the Pointbase Pointbase Console. To run the Console type:
  
  java -classpath .:/h/u2/csc309h/lib/pointbase/ pbembedded41ev.jar :/h/u2/csc309h/lib /pointbase/pbtools41ev.jar com.pointbase. tools.toolsConsole
  
  Once the "Connect to Database" dialog box appears, set the "Create New Database" checkbox. For all other options, choose the default.
  
  An alternative is to run use the Pointbase Commander, which provides similar functionality in a command-line interface. To run the Commander type:
  
  java -classpath .:/h/u2/csc309h/lib/pointbase/ pbembedded41ev.jar: /h/u2/csc309h/lib/ pointbase/pbtools41ev.jar com.pointbase.tools.toolsCommander
  
  Warning! By default, Commander runs with the "autocommit" option off. This will prevent pointbase from writing any modifications made to the database to disk, e.g., if you create a table it will banish once you quit Commander. To fix this problem, after starting Commander type "set autocommit on;"


• To run the examples execute the command:
  
  java -cp /u/csc309h/lib/pointbase/pbembedded41ev.jar:. classname

Servlet+Pointbase Example

This example demonstrate how to use PointBase from a servlet, which provides a simple web-based interface for issuing SQL queries to a PointBase database.

To install the servlet, follow this steps:

• Untar the following the file jdbc.tar in your webapps directory.
• Copy the file pointbase.ini located in $CATALINA_HOME/webapps/jdbc/WEB_INF/classes to $CATALINA_HOME/bin.
  
• Add the following line to pointbase.ini:
  
  database.home=$CATALINA_HOME/webapps/jdbc/WEB-INF/classes
  
  Important: replace $CATALINA_HOME with a fully qualified path, e.g., /u/yourname/project/tomcat.
  
  

To compile the servlet:

• Make sure that all of the following jar files are in your CLASSPATH:
  • /u/csc309h/lib/tomcat-5.0.28/common/lib/servlet-api.jar
  • /u/csc309h/lib/tomcat-5.0.28/common/lib/commons-pool-1.2.jar
  • /u/csc309h/lib/tomcat-5.0.28/common/lib/commons-dbcp-1.2.1.jar
• Type: "javac SQLGateway.java"

To run the servlet:

• Point Firefox to: http://127.0.0.1:your_port/jdbc/servlet/SQLGateway
• Type a sql query, e.g., select * from systables, and press the execute button.