1. Matt Balent
  2. PowerBuilder
  3. Friday, 7 June 2024 15:56 PM UTC

Anyone ever do this successfully?  (PB 2022 R3)

In the postgres database I have defined this FUNCTION 

CREATE OR REPLACE FUNCTION public.rpc_ordersbycustomer(
	custid integer DEFAULT 0,
	INOUT order_count integer DEFAULT 0)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
begin
    select count(*)
	into order_count
	from dbo.order
	where customerid = custid;
end;
$BODY$;

ALTER FUNCTION public.rpc_ordersbycustomer(integer, integer)
    OWNER TO postgres;

Which takes a customer id and returns the number of orders for them.

On my Transaction object I have this:

FUNCTION long rpc_ordersbycustomer(long custid, ref long order_count) RPCFUNC;

And I try to use it in some code in my application:

long ll_orders, ll_custid, ll
string ls
IF row > 0 THEN
	TRY
		ll_custid = this.getitemnumber(row,'customerid')
		
		ll = SQLCA.rpc_ordersbycustomer(ll_custid,ll_orders)
		
		IF SQLCA.Sqlcode <> 0 THEN
			MessageBox('RPC Error', SQLCA.sqlerrtext)
		END IF
		st_ordercount.text = 'CustID: ' + string(ll_custid) + ' Orders: ' + string(ll_orders)
		
	CATCH (runtimeerror er)
		MessageBox('RPC Error', 'Runtime error.~r~n' + er.GetMessage())

	END TRY
END IF

In all cases the number of orders (ll_orders in the above code) always returns 0.  The return from the call ('ll' in the example) is also 0.

Ideas?

Matt Balent Accepted Answer Pending Moderation
  1. Friday, 7 June 2024 21:53 PM UTC
  2. PowerBuilder
  3. # 1

Got it working :) Thanks to you all for your help:

Change to Function in Postgresql:

CREATE OR REPLACE FUNCTION public.rpc_ordersbycustomer(
	custid integer DEFAULT 0,
	INOUT order_count integer DEFAULT 0)
    --RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
begin
    select count(*)
	into order_count
	from dbo.order
	where customerid = custid;
end;
$BODY$;

notice I commented out the RETURNS integer

Change to declaration on SQLCA

SUBROUTINE rpc_ordersbycustomer(long custid, ref long order_count) RPCFUNC;

It's now a SUBROUTINE since it's not returning anything.

Hopefully this helps someone down the line...

 

Comment
  1. Matt Balent
  2. Monday, 8 July 2024 13:41 PM UTC
Yes, nothing since 2019? My assumption is Stored Procedures in postgresql via PowerBuilder are a NO GO.
  1. Helpful
  1. mike S
  2. Monday, 8 July 2024 13:52 PM UTC
have you tried using them in datawindows? IIRC that is the way i had used/tested them when i was doing postgresql stuff several years back.
  1. Helpful
  1. Matt Balent
  2. Monday, 8 July 2024 13:58 PM UTC
In 2022 R3 the datawindow creation wizard throws an error: Function xyz does not exist... even though it was on the list of Procs and is what I chose to create the dw from.
  1. Helpful
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Friday, 7 June 2024 16:25 PM UTC
  2. PowerBuilder
  3. # 2

i have a bunch of functions (working at least in older postgresql).  and  mine look different with the language at the end (yours probably just newer syntax, seems better imo)

example: (note the $$ to start and end the script).  also, i don't use public, i think it is defaulted to the schema like oracle? (i don't recall).  Does yours work when you call it directly in the postgresql tools?

 

CREATE OR REPLACE FUNCTION LEN(argstring text) RETURNS INT 
AS
/******************************************************************************
NOTE: the function script must be in QUOTED. postgres supports $$ as a single quote, making it easier to write.
******************************************************************************/
$$
BEGIN
RETURN length(argstring);
END;
$$ LANGUAGE plpgsql;

 

 

Comment
There are no comments made yet.
Andreas Mykonios Accepted Answer Pending Moderation
  1. Friday, 7 June 2024 16:21 PM UTC
  2. PowerBuilder
  3. # 3

Hi.

For the returning valie, i don't see a retrurn statement in pl/sql function. Was this intended?

Andreas.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.