JDBC and SQL Server

This page has moved.  Please update your links:
http://psst0101.digitaleagle.net/2009/04/14/jdbc-and-sql-server/

Well, I have explored JDBC connections to Access in the past, but here is a connection to SQL Server via ODBC.

A driver for SQL Server exists, but using that driver requires adding a Jar file to the server.  Assuming the App Server or Batch Server is running on Windows, you have to have an ODBC connection setup already.

Here is the code; hope it helps:

Local SQL &sql;
Local string &loadCommand;

/* Using JDBC */
/* Load the driver */
GetJavaClass(“java.lang.Class”).forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Local string &connectionUrl = “jdbc:odbc:” | %DbName | “;user=MY_ADMIN;passwordMY_PASSWORD;”;
Local JavaObject &conn = GetJavaClass(“java.sql.DriverManager”).getConnection(&connectionUrl);
Local JavaObject &connSource = GetJavaClass(“java.sql.DriverManager”).getConnection(&connectionUrl);
try
&conn.setCatalog(“MY_DB”);
catch Exception &changeCatalogWarning
end-try;
Local JavaObject &stTarget = &conn.createStatement(GetJavaClass(“java.sql.ResultSet”).TYPE_SCROLL_INSENSITIVE, GetJavaClass(“java.sql.ResultSet”).CONCUR_UPDATABLE);
Local JavaObject &stSource = &connSource.createStatement(GetJavaClass(“java.sql.ResultSet”).TYPE_FORWARD_ONLY, GetJavaClass(“java.sql.ResultSet”).CONCUR_READ_ONLY);
Local JavaObject &rsTarget;
Local JavaObject &rsSource;
Local number &rowsLoaded;
Local number &rowsUntilCommit;
Local JavaObject &metaData;
Local number &columnIndex;

&loadCommand = “”;
&loadCommand = &loadCommand | “SELECT top 50 * “;
&loadCommand = &loadCommand | “FROM OPENQUERY([MY_LINKED_SERVER], “;
&loadCommand = &loadCommand | “‘SELECT * FROM MY_REMOTE_TABLE’)”;
&rsSource = &stSource.executeQuery(&loadCommand);
&loadCommand = “”;
&loadCommand = &loadCommand | “SELECT * “;
&loadCommand = &loadCommand | “FROM [TARGET_TABLE]”;
&rsTarget = &stTarget.executeQuery(&loadCommand);
&rowsLoaded = 0;
&rowsUntilCommit = 50;
&metaData = &rsSource.getMetaData();
While &rsSource.next()
GetJavaClass(“java.lang.Thread”).yield();
&rsTarget.moveToInsertRow();
For &columnIndex = 1 To &metaData.getColumnCount();
Evaluate &metaData.getColumnType(&columnIndex)
When = – 6
rem &rsTarget.updateInt(&columnIndex, &rsSource.getInt(&columnIndex));
&rsTarget.updateInt(&columnIndex, CreateJavaObject(“java.lang.Integer”, 0).intValue());
When = 4
rem &rsTarget.updateInt(&columnIndex, &rsSource.getInt(&columnIndex));
&rsTarget.updateInt(&columnIndex, CreateJavaObject(“java.lang.Integer”, 0).intValue());
When = 6
rem &rsTarget.updateFloat(&columnIndex, &rsSource.getFloat(&columnIndex));
&rsTarget.updateFloat(&columnIndex, CreateJavaObject(“java.lang.Float”, ” “).floatValue());
When = 12
rem &rsTarget.updateString(&columnIndex, &rsSource.getString(&columnIndex));
&rsTarget.updateString(&columnIndex, CreateJavaObject(“java.lang.String”));
When = 93
rem &rsTarget.updateTimestamp(&columnIndex, &rsSource.getTimestamp(&columnIndex));
&rsTarget.updateTimestamp(&columnIndex, CreateJavaObject(“java.sql.Timestamp”, CreateJavaObject(“java.util.Date”).getTime()));
When-Other
rem &rsTarget.updateObject(&columnIndex, &rsSource.getObject(&columnIndex));
Error (“Unknown column type: ” | &metaData.getColumnType(&columnIndex) | ” — ” | &metaData.getColumnTypeName(&columnIndex) | ” for field ” | &metaData.getColumnName(&columnIndex));
End-Evaluate;
End-For;
&rsTarget.insertRow();
&rowsLoaded = &rowsLoaded + 1;
&rowsUntilCommit = &rowsUntilCommit – 1;
If &rowsUntilCommit <= 0 Then;
&conn.commit();
CommitWork();
&rowsUntilCommit = 50;
End-If;
End-While;
&rsSource.close();
&rsTarget.close();
&conn.commit();
CommitWork();
End-While;

Resources

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: