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

• Statement
• ResultSet
• ResultSetMetaData
• Connection
• SQL package for other SQL-related objects

JSON

This page has moved.  Please update your links:
http://psst0101.digitaleagle.net/2009/03/25/json/

Here is another one of those technologies that I would love to play with but have to wait for a client that actually needs it: JSON.  I found Jim’s post on JSON very interesting and would love to have the need to come back to it.

One other solution that might be worth considering is this library for Java.  You would have to compile it and place it on the class path, but you could access it from PeopleCode.  Or, I wonder if it would be worth implementing the library in a PeopleCode Application Package?

And in conjunction, you can’t mention JSON, without mentioning jQuery.

JDBC From PeopleCode — Disadvantage/Advantage

This page has moved.  Please update your links:
http://psst0101.digitaleagle.net/2009/03/23/odbc-from-peoplecode-disadvantage/

Jim Marion’s post on JDBC made me think a little more.  (By the way, thanks, Jim, for linking me.)  The one disadvantage about accessing the PeopleSoft database via JDBC is that you have to supply the password to make the connection.

I have thought about trying to read the password from the application server or batch server configuration file, but if I remember right it only has the connect id and the connect password.  With the connect password, I might be able to use it to read the Access id and password from the database, but I have would have to be able to unencrypt it.

One advantage that SQL Server might have is that you can use Window’s security.  Assuming that the account running the application server or batch server has access to the database, you could just use the integrated security instead of an actual user name or password.

The advantage that JDBC has is that you don’t have to know the number of fields/columns that you want until run time.  With both the SQL object and SQLExec, you have to have a variable for each field you return or a return that contains all of those fields.

For example, I am trying to loop through a group of tables in a linked server and copy all of their fields to a table in the current database.  I have a problem using INSERT … SELECT, and I have to read the values in and then write them out.  I can’t figure another way to do it other than use JDBC.

Please correct me if I am wrong on any of this.  Maybe these thoughts will give someone else an idea that I overlooked.

Deleting Files from PeopleCode

This page has moved.  Please update your links:
http://psst0101.digitaleagle.net/2008/10/28/deleting-files-from-peoplecode

We have an interface file that we want to delete for security reasons once we have processed the file.  But, deleting it from the Application  Engine was not as straight-forward as I would have thought.

This does not work:

RemoveDirectory("c:\temp\mytextfile.txt", %FilePath_Absolute);

So, Java to the rescue — this does:

Local JavaObject &javaFile;

&javaFile = CreateJavaObject("java.io.File", "c:\temp\mytextfile.txt");
&javaFile.delete();

Resources

• JavaDocs for File
• JavaDocs for the delete() method
• RemoveDirectory function

Determine Last Update Date of a File

We have had trouble with our interface picking up the wrong file.  The client thinks they have put the file in the right place, but the server is looking at a different file for some reason.  Placing the date/time in the standard output helps identify these situations.  Here is how you can do that:

Local JavaObject &javaFile;
Local JavaObject &javaFormat;

&javaFile = CreateJavaObject(“java.io.File”, “c:\temp\myFile.txt”);
&javaFormat = CreateJavaObject(“java.text.SimpleDateFormat”, “yyyy/MM/dd hh:mm:ss aa”);
MessageBox(0, ” “, 0, 0, File Timestamp: %1”, &javaFormat.format(&javaFile.lastModified()));

Writing to Access Databases

We are thinking about connecting to an Access database for one of our interfaces.  Here is some PeopleCode that I threw together to make sure that it was possible.

I tested the code in a VMWare instance with HCM8.9/Tools 8.46, Windows Server 2003.  The instance did not have Microsoft Access installed, and I changed the path (c:\temp\AccessTest.mdb below) to a network (UNC) path where the computer had Access installed.

Local JavaObject &class;
Local JavaObject &connection;
Local JavaObject &driverManager;
Local JavaObject &statement;

&class = GetJavaClass("java.lang.Class");
&class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

&driverManager = GetJavaClass("java.sql.DriverManager");
&connection = &driverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\temp\AccessTest.mdb;DriverID=22;READONLY=false");
&statement = &connection.createStatement();
&statement.executeUpdate("insert into tblTest(TestMessage) values ('testing')");

Hope it helps.

Resources

• How to manipulate a Microsoft Access Database via JDBC
• Swing – Connecting to MS access using JDBC
• Java API Object in PeopleBooks