Brandon Metcalf wrote:
> Assume I have an UPDATE statement that looks like
>
> UPDATE foo
> SET
> pattern = '$pattern',
> shape = '$shape',
> length = $length,
> comment = '$comment'
> WHERE foo_id = $foo_id
>
> and length is defined as NUMERIC. Is there any kind of magic that
> would allow me to use the SQL above as is even if $length is not
> defined? In other words, I'd like to avoid having to modify the SQL
> to include or not include "length = $length" based on whether or not
> $length is defined as it's acceptable for it to be NULL in foo.
No. There's a reason SQL is a pain in the ass to work with at an
application level and people are always coming up with abstraction
layers and generators for it.
What language are you using? Are you familiar with the problem of SQL
injection? You need to be really paranoid about user input. Otherwise
someone will do this to you:
http://xkcd.com/327/
... so you've got more to worry about than just handling undefined
inputs. Consider using existing tools provided in your database driver
or language that handle these issues for you.
> I can't say "length = '$length'" as '' is not valid input for NUMERIC.
... and because if it was, it'd probably map to NULL, so you wouldn't be
saying "don't change `length'" you'd be saying "set length to NULL".
Do you want to leave length unchanged if $length is undefined? or do you
want to set length to null if $length is undefined?
If you want to set length to null if $length is undefined, use a
parameterised query. Any decent database driver will map "undefined"
variables in a language to NULL parameters, so you can do something like:
$sql = "
UPDATE foo
SET
pattern = ?,
shape = ?,
length = ?,
comment = ?
WHERE foo_id = ?
";
db.execute($sql, $pattern, $shape, $length, $comment, $foo_id);
( adapt as necessary to your language's syntax, database driver,
parameter placeholder syntax, etc).
If you wanted to leave `length' unchanged when $length is not defined,
instead of setting it to null, you could use the following somewhat ugly
trick:
$sql = "
UPDATE foo
SET
pattern = ?,
shape = ?,
length = COALESCE(?, foo.length),
comment = ?
WHERE foo_id = ?
";
db.execute($sql, $pattern, $shape, $length, $comment, $foo_id);
--
Craig Ringer
