My comments are below.
kbass [EMAIL PROTECTED] wrote:
>
> ----- 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);
As someone already pointed out (twice), when you use placeholders
(? and entering variables to be substituted into execute), you
don't need to also use 'quote' - you basically should use one
or the other, but not both. And IMHO, placeholders is the
clear winner. So, you code should look like:
$sql = qq{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 = ?
}; ### end $sql
$sth = $dbh->prepare($sql);
### *NO* need to use 'quote' here ###
#my $territory_quoted = $dbh->quote( $territory);
#$sth->execute($territory_quoted);
$sth->execute($territory);
>
> $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 ));
This bind_columns is confusing, and I don't see the need for the
undef as the 1st element. Why not do it like this:
$sth->bind_columns(\$fyear,
\$fqtr,
\$adjtype,
...
);
To me that seems much cleaner and much easier to understand.
HTH>
--
Hardy Merrill
Red Hat, Inc.
