DBI Question

Discussion in 'Perl Misc' started by kbass, Nov 20, 2003.

  1. kbass

    kbass Guest

    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>
    ...
    kbass, Nov 20, 2003
    #1
    1. Advertising

  2. kbass

    kbass Guest

    "kbass" <> wrote in message
    news:NIavb.30934$...
    > 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>
    > ...
    >
    >


    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
    kbass, Nov 20, 2003
    #2
    1. Advertising

  3. kbass

    kbass Guest

    "kbass" <> wrote in message
    news:ZXavb.30935$...
    >
    > "kbass" <> wrote in message
    > news:NIavb.30934$...
    > > 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>
    > > ...
    > >
    > >

    >
    > 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
    >
    >


    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.
    kbass, Nov 20, 2003
    #3
  4. kbass <> wrote:

    > I am receiving a variable from HTML



    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");



    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?


    --
    Tad McClellan SGML consulting
    Perl programming
    Fort Worth, Texas
    Tad McClellan, Nov 20, 2003
    #4
  5. kbass

    kbass Guest

    "Tad McClellan" <> wrote in message
    news:...
    > kbass <> wrote:
    >
    > > I am receiving a variable from HTML

    >
    >
    > 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");

    >
    >
    > 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?
    >
    >
    > --
    > Tad McClellan SGML consulting
    > Perl programming
    > Fort Worth, Texas


    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;
    ....
    kbass, Nov 20, 2003
    #5
  6. kbass

    cp Guest

    In article <oycvb.30938$>, kbass
    <> wrote:

    > 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;


    [ 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;


    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.


    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 ...

    [snip SQL staement ]

    > from fy04q1bulletin
    > where subdist = ?


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


    this is not needed if you are using placeholders.

    > $sth->execute($territory_quoted);
    >

    [ snip rest ]

    --
    cp
    cp, Nov 21, 2003
    #6
  7. kbass

    kbass Guest

    "cp" <> wrote in message
    news:211120031015241008%...
    > In article <oycvb.30938$>, kbass
    > <> wrote:
    >
    > > 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;

    >
    > [ 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;

    >
    > 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.

    >
    > 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 ...

    > [snip SQL staement ]
    >
    > > from fy04q1bulletin
    > > where subdist = ?

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

    >
    > this is not needed if you are using placeholders.
    >
    > > $sth->execute($territory_quoted);
    > >

    > [ snip rest ]
    >
    > --
    > cp



    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
    kbass, Nov 23, 2003
    #7
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    515
    Juha Laiho
    Jul 22, 2004
  2. Dave Halliday
    Replies:
    3
    Views:
    174
    Dave Halliday
    Sep 12, 2003
  3. Jerome Hauss
    Replies:
    0
    Views:
    161
    Jerome Hauss
    Oct 13, 2004
  4. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    167
  5. Tim Haynes
    Replies:
    3
    Views:
    132
    Ron Reidy
    Sep 13, 2003
Loading...

Share This Page