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";
password="MY_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

2 thoughts on “JDBC and SQL Server

  1. this article helped me a lot. there is a typo
    orginal = passwordMY_PASSWORD
    corrected = password=MY_PASSWORD
    in the Local string &connectionUrl statement

Leave a Reply to ed liao Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.