Using JDBC in Creator

There's nothing special about using JDBC from within a Creator Web Application. - just get your connection and away you go.  You can google the net for all sorts of advice on using jdbc.  And there's always those book thingies.

Some notes:

Getting the connection from the app server's connection pool. 

For the bundled Application Server 8.1 PE, or whatever app server you plan to use, it's likely that you will be using database connection pools.  Getting a connection from the pool is usually done through a JNDI lookup.  For example, if you wish to use the creator data source "Travel", just do this:

            
import javax.sql.DataSource;
import java.sql.Connection;
    .....
        Connection conn = null ;
        // the following should be in a try-catch...


javax.naming.Context ctx = new javax.naming.InitialContext() ;
     DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/Travel") ;
           
     conn = ds.getConnection() ;

Setup your connection

Because you are likely using your app/web server's connection pool, set your connection properties at the start. For example, to assure the connection behaves the way you desire, call setAutoCommit().

Cleanup your connection and JDBC objects when finished.

Commit or rollback your transaction.

Close all your JDBC objects. Close each Statement and ResultSet, then close the connection.  Put this code in a finally block to make sure it's executed.

Don't assume a close() on the connection will do all of the above.  If the connection is from a pool, the jdbc driver never sees the close().

Can I use the same Data Source for my CachedRowSet(s) and for my own JDBC stuff?

Yes.

Having Creator setup your Data Source in the bundled App Server

For test deploys to Creator's bundled App Server, Creator will configure any data sources used by your CachedRowSet properties.  If you need a Data Source that's not used by a CachedRowSet, you can tell crreator to handle it by going to the Projects Pane, select the "Data Source References" node, then right-click "Add a Data Source Reference".

Synchronizing a CachedRowSet with my manual JDBC changes

Suppose you have a Table component displaying data from a CachedRowSet.  You need to do some complex operation that updates/deletes/inserts data.  How do you need to synchronize the data in the CachedRowSet?  Answer:  that's up to you.  The easiest way is to just re-execute the CachedRowSet.  Otherwise you'll need to iterate through the CachedRowSet, or use a wrapping CachedRowSetDataProvider, to manually update the CachedRowSet.

When should I use CachedRowSets and when should I use JDBC?

That's up to you.  A CachedRowSet is good for retrieving data, and it's easy to use for simple updates/inserts/deletes of that data.  However, it doesn't do everything, so don't hesitate to use plain old JDBC if that's what is needed.

Sample

        Connection conn = null ;
        Statement sqlStatement = null ;
        ResultSet rs = null ;
        try {
            javax.naming.Context ctx = new javax.naming.InitialContext() ;
            DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/Travel") ;
           
            conn = ds.getConnection() ;
           
            // setup the connection
            conn.setAutoCommit(false) ;
           
            // execute the query
            sqlStatement = conn.createStatement() ;
            rs = sqlStatement.executeQuery("select count(\*) from TRIP" ) ;
            rs.next() ;
            int rows = rs.getInt(1) ;
            conn.commit() ;
           
            info("Rows in table TRIP: " + Integer.toString(rows)) ;
           
        } catch (Exception ex) {
            error("Error counting rows: " + ex.getMessage() );
            try {
                if ( conn != null ) {
                    conn.rollback() ;
                }
            } catch (SQLException sqle) {
                log("Error on rollback " + sqle.getMessage() );
            }
        }
        finally {
           
            // close the ResultSet
            if ( rs != null ) {
                try {
                    rs.close() ;
                } catch (Exception ex) {
                    log("Error Description", ex);
                }
            }
            // close the statement
            if ( sqlStatement != null ) {
                try {
                    sqlStatement.close() ;
                } catch (Exception ex) {
                    log("Error Description", ex);
                }
            }
           
            if ( conn != null ) {
                // cleanup and close the conneciton.
                try {
                    conn.close() ;    
                } catch (Exception ex) {
                    log("Error Closing connection ", ex);
                }
            }
        }
Comments:

You referenced this in relation to a Java Creator Forum you answered: http://swforum.sun.com/jive/thread.jspa?threadID=60334&tstart=15 I tried something like this once, but got error messages from Creator that it wasn't allowed in Creator. I will try again. Thanks. dave

Posted by David Kovach on December 11, 2005 at 10:18 PM PST #

There's nothing to stop you from doing JDBC within Creator, so I'm curious as to the "wasn't allowed" error message. A CachedRowSet expects a ResultSet from it's "command" (i.e., it extends ResultSet). That limits it's purpose to a SELECT or a stored proc that returns one ResultSet. note: a stored proc won't work at design time since all/most databases can't determine the possible sproc's resultset without executing - so we can't give you the column names. For those reasons, our query editor only supports SELECT statements.

Posted by Joel Brown on December 12, 2005 at 02:00 AM PST #

What does this error message mean? "problem in Table Insertion: javax.naming.NameNotFoundException: No object bound to name java:comp:env/jdbc/npgs_817"

Posted by David Kovach on December 13, 2005 at 11:33 PM PST #

Post a Comment:
Comments are closed for this entry.
About

jfbrown

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
News
Blogroll

No bookmarks in folder