cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
767
Views
10
Helpful
9
Replies

Problem with DBRead in IVR

jefa
Level 1
Level 1

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

Hmm.

What if you write

SET QUOTED_IDENTIFIER OFF

before the SELECT statement?

View solution in original post

9 Replies 9

mstoychev
Level 1
Level 1

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.

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.

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?

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

Hmm.

What if you write

SET QUOTED_IDENTIFIER OFF

before the SELECT statement?

Yes, that is. Now, it works.

Thanks a lot for your help,

Jens & Juan Luis

Please rate my posts if you like em :-)

Thank you netbakter i gave you a 5.

Cheers! :-)

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: