scripting inserts, parameter array bindings

drumcircle
Rising Star

With most programming languages you can script inserts using an array of ? binding place-holders that gets bound with an array of typed variables.

In the example below, I'm trying to figure out how to bind variables :v1, :v2, :v2 to the array "vals" so it can be scripted more easily.

This is the base case, it works:

--/
CREATE OR REPLACE SCRIPT test_static_params() AS
query([[CREATE OR REPLACE TABLE ZTEST (A VARCHAR(30), B VARCHAR(30), C VARCHAR(30))]],{})

local vals = {'x', 'y', 'z'}

local qq_success, qquery = pquery([[INSERT INTO ::t (A,B,C) VALUES (:v1,:v2,:v3)]], {t='ZTEST',v1=vals[1],v2=vals[2],v3=vals[3]})
if (not qq_success) then
output("Error querying for questions, "..qq_success.error_message..','..qq_success.statement_text)
else
output("Inserted: "..qquery.rows_affected)
end

/

EXECUTE SCRIPT test_static_params() with output

Here's a futile stab at using dynamic assignment of values, it does not work:

--/
CREATE OR REPLACE SCRIPT test_dynamic_bindings() AS
        query([[CREATE OR REPLACE TABLE ZTEST (A VARCHAR(30), B VARCHAR(30), C VARCHAR(30))]],{})

        local vals = {'x', 'y', 'z'}

        -- local qq_success, qquery = pquery([[INSERT INTO ::t (A,B,C) VALUES (:v1)]], {t='ZTEST',v1=vals})
         local qq_success, qquery = pquery([[INSERT INTO ::t VALUES :v1]], {t='ZTEST',v1=vals})
        
        if (not qq_success) then
             output("Error querying for questions, "..qq_success.error_message..','..qq_success.statement_text)
        else
            output("Inserted: "..qquery.rows_affected) 
        end

/

EXECUTE SCRIPT test_dynamic_bindings() with output

 

Ideas?

The alternative is to build up a long sql string programmatically.  Not impossible but need to escape quotes, etc.

1 ACCEPTED SOLUTION

mwellbro
Xpert

Hi drumcircle,

I think you´re mixing layers in the second example, the ":v1" is a parameter binding and SQL only understands one binding for one variable , separated but "," ( also you´d need to put those brackets back where they belong ).

If you were to put together the pquery dynamically there would be no need to worry about quotes and such because the dynamic portion would only be a "variable length VALUES clause" ( depending on how many binds you need ) - the trickier part would be the assignment of your "lua table values" in the later part of the pquery....not sure how dynamic we can get here...

If I´ll come accross an idea that might help here I´ll let you know.

Cheers,
Malte

View solution in original post

2 REPLIES 2

mwellbro
Xpert

Hi drumcircle,

I think you´re mixing layers in the second example, the ":v1" is a parameter binding and SQL only understands one binding for one variable , separated but "," ( also you´d need to put those brackets back where they belong ).

If you were to put together the pquery dynamically there would be no need to worry about quotes and such because the dynamic portion would only be a "variable length VALUES clause" ( depending on how many binds you need ) - the trickier part would be the assignment of your "lua table values" in the later part of the pquery....not sure how dynamic we can get here...

If I´ll come accross an idea that might help here I´ll let you know.

Cheers,
Malte

View solution in original post

drumcircle
Rising Star