SQL bind error, 6 parameters but requires 0

Queries and Resources
#1

I'm getting this error when I try to run a query that I've successfully confirms works outside of retool (all parameters in the highlight feature in retool check out as well):

bind message supplies 6 parameters, but prepared statement "" requires 0

DO $$
    BEGIN
        IF EXISTS (
            SELECT * FROM retailer_sku
            WHERE retailer_id={{table1.selectedRow.data.id}}
            AND sku_id={{input2.value}}
        )
        then
            update retailer_sku
            set active=TRUE
            WHERE retailer_id={{table1.selectedRow.data.id}}
            AND sku_id={{input2.value}}
        ELSE
            insert into retailer_sku(retailer_id, sku_id, price)
            values ({{table1.selectedRow.data.id}}, {{input2.value)}}, 2000);
        END IF ;
    END
$$ ;
#2

I'm facing the same issue. Did anyone fix this for you @paul ?

#3

@karthik no word yet, I ended up making an api endpoint to resolve my issue, but I'd love for there to be a solution with SQL.

#4

@justin @william-retool Do you have any idea why this is happening? I've noticed a similar error when I try writing a function for postgres:

do $$
declare
    i int := 0;
    rows_to_insert int := {{inputAddRunCont.value}};
begin
    while @i < rows_to_insert loop
        INSERT INTO container(run_id) VALUES ({{addRun.data.id[0]}});
        i := i + 1;
        end loop;
end $$;

but getting error

bind message supplies 2 parameters, but prepared statement "" requires 0

How to build detail views that are populated from separate queries?
#5

HI Paul, I think you might be better off defining that function as a stored procedure directly in Postgres, and then just executing the stored procedures from Retool.

To provide a little more context, we're currently transforming your SQL query into this:

do $$
declare
    i int := 0;
    rows_to_insert int := ?;
begin
    while @i < rows_to_insert loop
        INSERT INTO container(run_id) VALUES (?);
        i := i + 1;
        end loop;
end $$;

with the parameters

[inputAddRunCont.value, addRun.data.id[0]]

However, you can't use ? inside postgres functions, and so it doesn't work.

1 Like
#6

Gotcha, thanks for the reply.

#7

Hi I'm getting a similar error.
"bind message supplies 4 parameters, but prepared statement "" requires 0"

Here's my query:

DO $$
DECLARE
    id_ownership INTEGER;
    id_profile INTEGER;

BEGIN

    INSERT INTO ownerships (shares, created_at, updated_at, property_id)
    VALUES ({{ createOwnerFormSelectShares.value }}, current_timestamp, current_timestamp, {{ createOwnerFormSelectProperty.value }})
    RETURNING id INTO id_ownership;

    INSERT INTO profiles (first_name, last_name, created_at, updated_at)
    VALUES ({{ createOwnerFormFirstNameInput.value }}, {{ createOwnerFormLastNameInput.value }}, current_timestamp, current_timestamp)
    RETURNING id INTO id_profile;

    INSERT INTO profile_ownerships (profile_id, ownership_id, created_at, updated_at)
    VALUES (id_profile, id_ownership, current_timestamp, current_timestamp);
    
END $$;

@william-retool Would you have any suggestions on how to fix this?

#8

I too am getting the "bind..." error due to it not recognizing the {{value}} with ?...

tried set, declare...stored procedure but can´t get it to work (the variable webshopId will be dynamic so its linked to a textInput componente)

Any ideas?

do $$
    declare
        total integer := 0;
        batchsize integer := 100;
        waitsecs integer := 10;
        webshopId integer := {{ Number(textInput2.value) }};
    begin
        select count(*) from sell_order_lines sol
        join sell_orders so
        on sol.sell_order_id = so.id
        where so.webshop_id = webshopId into total;
        while total > 0 loop
                raise notice 'deletes to go %', total;
                delete from sell_order_lines
                where (id) in (select sol.id from sell_order_lines sol 
                               join sell_orders so on sol.sell_order_id = so.id 
                               where so.webshop_id = webshopId 
                               limit batchsize);
                COMMIT;
                total := total-batchsize;
                PERFORM pg_sleep(waitsecs);
            end loop;
    end; $$

image
image704×186 12.5 KB

#9

Hey @ricardoguerreiro, the previous user messaged our Support team directly to help with this. The solution was that he needed to write each of those INSERT statements separately in their own query (instead of all the INSERT statements in 1 query). Then, if you're looking to trigger multiple queries at once, you should then be able to chain on events with a "Success" function to trigger another query when the initial query has ran successfully.

Are you able to run this query successfully using a program outside of Retool?

#10

Hey @Kenny thanks for the response! The query loops to delete table lines 100 at a time with a delay of 10seg so I don´t kill the database. If I code the id (ex. 307 or 456) instead of calling it as {{example.data.id}} the query works in dbeaver and also in retool. Its when I call the variable that it gives out the error message cause of the "$" that is being read:

image
image731×127 16.4 KB

#11

@ricardoguerreiro, thanks for sharing that!

By default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.

You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries. If that's something you'd like to explore, I often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.

1440×502 47.8 KB

#12

Thanks @Kenny it worked. Great suggestion to create parallel resource :wink: