How to retrieved a date variable with DB GET ?

[sferland]

Hi all,

I'm trying to retrieved a date from an SQL request.

First, I created a "Date" type variable in my script. Then I'm using a DB READ for my SQL statement. After, I put a DB GET and tried (without success) to map my variable to a "DateTime" field against the SQL table.

In the DB GET step, the data type of the field is "java.sql.Timestamp", which should be correctly map against a "Date" IPCC variable, right ? But, when I selected the date field in the top drop-down, there is no possible choice to select in the bottom drop-down.

Anybody ???

[ebreniz]

Use the DB Get step to assign the results of the Structured Query Language (SQL) query defined in the DB Read step to specific variables that you define using the Variable pane. Before you can use a DB Get step, you must use a DB Read step to define the SQL statements and identify the target database.

http://www.cisco.com/en/US/products/sw/custcosw/ps1846/products_programming_reference_guide_chapter09186a00800f0701.html#1030595

[sferland]

Thanks, but this is exactly what I did. My problem is that I can't assigned my "Date" variable to a "DateTime" type field into the DB GET step. The drop-down list is empty. My "Date" variable does not appear into it.

[geoff]

I 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

[swright · Accepted solution]

I did it by having a SQL view return the day, month, year, and time (in 24-hour fromat) as integers, and pulling those integers into my script as arguements in the "Create Generated Prompt - Date type" step to create a "TTS" Date/Time prompt. Let me know if you need more details.

[michaelmiu]

Hi Steven,

I'm running into the same thing. Were you able to find a workaround?

Thanks,

Mike

[jchandra]

You can create new object with java class type. You can type object selection -> java.sql.Timestamp