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

Re: Weird SQL Problem

From: <operationsengineer1(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Weird SQL Problem
Date: 2005-09-08 04:09:18
Message-ID: 20050908040918.96711.qmail@web33306.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
qstr(doh!) - i know better, too.

thanks for the "extra" too.  i have some queries that
wrap to over 10 lines on my laptop screen.  i've used
heredocs before, but this application lightbulb never
went off.

thanks again.

--- Michael Glaesemann <grzm(at)myrealbox(dot)com> wrote:

> 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
> 
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map
> settings
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

In response to

pgsql-novice by date

Next:From: Pradeepkumar, Pyatalo (IE10)Date: 2005-09-08 06:40:57
Subject: Please help - libpq API
Previous:From: DavidDate: 2005-09-08 02:01:19
Subject: Re: Weird SQL Problem

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