ODBC LIKE clause does not work - SQL Server *BUG*

Hi All:
I think there is a problem with the LIKE clause in a
SELECT statement. I am using the latest SQL Server 2000
(ver 8.00.760) and Visual C/C++ 6.00 (with SP3) on
Windows XP.
I created an ODBC connection and tied into a database
containing a customer table. The following piece of code
returns a record count of 0 (zero) when, in fact, the
record count should be over 2000.
=========================
#include <afxwin.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#define DATABASE_CLAUSE "MPOS_SQLSERVER"
#define USERNAME_CLAUSE ""
#define PASSWORD_CLAUSE ""
#define SELECT_CLAUSE "SELECT COUNT(*) from Customers
WHERE LastName LIKE ? "
#define LIKE_CLAUSE "A%"
void main( void )
{
HENV hEnv = SQL_NULL_HENV;
HDBC hDbc = SQL_NULL_HDBC;
HSTMT hStmt = SQL_NULL_HSTMT;
long lValue = 0;
SQLINTEGER sqlNull = 0;
SQLINTEGER sqlStrLen = SQL_NTS;
SQLUINTEGER sqlColumnLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlBufferLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlValue = 0;
if ( ! SQL_SUCCEEDED( SQLAllocEnv( &hEnv )))
printf( "Error in SQLAllocEnv()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocConnect( hEnv,
&hDbc )))
printf( "Error in SQLAllocConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLConnect( hDbc,
(SQLCHAR *)
DATABASE_CLAUSE, SQL_NTS,
(SQLCHAR *)
USERNAME_CLAUSE, SQL_NTS,
(SQLCHAR *)
PASSWORD_CLAUSE, SQL_NTS )))
printf( "Error in SQLConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocStmt( hDbc,
&hStmt )))
printf( "Error in SQLAllocStmt()\n" );
else if ( ! SQL_SUCCEEDED( SQLPrepare( hStmt,
(SQLCHAR *)
SELECT_CLAUSE,
SQL_NTS )))
printf( "Error in SQLPrepare()\n" );
else if ( ! SQL_SUCCEEDED( SQLBindParameter( hStmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
0,
0,
LIKE_CLAUSE,
sqlBufferLen,
&sqlStrLen )))
printf( "Error in SQLBindParameter()\n" );
else if ( ! SQL_SUCCEEDED( SQLExecute( hStmt )))
printf( "Error in SQLExecute()\n" );
else if ( ! SQL_SUCCEEDED( SQLFetch( hStmt )))
printf( "Error in SQLFetch()\n" );
else if ( ! SQL_SUCCEEDED( SQLGetData( hStmt,
1,
SQL_C_LONG,
&sqlValue,
sizeof(
sqlValue ),
&sqlNull )))
printf( "Error in SQLGetData()\n" );
else
printf( "sqlValue/sizeof( sqlValue )/sqlNull = <%
ld>/<%ld>/<%ld>\n", sqlValue, sizeof( sqlValue ),
sqlNull );
}
=========================
When using the Query Analyzer and Enterprise Manager it
works. This is because, in the above code, I use the
SQLBindParameter() function to do the binding.
I've tried using the SQL Trace facility but it only shows
me the SQL statement being executed but does not indicate
any translations with the binded parameter.
Any suggestions or solutions would be greatly appreciated.
Regards,
AngeloWhen using SQL Trace, what is the text content of the query being executed?
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:06b101c3c712$93afb310$a401280a@.phx.gbl...

quote:

---

> Hi All:
> I think there is a problem with the LIKE clause in a
> SELECT statement. I am using the latest SQL Server 2000
> (ver 8.00.760) and Visual C/C++ 6.00 (with SP3) on
> Windows XP.
> I created an ODBC connection and tied into a database
> containing a customer table. The following piece of code
> returns a record count of 0 (zero) when, in fact, the
> record count should be over 2000.
> =========================
> #include <afxwin.h>
> #include <stdio.h>
> #include <sql.h>
> #include <sqlext.h>
> #define DATABASE_CLAUSE "MPOS_SQLSERVER"
> #define USERNAME_CLAUSE ""
> #define PASSWORD_CLAUSE ""
> #define SELECT_CLAUSE "SELECT COUNT(*) from Customers
> WHERE LastName LIKE ? "
> #define LIKE_CLAUSE "A%"
> void main( void )
> {
> HENV hEnv = SQL_NULL_HENV;
> HDBC hDbc = SQL_NULL_HDBC;
> HSTMT hStmt = SQL_NULL_HSTMT;
> long lValue = 0;
> SQLINTEGER sqlNull = 0;
> SQLINTEGER sqlStrLen = SQL_NTS;
> SQLUINTEGER sqlColumnLen = strlen( LIKE_CLAUSE );
> SQLINTEGER sqlBufferLen = strlen( LIKE_CLAUSE );
> SQLINTEGER sqlValue = 0;
> if ( ! SQL_SUCCEEDED( SQLAllocEnv( &hEnv )))
> printf( "Error in SQLAllocEnv()\n" );
> else if ( ! SQL_SUCCEEDED( SQLAllocConnect( hEnv,
> &hDbc )))
> printf( "Error in SQLAllocConnect()\n" );
> else if ( ! SQL_SUCCEEDED( SQLConnect( hDbc,
> (SQLCHAR *)
> DATABASE_CLAUSE, SQL_NTS,
> (SQLCHAR *)
> USERNAME_CLAUSE, SQL_NTS,
> (SQLCHAR *)
> PASSWORD_CLAUSE, SQL_NTS )))
> printf( "Error in SQLConnect()\n" );
> else if ( ! SQL_SUCCEEDED( SQLAllocStmt( hDbc,
> &hStmt )))
> printf( "Error in SQLAllocStmt()\n" );
> else if ( ! SQL_SUCCEEDED( SQLPrepare( hStmt,
> (SQLCHAR *)
> SELECT_CLAUSE,
> SQL_NTS )))
> printf( "Error in SQLPrepare()\n" );
> else if ( ! SQL_SUCCEEDED( SQLBindParameter( hStmt,
> 1,
> SQL_PARAM_INPUT,
> SQL_C_CHAR,
> SQL_CHAR,
> 0,
> 0,
> LIKE_CLAUSE,
> sqlBufferLen,
> &sqlStrLen )))
> printf( "Error in SQLBindParameter()\n" );
> else if ( ! SQL_SUCCEEDED( SQLExecute( hStmt )))
> printf( "Error in SQLExecute()\n" );
> else if ( ! SQL_SUCCEEDED( SQLFetch( hStmt )))
> printf( "Error in SQLFetch()\n" );
> else if ( ! SQL_SUCCEEDED( SQLGetData( hStmt,
> 1,
> SQL_C_LONG,
> &sqlValue,
> sizeof(
> sqlValue ),
> &sqlNull )))
> printf( "Error in SQLGetData()\n" );
> else
> printf( "sqlValue/sizeof( sqlValue )/sqlNull = <%
> ld>/<%ld>/<%ld>\n", sqlValue, sizeof( sqlValue ),
> sqlNull );
> }
>
> =========================
> When using the Query Analyzer and Enterprise Manager it
> works. This is because, in the above code, I use the
> SQLBindParameter() function to do the binding.
> I've tried using the SQL Trace facility but it only shows
> me the SQL statement being executed but does not indicate
> any translations with the binded parameter.
> Any suggestions or solutions would be greatly appreciated.
> Regards,
> Angelo

---

|||When using the SQL Trace, the following is the snippet
from the log file...
=======================
rawodbc f38-f34 ENTER SQLPrepare
HSTMT 00391FE0
UCHAR * 0x00402104 [ -
3] "SELECT COUNT(*) from Customers WHERE LastName LIKE ?
\ 0"
SDWORD -3
=======================
If you'd like to see the entire log file, I can post that
too.

quote:

---

>--Original Message--
>When using SQL Trace, what is the text content of the

---

query being executed?

quote:

---

>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and

---

confers no rights.

quote:

---

>
>"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
>news:06b101c3c712$93afb310$a401280a@.phx.gbl...
code[QUOTE]
SQL_CHAR,[QUOTE]
<%[QUOTE]
shows[QUOTE]
indicate[QUOTE]
appreciated.[QUOTE]
>
>.
>

---

|||I tried the following and it works fine:
rc = SQLBindParameter(hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
2,
0,
"C%",
2,
NULL);
If I change the call to be like your example (passing 0 for the cbColDef
parameter) then I get an error trying to bind the parameter. What ODBC
version does your app set? 3.0 or earlier?
Try specifying the length of the string in the cbColDef parameter as well.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:0d9401c3d658$1c963ce0$a001280a@.phx.gbl...[QUOTE]
> When using the SQL Trace, the following is the snippet
> from the log file...
> =======================
> rawodbc f38-f34 ENTER SQLPrepare
> HSTMT 00391FE0
> UCHAR * 0x00402104 [ -
> 3] "SELECT COUNT(*) from Customers WHERE LastName LIKE ?
> \ 0"
> SDWORD -3
> =======================
> If you'd like to see the entire log file, I can post that
> too.
>
> query being executed?
> confers no rights.
> code
> SQL_CHAR,
> <%
> shows
> indicate
> appreciated.|||Thank you, thank you, thank you...
I am using ODBC version 2.x. The fix is the specification
of the 2 as the ColumnSize parameter.
It's funny how things work differently with Access and
SQLServer eventhough they are from the same manufacturer.
Thanks again.

quote:

---

>--Original Message--
>I tried the following and it works fine:
> rc = SQLBindParameter(hstmt,
> 1,
> SQL_PARAM_INPUT,
> SQL_C_CHAR,
> SQL_CHAR,
> 2,
> 0,
> "C%",
> 2,
> NULL);
>If I change the call to be like your example (passing 0

---

for the cbColDef

quote:

---

>parameter) then I get an error trying to bind the

---

parameter. What ODBC

quote:

---

>version does your app set? 3.0 or earlier?
>Try specifying the length of the string in the cbColDef

---

parameter as well.

quote:

---

>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and

---

confers no rights.

quote:

---

>
>"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
>news:0d9401c3d658$1c963ce0$a001280a@.phx.gbl...
LIKE ?[QUOTE]
that[QUOTE]
and[QUOTE]
message[QUOTE]
2000[QUOTE]
database[QUOTE]
the[QUOTE]
Customers[QUOTE]
LIKE_CLAUSE );[QUOTE]
hEnv,[QUOTE]
*)[QUOTE]
*)[QUOTE]
*)[QUOTE]
*)[QUOTE]
SQL_NTS )))[QUOTE]
hStmt,[QUOTE]
SQL_C_LONG,[QUOTE]
&sqlValue,[QUOTE]
&sqlNull )))[QUOTE]
sqlValue )/sqlNull =[QUOTE]
Manager it[QUOTE]
>
>.
>

---

|||Access and SQL Server are two completely different products (produced by
different groups here at Microsoft).
If you are using the SQL driver in 2.x mode, then it will allow you to
specify a zero-length precision, but obviously the behavior is not what you
expected. If you are using the SQL driver in 3.0 mode, then it will error
out if you specify a zero-length precision.
When binding parameters, you should always give the precision. In this
case, if you give a precision of zero, then we think the parameter (on the
server-side) has a length of zero, and so we truncate whatever data you give
us. Not the best behavior, but that's how it works. The precision (in
conjunction with the SQL data type) describes the type on the server.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:0bc501c3d9e7$2fd1d7a0$a401280a@.phx.gbl...[QUOTE]
> Thank you, thank you, thank you...
> I am using ODBC version 2.x. The fix is the specification
> of the 2 as the ColumnSize parameter.
> It's funny how things work differently with Access and
> SQLServer eventhough they are from the same manufacturer.
> Thanks again.
>
> for the cbColDef
> parameter. What ODBC
> parameter as well.
> confers no rights.
> LIKE ?
> that
> and
> message
> 2000
> database
> the
> Customers
> LIKE_CLAUSE );
> hEnv,
> *)
> *)
> *)
> *)
> SQL_NTS )))
> hStmt,
> SQL_C_LONG,
> &sqlValue,
> &sqlNull )))
> sqlValue )/sqlNull =
> Manager it