We recently ran into this type of behavior as well with ESQL/COBOL (please
don't ask). We were given 163480 as the bug number and it was introduced
with the 9.4 engine so you may want to call Informix Tech Support.



"Chris"
<chris.hallorbis To: [email]informix-listiiug.org[/email]
uk.com> cc:
Sent by: Subject: Re: Strange problem with ? parameter substitution
owner-informix-li with ESQL/C application against 9.40
[email]stiiug.org[/email]


08/05/2003 02:05
PM
Please respond to
"Chris"






On Tue, 05 Aug 2003 19:41:54 +0100, Paul Watson wrote:
> This is a guess but I'd expect the missing 0 on first date
> is the problem, datetime as you know is excessively picky. Personnally
> I wouldn't have expected it work under normal SQL/dbaccess but it has
> got less pedantic over the years.
Paul, it makes no difference. The point is that this fails:

select * from tcusttxnsum where txn_date between ? and ?

while this works:

select * from tcusttxnsum where txn_date >= ? and txn_date
<= ?

when prepared and then executred with exactly the same string parameters,
by exactly the same code. Both forms are fine on 7.31, 9.30, so I'm
guessing that someone's messed up 9.40...

Chris

> Chris Hall wrote:
>>
>> Really scratching my head with this one - any insight would be most
>> appreciated.
>>
>> I have the following table:
>>
>> > info columns for tcusttxnsum;
>>
>> Column name Type Nulls
>>
>> sum_id serial no
>> txn_date datetime year to hour yes
>> acct_id integer yes
>> amount decimal(12,2) yes
>> j_op_type char(4) yes
>>
>>
>> And the following query, which is prepared using a Tcl script which
>> provides an interface to an ESQL/C library:
>>
>>
>>
>> When executed with parameter values of "2003-07-23 0" and "2003-07-23
>> 23", the server returns the following error:
>>
>> (-1268) Invalid datetime or interval qualifier
>>
>> Exactly the same query, prepared by exactly the same code works
>> perfectly against a 7.31 (both 32- and 64-bit) and a 9.30 (32-bit)
>> server. Bizarrely, if the query is re-phrased like this:
>>
>>
>>
>> and the same parameters ("2003-07-23 0" and "2003-07-23 23") are
>> passed, then there are no errors. I'd like to think that this shows
>> that it's not our code which is at fault (it's been in use for several
>> years without any other problems like this). Also it's not a problem
>> with datetime year to hour specifically: datetime year to second fails
>> in the same way. We pass all parameters as ESQL/C "CSTRINGTYPE" values
>> to the server, in all cases.
>>
>> 9.40 shows a bit more than previous versions when you use "onstat -g
>> sql" - the relevant output is below... in the first case the server
>> seems to be saying that it's getting CHAR parameters, in the second
>> DTIME parameters...
>>
>> FIRST QUERY:
>>
>> % inf_prep_sql conn0 {
>> select * from tcusttxnsum where txn_date between ? and ?
>> }
>> % inf_exec_stmt stmt0 {2003-07-23 0} {2003-07-23 23}
>> inf_exec_stmt: (-1268) Invalid datetime or interval qualifier.
>>
>> [oberon_940 21]--> onstat -g sql 30
>>
>> Informix Dynamic Server Version 9.40.FC1 -- On-Line -- Up 42 days
>> 02:59:22 -- 813056 Kbytes
>>
>> Sess SQL Current Iso Lock SQL ISAM F.E.
>> Id Stmt type Database Lvl Mode ERR ERR Vers
>> Explain
>>
>> 30 SELECT ws NL Not Wait -1268 0 9.03
>> Off
>>
>>
>> Current statement name : uid4
>>
>> Current SQL statement :
>> select * from tcusttxnsum where txn_date between ? and ?
>>
>> Host variables :
>> address type flags value
>> -----------------------------------------
>> 0x000000012d118838 CHAR 0x002 2003-07-23 0
>> 0x000000012d1188c8 CHAR 0x000 2003-07-23 23
>>
>>
>>
>> SECOND QUERY:
>>
>> inf_prep_sql conn0 {
>> select * from tcusttxnsum where txn_date >= ? and txn_date <=
>> ?
>> }
>> stmt1
>> % inf_exec_stmt stmt1 {2003-07-23 0} {2003-07-23 23}
>> res0
>>
>> [oberon_940 23]--> onstat -g sql 30
>>
>> Informix Dynamic Server Version 9.40.FC1 -- On-Line -- Up 42 days
>> 03:01:14 -- 813056 Kbytes
>>
>> Sess SQL Current Iso Lock SQL ISAM F.E.
>> Id Stmt type Database Lvl Mode ERR ERR Vers
>> Explain
>>
>> 30 SELECT ws NL Not Wait 0 0 9.03
>> Off
>>
>>
>> Current statement name : uid5
>>
>> Current SQL statement :
>> select * from tcusttxnsum where txn_date >= ? and txn_date <= ?
>>
>> Host variables :
>> address type flags value
>> -----------------------------------------
>> 0x000000012d0dd838 DTIME 0x000 2003-07-23 00
>> 0x000000012d0dd8c8 DTIME 0x000 2003-07-23 23





sending to informix-list