Re: Weird SQL Problem - Mailing list pgsql-novice

On Sep 8, 2005, at 8:48 AM, <operationsengineer1@yahoo.com>
<operationsengineer1@yahoo.com> wrote:

> i use adodb and the code is as follows (the sql should
> be pretty evident - even if you don't follow adodb).
>
> $result_update = $db->Execute('UPDATE t_defect SET
> reworker_id = ' . $reworker_id . ', rework_completed =
> \'t\', rework_notes = ' . $rework_notes . ',
> rework_date = '. $db->DBDate(time()) . 'WHERE
> t_defect.defect_id = ' . $defect_id );
>
> my problem is this...  the text input for
> $rework_notes kicks out a pgsql error as follows...
>
> 'ERROR: column "aaaaaaaaaaaaaa" does not exist'

I know this is probably more than what you're asking for, but there
may be a few things you can do to make it easier for you to catch and
fix these types of errors. Here's another way to write code that I
think will do what you want, and may perhaps be easier to maintain.

This assumes $reworker_id, and $defect_id are numeric.

I haven't used the ADODB DBDate construct much, but if I'm reading
the docs and your code correctly, you want to insert the current
timestamp. If this is the case, you can also use the SQL-standard
CURRENT_TIMESTAMP, which will do the same thing and is easier to
read. If rework_date is actually a date column, PostgreSQL will do
the right thing and truncate the timestamp to date. Or, of course,
you could use CURRENT_DATE as well. In this case, you wouldn't need
the $rework_date variable. If you want to construct the a different
date or timestamp, I'd still abstract out a variable to make it
easier to use the heredoc method (the <<< stuff). Heredocs can be
much easier to maintain and read than concatenating a string. You can
think of a heredoc as just a big "" string, which means it does
variable interpolation.

$rework_date = $db->DBDate(time()); // currently unused

/*
Is rework_completed a boolean column? If so, you may as well just
write out 'true', which is easier to read, and you don't need to
escape it. I've made this assumption in rewriting the query.

The ADODB library has a handy qstr() method that handles string
quoting for you.
*/

$rework_notes = $db->qstr($rework_notes);

$sql = <<<_EOSQL
     UPDATE t_defect
     SET reworker_id = $reworker_id
         , rework_completed = true
         , rework_notes = $rework_notes
         , rework_date = CURRENT_TIMESTAMP
     WHERE t_defect.defect_id = $defect_id
_EOSQL;

$result_update = $db->Execute($sql);

As I said, much more than what you asked for. But it should fix the
quoting problem. :)


Michael Glaesemann
grzm myrealbox com