DB Read/Get Issues with SQL

ra_jeshkalra_2 · ‎10-29-2009

Hi,

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?

sferland · ‎10-29-2009

Hi,

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 · ‎10-29-2009

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 · ‎10-30-2009

Hi,

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.

Ex:

Before:

SELECT * FROM

WHERE ???

After:

SELECT * FROM WHERE ???

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 !

Steven

ra_jeshkalra_2 · ‎10-29-2009

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.

Thanks

Abdulbaseer Mohammed · ‎10-29-2009

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.

Baseer.

ra_jeshkalra_2 · ‎10-29-2009

Hi,

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?

Thanks

Abdulbaseer Mohammed · ‎10-29-2009

Rajesh,

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.

Baseer.