Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Bronze

How to retrieved a date variable with DB GET ?

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 ???

1 ACCEPTED SOLUTION

Accepted Solutions
Community Member

Re: How to retrieved a date variable with DB GET ?

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.

6 REPLIES
Silver

Re: How to retrieved a date variable with DB GET ?

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

Bronze

Re: How to retrieved a date variable with DB GET ?

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.

Green

Re: How to retrieved a date variable with DB GET ?

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

Community Member

Re: How to retrieved a date variable with DB GET ?

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.

Community Member

Re: How to retrieved a date variable with DB GET ?

Hi Steven,

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

Thanks,

Mike

Community Member

Re: How to retrieved a date variable with DB GET ?

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

650
Views
20
Helpful
6
Replies
CreatePlease to create content