Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Re: Re: Maximum DBI query length?

by Merlin83 (Novice)
on May 07, 2001 at 21:39 UTC ( [id://78577]=note: print w/replies, xml ) Need Help??


in reply to Re: Maximum DBI query length?
in thread Maximum DBI query length?

Very well. Here's the code that prepares the query....
my $sthQuery = $dbh->prepare('SELECT contact.forename, contact.surname +, business.occupation, acn.position, contact.city, contact.country 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.

Replies are listed 'Best First'.
Re: Re: Re: Maximum DBI query length?
by le (Friar) on May 07, 2001 at 21:48 UTC
    The bug is not in DBD::mysql, but in your SQL: there's no FROM.
      Oops, I thought the 'table.column' notation obviated the need for that. What should I be putting for the SQL query then?
        nope, you need to list out each of the tables..
        SELECT contact.field1, contact.field2, business.field1, acn.field1 FROM contact, business, acn WHERE ... (Etc.)
        or you can assign aliases
        SELECT c.field1, c.field2, b.field1, a.field1 FROM contact c, business b, acn a WHERE ... (Etc.)

        OK?

        SELECT [columns] FROM [tables] WHERE [condition].
Re: Re: Re: Maximum DBI query length?
by turnstep (Parson) on May 07, 2001 at 22:41 UTC
    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, C.city, C.country 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";
Re: Re: Re: Maximum DBI query length?
by KM (Priest) on May 07, 2001 at 21:54 UTC
    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.

    Cheers,
    KM

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://78577]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others studying the Monastery: (3)
As of 2024-03-29 01:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found