Re: how to call stored procedures with output parameters

This seems to be a very difficult question as I haven't receive any
answer yet from the list.
I managed though after several trial and error tries to succeed to call
a stored procedure from java.
It seems that stored procedures behave different in PostgreSQL compared
to MySQL and Oracle.

One big difference is that if you stored procedure/function (PostgreSQL
has only functions not procedures) returns something only using the
keyword RETURNS then when calling the statement you should register the
out parameter as the first parameter in the callable statement.

If on the other hand you declare in you stored procedure several OUT
parameters then you may address all the parameters in the correct order
as they are declared.

Connection connection = null;
CallableStatement callableStatement = null;
ResultSet resultSet = null;
...
callableStatement = connection.prepareCall("{call
aaa.validate_credential(?, ?, ?, ?)}");
callableStatement.setInt(1, principalId); //p_pk_principal_id_in IN
aaa.principals.pk_principal_id%TYPE
callableStatement.setString(2, type); // p_credential_type_in IN
aaa.credentials.credential_type%TYPE
callableStatement.setString(3, password); // p_credential_value_in IN
aaa.credentials.credential_value%TYPE
callableStatement.registerOutParameter(4, Types.INTEGER);
//p_return_code_out OUT INTEGER

everything else is as usuall.
Now I am still having problem on how to call some stored procedures from
the console.
It seems that in console you just forget the OUT parameters
SELECT aaa.validate_credential(1, 'password', 'secret');

and the same goes inside the function you just assign the out parameters
to variables

SELECT aaa.validate_credential(1, 'password', 'secret') INTO v_return_code;

If there are several out parameters then in the console you must call
the function like
SELECT * FROM function(...

and in the PL/PgSQL
SELECT aaa.function(1,2) INTO v_1, v_2, v_3 v_4;

My problem is when the function returns several values and especially a
record. e.g.
CREATE OR REPLACE FUNCTION aaa.authenticate (
p_name_in IN aaa.principals.principal_name %TYPE,
p_passwd_in IN aaa.credentials.credential_value%TYPE,
p_pk_principal_id_out OUT aaa.principals.pk_principal_id%TYPE,
p_groups_out OUT refcursor,
p_return_code_out OUT INTEGER)

to call this you should call it like this

SELECT * FROM aaa.authenticate('myuser', 'secret');

the problem is that I get this

<unnamed portal 4> 0

How do I see the values inside the <unnamed portal 4>??????

Can someone please help me on this.

Nikolas

Στις 18/3/2011 2:09 μμ, ο/η java4dev έγραψε:
> Hello,
>
> I am having problem with PostgreSQl syntax.
> setting values anf how to call stored procedures with output parameteres.
> From what I have seen by googling the subject it seems that PostgreSQL
> requires that you do not provide the out parameters.
>
> two tables
>
> DROP SCHEMA IF EXISTS aaa CASCADE ;
> CREATE SCHEMA aaa
> AUTHORIZATION myuser;
>
> CREATE TABLE aaa.principals (
> pk_principal_id INTEGER NOT NULL,
> principal_name CHARACTER VARYING(200) NOT NULL,
> CONSTRAINT IXP_PK_PRINCIPAL_ID PRIMARY KEY (pk_principal_id)
> );
>
> CREATE TABLE aaa.credentials (
> pk_credential_id INTEGER NOT NULL,
> fk_principal_id INTEGER NOT NULL,
> credential_value CHARACTER VARYING(254),
> credential_type SMALLINT NOT NULL,
> CONSTRAINT IXP_PK_CREDENTIAL_ID PRIMARY KEY (pk_credential_id),
> CONSTRAINT FK_AAA_CREDENTIAL_1 FOREIGN KEY (fk_principal_id)
> REFERENCES aaa.principals (pk_principal_id) ON DELETE CASCADE
> );
>
> a few things I am trying to do and they do not work
>
> CREATE OR REPLACE FUNCTION aaa.unlock_principal(p_pk_principal_id_in
> IN aaa.principals.pk_principal_id%TYPE)
> RETURNS void
> AS $BODY$
> BEGIN
> UPDATE aaa.principals SET aaa.principals.is_unlocked = TRUE
> WHERE aaa.principals.pk_principal_id = p_pk_principal_id_in;
> END;
> $BODY$ LANGUAGE plpgsql VOLATILE;
>
> ALTER FUNCTION
> aaa.unlock_principal(aaa.principals.pk_principal_id%TYPE) OWNER TO
> myuser;
>
> I am calling this function from the console like this
>
> SELECT aaa.unlock_principal(10);
>
> In this function it seems that aaa.principals.is_unlocked is wrong.
> What are the rules for fully qualifying the name of a field or a table?
>
> I also have the following
>
> CREATE OR REPLACE FUNCTION aaa.validate_credential (
> p_pk_principal_id_in IN aaa.principals.pk_principal_id%TYPE,
> p_credential_type_in IN aaa.credentials.credential_type%TYPE,
> p_credential_value_in IN aaa.credentials.credential_value%TYPE,
> p_return_code_out OUT INTEGER)
> AS $BODY$
> DECLARE
> v_credential_value aaa.credentials.credential_value%TYPE;
> BEGIN
> p_return_code_out := 0; -- C_OPERATION_SUCCESFULL;
>
> BEGIN
> SELECT credential_value INTO STRICT v_credential_value FROM
> aaa.credentials
> WHERE fk_principal_id = p_pk_principal_id_in AND
> credential_type = p_credential_type_in;
> EXCEPTION
> WHEN TOO_MANY_ROWS THEN
> p_return_code_out := 3; -- C_TOO_MANY_ROWS; -- too many rows
> with same id, consistency problem
> RETURN;
> WHEN NO_DATA_FOUND THEN
> p_return_code_out := 42; -- C_CREDENTIAL_DOES_NOT_EXIST;
> RETURN;
> END;
>
> IF v_credential_value <> p_credential_value_in THEN
> p_return_code_out := 44; -- C_WRONG_CREDENTIAL; -- wrong password
> RETURN;
> END IF;
>
> END;
> $BODY$ LANGUAGE plpgsql VOLATILE;
>
>
> the questions here are
> how do I call this function from the console?
> e.g. in MySQL you just define a variable @return as the out parameter
> call aaa.validate_credential(10, 0, 'user', @return);
>
> what is the right syntax in PostgreSQL?
>
> how do I call this function form inside another function?
>
> If a function has 3 out parameters , e.g
> CREATE OR REPLACE FUNCTION aaa.authenticate (
> p_name_in IN aaa.principals.principal_name %TYPE,
> p_passwd_in IN aaa.credentials.credential_value%TYPE,
> p_pk_principal_id_out OUT aaa.principals.pk_principal_id%TYPE,
> p_groups_out OUT refcursor,
> p_return_code_out OUT INTEGER)
>
> how do I call this from the psql console and how do I call this
> function form inside another function?
>
> thank you
>
> Nikolas
>
>
>
>
>
>
>
>
>