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: BC870C96-372F-44D3-A266-F300A02DFC79@myrealbox.com (view raw or flat)
Thread:
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
_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



In response to

Responses

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-2014 The PostgreSQL Global Development Group