Variable binding using soci with PL/pgSQL

Question

I'm using SOCI library to execute database queries on both Oracle and PostgreSQL databases. I got the following error :

Cannot execute query. Fatal error. ERROR: bind message supplies 1 parameters, but prepared statement "" requires 0"

When I bound a variable and execute following PL/pgSQL. But the Oracle variant works fine.

try
{
        // This doesn't work
        std::string sql_pg = " \
                               do $$\n \
                               declare\n \
                               n bigint := 1;\n \
                               begin\n \
                               while n <= :1\n \
                               loop\n \
                               insert into ip_table (ip, user) values('0.0.0.1', 'user1');\n \
                               n := n + 1;\n \
                               end loop;\n \
                               end;\n \
                               $$;";

        // This works
        std::string sql_ora = " \
                               declare\n \
                               n number := 1;\n \
                               sql_statement VARCHAR2(500);\n \
                               begin\n \
                               while n <= :1\n \
                               loop\n \
                               sql_statement := q'[insert into ip_table (ip, user) values('0.0.0.1', 'user1')]';\n \
                               execute immediate sql_statement;\n \
                               n := n + 1;\n \
                               end loop;\n \
                               commit;\n \
                               end;";

        int count=4;
        if (session.get_backend_name() == "postgresql")
                session << sql_pg.c_str(), soci::use(count); // error
        else
                session << sql_ora.c_str(), soci::use(count); // works

        std::cout << "executed successfully" << std::endl;
}
catch(const std::exception& err)
{
        std::cout << err.what() << std::endl;
}

I tried both variable binding by position and by name. Nothing works for PL/pgSQL script. If I replace variable by a value, the script works. Any thoughts?

This example might not be the perfect for the question. But think about if I use an if condition with a variable

Answer 1

You cannot do it in Postgres. DO commands doesn't allow external parameters. You can apply parameters (attention on SQL injection) on client side only.