Обсуждение: [SQL] Using bind variable within BEGIN END
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$ DECLARE rowcount int; BEGIN LOOP INSERT INTO temp_resolution ( SELECT s.from_entity_id,r.source_entity_id FROM temp_resolution as s JOIN temp_resolution as t ON (s.transfer_to_id = t.from_entity_id) LEFT OUTER JOIN relates as r ON ( s.transfer_to_id = r.target_entity_id AND r.relation_type_id = ? ) LEFT OUTER JOIN attributes as a ON ( r.source_entity_id = a.entity_id AND a.attribute_type_id = ? ) WHERE a.attribute_value::numeric <= 10 OR a.attribute_value::numeric = 99 ) ON CONFLICT(from_entity_id) DO UPDATE SET transfer_to_id = excluded.source_entity_id;GET DIAGNOSTICS rowcount = ROW_COUNT; EXIT WHEN rowcount = 0; END LOOP ; END $do$; Parameters: [3, 367] Received the same error while calling a function within BEGIN END code. What is the correct way to use bind variables in postgresql? -- View this message in context: http://www.postgresql-archive.org/Using-bind-variable-within-BEGIN-END-tp5964384.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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.
I think you'll need to show your code not the error. Postgres version is a good idea. What application stack are you usingor is this raw jdbc? > On Jun 1, 2017, at 6:24 PM, anand086 <anand086@gmail.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$ DECLARE rowcount int; BEGIN LOOP INSERT > INTO temp_resolution ( SELECT s.from_entity_id,r.source_entity_id FROM > temp_resolution as s JOIN temp_resolution as t ON (s.transfer_to_id = > t.from_entity_id) LEFT OUTER JOIN relates as r ON ( s.transfer_to_id = > r.target_entity_id AND r.relation_type_id = ? ) LEFT OUTER JOIN attributes > as a ON ( r.source_entity_id = a.entity_id AND a.attribute_type_id = ? ) > WHERE a.attribute_value::numeric <= 10 OR a.attribute_value::numeric = 99 ) > ON CONFLICT(from_entity_id) DO UPDATE SET transfer_to_id = > excluded.source_entity_id;GET DIAGNOSTICS rowcount = ROW_COUNT; EXIT WHEN > rowcount = 0; END LOOP ; END $do$; Parameters: [3, 367] > > > Received the same error while calling a function within BEGIN END code. > > What is the correct way to use bind variables in postgresql? > > > > > > > -- > View this message in context: http://www.postgresql-archive.org/Using-bind-variable-within-BEGIN-END-tp5964384.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Thank you for the update. So seems like bind variable can't be used in "DO" block. We are running on RDS PostgreSQL 9.6. I will update with the code soon. -- View this message in context: http://www.postgresql-archive.org/Using-bind-variable-within-BEGIN-END-tp5964384p5964400.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Another example where we are getting the same error is from the call of the
below code --
ctx.update("" + "begin \n"
+ "
access.register_type( \n" + "
p_entity_system => ?, \n" + "
p_entity_type => ?, \n" + "
p_attribute_name => ?, \n" + "
p_creator_id => ?, \n" + "
p_description => ? \n" + " );"
+ "end;",
systemName, entityType,
attributeName, creatorID,
attributeDescription);
access.register_type is a function
CREATE OR REPLACE FUNCTION access$register_type"( p_entity_system text, p_entity_type text, p_attribute_name
text, p_owner_id text DEFAULT NULL::text, p_sia_admin text DEFAULT NULL::text, p_ad_role text DEFAULT NULL::text,
p_create_role numeric DEFAULT 0, p_description text DEFAULT NULL::text, p_creator_id text DEFAULT NULL::text)
RETURNSvoid AS
$BODY$
--
View this message in context:
http://www.postgresql-archive.org/Using-bind-variable-within-BEGIN-END-tp5964384p5964545.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On Friday, June 2, 2017, anand086 <anand086@gmail.com> wrote:
Another example where we are getting the same error is from the call of the
below code --
ctx.update(""
+ "begin \n"
+ "
access.register_type( \n"
+ "
p_entity_system => ?, \n"
+ "
p_entity_type => ?, \n"
+ "
p_attribute_name => ?, \n"
+ "
p_creator_id => ?, \n"
+ "
p_description => ? \n"
+ " );"
+ "end;",
systemName,
entityType,
attributeName,
creatorID,
attributeDescription);
access.register_type is a function
PostgreSQL doesn't alllow named arguments when calling functions and function calls must be part of a SQL statement: select func(?,?,?,?.?); this applied even to functions that do not return results. IOW, PostgreSQL doesn't have stored procedures.
David J.
On Friday, June 2, 2017, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Friday, June 2, 2017, anand086 <anand086@gmail.com> wrote:Another example where we are getting the same error is from the call of the
below code --
ctx.update(""
+ "begin \n"
+ "
access.register_type( \n"
+ "
p_entity_system => ?, \n"PostgreSQL doesn't alllow named arguments when calling functions and function calls must be part of a SQL statement: select func(?,?,?,?.?); this applied even to functions that do not return results. IOW, PostgreSQL doesn't have stored procedures.
Guess I should have re-read the docs.
Named arguments are a thing so the main problem is that you don't have a select before your function name. Though I would have expected a different error message...
David J.
On Thursday, June 1, 2017, anand086 <anand086@gmail.com> wrote:
I am quite new to postgresql and working with application team to migrate to
postgresql from oracle.
It might help to do some exercises without any reference to Oracle code to learn how to do things from scratch in PostgreSQL before attempting to migrate existing code.
David J.