CRS Editor Date type - writing to SQL table

Answered Question
Jan 24th, 2009

Hello. Has anyone had to write a Date type to a SQL table? The Date type includes both the Date and Time. What SQL data type do I use? Or is this best handled by using strings?


I have tried writing Date type to SQL data types datetime, smalldatetime and timestamp with no joy. I have successfully written to int, char and varchar data types so there is something going wrong with the Date type write. Log shows standard SQL error message.



Thanks,

Eddie

Eddie, this is a standard Java/SQL question.


If you make the Jave Date class write the date and time as a string in exactly the correct form you can hand it to SQL and it will convert the string and store the value in a column whose type is SQL datetime.


Consider:


CREATE TABLE #dts(c1 char(1), dt datetime)

INSERT INTO #dts (c1, dt) VALUES('a', '20090124 08:43:31')


Look up on-line refs to T-SQL to ensure what I say is correct.


So all you need to do is create some Java code that will make a string like the above from your Date class. Attached is some verbose Java that you can make into a .class file and execute it at the command line to test the code. Once you are familiar with the solution, borrow the bits you need into the expression editor.


Regards,

Geoff



  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (3 ratings)
Loading.
Correct Answer

Eddie, this is a standard Java/SQL question.


If you make the Jave Date class write the date and time as a string in exactly the correct form you can hand it to SQL and it will convert the string and store the value in a column whose type is SQL datetime.


Consider:


CREATE TABLE #dts(c1 char(1), dt datetime)

INSERT INTO #dts (c1, dt) VALUES('a', '20090124 08:43:31')


Look up on-line refs to T-SQL to ensure what I say is correct.


So all you need to do is create some Java code that will make a string like the above from your Date class. Attached is some verbose Java that you can make into a .class file and execute it at the command line to test the code. Once you are familiar with the solution, borrow the bits you need into the expression editor.


Regards,

Geoff



edguidry Sat, 01/24/2009 - 16:25

Hello Geoff, thanks for the info!!! saved me a lot of time.


the Java string and the SQL table datetime type worked as you mentioned. SQL converts string to datetime. I put double quotes (ughh) but worked when I changed to single quotes.


INSERT INTO table (dt) VALUES('20090124 08:43:31')


Eddie

edguidry Sun, 01/25/2009 - 16:08

Follow-up question. How do I get the datetime from the database? What java data type (for CRS) can accept it?


thx,

eddie

edguidry Mon, 01/26/2009 - 11:00

Yes. Let's say I have a datetime field in a table. I would want to have a CRS script "get" this value, store it in a variable of type date or string. Then I would be able to use it in a script.


Thanks,

Eddie


It depends on what version and or features your CRS system is/has.

Does your version of CRS have Database dips?


I'm using CRS 3.5 Queue Manager; this is an old version with the least amount of features (no database features).

I had to write a java class to be able to connect to a DB and write to it or read from it.

I also had to write a class to convert date into string to write it to the DB.

edguidry Mon, 01/26/2009 - 11:28

Interesting. Using CRS 5.02 and database dips. I am able to write a date/time string variable directly to a datetime SQL type. SQL converts automatically. But yeah, I will need to keep digging on how to go the other direction. Java class type is interesting. That would be written in the Expression editor correct?

When you pull from the database you pass it through SQL CONVERT which returns a string and you specify how you want that string formatted, within limits.


Then you can use the Java string functions (like the Tokenizer) to split it up and rearrange things for the Date() constructor.


That link I posted is useful, and the T-SQL on-line page on CONVERT.


You can do all this in the Expression Editor.


Regards,

Geoff


edguidry Mon, 01/26/2009 - 12:13

I read this post by geoff:


understand the problem and have had this issue myself. Personally, I hardly ever use the DB steps to access a database, unless its something trivial for a demo or such.


You will have much more control by creating a custom Java class to do the SQL query and use the SQL CONVERT function to turn the SQL DateTime value into a string, and provide a method to fetch it from the class.


You can write the Java class(es) and write a test framework to test your database queries outside of the CRS engine. You can then have arbitrarily complex SQL queries, and not be limited by what the engine can deal with in the DB Get.


How are your Java coding skills?


Regards,

Geoff



In this case, my Java coding skills are poor. So in the interest of time. I will save it as a string and read it as a string; then convert in CRS script.

Actions

This Discussion