PERL - connect to db2 database using dbi?

07-10-2003, 07:44 PM

I am trying to execute a query on a db2 database using the dbi module. I'm wondering if someone can take a look at my code and tell me what I'm doing wrong. I'm assuming it's a problem with my query, but my query works fine in access, so I don't know. I guess I don't really understand how queries work differently in db2. I typically connect to a sql database.

Here is the error I'm getting:
Couldn't execute query: [IBM][CLI Driver][DB2] SQL0204N "(my userid here).ADXSPIT_SPI_MASTER" is an undefined name. SQLSTATE=42704
(SQL-S0002)(DBD: st_execute/SQLExecute err=-1)

My database name is DDXSPI1D, residing on server DB239. ADXSPIT.ADXSPIT_SPI_MASTER is the table name.

Here is my code:

#!/usr/bin/perl

# Include necessary modules
use CGI;
use CGI::Carp ("fatalsToBrowser");
use IO::File;
use DBI;

# create our CGI
$query = new CGI;

print "Content-Type: text/html\n\n";
print $query->start_html(-title=>"SLA DB2 TEST",);

#Include config and database info
require("../config/slaconfig.txt");

# Get SLA that is passed into this page
$ProjectNum=$query->param('ProjectNum');

# If the ProjectNum is undefined, just print out
# the form to search for a ProjectNum
if ($ProjectNum == undef)
{

print<<EOM;
<body>
<center>
<h1>SLA DB2 Project Test</h1>
<form name="GetProj" method="post" action="../../cgi-bin/lrrform_DB2.pl">
Please enter a Project Number: <input type="text" size="10" name="ProjectNum">
<p><input type="submit" value="Submit"> <input type="reset" value="Clear Form">
</p>

</form>
</center>
</body>
</html>
EOM
}
else
{
# Connect to the database
$dbh = DBI->connect("dbi:ODBC

B239", $PROJUSER, $PROJPassword)
or die "Couldn't connect to database: $DBI::errstr\n";

$DB2Query="SELECT ADXSPIT.PROJECT_NUM, ADXSPIT.PROJECT_NAME, ADXSPIT.PROJECT_DESC FROM ADXSPIT.ADXSPIT_SPI_MASTER where
ADXSPIT.PROJECT_NUM=$ProjectNum;";

#query database
$dbquery=$dbh->prepare($DB2Query) ||
die "Prepare failed: $DBI::errstr\n";

$dbquery->execute() ||
die "Couldn't execute query: $DBI::errstr\n";

$ProjInfo=$dbquery->fetchrow_array;

$dbquery->finish();

#print results
print <<EOM;
<body>
<center>
<h1>SLA DB2 Project Test RESULTS</h1>
This is cool!
</p>

</form>
</center>
</body>
</html>
EOM
print $ProjInfo;
}

Kate Perry

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

07-10-2003, 07:44 PM	#1
	connect to db2 database using dbi? I am trying to execute a query on a db2 database using the dbi module. I'm wondering if someone can take a look at my code and tell me what I'm doing wrong. I'm assuming it's a problem with my query, but my query works fine in access, so I don't know. I guess I don't really understand how queries work differently in db2. I typically connect to a sql database. Here is the error I'm getting: Couldn't execute query: [IBM][CLI Driver][DB2] SQL0204N "(my userid here).ADXSPIT_SPI_MASTER" is an undefined name. SQLSTATE=42704 (SQL-S0002)(DBD: st_execute/SQLExecute err=-1) My database name is DDXSPI1D, residing on server DB239. ADXSPIT.ADXSPIT_SPI_MASTER is the table name. Here is my code: #!/usr/bin/perl # Include necessary modules use CGI; use CGI::Carp ("fatalsToBrowser"); use IO::File; use DBI; # create our CGI $query = new CGI; print "Content-Type: text/html\n\n"; print $query->start_html(-title=>"SLA DB2 TEST",); #Include config and database info require("../config/slaconfig.txt"); # Get SLA that is passed into this page $ProjectNum=$query->param('ProjectNum'); # If the ProjectNum is undefined, just print out # the form to search for a ProjectNum if ($ProjectNum == undef) { print<<EOM; <body> <center> <h1>SLA DB2 Project Test</h1> <form name="GetProj" method="post" action="../../cgi-bin/lrrform_DB2.pl"> Please enter a Project Number: <input type="text" size="10" name="ProjectNum"> <p><input type="submit" value="Submit"> <input type="reset" value="Clear Form"> </p> </form> </center> </body> </html> EOM } else { # Connect to the database $dbh = DBI->connect("dbi:ODBCB239", $PROJUSER, $PROJPassword) or die "Couldn't connect to database: $DBI::errstr\n"; $DB2Query="SELECT ADXSPIT.PROJECT_NUM, ADXSPIT.PROJECT_NAME, ADXSPIT.PROJECT_DESC FROM ADXSPIT.ADXSPIT_SPI_MASTER where ADXSPIT.PROJECT_NUM=$ProjectNum;"; #query database $dbquery=$dbh->prepare($DB2Query) \|\| die "Prepare failed: $DBI::errstr\n"; $dbquery->execute() \|\| die "Couldn't execute query: $DBI::errstr\n"; $ProjInfo=$dbquery->fetchrow_array; $dbquery->finish(); #print results print <<EOM; <body> <center> <h1>SLA DB2 Project Test RESULTS</h1> This is cool! </p> </form> </center> </body> </html> EOM print $ProjInfo; } Kate Perry