Обсуждение: Bound parameter is not substituted
Hello all,
I am trying to create a stored function through ODBC connection,
But the parameter marker inside the function is not substituted.
In the following snippet, '?' is not substituted, and I got a syntax
error around it.
If I replace the '?' with '2', it is OK.
The psqlodbc I am using is 08.02.0400 together with unixODBC:2.2.12 on
OpenSUSE 10.3.
Is there a solution or workaround?
Thank you.
--------------
std::stringstream ss;
ss <<
"create or replace function plpgsql_setDocAttrs() returns
void as $$ "
"declare "
"pid integer; "
"begin "
"pid := ?; " // parameter marker
"end;"
"$$ language plpgsql volatile;"
"select plpgsql_setDocAttrs();";
// prepare statement handler
Stmt stmt();
// prepare the statement
stmt.prepare(ss.str());
stmt.bindNextParameter(
SQL_PARAM_INPUT,
SQL_C_LONG,
SQL_INTEGER,
0,
0,
&id,
0,
0);
stmt.execute();
---------------
ning
ning <mailxiening@gmail.com> writes:
> I am trying to create a stored function through ODBC connection,
> But the parameter marker inside the function is not substituted.
You've written a question mark inside a string literal. It would be
a bug for the software to consider that to be a parameter marker.
It's just a question mark.
regards, tom lane
On Thursday 16 April 2009 1:02:56 am ning wrote: > Hello all, > > I am trying to create a stored function through ODBC connection, > But the parameter marker inside the function is not substituted. > In the following snippet, '?' is not substituted, and I got a syntax > error around it. > If I replace the '?' with '2', it is OK. > The psqlodbc I am using is 08.02.0400 together with unixODBC:2.2.12 on > OpenSUSE 10.3. > Is there a solution or workaround? > > Thank you. > > -------------- > std::stringstream ss; > ss << > "create or replace function plpgsql_setDocAttrs() returns > void as $$ " > "declare " > "pid integer; " > "begin " > "pid := ?; " // parameter marker > "end;" > "$$ language plpgsql volatile;" > "select plpgsql_setDocAttrs();"; > // prepare statement handler > Stmt stmt(); > // prepare the statement > stmt.prepare(ss.str()); > stmt.bindNextParameter( > SQL_PARAM_INPUT, > SQL_C_LONG, > SQL_INTEGER, > 0, > 0, > &id, > 0, > 0); > stmt.execute(); > --------------- > > ning Might want to take a look at this: http://www.commandprompt.com/blogs/joshua_drake/2009/04/escaping_data_madness/ -- Adrian Klaver aklaver@comcast.net
Thank you very much for your reply. Now I understand the cause of the error, but I seems funciton body cannot be constructed dynamically. I tried: ----------- ss << "create or replace function plpgsql_setDocAttrs() returns void as ' " "declare " "pid integer; " "begin " "pid := ' " " || ? || " "'; " "end;" "' language plpgsql volatile;" ---------- When I bind a variable, with value 10, to ?, the string after substitution is ---------- create or replace function plpgsql_setDocAttrs() returns void as 'declare pid integer;begin pid := ' || 10 || '; end;' language plpgsql volatile; ---------- I got the following error: ERROR: syntax error at or near "||" String concatenation operator "||" is not allowed in "create function" statement, which breaks syntax. Is there any way to force string concatenation before function definition? So that the function body is firstly concatenated to --------- 'declare pid integer;begin pid := 10; end;' -------- Then "create function" statement is executed as ------- create or replace function plpgsql_setDocAttrs() returns void as ' declare pid integer;begin pid := 10; end;' language plpgsql volatile; ------ Any suggestion or idea is appreciated. Best regards. ning On Thu, Apr 16, 2009 at 11:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > ning <mailxiening@gmail.com> writes: >> I am trying to create a stored function through ODBC connection, >> But the parameter marker inside the function is not substituted. > > You've written a question mark inside a string literal. It would be > a bug for the software to consider that to be a parameter marker. > It's just a question mark. > > regards, tom lane >
Hello all, I finally found a way to create function dynamically from client side through ODBC connection by referring to http://www.postgresql.org/docs/8.3/interactive/plpgsql-porting.html example 38-6: definiton of function cs_update_referrer_type_proc() The psql command line log is pasted below for reference. First, a stored function function_factory() needs to be installed on server side, which is used to created function dynamically. Please refer to log below for definition of funciton_factory(). Second, we call function_factory() from client side to create a new function whose name, "declare" part and "begin-end" part are dynamically constructed. --------- SQLPrepare( hstmt, "select function_factory('function_eval', 'pid integer;', 'pid := ' || ? || ';');", SQL_NTS ); --------- 'function_eval': function name. 'pid integer': "declare" part of function_eval(). 'pid := ' || ? || ';': "begin-end" part of function_eval(), "?" can be substituted. Third, execute function_eval() -------- SQLPrepare( hstmt, "select function_eval();", SQL_NTS ); -------- I know it's hard to use and maybe not the best solution for dynamic function. Any idea for simpler solution will be appreciated. In fact, if inline SQL PL were supported as DB2: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.sql.doc/doc/r0020490.htm The solution would be straightforward. Someone talked about Inline PL/pgSQL in 2005: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg58796.html But I cannot find anything about implementation in this field. Best regards. ning -------log------------ sample=# CREATE OR REPLACE FUNCTION function_factory(text, text, text) RETURNS void AS $func$ sample$# DECLARE sample$# func_body text; sample$# func_cmd text; sample$# BEGIN sample$# func_body := 'declare '; sample$# func_body := func_body || $2; sample$# sample$# func_body := func_body || 'BEGIN '; sample$# func_body := func_body || $3; sample$# func_body := func_body || ' END;'; sample$# sample$# func_cmd := sample$# 'CREATE OR REPLACE FUNCTION ' || $1 || sample$# '() RETURNS void AS ' sample$# || quote_literal(func_body) sample$# || ' LANGUAGE plpgsql;' ; sample$# sample$# EXECUTE func_cmd; sample$# END; sample$# $func$ LANGUAGE plpgsql; CREATE FUNCTION sample=# select function_factory('function_eval', 'pid integer;', 'pid := ' || 10 || ';'); function_factory ------------------ (1 row) sample=# select function_eval(); function_eval --------------- (1 row) sample=# ------------------ On Fri, Apr 17, 2009 at 2:52 PM, ning <mailxiening@gmail.com> wrote: > Thank you very much for your reply. > > Now I understand the cause of the error, but I seems funciton body > cannot be constructed dynamically. > I tried: > ----------- > ss << > "create or replace function plpgsql_setDocAttrs() returns void as ' " > "declare " > "pid integer; " > "begin " > "pid := ' " > " || ? || " > "'; " > "end;" > "' language plpgsql volatile;" > ---------- > > When I bind a variable, with value 10, to ?, the string after substitution is > ---------- > create or replace function plpgsql_setDocAttrs() returns void as > 'declare pid integer;begin pid := ' || 10 || '; end;' language > plpgsql volatile; > ---------- > I got the following error: > ERROR: syntax error at or near "||" > > String concatenation operator "||" is not allowed in "create function" > statement, which breaks syntax. > Is there any way to force string concatenation before function definition? > So that the function body is firstly concatenated to > --------- > 'declare pid integer;begin pid := 10; end;' > -------- > Then "create function" statement is executed as > ------- > create or replace function plpgsql_setDocAttrs() returns void as ' > declare pid integer;begin pid := 10; end;' language plpgsql volatile; > ------ > > Any suggestion or idea is appreciated. > > Best regards. > > ning > > On Thu, Apr 16, 2009 at 11:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> ning <mailxiening@gmail.com> writes: >>> I am trying to create a stored function through ODBC connection, >>> But the parameter marker inside the function is not substituted. >> >> You've written a question mark inside a string literal. It would be >> a bug for the software to consider that to be a parameter marker. >> It's just a question mark. >> >> regards, tom lane >> >