Re: Weird SQL Problem

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

Michael,

i'm trying apply your technique along with the bind
variables technique.

old method (works):

$result = $db->Execute("INSERT INTO t_customer
(customer_name, customer_entry_date) VALUES (?,?)",
array($db->qstr($customer_name),
$db->DBDate(time())));

new method (yields blank white screen with no error
messages, db or otherwise):

$sql_insert = <<<_EOSQL

INSERT INTO t_customer (customer_name,
customer_entry_date)
VALUES (?,?)

_EOSQL;

$result = $db->Execute($sql_insert,
array($customer_name, CURRENT_TIMESTAMP));

does using a heredoc preclude me from being able to
bind variables to parameters or have i found another
novel (to me, anyway!) way to jack my code up?

tia...

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