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

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

Problem with DBRead in IVR

Hi,

I would like to execute this SQL command in a DBRead Step in CRS 4.0:

Select fld1, fld2 from tbl1 where fld1 = $var

If I execute the command

Select fld1, fld2 from tbl1 where fld1 = '1234' without use $var the result in number of rows returned is 1,

But if i use the string variable $var the number of rows = 0

I tried to assign the value "%1234%" to the variable $var, but it still returns 0.

Can anuone help me with this?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Problem with DBRead in IVR

Hmm.

What if you write

SET QUOTED_IDENTIFIER OFF

before the SELECT statement?

9 REPLIES
New Member

Re: Problem with DBRead in IVR

Why did you set the $var to '%1234%' ?

It shold be '1234' or not? Also be careful with variable types. The variable may have to be Integer or String depending on key. I did not use this step for a while, but I hope it gives you some ideas to try.

New Member

Re: Problem with DBRead in IVR

I assigned the variable $var the value "%1234%" because i found this old note in the forum:

"Posted by: astinus - Mar 9, 2005, 12:27pm PST

Hi, I just tried this out and it seems to work fine. What I did to simulate was this query:

SELECT * FROM table WHERE fld1 LIKE $strLikeTest

I setup the variable strLikeTest as a string type variable and I assigned the value of it as "%acct%" where acct is the specific string I wanted to match in the database. I tested it also with a string varialbe of "%1234%" and it worked ok against a SQL table with data type of int for the field being queried." END

This is the configuration i have:

SQL Database: fld1= char 4

Script Editor: GetNumber= "1234" (string)

The query i do is: select * from table where fld1 = $GetNumber

This was working fine in ivr 3.5, but after changing to ivr 4.0, using the same script and same database as i did for 3.5, it doesnt work anymore.

Re: Problem with DBRead in IVR

Hello,

% in an SQL query usually is the wildcard character. So if you use '%1234%' with

- column = '%1234%': this will return rows where column IS EXACTLY '%1234%',

- column LIKE '%1234%': this will return rows where column CONTAINS string '1234'

What kind of database is that? [assume MS SQL]

If you connect to the database and try to run the query in a sort of client tool [assume Query Analyzer], what do you get?

Gold

Re: Problem with DBRead in IVR

Hi,

We are using Microsoft SQL Server 2000 and we are using Cisco IP/IVR 4.0(2).

The problem is as follows:

We are trying to make this basic SQL Query:

SELECT * FROM table WHERE field1 = $var1

Where '$var' is an string type in IP/IVR. This query fails againts SQL Server with this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '22222222'

I know that the problem is with the single qoutation ' and double qoutation ". If I execute the Query in this way:

SELECT * FROM table WHERE field1 = '1111'

it works fine, but IP/IVR is passing to SQL Server this query if I use $var:

SELECT * FROM table WHERE field1 = "1111"

If I excute the above query in the Query Analizer I obtained the same error.

So, I would need to know how to send to SQL Server in '$var' the string '1111' instead of "1111".

Thanks in advance for your help,

Juan Luis

Re: Problem with DBRead in IVR

Hmm.

What if you write

SET QUOTED_IDENTIFIER OFF

before the SELECT statement?

Gold

Re: Problem with DBRead in IVR

Yes, that is. Now, it works.

Thanks a lot for your help,

Jens & Juan Luis

Re: Problem with DBRead in IVR

Please rate my posts if you like em :-)

New Member

Re: Problem with DBRead in IVR

Thank you netbakter i gave you a 5.

Re: Problem with DBRead in IVR

Cheers! :-)

146
Views
10
Helpful
9
Replies