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;
this article helped me a lot. there is a typo
orginal = passwordMY_PASSWORD
corrected = password=MY_PASSWORD
in the Local string &connectionUrl statement
ed liao,
Thanks for pointing that out. I fixed that, and I went back and tried to get the formatting that.