DBD::mysql::st execute failed

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

This code worked under perl4 but when i upgraded the server it gives me this error DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 at /www/cgi-bin/recordsets/BaseRecordset.pm line 66. Any help will be apreciated.

      1 package BaseRecordset;
      2 use Carp;
      3
      4 use lib '/www/cgi-bin/libraries';
      5 use Library;
      6
      7 sub _initialize {
      8     my $self  = shift;
      9         $self->{'m_dbhConnection'} = $self->DefaultConnection(
+ );
     10         $self->{'m_strFilter'} = "";
     11         $self->{'m_strSort'} = "";
     12         $self->{'m_strGroup'} = "";
     13         $self->{'m_dbhHandle'} = "";
     14         $self->{'m_lRows'} = "0";
     15         $self->{'m_lPos'} = "0";
     16         $self->{'m_bEdit'} = "";
     17         $self->{'m_lCurrentId'} = "0";
     18 }
     19
     20 sub DefaultConnection {
     21         my $self = shift;
     22
     23         my $pLibrary = new Library;
     24         my $dbh = $pLibrary->GetDB();
     25
     26         return $dbh;
     27 }
     28
     29 sub Query {
     30         my $self = shift;
     31         my $lpszQuery = "select * from " . $self->GetDefaultQu
+ery();
     32
     33         if ( $self->m_strFilter ne "" ) {
     34                 $lpszQuery .= " where " . $self->m_strFilter;
     35         } else {
     36                 $lpszQuery .= " where id < '0'";
     37         }
     38
     39         if ( $self->m_strSort ne "" ) {
     40                 $lpszQuery .= " order by " . $self->m_strSort;
     41         }
     42
     43         if ( $self->m_strGroup ne "" ) {
     44                 $lpszQuery .= " group by " . $self->m_strGroup
+;
     45         }
     46
     47         return $lpszQuery;
     48 }
     49
     50 sub Open {
     51         my $self = shift;
     52
     53         $self->Execute( $self->Query( ) , "True" );
     54         $self->m_lPos( 0 );
     55         $self->m_lRows( $self->m_dbhHandle->rows );
     56         $self->DoDataExchange( );
     57 }
     58
     59 sub Execute {
     60         my $self = shift;
     61         my ( $lpszQuery , $bIsSelect ) = @_;
     62         my $dbhAlterHandle;
     63
     64         if ( $bIsSelect eq "True" ) {
     65                 $self->m_dbhHandle( $self->m_dbhConnection->pr
+epare( $lpszQuery ) );
     66                 $self->m_dbhHandle->execute;
     69         } else {
     70                 $dbhAlterHandle = $self->m_dbhConnection->prep
+are( $lpszQuery );
     71                 $dbhAlterHandle->execute;
     72                 $dbhAlterHandle->finish;
     73         }
     74
     75 }
     76
[download]

67, 68 omitted, they are comments Thanks

Comment on DBD::mysql::st execute failed Download Code

Replies are listed 'Best First'.
Re: DBD::mysql::st execute ...ZOMGWTFBBQ that's a terrible subject by Fletch (Bishop) on Oct 15, 2008 at 17:47 UTC
Considering perl 4 had neither lexical variables (`my`), nor the Perl 5 package mechanism (`use Foo`), nor DBI that's a rather surprising assertion to make . . . That being said, the error is not from perl but from your mysql database and you need to find out why it (meaning mysql) doesn't like the SQL statement you're passing it. Enabling tracing on your DBI handle (`$self->m_dbhConnection->trace( 2 )`) will probably be enlightening. Update: And a handy snippet for free nodelet JavaScript for train wreck subjects such as this . . . `// presumes JQuery available . . . function pm_trim_long_titles( ) { var candidate_tds = $('.main_content tbody > tr > td > a' ) candidate_tds.each( function( idx ) { var this_txt = $(this).html(); if( this_txt.length > 100 ) { var trimmed_txt = this_txt.substring( 0, 90 ) + "<i>[...trimmed] +</i>"; $(this).html( trimmed_txt ); } }); }` [download] The cake is a lie. The cake is a lie. The cake is a lie.	[reply] [d/l] [select]
Re: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 at /www/cgi-bin/recordsets/BaseRecordset.pm line 66. by Illuminatus (Curate) on Oct 15, 2008 at 18:03 UTC
I may be going out on a limb here, but I think jmccall meant mysql4, not perl4. That would explain the 'upgraded server' comment. Before you do anything, you need to add the following after line 63: `print "query is $lpszQuery\n";` [download] And verify that the SQL is still valid for the version of mysql that you are currently using	[reply] [d/l]
Re: DBD::mysql::st ... line 66. by MidLifeXis (Monsignor) on Oct 15, 2008 at 17:50 UTC
~~I call BS. This looks like perl5 code through and through.~~¹ Perhaps printing out the SQL statement (the part you pass to `prepare` on line 65) might give a hint. 1 - Update: I think that limb that Illuminatus is on is pretty strong. I believe that I will also climb out onto it. --MidLifeXis	[reply] [d/l]
Re: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 at /www/cgi-bin/recordsets/BaseRecordset.pm line 66. by jmccall (Initiate) on Oct 15, 2008 at 20:57 UTC
Sorry, You're right it was perl 5 on the old machine, I inherited this and the server went down over the weekend. I've been transfering all the old scripts onto the new machine and this is the last issue I am having. Thanks for the info I will look into these suggestions.	[reply]
Re: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 at /www/cgi-bin/recordsets/BaseRecordset.pm line 66. by gone2015 (Deacon) on Oct 15, 2008 at 17:55 UTC
Perl4. Goodness. For completeness, `use strict` and `use warnings` -- often help in the face of the apparently inexplicable. 67, 68 omitted, they are comments You're absolutely right. You can over do it with comments :-)	[reply] [d/l] [select]