cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
1235
Views
5
Helpful
11
Replies

CRS Editor Date type - writing to SQL table

edguidry
Level 1
Level 1

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

2 Accepted Solutions

Accepted Solutions

geoff
Level 10
Level 10

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

View solution in original post

This SQL link may also help you understand the SQL datetime opaque type.

http://www.karaszi.com/SQLServer/info_datetime.asp

Regards,

Geoff

View solution in original post

11 Replies 11

geoff
Level 10
Level 10

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

This SQL link may also help you understand the SQL datetime opaque type.

http://www.karaszi.com/SQLServer/info_datetime.asp

Regards,

Geoff

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

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

thx,

eddie

What are you trying to do? Read a SQL db from a Java class? inside a CRS script?

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.

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?

If you can write to it then you can read from it.

No, outside the editor. I had to write an independent class and then import it into the server.

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

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.

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: