Can't perform wildcard search when using ODBC in visual basic 
Author Message
 Can't perform wildcard search when using ODBC in visual basic

Dear all,

        I'm writing an application in VB 3.0.  In the application, I
need to access data from an Oracle database in Unix environment.
Although I have successfully connected to the Oracle database using ODBC
in VB, I cannot perform WildCard search using SQL statement.  There is
no error message but just no result obtained after the WildCard search.
But it works perfectly fine if I perform SQL search using an exact word.

e.g.    Dim Db As Database
        Dim Query As Dynaset
        Dim SQLQ As String
                .
                .
                .
        SQLQ = "SELECT * FROM MeSH WHERE MeSH.TreeNo LIKE '___'"
        Query = Db.CreateDynaset(SQLQ)

No record was found in this way, but if i change the SQL statement to

        SQLQ = "SELECT * FROM MeSH WHERE MeSH.TreeNo LIKE 'A01'"

Records could then be found.

        The most strange point is, when I use the interface provided by the ODBC itself after
installing Oracle SQL*NET, I can connect and perform SQL statement using
WildCard and get many records found.

        It makes me feel very strange and I really dunno why.  Could
anybody be kind enough to give me some hints ASAP?

        Many thanx in advance.

                                                        Regards,
                                                        Michael Lam



Mon, 14 Sep 1998 03:00:00 GMT  
 Can't perform wildcard search when using ODBC in visual basic

Quote:


>Subject: Can't perform wildcard search when using ODBC in Visual Basic
>Date: 28 Mar 1996 05:03:20 GMT
>Dear all,
>        I'm writing an application in VB 3.0.  In the application, I
>need to access data from an Oracle database in Unix environment.
>Although I have successfully connected to the Oracle database using ODBC
>in VB, I cannot perform WildCard search using SQL statement.  There is
>no error message but just no result obtained after the WildCard search.
>But it works perfectly fine if I perform SQL search using an exact word.
>e.g.    Dim Db As Database
>        Dim Query As Dynaset
>        Dim SQLQ As String
>                .
>                .
>                .
>        SQLQ = "SELECT * FROM MeSH WHERE MeSH.TreeNo LIKE '___'"
>        Query = Db.CreateDynaset(SQLQ)

>No record was found in this way, but if i change the SQL statement to
>        SQLQ = "SELECT * FROM MeSH WHERE MeSH.TreeNo LIKE 'A01'"
>Records could then be found.
>        The most strange point is, when I use the interface provided by the ODBC
>itself after
>installing Oracle SQL*NET, I can connect and perform SQL statement using
>WildCard and get many records found.

The problem here is that the SELECT statement is being parsed by the JET
engine before being sent to Oracle.  The wildcards that JET uses are ? and *,
rather than _ and % as defined by ANSI SQL.

There are two solutions.  One is to use the statement as you coded it and
execute it as a passthrough query.  This gets rid of the interference of the
JET engine.  The other is to use the JET wildcards.  JET *should* translate
the wildcards before sending them to Oracle.

Hope this helps,



Mon, 14 Sep 1998 03:00:00 GMT  
 Can't perform wildcard search when using ODBC in visual basic

: Dear all,

:       I'm writing an application in VB 3.0.  In the application, I
: need to access data from an Oracle database in Unix environment.
: Although I have successfully connected to the Oracle database using ODBC
: in VB, I cannot perform WildCard search using SQL statement.  There is
: no error message but just no result obtained after the WildCard search.
: But it works perfectly fine if I perform SQL search using an exact word.

: e.g.  Dim Db As Database
:       Dim Query As Dynaset
:       Dim SQLQ As String
:               .
:               .
:               .
:       SQLQ = "SELECT * FROM MeSH WHERE MeSH.TreeNo LIKE '___'"
:       Query = Db.CreateDynaset(SQLQ)

Try LIKE '???'

VB uses its own wildcards, which are supposed to be translated
by the ODBC driver to the RDBMS server's wildcards.

[snip]

--

WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!



Tue, 15 Sep 1998 03:00:00 GMT  
 Can't perform wildcard search when using ODBC in visual basic

Quote:

> : Dear all,
> :  I'm writing an application in VB 3.0.  In the application, I
> : need to access data from an Oracle database in Unix environment.
> : Although I have successfully connected to the Oracle database using ODBC
> : in VB, I cannot perform WildCard search using SQL statement.  There is
> : no error message but just no result obtained after the WildCard search.
> : But it works perfectly fine if I perform SQL search using an exact word.
> : e.g.     Dim Db As Database
> :  Dim Query As Dynaset
> :  Dim SQLQ As String
> :          .
> :          .
> :          .
> :  SQLQ = "SELECT * FROM MeSH WHERE MeSH.TreeNo LIKE '___'"
> :  Query = Db.CreateDynaset(SQLQ)
> Try LIKE '???'

        '???' is the wildcard that I originally used in VB.  I found
that it doesn't work for Oracle database and after reading books about
Oracle, I find that the wildcard used in Oracle should be '___'.
        That is, I've tried both of them already and it still doesn't
work.
        Really thanx a lot and any more hints?

        Thanx again.

                                                        Regards,
                                                        Michael Lam.



Tue, 15 Sep 1998 03:00:00 GMT  
 Can't perform wildcard search when using ODBC in visual basic

Quote:



> >Subject: Can't perform wildcard search when using ODBC in visual basic
> >Date: 28 Mar 1996 05:03:20 GMT

> >Dear all,

> >        I'm writing an application in VB 3.0.  In the application, I
> >need to access data from an Oracle database in Unix environment.
> >Although I have successfully connected to the Oracle database using ODBC
> >in VB, I cannot perform WildCard search using SQL statement.  There is
> >no error message but just no result obtained after the WildCard search.
> >But it works perfectly fine if I perform SQL search using an exact word.

> >e.g.    Dim Db As Database
> >        Dim Query As Dynaset
> >        Dim SQLQ As String
> >                .
> >                .
> >                .
> >        SQLQ = "SELECT * FROM MeSH WHERE MeSH.TreeNo LIKE '___'"
> >        Query = Db.CreateDynaset(SQLQ)

> >No record was found in this way, but if i change the SQL statement to

> >        SQLQ = "SELECT * FROM MeSH WHERE MeSH.TreeNo LIKE 'A01'"

> >Records could then be found.

> >        The most strange point is, when I use the interface provided by the ODBC
> >itself after
> >installing Oracle SQL*NET, I can connect and perform SQL statement using
> >WildCard and get many records found.

> The problem here is that the SELECT statement is being parsed by the JET
> engine before being sent to Oracle.  The wildcards that JET uses are ? and *,
> rather than _ and % as defined by ANSI SQL.

> There are two solutions.  One is to use the statement as you coded it and
> execute it as a passthrough query.  This gets rid of the interference of the
> JET engine.  The other is to use the JET wildcards.  JET *should* translate
> the wildcards before sending them to Oracle.

> Hope this helps,


Of these two solutions, the first is probably by far the best.  If your experience
with Oracle is anything like ours with Sybase, you will find that your queries
execute much faster with Passthrough.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Ray Porter                                  
Applications Analyst Programmer
Administrative Data Processing
University of North Carolina at Chapel Hill
Phone: 919/966-5878       Fax: 919/962-0900


Home Page: http://www.adp.unc.edu/~lrp/lrp.html



Tue, 15 Sep 1998 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Searching - Visual Basic or Access/ODBC/RDB training

2. Search in Outlook 98/2000 using wildcards in VB6

3. address book search using wildcards

4. Help! - Field search using * as a wildcard

5. Searching text string variables using wildcard characters

6. Search database using wildcards

7. Search database using wildcards

8. Searching strings using wildcard characters

9. Searching network drives using visual basic

10. Maximum field length using ODBC and Visual Basic 4

11. Using ODBC with Visual Basic

12. Maximum field length using ODBC and Visual Basic 4

 

 
Powered by phpBB® Forum Software