From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | anand086 <anand086(at)gmail(dot)com> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Using bind variable within BEGIN END |
Date: | 2017-06-02 00:31:06 |
Message-ID: | CAKFQuwZYqwvo+yxZGb9U=iciN5wC_+F_aim8_kZDPqfTEbekpw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thu, Jun 1, 2017 at 5:24 PM, anand086 <anand086(at)gmail(dot)com> wrote:
> Hi,
>
> I am quite new to postgresql and working with application team to migrate
> to
> postgresql from oracle.
>
> When we are trying to use bind variable within BEGIN/END code block, it
> fails with
>
> Caused by: java.sql.SQLException: The column index is out of range: 1,
> number of columns: 0. Query: DO $do$
> [...]
>
> What is the correct way to use bind variables in postgresql?
>
CREATE FUNCTION func(arg1 text, arg2 text) AS $$ SELECT arg1, arg2; $$
LANGUAGE sql; --or something like this
SELECT func(?, ?);
Explanation:
You cannot bind into a DO block because the content of the DO block is text
and Java will not bind to question marks within text. Creating a formal
function and then calling it using a normal SELECT statement with binding
positions - i.e., typical function execution - is thus required.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2017-06-02 00:59:50 | Re: Using bind variable within BEGIN END |
Previous Message | anand086 | 2017-06-02 00:24:08 | Using bind variable within BEGIN END |