Re: [sqlite] host parameter names in string literals

[drh]

Darren Duncan <[EMAIL PROTECTED]> wrote:
> 
> Alternately, and probably more elegantly,
> 
>SELECT name FROM people WHERE name LIKE '%' || ? || '%'
> 

Yes, it is mome elegant.  Unfortunately, SQLite is stupid and
does not optimize this well.  It computes the concatenation once
per row instead of once for the whole query.  

This is really something I should fix.  

When I was first writing the expression code generator, I thought
about doing constant folding but thought "No - who ever type
in SQL expressions that contain foldable constants".  That was
a reasonable assumption then - because at that time there where
no parameters.  But now with parameters, one can see a compelling
reason to do constant folding.

There are other things ahead of this on queue (such as getting
3.3.x to work and incorporating the wince port).  So until I
get to this, your work-around is to put the constant expression
in a subquery:

   SELECT name FROM people WHERE name LIKE (SELECT '%' || ? || '%');

--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] host parameter names in string literals

[Darren Duncan]

At 10:10 AM -0700 1/18/06, Robert Simpson wrote:
- Original Message - From: "Marvin K. Bellamy" 
<[EMAIL PROTECTED]>

I want to prepare a statement like this one:

SELECT name FROM people WHERE name LIKE '%?%'

But, it looks like host parameters inside string literals aren't 
parsed.  Any clues as to how I pull this off or if the parameter 
really will be parsed?


SELECT name from people WHERE name LIKE ?

Your parameter then should contain the text to look for, such as "%mytext%"


Alternately, and probably more elegantly,

  SELECT name FROM people WHERE name LIKE '%' || ? || '%'

-- Darren Duncan

Re: [sqlite] host parameter names in string literals

[Robert Simpson]

- Original Message - 
From: "Marvin K. Bellamy" <[EMAIL PROTECTED]>

I want to prepare a statement like this one:

SELECT name FROM people WHERE name LIKE '%?%'

But, it looks like host parameters inside string literals aren't 
parsed.  Any clues as to how I pull this off or if the parameter really 
will be parsed?


SELECT name from people WHERE name LIKE ?

Your parameter then should contain the text to look for, such as "%mytext%"