Hi,
I have a textarea where I can write queries, but when I pass the value of the textarea to the mysql resource, it fails.
-
Goal:
-
Steps:
-
Details:
-
Screenshots:
There is probably a line break character \n coming through in the textArea1.value. For example:
Either enter the SQL with no line breaks or replace line breaks (e.g. .replace(/\r?\n/g, " ")):
When you type the variable name into the console, what does it show?
Do you not need from in your most recent screenshot?
Does the query work if you copy/paste the SQL statement from the variable into the query? From the error, it doesn't seem to be an issue with disabling prepared SQL statements (which I assume you have already done), so this is stumping me...
2 Likes
yes, the query work when I copy/past the sql statment. Where i can see disabling prepared SQL statements option?
It’s an option in the resources page, select settings for your DB.
I did test this with and without prepared statements and they do need to be disabled for it to work.
Just curious, what’s the use case for passing entire SQL statements into a query?
1 Like
WOW, now work!
I have this multiple statment for transpose rows into columns.
SET SESSION group_concat_max_len = 1000000;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(CASE WHEN giorno = ''',
giorno,
''' THEN casette_occupate END) AS ',
'"',giorno,'"'
)
) INTO @sql
FROM (select giorno, count(id_preventivo) casette_occupate from(
select id_preventivo, case when count(1)=0 then '15/08/2024' else giorno end giorno from(
select
id_preventivo ,
DATE_FORMAT(dt_check_in,'%d/%m/%Y') giorno
from v_p_preventivi
where dt_check_in = STR_TO_DATE('15/08/2024','%d/%m/%Y')
and fl_confermato =1) a
union
select
id_preventivo ,
DATE_FORMAT(dt_check_in,'%d/%m/%Y') giorno
from v_p_preventivi
where dt_check_in <= DATE_ADD(STR_TO_DATE('15/08/2024','%d/%m/%Y'),INTERVAL 5 day) and dt_check_in > DATE_ADD(STR_TO_DATE('15/08/2024','%d/%m/%Y'),INTERVAL -5 DAY)
and fl_confermato =1
UNION
select
id_preventivo ,
DATE_FORMAT(DATE_ADD(dt_check_in,INTERVAL 1 day),'%d/%m/%Y') giorno
from v_p_preventivi
where dt_check_in <= DATE_ADD(STR_TO_DATE('15/08/2024','%d/%m/%Y'),INTERVAL 5 day) and dt_check_in > DATE_ADD(STR_TO_DATE('15/08/2024','%d/%m/%Y'),INTERVAL -5 DAY)
and fl_confermato =1
UNION
select
id_preventivo ,
DATE_FORMAT(DATE_ADD(dt_check_in,INTERVAL 2 day),'%d/%m/%Y') giorno
from v_p_preventivi
where dt_check_in <= DATE_ADD(STR_TO_DATE('15/08/2024','%d/%m/%Y'),INTERVAL 5 day) and dt_check_in > DATE_ADD(STR_TO_DATE('15/08/2024','%d/%m/%Y'),INTERVAL -5 DAY)
and fl_confermato =1
) a
group by giorno) v;
SET @sql = CONCAT('SELECT ', @sql, ' FROM (
select giorno, count(*) casette_occupate from
(select
id_preventivo ,
DATE_FORMAT(dt_check_in,''%d/%m/%Y'') giorno
from v_p_preventivi
where dt_check_in <= DATE_ADD(STR_TO_DATE(''15/08/2024'',''%d/%m/%Y''),INTERVAL 5 day) and dt_check_in > DATE_ADD(STR_TO_DATE(''15/08/2024'',''%d/%m/%Y''),INTERVAL -5 DAY)
and fl_confermato =1
UNION
select
id_preventivo ,
DATE_FORMAT(DATE_ADD(dt_check_in,INTERVAL 1 day),''%d/%m/%Y'') giorno
from v_p_preventivi
where dt_check_in <= DATE_ADD(STR_TO_DATE(''15/08/2024'',''%d/%m/%Y''),INTERVAL 5 day) and dt_check_in > DATE_ADD(STR_TO_DATE(''15/08/2024'',''%d/%m/%Y''),INTERVAL -5 DAY)
and fl_confermato =1
UNION
select
id_preventivo ,
DATE_FORMAT(DATE_ADD(dt_check_in,INTERVAL 2 day),''%d/%m/%Y'') giorno
from v_p_preventivi
where dt_check_in <= DATE_ADD(STR_TO_DATE(''15/08/2024'',''%d/%m/%Y''),INTERVAL 5 day) and dt_check_in > DATE_ADD(STR_TO_DATE(''15/08/2024'',''%d/%m/%Y''),INTERVAL -5 DAY)
and fl_confermato =1
) a
group by giorno) x');
PREPARE stmt FROM @sql;
EXECUTE stmt;
in this statment I need to replace "15/08/2024" with the value present in a textbox.
if I run with the value 08/15/2024, the query works, but if I replace with the variable, it fails. Same error as the query indicated in this post