RE: Newbie question concerning DBI

[timothy . helck]

Dana,

Generally I prefer to cat together my select statement as a string, then use
prepare(). Like this:
        my $SQL_sth1 = q
        {       SELECT * 
                FROM coverage_primary_loan
                WHERE certificate_number = $certificate
        };

        my $sth1 = $dbh1->prepare($SQL_sth1);

Now you can print $SQL_sth1 and you can find many obvious errors this way.

But there's a better way to do what you're doing: placeholders and bind
variables. I urge you to buy and read "Programming the Perl DBI" from
O'Reilly Press. It has pretty much everything you need to know about this
great tool.

Tim Helck


-----Original Message-----
From: Dana Lucent [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 09, 2002 9:41 AM
To: [EMAIL PROTECTED]
Subject: Newbie question concerning DBI


Hello,

 

I am currently attempting to read data from a text file that contains 1
column of numbers, use each record that was read to query a database.
However, (depending 

Upon how the code is written) I receive an error message that states
that my SQL statement is contains an error.  I am enclosing a sample of
the code in the hopes

that I can obtain some assistance.

 

ERROR MESSAGE:

 

Mysql::st execute failed: You have an error in your SQL syntax near '' 

> at line 1 at MREP_PCM.pl line 41, <DATAFILE> line 16. Couldn't execute
the pmiCentral statement at mrep_pcm-org.pl line 41, <DATAFILE> line 16.

 

CODE

 

#Open the DBI Database Interface module

use DBI;

 

#Open the database "pmicentral" for reading.

$dbh1 = DBI->connect("DBI:mysql:database=pmicentral:host=192.168.0.111",

                              "dlucent","hacnooto") || die "could not
connect to pmicentral";

 

#Open the database "MREP_PCM" for reading.

$dbh2 = DBI->connect("DBI:mysql:database=MREP_PCM:host=192.168.0.111",

                              "dlucent","hacnooto") || die "could not
connect to MREP_PCM";

 

#If the database connection is established, print the following line,

#else end the program with the DIE command.

print "Database connection established.";

 

 

 

# Open file and grab the CERTIFICATE numbers

$datafile = "CERT.TXT";

open(DATAFILE, $datafile);

#After next line I should have an array with the numbers from CERT.TXT

chomp(@cert = <DATAFILE>);

 

 

 

$resultfile = "> MREP_PCM.TXT";

open(OUTPUTFILE, $resultfile);

 

foreach $certificate (@cert) {  # The next line is where the error
occurs

      

      my $sth1 = $dbh1->prepare( "SELECT * FROM coverage_primary_loan
WHERE certificate_number = $certificate" );

      ##Prepare the SELECT SQL statement for MREP_PCM for obtaining the
data

      ##my $sth2 = $dbh2->prepare( "SELECT $file FROM mreppcm" );

      $sth1->execute || die "Couldn't execute the pmiCentral statement";

      ##$sth2->execute || die "Couldn't execute the MREP_PCM statement";

 

# Open file for output of ERROR Certificate numbers

 

      print OUTPUTFILE;

      print OUTPUTFILE "$certificate \n";

 

}

# One more thing, disconnect from the database server and output file

# when done.

 

$dbh1->disconnect;

$dbh2->disconnect;

close(OUTPUTFILE);