MySQL Forums
Forum List  »  Stored Procedures

Can mysql handle multi-lines sql query? and how?
Posted by: andrew acyk
Date: May 05, 2017 08:29AM

I am new to Mysql, but I am very experienced in Oracle.
I need to run a very long sql query (about 2k chars long)
in Mysql stored-procedure. The following is a similar code
except the sql statement on line 3 is much longer:

set @var1 = '';
set v_sql =
'select parameter_value into @var1
from ce_configuration_parameter
where parameter_name = ''name_var1'' ';
select replace(replace(v_sql, '\r', ' '), '\n', ' ') into @sql;
if p_debugl = 1
then
select concat('v_sql: ', v_sql);
else
prepare stmt from @sql;
execute stmt;
-- catch exception by exit handler
deallocate prepare stmt;
end if;
select @var1;
select @sql;

Output of line 9:
v_sql: select parameter_value_into @var1
from ce_configuration_parameter
where parameter_name = 'name_var1'

Output of line 17 (in one line):
'select parameter_value into @ce_emissions_cap_perd_perc from ce_configuration_parameter where parameter_name = \'ce_emissions_cap_perd_perc\''

Notice that string variable v_sql (line 2) has multiple lines
with proper indentation. This is necessary for a long sql query.
It is difficult to read long sql statement in one single line.
Besides that, we normally read the sql query in many passes.
Problem is Mysql adds \r\n to v_sql for each new line.
\r\n is not recognized by the parser and need to be replaced
(should treat \r\n like white space or simply replace with space).

Line 6 replaces the \r and \n chars and save the string to @sql.
Unfortunately @sql contains backslash (\) before the single quotes
and backslash (\) is not recognized by the parser neither
(see output of line 17). So it seems to me that
Mysql stored-procedure has problem to
handle very long sql query. Do I miss anything here?
Can anyone tell me how to handle long sql query in Mysql
stored-procedure? Or do I need to do this in, say Python codes?
Thank you in advance for your advise or suggestion.

Andrew

Options: ReplyQuote


Subject
Views
Written By
Posted
Can mysql handle multi-lines sql query? and how?
1096
May 05, 2017 08:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.