JDataStore is a feature of JBuilder Professional and Enterprise, and the Inprise Application Server.
JDataStore provides embedded database functionality in your applications with a single JDataStore file and the JDataStore JDBC driver (and its supporting classes). No server process is needed for local connections. In addition to industry-standard JDBC support, you can take advantage of the added convenience and flexibility of accessing the JDataStore directly through the DataExpress API. You can use both types of access in the same application.
JDBC access requires that the JDataStore be transactional. DataExpress does not. This chapter begins with DataExpress access, then discusses transactional JDataStores, and finally the local JDBC driver. The remote JDBC driver and JDataStore Server are discussed in "Multi-user and remote access to JDataStores."
StorageDataSet
, such as TableDataSet
, to a stream inside a JDataStore. The StorageDataSet
represents a table in the embedded database and provides all the methods necessary to navigate, locate, add, edit, and delete data.
dsbasic
project/package and name it
DxTable.java
:
// DxTable.java package dsbasic; import com.borland.datastore.*; import com.borland.dx.dataset.*; public class DxTable { DataStoreConnection store = new DataStoreConnection(); TableDataSet table = new TableDataSet(); public void demo() { try { store.setFileName( "Basic.jds" ); table.setStoreName( "Accounts" ); table.setStore( store ); table.open(); } catch ( DataSetException dse ) { dse.printStackTrace(); } finally { try { store.close(); table.close(); } catch ( DataSetException dse ) { dse.printStackTrace(); } } } public static void main( String[] args ) { new DxTable().demo(); } }Because the program uses DataExpress, it imports the DataExpress package in addition to the JDataStore package. The class has two fields: a
DataStoreConnection
and a TableDataSet
. The main()
method instantiates a new instance of the class and executes its demo()
method.
StorageDataSet
. This class has three subclasses to be used for different kinds of data sources:
QueryDataSet
is for data from SQL queries.
ProcedureDataSet
is for data from a SQL stored procedure.
TableDataSet
has no predefined provider of data.
If you are defining a completely new table, use TableDataSet
.
Each StorageDataSet
has a store
property that is null when the object is instantiated. If it's still null when the dataset is opens, a com.borland.dx.memorystore.MemoryStore
is assigned automatically, which means that the data is stored in memory. If you assign a DataStoreConnection
or DataStore
to the store
property, the data is stored in a persistent JDataStore file instead.
To connect a StorageDataSet
to a JDataStore, assign values to these three properties:
fileName
property of the DataStoreConnection
. This indicates which JDataStore to connect to.
The storeName
property of the StorageDataSet
. This indicates the name of the table stream inside the JDataStore. You can reuse an existing name if it's for the same dataset. Otherwise, you must use a new name. (It's up to you to manage what's inside the JDataStore and choose names that don't conflict.)
The store
property of the StorageDataSet
, which is set to the DataStoreConnection
(or DataStore
) object. This connects the two together.
Peform these three steps in any order. Once you've set all three properties, you have a fully qualified connection between a StorageDataSet
and a JDataStore.
In DxTable.java
, the JDataStore file is Basic.jds
, which you created in "Creating a JDataStore file." The table stream is named "Accounts." Think of it as the name of the table. DxTable.java
assigns DataStoreConnection
as the value of the TableDataSet
's store
property.
Then the TableDataSet
opens. Opening a dataset that has a JDataStore attached automatically opens that JDataStore file. If the JDataStore opens successfully, the program creates the named table stream if it doesn't already exist. If it does exist, that table stream reopens. This establishes an open connection between the dataset and its table stream in the JDataStore. Note that if there is a file stream in the JDataStore with the same name, the program throws an exception because you can't have a table stream with the same name as a file stream.
StorageDataSet
connected to a JDataStore results in an open table stream. For new table streams, QueryDataSet
and ProcedureDataSet
then fetch data from their data source and populate the table stream as explained in "Tutorial: Offline editing with JDataStore." But TableDataSet
has no data source. You start with an empty and undefined table stream.
Add the highlighted statements to DxTable.java
:
table.open(); if ( table.getColumns().length == 0 ) { createTable(); } } catch ( DataSetException dse ) {To detect that a table stream is new, check the number of columns in the
TableDataSet
. If it's zero, you can then define the columns in the table. In this case, it's done by a method called createTable()
. Add it to DxTable.java
:
public void createTable() throws DataSetException { table.addColumn( "ID" , Variant.INT ); table.addColumn( "Name" , Variant.STRING ); table.addColumn( "Update", Variant.TIMESTAMP ); table.addColumn( "Text" , Variant.INPUTSTREAM ); table.restructure(); }In this demo program, the
createTable()
method uses the simplest form of the StorageDataSet
.addColumn()
method to add columns individually by name and type. The columns have no constraints. Character columns, defined as Variant.STRING
, can contain strings of any length. You can define columns with constraints by defining Column
objects, setting the appropriate properties such as precision
, and then adding them with the addColumn()
or setColumns()
methods to the table.
After you modify the structure of the table by adding these new columns, activate the changes by calling the StorageDataSet
.restructure()
method. The result is an empty but structured table stream, a new table in the JDataStore. (If you know the table doesn't exist, you can use addColumns()
to define the structure before opening the TableDataSet
. Then you won't need to call restructure()
.)
You can store as many tables as you want in a single JDataStore file. They must use different table stream names. You can use the same DataStoreConnection
object in the store
properties of each TableDataSet
.
There are other ways to create tables in a JDataStore. In particular, you can use an SQL CREATE TABLE statement through the JDataStore JDBC driver.
TableDataSet
object, just as you would with any dataset. You can create filters, indexes, master-detail links, and so on. In fact, such secondary indexes are also persisted and maintained in the JDataStore file, making the JDataStore a complete embedded database.
To complete the demonstration program, add a smattering of DataExpress functionality with this new method:
public void appendRow( String name ) throws DataSetException { int newID; table.last(); newID = table.getInt( "ID" ) + 1; table.insertRow( false ); table.setInt( "ID", newID ); table.setString( "Name", name ); table.setTimestamp( "Update", new java.util.Date().getTime() ); table.post(); }Add the highlighted statements to the
demo()
method:
if ( table.getColumns().length == 0 ) { createTable(); } table.setSort( new SortDescriptor( new String[] {"ID"} ) ); appendRow( "Rabbit season" ); appendRow( "Duck season" ); table.first(); while ( table.inBounds() ) { System.out.println( table.getInt( "ID" ) + ": " + table.getString( "Name" ) + ", " + table.getTimestamp( "Update" ) ); table.next(); } } catch ( DataSetException dse ) {After the program opens the table, creating the table's structure if necessary, it sets a
SortDescriptor
on the ID field. To add some rows, it calls the appendRow()
method.
The
appendRow()
method begins by going to the last row in the table and obtaining the value of the ID field. Because of the sort order, this value should be the highest ID number used so far. (If the table is empty, the getInt()
method returns zero.) The new ID value is one greater than the last. appendRow()
inserts a new row and sets its attributes, including the Update field, which is set to the current date and time. Finally appendRow()
saves the new row by calling the post()
method.
After appending a few rows, a loop navigates through the table, displaying its contents in the console. Finally, the JDataStore and TableDataSet
are closed.
If you run the program a few times, you'll see that the new rows get unique ID numbers. This method of generating ID numbers works for a simple single-threaded demonstration program like this that always commits new rows after getting the old ID number. But for more realistic programs, such an approach may not be safe. To use a more robust approach, you must understand locks and transactions.
While this behavior is safe for simple applications, more robust applications require some level of transaction isolation. Not only do transactions ensure that you are not reading dirty or phantom data, but you can also undo changes made during a transaction. Transaction support also enables automatic crash recovery. It's required for JDBC access.
com.borland.datastore.TxManager
class. A JDataStore can be transactional when it is first created, or you can add transaction support later. In either case, you assign a TxManager
object as the value of the txManager
property of the DataStore
object, usually before calling the create()
or open()
method.
If you assign the txManager
property on an open JDataStore, it causes the TxManager
to automatically shut down and attempt to reopen the JDataStore so that the new property setting can take effect immediately. If the DataStoreConnection.userName
property has not been set, the JDataStore will fail to reopen, and an exception will be thrown.
The properties of the TxManager
object determine various aspects of the transaction manager. When instantiated, the TxManager
has usable default settings for these properties. If you want to change any of these settings, it's better to do it before creating or opening the JDataStore.
The first time the now-transactional JDataStore opens, it stores its transactional settings internally. The next time you open the JDataStore, you don't have to assign a TxManager
. Instead the JDataStore automatically instantiates a TxManager
with its stored settings.
To open (or create) a transactional JDataStore, you must also set the DataStoreConnection
.userName
property. The userName
property is used to identify individuals in a multi-user environment when necessary, such as during lock contention. If there is no name in particular that you find appropriate, you can set it to a dummy name.
DataStore store = new DataStore(); store.setFileName( "SomeFileName.jds" ); store.setUserName( "AnyNameWouldWork" ); store.setTxManager( new TxManager() ); store.create();The two differences between this code and a non-transactional JDataStore is the setting of the
userName
and txManager
properties. (You can set them in any order.) If you don't want the default settings, the code generally looks something like this:
DataStore store = new DataStore(); TxManager txMan = new TxManager(); // Make changes to TxManager txMan.setRecordStatus( false ); store.setFileName( "SomeFileName.jds" ); store.setUserName( "AnyNameWouldWork" ); store.setTxManager( txMan ); store.create();In this example, the
recordStatus
property, which controls whether status messages are written, is set to false.
DSX: See "Creating a new JDataStore file."
open()
instead of create()
. For a default TxManager
, the code might look like this:
DataStore store = new DataStore(); store.setFileName( "SomeFileName.jds" ); store.setUserName( "AnyNameWouldWork" ); store.setTxManager( new TxManager() ); store.open();Note that even though you are much more likely to use a
DataStoreConnection
to open an existing JDataStore file, you can't use one when you are adding transaction support, because txManager
is a property of DataStore
, not DataStoreConnection
.
DSX: See "Making the JDataStore transactional."
userName
. Because it doesn't hurt to specify a userName
for a non-transactional JDataStore (it's simply ignored), you might want to always specify a userName
when opening a JDataStore. The code would look something like this:
DataStoreConnection store = new DataStoreConnection(); store.setFileName( "SomeFileName.jds" ); store.setUserName( "AnyNameWouldWork" ); store.open();Because no
TxManager
was assigned, when the JDataStore opens, a TxManager
is automatically instantiated with its properties set to the values that were persisted in the JDataStore. The TxManager
is assigned to the JDataStore's txManager
property. You can get the values of the persisted transaction management properties from there, but you can't change them directly.
TxManager
object before opening. The TxManager
object knows which properties have been assigned and which ones have been left at their default value. If you assign a TxManager
to a transactional JDataStore, only those properties that have been assigned in the new TxManager
are changed. All other properties remain as they were; they do not revert to default values (the ones in the new TxManager
).
As when adding transaction support, you should assign the TxManager
with the new values before you open the JDataStore. For example, suppose you want to change the softCommit
property to true. Doing so improves performance by not guaranteeing recently committed transactions (within approximately one second before a system failure) yet it still guarantees crash recovery:
DataStore store = new DataStore(); TxManager txMan = new TxManager(); // Make changes to TxManager txMan.setSoftCommit( true ); store.setFileName( "SomeFileName.jds" ); store.setUserName( "AnyNameWouldWork" ); store.setTxManager( txMan ); store.open();Note that the other properties, such as
recordStatus
, aren't set. Although the new TxManager
has the default setting when it is assigned to the JDataStore, the setting in the JDataStore isn't affected, even if it's not the default.
DSX: See "Modifying transaction settings."
TxManager
properties control these attributes of the transaction log files:
Where to put them. When the log files are duplexed, the two copies are usually kept in different locations. Keeping them on different physical drives increases reliability and the chance for recovery even further, and it might also offset some of the performance penalty.
How big they should get before starting another one.
By default, you get one copy of the log files (simplexing instead of duplexing) in the same directory that contains the JDataStore file.
Whenever a JDataStore is transaction-enabled the first time, it creates its log files. The names of the log files use the name of the JDataStore file without the file extension. For example, if the JDataStore file MyStore.jds
uses simplex transaction logging, these log files are created:
MyStore_STATUS_0000000000
,
MyStore_LOGA_ANCHOR
MyStore_LOGA_0000000000
.
Duplex logging adds the files MyStore_LOGB_ANCHOR
and MyStore_LOGB_0000000000
. These two sets of log files are referred to as the "A" and "B" log files. The ALogDir
and BLogDir
properties control the location of these files.
Once a log files reaches the size determined by the TxManager
's
maxLogSize
property, additional status and record files are created with the log file number incrementing by one each time. As old log files are no longer needed for active transactions or crash recovery, they are automatically deleted. For information on archiving them, see "Saving log files."
ALogDir
and BLogDir
properties are not set, then the location of the log files is always assumed to be in the same directory as the directory of the JDataStore file. This makes it easier to move the JDataStore from one directory to another.
If the
ALogDir
and BLogDir
properties are set, they include the drive and full path, which means two things:
ALogDir
and BLogDir
properties, you should try to create your transactional JDataStore files in a location with the same drive and directory name. For example, if you intend to deploy the files to the D: drive, but the JDataStore files were created on the C: drive because you don't have a D: drive on your development computer, you must go through the extra steps of moving the log files when you deploy because the drives are different.
When you move log files, follow these steps:
Move the log files to the new location. Be sure to remove or rename any copies of the files in the original location.
Create a new TxManager
with the new location property settings. Assign it to the txManager
property of the DataStore
.
Open the JDataStore. The TxManager
looks in the new location, sees that the log files are there, and changes the persisted settings in the JDataStore.
You only have read-only access to the JDataStore files. For example, they might be on a CD-ROM or a network directory where you don't have write access.
In both cases you can temporarily bypass transaction support by opening the JDataStore in read-only mode. Do this through a DataStore
object. Before opening the JDataStore, set its readOnly
property to true. For example,
DataStore store = new DataStore(); store.setFileName( "SomeReadOnly.jds" ); store.setReadOnly( true ); store.open();Because you are bypassing the
TxManager
, you don't need to set the userName
. If the transaction log files are lost, use the copyStreams()
method (or the JDataStore Explorer, see "Copying JDataStore streams") to copy the streams to another file.
TxManager
that has its enabled
property set to false. (It's default value is true.) If the DataStore's
consistent
property is false, the JDataStore is internally inconsistent and you won't be allowed to make the change. Because you are disabling the TxManager
, you don't need to set the userName
. This code removes transaction support:
DataStore store = new DataStore(); TxManager txMan = new TxManager(); // Disable TxManager txMan.setEnabled( false ); store.setFileName( "SomeFileName.jds" ); store.setTxManager( txMan ); store.open();Disabling the
TxManager
doesn't remove any existing log files. Disabling the TxManager
does cause all TxManager
properties to be forgotten. If you make the JDataStore transactional again, the TxManager
properties revert to their defaults, so
if the ALogDir
and BLogDir
properties were previously set to a non-default value, you will need to remember to set them again.
DSX: See "Removing transaction support."
TxManager
. The only time you need to reference a TxManager
is if you want to examine or change the JDataStore's transaction settings. The interface for controlling transactions is on the DataStoreConnection
object, primarily through the
commit()
and
rollback()
methods.
DataStoreConnection
is a separate
transaction context. This means that all the changes made through a particular DataStoreConnection
are treated as a group and separate from changes made through all others.
Note that as a subclass of DataStoreConnection
, a DataStore
object can also act as a separate transaction context. The difference is that you can have just one DataStore
object accessing a particular JDataStore file, while you can have many DataStoreConnection
objects. When you open a DataStoreConnection
, it contains a reference to a DataStore
object, as explained in "Referencing the connected JDataStore." If there is no suitable DataStore
object in memory, the DataStoreConnection
automatically opens a DataStore
to satisfy this reference. This means that if you open a DataStoreConnection
first, subsequent DataStore
objects accessing the same JDataStore file have their allowed functionality reduced so that they behave like a DataStoreConnection
.
A transaction's lifecycle begins with any read or write operation through a connection. The JDataStore uses stream locks to control access to resources. To read a stream or make a change to any part of a stream (a byte in a file, a row in a table), you must be able to acquire a lock on that stream. Once a connection acquires a lock, it holds on to it until the transaction is committed or rolled back.
In single-connection applications, transactions can be considered primarily as a feature that allows you to undo changes and provide crash recovery. Or you might have made a JDataStore transactional so that it can be accessed through JDBC. If you want to access that JDataStore using DataExpress, you must now deal with transactions. How transactions work has deeper ramifications for multi-connection (multi-user or single-user multi-session) applications. These are discussed in "Avoiding blocks and deadlocks," along with other multi-user issues in "Multi-user and remote access to JDataStores."
DataStoreConnection
:
transactionStarted()
.
commit()
.
rollback()
.
When you close DataStoreConnection
, it attempts to commit any pending transaction. You can control this automatic behavior by listening to the DataStore
's Response
event for a COMMIT_ON_CLOSE
, as shown in the following tutorial.
Basic.jds
. Instead of making that file transactional, make a copy of the file and make the copy transactional. This way, you have both kinds of JDataStore files, transactional and non-transactional, to play with.
Make a copy of the file, naming it Tx.jds
. Then add the following program to the project, MakeTx.java
:
// MakeTx.java package dsbasic; import com.borland.datastore.*; public class MakeTx { public static void main( String[] args ) { if ( args.length > 0 ) { DataStore store = new DataStore(); try { store.setFileName( args[0] ); store.setUserName( "MakeTx" ); store.setTxManager( new TxManager() ); store.open(); store.close(); } catch ( com.borland.dx.dataset.DataSetException dse ) { dse.printStackTrace(); } } } }This utility program makes any JDataStore file transactional if it isn't already. For JDataStores that are already transactional, nothing happens because no properties are set on the
TxManager
object.
Set the runtime parameters in the Project Properties dialog box to Tx.jds
and run the program. It takes a moment to create the three transaction log files Tx_STATUS_0000000000
, Tx_LOGA_ANCHOR
, and Tx_LOGA_0000000000
.
TableDataSet
:
In the Data Module Wizard, make sure the Package name is dsbasic
and set the Class name to AccountsDM
. Make sure the Invoke Data Modeler check box is not selected. Click OK.
Add a DataStore
component from the Data Express tab to the component tree. Change its name to dataStore
(easily done by pressing F2 after adding it to the tree).
In the Inspector, use the file chooser to set the fileName
property of the DataStore
to the Tx.jds
you created earlier, and set the userName
property to some name. (If you can't think of a name, how about Chuck
?)
Add a TableDataSet
component from the Data Express tab to the component tree.
In the Inspector, set the storeName
property to Accounts
and the store
property to dataStore
.
Save the file.
On page 1 of the Application wizard, set the Class name to AccountsApp
. Click Next.
On page 2 of the Application wizard, set the Frame Class name to AccountsFrame
, and the Title to Accounts
. Make sure the Center frame on screen check box is selected; deselect the rest. Click Finish.
The wizard should scan, find, and select the AccountsDM
data module. Set the Field name to dataModule
. Select the option to use a shared (static) instance. Click OK.
Add a JdbNavToolBar
component from the dbSwing tab to the North position of the frame.
Add a JdbStatusLabel
component from the dbSwing tab to the South position of the frame.
Add a TableScrollPane
component from the dbSwing tab to the Center position of the frame.
Add a JdbTable
component from the dbSwing tab to the TableScrollPane
.
In the Inspector, set the dataSet
property for all three Jdb
components to dataModule.TableDataSet1
(the only choice).
Go to the processWindowEvent()
method. This method is generated so that System.exit()
is called when the window is closed. It's important that you close the JDataStore before terminating the program.
In this case, with only one connection, the close()
method would work, but because you are calling System.exit()
, you want to make sure the JDataStore is closed, no matter how many connections the application is using. You should use DataStore
.shutdown()
in this situation, which
closes the JDataStore file directly. That is why this application uses a DataStore
instead of a DataStoreConnection
.
You could place the shutdown()
method call just before System.exit()
, but for reasons that you'll see soon, you want to do this before the window physically closes. Insert the highlighted statements:
//Overriden so we can exit on System Close protected void processWindowEvent(WindowEvent e) { if (e.getID() == WindowEvent.WINDOW_CLOSING) { try { dataStore.shutdown(); } catch ( DataSetException dse ) { dse.printStackTrace(); } } super.processWindowEvent(e); if (e.getID() == WindowEvent.WINDOW_CLOSING) { System.exit(0); } }
DataStore
object as dataStore
, which hasn't been defined. First, add the following import statements:
import com.borland.datastore.*; import com.borland.dx.dataset.*;
TableScrollPane tableScrollPane1 = new TableScrollPane(); JdbTable jdbTable1 = new JdbTable(); DataStore dataStore;
DataStore
from the data module. Add the highlighted statement to the jbInit()
method:
private void jbInit() throws Exception { dataModule = dsbasic.AccountsDM.getDataModule(); dataStore = dataModule.getDataStore();
Run AccountsApp.java
. You can navigate through the table and add, edit, and delete rows. All the changes you make are done within the context of a single transaction, although you can't tell at the moment. When you close the window, the JDataStore closes, and the changes you made are committed. You can verify this by running the application again.
If you didn't close the JDataStore (or at least commit the current transaction) before terminating the application, you would have an uncommitted transaction in the transaction log. As a result, the changes would have been orphaned and not written to the JDataStore. No changes you made in the application would ever apply. Closing the JDataStore commits those changes automatically.
Add a JPanel
component from the Swing Containers tab to the South position of the frame.
Add a JdbStatusLabel
component from the dbSwing tab to the JPanel
.
Set its dataSet
property to dataModule.TableDataSet1
(the only choice).
Add another JPanel
component from the Swing Containers tab to the first JPanel
. It should appear to the right of the JdbStatusLabel
.
Add a JButton
component from the Swing tab to the nested JPanel
.
Set its name to commitButton
and its text
property to Commit
.
Add another JButton
component from the Swing tab to the nested JPanel
.
Set its name to rollbackButton
and its text
property to Rollback
.
Set the actionPerfomed
event handler for the commitButton
to:
void commitButton_actionPerformed(ActionEvent e) { try { dataStore.commit(); } catch ( DataSetException dse ) { dse.printStackTrace(); } }
actionPerfomed
event handler for the commitButton
to:
void rollbackButton_actionPerformed(ActionEvent e) { try { dataStore.rollback(); } catch ( DataSetException dse ) { dse.printStackTrace(); } }
These buttons now call commit()
or rollback()
on the DataStore
to commit or roll back any changes made during the current transaction. The current transaction is all that happened since the last commit or rollback.
AccountsFrame.java
, modify the class definition so that it implements ResponseListener
:
public class AccountsFrame extends JFrame implements ResponseListener {
response
method for the ResponseListener
interface:
public void response( ResponseEvent response ) { if ( response.getCode() == ResponseEvent.COMMIT_ON_CLOSE ) { if ( JOptionPane.showConfirmDialog( this, "Posted changes have not been committed. Do that now?", "Commit or rollback", JOptionPane.YES_NO_OPTION ) == JOptionPane.YES_OPTION ) { response.ok(); } else { response.cancel(); } } }This method checks for the
COMMIT_ON_CLOSE
event. When that occurs, a simple yes/no dialog box appears, asking the user if they want to commit the changes. "Yes" sends the ok
response, which signals the JDataStore to commit the changes. "No" sends the cancel
response, which signals the JDataStore to roll back the changes.
ResponseListeners
:
private void jbInit() throws Exception { dataModule = dsbasic.AccountsDM.getDataModule(); dataStore = dataModule.getDataStore(); dataStore.addResponseListener( this );
With these additions, the user gets a dialog box if there are unsaved changes that asks if the user wants to commit them. Remember that the JDataStore is closed before the window is. If it's not, the dialog box would appear after the window had already disappeared.
You can now run the completed application. In addition to using the buttons to commit and roll back changes, try making some changes and then closing the window to exercise the auto-commit handling.
com.borland.datastore.jdbc.DataStoreDriver
.
You can use this driver for both local and remote access. Remote access requires a JDataStore Server, which is also used for multi-user access. For details on remote access and multi-user issues, see "Multi-user and remote access to JDataStores."
This is the local connection URL:
jdbc:borland:dslocal:<filename>
As with any JDBC driver, you can use the JDBC API or an added-value API such as DataExpress with QueryDataSet
and ProcedureDataSet
to access tables.
JdbcTable.java
, is functionally identical to its DataExpress twin, DxTable.java
. It uses the JDBC API.
// JdbcTable.java package dsbasic; import java.sql.*; public class JdbcTable { static final String DRIVER = "com.borland.datastore.jdbc.DataStoreDriver"; static final String URL = "jdbc:borland:dslocal:"; Connection con; Statement stmt; DatabaseMetaData dmd; ResultSet rs; PreparedStatement appendPStmt, getIdPStmt; public JdbcTable() { try { Class.forName( DRIVER ); con = DriverManager.getConnection( URL + "Tx.jds", "Chuck", "" ); stmt = con.createStatement(); dmd = con.getMetaData(); rs = dmd.getTables( null, null, "Accounts", null ); if ( !rs.next() ) { createTable(); } appendPStmt = con.prepareStatement("INSERT INTO \"Accounts\" VALUES" + "(?, ?, CURRENT_TIMESTAMP, NULL)" ); getIdPStmt = con.prepareStatement( "SELECT MAX(ID)FROM \"Accounts\""); } catch ( SQLException sqle ) { sqle.printStackTrace(); } catch ( ClassNotFoundException cnfe ) { cnfe.printStackTrace(); } } public void createTable() throws SQLException { stmt.executeUpdate( "CREATE TABLE \"Accounts\" (" + "ID INTEGER," + "\"Name\" VARCHAR," + "\"Update\" TIMESTAMP," + "\"Text\" BINARY)" ); } public void appendRow( String name ) throws SQLException { int newID; rs = getIdPStmt.executeQuery(); if ( rs.next() ) { newID = rs.getInt( 1 ) + 1; } else { newID = 1; } appendPStmt.setInt( 1, newID ); appendPStmt.setString( 2, name ); appendPStmt.executeUpdate(); } public void demo() { try { appendRow( "Rabbit season" ); appendRow( "Duck season" ); rs = stmt.executeQuery( "SELECT * FROM \"Accounts\"" ); while ( rs.next() ) { System.out.println( rs.getInt( "ID" ) + ": " + rs.getString( "Name" ) + ", " + rs.getTimestamp( "Update" ) ); } stmt.close(); con.close(); } catch ( SQLException sqle ) { sqle.printStackTrace(); } } public static void main( String[] args ) { new JdbcTable().demo(); } }This JDBC application uses two prepared statements: one to append rows, and the other to get the last ID value for that append. Initialize these prepared statements before calling the
appendRow()
method. A good place to do this is in the class constructor. Because the constructor is used, the organization of the code is a little different than in DxTable.java
.
The first thing that happens in the class constructor is the loading of the JDataStore JDBC driver using Class.forName
. Both the driver name and the beginning of the connection URL are defined as class variables for convenience. A Connection
to Tx.jds
is created, and from that, a generic Statement
.
The next step is to determine if the table exists. You can do this using through DatabaseMetaData.getTables()
. The code asks for a list of tables named "Accounts." If that list is empty, that means there is no such table and you must create it by calling the createTable()
method. The createTable()
method uses a SQL CREATE TABLE statement. Note that the SQL parser usually converts identifiers to uppercase. To keep the proper casing used by DxTable.java
, enclose the identifiers in quotes in this and other SQL statements. Finally, the two prepared statements are created.
The demo()
method calls appendRow()
to add a couple of test rows. As in DxTable.java
, the last/largest ID value is retrieved and incremented for the new row. But instead of using a sort order and going to the last row, the JDBC approach uses an SQL SELECT statement that fetches the maximum value. As a result, the empty table condition, when there is no last value, must be handled specifically.
Finally, the contents of the table are displayed using an SQL SELECT statement to fetch the rows and a loop that's very similar to the one in DxTable.java
. The statement and connection are closed as required by JDBC.
You can run this program interchangeably with DxTable.java
. Both of them add two more test rows to the same table.
DataStoreConnection
for the connection. That's how the JDataStore JDBC driver is implemented. But this internal object is not accessible, so you must use the JDBC API.
For control over transactions, disable auto-commit mode by calling Connection.setAutoCommit(false)
. You can then call commit()
and rollback()
on the Connection
object.
jpgpubs@inprise.com
Copyright © 2000, Inprise Corporation. All rights reserved.