Re: Weird SQL Problem - Mailing list 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@myrealbox.com> wrote:

> 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
>
>
>
> ---------------------------(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