Strange problem with ? parameter substitution with ESQL/C application against 9.40

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: select * from tcusttxnsum where txn_date between ? and ? 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: select * from tcusttxnsum where txn_date >= ? and txn_date <= ? 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

On Tue, 05 Aug 2003 05:41:11 -0400, Chris Hall wrote: Looks like the engine is seeing the parameters as strings. If so you may have a conversion to DATETIME problem because the hour is "0" not "00". Try making it "2003-07-23 00" and see. Art S. Kagel > 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: > > select * from tcusttxnsum where txn_date between ? and ?> > 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: > > select * from tcusttxnsum where txn_date >= ? and txn_date <= ?> > 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