Teamworks 6.2.1 - Inserting TWDate into Oracle 10g Timestamp Column

I've recently moved away from the "SQL Execute Statement" services in teamworks in favor of the "SQL Call Statement" versions that use the SQLStatement datatype for input and SQLResult type for output. This way, all SQL calls are formatted using the same SQLStatement type and all results returned in the same standard catch all SQLResults complex type. Even better, using the SQLStatement type encourages the use of SQL parameters ... which helps with query plan caching in oracle, avoids a lot of string parsing issues with appending variables directly to the SQL execute string, and also helps prevent SQL injection.



Anyhow, I ran into an issue with passing TWDate values into the teamworks "SQL Call Statement" service as a type TIMESTAMP SQL parameter. I still dont know if this is an oracle issue or a JDBC issue...or if it is some other timezone/locale thing. Whatever the case, I ended up string formatting the TWDate value into something suitable for JDBC to plug into Oracle as a TIMESTAMP column type. Below is an example teamworks server script that shows how it all works, assuming the oracle table has two columns: ID of type INT and CreateDate of type TIMESTAMP.

tw.local.dsn = "jdbc/mydsn";
tw.local.thisDate = new TWDate();
tw.local.sqlStatement = new tw.object.SQLStatement();
tw.local.sqlStatement.sql = "INSERT INTO TestTimestampeTable (ID,CreateDate)VALUES(?,?)";
tw.local.sqlStatement.parameters = new tw.object.arrayOf.SQLParameter();
tw.local.sqlStatement.parameters[0] = new tw.object.SQLParameter();
tw.local.sqlStatement.parameters[0].value = 1;
tw.local.sqlStatement.parameters[0].type = "INTEGER";
tw.local.sqlStatement.parameters[1] = new tw.object.SQLParameter();
tw.local.sqlStatement.parameters[1].value = tw.local.thisDate.format('yyyy-MM-dd H:mm:ss.SSS');
tw.local.sqlStatement.parameters[1].type = "TIMESTAMP";
tw.local.sqlStatement.maxRows = 0;

Once you have the sqlStatement set, all you have to do is map variables to the service, similar to that shown below.




- ... and just a few minutes later... -

OR! you can apparently just shove the TWDate object right into the SQL parameter value and it just magically converts to the right format for you. (it also helps to be using the right oracle query when verifying data was inserted properly into oracle x.x; ). Here's the simpler method ... just change the server script to this:

tw.local.dsn = "jdbc/mydsn";
tw.local.thisDate = new TWDate();
tw.local.sqlStatement = new tw.object.SQLStatement();
tw.local.sqlStatement.sql = "INSERT INTO TestTimestampeTable (ID,CreateDate)VALUES(?,?)";
tw.local.sqlStatement.parameters = new tw.object.arrayOf.SQLParameter();
tw.local.sqlStatement.parameters[0] = new tw.object.SQLParameter();
tw.local.sqlStatement.parameters[0].value = 1;
tw.local.sqlStatement.parameters[0].type = "INTEGER";
tw.local.sqlStatement.parameters[1] = new tw.object.SQLParameter();
tw.local.sqlStatement.parameters[1].value = tw.local.thisDate;
tw.local.sqlStatement.parameters[1].type = "TIMESTAMP";
tw.local.sqlStatement.maxRows = 0;

To validate from oracle, you'll want to run a query like this from SQL plus:

SELECT ID,TO_CHAR(createDate,'mm/dd/yyyy HH:MI:SS.FF PM') FROM TestTimestampeTable;