01-24-2009 07:57 AM - edited 03-14-2019 03:34 AM
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
Solved! Go to Solution.
01-24-2009 09:21 AM
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
01-24-2009 09:40 AM
This SQL link may also help you understand the SQL datetime opaque type.
http://www.karaszi.com/SQLServer/info_datetime.asp
Regards,
Geoff
01-24-2009 09:21 AM
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
01-24-2009 09:40 AM
This SQL link may also help you understand the SQL datetime opaque type.
http://www.karaszi.com/SQLServer/info_datetime.asp
Regards,
Geoff
01-24-2009 04:25 PM
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
01-25-2009 04:08 PM
Follow-up question. How do I get the datetime from the database? What java data type (for CRS) can accept it?
thx,
eddie
01-26-2009 07:46 AM
What are you trying to do? Read a SQL db from a Java class? inside a CRS script?
01-26-2009 11:00 AM
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
01-26-2009 11:23 AM
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.
01-26-2009 11:28 AM
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?
01-26-2009 11:34 AM
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.
01-26-2009 12:17 PM
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
01-26-2009 12:13 PM
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.
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide