Oninit cdi / Archive
Talk to an expert →

Too many or too few host variables given

Posted in 2004

Summary

A user running MS Access 97 (DAO/ODBC) on Windows 2000 against Informix 9.40 found that a UNION of two GROUP BY/HAVING queries, each with a bound parameter, failed with "ODBC Call Failed" and SQL error -254 ("Too many or too few host variables given") at SQLExecDirect. The failure occurred only with Informix client/ODBC driver versions 3.81 and 3.82; older clients (3.32/3.33) worked. Paul Watson speculated about a casting issue newer drivers no longer tolerate and asked to see the generated SQL, which the poster supplied (two '?' placeholders in a UNION over varchar columns). No fix, workaround, or confirmed cause is recorded in the thread.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: SQL Development & Query Writing, Connectivity: ODBC / JDBC / .NET, Data Types & Schema Design

Maks Romih — — source: Usenet: comp.databases.informix

I'm working in MS Access97, DAO and ODBC on several Windows 2000 machines with various Informix clients, versions 3.32, 3.33, 3.81, 3.82. Server is Informix 9.40 on Windows 2000. I have pretty simple union of two simple GROUP-BY querys with a parameter that breaks with "ODBC Call Failed", but only on newer versions of Informix clients, that is 3.81 and 3.82. Looking into the trace I can see the error message "Too many or too few host variables given. (-254)". Is this a bug in the drivers or some compatibility mismatch between Jet, ODBC driver, ODBC manager and Informix server? Does anybody have a suggestion, how to circumvent the problem or which combination of ODBC, Informix client and Access would work better? Below I include the three Access querys and a part of the trace. The table T1 can be created with CREATE TABLE T1( f1 varchar(2), f2 varhcar(2)) and linked from Access. The problem shows up, if you have Informix client 3.82 and try to open the query Tq3. Maks. -------------------------------------------------------- Tq1 : SELECT T1.f1, Count(*) AS Expr1 FROM T1 GROUP BY T1.f1 HAVING (((T1.f1)=[a_pri])); -------------------------------------------------------- Tq2 : SELECT T1.f2, Count(*) AS Expr1 FROM T1 GROUP BY T1.f2 HAVING (((T1.f2)=[a_pri])); -------------------------------------------------------- Tq3 : SELECT * from Tq1 UNION select * from Tq2; -------------------------------------------------------- Excerpt from SQL.LOG, generated by Tracing option in ODBC Administrator : ... KRD_AP~1 760-c44 ENTER SQLAllocStmt HDBC 027215E8 HSTMT * 02CE1564 KRD_AP~1 760-c44 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) HDBC 027215E8 HSTMT * 0x02CE1564 ( 0x027229a8) KRD_AP~1 760-c44 ENTER SQLGetStmtOption HSTMT 027229A8 UWORD 0 PTR 0x0013DC20 KRD_AP~1 760-c44 EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS) HSTMT 027229A8 UWORD 0 PTR 0x0013DC20 KRD_AP~1 760-c44 ENTER SQLSetStmtOption HSTMT 027229A8 UWORD 0 <SQL_QUERY_TIMEOUT> SQLPOINTER 0x0000003C KRD_AP~1 760-c44 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS) HSTMT 027229A8 UWORD 0 <SQL_QUERY_TIMEOUT> SQLPOINTER 0x0000003C (BADMEM) KRD_AP~1 760-c44 ENTER SQLBindParameter HSTMT 027229A8 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 1 <SQL_C_CHAR> SWORD 12 <SQL_VARCHAR> SQLULEN 255 SWORD 0 PTR 0x02CE1A34 SQLLEN 0 SQLLEN * 0x02CE1A30 KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) HSTMT 027229A8 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 1 <SQL_C_CHAR> SWORD 12 <SQL_VARCHAR> SQLULEN 255 SWORD 0 PTR 0x02CE1A34 SQLLEN 0 SQLLEN * 0x02CE1A30 (1) KRD_AP~1 760-c44 ENTER SQLBindParameter HSTMT 027229A8 UWORD 2 SWORD 1 <SQL_PARAM_INPUT> SWORD 1 <SQL_C_CHAR> SWORD 12 <SQL_VARCHAR> SQLULEN 255 SWORD 0 PTR 0x02CE1A39 SQLLEN 0 SQLLEN * 0x02CE1A35 KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) HSTMT 027229A8 UWORD 2 SWORD 1 <SQL_PARAM_INPUT> SWORD 1 <SQL_C_CHAR> SWORD 12 <SQL_VARCHAR> SQLULEN 255 SWORD 0 PTR 0x02CE1A39 SQLLEN 0 SQLLEN * 0x02CE1A35 (1) KRD_AP~1 760-c44 ENTER SQLExecDirect HSTMT 027229A8 UCHAR * 0x02CE17D0 [ -3] "(SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) UNION (SELECT f2 ,COUNT(* ) FROM informix.T1 WHERE (f2 = ? ) GROUP BY f2 )\\ 0" SDWORD -3 KRD_AP~1 760-c44 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 027229A8 UCHAR * 0x02CE17D0 [ -3] "(SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) UNION (SELECT f2 ,COUNT(* ) FROM informix.t1 WHERE (f2 = ? ) GROUP BY f2 )\\ 0" SDWORD -3 DIAG [07001] [Informix][Informix ODBC Driver][Informix]Too many or too few host variables given. (-254) ...

↪ replying to Maks Romih
Paul Watson — — source: Usenet: comp.databases.informix

You might be getting caught by a casting problem that earlier versions coped with, but I would expect a different error. Posting the sql would help Maks Romih wrote: > > I'm working in MS Access97, DAO and ODBC on several Windows 2000 > machines with various Informix clients, versions 3.32, 3.33, 3.81, > 3.82. Server is Informix 9.40 on Windows 2000. > > I have pretty simple union of two simple GROUP-BY querys with a > parameter that breaks with "ODBC Call Failed", but only on newer > versions of Informix clients, that is 3.81 and 3.82. Looking into the > trace I can see the error message "Too many or too few host variables > given. (-254)". > > Is this a bug in the drivers or some compatibility mismatch between > Jet, ODBC driver, ODBC manager and Informix server? > > Does anybody have a suggestion, how to circumvent the problem or which > combination of ODBC, Informix client and Access would work better? > > Below I include the three Access querys and a part of the trace. > > The table T1 can be created with CREATE TABLE T1( f1 varchar(2), f2 > varhcar(2)) and linked from Access. The problem shows up, if you have > Informix client 3.82 and try to open the query Tq3. > > Maks. > > -------------------------------------------------------- > Tq1 : > > SELECT T1.f1, Count(*) AS Expr1 > FROM T1 > GROUP BY T1.f1 > HAVING (((T1.f1)=[a_pri]));> > -------------------------------------------------------- > Tq2 : > > SELECT T1.f2, Count(*) AS Expr1 > FROM T1 > GROUP BY T1.f2 > HAVING (((T1.f2)=[a_pri]));> > -------------------------------------------------------- > Tq3 : > > SELECT * from Tq1 UNION select * from Tq2;> > -------------------------------------------------------- > Excerpt from SQL.LOG, generated by Tracing option in ODBC Administrator : > > ... > > KRD_AP~1 760-c44 ENTER SQLAllocStmt > HDBC 027215E8 > HSTMT * 02CE1564 > > KRD_AP~1 760-c44 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) > HDBC 027215E8 > HSTMT * 0x02CE1564 ( 0x027229a8) > > KRD_AP~1 760-c44 ENTER SQLGetStmtOption > HSTMT 027229A8 > UWORD 0 > PTR 0x0013DC20 > > KRD_AP~1 760-c44 EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS) > HSTMT 027229A8 > UWORD 0 > PTR 0x0013DC20 > > KRD_AP~1 760-c44 ENTER SQLSetStmtOption > HSTMT 027229A8 > UWORD 0 <SQL_QUERY_TIMEOUT> > SQLPOINTER 0x0000003C > > KRD_AP~1 760-c44 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS) > HSTMT 027229A8 > UWORD 0 <SQL_QUERY_TIMEOUT> > SQLPOINTER 0x0000003C (BADMEM) > > KRD_AP~1 760-c44 ENTER SQLBindParameter > HSTMT 027229A8 > UWORD 1 > SWORD 1 <SQL_PARAM_INPUT> > SWORD 1 <SQL_C_CHAR> > SWORD 12 <SQL_VARCHAR> > SQLULEN 255 > SWORD 0 > PTR 0x02CE1A34 > SQLLEN 0 > SQLLEN * 0x02CE1A30 > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > HSTMT 027229A8 > UWORD 1 > SWORD 1 <SQL_PARAM_INPUT> > SWORD 1 <SQL_C_CHAR> > SWORD 12 <SQL_VARCHAR> > SQLULEN 255 > SWORD 0 > PTR 0x02CE1A34 > SQLLEN 0 > SQLLEN * 0x02CE1A30 (1) > > KRD_AP~1 760-c44 ENTER SQLBindParameter > HSTMT 027229A8 > UWORD 2 > SWORD 1 <SQL_PARAM_INPUT> > SWORD 1 <SQL_C_CHAR> > SWORD 12 <SQL_VARCHAR> > SQLULEN 255 > SWORD 0 > PTR 0x02CE1A39 > SQLLEN 0 > SQLLEN * 0x02CE1A35 > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > HSTMT 027229A8 > UWORD 2 > SWORD 1 <SQL_PARAM_INPUT> > SWORD 1 <SQL_C_CHAR> > SWORD 12 <SQL_VARCHAR> > SQLULEN 255 > SWORD 0 > PTR 0x02CE1A39 > SQLLEN 0 > SQLLEN * 0x02CE1A35 (1) > > KRD_AP~1 760-c44 ENTER SQLExecDirect > HSTMT 027229A8 > UCHAR * 0x02CE17D0 [ -3] "(SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) UNION (SELECT f2 ,COUNT(* ) FROM informix.T1 WHERE (f2 = ? ) GROUP BY f2 )\\ 0" > SDWORD -3 > > KRD_AP~1 760-c44 EXIT SQLExecDirect with return code -1 (SQL_ERROR) > HSTMT 027229A8 > UCHAR * 0x02CE17D0 [ -3] "(SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) UNION (SELECT f2 ,COUNT(* ) FROM informix.t1 WHERE (f2 = ? ) GROUP BY f2 )\\ 0" > SDWORD -3 > > DIAG [07001] [Informix][Informix ODBC Driver][Informix]Too many or too few host variables given. (-254) > > ... -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

↪ replying to Paul Watson
Maks Romih — — source: Usenet: comp.databases.informix

I'm really grateful for your taking a look, but I don't understand you exactly. Which sql should I post and where? I've included all the concerning SQLs in the posting. Maks. Paul Watson <paul@oninit.com> writes: > You might be getting caught by a casting problem that earlier > versions coped with, but I would expect a different error. Posting > the sql would help > > Maks Romih wrote: > > > > I'm working in MS Access97, DAO and ODBC on several Windows 2000 > > machines with various Informix clients, versions 3.32, 3.33, 3.81, > > 3.82. Server is Informix 9.40 on Windows 2000. > > > > I have pretty simple union of two simple GROUP-BY querys with a > > parameter that breaks with "ODBC Call Failed", but only on newer > > versions of Informix clients, that is 3.81 and 3.82. Looking into the > > trace I can see the error message "Too many or too few host variables > > given. (-254)". > > > > Is this a bug in the drivers or some compatibility mismatch between > > Jet, ODBC driver, ODBC manager and Informix server? > > > > Does anybody have a suggestion, how to circumvent the problem or which > > combination of ODBC, Informix client and Access would work better? > > > > Below I include the three Access querys and a part of the trace. > > > > The table T1 can be created with CREATE TABLE T1( f1 varchar(2), f2 > > varhcar(2)) and linked from Access. The problem shows up, if you have > > Informix client 3.82 and try to open the query Tq3. > > > > Maks. > > > > -------------------------------------------------------- > > Tq1 : > > > > SELECT T1.f1, Count(*) AS Expr1 > > FROM T1 > > GROUP BY T1.f1 > > HAVING (((T1.f1)=[a_pri]));> > > > -------------------------------------------------------- > > Tq2 : > > > > SELECT T1.f2, Count(*) AS Expr1 > > FROM T1 > > GROUP BY T1.f2 > > HAVING (((T1.f2)=[a_pri]));> > > > -------------------------------------------------------- > > Tq3 : > > > > SELECT * from Tq1 UNION select * from Tq2;> > > > -------------------------------------------------------- > > Excerpt from SQL.LOG, generated by Tracing option in ODBC Administrator : > > > > ... > > > > KRD_AP~1 760-c44 ENTER SQLAllocStmt > > HDBC 027215E8 > > HSTMT * 02CE1564 > > > > KRD_AP~1 760-c44 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) > > HDBC 027215E8 > > HSTMT * 0x02CE1564 ( 0x027229a8) > > > > KRD_AP~1 760-c44 ENTER SQLGetStmtOption > > HSTMT 027229A8 > > UWORD 0 > > PTR 0x0013DC20 > > > > KRD_AP~1 760-c44 EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS) > > HSTMT 027229A8 > > UWORD 0 > > PTR 0x0013DC20 > > > > KRD_AP~1 760-c44 ENTER SQLSetStmtOption > > HSTMT 027229A8 > > UWORD 0 <SQL_QUERY_TIMEOUT> > > SQLPOINTER 0x0000003C > > > > KRD_AP~1 760-c44 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS) > > HSTMT 027229A8 > > UWORD 0 <SQL_QUERY_TIMEOUT> > > SQLPOINTER 0x0000003C (BADMEM) > > > > KRD_AP~1 760-c44 ENTER SQLBindParameter > > HSTMT 027229A8 > > UWORD 1 > > SWORD 1 <SQL_PARAM_INPUT> > > SWORD 1 <SQL_C_CHAR> > > SWORD 12 <SQL_VARCHAR> > > SQLULEN 255 > > SWORD 0 > > PTR 0x02CE1A34 > > SQLLEN 0 > > SQLLEN * 0x02CE1A30 > > > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > > HSTMT 027229A8 > > UWORD 1 > > SWORD 1 <SQL_PARAM_INPUT> > > SWORD 1 <SQL_C_CHAR> > > SWORD 12 <SQL_VARCHAR> > > SQLULEN 255 > > SWORD 0 > > PTR 0x02CE1A34 > > SQLLEN 0 > > SQLLEN * 0x02CE1A30 (1) > > > > KRD_AP~1 760-c44 ENTER SQLBindParameter > > HSTMT 027229A8 > > UWORD 2 > > SWORD 1 <SQL_PARAM_INPUT> > > SWORD 1 <SQL_C_CHAR> > > SWORD 12 <SQL_VARCHAR> > > SQLULEN 255 > > SWORD 0 > > PTR 0x02CE1A39 > > SQLLEN 0 > > SQLLEN * 0x02CE1A35 > > > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > > HSTMT 027229A8 > > UWORD 2 > > SWORD 1 <SQL_PARAM_INPUT> > > SWORD 1 <SQL_C_CHAR> > > SWORD 12 <SQL_VARCHAR> > > SQLULEN 255 > > SWORD 0 > > PTR 0x02CE1A39 > > SQLLEN 0 > > SQLLEN * 0x02CE1A35 (1) > > > > KRD_AP~1 760-c44 ENTER SQLExecDirect > > HSTMT 027229A8 > > UCHAR * 0x02CE17D0 [ -3] "(SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) UNION (SELECT f2 ,COUNT(* ) FROM informix.T1 WHERE (f2 = ? ) GROUP BY f2 )\\ 0" > > SDWORD -3 > > > > KRD_AP~1 760-c44 EXIT SQLExecDirect with return code -1 (SQL_ERROR) > > HSTMT 027229A8 > > UCHAR * 0x02CE17D0 [ -3] "(SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) UNION (SELECT f2 ,COUNT(* ) FROM informix.t1 WHERE (f2 = ? ) GROUP BY f2 )\\ 0" > > SDWORD -3 > > > > DIAG [07001] [Informix][Informix ODBC Driver][Informix]Too many or too few host variables given. (-254) > > > > ... > > -- > Paul Watson # > Oninit Ltd # Growing old is mandatory > Tel: +44 1436 672201 # Growing up is optional > Fax: +44 1436 678693 # > Mob: +44 7818 003457 # > www.oninit.com #

↪ replying to Maks Romih
Paul Watson — — source: Usenet: comp.databases.informix

This one <quote> I have pretty simple union of two simple GROUP-BY querys with a </quote> Maks Romih wrote: > > I'm really grateful for your taking a look, but I don't understand you > exactly. Which sql should I post and where? I've included all the > concerning SQLs in the posting. > > Maks. > > Paul Watson <paul@oninit.com> writes: > > > You might be getting caught by a casting problem that earlier > > versions coped with, but I would expect a different error. Posting > > the sql would help > > > > Maks Romih wrote: > > > > > > I'm working in MS Access97, DAO and ODBC on several Windows 2000 > > > machines with various Informix clients, versions 3.32, 3.33, 3.81, > > > 3.82. Server is Informix 9.40 on Windows 2000. > > > > > > I have pretty simple union of two simple GROUP-BY querys with a > > > parameter that breaks with "ODBC Call Failed", but only on newer > > > versions of Informix clients, that is 3.81 and 3.82. Looking into the > > > trace I can see the error message "Too many or too few host variables > > > given. (-254)". > > > > > > Is this a bug in the drivers or some compatibility mismatch between > > > Jet, ODBC driver, ODBC manager and Informix server? > > > > > > Does anybody have a suggestion, how to circumvent the problem or which > > > combination of ODBC, Informix client and Access would work better? > > > > > > Below I include the three Access querys and a part of the trace. > > > > > > The table T1 can be created with CREATE TABLE T1( f1 varchar(2), f2 > > > varhcar(2)) and linked from Access. The problem shows up, if you have > > > Informix client 3.82 and try to open the query Tq3. > > > > > > Maks. > > > > > > -------------------------------------------------------- > > > Tq1 : > > > > > > SELECT T1.f1, Count(*) AS Expr1 > > > FROM T1 > > > GROUP BY T1.f1 > > > HAVING (((T1.f1)=[a_pri]));> > > > > > -------------------------------------------------------- > > > Tq2 : > > > > > > SELECT T1.f2, Count(*) AS Expr1 > > > FROM T1 > > > GROUP BY T1.f2 > > > HAVING (((T1.f2)=[a_pri]));> > > > > > -------------------------------------------------------- > > > Tq3 : > > > > > > SELECT * from Tq1 UNION select * from Tq2;> > > > > > -------------------------------------------------------- > > > Excerpt from SQL.LOG, generated by Tracing option in ODBC Administrator : > > > > > > ... > > > > > > KRD_AP~1 760-c44 ENTER SQLAllocStmt > > > HDBC 027215E8 > > > HSTMT * 02CE1564 > > > > > > KRD_AP~1 760-c44 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) > > > HDBC 027215E8 > > > HSTMT * 0x02CE1564 ( 0x027229a8) > > > > > > KRD_AP~1 760-c44 ENTER SQLGetStmtOption > > > HSTMT 027229A8 > > > UWORD 0 > > > PTR 0x0013DC20 > > > > > > KRD_AP~1 760-c44 EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS) > > > HSTMT 027229A8 > > > UWORD 0 > > > PTR 0x0013DC20 > > > > > > KRD_AP~1 760-c44 ENTER SQLSetStmtOption > > > HSTMT 027229A8 > > > UWORD 0 <SQL_QUERY_TIMEOUT> > > > SQLPOINTER 0x0000003C > > > > > > KRD_AP~1 760-c44 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS) > > > HSTMT 027229A8 > > > UWORD 0 <SQL_QUERY_TIMEOUT> > > > SQLPOINTER 0x0000003C (BADMEM) > > > > > > KRD_AP~1 760-c44 ENTER SQLBindParameter > > > HSTMT 027229A8 > > > UWORD 1 > > > SWORD 1 <SQL_PARAM_INPUT> > > > SWORD 1 <SQL_C_CHAR> > > > SWORD 12 <SQL_VARCHAR> > > > SQLULEN 255 > > > SWORD 0 > > > PTR 0x02CE1A34 > > > SQLLEN 0 > > > SQLLEN * 0x02CE1A30 > > > > > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > > > HSTMT 027229A8 > > > UWORD 1 > > > SWORD 1 <SQL_PARAM_INPUT> > > > SWORD 1 <SQL_C_CHAR> > > > SWORD 12 <SQL_VARCHAR> > > > SQLULEN 255 > > > SWORD 0 > > > PTR 0x02CE1A34 > > > SQLLEN 0 > > > SQLLEN * 0x02CE1A30 (1) > > > > > > KRD_AP~1 760-c44 ENTER SQLBindParameter > > > HSTMT 027229A8 > > > UWORD 2 > > > SWORD 1 <SQL_PARAM_INPUT> > > > SWORD 1 <SQL_C_CHAR> > > > SWORD 12 <SQL_VARCHAR> > > > SQLULEN 255 > > > SWORD 0 > > > PTR 0x02CE1A39 > > > SQLLEN 0 > > > SQLLEN * 0x02CE1A35 > > > > > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > > > HSTMT 027229A8 > > > UWORD 2 > > > SWORD 1 <SQL_PARAM_INPUT> > > > SWORD 1 <SQL_C_CHAR> > > > SWORD 12 <SQL_VARCHAR> > > > SQLULEN 255 > > > SWORD 0 > > > PTR 0x02CE1A39 > > > SQLLEN 0 > > > SQLLEN * 0x02CE1A35 (1) > > > > > > KRD_AP~1 760-c44 ENTER SQLExecDirect > > > HSTMT 027229A8 > > > UCHAR * 0x02CE17D0 [ -3] "(SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) UNION (SELECT f2 ,COUNT(* ) FROM informix.T1 WHERE (f2 = ? ) GROUP BY f2 )\\ 0" > > > SDWORD -3 > > > > > > KRD_AP~1 760-c44 EXIT SQLExecDirect with return code -1 (SQL_ERROR) > > > HSTMT 027229A8 > > > UCHAR * 0x02CE17D0 [ -3] "(SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) UNION (SELECT f2 ,COUNT(* ) FROM informix.t1 WHERE (f2 = ? ) GROUP BY f2 )\\ 0" > > > SDWORD -3 > > > > > >

↪ replying to Paul Watson
Maks Romih — — source: Usenet: comp.databases.informix

I'm sorry I didn't point it out more explicitly. It can be seen in the included trace. The SQL that goes executed with SQLExecDirect is: (SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) UNION (SELECT f2 ,COUNT(* ) FROM informix.t1 WHERE (f2 = ? ) GROUP BY f2 ) The fields f1 and f2 in table t1 are varchar(..). Maks. Paul Watson <paul@oninit.com> writes: > This one > > <quote> > I have pretty simple union of two simple GROUP-BY querys with a > </quote> > > Maks Romih wrote: > > > > I'm really grateful for your taking a look, but I don't understand you > > exactly. Which sql should I post and where? I've included all the v> > concerning SQLs in the posting. > > > > Maks. > > > > Paul Watson <paul@oninit.com> writes: > > > > > You might be getting caught by a casting problem that earlier > > > versions coped with, but I would expect a different error. Posting > > > the sql would help > > > > > > Maks Romih wrote: > > > > > > > > I'm working in MS Access97, DAO and ODBC on several Windows 2000 > > > > machines with various Informix clients, versions 3.32, 3.33, 3.81, > > > > 3.82. Server is Informix 9.40 on Windows 2000. > > > > > > > > I have pretty simple union of two simple GROUP-BY querys with a > > > > parameter that breaks with "ODBC Call Failed", but only on newer > > > > versions of Informix clients, that is 3.81 and 3.82. Looking into the > > > > trace I can see the error message "Too many or too few host variables > > > > given. (-254)". > > > > > > > > Is this a bug in the drivers or some compatibility mismatch between > > > > Jet, ODBC driver, ODBC manager and Informix server? > > > > > > > > Does anybody have a suggestion, how to circumvent the problem or which > > > > combination of ODBC, Informix client and Access would work better? > > > > > > > > Below I include the three Access querys and a part of the trace. > > > > > > > > The table T1 can be created with CREATE TABLE T1( f1 varchar(2), f2 > > > > varhcar(2)) and linked from Access. The problem shows up, if you have > > > > Informix client 3.82 and try to open the query Tq3. > > > > > > > > Maks. > > > > > > > > -------------------------------------------------------- > > > > Tq1 : > > > > > > > > SELECT T1.f1, Count(*) AS Expr1 > > > > FROM T1 > > > > GROUP BY T1.f1 > > > > HAVING (((T1.f1)=[a_pri]));> > > > > > > > -------------------------------------------------------- > > > > Tq2 : > > > > > > > > SELECT T1.f2, Count(*) AS Expr1 > > > > FROM T1 > > > > GROUP BY T1.f2 > > > > HAVING (((T1.f2)=[a_pri]));> > > > > > > > -------------------------------------------------------- > > > > Tq3 : > > > > > > > > SELECT * from Tq1 UNION select * from Tq2;> > > > > > > > -------------------------------------------------------- > > > > Excerpt from SQL.LOG, generated by Tracing option in ODBC Administrator : > > > > > > > > ... > > > > > > > > KRD_AP~1 760-c44 ENTER SQLAllocStmt > > > > HDBC 027215E8 > > > > HSTMT * 02CE1564 > > > > > > > > KRD_AP~1 760-c44 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) > > > > HDBC 027215E8 > > > > HSTMT * 0x02CE1564 ( 0x027229a8) > > > > > > > > KRD_AP~1 760-c44 ENTER SQLGetStmtOption > > > > HSTMT 027229A8 > > > > UWORD 0 > > > > PTR 0x0013DC20 > > > > > > > > KRD_AP~1 760-c44 EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS) > > > > HSTMT 027229A8 > > > > UWORD 0 > > > > PTR 0x0013DC20 > > > > > > > > KRD_AP~1 760-c44 ENTER SQLSetStmtOption > > > > HSTMT 027229A8 > > > > UWORD 0 <SQL_QUERY_TIMEOUT> > > > > SQLPOINTER 0x0000003C > > > > > > > > KRD_AP~1 760-c44 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS) > > > > HSTMT 027229A8 > > > > UWORD 0 <SQL_QUERY_TIMEOUT> > > > > SQLPOINTER 0x0000003C (BADMEM) > > > > > > > > KRD_AP~1 760-c44 ENTER SQLBindParameter > > > > HSTMT 027229A8 > > > > UWORD 1 > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > SWORD 1 <SQL_C_CHAR> > > > > SWORD 12 <SQL_VARCHAR> > > > > SQLULEN 255 > > > > SWORD 0 > > > > PTR 0x02CE1A34 > > > > SQLLEN 0 > > > > SQLLEN * 0x02CE1A30 > > > > > > > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > > > > HSTMT 027229A8 > > > > UWORD 1 > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > SWORD 1 <SQL_C_CHAR> > > > > SWORD 12 <SQL_VARCHAR> > > > > SQLULEN 255 > > > > SWORD 0 > > > > PTR 0x02CE1A34 > > > > SQLLEN 0 > > > > SQLLEN * 0x02CE1A30 (1) > > > > > > > > KRD_AP~1 760-c44 ENTER SQLBindParameter > > > > HSTMT 027229A8 > > > > UWORD 2 > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > SWORD 1 <SQL_C_CHAR> > > > > SWORD 12 <SQL_VARCHAR> > > > > SQLULEN 255 > > > > SWORD 0 > > > > PTR 0x02CE1A39 > > > > SQLLEN 0 > > > > SQLLEN * 0x02CE1A35 > > > > > > > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > > > > HSTMT 027229A8 > > > > UWORD 2 > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > SWORD 1 <SQL_C_CHAR> > > > > SWORD 12 <SQL_VARCHAR> > > > > SQLULEN 255 > > > > SWORD 0 > > > > PTR 0x02CE1A39 > > > > SQLLEN 0 > > > > SQLLEN * 0x02CE1A35 (1) > > > > > > > > KRD_AP~1 760-c44 ENTER SQLExecDirect > > > > HSTMT 027229A8 >

↪ replying to Maks Romih
Paul Watson — — source: Usenet: comp.databases.informix

what are f1 and f2 at runtime Maks Romih wrote: > > I'm sorry I didn't point it out more explicitly. It can be seen in the > included trace. The SQL that goes executed with SQLExecDirect is: > > (SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) > UNION > (SELECT f2 ,COUNT(* ) FROM informix.t1 WHERE (f2 = ? ) GROUP BY f2 ) > > The fields f1 and f2 in table t1 are varchar(..). > > Maks. > > Paul Watson <paul@oninit.com> writes: > > > This one > > > > <quote> > > I have pretty simple union of two simple GROUP-BY querys with a > > </quote> > > > > Maks Romih wrote: > > > > > > I'm really grateful for your taking a look, but I don't understand you > > > exactly. Which sql should I post and where? I've included all the > v> > concerning SQLs in the posting. > > > > > > Maks. > > > > > > Paul Watson <paul@oninit.com> writes: > > > > > > > You might be getting caught by a casting problem that earlier > > > > versions coped with, but I would expect a different error. Posting > > > > the sql would help > > > > > > > > Maks Romih wrote: > > > > > > > > > > I'm working in MS Access97, DAO and ODBC on several Windows 2000 > > > > > machines with various Informix clients, versions 3.32, 3.33, 3.81, > > > > > 3.82. Server is Informix 9.40 on Windows 2000. > > > > > > > > > > I have pretty simple union of two simple GROUP-BY querys with a > > > > > parameter that breaks with "ODBC Call Failed", but only on newer > > > > > versions of Informix clients, that is 3.81 and 3.82. Looking into the > > > > > trace I can see the error message "Too many or too few host variables > > > > > given. (-254)". > > > > > > > > > > Is this a bug in the drivers or some compatibility mismatch between > > > > > Jet, ODBC driver, ODBC manager and Informix server? > > > > > > > > > > Does anybody have a suggestion, how to circumvent the problem or which > > > > > combination of ODBC, Informix client and Access would work better? > > > > > > > > > > Below I include the three Access querys and a part of the trace. > > > > > > > > > > The table T1 can be created with CREATE TABLE T1( f1 varchar(2), f2 > > > > > varhcar(2)) and linked from Access. The problem shows up, if you have > > > > > Informix client 3.82 and try to open the query Tq3. > > > > > > > > > > Maks. > > > > > > > > > > -------------------------------------------------------- > > > > > Tq1 : > > > > > > > > > > SELECT T1.f1, Count(*) AS Expr1 > > > > > FROM T1 > > > > > GROUP BY T1.f1 > > > > > HAVING (((T1.f1)=[a_pri]));> > > > > > > > > > -------------------------------------------------------- > > > > > Tq2 : > > > > > > > > > > SELECT T1.f2, Count(*) AS Expr1 > > > > > FROM T1 > > > > > GROUP BY T1.f2 > > > > > HAVING (((T1.f2)=[a_pri]));> > > > > > > > > > -------------------------------------------------------- > > > > > Tq3 : > > > > > > > > > > SELECT * from Tq1 UNION select * from Tq2;> > > > > > > > > > -------------------------------------------------------- > > > > > Excerpt from SQL.LOG, generated by Tracing option in ODBC Administrator : > > > > > > > > > > ... > > > > > > > > > > KRD_AP~1 760-c44 ENTER SQLAllocStmt > > > > > HDBC 027215E8 > > > > > HSTMT * 02CE1564 > > > > > > > > > > KRD_AP~1 760-c44 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) > > > > > HDBC 027215E8 > > > > > HSTMT * 0x02CE1564 ( 0x027229a8) > > > > > > > > > > KRD_AP~1 760-c44 ENTER SQLGetStmtOption > > > > > HSTMT 027229A8 > > > > > UWORD 0 > > > > > PTR 0x0013DC20 > > > > > > > > > > KRD_AP~1 760-c44 EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS) > > > > > HSTMT 027229A8 > > > > > UWORD 0 > > > > > PTR 0x0013DC20 > > > > > > > > > > KRD_AP~1 760-c44 ENTER SQLSetStmtOption > > > > > HSTMT 027229A8 > > > > > UWORD 0 <SQL_QUERY_TIMEOUT> > > > > > SQLPOINTER 0x0000003C > > > > > > > > > > KRD_AP~1 760-c44 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS) > > > > > HSTMT 027229A8 > > > > > UWORD 0 <SQL_QUERY_TIMEOUT> > > > > > SQLPOINTER 0x0000003C (BADMEM) > > > > > > > > > > KRD_AP~1 760-c44 ENTER SQLBindParameter > > > > > HSTMT 027229A8 > > > > > UWORD 1 > > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > > SWORD 1 <SQL_C_CHAR> > > > > > SWORD 12 <SQL_VARCHAR> > > > > > SQLULEN 255 > > > > > SWORD 0 > > > > > PTR 0x02CE1A34 > > > > > SQLLEN 0 > > > > > SQLLEN * 0x02CE1A30 > > > > > > > > > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > > > > > HSTMT 027229A8 > > > > > UWORD 1 > > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > > SWORD 1 <SQL_C_CHAR> > > > > > SWORD 12 <SQL_VARCHAR> > > > > > SQLULEN 255 > > > > > SWORD 0 > > > > > PTR 0x02CE1A34 > > > > > SQLLEN 0 > > > > > SQLLEN * 0x02CE1A30 (1) > > > > > > > > > > KRD_AP~1 760-c44 ENTER SQLBindParameter > > > > > HSTMT 027229A8 > > > > > UWORD 2 > > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > > SWORD 1 <SQL_C_CHAR> > > > > > SWORD 12 <SQL_VARCHAR> > > > > > SQLULEN 255 > > > > > SWORD 0 > > > > > PTR 0x02CE1A39 > > > > > SQLLEN 0 > > > > > SQLLEN * 0x02CE1A35 > > > > > > > > > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > > > > > HSTMT 027229A8 > > > > > UWORD 2 > > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > > SWORD 1 <SQL_C_CHAR> > > > > > SWORD 12 <SQL_VARCHAR> > > > > > SQLULEN 25

↪ replying to Paul Watson
Maks Romih — — source: Usenet: comp.databases.informix

About the type I can not tell much more than what can be sen from the appended ODBC trace, under SQLBindParameter: > > > > >...SQLBindParameter... > > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > > SWORD 1 <SQL_C_CHAR> > > > > > SWORD 12 <SQL_VARCHAR> > > > > > SQLULEN 255 > > > > > SWORD 0 So, apparently the ODBC type is SQL_VARCHAR. The contents cannot be seen from the trace, but should be what I enter in application, that is the string of length 1 containing decimal character 3 (ascii 51). Maks. Paul Watson <paul@oninit.com> writes: > what are f1 and f2 at runtime > > Maks Romih wrote: > > > > I'm sorry I didn't point it out more explicitly. It can be seen in the > > included trace. The SQL that goes executed with SQLExecDirect is: > > > > (SELECT f1 ,COUNT(* ) FROM informix.t1 WHERE (f1 = ? ) GROUP BY f1 ) > > UNION > > (SELECT f2 ,COUNT(* ) FROM informix.t1 WHERE (f2 = ? ) GROUP BY f2 ) > > > > The fields f1 and f2 in table t1 are varchar(..). > > > > Maks. > > > > Paul Watson <paul@oninit.com> writes: > > > > > This one > > > > > > <quote> > > > I have pretty simple union of two simple GROUP-BY querys with a > > > </quote> > > > > > > Maks Romih wrote: > > > > > > > > I'm really grateful for your taking a look, but I don't understand you > > > > exactly. Which sql should I post and where? I've included all the > > v> > concerning SQLs in the posting. > > > > > > > > Maks. > > > > > > > > Paul Watson <paul@oninit.com> writes: > > > > > > > > > You might be getting caught by a casting problem that earlier > > > > > versions coped with, but I would expect a different error. Posting > > > > > the sql would help > > > > > > > > > > Maks Romih wrote: > > > > > > > > > > > > I'm working in MS Access97, DAO and ODBC on several Windows 2000 > > > > > > machines with various Informix clients, versions 3.32, 3.33, 3.81, > > > > > > 3.82. Server is Informix 9.40 on Windows 2000. > > > > > > > > > > > > I have pretty simple union of two simple GROUP-BY querys with a > > > > > > parameter that breaks with "ODBC Call Failed", but only on newer > > > > > > versions of Informix clients, that is 3.81 and 3.82. Looking into the > > > > > > trace I can see the error message "Too many or too few host variables > > > > > > given. (-254)". > > > > > > > > > > > > Is this a bug in the drivers or some compatibility mismatch between > > > > > > Jet, ODBC driver, ODBC manager and Informix server? > > > > > > > > > > > > Does anybody have a suggestion, how to circumvent the problem or which > > > > > > combination of ODBC, Informix client and Access would work better? > > > > > > > > > > > > Below I include the three Access querys and a part of the trace. > > > > > > > > > > > > The table T1 can be created with CREATE TABLE T1( f1 varchar(2), f2 > > > > > > varhcar(2)) and linked from Access. The problem shows up, if you have > > > > > > Informix client 3.82 and try to open the query Tq3. > > > > > > > > > > > > Maks. > > > > > > > > > > > > -------------------------------------------------------- > > > > > > Tq1 : > > > > > > > > > > > > SELECT T1.f1, Count(*) AS Expr1 > > > > > > FROM T1 > > > > > > GROUP BY T1.f1 > > > > > > HAVING (((T1.f1)=[a_pri]));> > > > > > > > > > > > -------------------------------------------------------- > > > > > > Tq2 : > > > > > > > > > > > > SELECT T1.f2, Count(*) AS Expr1 > > > > > > FROM T1 > > > > > > GROUP BY T1.f2 > > > > > > HAVING (((T1.f2)=[a_pri]));> > > > > > > > > > > > -------------------------------------------------------- > > > > > > Tq3 : > > > > > > > > > > > > SELECT * from Tq1 UNION select * from Tq2;> > > > > > > > > > > > -------------------------------------------------------- > > > > > > Excerpt from SQL.LOG, generated by Tracing option in ODBC Administrator : > > > > > > > > > > > > ... > > > > > > > > > > > > KRD_AP~1 760-c44 ENTER SQLAllocStmt > > > > > > HDBC 027215E8 > > > > > > HSTMT * 02CE1564 > > > > > > > > > > > > KRD_AP~1 760-c44 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) > > > > > > HDBC 027215E8 > > > > > > HSTMT * 0x02CE1564 ( 0x027229a8) > > > > > > > > > > > > KRD_AP~1 760-c44 ENTER SQLGetStmtOption > > > > > > HSTMT 027229A8 > > > > > > UWORD 0 > > > > > > PTR 0x0013DC20 > > > > > > > > > > > > KRD_AP~1 760-c44 EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS) > > > > > > HSTMT 027229A8 > > > > > > UWORD 0 > > > > > > PTR 0x0013DC20 > > > > > > > > > > > > KRD_AP~1 760-c44 ENTER SQLSetStmtOption > > > > > > HSTMT 027229A8 > > > > > > UWORD 0 <SQL_QUERY_TIMEOUT> > > > > > > SQLPOINTER 0x0000003C > > > > > > > > > > > > KRD_AP~1 760-c44 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS) > > > > > > HSTMT 027229A8 > > > > > > UWORD 0 <SQL_QUERY_TIMEOUT> > > > > > > SQLPOINTER 0x0000003C (BADMEM) > > > > > > > > > > > > KRD_AP~1 760-c44 ENTER SQLBindParameter > > > > > > HSTMT 027229A8 > > > > > > UWORD 1 > > > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > > > SWORD 1 <SQL_C_CHAR> > > > > > > SWORD 12 <SQL_VARCHAR> > > > > > > SQLULEN 255 > > > > > > SWORD 0 > > > > > > PTR 0x02CE1A34 > > > > > > SQLLEN 0 > > > > > > SQLLEN * 0x02CE1A30 > > > > > > > > > > > > KRD_AP~1 760-c44 EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) > > > > > > HSTMT 027229A8 > > > > > > UWORD 1 > > > > > > SWORD 1 <SQL_PARAM_INPUT> > > > > > > SWORD 1 <SQL_C_CHAR> > > > > > > SWORD 12 <SQL_VARCHAR> > > > > > > SQLULEN 255 > > > > > > SWORD 0 > > > > > > PTR 0x02CE1A34 > > > > > > SQLLEN 0 > > > > > > SQLLEN * 0x02CE1A30 (1) > > > > > > > > > > > > KRD_AP~1 760-c44 ENTER SQLBindParameter > > > > > > HSTMT 027229A8 > > > > >

Related threads