Strange behaviour of SQL Query SELECT WHERE LIKE '*something*'

[Frogbèrt]

I'm writing the simplest of search routines in ASP versus an Access database
w. ADO. The application relies heavily on the database, my project includes
a general purpose RecordSet.Open routine to handle queries. Basically, I
pass a query to the routine, it opens a connection to the database, creates
a recordset, clones it, closes the recordset and connection and returns the
clone to the calling procedure. No problems there, everything smooth.

Here comes the bizarre situation: I wrote a search routine using "SELECT *
FROM TABLE WHERE TITLE LIKE '*" & Some Variable passed in from a form & "*'"
(Caps for clarity). Now at first, this query did not return ANY result,
although copy/pasting it into Access returned the expected result. So, I
started fiddling with the query and something strange happened: I changed
the query to "SELECT * FROM TABLE WHERE TITLE='" & Variable & "'" to test
wether the LIKE clause was the problem. Now, when I entered a variable that
would logically return no results (i.e. no match found for the TITLE field),
the query indeed returned no records. But: if I enter a string into Variable
that is actually FOUND in the TITLE field, the server (W2K w. all the
updates) jumps to 100% CPU load and the process DLLHOST starts gobbling up
memory until the server hits "Low virtual memory warning". No errors are
reported in the event log, no error is returned to the client except a
timeout when the server hits "Low virtual memory". After the client stops
(either through timeout or manually), server memory and CPU load drop back
to normal. Strangely, there are multiple "SELECT * FROM TABLE WHERE ID=" &
Variable queries in the application that function flawlessly.

MSDN and KB give no answer; I am at a loss. Behaviour can be reproduced on a
different server. Anyone?

Thanks,
Robert

[Bob Barrows]

When creating dynamic sql statements in ADO, you must use the % and _
wildcards in your criteria ("select *" still works, although its use is not
recommended in production code - see Ken Schaeffer's reply to the post that
came immediately before yours to see why), no matter what wildcard
characters are used by the backend database.

HTH,
Bob Barrows

Frogbèrt wrote:

> Here comes the bizarre situation: I wrote a search routine using
> "SELECT * FROM TABLE WHERE TITLE LIKE '*" & Some Variable passed in

[Frogbèrt]

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:OuKGK3hbDHA.2344@TK2MSFTNGP12.phx.gbl...

> When creating dynamic sql statements in ADO, you must use the % and _
> wildcards in your criteria ("select *" still works, although its use is

not

> recommended in production code - see Ken Schaeffer's reply to the post

that

> came immediately before yours to see why), no matter what wildcard
> characters are used by the backend database.
>
> HTH,
> Bob Barrows
>

Thanks for the response, Bob, but changing the wildcards does not help. I'm
including some code for clarity's sake; this is the original code, which
DOES NOT return any results even though the query would return results (e.g.
in Access or SQL Server, the query would return one or more records).
Changing the query in the second line to "select * from items where Title='"
& SearchFor & "'" results (if a valid search term is entered) in the bizarre
behaviour described earlier.
SearchFor=Request.Form("TxtSearch")
SearchString="select * from items where Title like '&" & SearchFor & "&'"
Set MyRst=OpenRst(SearchString)
If MyRst.RecordCount=0 then
Response.Write "no items found."
MyRst.Close
Set MyRst=Nothing
Response.End
End If
Do Until MyRst.EOF
Response.Write "<br>" & MyRst.Fields("Description")
Loop
MyRst.Close
Set MyRst=Nothing



Function OpenRst(nQuery)
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open ConnString
Set GPRst=Server.CreateObject("ADODB.Recordset")
GPRst.LockType=2
GPRst.CursorType=3
GPRst.ActiveConnection=MyConn
GPRst.Source=nQuery
GPRst.Open
Set OpenRst=GPRst.Clone
GPRst.Close
Set GPRst=Nothing
End Function

[Ken Schaefer]

The reason that you see the 100% CPU is because you have:

: Do Until MyRst.EOF
: Response.Write "<br>" & MyRst.Fields("Description")
: Loop

because you have no myRST.MoveNext, you have an infinite loop.

Also, what Bob said about wildcards is right. When using the OLEDB Provider
you need to use the ANSI wildcards, not the Jet wildcards:

strSQL = _
"SELECT field1, field2, field3 " & _
"FROM myTable " & _
"WHERE myField LIKE '%" & strSearchFor & "%'"

[url]www.adOpenStatic.com/faq/likequeries.asp[/url]

Cheers
Ken


"Frogbèrt" <aa@aa.aa> wrote in message
news:bindl9$omn$1@news-reader5.wanadoo.fr...
: "Bob Barrows" <reb_01501@yahoo.com> wrote in message
: news:OuKGK3hbDHA.2344@TK2MSFTNGP12.phx.gbl...
: > When creating dynamic sql statements in ADO, you must use the % and _
: > wildcards in your criteria ("select *" still works, although its use is
: not
: > recommended in production code - see Ken Schaeffer's reply to the post
: that
: > came immediately before yours to see why), no matter what wildcard
: > characters are used by the backend database.
: >
: > HTH,
: > Bob Barrows
: >
: Thanks for the response, Bob, but changing the wildcards does not help.
I'm
: including some code for clarity's sake; this is the original code, which
: DOES NOT return any results even though the query would return results
(e.g.
: in Access or SQL Server, the query would return one or more records).
: Changing the query in the second line to "select * from items where
Title='"
: & SearchFor & "'" results (if a valid search term is entered) in the
bizarre
: behaviour described earlier.
: SearchFor=Request.Form("TxtSearch")
: SearchString="select * from items where Title like '&" & SearchFor & "&'"
: Set MyRst=OpenRst(SearchString)
: If MyRst.RecordCount=0 then
: Response.Write "no items found."
: MyRst.Close
: Set MyRst=Nothing
: Response.End
: End If
: Do Until MyRst.EOF
: Response.Write "<br>" & MyRst.Fields("Description")
: Loop
: MyRst.Close
: Set MyRst=Nothing
:
:
:
: Function OpenRst(nQuery)
: Set MyConn=Server.CreateObject("ADODB.Connection")
: MyConn.Open ConnString
: Set GPRst=Server.CreateObject("ADODB.Recordset")
: GPRst.LockType=2
: GPRst.CursorType=3
: GPRst.ActiveConnection=MyConn
: GPRst.Source=nQuery
: GPRst.Open
: Set OpenRst=GPRst.Clone
: GPRst.Close
: Set GPRst=Nothing
: End Function
:
:

[Bob Barrows]

Frogbèrt wrote:

> SearchString="Select ID,Title from Items where Trim(Title) like '&" &
> SearchFor & "&'"
> Set MyRst=OpenRst(SearchString)
>

Change
like '&" &
to
like '%" &

and
SearchFor & "&'"
to
SearchFor & "%'"

The wildcard is %, not &.

Bob Barrows

[Ken Schaefer]

Why are you using &?!? That's not a multi-character wildcard. You want %

SearchString="SELECT ID,Title FROM Items WHERE Title LIKE '%" & SearchFor &
"%'"

NOTE: I replaced your & with %

Cheers
Ken


"Frogbèrt" <aa@aa.aa> wrote in message
news:binhnk$bjr$1@news-reader2.wanadoo.fr...
: OK, following some hints from Bob and Ken (thanks) a train of thought
ended
: up with a workaround (which seems to get me a performance improvement as
: well :)
:
: However, I somehow feel that this code is incredibly unelegant (for-next
: loops?). And (isn't any developer like this?) I still don't know WHY the
: original code didn't work and I want to figure it out... Ah well, perhaps
: after the project is finished. But then again, perhaps not :)
:
: This is the adapted code I used:
: SearchFor=Request.Form("TxtSearch")
: Set MyRst=OpenRst("Select ID,Title from Items")
: MyArray=MyRst.GetRows
: MyRst.Close
: Set MyRst=Nothing
: For R=0 to Ubound(MyArray)
: If Instr(1,MyArray(1,R),SearchFor) then
: Response.Write MyArray(0,R) & "-" & MyArray(1,R)
: End If
: Next

[Frogbèrt]

Jep, seen that one, but I dropped the query alltogether (re. code snippet)
and worked around it, since even with the % operators the bizarre memory
gobble occurred.

Thanks for your help,
Robert

"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:e#iCe2ibDHA.2940@TK2MSFTNGP11.phx.gbl...

> Why are you using &?!? That's not a multi-character wildcard. You want %
>
> SearchString="SELECT ID,Title FROM Items WHERE Title LIKE '%" & SearchFor

&

> "%'"
>
> NOTE: I replaced your & with %
>
> Cheers
> Ken
>
>
> "Frogbèrt" <aa@aa.aa> wrote in message
> news:binhnk$bjr$1@news-reader2.wanadoo.fr...
> : OK, following some hints from Bob and Ken (thanks) a train of thought
> ended
> : up with a workaround (which seems to get me a performance improvement as
> : well :)
> :
> : However, I somehow feel that this code is incredibly unelegant (for-next
> : loops?). And (isn't any developer like this?) I still don't know WHY the
> : original code didn't work and I want to figure it out... Ah well,

perhaps

> : after the project is finished. But then again, perhaps not :)
> :
> : This is the adapted code I used:
> : SearchFor=Request.Form("TxtSearch")
> : Set MyRst=OpenRst("Select ID,Title from Items")
> : MyArray=MyRst.GetRows
> : MyRst.Close
> : Set MyRst=Nothing
> : For R=0 to Ubound(MyArray)
> : If Instr(1,MyArray(1,R),SearchFor) then
> : Response.Write MyArray(0,R) & "-" & MyArray(1,R)
> : End If
> : Next
>
>