DBI Question

K

kbass

I am receiving a variable from HTML called 'STerr' and reading it into my
CGI program. The variable is accepted into the program and filters down into
a SQL statement. My problem is that I get no results from within my CGI
program but when I place this same SQL statement within Postgres, I receive
data back.

If I get rid of the $dbh->quote, the SQL statement still returns no data. I
have attempted to set $territory and $territory_quoted within my SQL
statements and still no data. What am I doing incorrect to not get data back
within my CGI program but retrieve data from the Postgres backend? The
database is Postgres.

use strict
....

$territory = param("STerr");
#$territory = '1-1-1-1-1-2-0'; # Sample data

View_Info();

##############################
# Procedure: View_Info #
##############################
sub View_Info {

$sql = "select fyear,
fqtr,
adjtype,
adjref,
subdist,
subdate,
adjgoalvolqty
from fy04q1bulletin
where subdist = ?
";

$sth = $dbh->prepare($sql);
my $territory_quoted = $dbh->quote( $territory);
$sth->execute($territory_quoted);

$sth->bind_columns(undef, \( $fyear, $fqtr, $adjtype, $adjref,
$subdist,
$subdate, $aprreason, $custid, $custname, $custtype, $adjstatus,
$declreason, $
proccomments, $dispflag, $losingorg, $losingterr, $gainorg, $gainterr,
$revfilte
rtype, $revfilterdesc, $adjrevamt, $adjpackqty, $adjprvyear,
$adjpackprvyear, $a
djgoalrevamt, $adjgoalvolqty ));

....

while (@rows= $sth->fetchrow_array) {
print<<HTML;
<tr>
<td><center>$rows[0]</center></td>
<td><center>$rows[1]</center></td>
<td><center>$rows[2]</center></td>
<td><center>$rows[3]</center></td>
...
 
K

kbass

kbass said:
I am receiving a variable from HTML called 'STerr' and reading it into my
CGI program. The variable is accepted into the program and filters down into
a SQL statement. My problem is that I get no results from within my CGI
program but when I place this same SQL statement within Postgres, I receive
data back.

If I get rid of the $dbh->quote, the SQL statement still returns no data. I
have attempted to set $territory and $territory_quoted within my SQL
statements and still no data. What am I doing incorrect to not get data back
within my CGI program but retrieve data from the Postgres backend? The
database is Postgres.

use strict
...

$territory = param("STerr");
#$territory = '1-1-1-1-1-2-0'; # Sample data

View_Info();

##############################
# Procedure: View_Info #
##############################
sub View_Info {

$sql = "select fyear,
fqtr,
adjtype,
adjref,
subdist,
subdate,
adjgoalvolqty
from fy04q1bulletin
where subdist = ?
";

$sth = $dbh->prepare($sql);
my $territory_quoted = $dbh->quote( $territory);
$sth->execute($territory_quoted);

$sth->bind_columns(undef, \( $fyear, $fqtr, $adjtype, $adjref,
$subdist,
$subdate, $aprreason, $custid, $custname, $custtype, $adjstatus,
$declreason, $
proccomments, $dispflag, $losingorg, $losingterr, $gainorg, $gainterr,
$revfilte
rtype, $revfilterdesc, $adjrevamt, $adjpackqty, $adjprvyear,
$adjpackprvyear, $a
djgoalrevamt, $adjgoalvolqty ));

...

while (@rows= $sth->fetchrow_array) {
print<<HTML;
<tr>
<td><center>$rows[0]</center></td>
<td><center>$rows[1]</center></td>
<td><center>$rows[2]</center></td>
<td><center>$rows[3]</center></td>
...
Click to expand...

As a side note, when I add the $dbh->trace functionality and the $dbh->quote
used in my CGI program, I get information the message below. Without the
$dbh->quote used, I received an error message and get the information
message below too.

I didn't place the entire trace file messages into this posting since it is
fairly large. Hope this helps. Thanks!

Kevin
-------------------------------------------------------
Within database trace on & $dbh->quote used
-------------------------------------------------------
....
<- STORE= 1 at PgPP.pm line 173 via
/home/salesplan/website/cgi-bin/abb1.cgi
line 125
-> STORE for DBD::pgPP::st (DBI::st=HASH(0x851e038)~0x8114278
'NUM_OF_PARAMS
' 1)
STORE DBI::st=HASH(0x8114278) 'NUM_OF_PARAMS' => 1
<- STORE= 1 at PgPP.pm line 174 via
/home/salesplan/website/cgi-bin/abb1.cgi
line 125
<- prepare= DBI::st=HASH(0x851e038) at abb1.cgi line 125 via
/home/salesplan
/website/cgi-bin/abb1.cgi line 82
-> quote in DBD::_::db for DBD::pgPP::db
(DBI::db=HASH(0x851870c)~0x8514034
'1-1-1-1-1-2-0')
<- quote= ''1-1-1-1-1-2-0'' at abb1.cgi line 126 via
/home/salesplan/website
/cgi-bin/abb1.cgi line 82
-> execute for DBD::pgPP::st (DBI::st=HASH(0x851e038)~0x8114278
''1-1-1-1-1-
2-0'')
1 <- FETCH= 1 ('NUM_OF_PARAMS' from cache) at PgPP.pm line 281 via
/home/sales
plan/website/cgi-bin/abb1.cgi line 127
-> quote for DBD::pgPP::db (DBI::db=HASH(0x8514034)~INNER
''1-1-1-1-1-2-0'')
<- quote= ''''1-1-1-1-1-2-0'''' at PgPP.pm line 288 via
/home/salesplan/webs
ite/cgi-bin/abb1.cgi line 127
1 <- FETCH= DBD::pgPP::protocol=HASH(0x84e086c) ('pgpp_handle' from cache)
at
PgPP.pm line 291 via /home/salesplan/website/cgi-bin/abb1.cgi line 127
1 -> STORE for DBD::pgPP::st (DBI::st=HASH(0x8114278)~INNER
'NUM_OF_FIELDS' 26
)
STORE DBI::st=HASH(0x8114278) 'NUM_OF_FIELDS' => 26
1 <- STORE= 1 at PgPP.pm line 309 via
/home/salesplan/website/cgi-bin/abb1.cgi
line 82
1 -> STORE for DBD::pgPP::st (DBI::st=HASH(0x8114278)~INNER 'NAME'
ARRAY(0x854
7fe8))
1 <- STORE= 1 at PgPP.pm line 310 via
/home/salesplan/website/cgi-bin/abb1.cgi
line 82
<- execute= '0E0' at abb1.cgi line 127 via
/home/salesplan/website/cgi-bin/a
bb1.cgi line 82
.....

---------------------------------------------------------------
Within database trace on & within $dbh->quote used
----------------------------------------------------------------
-> STORE for DBD::pgPP::st (DBI::st=HASH(0x851e038)~0x8114278 'NUM_OF_PARAMS
' 1)
STORE DBI::st=HASH(0x8114278) 'NUM_OF_PARAMS' => 1
<- STORE= 1 at PgPP.pm line 174 via
/home/salesplan/website/cgi-bin/abb1.cgi
line 125
<- prepare= DBI::st=HASH(0x851e038) at abb1.cgi line 125 via
/home/salesplan
/website/cgi-bin/abb1.cgi line 82
-> quote in DBD::_::db for DBD::pgPP::db
(DBI::db=HASH(0x851870c)~0x8514034
'1-1-1-1-1-2-0')
<- quote= ''1-1-1-1-1-2-0'' at abb1.cgi line 126 via
/home/salesplan/website
/cgi-bin/abb1.cgi line 82
-> execute for DBD::pgPP::st (DBI::st=HASH(0x851e038)~0x8114278
'1-1-1-1-1-2
-0')
1 <- FETCH= 1 ('NUM_OF_PARAMS' from cache) at PgPP.pm line 281 via
/home/sales
plan/website/cgi-bin/abb1.cgi line 127
-> quote for DBD::pgPP::db (DBI::db=HASH(0x8514034)~INNER
'1-1-1-1-1-2-0')
<- quote= ''1-1-1-1-1-2-0'' at PgPP.pm line 288 via
/home/salesplan/website/
cgi-bin/abb1.cgi line 127
1 <- FETCH= DBD::pgPP::protocol=HASH(0x84e086c) ('pgpp_handle' from cache)
at
PgPP.pm line 291 via /home/salesplan/website/cgi-bin/abb1.cgi line 127
!! ERROR: 1 'Unknown message type: '.' at
/usr/local/lib/perl5/site_perl/5.8
..0/i686-linux/DBD/PgPP.pm line 634
'
<- execute= undef at abb1.cgi line 127 via
/home/salesplan/website/cgi-bin/a
bb1.cgi line 82
 
K

kbass

kbass said:
kbass said:
I am receiving a variable from HTML called 'STerr' and reading it into my
CGI program. The variable is accepted into the program and filters down into
a SQL statement. My problem is that I get no results from within my CGI
program but when I place this same SQL statement within Postgres, I receive
data back.

If I get rid of the $dbh->quote, the SQL statement still returns no
Click to expand...
data.
I
have attempted to set $territory and $territory_quoted within my SQL
statements and still no data. What am I doing incorrect to not get data back
within my CGI program but retrieve data from the Postgres backend? The
database is Postgres.

use strict
...

$territory = param("STerr");
#$territory = '1-1-1-1-1-2-0'; # Sample data

View_Info();

##############################
# Procedure: View_Info #
##############################
sub View_Info {

$sql = "select fyear,
fqtr,
adjtype,
adjref,
subdist,
subdate,
adjgoalvolqty
from fy04q1bulletin
where subdist = ?
";

$sth = $dbh->prepare($sql);
my $territory_quoted = $dbh->quote( $territory);
$sth->execute($territory_quoted);

$sth->bind_columns(undef, \( $fyear, $fqtr, $adjtype, $adjref,
$subdist,
$subdate, $aprreason, $custid, $custname, $custtype, $adjstatus,
$declreason, $
proccomments, $dispflag, $losingorg, $losingterr, $gainorg, $gainterr,
$revfilte
rtype, $revfilterdesc, $adjrevamt, $adjpackqty, $adjprvyear,
$adjpackprvyear, $a
djgoalrevamt, $adjgoalvolqty ));

...

while (@rows= $sth->fetchrow_array) {
print<<HTML;
<tr>
<td><center>$rows[0]</center></td>
<td><center>$rows[1]</center></td>
<td><center>$rows[2]</center></td>
<td><center>$rows[3]</center></td>
...
Click to expand...

As a side note, when I add the $dbh->trace functionality and the $dbh->quote
used in my CGI program, I get information the message below. Without the
$dbh->quote used, I received an error message and get the information
message below too.

I didn't place the entire trace file messages into this posting since it is
fairly large. Hope this helps. Thanks!

Kevin
-------------------------------------------------------
Within database trace on & $dbh->quote used
-------------------------------------------------------
...
<- STORE= 1 at PgPP.pm line 173 via
/home/salesplan/website/cgi-bin/abb1.cgi
line 125
-> STORE for DBD::pgPP::st (DBI::st=HASH(0x851e038)~0x8114278
'NUM_OF_PARAMS
' 1)
STORE DBI::st=HASH(0x8114278) 'NUM_OF_PARAMS' => 1
<- STORE= 1 at PgPP.pm line 174 via
/home/salesplan/website/cgi-bin/abb1.cgi
line 125
<- prepare= DBI::st=HASH(0x851e038) at abb1.cgi line 125 via
/home/salesplan
/website/cgi-bin/abb1.cgi line 82
-> quote in DBD::_::db for DBD::pgPP::db
(DBI::db=HASH(0x851870c)~0x8514034
'1-1-1-1-1-2-0')
<- quote= ''1-1-1-1-1-2-0'' at abb1.cgi line 126 via
/home/salesplan/website
/cgi-bin/abb1.cgi line 82
-> execute for DBD::pgPP::st (DBI::st=HASH(0x851e038)~0x8114278
''1-1-1-1-1-
2-0'')
1 <- FETCH= 1 ('NUM_OF_PARAMS' from cache) at PgPP.pm line 281 via
/home/sales
plan/website/cgi-bin/abb1.cgi line 127
-> quote for DBD::pgPP::db (DBI::db=HASH(0x8514034)~INNER
''1-1-1-1-1-2-0'')
<- quote= ''''1-1-1-1-1-2-0'''' at PgPP.pm line 288 via
/home/salesplan/webs
ite/cgi-bin/abb1.cgi line 127
1 <- FETCH= DBD::pgPP::protocol=HASH(0x84e086c) ('pgpp_handle' from cache)
at
PgPP.pm line 291 via /home/salesplan/website/cgi-bin/abb1.cgi line 127
1 -> STORE for DBD::pgPP::st (DBI::st=HASH(0x8114278)~INNER
'NUM_OF_FIELDS' 26
)
STORE DBI::st=HASH(0x8114278) 'NUM_OF_FIELDS' => 26
1 <- STORE= 1 at PgPP.pm line 309 via
/home/salesplan/website/cgi-bin/abb1.cgi
line 82
1 -> STORE for DBD::pgPP::st (DBI::st=HASH(0x8114278)~INNER 'NAME'
ARRAY(0x854
7fe8))
1 <- STORE= 1 at PgPP.pm line 310 via
/home/salesplan/website/cgi-bin/abb1.cgi
line 82
<- execute= '0E0' at abb1.cgi line 127 via
/home/salesplan/website/cgi-bin/a
bb1.cgi line 82
....

---------------------------------------------------------------
Within database trace on & within $dbh->quote used
----------------------------------------------------------------
-> STORE for DBD::pgPP::st (DBI::st=HASH(0x851e038)~0x8114278 'NUM_OF_PARAMS
' 1)
STORE DBI::st=HASH(0x8114278) 'NUM_OF_PARAMS' => 1
<- STORE= 1 at PgPP.pm line 174 via
/home/salesplan/website/cgi-bin/abb1.cgi
line 125
<- prepare= DBI::st=HASH(0x851e038) at abb1.cgi line 125 via
/home/salesplan
/website/cgi-bin/abb1.cgi line 82
-> quote in DBD::_::db for DBD::pgPP::db
(DBI::db=HASH(0x851870c)~0x8514034
'1-1-1-1-1-2-0')
<- quote= ''1-1-1-1-1-2-0'' at abb1.cgi line 126 via
/home/salesplan/website
/cgi-bin/abb1.cgi line 82
-> execute for DBD::pgPP::st (DBI::st=HASH(0x851e038)~0x8114278
'1-1-1-1-1-2
-0')
1 <- FETCH= 1 ('NUM_OF_PARAMS' from cache) at PgPP.pm line 281 via
/home/sales
plan/website/cgi-bin/abb1.cgi line 127
-> quote for DBD::pgPP::db (DBI::db=HASH(0x8514034)~INNER
'1-1-1-1-1-2-0')
<- quote= ''1-1-1-1-1-2-0'' at PgPP.pm line 288 via
/home/salesplan/website/
cgi-bin/abb1.cgi line 127
1 <- FETCH= DBD::pgPP::protocol=HASH(0x84e086c) ('pgpp_handle' from cache)
at
PgPP.pm line 291 via /home/salesplan/website/cgi-bin/abb1.cgi line 127
!! ERROR: 1 'Unknown message type: '.' at
/usr/local/lib/perl5/site_perl/5.8
.0/i686-linux/DBD/PgPP.pm line 634
'
<- execute= undef at abb1.cgi line 127 via
/home/salesplan/website/cgi-bin/a
bb1.cgi line 82
Click to expand...

The error message points to the line featuring the $sth->bind_columns()
statement.
Statement has no result columns to bind (perhaps you need to successfully
call execute first) at /home/salesplan/website/cgi-bin/abb1.cgi line 129.
 
T

Tad McClellan

kbass said:
I am receiving a variable from HTML
Click to expand...


No you're not, as that is impossible.

You meant you are receiving _values_ from a web server, I expect.

use strict
...

$territory = param("STerr");
Click to expand...


If this is your real code, then where is the declaration
for that variable (and for all of the others too)?

If this in not your real code, then why is it even here?
 
K

kbass

Tad McClellan said:
No you're not, as that is impossible.

You meant you are receiving _values_ from a web server, I expect.




If this is your real code, then where is the declaration
for that variable (and for all of the others too)?

If this in not your real code, then why is it even here?
Click to expand...

No, I posted a snippet of the code. Here is part of the real code if it will
help you.

use DBI qw:)sql_types);
use strict;
use diagnostics; #debugging purposes
use CGI qw:)standard);
use CGI::Carp qw(fatalsToBrowser);
use VCP::Connect;

my (%files,
$dbh,
$sth,
$data,
@rows,
$sql,
$territory,
$table_name,
$excel_sheet,
$output_file,
$fyear,
$fqtr,
$adjtype,
$adjref,
$subdist,
$subdate,
$aprreason,
$custid,
$custname,
$custtype,
$adjstatus,
$declreason,
$proccomments,
$dispflag,
$losingorg,
$losingterr,
$gainorg,
$gainorg,
$gainterr,
$revfiltertype,
$revfilterdesc,
$adjrevamt,
$adjpackqty,
$adjprvyear,
$adjpackprvyear,
$adjgoalrevamt,
$adjgoalvolqty,
$adjustment
);

# Autoflush the output buffer
$| = 1;

# Establish database connections for the CompAlign and Sales databases.
$dbh = Connect->compalignDB;

# Check action variable. If this CGI program is accessed without a
parameter
# within the URL, the default value of 'view' will be given. This is a work
# around that for the '... Use of uninitialized value in string' error
# message.
$territory = param("STerr");
$table_name = param("table");
$adjustment = param("anbr");

#$territory = '1-1-1-1-1-2-0';

View_Info();

##############################
# Procedure: View_Info #
##############################
sub View_Info {

# IMPORTANT - This function is for debugging purposes only.
# Make sure this function is comments after use.
#unlink 'dbtrace.log' if -e 'dbtrace.log';
#DBI->trace( 4, 'dbtrace.log' );


$sql = "select fyear,
fqtr,
adjtype,
adjref,
subdist,
subdate,
aprreason,
custid,
custname,
custtype,
adjstatus,
declreason,
proccomments,
dispflag,
losingorg,
losingterr,
gainorg,
gainterr,
revfiltertype,
revfilterdesc,
adjrevamt,
adjpackqty,
adjprvyear,
adjpackprvyear,
adjgoalrevamt,
adjgoalvolqty
from fy04q1bulletin
where subdist = ?
";

$sth = $dbh->prepare($sql);
my $territory_quoted = $dbh->quote( $territory);
$sth->execute($territory_quoted);

$sth->bind_columns(undef, \( $fyear, $fqtr, $adjtype, $adjref,
$subdist,
$subdate, $aprreason, $custid, $custname, $custtype, $adjstatus,
$declreason, $
proccomments, $dispflag, $losingorg, $losingterr, $gainorg, $gainterr,
$revfilte
rtype, $revfilterdesc, $adjrevamt, $adjpackqty, $adjprvyear,
$adjpackprvyear, $a
djgoalrevamt, $adjgoalvolqty ));

print header;
....
 
C

cp

kbass said:
No, I posted a snippet of the code. Here is part of the real code if it will
help you.

use DBI qw:)sql_types);
use strict;
use diagnostics; #debugging purposes
use CGI qw:)standard);
use CGI::Carp qw(fatalsToBrowser);
use VCP::Connect;
Click to expand...

[ snip impossibly long list of variables ]

Don't do that. Declare them as you need them, in the smallest possible
scope. If you don't need them until a sub, delcare them inside in the
sub. It's so hard to debug otherwise.
# Autoflush the output buffer
$| = 1;

# Establish database connections for the CompAlign and Sales databases.
$dbh = Connect->compalignDB;
Click to expand...

In your original post, you gave the clue to what is happening. It ran
from the command line, but not from the Web server. This connection
string looks suspiciously like it gets default values from the user's
environment. The Web server runs as a different user than you do, and
it definitely does not have those same default values. You'd need to
provide them. See perldoc DBI.

At the VERY least, CHECK to see if the connection works:

# one way
die "Unable to connect to compalignDB" unless $dbh;

Better set RaiseError or PrintError in your connection package.
# Check action variable. If this CGI program is accessed without a
parameter
# within the URL, the default value of 'view' will be given. This is a work
# around that for the '... Use of uninitialized value in string' error
# message.
Click to expand...

warning message. Errors cause programs to terminate. Warnings try to
keep you from doing something stupid.

$territory = param("STerr");
$table_name = param("table");
$adjustment = param("anbr");

#$territory = '1-1-1-1-1-2-0';

View_Info();

##############################
# Procedure: View_Info #
##############################
sub View_Info {

# IMPORTANT - This function is for debugging purposes only.
# Make sure this function is comments after use.
#unlink 'dbtrace.log' if -e 'dbtrace.log';
#DBI->trace( 4, 'dbtrace.log' );


$sql = "select fyear ...
Click to expand...
[snip SQL staement ]
from fy04q1bulletin
where subdist = ?
Click to expand...
$sth = $dbh->prepare($sql);
my $territory_quoted = $dbh->quote( $territory);
Click to expand...

this is not needed if you are using placeholders.
$sth->execute($territory_quoted);
Click to expand...
[ snip rest ]
 
K

kbass

cp said:
kbass said:
No, I posted a snippet of the code. Here is part of the real code if it will
help you.

use DBI qw:)sql_types);
use strict;
use diagnostics; #debugging purposes
use CGI qw:)standard);
use CGI::Carp qw(fatalsToBrowser);
use VCP::Connect;
Click to expand...

[ snip impossibly long list of variables ]

Don't do that. Declare them as you need them, in the smallest possible
scope. If you don't need them until a sub, delcare them inside in the
sub. It's so hard to debug otherwise.
# Autoflush the output buffer
$| = 1;

# Establish database connections for the CompAlign and Sales databases.
$dbh = Connect->compalignDB;
Click to expand...

In your original post, you gave the clue to what is happening. It ran
from the command line, but not from the Web server. This connection
string looks suspiciously like it gets default values from the user's
environment. The Web server runs as a different user than you do, and
it definitely does not have those same default values. You'd need to
provide them. See perldoc DBI.

At the VERY least, CHECK to see if the connection works:

# one way
die "Unable to connect to compalignDB" unless $dbh;

Better set RaiseError or PrintError in your connection package.
# Check action variable. If this CGI program is accessed without a
parameter
# within the URL, the default value of 'view' will be given. This is a work
# around that for the '... Use of uninitialized value in string' error
# message.
Click to expand...

warning message. Errors cause programs to terminate. Warnings try to
keep you from doing something stupid.

$territory = param("STerr");
$table_name = param("table");
$adjustment = param("anbr");

#$territory = '1-1-1-1-1-2-0';

View_Info();

##############################
# Procedure: View_Info #
##############################
sub View_Info {

# IMPORTANT - This function is for debugging purposes only.
# Make sure this function is comments after use.
#unlink 'dbtrace.log' if -e 'dbtrace.log';
#DBI->trace( 4, 'dbtrace.log' );


$sql = "select fyear ...
Click to expand...
[snip SQL staement ]
from fy04q1bulletin
where subdist = ?
Click to expand...
$sth = $dbh->prepare($sql);
my $territory_quoted = $dbh->quote( $territory);
Click to expand...

this is not needed if you are using placeholders.
$sth->execute($territory_quoted);
Click to expand...
[ snip rest ]
Click to expand...


Thanks for the responses. I have found the problem. The code was correct
but there was a problem (or bug) in the DBD::pgPP module. I used the
DBD::pg module and all problems got resolved. Thanks!

Kevin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping messages that have similarities 2
Fail extracting table from .mdb file using DBI module 6
Help with my responsive home page 2
Need suggestions to improve code 2
DBI query does not fetch all data 2
two attempts at DBI connect call 2
DBI MS SQL connection error 2
I need advice re mysqli dropdown 0

Members online

Total: 29 (members: 1, guests: 28)
Robots: 352

Forum statistics

Threads
473,755
Messages
2,569,537
Members
45,020
Latest member
GenesisGai

Latest Threads

Top