Working MySQL Query fails in Perl

Question

I have a query, which when run from within phpMyAdmin, it works, however when integrated within a website written in Perl, it fails and throws errors. Both are run with a connection to the same database, and from coding/formatting side when integrated into the Website, all is correct and the same as other queries.

Any help with this would be much appreciated - Thanks!

MySQL Query:

CREATE TEMPORARY TABLE tmp_lecture_days (
timeslot_id int(50)
);
INSERT INTO tmp_lecture_days (timeslot_id)
SELECT DISTINCT tab_appointment.timeslot_id
FROM tab_appointment WHERE lecture_id = '1115';
SELECT COUNT(timeslot_id)
FROM tmp_lecture_days;

MySQL Query in Perl:

            $query
                = &statement_database(
                    "CREATE TEMPORARY TABLE tmp_lecture_days (
                    timeslot_id int(50)
                    );
                    INSERT INTO tmp_lecture_days (timeslot_id)
                    SELECT DISTINCT tab_appointment.timeslot_id
                    FROM tab_appointment WHERE lecture_id = '1115';
                    SELECT COUNT(timeslot_id)
                    FROM tmp_lecture_days;");

             my ($days) = $query->fetchrow_array;

Error Log:

7.3.2011 10:14:12 error 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 '; 7.3.2011 10:14:12 error INSERT INTO tmp_lecture_days (timeslot_id) 7.3.2011 10:14:12 error SELECT DISTINCT tab_appoi' at line 3

bvr · Accepted Answer · 2011-03-07 13:40:03Z

8

If you are using DBI for running the query, you are not allowed to put more than one statement into it. Try putting CREATE TABLE ... into one query and INSERT ... into another.

answered Mar 7, 2011 at 13:40

bvr

9,69725 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

schuhmi2 Over a year ago

Although I was thinking that could be the problem, I'm creating a temporary table there, so wouldn't closing the first query also delete the temporary table? Changing to that way would mean 4 queries to run per an entry (the fourth then being a delete table created in query one). That could cause timing issues on a long list as you are sort of quadrupling the amount you have to do.

jishi Over a year ago

A temporary table lives through a whole connection. Just make sure you are querying over the same connection and not make a new connection for each statement.

Jamie McCarthy Over a year ago

DBI.pm does this for your own safety. Forbidding multiple statements will often greatly limit the damage attackers can do if they find an SQL injection to exploit. The best-known use of the statement-terminating semicolon is in xkcd.com/327

nikhil500 · Accepted Answer · 2011-03-07 13:47:59Z

3

I think the following query is equivalent to the 3 queries:

SELECT COUNT(DISTINCT timeslot_id) FROM tab_appointment
    WHERE lecture_id = '1115'

For more details, check the MySQL reference manual for COUNT() here.

answered Mar 7, 2011 at 13:47

nikhil500

3,45021 silver badges23 bronze badges

1 Comment

schuhmi2 Over a year ago

Or that could also work - I tried combining but for some reason it went wrong. Comparing your short version to mine, just turned out I got the bracket on the wrong side of DISTINCT - Thanks a bunch XD

Collectives™ on Stack Overflow

Working MySQL Query fails in Perl

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related