Re: quoting values magic

On May 26, 2009, at 6:37 PM, Brandon Metcalf wrote:
> j> option 2: case when '$length' = '' ...
>
> j> you can use case like this:
>
> j>    UPDATE foo
> j>      SET
> j>        pattern = '$pattern',
> j>        shape   = '$shape',
> j>        length  = case when '$length'='' then length else
> '$length' end,
> j>        comment = '$comment'
> j>      WHERE foo_id = $foo_id
>
> j> here you can substitute any value you choose for the empty string,
> j> 0 or NULL may (or may not) be more apropriate.
>
>
> The issue here is that these reduce back to my original problem.  For
> example, if I use a CASE statement and I fall through to the ELSE,
> then the SQL is attempting to insert a "''" in a NUMERIC field which
> is not valid.  That is, it's trying to do

No it doesn't, read that statement again ;)


If $length = 'foo' it reads (leaving out the extra fields):

UPDATE foo
    SET length = CASE WHEN 'foo'='' THEN length ELSE 'foo' END
  WHERE foo_id = $foo_id;

Which evaluates to:

UPDATE foo SET length = 'foo' WHERE foo_id = $foo_id;



Whereas if $length = '' it reads:

UPDATE foo
    SET length = CASE WHEN ''='' THEN length ELSE '' END
  WHERE foo_id = $foo_id

Which evaluates to:

UPDATE foo SET length = length WHERE foo_id = $foo_id


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a1c2f7010091048315763!