Bind Values Trouble

--------------090808000208090402040208
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hello I'm having a trouble with a dbi statement if anyone could help me...

I have to do an update in a table like:

$dbh->do(qq{UPDATE foos SET $new_foo = $old_foo});

when i use this statement all works fine but when i use placeholders like:

$dbh->do(qq{UPDATE foos SET $new_foo = ?}, undef, $old_foo);

i get zeros in the entire column.

any clue about this?


Best Regards,

--
Gabriel Sales de Oliveira | Async Open Source | [16] 3376 0125


--------------090808000208090402040208--

Re: Bind Values Trouble

Gabriel S. Oliveira wrote:
> Hello I'm having a trouble with a dbi statement if anyone could help me...
>
> I have to do an update in a table like:
>
> $dbh->do(qq{UPDATE foos SET $new_foo = $old_foo});
>
> when i use this statement all works fine but when i use placeholders like:
>
> $dbh->do(qq{UPDATE foos SET $new_foo = ?}, undef, $old_foo);
>
> i get zeros in the entire column.
>
> any clue about this?

The statements should have identical results. Assuming $old_foo == zero,
both statements will set the column named by $old_foo to zero for
*all* rows in table 'foos'.

However, your choice of variable names suggests you may be confused
between column names and column values, since you use $new_foo to
refer to a column *name*, and $old_foo to refer to a column *value*.

You may be really trying to do this:

UPDATE foos SET =
WHERE = ;

In any event, without a WHERE clause, an UPDATE statement will always
update every row in the table, which is what your statements are doing.
The use, or non-use, of bind variables has no effect on that.

Mark

Re:Bind Values Trouble

--------------000601000306030305060703
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Let me explain better

I'm moving an entirely column to another in the same table the variables
are the columns names:

$new_foo = "new_column";

$old_foo = "old_column";

When i use this statement everything is fine:

$dbh->do(qq{UPDATE foos SET $new_foo = $old_foo});

when i use i use placeholders like:

$dbh->do(qq{UPDATE foos SET $new_foo = ?}, undef, $old_foo);

i filled the "new_column" with zeros.


Best Regards,

--
Gabriel Sales de Oliveira | Async Open Source | [16] 3376 0125



--------------000601000306030305060703--

RE: Bind Values Trouble

That explains it. The purpose of placeholders is to let you specify
values, not column names. Your first form, without the placeholders, is
the correct way to do it.

I have a question: why do you even use variables for the column names?
Aren't the column names constant?

-Will


> -----Original Message-----
> From: Gabriel S. Oliveira [mailto:gabriel@async.com.br]=20
> Sent: Friday 12 May 2006 11:42
> To: dbi-users@perl.org
> Subject: Re:Bind Values Trouble
>=20
>=20
> Let me explain better
>=20
> I'm moving an entirely column to another in the same table=20
> the variables=20
> are the columns names:
>=20
> $new_foo =3D "new_column";
>=20
> $old_foo =3D "old_column";
>=20
> When i use this statement everything is fine:
>=20
> $dbh->do(qq{UPDATE foos SET $new_foo =3D $old_foo});
>=20
> when i use i use placeholders like:
>=20
> $dbh->do(qq{UPDATE foos SET $new_foo =3D ?}, undef, $old_foo);
>=20
> i filled the "new_column" with zeros.
>=20
>=20
> Best Regards,
>=20
> --=20
> Gabriel Sales de Oliveira | Async Open Source | [16] 3376 0125
>=20
>=20
>=20


- - - - - Appended by Scientific Atlanta, a Cisco company - - - - - =
This e-mail and any attachments may contain information which is confident=
ial, proprietary, privileged or otherwise protected by law. The information=
is solely intended for the named addressee (or a person responsible for de=
livering it to the addressee). If you are not the intended recipient of thi=
s message, you are not authorized to read, print, retain, copy or dissemina=
te this message or any part of it. If you have received this e-mail in erro=
r, please notify the sender immediately by return e-mail and delete it from=
your computer.

Re: Bind Values Trouble

--------------080800030402030407030506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Rutherdale, Will wrote:

>That explains it. The purpose of placeholders is to let you specify
>values, not column names. Your first form, without the placeholders, is
>the correct way to do it.
>
>I have a question: why do you even use variables for the column names?
>Aren't the column names constant?
>
>
I'm creating a sub to update columns so i can reuse the code...

Thanks for the response.

-Gabriel

>-Will
>
>
>
>
>>-----Original Message-----
>>From: Gabriel S. Oliveira [mailto:gabriel@async.com.br]
>>Sent: Friday 12 May 2006 11:42
>>To: dbi-users@perl.org
>>Subject: Re:Bind Values Trouble
>>
>>
>>Let me explain better
>>
>>I'm moving an entirely column to another in the same table
>>the variables
>> are the columns names:
>>
>>$new_foo = "new_column";
>>
>>$old_foo = "old_column";
>>
>>When i use this statement everything is fine:
>>
>>$dbh->do(qq{UPDATE foos SET $new_foo = $old_foo});
>>
>>when i use i use placeholders like:
>>
>>$dbh->do(qq{UPDATE foos SET $new_foo = ?}, undef, $old_foo);
>>
>>i filled the "new_column" with zeros.
>>
>>
>>Best Regards,
>>
>>--
>>Gabriel Sales de Oliveira | Async Open Source | [16] 3376 0125
>>
>>
>>
>>
>>
>
>
> - - - - - Appended by Scientific Atlanta, a Cisco company - - - - - This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer.
>
>
>

--------------080800030402030407030506--

Re: Bind Values Trouble

> Let me explain better
>
> I'm moving an entirely column to another in the same table the variables
> are the columns names:

OK, that helps.


> $new_foo = "new_column";
>
> $old_foo = "old_column";
>
> When i use this statement everything is fine:
>
> $dbh->do(qq{UPDATE foos SET $new_foo = $old_foo});
>
> when i use i use placeholders like:
>
> $dbh->do(qq{UPDATE foos SET $new_foo = ?}, undef, $old_foo);
>
> i filled the "new_column" with zeros.

This is because placeholders cannot be used for column names,
only *values* (at least in most databases.)

The way to think about it is to think about the difference between
validating the structure of the SQL, vs. actually executing the SQL.

Everything that is needed to validate the SQL itself must be fixed
at prepare time, whereas things that are only needed to actually
execute the SQL can be postponed until execution time.

For example, consider this SQL:

update FOO set COL_A = COL_B where COL_A = ?;

Note that the validity of the statement, that is: that the table
exists; that the columns exist; that the syntax is valid; that
you have the permissions to execute it; etc.. can all be established
without reference to "?". Hence, you can have a "placeholder"
for the value that is supplied at execution time.

But this statement:

update FOO set ? = 10;

The database cannot prepare the query because it cannot determine if
the table foo has the missing column.

Admittedly, using "do", where prepare and execution are merged, this
distinction is less obvious. But this is core issue that you need to
grasp in order to understand why and where placeholders can be used.

Note that this is also in the DBI docs. See the section on placeholders.

Also, note that this is not a DBI constraint. This is a constraint
imposed by the underlying databases.

Mark