LIKE operator doesn't work with MS Access


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: LIKE operator doesn't work with MS Access

Hybrid View

  1. #1
    Sri Ram Guest

    LIKE operator doesn't work with MS Access


    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

  2. #2
    James Curran Guest

    Re: LIKE operator doesn't work with MS Access

    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




  3. #3
    ralph Guest

    Re: LIKE operator doesn't work with MS Access


    "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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center