Re: Problematic prepared statement problem in stored procedure

[Bill Phan]

Peter, mySQL only reports the first error encountered when parsing SQL statements right? So if there's more than one error, it only reports the first one found?

I've written a similar INSERT-SELECT statements that used a IN parameter for the table name and declared variables in the WHERE clause without any problems. An example would be something like:

SET @sql_stmt_insrt = CONCAT('INSERT INTO Wait_Time(job_user,sbmt_time_hrs,sbmt_time,strt_time_hrs,strt_time,qname,job_wait_time) SELECT owner,(submission_time/3600),FROM_UNIXTIME(submission_time),(start_time/3600),FROM_UNIXTIME(start_time) AS strt_time_hrs,qname,((start_time - submission_time)/3600) FROM ',table_name,' WHERE (start_time <> 0) AND (submission_time <> 0) AND (qname = ''largeMem.q'') AND (start_time < UNIX_TIMESTAMP(''',next_month,''')) AND (((end_time >= UNIX_TIMESTAMP(''',curr_month,''')) AND (start_time >= UNIX_TIMESTAMP(''',last_month,''')) AND (end_time < UNIX_TIMESTAMP(''',next_month,''')) AND ((end_time - UNIX_TIMESTAMP(''',curr_month,''')) >= (UNIX_TIMESTAMP(''',curr_month,''') - start_time))) OR ((start_time >= UNIX_TIMESTAMP(''',curr_month,''')) AND ((end_time - UNIX_TIMESTAMP(''',next_month,''')) < (UNIX_TIMESTAMP(''',next_month,''') - start_time)))) ORDER BY owner;');

where table_name is an IN parameter and next_month, curr_month, last_month are
all declared variables.

The strange thing is, I can't even use a string literal in the SELECT part of the prepared statement:

SET @sql_stmt_insrt_mnth_rprt = CONCAT('INSERT INTO Monthly_Report (month,job_user,TotCPU,TotRT,TotGbytes_secs,TotIO,Totmaxvmem) SELECT ''2013-03-01 00:00:00'',owner,SUM(CPU)/3600 AS "TotCPU",

Thanks for all your help BTW.