Problem with DBRead in IVR

[jefa]

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?

[mstoychev]

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.

[jefa]

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.

[Gergely Szabo]

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?

[lpezj]

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

[Gergely Szabo · Accepted solution]

Hmm.

What if you write

SET QUOTED_IDENTIFIER OFF

before the SELECT statement?

[lpezj]

Yes, that is. Now, it works.

Thanks a lot for your help,

Jens & Juan Luis

[Gergely Szabo]

Please rate my posts if you like em :-)

[jefa]

Thank you netbakter i gave you a 5.

[Gergely Szabo]

Cheers! :-)