Clarification on DBI module

--0-1696095135-1178694563=:88382
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi,

Can you please clarify my doubts regarding DBI perl module used for database connection.

In my environment I am using single module to prepare and execute the sql queries. The sql query can have bind variables or they may not have. In order to improve performance i used prepare() and execute() sequence for the queries.

Recently I am facing a issue. When i prepare a query with bind variables and pass the bind variables in execute() method it works fine, but second time if i invoke without passing bind variables it returns the previous query results and it is not throwing the error:

DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: OCIStmtExecute) [for Statement "

Which i believe is the expected behaviour since i should pass bind variables without which the query should fail. How come the execute functions fine without bind variables in the second/multiple query runs.

Is there a way to solve this issue other that re preparing the query ?.

Tried searching on Web regarding this issue but couldn't find any discussion on this.

Thanks,
Ramesh


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.
--0-1696095135-1178694563=:88382--

Re: Clarification on DBI module

Hard to say without some of the orginal code could be a number of things.

Do you have raiserror or pringerror set on the handle. If you are only
printing an error you may not see it and you end up just rereading the
cached data from the last query.

Post you code so we can have a look at it.

cheers
John Scoles

----- Original Message -----
From: "ramesh thangamani"
To: ;
Sent: Wednesday, May 09, 2007 3:09 AM
Subject: Clarification on DBI module


> Hi,
>
> Can you please clarify my doubts regarding DBI perl module used for
> database connection.
>
> In my environment I am using single module to prepare and execute the sql
> queries. The sql query can have bind variables or they may not have. In
> order to improve performance i used prepare() and execute() sequence for
> the queries.
>
> Recently I am facing a issue. When i prepare a query with bind variables
> and pass the bind variables in execute() method it works fine, but second
> time if i invoke without passing bind variables it returns the previous
> query results and it is not throwing the error:
>
> DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD
> ERROR: OCIStmtExecute) [for Statement "
>
> Which i believe is the expected behaviour since i should pass bind
> variables without which the query should fail. How come the execute
> functions fine without bind variables in the second/multiple query runs.
>
> Is there a way to solve this issue other that re preparing the query ?.
>
> Tried searching on Web regarding this issue but couldn't find any
> discussion on this.
>
> Thanks,
> Ramesh
>
>
> ---------------------------------
> Ahhh...imagining that irresistible "new car" smell?
> Check outnew cars at Yahoo! Autos.

Re: Clarification on DBI module

------=_NextPart_000_0174_01C7921C.58BB2DB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

well this is your problem

my $rc =3D $sth->execute(@bind) or die "Can't execute statement: =
$DBI::errstr";

You are expecting statement handle "$sth" to return a recordset into =
$rc when it calls the execute method. It does not work like that.=20

try this

my $sth =3D $dbh->prepare( q{select ename from emp where empno =3D ?})=20
or die "Can't prepare statement: $DBI::errstr";

@bind =3D (7902);

$sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

while ( $row =3D $sth->fetchrow_arrayref() ) {
print Dumper $row;
}

my @bind1 =3D ();

$sth->execute(@bind1) or die "Can't execute statement: $DBI::errstr";

while ( $row =3D $sth->fetchrow_arrayref() ) {
print Dumper $row;
}


In your old code try printing out the value of $rc you might see the =
error code there.

You code is working correctly as it is written by the way.=20
You get the same results for the second execute because the record =
buffer in the statment handle is not cleaned out because no execute took =
place.

----- Original Message -----=20
From: ramesh thangamani=20
To: John Scoles ; dbi-users@perl.org ; dbi-users-help@perl.org=20
Sent: Wednesday, May 09, 2007 8:10 AM
Subject: Re: Clarification on DBI module


Hi John,

I am attaching the code.

Here is the result:

$VAR1 =3D [
'FORD'
];
$VAR1 =3D [
'FORD'
];

Thanks,
Ramesh

John Scoles wrote:
Hard to say without some of the orginal code could be a number of =
things.

Do you have raiserror or pringerror set on the handle. If you are =
only=20
printing an error you may not see it and you end up just rereading =
the=20
cached data from the last query.

Post you code so we can have a look at it.

cheers
John Scoles

----- Original Message -----=20
From: "ramesh thangamani"=20
To: ;=20
Sent: Wednesday, May 09, 2007 3:09 AM
Subject: Clarification on DBI module


> Hi,
>
> Can you please clarify my doubts regarding DBI perl module used =
for=20
> database connection.
>
> In my environment I am using single module to prepare and execute =
the sql=20
> queries. The sql query can have bind variables or they may not =
have. In=20
> order to improve performance i used prepare() and execute() =
sequence for=20
> the queries.
>
> Recently I am facing a issue. When i prepare a query with bind =
variables=20
> and pass the bind variables in execute() method it works fine, but =
second=20
> time if i invoke without passing bind variables it returns the =
previous=20
> query results and it is not throwing the error:
>
> DBD::Oracle::st execute failed: ORA-01008: not all variables bound =
(DBD=20
> ERROR: OCIStmtExecute) [for Statement "
>
> Which i believe is the expected behaviour since i should pass bind =

> variables without which the query should fail. How come the =
execute=20
> functions fine without bind variables in the second/multiple query =
runs.
>
> Is there a way to solve this issue other that re preparing the =
query ?.
>
> Tried searching on Web regarding this issue but couldn't find any=20
> discussion on this.
>
> Thanks,
> Ramesh
>
>
> ---------------------------------
> Ahhh...imagining that irresistible "new car" smell?
> Check outnew cars at Yahoo! Autos.=20






------------------------------------------------------------ -------------=
-----
Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.
------=_NextPart_000_0174_01C7921C.58BB2DB0--

Re: Clarification on DBI module

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

On 5/9/07, ramesh thangamani wrote:

> Can you please clarify my doubts regarding DBI perl module used for
> database connection.
>
> In my environment I am using single module to prepare and execute the sql
> queries. The sql query can have bind variables or they may not have. In
> order to improve performance i used prepare() and execute() sequence for the
> queries.
>
> Recently I am facing a issue. When i prepare a query with bind variables
> and pass the bind variables in execute() method it works fine, but second
> time if i invoke without passing bind variables it returns the previous
> query results and it is not throwing the error:
>
> DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD
> ERROR: OCIStmtExecute) [for Statement "
>
> Which i believe is the expected behaviour since i should pass bind
> variables without which the query should fail. How come the execute
> functions fine without bind variables in the second/multiple query runs.
>
> Is there a way to solve this issue other that re preparing the query ?.
>
> Tried searching on Web regarding this issue but couldn't find any
> discussion on this.
>


My reading of 'perldoc DBI' (at
http://search.cpan.org/~timb/DBI-1.55/DBI.pm#DBI_STATEMENT_H ANDLE_OBJECTS)
suggests that the $sth->bind_param() method makes the values bound sticky -
the types definitely are sticky - and therefore, once values have been
supplied, those values are remembered. The $sth->bind_param_inout() - which
isn't supported by all drivers - stores references to variables, so it uses
the value at the time the $sth->execute() is called.

In other words, what you're seeing is what I'd expect to see. If you want
to provoke the error, try (it might not work) supplying one value instead of
the half-dozen needed; that might generate an error, though I'd not want to
rely on that.


--
Jonathan Leffler #include
Guardian of DBD::Informix - v2007.0226 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

------=_Part_113852_30935619.1178718662729--

Re: Clarification on DBI module

--0-699149911-1178770945=:82185
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I tried your suggestion, but still getting the same result. When i tried printing $rc in my old code i get '0E0' which means success.

John Scoles wrote: well this is your problem

my $rc = $sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

You are expecting statement handle "$sth" to return a recordset into $rc when it calls the execute method. It does not work like that.

try this

my $sth = $dbh->prepare( q{select ename from emp where empno = ?})
or die "Can't prepare statement: $DBI::errstr";

@bind = (7902);

$sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

while ( $row = $sth->fetchrow_arrayref() ) {
print Dumper $row;
}

my @bind1 = ();

$sth->execute(@bind1) or die "Can't execute statement: $DBI::errstr";

while ( $row = $sth->fetchrow_arrayref() ) {
print Dumper $row;
}


In your old code try printing out the value of $rc you might see the error code there.

You code is working correctly as it is written by the way.
You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place.

----- Original Message -----
From: ramesh thangamani
To: John Scoles ; dbi-users@perl.org ; dbi-users-help@perl.org
Sent: Wednesday, May 09, 2007 8:10 AM
Subject: Re: Clarification on DBI module


Hi John,

I am attaching the code.

Here is the result:

$VAR1 = [
'FORD'
];
$VAR1 = [
'FORD'
];

Thanks,
Ramesh

John Scoles wrote: Hard to say without some of the orginal code could be a number of things.

Do you have raiserror or pringerror set on the handle. If you are only
printing an error you may not see it and you end up just rereading the
cached data from the last query.

Post you code so we can have a look at it.

cheers
John Scoles

----- Original Message -----
From: "ramesh thangamani"
To: ;
Sent: Wednesday, May 09, 2007 3:09 AM
Subject: Clarification on DBI module


> Hi,
>
> Can you please clarify my doubts regarding DBI perl module used for
> database connection.
>
> In my environment I am using single module to prepare and execute the sql
> queries. The sql query can have bind variables or they may not have. In
> order to improve performance i used prepare() and execute() sequence for
> the queries.
>
> Recently I am facing a issue. When i prepare a query with bind variables
> and pass the bind variables in execute() method it works fine, but second
> time if i invoke without passing bind variables it returns the previous
> query results and it is not throwing the error:
>
> DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD
> ERROR: OCIStmtExecute) [for Statement "
>
> Which i believe is the expected behaviour since i should pass bind
> variables without which the query should fail. How come the execute
> functions fine without bind variables in the second/multiple query runs.
>
> Is there a way to solve this issue other that re preparing the query ?.
>
> Tried searching on Web regarding this issue but couldn't find any
> discussion on this.
>
> Thanks,
> Ramesh
>
>
> ---------------------------------
> Ahhh...imagining that irresistible "new car" smell?
> Check outnew cars at Yahoo! Autos.




---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.
--0-699149911-1178770945=:82185--

Re: Clarification on DBI module

--0-338172263-1178772046=:23978
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Thanks for your comments. May be i guess before calling execute i should check if the query has bind variables and whether they are passed otherwise i should error out.

Jonathan Leffler wrote:

On 5/9/07, ramesh thangamani wrote:
Can you please clarify my doubts regarding DBI perl module used for database connection.

In my environment I am using single module to prepare and execute the sql queries. The sql query can have bind variables or they may not have. In order to improve performance i used prepare() and execute() sequence for the queries.

Recently I am facing a issue. When i prepare a query with bind variables and pass the bind variables in execute() method it works fine, but second time if i invoke without passing bind variables it returns the previous query results and it is not throwing the error:

DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: OCIStmtExecute) [for Statement "

Which i believe is the expected behaviour since i should pass bind variables without which the query should fail. How come the execute functions fine without bind variables in the second/multiple query runs.

Is there a way to solve this issue other that re preparing the query ?.

Tried searching on Web regarding this issue but couldn't find any discussion on this.



My reading of 'perldoc DBI' (at http://search.cpan.org/~timb/DBI-1.55/DBI.pm#DBI_STATEMENT_H ANDLE_OBJECTS) suggests that the $sth->bind_param() method makes the values bound sticky - the types definitely are sticky - and therefore, once values have been supplied, those values are remembered. The $sth->bind_param_inout() - which isn't supported by all drivers - stores references to variables, so it uses the value at the time the $sth->execute() is called.

In other words, what you're seeing is what I'd expect to see. If you want to provoke the error, try (it might not work) supplying one value instead of the half-dozen needed; that might generate an error, though I'd not want to rely on that.


--
Jonathan Leffler #include
Guardian of DBD::Informix - v2007.0226 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.
--0-338172263-1178772046=:23978--

Re: Clarification on DBI module

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

On your execution without a bound value, are you actually looking for rows
where the empno column is null? If so, try this:

instead of
my @bind1 = ();
try:
my @bind1 = (undef);

Otherwise, what exactly are you looking for?

Actually, even that may not get you the null rows now that I think about it,
because null=null is false as far as Oracle is considered. You may have to
do something like this:
my $sth = $dbh->prepare( q{select ename from emp where empno = ? or (empno
is null and ? is null)})
or die "Can't prepare statement: $DBI::errstr";
and then pass in your values twice (or switch to named variables).

On 5/10/07, ramesh thangamani wrote:
>
> I tried your suggestion, but still getting the same result. When i tried
> printing $rc in my old code i get '0E0' which means success.
>
> John Scoles wrote: well this is your problem
>
> my $rc = $sth->execute(@bind) or die "Can't execute statement:
> $DBI::errstr";
>
> You are expecting statement handle "$sth" to return a recordset into $rc
> when it calls the execute method. It does not work like that.
>
> try this
>
> my $sth = $dbh->prepare( q{select ename from emp where empno = ?})
> or die "Can't prepare statement: $DBI::errstr";
>
> @bind = (7902);
>
> $sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";
>
> while ( $row = $sth->fetchrow_arrayref() ) {
> print Dumper $row;
> }
>
> my @bind1 = ();
>
> $sth->execute(@bind1) or die "Can't execute statement: $DBI::errstr";
>
> while ( $row = $sth->fetchrow_arrayref() ) {
> print Dumper $row;
> }
>
>
> In your old code try printing out the value of $rc you might see the
> error code there.
>
> You code is working correctly as it is written by the way.
> You get the same results for the second execute because the record buffer
> in the statment handle is not cleaned out because no execute took place.
>
> ----- Original Message -----
> From: ramesh thangamani
> To: John Scoles ; dbi-users@perl.org ; dbi-users-help@perl.org
> Sent: Wednesday, May 09, 2007 8:10 AM
> Subject: Re: Clarification on DBI module
>
>
> Hi John,
>
> I am attaching the code.
>
> Here is the result:
>
> $VAR1 = [
> 'FORD'
> ];
> $VAR1 = [
> 'FORD'
> ];
>
> Thanks,
> Ramesh
>
> John Scoles wrote: Hard to say without some of
> the orginal code could be a number of things.
>
> Do you have raiserror or pringerror set on the handle. If you are
> only
> printing an error you may not see it and you end up just rereading
> the
> cached data from the last query.
>
> Post you code so we can have a look at it.
>
> cheers
> John Scoles
>
> ----- Original Message -----
> From: "ramesh thangamani"
> To: ;
> Sent: Wednesday, May 09, 2007 3:09 AM
> Subject: Clarification on DBI module
>
>
> > Hi,
> >
> > Can you please clarify my doubts regarding DBI perl module used for
> > database connection.
> >
> > In my environment I am using single module to prepare and execute
> the sql
> > queries. The sql query can have bind variables or they may not
> have. In
> > order to improve performance i used prepare() and
> execute() sequence for
> > the queries.
> >
> > Recently I am facing a issue. When i prepare a query with bind
> variables
> > and pass the bind variables in execute() method it works fine, but
> second
> > time if i invoke without passing bind variables it returns the
> previous
> > query results and it is not throwing the error:
> >
> > DBD::Oracle::st execute failed: ORA-01008: not all variables bound
> (DBD
> > ERROR: OCIStmtExecute) [for Statement "
> >
> > Which i believe is the expected behaviour since i should pass bind
> > variables without which the query should fail. How come the execute
> > functions fine without bind variables in the second/multiple query
> runs.
> >
> > Is there a way to solve this issue other that re preparing the
> query ?.
> >
> > Tried searching on Web regarding this issue but couldn't find any
> > discussion on this.
> >
> > Thanks,
> > Ramesh
> >
> >
> > ---------------------------------
> > Ahhh...imagining that irresistible "new car" smell?
> > Check outnew cars at Yahoo! Autos.
>
>
>
>
> ---------------------------------
> Ahhh...imagining that irresistible "new car" smell?
> Check out new cars at Yahoo! Autos.
>
>
> ---------------------------------
> Ahhh...imagining that irresistible "new car" smell?
> Check outnew cars at Yahoo! Autos.




--
------------------------------------------------------------ --------------------------------------------------
The darkest places in hell are reserved for those who maintain their
neutrality in times of moral crisis.
Dante Alighieri (1265 - 1321)

They who would give up an essential liberty for temporary security, deserve
neither liberty or security.
Benjamin Franklin

Our lives begin to end the day we become silent about things that matter.
Martin Luther King

The right of the people to be secure in their persons, houses, papers, and
effects, against unreasonable searches and seizures, shall not be violated,
and no warrants shall issue, but upon probable cause, supported by oath or
affirmation, and particularly describing the place to be searched, and the
persons or things to be seized.

Amendment IV to the Constitution of the United States
------------------------------------------------------------ --------------------------------------------------

------=_Part_36914_20690807.1178797816282--

Re: Clarification on DBI module

--LYw3s/afESlflPpp
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2007-05-10 07:50:16 -0400, Jeffrey Seger wrote:
> On your execution without a bound value, are you actually looking for rows
> where the empno column is null? If so, try this:
>=20
> instead of
> my @bind1 =3D ();
> try:
> my @bind1 =3D (undef);
>=20
> Otherwise, what exactly are you looking for?

As I understood Ramesh, the code wasn't supposed to do anything useful.
He expected an error (e.g. "DBD::Oracle::st execute failed: called with
0 bind variables when 1 are needed"), but instead got a result.

While the behaviour is documented (if you read closely enough), and also
useful, it is somewhat surprising:

When you use 2 placeholders in your query, and then invoke execute with
1 or 3 parameters, you get an error, that the number of bind variables
doesn't match the prepared query. But if you invoke execute with 0
parameters, you don't get such an error.=20

I don't see how this behaviour could be changed without breaking
existing code, though.

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--LYw3s/afESlflPpp
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGQxUCMdFfQa64PCwRAmaCAKCVxm5AyHV3oLnfm1bJ32lj0VsyCQCe PlO+
35GrZt0xgFAPoyOZAymhtI0=
=Vj4i
-----END PGP SIGNATURE-----

--LYw3s/afESlflPpp--

Re: Clarification on DBI module

--0-1408099579-1178803413=:84200
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

What I am expecting is getting error message saying enough bind variables are not passed. Is there a way to force checking bind variables or i need to check myself?

Jeffrey Seger wrote: On your execution without a bound value, are you actually looking for rows where the empno column is null? If so, try this:

instead of
my @bind1 = ();
try:
my @bind1 = (undef);

Otherwise, what exactly are you looking for?

Actually, even that may not get you the null rows now that I think about it, because null=null is false as far as Oracle is considered. You may have to do something like this:
my $sth = $dbh->prepare( q{select ename from emp where empno = ? or (empno is null and ? is null)})
or die "Can't prepare statement: $DBI::errstr";
and then pass in your values twice (or switch to named variables).

On 5/10/07, ramesh thangamani wrote: I tried your suggestion, but still getting the same result. When i tried printing $rc in my old code i get '0E0' which means success.

John Scoles wrote: well this is your problem

my $rc = $sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

You are expecting statement handle "$sth" to return a recordset into $rc when it calls the execute method. It does not work like that.

try this

my $sth = $dbh->prepare( q{select ename from emp where empno = ?})
or die "Can't prepare statement: $DBI::errstr";

@bind = (7902);

$sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

while ( $row = $sth->fetchrow_arrayref() ) {
print Dumper $row;
}

my @bind1 = ();

$sth->execute(@bind1) or die "Can't execute statement: $DBI::errstr";

while ( $row = $sth->fetchrow_arrayref() ) {
print Dumper $row;
}


In your old code try printing out the value of $rc you might see the error code there.

You code is working correctly as it is written by the way.
You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place.

----- Original Message -----
From: ramesh thangamani
To: John Scoles ; dbi-users@perl.org ; dbi-users-help@perl.org
Sent: Wednesday, May 09, 2007 8:10 AM
Subject: Re: Clarification on DBI module


Hi John,

I am attaching the code.

Here is the result:

$VAR1 = [
'FORD'
];
$VAR1 = [
'FORD'
];

Thanks,
Ramesh

John Scoles wrote: Hard to say without some of the orginal code could be a number of things.

Do you have raiserror or pringerror set on the handle. If you are only
printing an error you may not see it and you end up just rereading the
cached data from the last query.

Post you code so we can have a look at it.

cheers
John Scoles

----- Original Message -----
From: "ramesh thangamani"
To: ;
Sent: Wednesday, May 09, 2007 3:09 AM
Subject: Clarification on DBI module


> Hi,
>
> Can you please clarify my doubts regarding DBI perl module used for
> database connection.
>
> In my environment I am using single module to prepare and execute the sql
> queries. The sql query can have bind variables or they may not have. In
> order to improve performance i used prepare() and execute() sequence for
> the queries.
>
> Recently I am facing a issue. When i prepare a query with bind variables
> and pass the bind variables in execute() method it works fine, but second
> time if i invoke without passing bind variables it returns the previous
> query results and it is not throwing the error:
>
> DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD
> ERROR: OCIStmtExecute) [for Statement "
>
> Which i believe is the expected behaviour since i should pass bind
> variables without which the query should fail. How come the execute
> functions fine without bind variables in the second/multiple query runs.
>
> Is there a way to solve this issue other that re preparing the query ?.
>
> Tried searching on Web regarding this issue but couldn't find any
> discussion on this.
>
> Thanks,
> Ramesh
>
>
> ---------------------------------
> Ahhh...imagining that irresistible "new car" smell?
> Check outnew cars at Yahoo! Autos.




---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.



--
------------------------------------------------------------ --------------------------------------------------
The darkest places in hell are reserved for those who maintain their neutrality in times of moral crisis.
Dante Alighieri (1265 - 1321)

They who would give up an essential liberty for temporary security, deserve neither liberty or security.
Benjamin Franklin

Our lives begin to end the day we become silent about things that matter.
Martin Luther King

The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.

Amendment IV to the Constitution of the United States
------------------------------------------------------------ --------------------------------------------------


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.
--0-1408099579-1178803413=:84200--

Re: Clarification on DBI module

On Thu, May 10, 2007 at 02:50:10PM +0200, Peter J. Holzer wrote:
> On 2007-05-10 07:50:16 -0400, Jeffrey Seger wrote:
> > On your execution without a bound value, are you actually looking for rows
> > where the empno column is null? If so, try this:
> >
> > instead of
> > my @bind1 = ();
> > try:
> > my @bind1 = (undef);
> >
> > Otherwise, what exactly are you looking for?
>
> As I understood Ramesh, the code wasn't supposed to do anything useful.
> He expected an error (e.g. "DBD::Oracle::st execute failed: called with
> 0 bind variables when 1 are needed"), but instead got a result.
>
> While the behaviour is documented (if you read closely enough), and also
> useful, it is somewhat surprising:
>
> When you use 2 placeholders in your query, and then invoke execute with
> 1 or 3 parameters, you get an error, that the number of bind variables
> doesn't match the prepared query. But if you invoke execute with 0
> parameters, you don't get such an error.
>
> I don't see how this behaviour could be changed without breaking
> existing code, though.

It can't as there's no way [*] for the execute() to distinguish
between $sth->execute() and $sth->execute(@empty_array).

Tim.

[*] Well, no sufficiently easy way. I could write the C code to dig
through perls internal op tree to see if there was an argument array,
but that seems like more pain than gain. Patches welcome, naturally.

Re: Clarification on DBI module

--0-1295562936-1179126238=:72370
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

At last i think i found out a way out :-). Since my statements are prepared i can use the Statement handle attribute 'NUM_OF_PARAMS' to check whether the bind variables count is matching with the bind variables count in the array passed.

Any feedback or suggestion on this.

Thanks,
Ramesh

ramesh thangamani wrote: What I am expecting is getting error message saying enough bind variables are not passed. Is there a way to force checking bind variables or i need to check myself?

Jeffrey Seger wrote: On your execution without a bound value, are you actually looking for rows where the empno column is null? If so, try this:

instead of
my @bind1 = ();
try:
my @bind1 = (undef);

Otherwise, what exactly are you looking for?

Actually, even that may not get you the null rows now that I think about it, because null=null is false as far as Oracle is considered. You may have to do something like this:
my $sth = $dbh->prepare( q{select ename from emp where empno = ? or (empno is null and ? is null)})
or die "Can't prepare statement: $DBI::errstr";
and then pass in your values twice (or switch to named variables).

On 5/10/07, ramesh thangamani wrote: I tried your suggestion, but still getting the same result. When i tried printing $rc in my old code i get '0E0' which means success.

John Scoles wrote: well this is your problem

my $rc = $sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

You are expecting statement handle "$sth" to return a recordset into $rc when it calls the execute method. It does not work like that.

try this

my $sth = $dbh->prepare( q{select ename from emp where empno = ?})
or die "Can't prepare statement: $DBI::errstr";

@bind = (7902);

$sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

while ( $row = $sth->fetchrow_arrayref() ) {
print Dumper $row;
}

my @bind1 = ();

$sth->execute(@bind1) or die "Can't execute statement: $DBI::errstr";

while ( $row = $sth->fetchrow_arrayref() ) {
print Dumper $row;
}


In your old code try printing out the value of $rc you might see the error code there.

You code is working correctly as it is written by the way.
You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place.

----- Original Message -----
From: ramesh thangamani
To: John Scoles ; dbi-users@perl.org ; dbi-users-help@perl.org
Sent: Wednesday, May 09, 2007 8:10 AM
Subject: Re: Clarification on DBI module


Hi John,

I am attaching the code.

Here is the result:

$VAR1 = [
'FORD'
];
$VAR1 = [
'FORD'
];

Thanks,
Ramesh

John Scoles wrote: Hard to say without some of the orginal code could be a number of things.

Do you have raiserror or pringerror set on the handle. If you are only
printing an error you may not see it and you end up just rereading the
cached data from the last query.

Post you code so we can have a look at it.

cheers
John Scoles

----- Original Message -----
From: "ramesh thangamani"
To: ;
Sent: Wednesday, May 09, 2007 3:09 AM
Subject: Clarification on DBI module


> Hi,
>
> Can you please clarify my doubts regarding DBI perl module used for
> database connection.
>
> In my environment I am using single module to prepare and execute the sql
> queries. The sql query can have bind variables or they may not have. In
> order to improve performance i used prepare() and execute() sequence for
> the queries.
>
> Recently I am facing a issue. When i prepare a query with bind variables
> and pass the bind variables in execute() method it works fine, but second
> time if i invoke without passing bind variables it returns the previous
> query results and it is not throwing the error:
>
> DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD
> ERROR: OCIStmtExecute) [for Statement "
>
> Which i believe is the expected behaviour since i should pass bind
> variables without which the query should fail. How come the execute
> functions fine without bind variables in the second/multiple query runs.
>
> Is there a way to solve this issue other that re preparing the query ?.
>
> Tried searching on Web regarding this issue but couldn't find any
> discussion on this.
>
> Thanks,
> Ramesh
>
>
> ---------------------------------
> Ahhh...imagining that irresistible "new car" smell?
> Check outnew cars at Yahoo! Autos.




---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.



--
------------------------------------------------------------ --------------------------------------------------
The darkest places in hell are reserved for those who maintain their neutrality in times of moral crisis.
Dante Alighieri (1265 - 1321)

They who would give up an essential liberty for temporary security, deserve neither liberty or security.
Benjamin Franklin

Our lives begin to end the day we become silent about things that matter.
Martin Luther King

The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.

Amendment IV to the Constitution of the United States
------------------------------------------------------------ --------------------------------------------------


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.


---------------------------------
Park yourself in front of a world of choices in alternative vehicles.
Visit the Yahoo! Auto Green Center.
--0-1295562936-1179126238=:72370--

Re: Clarification on DBI module

--0-1925385648-1179981852=:13144
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

In order to avoid this caching behavior my suggestion is to have an attribute like param_cache which can be used to disable/enable the caching behavior.

ramesh thangamani wrote: At last i think i found out a way out :-). Since my statements are prepared i can use the Statement handle attribute 'NUM_OF_PARAMS' to check whether the bind variables count is matching with the bind variables count in the array passed.

Any feedback or suggestion on this.

Thanks,
Ramesh

ramesh thangamani wrote: What I am expecting is getting error message saying enough bind variables are not passed. Is there a way to force checking bind variables or i need to check myself?

Jeffrey Seger wrote: On your execution without a bound value, are you actually looking for rows where the empno column is null? If so, try this:

instead of
my @bind1 = ();
try:
my @bind1 = (undef);

Otherwise, what exactly are you looking for?

Actually, even that may not get you the null rows now that I think about it, because null=null is false as far as Oracle is considered. You may have to do something like this:
my $sth = $dbh->prepare( q{select ename from emp where empno = ? or (empno is null and ? is null)})
or die "Can't prepare statement: $DBI::errstr";
and then pass in your values twice (or switch to named variables).

On 5/10/07, ramesh thangamani wrote: I tried your suggestion, but still getting the same result. When i tried printing $rc in my old code i get '0E0' which means success.

John Scoles wrote: well this is your problem

my $rc = $sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

You are expecting statement handle "$sth" to return a recordset into $rc when it calls the execute method. It does not work like that.

try this

my $sth = $dbh->prepare( q{select ename from emp where empno = ?})
or die "Can't prepare statement: $DBI::errstr";

@bind = (7902);

$sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

while ( $row = $sth->fetchrow_arrayref() ) {
print Dumper $row;
}

my @bind1 = ();

$sth->execute(@bind1) or die "Can't execute statement: $DBI::errstr";

while ( $row = $sth->fetchrow_arrayref() ) {
print Dumper $row;
}


In your old code try printing out the value of $rc you might see the error code there.

You code is working correctly as it is written by the way.
You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place.

----- Original Message -----
From: ramesh thangamani
To: John Scoles ; dbi-users@perl.org ; dbi-users-help@perl.org
Sent: Wednesday, May 09, 2007 8:10 AM
Subject: Re: Clarification on DBI module


Hi John,

I am attaching the code.

Here is the result:

$VAR1 = [
'FORD'
];
$VAR1 = [
'FORD'
];

Thanks,
Ramesh

John Scoles wrote: Hard to say without some of the orginal code could be a number of things.

Do you have raiserror or pringerror set on the handle. If you are only
printing an error you may not see it and you end up just rereading the
cached data from the last query.

Post you code so we can have a look at it.

cheers
John Scoles

----- Original Message -----
From: "ramesh thangamani"
To: ;
Sent: Wednesday, May 09, 2007 3:09 AM
Subject: Clarification on DBI module


> Hi,
>
> Can you please clarify my doubts regarding DBI perl module used for
> database connection.
>
> In my environment I am using single module to prepare and execute the sql
> queries. The sql query can have bind variables or they may not have. In
> order to improve performance i used prepare() and execute() sequence for
> the queries.
>
> Recently I am facing a issue. When i prepare a query with bind variables
> and pass the bind variables in execute() method it works fine, but second
> time if i invoke without passing bind variables it returns the previous
> query results and it is not throwing the error:
>
> DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD
> ERROR: OCIStmtExecute) [for Statement "
>
> Which i believe is the expected behaviour since i should pass bind
> variables without which the query should fail. How come the execute
> functions fine without bind variables in the second/multiple query runs.
>
> Is there a way to solve this issue other that re preparing the query ?.
>
> Tried searching on Web regarding this issue but couldn't find any
> discussion on this.
>
> Thanks,
> Ramesh
>
>
> ---------------------------------
> Ahhh...imagining that irresistible "new car" smell?
> Check outnew cars at Yahoo! Autos.




---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.



--
------------------------------------------------------------ --------------------------------------------------
The darkest places in hell are reserved for those who maintain their neutrality in times of moral crisis.
Dante Alighieri (1265 - 1321)

They who would give up an essential liberty for temporary security, deserve neither liberty or security.
Benjamin Franklin

Our lives begin to end the day we become silent about things that matter.
Martin Luther King

The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized.

Amendment IV to the Constitution of the United States
------------------------------------------------------------ --------------------------------------------------


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.


---------------------------------
Park yourself in front of a world of choices in alternative vehicles.
Visit the Yahoo! Auto Green Center.


---------------------------------
Get the Yahoo! toolbar and be alerted to new email wherever you're surfing.
--0-1925385648-1179981852=:13144--