topic Re: SQL - cannot insert multiple comands into prepared statement in Discussions

SQL - cannot insert multiple comands into prepared statement

ron_horne — Sat, 10 Jun 2023 20:55:05 GMT

dear members of the forum,
when using jmp to query amazon redshift i need to send two statements together but for some reason this i get an error saying:

unfortunately, my query is very elaborate and doesn't work with just a "Where" statement for each separate table.
if anyone has any idea how to overcome this issue it would be helpful.

Thanks

Re: SQL - cannot insert multiple comands into prepared statement

Georg — Sat, 06 May 2023 14:51:44 GMT

Dear Ron, I did Not Do This so far, but here you are asking new sql query for two tables, I suppose, it can not handle that. Did you try execute sql instead?

Other possibility would be union between both queries to have one only. But this requires exactly the same columns in same order.

Re: SQL - cannot insert multiple comands into prepared statement

ron_horne — Sat, 06 May 2023 20:09:18 GMT

i get the same error if i try it using the following script:

// connect to database - open data base
DBConnection = Create Database Connection(
"ODBC:DSN=database-project;EPWD=.....;");

sql_events = 
"\[
select * from "public"."logs"  "t1";
select * from "public"."logs"  "t1";
]\"
dt1 = execute sql ( DBConnection,  eval insert ( sql_events , "¤") ,  "Events from logs"  );

the issue may be similar to this discussion:
https://community.jmp.com/t5/Discussions/SQL-query-to-ODBC-datasource-SQL-server-with-temporary-tables/td-p/457582

but the solution there is not exactly applicable to redshift.

Re: SQL - cannot insert multiple comands into prepared statement

pmroz — Mon, 08 May 2023 00:46:35 GMT

Generally you can't execute two SQL statements with one call to EXECUTE SQL. Both statements are the same; perhaps you were just showing an example?

Why are you trying to run two SQL statements at once? EXECUTE SQL returns a dataset for each SQL statement.

Why not just run two EXECUTE SQL commands?

Re: SQL - cannot insert multiple comands into prepared statement

ron_horne — Tue, 09 May 2023 20:15:52 GMT

you are right @pmroz , it was just an example of two statements.
sometimes i need two statements to create a sub-sample (temporary table) or define a function.
in most cases i manage to use the "With" statement instead of a temporary table but not always. especially when using the "With" statements requires very complex sub-queries that are not allowed. .

Re: SQL - cannot insert multiple comands into prepared statement

pmroz — Tue, 09 May 2023 20:33:53 GMT

This approach might help:

Call a PL/SQL procedure that does the super-complex query, and creates a temporary table.

Then select * from the temporary table.

Here's some code that does something like that:

event_sql = evalinsert(
"DECLARE 
	l_proc_name VARCHAR2 (100)	:= 'Get_Reference_PTs'; 
	l_user_SID 	NUMBER			:= SYS_CONTEXT('USERENV', 'SID'); 
	l_user_name	VARCHAR2 (30)	:= UPPER(SYS_CONTEXT('USERENV', 'OS_USER')); 
BEGIN 

    INSERT INTO quality_attrib (SID, OS_USER, Completion_Time, Progress_Text) 
        VALUES (l_user_SID, l_user_name, SYSTIMESTAMP, 'START Get_Reference_PTs');  
        COMMIT; 

	INSERT INTO Ref_PT_List (SID, OS_USER, PT_Name) 
	(SELECT DISTINCT l_user_SID, l_user_name, mu.pt_name 
	FROM info ci, 
		 drug cd, 
	     reaction cr, 
	     meddra_uta mu,  
	 WHERE cd.case_id = ci.case_id 
	   AND cr.case_id = ci.case_id 
	   AND cr.reported_meddra_uta_id = mu.meddra_uta_id 
	   AND cd.cdd_generic_id = rd.cdd_generic_id 
	   AND ci.report_type_code_id = 8  
	   AND rd.calendar_id =  ^one_calendar_id^
	   AND cd.drug_character_code_id IN (31, 32)
	^pt_clause^
	^date_clause^
	^event_type_clause^
	); 
	COMMIT; 

    INSERT INTO quality_attrib (SID, OS_USER, Completion_Time, Progress_Text) 
        VALUES (l_user_SID, l_user_name, SYSTIMESTAMP, 'END Get_Reference_PTs');  
        COMMIT; 

EXCEPTION 
    WHEN OTHERS 
        THEN 
        log_error_message(SQLCODE, SQLERRM, l_proc_name); 
END; ");

Execute SQL(dbc, event_sql);

Execute SQL(dbc, "select * from ref_pt_list");

Re: SQL - cannot insert multiple comands into prepared statement

ron_horne — Tue, 30 May 2023 11:18:35 GMT

thank you very much @pmroz
splitting the two statements into separate Execute SQL statements works perfect.