Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

SQL error

by Anonymous Monk
on Aug 13, 2004 at 13:07 UTC ( #382652=perlquestion: print w/replies, xml ) Need Help??

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;

    It is more readable and you will get a more detailed error message...
      #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);
      Here is the exact code but I am still receiveing the same error.
        Try this instead:
        $stg = $dbh->prepare( q{SELECT REFERENCE, REQUIREMENT FROM TestCasesOutput WHERE PassFail += 'FAIL'} );

        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

        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?
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.
      #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);

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

        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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://382652]
Approved by csuhockey3
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (6)
As of 2020-01-22 06:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?