DB Read/Get Issues with SQL

Unanswered Question
Oct 29th, 2009


I am having issues with DB Get. Even after assigning the local variable in CRS script as "Date" type, I am unable to select it as a local variable in the "Add/Modify Button under the Filed Selection TAB.

The Filed type for this in SQL DB is DateTime.

Attached is the screenshot.

Can somebody suggest what is the issue?

I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
sferland Thu, 10/29/2009 - 06:13


I ran into this situation before. The only way I could work around it, was to create a view in SQL, which takes the date and convert it to a String (ex: 20091029). So, instead of doing the request against the table, I'm doing it against the view.

After that, I'm able to retrieve the date as a String and convert it back to a date using the following Java code into a "SET" step:

Assume that you have a variable named "nDateFromSQL" of type String


SimpleDateFormat lFormatter = new SimpleDateFormat("yyyyMMdd");

nTrandate = lFormatter.parse(nDateFromSQL);


I hope that help !

ra_jeshkalra_2 Thu, 10/29/2009 - 12:55

Hi Sferland,

Thanks. I don't know Java and only little SQL.

The above Java code ?

1.Will the final query in DB Read look like this:

Select { your suggested Java code above) from

2.I have other complex queries as well, are the above type of problems explained in any of the IPCCX7.x document/guide?

sferland Fri, 10/30/2009 - 07:41


Your query in "DB Read" should look exactly the same as today. Instead of doing the query on the table you're doing it on the view.







Then, in your ""DB Get", you retrieve a String variable for your date.

After that, with a "SET" step, you place the Java Code posted earlier and assign the step to your real Date variable.

Should do the job !


ra_jeshkalra_2 Thu, 10/29/2009 - 22:44

ok, thanks.

I am also facing an issue for e.g. the SQL filed type is smallint, the ivr variable I declare is of type int.

But when I do a DB get, the control reaches the SQL error branch.

I am not sure why is this so,,,

In the DBGET, the DB Get function declared desclared the data type as java.lang.integer.


Abdulbaseer Mohammed Thu, 10/29/2009 - 23:06

What exactly are you trying to accomplish?

I usually stay away from touching the DB direct. Can you ask your DB team if they can have a webservice its lot easier cleaner and saves you lot of pain in long run.

good luck.


ra_jeshkalra_2 Thu, 10/29/2009 - 23:18


I am trying to read the value from customer SQL 2005 DB from another server in IVR local variables, which I have to announce to caller using create generated prompt. Hence I do DB Read first followed by DB Get.

Hence facing these issues. Can you help to suggest how to resolve.

Also can you elaborate about a web service, If I have to use this, what the customer has to do?


Abdulbaseer Mohammed Thu, 10/29/2009 - 23:26


Your customer probably has this if not they have to setup a web page where you can go to the url and type post or get your desired input and output. Store this output to variable then announce it .

this can be done using getxml document etc.

Good luck.



This Discussion