Perl Guru Forums: Perl Programming Help: DBI: Combined SQL select / insert queries

CGI/Perl Guide | Learning Center | Forums | Advertise | Login

Site Search: in


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN

Home: Perl Programming Help: DBI:
Combined SQL select / insert queries

nine_grounds
New User

Dec 7, 2008, 2:48 PM

Post #1 of 6 (13940 views)

Combined SQL select / insert queries	Can't Post

Hello,

I am trying to get a query to work in Perl, but it can't seem to handle it and I don't really know why. I am using DBI, and it gives me a MySQL syntax error at the start of the second part. The query has 4 parts as follows:

CREATE TEMPORARY TABLE TempTimesheet ( jobnum int, job_sub_num varchar(5), lname varchar(100), fname varchar(100), type varchar(6), std_charge_rate decimal(5,2), hours decimal(5,2), rate decimal(5,2) );

This creates a temporary table with all the fields I require. The next part take values from other tables (`Timesheets_Jobs` and `employees`) and inserts filtered results into the temporary table as follows:

INSERT INTO TempTimesheet (jobnum, lname, fname, type, std_charge_rate, hours, rate) SELECT `Timesheets_Jobs`.`jobnum`, `employees`.`lname`, `employees`.`fname`, `employees`.`type`, `employees`.`std_charge_rate`, `Timesheets_Jobs`.`hours`, `Timesheets_Jobs`.`rate` FROM `Timesheets_Jobs` LEFT OUTER JOIN `employees` ON `Timesheets_Jobs`.`employee` = `employees`.`number` WHERE `Timesheets_Jobs`.`fiscal_week` = 20080903;

Followed by a similar insert command, that uses the tables `Timesheets_Job_Parts` and `employees`, as follows:

INSERT INTO TempTimesheet (jobnum, job_sub_num, lname, fname, type, std_charge_rate, hours, rate) SELECT `Timesheets_Job_Parts`.`jobnum`, `Timesheets_Job_Parts`.`job_sub_num`, `employees`.`lname`, `employees`.`fname`, `employees`.`type`, `employees`.`std_charge_rate`, `Timesheets_Job_Parts`.`hours`, `Timesheets_Job_Parts`.`rate` FROM `Timesheets_Job_Parts` LEFT OUTER JOIN `employees` ON `Timesheets_Job_Parts`.`employee` = `employees`.`number` WHERE `Timesheets_Job_Parts`.`fiscal_week` = 20080903;

Note that "20080903" is a value that gets determined by variables selected by the user. The last part of the query selects the data from the temporary table (using a reference to a table called `jobs`) and sorts it as I require:

SELECT `jobs`.`bid_status`, `TempTimesheet`.`jobnum`, `jobs`.`client_code`, `TempTimesheet`.`job_sub_num`, `TempTimesheet`.`lname`, `TempTimesheet`.`fname`, `TempTimesheet`.`hours`, `TempTimesheet`.`rate`, `TempTimesheet`.`type` FROM jobs, TempTimesheet WHERE `jobs`.`jobnum` = `TempTimesheet`.`jobnum` ORDER BY `jobnum` DESC, `job_sub_num` ASC, `type` ASC, `std_charge_rate` DESC, `lname` ASC

The query itself works fine in MySQL command line, and in phpMyAdmin, and typically runs in under 2 mS, but it seems that DBI has a problem with multiple steps like this. I have also tried including the word "VALUES", and brackets, before the selections for inserting, but to no avail.

Any help would be appreciated...

Thanks :)

(This post was edited by nine_grounds on Dec 7, 2008, 8:15 PM)

FishMonger
Veteran / Moderator

Dec 7, 2008, 7:42 PM

Post #2 of 6 (13933 views)

Re: [nine_grounds] Combined SQL select / insert queries [In reply to]	Can't Post

Post your sql statement in a more readable manor and some might be able to help.

FishMonger
Veteran / Moderator

Dec 7, 2008, 9:26 PM

Post #3 of 6 (13925 views)

Re: [nine_grounds] Combined SQL select / insert queries [In reply to]	Can't Post

Do you believe that line lengths of 400+ chars is readable?

Since you're asking for help in a Perl forum, it would be best to post the relivent Perl code.

You need to add vertical and horizontal white space and this is the type of readability I meant for you to post.

Code

$sth = $dbh->prepare( 
            "INSERT INTO TempTimesheet (jobnum, 
                                        lname, 
                                        fname, 
                                        type, 
                                        std_charge_rate, 
                                        hours, 
                                        rate) 
            SELECT `Timesheets_Jobs`.`jobnum`, 
                   `employees`.`lname`, 
                   `employees`.`fname`, 
                   `employees`.`type`, 
                   `employees`.`std_charge_rate`, 
                   `Timesheets_Jobs`.`hours`, 
                   `Timesheets_Jobs`.`rate` 
            FROM `Timesheets_Jobs` 
            LEFT OUTER JOIN `employees` ON `Timesheets_Jobs`.`employee` = `employees`.`number` 
            WHERE `Timesheets_Jobs`.`fiscal_week` = 20080903" 
);

I suspect that perl was complaining about your concatenation.

Try this:

Code

$sth = $dbh->prepare( 
            "INSERT INTO TempTimesheet (jobnum, 
                                        lname, 
                                        fname, 
                                        type, 
                                        std_charge_rate, 
                                        hours, 
                                        rate) 
            SELECT `Timesheets_Jobs.jobnum`, 
                   `employees.lname`, 
                   `employees.fname`, 
                   `employees.type`, 
                   `employees.std_charge_rate`, 
                   `Timesheets_Jobs.hours`, 
                   `Timesheets_Jobs.rate` 
            FROM `Timesheets_Jobs` 
            LEFT OUTER JOIN `employees` ON `Timesheets_Jobs.employee` = `employees.number` 
            WHERE `Timesheets_Jobs.fiscal_week` = 20080903" 
);

nine_grounds
New User

Dec 7, 2008, 10:53 PM

Post #4 of 6 (13915 views)

Re: [nine_grounds] Combined SQL select / insert queries [In reply to]	Can't Post

Well, it wasn't the concatenation, but believe it or not, the mess of SQL I initially posted was what I was trying to use - in one query. After reading your post "Do you believe that line lengths of 400+ chars is readable?", I thought "maybe computers feel the same", and I decided to break up the query into the individual steps, as such:

$sth=$dbh->prepare($query1);
$sth->execute();
$sth->finish;
$sth=$dbh->prepare($query2);
$sth->execute();

...etc. with $query1 thru $query4 being the 4 SQL queries in my post, and it worked. Thanks :)

FishMonger
Veteran / Moderator

Dec 8, 2008, 12:15 AM

Post #5 of 6 (13913 views)

Re: [nine_grounds] Combined SQL select / insert queries [In reply to]	Can't Post

Why would you think executing 4 sql statements as if it were 1 statement would work? Even when executing them from the command line, they are executed as 4 separate sql statements (the semi-colon indicate the end of an sql statement).

nine_grounds
New User

Dec 8, 2008, 5:07 PM

Post #6 of 6 (13881 views)

Re: [FishMonger] Combined SQL select / insert queries [In reply to]	Can't Post

I had the semi-colons - I just didn't know that DBI doesn't separate the statements like the command line does.

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives