HomePowerBuilderAppeon Documentation Center

PostgreSQL

Limited support for stored procedure
Support for auto-increment column

PowerBuilder apps can connect with the PostgreSQL database through the PostgreSQL ODBC driver. (Any PostgreSQL version supported by the ODBC driver will be supported by PowerBuilder.)

PBODB.ini must be configured first in order for connecting with the PostgreSQL database through ODBC interface. Search "PostgreSQL" in PBODB.ini to view the parameter list, related syntax, and functions. For how to configure the PBODB.ini file, see Adding Functions to the PBODB Initialization File.

Note

When the PostgreSQL database is used in the remote procedure call (RPC), use the inout keyword to define the stored procedure (using out keyword cannot return data), and use subroutine (rather than function) to define the RPC function.

Limited support for stored procedure

PostgreSQL stored procedure is not fully supported:

  • PostgreSQL stored procedure syntax cannot be previewed in the Database Painter or the DataWindow data source.

  • PostgreSQL does not require the argument names for its stored procedures and functions; while PowerBuilder requires the names; therefore PostgreSQL stored procedure with parameters cannot be used as the data source for DataWindow objects.

    PostgreSQL supports the following syntax (with or without argument name): 

    function double triple_price(double) RPCFUNC

    PowerBuilder supports the following syntax (argument name must be existing): 

    function double triple_price(double price ) RPCFUNC

Code example 1:

To workaround this unsupported feature, you can define the stored function to replace the stored procedure, and you should use the INOUT keyword (rather than OUT) when defining the stored function and use the SUBROUTINE keyword (rather than FUNCTION) when defining the RPC function. Below is the code example.

Define the stored function using the INOUT keyword:

CREATE OR REPLACE FUNCTION public.f_test(INOUT arg1 bigint, INOUT arg2 character varying)
  RETURNS record
  LANGUAGE 'plpgsql'
  VOLATILE
  PARALLEL UNSAFE
  COST 100
AS $BODY$
BEGIN
  SELECT 999 INTO arg1;
  SELECT 'Hello from f_test' INTO arg2;
END;
$BODY$;

Set the StripParmNames connection parameter to Yes (or select the "strip parameter names" option in the syntax section of the database profile setup): 

// Profile postgre
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=PostgreSQL35W;UID=postgres;PWD=appeon',StripParmNames='Yes'"

Define the RPC function using SUBROUTINE in the transaction object: 

SUBROUTINE f_test(ref longlong arg1,ref string arg2) RPCFUNC

Call the RPC function in PowerScript:

n_trans   ln_trans
LongLong        ll_arg1
String      ls_arg2

ll_arg1 = 0
ls_arg2 = space(20)
ln_trans = Create n_trans

CONNECT USING ln_trans;
ln_trans.f_test( ll_arg1, ls_arg2 )
DISCONNECT USING ln_trans;

MessageBox(String(ll_arg1), ls_arg2)

Code example 2:

The following code examples show how to call the stored procedure with the dynamic SQL (Format 1 to 4 are supported).

Define the stored procedure in the database:

CREATE OR REPLACE PROCEDURE public.proname(IN i_in integer, INOUT i_out integer)
LANGUAGE plpgsql
AS $procedure$
begin
    i_out = i_in+i_out;
end
$procedure$;

Call the procedure with the dynamic SQL format 3:

PREPARE SQLSA  FROM "call proname(?,?)"  using itr_postgresql;
declare pro3  dynamic procedure  for sqlsa;
execute dynamic pro3  USING :li_int ,:li_inout;
fetch pro3  into :li_inout;
close pro3;

Code example 3:

The following code examples show how to call the stored function and get the ref value.

Define the stored function in the database:

CREATE OR REPLACE FUNCTION public.ado_func_inout(i_in integer, INOUT i_out integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
begin
    i_out = i_in+i_out;

end
$function$;

Define the RPC function in PowerScript:

SUBROUTINE ado_func_inout(long i_in,ref long i_out) RPCFUNC

Call the RPC function in PowerScript:

itr_postgresql.ado_func_inout( 1,ll_inout)

Code example 4:

The following code examples show how to call the stored function and get the return value.

Define the stored function in the database:

CREATE OR REPLACE FUNCTION public.ado_func_ret(OUT io_int integer, OUT io_dec numeric, INOUT io_time time without time zone)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
begin 
    return query select 1,2.123::decimal, '00:00:00'::time;
end 
$function$;

Define the RPC function in PowerScript:

function string ado_func_ret(ref long io_int,ref decimal io_dec,ref time io_time) RPCFUNC

Call the RPC function in PowerScript:

long   ll_test
time   lt_time
dec   ldec_dec
ls_return = itr_postgresql.ado_func_ret(ll_test,ldec_dec,lt_time)

//ls_return result is (1,2.123,00:00:00)

Support for auto-increment column

PostgreSQL supports the auto-increment column with the following two methods:

  • Method 1: GetIdentity='Select currval(''GEN_&TableName'')'

  • Method 2: GetIdentity='Select currval(''&TableName._&ColumnName._seq')'

    Method 2 uses the serial to create the auto-increment column.

These methods require that the sequence name follows the name conversions specified by GetIdentity in pbodb.ini, for example, gen_TableName, TableName_ColumnName_seq. If the sequence name is not in the required format, the identity value cannot be obtained automatically.

These methods also require that the DelimitIdentifier property ("Enclose Table and Column Names in Quotes" option) set to NO. If it is set to YES, the identity value cannot be returned.

Prev  Up  Next
SAP SQL Anywhere  Home  MySQL