CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in 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 (6643 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 (6636 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 (6628 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 (6618 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 (6616 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 (6584 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) Powered by Gossamer Forum v.1.2.0

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