Skip site navigation (1) Skip section navigation (2)

Re: Weird SQL Problem

From: Michael Glaesemann <grzm(at)myrealbox(dot)com>
To: <operationsengineer1(at)yahoo(dot)com> <operationsengineer1(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Weird SQL Problem
Date: 2005-09-08 01:13:34
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On Sep 8, 2005, at 8:48 AM, <operationsengineer1(at)yahoo(dot)com>  
<operationsengineer1(at)yahoo(dot)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

$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

In response to


pgsql-novice by date

Next:From: Walter KaanDate: 2005-09-08 01:16:18
Subject: Re: Weird SQL Problem
Previous:From: Tom LaneDate: 2005-09-08 00:41:29
Subject: Re: MVCC handling of updates

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group