LIKE operator doesn't work with MS Access

[Sri Ram]

I am trying to get data from MS Access database. But my query with LIKE operator
doesn't work. i am using DSN less connection using DAO SDK.
My database table is like this:
EMPID age_category
(number) (text)

1 056478
2 158769
3 025796
4 185246


my query is: select empid from EmpDetails where age_category like '0[1-6]*'

it doesn't work. it returns 0 rows.

please see my code:


#include <iostream>
#include <rpc.h>
#include <sql.h>
#include <sqlext.h>

using namespace std;
#define MAXBUFLEN 255

int main()
{
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt= SQL_NULL_HSTMT;
RETCODE retcode;
SQLCHAR ConnStrIn[MAXBUFLEN] = "DRIVER={Microsoft Access
Driver (*.mdb)};
Dbq=c:\\MyDB.mdb";
SQLCHAR ConnStrOut[MAXBUFLEN];
SQLSMALLINT cbConnStrOut = 0;
SQLINTEGER nEMPID, cbEMPID;

retcode = SQLAllocEnv(&henv);
retcode = SQLAllocConnect(henv, &hdbc);

retcode = SQLDriverConnect(hdbc,NULL,ConnStrIn,SQL_NTS,
ConnStrOut,MAXBUFLEN,
&cbConnStrOut,SQL_DRIVER_NOPROMPT);
if( retcode != SQL_SUCCESS)
return 1;

std::string strQuery="select empid from EmpDetails
where age_category like '0[1-6]*'";

retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if( retcode != SQL_SUCCESS)
return 2;

retcode = SQLExecDirect(hstmt,(SQLTCHAR *)
strQuery.c_str(), SQL_NTS);
if(( retcode != SQL_SUCCESS) &&
( retcode != SQL_SUCCESS_WITH_INFO))
return 3;

retcode = SQLBindCol(hstmt, 1, SQL_C_LONG, &nEMPID, 0, &cbEMPID);
if(retcode == SQL_ERROR)
return 4;


while ( SQLFetch(hstmt) != SQL_NO_DATA_FOUND)
{
cout << nEMPID << endl;
}

return 0;
}


please correct me.

regards,
Sri Ram

[James Curran]

As far as I know, regular expressions like "0[1-6]*" do not work in a
LIKE clause. In proper SQL, you just get "%" as a wildcard for "any
characters". In MSAccess, they use "*" instead of "%", but otherwise,
that's all the options you get; all other characters are taken literally.

--
Truth,
James Curran
www.NovelTheory.com (Personal)
www.NJTheater.com (Professional)
www.aurora-inc.com (Day job)


"Sri Ram" <tekguy2k@yahoo.com> wrote in message
news:3d3b6e9b$1@10.1.10.29...
>
> I am trying to get data from MS Access database. But my query with LIKE
operator
> doesn't work. i am using DSN less connection using DAO SDK.
> My database table is like this:
> EMPID age_category
> (number) (text)
>
> 1 056478
> 2 158769
> 3 025796
> 4 185246
>
>
> my query is: select empid from EmpDetails where age_category like
'0[1-6]*'
>
> it doesn't work. it returns 0 rows.
>
> please see my code:
>
>
> #include <iostream>
> #include <rpc.h>
> #include <sql.h>
> #include <sqlext.h>
>
> using namespace std;
> #define MAXBUFLEN 255
>
> int main()
> {
> SQLHENV henv = SQL_NULL_HENV;
> SQLHDBC hdbc = SQL_NULL_HDBC;
> SQLHSTMT hstmt= SQL_NULL_HSTMT;
> RETCODE retcode;
> SQLCHAR ConnStrIn[MAXBUFLEN] = "DRIVER={Microsoft Access
> Driver (*.mdb)};
> Dbq=c:\\MyDB.mdb";
> SQLCHAR ConnStrOut[MAXBUFLEN];
> SQLSMALLINT cbConnStrOut = 0;
> SQLINTEGER nEMPID, cbEMPID;
>
> retcode = SQLAllocEnv(&henv);
> retcode = SQLAllocConnect(henv, &hdbc);
>
> retcode = SQLDriverConnect(hdbc,NULL,ConnStrIn,SQL_NTS,
> ConnStrOut,MAXBUFLEN,
> &cbConnStrOut,SQL_DRIVER_NOPROMPT);
> if( retcode != SQL_SUCCESS)
> return 1;
>
> std::string strQuery="select empid from EmpDetails
> where age_category like '0[1-6]*'";
>
> retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
> if( retcode != SQL_SUCCESS)
> return 2;
>
> retcode = SQLExecDirect(hstmt,(SQLTCHAR *)
> strQuery.c_str(), SQL_NTS);
> if(( retcode != SQL_SUCCESS) &&
> ( retcode != SQL_SUCCESS_WITH_INFO))
> return 3;
>
> retcode = SQLBindCol(hstmt, 1, SQL_C_LONG, &nEMPID, 0, &cbEMPID);
> if(retcode == SQL_ERROR)
> return 4;
>
>
> while ( SQLFetch(hstmt) != SQL_NO_DATA_FOUND)
> {
> cout << nEMPID << endl;
> }
>
> return 0;
> }
>
>
> please correct me.
>
> regards,
> Sri Ram

[ralph]

"James Curran" <jamescurran@mvps.org> wrote:
> As far as I know, regular expressions like "0[1-6]*" do not work in
a
>LIKE clause. In proper SQL, you just get "%" as a wildcard for "any
>characters". In MSAccess, they use "*" instead of "%", but otherwise,
>that's all the options you get; all other characters are taken literally.
>
>--
>Truth,
>James Curran
>www.NovelTheory.com (Personal)
>www.NJTheater.com (Professional)
>www.aurora-inc.com (Day job)
>
>
>"Sri Ram" <tekguy2k@yahoo.com> wrote in message
>news:3d3b6e9b$1@10.1.10.29...
>>
>> I am trying to get data from MS Access database. But my query with LIKE
>operator
>> doesn't work. i am using DSN less connection using DAO SDK.
>> My database table is like this:
>> EMPID age_category
>> (number) (text)
>>
>> 1 056478
>> 2 158769
>> 3 025796
>> 4 185246
>>
>>
>> my query is: select empid from EmpDetails where age_category like
>'0[1-6]*'
>>
>> it doesn't work. it returns 0 rows.
>>
>> please see my code:
>>
>>
>> #include <iostream>
>> #include <rpc.h>
>> #include <sql.h>
>> #include <sqlext.h>
>>
>> using namespace std;
>> #define MAXBUFLEN 255
>>
>> int main()
>> {
>> SQLHENV henv = SQL_NULL_HENV;
>> SQLHDBC hdbc = SQL_NULL_HDBC;
>> SQLHSTMT hstmt= SQL_NULL_HSTMT;
>> RETCODE retcode;
>> SQLCHAR ConnStrIn[MAXBUFLEN] = "DRIVER={Microsoft Access
>> Driver (*.mdb)};
>> Dbq=c:\\MyDB.mdb";
>> SQLCHAR ConnStrOut[MAXBUFLEN];
>> SQLSMALLINT cbConnStrOut = 0;
>> SQLINTEGER nEMPID, cbEMPID;
>>
>> retcode = SQLAllocEnv(&henv);
>> retcode = SQLAllocConnect(henv, &hdbc);
>>
>> retcode = SQLDriverConnect(hdbc,NULL,ConnStrIn,SQL_NTS,
>> ConnStrOut,MAXBUFLEN,
>> &cbConnStrOut,SQL_DRIVER_NOPROMPT);
>> if( retcode != SQL_SUCCESS)
>> return 1;
>>
>> std::string strQuery="select empid from EmpDetails
>> where age_category like '0[1-6]*'";
>>
>> retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
>> if( retcode != SQL_SUCCESS)
>> return 2;
>>
>> retcode = SQLExecDirect(hstmt,(SQLTCHAR *)
>> strQuery.c_str(), SQL_NTS);
>> if(( retcode != SQL_SUCCESS) &&
>> ( retcode != SQL_SUCCESS_WITH_INFO))
>> return 3;
>>
>> retcode = SQLBindCol(hstmt, 1, SQL_C_LONG, &nEMPID, 0, &cbEMPID);
>> if(retcode == SQL_ERROR)
>> return 4;
>>
>>
>> while ( SQLFetch(hstmt) != SQL_NO_DATA_FOUND)
>> {
>> cout << nEMPID << endl;
>> }
>>
>> return 0;
>> }
>>
>>
>> please correct me.
>>
>> regards,
>> Sri Ram
>


I wasn't going to respond to your original query because it is OT and I honestly
can't see anything wrong after a quick look. So I am not sure why it isn't
returning any records, but your expression for the Like operator is OK.

This is just supersition, but you might try and not use the "string", use
CString or just a character buffer to hold the SQL statement. It is possible
to have a joint psychosis with MBCS and ANSI sometimes. You might also try
using a NOT - is there any chance you have NULLS?

Sorry I can't be of more help.

-ralph

**** Just for amplification, Access Wildcards/Expressions are
? := any single character
* := any number of characters
# := any single digit[list] := any single character in list [1-9]
[!list] := any single not in list
[?] := take wildcard as a literal