select placeholder

Hi

Can anyone see the error in the following code. It has to do with the
placeholder in the select statement. Substitution fails to occur at the
"$sth1->execute..." statement script dies at "while (my $rowref...". If
I replace the placeholder with a literal it works as expected.

[code]
#
# open database
#


my $dbh = DBI->connect("dbi:SQLite:$db1","","")
or die "couldn't connect to database: " . DBI->errstr;

#
# sql statements
#

my $st1 = "SELECT * FROM a1 WHERE custnum == ?";

my $sth1 = $dbh->prepare($st1) or die "couldn't prepare... " . DBI->errstr;

my ($asset, $custnum, $branch, $customer, $address, $town, $postcode);
my ($line, $aline, $cline, @assets, @customers, @lines, @cust2, @unigas);
my ($done, $record, @records, %seen, $cnt, $ii, $best, @updates);

#
# build list of duplicate details
#

#while (not defined $done)
{
$custnum = shift @duplist;
redo if int($custnum) == 0;

$sth1->execute($custnum) or die "couldn't execute... " . DBI->errstr;

while (my $rowref = $sth1->fetchrow_arrayref) {
($asset, $custnum, $branch, $customer, $address, $town,
$postcode) = @$rowref;
[/code]


--
Regards
John McMahon

RE: select placeholder

Where you say "WHERE custnum == ?", there ought to be only one =
equals sign.

Ted Behling

-----Original Message-----
From: John [mailto:jmcm@bendigo.net.au]
Sent: Sunday, December 11, 2005 12:40 AM
To: dbi-users@perl.org
Subject: select placeholder


Hi

Can anyone see the error in the following code. It has to do with the=20
placeholder in the select statement. Substitution fails to occur at the=20
"$sth1->execute..." statement script dies at "while (my $rowref...". If=20
I replace the placeholder with a literal it works as expected.

[code]
#
# open database
#


my $dbh =3D DBI->connect("dbi:SQLite:$db1","","")
or die "couldn't connect to database: " . DBI->errstr;

#
# sql statements
#

my $st1 =3D "SELECT * FROM a1 WHERE custnum == ?";

my $sth1 =3D $dbh->prepare($st1) or die "couldn't prepare... " . =
DBI->errstr;

my ($asset, $custnum, $branch, $customer, $address, $town, $postcode);
my ($line, $aline, $cline, @assets, @customers, @lines, @cust2, =
@unigas);
my ($done, $record, @records, %seen, $cnt, $ii, $best, @updates);

#
# build list of duplicate details
#

#while (not defined $done)
{
$custnum =3D shift @duplist;
redo if int($custnum) == 0;

$sth1->execute($custnum) or die "couldn't execute... " . =
DBI->errstr;

while (my $rowref =3D $sth1->fetchrow_arrayref) {
($asset, $custnum, $branch, $customer, $address, $town,=20
$postcode) =3D @$rowref;
[/code]


--
Regards
John McMahon

RE: select placeholder

Try "SELECT * FROM a1 WHERE custnum =3D ?"

-----Original Message-----
From: John [mailto:jmcm@bendigo.net.au]=20
Sent: Saturday, December 10, 2005 10:40 PM
To: dbi-users@perl.org
Subject: select placeholder

Hi

Can anyone see the error in the following code. It has to do with the
placeholder in the select statement. Substitution fails to occur at the
"$sth1->execute..." statement script dies at "while (my $rowref...". If
I replace the placeholder with a literal it works as expected.

[code]
#
# open database
#


my $dbh =3D DBI->connect("dbi:SQLite:$db1","","")
or die "couldn't connect to database: " . DBI->errstr;

#
# sql statements
#

my $st1 =3D "SELECT * FROM a1 WHERE custnum == ?";

my $sth1 =3D $dbh->prepare($st1) or die "couldn't prepare... " .
DBI->errstr;

my ($asset, $custnum, $branch, $customer, $address, $town, $postcode);
my ($line, $aline, $cline, @assets, @customers, @lines, @cust2,
@unigas); my ($done, $record, @records, %seen, $cnt, $ii, $best,
@updates);

#
# build list of duplicate details
#

#while (not defined $done)
{
$custnum =3D shift @duplist;
redo if int($custnum) == 0;

$sth1->execute($custnum) or die "couldn't execute... " .
DBI->errstr;

while (my $rowref =3D $sth1->fetchrow_arrayref) {
($asset, $custnum, $branch, $customer, $address, $town,
$postcode) =3D @$rowref;
[/code]


--
Regards
John McMahon

Re: select placeholder

Ian Harisay wrote:
> Try "SELECT * FROM a1 WHERE custnum = ?"

Thank you Ian and Ted

That is not the problem, I had previously used a single "=" and went to
"==" when it didn't work, tried again this morning - still doesn't work.
SQLite documentation says either is acceptable. A little more
information, the substitution value is a string - dbi docs say the
placeholder does not need to be quoted, looking with "ptkdb", $rowref
remains "undef" so I am assuming the substitution is not made at the
"execute" statement and the result is a SQL search for a "null" value in
the "custnum" field which returns nothing.

John
>
> -----Original Message-----
> From: John [mailto:jmcm@bendigo.net.au]
> Sent: Saturday, December 10, 2005 10:40 PM
> To: dbi-users@perl.org
> Subject: select placeholder
>
> Hi
>
> Can anyone see the error in the following code. It has to do with the
> placeholder in the select statement. Substitution fails to occur at the
> "$sth1->execute..." statement script dies at "while (my $rowref...". If
> I replace the placeholder with a literal it works as expected.
>
> [code]
> #
> # open database
> #
>
>
> my $dbh = DBI->connect("dbi:SQLite:$db1","","")
> or die "couldn't connect to database: " . DBI->errstr;
>
> #
> # sql statements
> #
>
> my $st1 = "SELECT * FROM a1 WHERE custnum == ?";
>
> my $sth1 = $dbh->prepare($st1) or die "couldn't prepare... " .
> DBI->errstr;
>
> my ($asset, $custnum, $branch, $customer, $address, $town, $postcode);
> my ($line, $aline, $cline, @assets, @customers, @lines, @cust2,
> @unigas); my ($done, $record, @records, %seen, $cnt, $ii, $best,
> @updates);
>
> #
> # build list of duplicate details
> #
>
> #while (not defined $done)
> {
> $custnum = shift @duplist;
> redo if int($custnum) == 0;
>
> $sth1->execute($custnum) or die "couldn't execute... " .
> DBI->errstr;
>
> while (my $rowref = $sth1->fetchrow_arrayref) {
> ($asset, $custnum, $branch, $customer, $address, $town,
> $postcode) = @$rowref;
> [/code]
>
>
> --
> Regards
> John McMahon
>
>
>
>


--
Regards
John McMahon (jmcm@bendigo.net.au)




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 9/12/2005

Re: select placeholder

John wrote:
> Ian Harisay wrote:
>
>> Try "SELECT * FROM a1 WHERE custnum = ?"
>
>
> Thank you Ian and Ted
>
> That is not the problem, I had previously used a single "=" and went to
> "==" when it didn't work, tried again this morning - still doesn't work.
> SQLite documentation says either is acceptable. A little more
> information, the substitution value is a string - dbi docs say the
> placeholder does not need to be quoted, looking with "ptkdb", $rowref
> remains "undef" so I am assuming the substitution is not made at the
> "execute" statement and the result is a SQL search for a "null" value in
> the "custnum" field which returns nothing.
>
If I replace the variable in the execute statement with a literal it
works for that literal. Steping thru with ptkdb the variable is defined
with an acceptable value at that point - the execute statement just
doesn't seem to see it.

John
> John
>
>>
>> -----Original Message-----
>> From: John [mailto:jmcm@bendigo.net.au] Sent: Saturday, December 10,
>> 2005 10:40 PM
>> To: dbi-users@perl.org
>> Subject: select placeholder
>>
>> Hi
>>
>> Can anyone see the error in the following code. It has to do with the
>> placeholder in the select statement. Substitution fails to occur at the
>> "$sth1->execute..." statement script dies at "while (my $rowref...". If
>> I replace the placeholder with a literal it works as expected.
>>
>> [code]
>> #
>> # open database
>> #
>>
>>
>> my $dbh = DBI->connect("dbi:SQLite:$db1","","")
>> or die "couldn't connect to database: " . DBI->errstr;
>>
>> #
>> # sql statements
>> #
>>
>> my $st1 = "SELECT * FROM a1 WHERE custnum == ?";
>>
>> my $sth1 = $dbh->prepare($st1) or die "couldn't prepare... " .
>> DBI->errstr;
>>
>> my ($asset, $custnum, $branch, $customer, $address, $town, $postcode);
>> my ($line, $aline, $cline, @assets, @customers, @lines, @cust2,
>> @unigas); my ($done, $record, @records, %seen, $cnt, $ii, $best,
>> @updates);
>>
>> #
>> # build list of duplicate details
>> #
>>
>> #while (not defined $done)
>> {
>> $custnum = shift @duplist;
>> redo if int($custnum) == 0;
>>
>> $sth1->execute($custnum) or die "couldn't execute... " .
>> DBI->errstr;
>>
>> while (my $rowref = $sth1->fetchrow_arrayref) {
>> ($asset, $custnum, $branch, $customer, $address, $town,
>> $postcode) = @$rowref;
>> [/code]
>>
>>
>> --
>> Regards
>> John McMahon
>>
>>
>>
>>
>
>


--
Regards
John McMahon (jmcm@bendigo.net.au)




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 9/12/2005

RE: select placeholder

John wrote:
>> Can anyone see the error in the following code. It has to do with the

>> placeholder in the select statement. Substitution fails to occur at=20
>> the "$sth1->execute..." statement script dies at "while (my=20
>> $rowref...". If I replace the placeholder with a literal it works as=20
>> expected.
>>
>> [code] snipped

I'm not sure if this is relevant because of differences in the database
type, but here goes...
We had a similar problem with an Oracle database, and from an item=20
on this list I found the following which fixed our issue.

"CHAR column values are padded with spaces to the length of the column.=20
When compared with a literal, the SQL engine automatically=20
adds the padding to the literal. When compared with a placeholder, the=20
blank padded semantics are not available unless the placeholder type is=20
CHAR."
=20
Evidently this is not unique to the Perl DBI, it was documented in the=20
Oracle OCI manual. Is it possible that SQLite is similar?
If so you may need something similar to this:

use DBD::Oracle qw(:ora_types);
... # after connect but before prepare
$dbh->{ora_ph_type} =3D ORA_CHAR;

FWIW, Hope it helps.
Paula

Re: select placeholder

Capacio, Paula J wrote:
> John wrote:
>
>>>Can anyone see the error in the following code. It has to do with the
>
>
>>>placeholder in the select statement. Substitution fails to occur at
>>>the "$sth1->execute..." statement script dies at "while (my
>>>$rowref...". If I replace the placeholder with a literal it works as
>>>expected.
>>>
>>>[code] snipped
>
>
> I'm not sure if this is relevant because of differences in the database
> type, but here goes...
> We had a similar problem with an Oracle database, and from an item
> on this list I found the following which fixed our issue.
>
> "CHAR column values are padded with spaces to the length of the column.
> When compared with a literal, the SQL engine automatically
> adds the padding to the literal. When compared with a placeholder, the
> blank padded semantics are not available unless the placeholder type is
> CHAR."
>
> Evidently this is not unique to the Perl DBI, it was documented in the
> Oracle OCI manual. Is it possible that SQLite is similar?
> If so you may need something similar to this:
>
> use DBD::Oracle qw(:ora_types);
> ... # after connect but before prepare
> $dbh->{ora_ph_type} = ORA_CHAR;
>
> FWIW, Hope it helps.
> Paula
Thanks Paula

I don't think that is the issue, in fact I don't think it is a DBI/SQL
issue at all because when I do this:
[code]
{
#$custnum = shift @duplist;
#redo if int($custnum) == 0;
$custnum = "000281610"; <===#######

$sth1->execute( $custnum ) or die "couldn't execute... " . DBI->errstr;

while (my $rowref = $sth1->fetchrow_arrayref) {
($asset, $custnum, $branch, $customer, $address, $town,
$postcode) = @$rowref;
[/code]

it works.

I will look into how I load @duplist and if I have further problems I
will take them elsewhere.

--
Regards
John McMahon (jmcm@bendigo.net.au)




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/198 - Release Date: 12/12/2005

RE: select placeholder

I take it the real problem is that your query is not returning any data.
Could it be that you're loading @duplist from a file, and it ends with a
newline? Try adding:

chomp $custnum;

before your execute() call.

-----Original Message-----
From: John [mailto:jmcm@bendigo.net.au]=20
Sent: Monday, December 12, 2005 6:14 PM
Cc: dbi-users@perl.org
Subject: Re: select placeholder


I don't think that is the issue, in fact I don't think it is a DBI/SQL
issue at all because when I do this:
[code]
{
#$custnum =3D shift @duplist;
#redo if int($custnum) == 0;
$custnum =3D "000281610"; <===3D#######

$sth1->execute( $custnum ) or die "couldn't execute... " .
DBI->errstr;

while (my $rowref =3D $sth1->fetchrow_arrayref) {
($asset, $custnum, $branch, $customer, $address, $town,
$postcode) =3D @$rowref;
[/code]

it works.

I will look into how I load @duplist and if I have further problems I
will take them elsewhere.

--
Regards
John McMahon (jmcm@bendigo.net.au)