Thread: Weird SQL Problem

Weird SQL Problem

From

Date:

07 September 2005, 23:57:40

hi all,

i'm baffled by a sql issue.

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'

where "aaaaaaaaaaaaaa" is what i entered in the
$rework _notes text area.

what am i missing here?  why is pgsql trying to find
the column? rework_notes is varchar and i've done
inserts for similarnote setups right and left with no
problem.

tia...

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

Re: Weird SQL Problem

From

Josh Berkus

Date:

08 September 2005, 00:19:22

OE,

> what am i missing here?  why is pgsql trying to find
> the column? rework_notes is varchar and i've done
> inserts for similarnote setups right and left with no
> problem.

At a guess, rework_notes isn't being quoted properly.  That's the error
you'd get if you tried to:

SET rework_notes = aaaaaaaaaaaaaaaaa

instead of

SET rework_notes = 'aaaaaaaaaaaaaaaaaa'


--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Re: Weird SQL Problem

From

Walter Kaan

Date:

08 September 2005, 01:55:41

Maybe there are double quotes enclosing the aaaaaa string? This would
make them be interpreted as a column name.

WK

On Wed, 2005-09-07 at 16:48 -0700, operationsengineer1@yahoo.com wrote:
> hi all,
>
> i'm baffled by a sql issue.
>
> 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'
>
> where "aaaaaaaaaaaaaa" is what i entered in the
> $rework _notes text area.
>
> what am i missing here?  why is pgsql trying to find
> the column? rework_notes is varchar and i've done
> inserts for similarnote setups right and left with no
> problem.
>
> tia...
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

Re: Weird SQL Problem

From

Michael Glaesemann

Date:

08 September 2005, 02:00:04

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

Re: Weird SQL Problem

From

David

Date:

08 September 2005, 02:14:07

On Wed, Sep 07, 2005 at 04:48:36PM -0700, operationsengineer1@yahoo.com wrote:
> hi all,
>
> i'm baffled by a sql issue.
>
> 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'

As Josh Berkus replied, it's a quoting issue.  I _think_ this is what
you might need (untested)

 $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 );

And the same for rework_date and/or t_defect.defect_id if either should
be a string.

One idea..  if this is in php, or a language that allows the use of
either single or double quotes for strings, I think I'd quote the query
string here with double quotes.  That would make it much easier to catch
the missing single quotes that should appear in the query (and avoid the
need to escape the single quotes.

Re: Weird SQL Problem

From

Date:

08 September 2005, 04:09:26

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

Re: Weird SQL Problem

From

Date:

08 September 2005, 22:03:48

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

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

Re: Weird SQL Problem

From

Michael Glaesemann

Date:

11 September 2005, 10:59:16

On Sep 9, 2005, at 7:03 AM, <operationsengineer1@yahoo.com>
<operationsengineer1@yahoo.com> wrote:

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

To PHP, the result of heredoc, single-quotes, or double-quotes is
just a string, so I can't think of why heredoc should have anything
to do with it. What does jump out at me is that you're using
CURRENT_TIMESTAMP outside of the string. The bare word
CURRENT_TIMESTAMP only has meaning in SQL, i.e., the SQL string
passed back to database server. Here you're using it in a PHP array,
which I'm guessing PHP is trying to interpret as a constant, and one
that's probably undefined. I'd put it in query itself. Bind variables
are for just that: variables, i.e., things that change.

Besides what appears in the browser, be sure to check your logs as
well. I keep Apache's error log open all the time when I'm coding, as
there are often errors that don't manifest themselves in the browser.

As an aside, I haven't used ADOdb's variable binding before, so I
don't know how well it compares with Perl DBI's, which does the right
thing with strings automatically (no need to escape the string). I'll
have to look into it more. If it *does*, that would be really nice.

Michael Glaesemann
grzm myrealbox com

Re: Weird SQL Problem

From

"Obe, Regina DND\\MIS"

Date:

12 September 2005, 12:25:01

I see at least 2 problems with the below.  First you are missing a space
after your WHERE and second your $reworker_notes is not quoted - use the
ADODB qstr for quoting.

It should be something like

$db->Execute('UPDATE t_defect SET
reworker_id = ' . $reworker_id . ', rework_completed =
\'t\', rework_notes = ' . $db->qstr($rework_notes) . ',
rework_date = '. $db->DBDate(time()) . ' WHERE t_defect.defect_id = ' .
$defect_id );

-----Original Message-----
From: operationsengineer1@yahoo.com [mailto:operationsengineer1@yahoo.com]
Sent: Wednesday, September 07, 2005 7:49 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Weird SQL Problem


hi all,

i'm baffled by a sql issue.

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'

where "aaaaaaaaaaaaaa" is what i entered in the
$rework _notes text area.

what am i missing here?  why is pgsql trying to find
the column? rework_notes is varchar and i've done
inserts for similarnote setups right and left with no
problem.

tia...

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org