Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to do a select in perl to grab some information from an access database. The select statement works great until I put the where part in.

The table consists of TableName, Field1, Field2, Field3, Field4, Field5.(generic names).
The select statement is SELECT Field1, Field2 FROM TableName WHERE Field3 = "fail".
The error i get is DBD::ODBC::st execute failed: MicrosoftODBC Microsoft Access Driver Too few parameters. Expected 1.

Replies are listed 'Best First'.
Re: SQL error
by reneeb (Chaplain) on Aug 13, 2004 at 13:23 UTC
    This:
    $stg = $dbh->prepare('SELECT REFERENCE, REQUIREMENTS FROM TestCasesOutput WHERE PassFail = "fail";

    has to be
    $stg = $dbh->prepare('SELECT REFERENCE, REQUIREMENTS FROM TestCasesOutput WHERE PassFail = "fail"');

    It's better to write
    my $statement = 'SELECT REFERENCE, REQUIREMENTS FROM TestCasesOutput W
+HERE PassFail = "fail"';
my $stg = $dbh->prepare($statement);
$stg->execute() or die "An error occured while executing statement: ".
+$DBI::errstr;

    [download]

    It is more readable and you will get a more detailed error message...
[reply]
[d/l]
[select]
      
#Windows-based Perl/DBI/MS Access example
 use DBI;

#open connection to Access database
$dbh = DBI->connect( "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);
+dbq=C:TestCasesXP2K.mdb", "", "" );



#prepare and execute SQL statement
$stg = $dbh->prepare('SELECT REFERENCE, REQUIREMENT FROM TestCasesOutp
+ut WHERE PassFail = "FAIL"');
print "are we getting here";
$stg->execute || 
       die "Could not execute SQL statement ... maybe invalid?";
$sth = $dbh->prepare('SELECT REFERENCE FROM TestCasesOutput');
$sth->execute || 
       die "Could not execute SQL statement ... maybe invalid?";
#output database results
$stt = $dbh->prepare('SELECT REQUIREMENT FROM TestCasesOutput');
$stt->execute || 
       die "Could not execute SQL statement ... maybe invalid?";
#output database results
while (@row=$stg->fetchrow_array)  {
    @row1=$sth->fetchrow_array;
    @row2=$stt->fetchrow_array;
    open(fileOUT, ">>log.txt") or dienice("Can't open log.txt for writ
+ing: $!");
    flock(fileOUT, 2);
    seek(fileOUT, 0, 2);
    print fileOUT "Reference:   @row1\n";
    print fileOUT "Requirement:  @row2\n\n\n\n";
    }
close(fileOUT);

      [download]
      Here is the exact code but I am still receiveing the same error.
[reply]
[d/l]
        Try this instead: 
        $stg = $dbh->prepare(
  q{SELECT REFERENCE, REQUIREMENT FROM TestCasesOutput WHERE PassFail 
+= 'FAIL'}
);

        [download]

        Jenda
        Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.
           -- Rick Osborne

[reply]
[d/l]
        The over all goal is to grab certain information out of the database and export to ms word. Right now i am exporting to txt. Do yall know a why to export to MS Word?
[reply]
Re: SQL error
by deibyz (Hermit) on Aug 13, 2004 at 13:10 UTC
    Can you post your code? I guess you're having problems with string delimiters or something like that.
[reply]
      #Windows-based Perl/DBI/MS Access example
 use DBI;

#open connection to Access database
$dbh = DBI->connect( "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);
+dbq=C:TestCasesXP2K.mdb", "", "" );



#prepare and execute SQL statement
$stg = $dbh->prepare('SELECT REFERENCE, REQUIREMENTS FROM TestCasesOut
+put WHERE PassFail = "fail";
$stg->execute ||
       die "Could not execute SQL statement ... maybe invalid?";
"""""THE ERROR OCCURS RIGHT HERE""""" 
$sth = $dbh->prepare('SELECT REFERENCE FROM TestCasesOutput'); 
$sth->execute || 
       die "Could not execute SQL statement ... maybe invalid?"; 
#output database results 
$stt = $dbh->prepare('SELECT REQUIREMENT FROM TestCasesOutput');  
$stt->execute || 
       die "Could not execute SQL statement ... maybe invalid?"; 
#output database results 
while (@row=$stg->fetchrow_array)  { 
    @row1=$sth->fetchrow_array; 
    @row2=$stt->fetchrow_array; 
    open(fileOUT, ">>log.txt") or dienice("Can't open log.txt for writ
+ing: $!"); 
    flock(fileOUT, 2); 
    seek(fileOUT, 0, 2); 
    print fileOUT "Reference:   @row1\n"; 
    print fileOUT "Requirement:  @row2\n\n\n\n"; 
    } 
close(fileOUT);

      [download]

      janitore<d by ybiC: Remove HTML markup formatting and replace with balanced <code> tags, as per Monastery convention

[reply]
[d/l]
        Just what it seemed, close the quote in $stg = $dbh->prepare('SELECT REFERENCE, REQUIREMENTS FROM TestCasesOutput WHERE PassFail = "fail";

        Should be:

        $stg = $dbh->prepare('SELECT REFERENCE, REQUIREMENTS FROM TestCasesOutput WHERE PassFail = "fail"');

        BTW, try reading Writeups Formatting Tips and How do I post a question effectively?, they can help you a lot.

        deibyz

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom