Problem with DBRead in IVR

Answered Question
Mar 14th, 2007
User Badges:

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?

Correct Answer by Gergely Szabo about 10 years 2 months ago

Hmm.

What if you write


SET QUOTED_IDENTIFIER OFF


before the SELECT statement?



  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (3 ratings)
Loading.
mstoychev Wed, 03/14/2007 - 14:12
User Badges:

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 Thu, 03/15/2007 - 03:47
User Badges:

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 Thu, 03/15/2007 - 11:57
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

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 Fri, 03/16/2007 - 01:25
User Badges:
  • Gold, 750 points or more

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



Correct Answer
Gergely Szabo Fri, 03/16/2007 - 01:57
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

Hmm.

What if you write


SET QUOTED_IDENTIFIER OFF


before the SELECT statement?



lpezj Fri, 03/16/2007 - 02:21
User Badges:
  • Gold, 750 points or more

Yes, that is. Now, it works.


Thanks a lot for your help,


Jens & Juan Luis

Gergely Szabo Fri, 03/16/2007 - 02:25
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

Please rate my posts if you like em :-)

jefa Fri, 03/16/2007 - 04:05
User Badges:

Thank you netbakter i gave you a 5.

Gergely Szabo Fri, 03/16/2007 - 05:23
User Badges:
  • Green, 3000 points or more
  • Community Spotlight Award,

    Member's Choice, December 2015

Cheers! :-)

Actions

This Discussion