agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedUsing bind variable within BEGIN END
9+ messages / 3 participants
[nested] [flat]
* Using bind variable within BEGIN END
@ 2017-06-02 00:24 anand086 <anand086@gmail.com>
0 siblings, 3 replies; 9+ messages in thread
From: anand086 @ 2017-06-02 00:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using bind variable within BEGIN END
@ 2017-06-02 00:31 David G. Johnston <david.g.johnston@gmail.com>
parent: anand086 <anand086@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2017-06-02 00:31 UTC (permalink / raw)
To: anand086 <anand086@gmail.com>; +Cc: pgsql-sql
On Thu, Jun 1, 2017 at 5: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$
> [...]
>
> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using bind variable within BEGIN END
@ 2017-06-02 00:59 Rob Sargent <robjsargent@gmail.com>
parent: anand086 <anand086@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Rob Sargent @ 2017-06-02 00:59 UTC (permalink / raw)
To: anand086 <anand086@gmail.com>; +Cc: pgsql-sql
I think you'll need to show your code not the error. Postgres version is a good idea. What application stack are you using or 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using bind variable within BEGIN END
@ 2017-06-02 01:35 anand086 <anand086@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: anand086 @ 2017-06-02 01:35 UTC (permalink / raw)
To: 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using bind variable within BEGIN END
@ 2017-06-02 17:29 anand086 <anand086@gmail.com>
parent: anand086 <anand086@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: anand086 @ 2017-06-02 17:29 UTC (permalink / raw)
To: pgsql-sql
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)
RETURNS void 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using bind variable within BEGIN END
@ 2017-06-02 18:21 David G. Johnston <david.g.johnston@gmail.com>
parent: anand086 <anand086@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2017-06-02 18:21 UTC (permalink / raw)
To: anand086 <anand086@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using bind variable within BEGIN END
@ 2017-06-02 18:27 David G. Johnston <david.g.johnston@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2017-06-02 18:27 UTC (permalink / raw)
To: anand086 <anand086@gmail.com>; +Cc: pgsql-sql
On Friday, June 2, 2017, David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Friday, June 2, 2017, anand086 <anand086@gmail.com
> <javascript:_e(%7B%7D,'cvml','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.
https://www.postgresql.org/docs/10/static/sql-syntax-calling-funcs.html
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using bind variable within BEGIN END
@ 2017-06-02 18:57 David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2017-06-02 18:57 UTC (permalink / raw)
To: gulsumramazanoglu <gulsumramazanoglu@gmail.com>; +Cc: anand086 <anand086@gmail.com>; pgsql-sql
On Friday, June 2, 2017, gulsumramazanoglu <gulsumramazanoglu@gmail.com>
wrote:
> David hi, if PostgreSQL doesnt have stored procedures and function can
> only be used by a SQL command, so we cannot make a call to an SQL script
> (of any kind, name it procedure or something) directly from a HLL program..
> do i understand correctly?
>
I don't think so but I cannot figure out what your actual confusion is.
Write a function. Execute a statement that invokes the function. Remain
connected until the function returns. Learn whether it completed
successfully or not and react accordingly. Move on in your HLL (higher
level language?) code.
The term procedure in sql often implies autonomous which client invoked
code cannot presently do (i.e., disconnect the connection after initiating
the code but before it completes).
Your syntax issues are mostly about not having read the right sections of
the docs pertaining to those syntactic issues. Once you get past that you
should be able to do most things that you can imagine.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Using bind variable within BEGIN END
@ 2017-06-02 18:59 David G. Johnston <david.g.johnston@gmail.com>
parent: anand086 <anand086@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2017-06-02 18:59 UTC (permalink / raw)
To: anand086 <anand086@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2017-06-02 18:59 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-02 00:24 Using bind variable within BEGIN END anand086 <anand086@gmail.com>
2017-06-02 00:31 ` David G. Johnston <david.g.johnston@gmail.com>
2017-06-02 01:35 ` anand086 <anand086@gmail.com>
2017-06-02 17:29 ` anand086 <anand086@gmail.com>
2017-06-02 18:21 ` David G. Johnston <david.g.johnston@gmail.com>
2017-06-02 18:27 ` David G. Johnston <david.g.johnston@gmail.com>
2017-06-02 00:59 ` Rob Sargent <robjsargent@gmail.com>
2017-06-02 18:59 ` David G. Johnston <david.g.johnston@gmail.com>
2017-06-02 18:57 Re: Using bind variable within BEGIN END David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox