Re^2: SQL error


#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.

Comment on Re^2: SQL error Download Code

Replies are listed 'Best First'.
Re^3: SQL error by Jenda (Abbot) on Aug 13, 2004 at 13:47 UTC
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]
Re^3: SQL error by Anonymous Monk on Aug 13, 2004 at 13:48 UTC
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^4: SQL error by bassplayer (Monsignor) on Aug 13, 2004 at 15:07 UTC
For creating MS Word documents on a Windows machine, look into the Win32::OLE module. There are plenty of code examples, in the documentation (mostly Excel but can easily be converted), on this site (try a Super Search), or via the all knowing google. bassplayer	[reply]
Re^4: SQL error by Anonymous Monk on Aug 13, 2004 at 13:53 UTC
I bleive that is going to work thank you. But what is the reasoning for putting the q and the brackets.	[reply]
Re^5: SQL error by Jenda (Abbot) on Aug 13, 2004 at 14:28 UTC
The q{} is irrelevant. The important change was the "FAIL" to 'FAIL'. I could have written it as `$stg = $dbh->prepare( 'SELECT REFERENCE, REQUIREMENT FROM TestCasesOutput WHERE PassFail = + \'FAIL\'' );` [download] or `$stg = $dbh->prepare( "SELECT REFERENCE, REQUIREMENT FROM TestCasesOutput WHERE PassFail = + 'FAIL'" );` [download] The problem was that only single quotes should be used to denote string literals in SQL. "Some Name" may be either a string or the name of a column or other object. Which is it depends on the database and its settings, but the later meaning is much more likely. I believe you got the misleading error message because Access tries to be helpfull and treats unknown column names as "query parameters". If you would run that query from within Access you'd get a popup dialog asking you to enter the value of "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	[reply] [d/l] [select]
Re^5: SQL error by TrekNoid (Pilgrim) on Aug 13, 2004 at 15:17 UTC
q{} treats the entire set of characters between the braces as single quoted. It's handy to use it for SQL because it removes the issue of getting confused and not closing all your quote marks in your SQL commands. So: `q{This is a string} 'This is a string'` [download] are essentially the same thing. qq{}, incidentally, works the same way, only with double quotes Trek	[reply] [d/l]