Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

Maximum DBI query length?

by Merlin83 (Novice)
on May 07, 2001 at 21:27 UTC ( #78571=perlquestion: print w/replies, xml ) Need Help??
Merlin83 has asked for the wisdom of the Perl Monks concerning the following question:

I'm crafting quite a long SQL query to be run through DBI using the MySQL drivers. I have plenty of smaller ones working fine, but this one seems to get choped off after a number of characters.

Does anyone know of any limit to query lengths, or (more usefully) any way to make them longer or any workarounds?

Replies are listed 'Best First'.
Re: Maximum DBI query length?
by KM (Priest) on May 07, 2001 at 21:33 UTC
    I have never run across any limitations with MySQL (and I have had some large honking queries). Can you give us some code, and errors, and what makes you think (know) it is being chopped off? Let us see through your eyes, man! :)


      Very well. Here's the code that prepares the query....
      my $sthQuery = $dbh->prepare('SELECT contact.forename, contact.surname +, business.occupation, acn.position,, AS + forename, surname, occupation, position, city, country WHERE cont +act.ruid=business.ruid AND contact.ruid=acn.ruid AND forename LIKE ? +AND surname LIKE ? AND occupation LIKE ? AND position LIKE ? AND city + LIKE ? AND country LIKE ?') or die "Couldn't prepare query: " . $dbh->errstr;
      And here's the page (it's a CGI script) that makes me think it's being chopped off...
      Software error: Couldn't execute query: You have an error in your SQL syntax near 'WHE +RE contact.ruid=business.ruid AND contact.ruid=acn.ruid AND forename +LIKE 'jo' at line 1 at /home/httpd/cgi-bin/basicsearch.cgi line 71. For help, please send mail to the webmaster (dan@timmy), giving this e +rror message and the time and date of the error.
        The bug is not in DBD::mysql, but in your SQL: there's no FROM.
        As an aside (yes, I know the SQL is missing a FROM clause) that particular SQL statement is not really that long. Once you start programming a bit more, SQL statements 10x as long as that can become the norm. I highly recommend you start developing a better style to handle the SQL (for aesthetic, as well as other reasons). Try putting the SQL in a variable and then stick that variable into the prepare, like this:
        my $CONTACT_SQL = " SELECT C.forename, C.surname, B.occupation, A.position,, AS forename, surname, occupation, position, city, cou +ntry FROM ACN A, BUSINESS B, CONTACTS C WHERE C.ruid=B.ruid AND C.ruid=A.ruid AND forename LIKE ? AND surname LIKE ? AND occupation LIKE ? AND position LIKE ? AND city LIKE ? AND country LIKE ?"; my $sthQuery = $dbh->prepare($CONTACT_SQL) or die "Couldn't prepare query: $DBI::errstr";
        You have no FROM in your SQL. I suggest finding one of the numerous SQL tutorials on the web (starting with the one on the MySQL website) and looking at the syntax (since this problem isn't Perl related). But, you will need to add:

        FROM contact, business, acn

        In your query.


Re: Maximum DBI query length?
by AidanLee (Chaplain) on May 07, 2001 at 23:56 UTC

    I have actually run into limits with query lengths, but not of the entire query itself. I have found that MySQL does not like lists "... where myfield IN (3,4,2,7,9,...)" to be longer than 255.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://78571]
Approved by root
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (8)
As of 2018-05-22 14:54 GMT
Find Nodes?
    Voting Booth?