"unknown sql type" with parameterized query in 3.5

Not sure if this is a known bug or I'm missing something simple but thought I'd check here just in case - I found similiar cases (though not specificly like this) but were only older versions. We're running 3.5 TC9 .NET driver here and I'm seeing something odd with parameterized queries. Here's a simple example - "SELECT * from vw_subscriber where Alias=? AND DTMFAccessID=? AND ConversationName=?" and I of course add the 3 strings as varchar parameters as you'd expect and it all works dandy. However, if I change the query to include the fn_tolower function like this: "SELECT * from vw_subscriber where fn_tolower(Alias)=? AND DTMFAccessID=? AND ConversationName=?" with the same exact parameter construction I get the "Unknown SQL type - 0" message. I don't see anything in the .NET PDF or the like - is there something I'm missing or is this a bug in the driver? thanks -Jeff

I do not see what version of the engine you are using. I am not familiar with the function fn_tolower() function. What data types does this function return? I am wondering if the engine is converting both side to an lvarchar? Might try and explicitly setting the function to a varchar "SELECT * from vw_subscriber where fn_tolower(Alias)::varchar(250)=? AND DTMFAccessID=? AND ConversationName=?" John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) (Embedded image moved to file: pic02026.gif) ids-bounces@iiug.org wrote on 03/23/2012 02:29:46 PM: > From: "JEFF LINDBORG" <lindborg@cisco.com> > To: ids@iiug.org > Date: 03/23/2012 02:31 PM > Subject: "unknown sql type" with parameterized query in 3.5 [26567] > Sent by: ids-bounces@iiug.org > > Not sure if this is a known bug or I'm missing something simple but thought > I'd check here just in case - I found similiar cases (though not specificly > like this) but were only older versions. We're running 3.5 TC9 .NET driver > here and I'm seeing something odd with parameterized queries. Here'sa simple > example - > > "SELECT * from vw_subscriber where Alias=? AND DTMFAccessID=? AND > ConversationName=?" > > and I of course add the 3 strings as varchar parameters as you'd > expect and it > all works dandy. However, if I change the query to include the fn_tolower > function like this: > > "SELECT * from vw_subscriber where fn_tolower(Alias)=? AND DTMFAccessID=? AND > ConversationName=?" > > with the same exact parameter construction I get the "Unknown SQL type - 0" > message. > > I don't see anything in the .NET PDF or the like - is there something I'm > missing or is this a bug in the driver? > thanks > -Jeff > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

very cool - I had not thought of that - changing the query string to look like this: "SELECT * from vw_subscriber where fn_tolower(Alias)::varchar(64)=? AND DTMFAccessID=? AND ConversationName=?" works just fine - it was making me sad to think about having to do inline query construction for anything having to use that function (for case insensitve string searches on name fields mostly). thanks much. -J

FYI, in 11.70 there is an ONCONFIG parameter that makes NCHAR and NVCHAR columns case insensitive for searching! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sat, Mar 24, 2012 at 12:54 PM, JEFF LINDBORG <lindborg@cisco.com> wrote: > very cool - I had not thought of that - changing the query string to look > like > this: > > "SELECT * from vw_subscriber where fn_tolower(Alias)::varchar(64)=? AND > DTMFAccessID=? AND ConversationName=?" > > works just fine - it was making me sad to think about having to do inline > query construction for anything having to use that function (for case > insensitve string searches on name fields mostly). > > thanks much. > > -J > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f2351df966f0904bc066917

If you are trying to do case insenitive searches then you can do the following in version 11.70. 1. create database db1 with log NLSCASE INSENSITIVE; 2. create table tab1 (c1 serial, c2 nvarchar(200)); 3. insert into tab1 values (0,"JOHN"); 4. insert into tab1 values (0."john"); 5. select * from tab1 where c2="JOHN"; returns c1 1 c2 JOHN c1 2 c2 john 2 row(s) retrieved. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) (Embedded image moved to file: pic30942.gif) ids-bounces@iiug.org wrote on 03/24/2012 09:54:43 AM: > From: "JEFF LINDBORG" <lindborg@cisco.com> > To: ids@iiug.org > Date: 03/24/2012 09:56 AM > Subject: Re: "unknown sql type" with parameterized query in [26570] > Sent by: ids-bounces@iiug.org > > very cool - I had not thought of that - changing the query string tolook like > this: > > "SELECT * from vw_subscriber where fn_tolower(Alias)::varchar(64)=? AND > DTMFAccessID=? AND ConversationName=?" > > works just fine - it was making me sad to think about having to do inline > query construction for anything having to use that function (for case > insensitve string searches on name fields mostly). > > thanks much. > > -J > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >