wrappers query_rows and query_values example calls from within dbVisualizer

SQL-Fighter

Please! Please! Please! can someone enlighten me as to how to call these 2 wrapper functions from the dbVisualizer script window?

 

            for data in wrapper:query_rows( 'SELECT * FROM CAT' ) do
                output( data.TABLE_NAME .. data[2] )
            end


            for table_name, table_type in wrapper:query_values( 'SELECT * FROM CAT' ) do
                output( table_name .. table_type )
            end

 

I'm trying to run the example calls mentioned here but keep getting syntax errors! (I feel like *crying*)

[Code: 0, SQL State: 42000] syntax error, unexpected ',', expecting ASSIGNMENT_OPERATOR_ or IN_REVERSE_ or AS_ or IN_ [line 1, column 15] (Session: 1668648216556587131) [Script position: 179 - 180]


Do I have to encapsulate it in a 'create script ...' and execute the script or can I just execute it inline directly in the query editor window?

1 ACCEPTED SOLUTION

Moderator
Moderator

After having run all the statements from https://raw.githubusercontent.com/exasol/etl-utils/master/query_wrapper.sql I have installed the query wrapper.

Say my use case is to copy tables from one schema to another. My original script to do that is this (without query wrapper usage so far):

create schema testschema;
--/
create or replace script original_script
as
suc,res=pquery([[select table_name from exa_dba_tables
where table_schema ='RETAIL']])
for i=1, #res
do
query([[create or replace table testschema.::t
as select * from retail.::t]]
,{t=res[i].TABLE_NAME})
end
/

Now I want to use the query wrapper instead. With the ETL schema in place from calling the github script, I can do it now this way:

--/
CREATE OR REPLACE SCRIPT etl.copy_retail RETURNS TABLE
AS
import('etl.query_wrapper','qw')
wrapper = qw.new('etl.job_log', 'etl.job_details', 'copy_retail_to_testschema')
suc,res=wrapper:query([[select table_name from exa_all_tables
where table_schema ='RETAIL']])
for i=1, #res
do
wrapper:set_param("t",res[i].TABLE_NAME)
wrapper:query([[create or replace table testschema.::t
as select * from retail.::t]])
end
return wrapper:finish()
/
execute script etl.copy_retail;

wrapper_call.PNG

So you import the wrapper into your own scripts and call it with the wrapper prefix there.

Hope that helps.

View solution in original post

1 REPLY 1

Moderator
Moderator

After having run all the statements from https://raw.githubusercontent.com/exasol/etl-utils/master/query_wrapper.sql I have installed the query wrapper.

Say my use case is to copy tables from one schema to another. My original script to do that is this (without query wrapper usage so far):

create schema testschema;
--/
create or replace script original_script
as
suc,res=pquery([[select table_name from exa_dba_tables
where table_schema ='RETAIL']])
for i=1, #res
do
query([[create or replace table testschema.::t
as select * from retail.::t]]
,{t=res[i].TABLE_NAME})
end
/

Now I want to use the query wrapper instead. With the ETL schema in place from calling the github script, I can do it now this way:

--/
CREATE OR REPLACE SCRIPT etl.copy_retail RETURNS TABLE
AS
import('etl.query_wrapper','qw')
wrapper = qw.new('etl.job_log', 'etl.job_details', 'copy_retail_to_testschema')
suc,res=wrapper:query([[select table_name from exa_all_tables
where table_schema ='RETAIL']])
for i=1, #res
do
wrapper:set_param("t",res[i].TABLE_NAME)
wrapper:query([[create or replace table testschema.::t
as select * from retail.::t]])
end
return wrapper:finish()
/
execute script etl.copy_retail;

wrapper_call.PNG

So you import the wrapper into your own scripts and call it with the wrapper prefix there.

Hope that helps.

View solution in original post