Tutorial for JDBC module

New Transport:

Source:

 In the dialog "Choose Data Store" select "JDBC" and the following window appears:
 
 

First enter the name of the driver class for your database. In this example, for Sybase, the driver's name is "com.sybase.jdbc2.jdbc.SybDriver". For other driver name examples click the "Notes" button. The driver name is specific to your database and can be found in its documentation. It is the Java class name for the particular JDBC driver. Drivers for Sybase, Oracle and OpenBase come preinstalled with SRTransport.  You must also have the .jar, .zip or .class file installed on your system and it must be in your Java CLASSPATH.

Next enter the URL for the database connection. Again this is specific to your database and can be found in its documentation. Our example connects to a Sybase database named "alexis" listening on port 5000 - jdbc:sybase:Tds:alexis:5000 - again you must find the format for this URL in your JDBC Driver documentation. (The "Notes" button gives example for Sybase, Oracle and OpenBase).

Enter your Log In name and password and click Set.

SRTransport will then try to connect to your data base. If all goes well you will be presented with several dialogs asking about additional connection information such as - database name - user - schema - or other possibilities,  depending upon your database.  You will also chose a specific Table for your source. In this case Movies.

You will now return to the Transport window and will be able to see the source columns for your chosen table. Now is a good time to save your transport. SRTransport automatically appends a .transport extension to the file name.
 

Double click on the JDBC Icon in the source well and you can view the source table information in detail. Things like primary key, whether nulls are allowed, the database's type for the column and so forth. The "where" box allows you to add a qualifying expression which will determine what rows are read from the database when a transport occurs.( see the destination window).

Choose "Preview Source" from the "Transport" menu to see what will be read from the database.
 

Destination:

First the destination table should already exist in your data base. SRTransport's JDBC Module won't create it for you. (Note that some SRTransport modules will.) Now double click on the destination well or select "Choose Destination" from the "Transport" menu. Choose JDBC in the dialog that appears. Now you will set up the connection to a destination table in OpenBase. The process is the same as for the source table. The JDBC Driver for OpenBase is named "com.openbase.jdbc.ObDriver". The URL looks different than Sybase - "jdbc:openbase://127.0.0.1/WOMovies". You're connecting to the database WOMovies on local host. Again click "Set" and follow the dialogs to set up the connection choosing MOVIE as the table.

You'll be back at the Transport window and the JDBC icon will appear in the destination well. Save your efforts, double click on the destination and you'll see the configuration window. Here you can do a great deal to customize the movement of the the data from source to destination.

First notice the set of radio buttons called "Mode". The default setting is "Insert". Running a transport with this button selected will cause SRTransport to try to insert a new row into the database. The other modes will attempt do do exactly what they say.

Next notice the check boxes in the table labeled P and I. The P indicates what the primary key(s) are for the table (or what the database considers unique). The I column is selectable and tells the SRTransport how to identify rows for updates and deletes. Usually you can use the primary key, but other choices may be appropriate. Columns with I selected become part of the where clause. UPDATE 'x' WHERE 'I' = 'y'.

Also important are the "Nulls overwrite" check box and the U column in the table.  When checked "Nulls overwrite" causes SRTransport to write null values into the database during updates.  This would possibly replace non null data. When the U is checked for a column SRTransport will never update that column.

Close this window and you're now ready to map the source columns to the destination columns.
 

Expressions:

The simplest way to do this is to start with "Match By Name" from the "Transport" Menu.  SRTransport will match all columns that have the same names in the source and the destination. You can see in the middle of the transport under "Value Expression" just how those map.

You can also match things by selecting a destination column and then double click a source column - the names do not need to match. SRTransport will try to convert different data types if those don't match. The expression are also a place to customize how the data are changed on the way from source to destination. For example you can concatenate 2 strings from different columns in the source into one column in the destination FIRST_NAME + " " + LAST_NAME (You need to add a space in between). You can also do things like upper(ADDRESS) to change a string to all uppercase. All the functions are in the "Transport - Functions" menu. Detailed documentation is available at Schema's Web site. You may also specify constants or other data that don't appear in the source.
 

Transport:

Now you are ready to test the transport and move some rows of data. Click on the transport arrow between the source and destination wells (or choose "Transport Rows" from the "Transport" menu.)

You are in the "Transporter Room". Click "Trace Records" and hit the "Trial Ten" button to see what will be transported or if there are any errors in your configuration. If that works you can now click the "Run" button and the transport will take place.

You can use SRBrowse to look at the data that has been moved into the destination table.