----- Original Message -----
From: "kbass" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, November 20, 2003 3:53 PM
Subject: Please help: DBI Question
> 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. Thanks!
>
> Kevin
>
> Code below
> ==========
>
> 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>
> ...
>
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;
...
