Bind variable question

------_=_NextPart_001_01C7044F.A37B46A8
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable


Here is what I am trying to do:

=0D

$UPDATE_COMPANY_STRING =3D "ccompStreet =3D ?";

$UPDATE_COMPANY_VALUE_STRING =3D "\$tmpccompStreet";

=0D

$sql=3D"UPDATE clientcomp SET $UPDATE_COMPANY_STRING WHERE ccompid =3D ?"; =
=0D

$sthUpdate =3D $dbh->prepare($sql);

$sthUpdate->execute($UPDATE_COMPANY_VALUE_STRING, $tmpstcompid);

=0D

Obviously there are many more fields that _may_ be added to the 2
variables that are in all caps (I only showed 1 for simplicity). I only
add the fields that need to be updated to those 2 variables. It does
what I expect it to do except for the last line. I want the
$UPDATE_COMPANY_VALUE_STRING to be expanded to the actual string it
contains before the "execute" is run. I've tried a bunch of different
ways to make it happen - all to no avail. I either get errors or end up
with "$tmpccompStreet" in the database (instead of the value that
$tmpccompStreet contains).

=0D

Hopefully it's clear what I'm trying to accomplish here. Please be kind
- I'm relatively new to pl/sql :)

=0D

Thanks,

=0D

Steve



Confidentiality Notice: The material in this e-mail is intended only for=
the use of the individual to whom it is addressed and may contain=
information that is confidential, privileged, and exempt from disclosure=
under applicable law. If you are not the intended recipient, be advised=
that the unauthorized use, disclosure, copying, distribution, or the=
taking of any action in reliance on this information is strictly=
prohibited. If you have received this e-mail in error, please immediately=
notify us at 866-335-3289 to arrange for the return of this material to=
us. Thank You.
------_=_NextPart_001_01C7044F.A37B46A8--

RE: Bind variable question

Berlage, Steve [mailto:Steve.Berlage@Entegee.com] wrote:
>
> $UPDATE_COMPANY_STRING = "ccompStreet = ?";
>
> $UPDATE_COMPANY_VALUE_STRING = "\$tmpccompStreet";
>
> $sql="UPDATE clientcomp SET $UPDATE_COMPANY_STRING WHERE ccompid = ?";
>
> $sthUpdate = $dbh->prepare($sql);
>
> $sthUpdate->execute($UPDATE_COMPANY_VALUE_STRING, $tmpstcompid);
>
> Obviously there are many more fields that _may_ be added to the 2
> variables that are in all caps (I only showed 1 for simplicity). I only
> add the fields that need to be updated to those 2 variables. It does
> what I expect it to do except for the last line. I want the
> $UPDATE_COMPANY_VALUE_STRING to be expanded to the actual string it
> contains before the "execute" is run. I've tried a bunch of different
> ways to make it happen - all to no avail. I either get errors or end up
> with "$tmpccompStreet" in the database (instead of the value that
> $tmpccompStreet contains).

$UPDATE_COMPANY_VALUE_STRING is being expanded to the actual string it
contains. That string is "\$tmpccompStreet", which is a constant string
that has nothing to do with the variable.

If you want $UPDATE_COMPANY_VALUE_STRING to contain the value of
$tmpccompStreet, then assign $tmpccompStreet:

$UPDATE_COMPANY_VALUE_STRING = $tmpccompStreet;


Ronald

RE: Bind variable question

Kindness it is ...

Your statement only has one bind variable (?). The other "bind
variable" is actually a Perl variable. Your execute will fail because
of this.

However, there is another issue. You cannot substitute bind variables
in this manner. Your attempt to dynamically build column names will
fail. You will need to use 'EXECUTE IMMEDIATE' to perform this type of
action.

The PL/SQL documents and the Pro*C documents can give you light on how
this might be accomplished. This type of coding may be beyond the
capabilities of DBD::Oracle (at least, I have not looked into this) and
you might need to resort to using OCI::Oracle to get this level of
control.

On another note, I as a DBA am horrified to see this type of coding
practice. Building dynamic SQL, especially in a high transaction
environment, will wreak havoc on your instance's SGA. This type of
application coding is the number one way to keep an application from
scaling.

Just something to think about.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Berlage, Steve [mailto:Steve.Berlage@Entegee.com]=20
Sent: Thursday, November 09, 2006 3:37 PM
To: dbi-users@perl.org
Subject: Bind variable question


Here is what I am trying to do:




$UPDATE_COMPANY_STRING =3D "ccompStreet =3D ?";

$UPDATE_COMPANY_VALUE_STRING =3D "\$tmpccompStreet";




$sql=3D"UPDATE clientcomp SET $UPDATE_COMPANY_STRING WHERE ccompid =3D =
?";=20


$sthUpdate =3D $dbh->prepare($sql);

$sthUpdate->execute($UPDATE_COMPANY_VALUE_STRING, $tmpstcompid);




Obviously there are many more fields that _may_ be added to the 2
variables that are in all caps (I only showed 1 for simplicity). I only
add the fields that need to be updated to those 2 variables. It does
what I expect it to do except for the last line. I want the
$UPDATE_COMPANY_VALUE_STRING to be expanded to the actual string it
contains before the "execute" is run. I've tried a bunch of different
ways to make it happen - all to no avail. I either get errors or end up
with "$tmpccompStreet" in the database (instead of the value that
$tmpccompStreet contains).




Hopefully it's clear what I'm trying to accomplish here. Please be kind
- I'm relatively new to pl/sql :)




Thanks,




Steve



Confidentiality Notice: The material in this e-mail is intended only for
the use of the individual to whom it is addressed and may contain
information that is confidential, privileged, and exempt from disclosure
under applicable law. If you are not the intended recipient, be advised
that the unauthorized use, disclosure, copying, distribution, or the
taking of any action in reliance on this information is strictly
prohibited. If you have received this e-mail in error, please
immediately notify us at 866-335-3289 to arrange for the return of this
material to us. Thank You.

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: Bind variable question

------=_Part_64509_775419.1163134293868
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 11/9/06, Berlage, Steve wrote:
>
>
> Here is what I am trying to do:
>
> $UPDATE_COMPANY_STRING = "ccompStreet = ?";
>
> $UPDATE_COMPANY_VALUE_STRING = "\$tmpccompStreet";
>
>
>
> $sql="UPDATE clientcomp SET $UPDATE_COMPANY_STRING WHERE ccompid = ?";
>
> $sthUpdate = $dbh->prepare($sql);
>
> $sthUpdate->execute($UPDATE_COMPANY_VALUE_STRING, $tmpstcompid);
>
>
>
> Obviously there are many more fields that _may_ be added to the 2
> variables that are in all caps (I only showed 1 for simplicity). I only
> add the fields that need to be updated to those 2 variables. It does
> what I expect it to do except for the last line. I want the
> $UPDATE_COMPANY_VALUE_STRING to be expanded to the actual string it
> contains before the "execute" is run. I've tried a bunch of different
> ways to make it happen - all to no avail. I either get errors or end up
> with "$tmpccompStreet" in the database (instead of the value that
> $tmpccompStreet contains).
>
> Hopefully it's clear what I'm trying to accomplish here. Please be kind
> - I'm relatively new to pl/sql :)



I think you have the wrong data structure for the value string - what you
need is an array of values, with the ccompid value at the end and the other
values in sequence. So, build the SQL statement one item at a time and add
the corresponding value to an array.

You then pass the array to the $sth->execute() call.

--
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."

------=_Part_64509_775419.1163134293868--

RE: Bind variable question

Thanks guys!
I got it to work by using an array and dropping the "\".

Ron,
I should have mentioned that I'm using MySQL, so I don't think SGA will
be an issue. I will keep your warning in mind though.

Thanks again all.

Steve

-----Original Message-----
From: Jonathan Leffler [mailto:jonathan.leffler@gmail.com]=0D
Sent: Thursday, November 09, 2006 10:52 PM
To: Berlage, Steve
Cc: dbi-users@perl.org
Subject: Re: Bind variable question

On 11/9/06, Berlage, Steve wrote:
>
>
> Here is what I am trying to do:
>
> $UPDATE_COMPANY_STRING =3D "ccompStreet =3D ?";
>
> $UPDATE_COMPANY_VALUE_STRING =3D "\$tmpccompStreet";
>
>
>
> $sql=3D"UPDATE clientcomp SET $UPDATE_COMPANY_STRING WHERE ccompid =3D=
?";
>
> $sthUpdate =3D $dbh->prepare($sql);
>
> $sthUpdate->execute($UPDATE_COMPANY_VALUE_STRING, $tmpstcompid);
>
>
>
> Obviously there are many more fields that _may_ be added to the 2
> variables that are in all caps (I only showed 1 for simplicity). I
only
> add the fields that need to be updated to those 2 variables. It does
> what I expect it to do except for the last line. I want the
> $UPDATE_COMPANY_VALUE_STRING to be expanded to the actual string it
> contains before the "execute" is run. I've tried a bunch of different
> ways to make it happen - all to no avail. I either get errors or end
up
> with "$tmpccompStreet" in the database (instead of the value that
> $tmpccompStreet contains).
>
> Hopefully it's clear what I'm trying to accomplish here. Please be
kind
> - I'm relatively new to pl/sql :)



I think you have the wrong data structure for the value string - what
you
need is an array of values, with the ccompid value at the end and the
other
values in sequence. So, build the SQL statement one item at a time and
add
the corresponding value to an array.

You then pass the array to the $sth->execute() call.

--=0D
Jonathan Leffler #include
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."


Confidentiality Notice: The material in this e-mail is intended only for=
the use of the individual to whom it is addressed and may contain=
information that is confidential, privileged, and exempt from disclosure=
under applicable law. If you are not the intended recipient, be advised=
that the unauthorized use, disclosure, copying, distribution, or the=
taking of any action in reliance on this information is strictly=
prohibited. If you have received this e-mail in error, please immediately=
notify us at 866-335-3289 to arrange for the return of this material to=
us. Thank You.

RE: Bind variable question

Reidy, Ron [mailto:Ron.Reidy@arraybiopharma.com] wrote:
>
> Your statement only has one bind variable (?). The other "bind
> variable" is actually a Perl variable. Your execute will fail because
> of this.
>
> However, there is another issue. You cannot substitute bind variables
> in this manner. Your attempt to dynamically build column names will
> fail. You will need to use 'EXECUTE IMMEDIATE' to perform this type of
> action.

I think you're misinterpreting his code. He's just building up an SQL
statement in steps. After all the assignments, the value of $sql that's
passed to $dbh->prepare() is:

UPDATE clientcomp SET ccompStreet = ? WHERE ccompid = ?

which is perfectly valid. The only problem I see is that he's passing the
wrong value for the first bind parameter.

Ronald