Ok, my best guess is that you have blank lines in your data file. You
may be passing an empty string to your prepare statement. What is on
line 16 of your datafile?
Also, at what point do you set the value of $file? You need to execute
and fetch the values from the first query into $file or you are oging to
get errors there too.
Try this alternative version of your code:
my $sth1 = $dbh1->prepare( "SELECT file FROM coverage_primary_loan
WHERE certificate_number = ?" ); # or whatever other columns you want
foreach $certificate (@cert) {
$sth1->execute($certificate);
my ($file) = $sth1->fetchrow_array;
my $sth2 = $dbh2->prepare( "SELECT $file FROM mreppcm");
$sth2->execute();
while (my @row = $sth2->fetchrow_array){
#do something with the data from $sth2
}
}
Note: This is off the top of the head code and is provided with no
warranty whatsoever. Your mileage will most likely vary. (Test it
yourself)
On Tue, 2002-07-09 at 09:40, Dana Lucent wrote:
> 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);
>
