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

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

Post by Angelo Kal » Mon, 22 Dec 2003 01:01:49


i 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 a
 
 
 

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

Post by Brannon Jo » Sat, 10 Jan 2004 09:39:34

hen 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" < XXXX@XXXXX.COM > wrote in message
news:06b101c3c712$93afb310$ XXXX@XXXXX.COM ...



 
 
 

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

Post by Angelo Kal » Sat, 10 Jan 2004 11:27:22

hen 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.
 
 
 

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

Post by Brannon Jo » Sat, 10 Jan 2004 12:03:21

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" < XXXX@XXXXX.COM > wrote in message
news:0d9401c3d658$1c963ce0$ XXXX@XXXXX.COM ...


 
 
 

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

Post by Angelo Kal » Thu, 15 Jan 2004 00:09:05

hank 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
 
 
 

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

Post by Brannon Jo » Fri, 16 Jan 2004 04:58:36

ccess 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" < XXXX@XXXXX.COM > wrote in message
news:0bc501c3d9e7$2fd1d7a0$ XXXX@XXXXX.COM ...