Re: quoting values magic

j == jasen(at)xnet(dot)co(dot)nz writes:

j> On 2009-05-22, Brandon Metcalf <brandon(at)geronimoalloys(dot)com> wrote:
j> > Assume I have an UPDATE statement that looks like
j> >
j> > UPDATE foo
j> > SET
j> > pattern = '$pattern',
j> > shape = '$shape',
j> > length = $length,
j> > comment = '$comment'
j> > WHERE foo_id = $foo_id
j> >
j> > and length is defined as NUMERIC. Is there any kind of magic that
j> > would allow me to use the SQL above as is even if $length is not
j> > defined?

j> no, but you can get the same effect in a different way.

j> > In other words, I'd like to avoid having to modify the SQL
j> > to include or not include "length = $length" based on whether or not
j> > $length is defined as it's acceptable for it to be NULL in foo.

j> > I can't say "length = '$length'" as '' is not valid input for NUMERIC.
j> >
j> > Hope that makes sense?

j> option 1: $length = "length"

j> If your language of choice (it appears to be similar to shell, PHP, or Perl)
j> allows you to store the string value "length" in your $length variable
j> then the existing values of length will be retained in the update.

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

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

This fails. It's exactly this problem I'm trying to avoid by some
slick use of SQL. I can always generate the SQL on the fly based on
the value of $length, but I'd like to avoid this if possible.

Maybe I'm misunderstanding your suggestion.

--
Brandon