Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (11)

Resolved Calling a Postgres Procedure

  1. How-to
0
Votes
Undo
  1. Markus Schröder
    Markus Schröder
  2. PowerBuilder
  3. Thursday, 26 September 2019 06:07 AM UTC

Hello,

i am migrating a PowerBuilder 2019 Application from Oracle to Postgres.

When i am connected to Oracle, i call the following procedure
PROCEDURE p_proc(aol_error_nr OUT NUMBER, aos_error_text OUT VARCHAR2)

in this way:

DECLARE dbp_Proc PROCEDURE FOR p_proc();
EXECUTE dbp_Proc;
FETCH dbp_Proc INTO :ll_Return, :as_error;
CLOSE dbp_Proc;

How it is done when i am connected to Postgres? I already set StripParmNames to Yes.

Thanks.

  1. PostgreSQL
  2. PowerBuilder
Appeon theme190 not working after deployment
Smart Card Reading
Accepted Answer
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 28 September 2019 14:50 PM UTC
  2. PowerBuilder
  3. # Permalink

Giving up on Stored Procedures, I finally managed to get some result by simply changing the Stored Procedure to a Function doing minimal changes (I don't think it would be a lot of work, but it depends on the amount of procedures of course):

Instead of :

CREATE OR REPLACE PROCEDURE public.p_test(
  INOUT arg1 bigint,
  INOUT arg2 character varying)
LANGUAGE 'plpgsql'

AS $BODY$
BEGIN
  Select 999 INTO arg1;
  Select 'Hello from p_test' INTO arg2;
END;
$BODY$;

--------------------------------

Do this :

CREATE OR REPLACE FUNCTION public.p_test(
INOUT arg1 bigint,
INOUT arg2 character varying)
RETURNS RECORD
LANGUAGE 'plpgsql'

AS $BODY$
BEGIN
  Select 999 INTO arg1;
  Select 'Hello from p_test' INTO arg2;
END;
$BODY$;

In the transaction object n_tr_postgres I have it declared as a local external function, using RPC SUBROUTINE.

 The call from Powerbuilder (see w_genapp_main, clicked of the only button that's on the window):

longlong all_inout
string as_inout

all_inout = 0
as_inout = space(20)

sqlca.p_test5( REF all_inout, REF as_inout)

MESSAGEBOX(string(all_inout), trim(as_inout))

//Don't forget that strings by REF have to be initialised  (space(20)) here, before the call to the stored //procedure/function.

 

See the attached mini-application (postgres.zip), you'll have to replace the values present in n_genapp_connectservice.of_getConnectionInfo(), so that it'll connect to your database (mine is "test_db")

I've simply used an odbc profile called PostgreSQL35W and create a template app specifying my odbc profile:

ConnectString='DSN=PostgreSQL35W;UID=postgres;PWD=yourPassword',DelimitIdentifier='No',StripParmNames='Yes',PBCatalogOwner='postgres'" )

Attachments (1)
postgres.zip
Comment
Markus Schröder
  1. Markus Schröder
  2. Monday, 30 September 2019 05:42 AM UTC
Hi Miguel,

thanks for your time and effort.

I think i have to rewrite all my procedure calls in our old powerbuilder application. Maybe Appeon will fix this in a future release (or at least document this problem in the help files).

  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 30 September 2019 06:39 AM UTC
Yes, I found a JDBC driver which is NOT free. That one might be more up to date. The support of Appeon is for postgresql 10 and stored procedures only started to exist with V11.5 (october 2018).

I think it's not very cheap though, so maybe it's best to wait until Appeon supports them.



https://www.cdata.com/download/getfile.aspx?file=demo/FPRE-V/setup.zip&name=PostgreSQL%20JDBC%20Driver&tag=



I've downloaded the 30 day trial version and it seems to work ok. Will try with stored procedures again, once I re-install postgress 11.5.

  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 30 September 2019 06:41 AM UTC
By the way, Enterprise DB has a migration from Oracle tool. If you download the trial version, I'm pretty sure it would automatically generate all your stored procedures converted to functions.

Just a thought.

  1. Helpful
There are no comments made yet.
Responses (11)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 30 September 2019 10:42 AM UTC
  2. PowerBuilder
  3. # 1

Just to round up:

 

- I've tried to connect to PB with the https://www.cdata.com/download/getfile.aspx?file=demo/FPRE-V/setup.zip&name=PostgreSQL%20JDBC%20Driver&tag=

JDBC driver.

- Connects fine.

- Re-installed postgresql 11.5, which supports stored procedures.

When running a stored procedure / function it's complaining about not being able to bind the @1 argument.

I guess it needs some kind of StripParmNames='Yes' in the dbparm of the transaction object, but that parameter is not available for JDBC.

 

End my mission, we have to use ODBC for now and use functions instead of stored procedures.

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 30 September 2019 05:45 AM UTC
  2. PowerBuilder
  3. # 2

I've just uninstalled Postgresql 11 and installed v 10.10.2

Then ran pgAdmin and ....


NO stored procedures ... that explains a LOT of PB having problems with them, since Appeon's support for Postgresql was / is based upon v 10 and not 11. (I also discovered that 11 is SERVER only, I wonder how it even could be installed on my windows 10).

Postgresql only supports stored procedures since v. 11.5. released on 10/18/2018: http://thedeveloperspace.com/postgresql-finally-gets-stored-procedures/

 

So I'd say FUNCTIONS is definitely the way to go for now.

 

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 28 September 2019 12:50 PM UTC
  2. PowerBuilder
  3. # 3

I've had exactly the same problems you have.

I've also tried to connect with JDBC, but I'm getting an error:

---------------------------
Appeon PowerBuilder
---------------------------
Java Exception : Fatal Error. Unable to initialize DatabaseMetaData class.
---------------------------
OK
---------------------------

For "Drivername" I'm not sure what to use.

This is the connection profile I've tried to use:

// Profile postgresJDBC
SQLCA.DBMS = "JDBC"
SQLCA.LogPass = <********>
SQLCA.LogId = "postgres"
SQLCA.AutoCommit = False
SQLCA.DBParm = "URL='jdbc:postgresql://localhost:5432/test_db',DelimitIdentifier='No',Driver='org.postgresql.Driver'"

I still think it should be possible to connect with JDBC.

 

 

If you read the comments on this page:

https://community.appeon.com/index.php/articles-blogs/tutorials-articles/2-powerbuilder/179-powerbuilder-2017-r2-new-feature-postgresql-support

 

It all seems clear to me. Though it's about stored procedures and datawindows, it seems like the support for stored procedures with ODBC is still very "green". 

Maybe you could write a FUNCTION that calls your stored procedure and call the function from powerbuilder instead?

 

Regards,

MiguelL

mike S
 
  1. mike S
    2.  
  2.  

be aware that postgresql stored procedure datawindows are not supported.  however, they work if you don't use any parameter(s) in your procedure (function).

  1. Bruce Armstrong
 
  1. Bruce Armstrong
    2.  
  2.  

Yes, I missed that when I was reviewing the capability.  I also didn't covered embedded SQL.  That's probably because I don't use either of those significantly in production applications.

  1. mike S
 
  1. mike S
    2.  
  2.  

stored functions/procedures DO work, just not with parameter names.  

The odbc connection MUST have "strip parameter names" checked/selected.  This is in the syntax section of the database profile setup. 

In the connection string it is:    StripParmNames='Yes'

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 27 September 2019 10:53 AM UTC
  2. PowerBuilder
  3. # 4

Don't know if it might be your case but:

- if your stored procedure returns a value you should declare as FUNCTION instead of SUBROUTINE

 

Here's an example of how we declare an Oracle stored procedure which returns an oracle numeric value:

- function double F_BUCKET_PROMISE_VALUE(integer T_BUCKET, string T_CUSTOMER, string T_USERID, string showall) RPCFUNC ALIAS FOR "CREDIT.F_BUCKET_PROMISE_VALUE"

 

We also have to specify the OWNER in the ALIAS (in the example that's CREDIT).

Have you tried with a very simple stored procedure? (No bigint)

HTH

 

 

Comment
Markus Schröder
  1. Markus Schröder
  2. Friday, 27 September 2019 12:50 PM UTC
Hi Miguel,

with Oracle we never had any problems (functions, procedures, packages).



If i call a simple postgres function ( public.p_test(aol_error_nr int) returns int ) it works.

If i call a simple postgres procedure ( public.p_test(aol_error_nr int) ) PowerBuilder gives me an error:

SQLSTATE = 42809

ERROR: public.p_test(unknown) is a procedure;

Error while preparing parameters



My PowerBuilder Code is

integer li_in

declare p_proc PROCEDURE FOR public.p_test(:li_in);

execute p_proc;

close p_proc;

A RPCFUNC call in the transaction object results in the same error.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 27 September 2019 13:12 PM UTC
Hmm, I have once made a test application for Appeon to prove that there's huge differences (and errors) between ODBC, JDBC and OLEDB when it comes to calling a stored procedure.

The problem was that it was with the database "Tibero" (tmaxsoft).

For us, the only connection that works well on all levels with Tibero resulted to be the JDBC connection.

I know there's a pgJDBC for Postgres. Most probably if you could try with a JDBC connection, all of your problems (as long as you use RPC external function declarations) will be gone.

For example, you could connect to a second transaction object, only to run your stored procedures.



I've downloaded Postgresql - as I think it's a very nice option for DB - and when I have some time, I'll do some testing myself.
  1. Helpful
There are no comments made yet.
Markus Schröder
Markus Schröder Accepted Answer Pending Moderation
  1. Friday, 27 September 2019 09:47 AM UTC
  2. PowerBuilder
  3. # 5

I think it's a problem using Postgres procedures in PowerBuilder.

I can call Postgres functions without problems, but i can't call Postgres procedures.

 

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 27 September 2019 09:01 AM UTC
  2. PowerBuilder
  3. # 6

It seems it doesn't like BigInt.

Maybe it'll work if you use "Numeric..." or any other type in your stored procedure?

 

Comment
There are no comments made yet.
Markus Schröder
Markus Schröder Accepted Answer Pending Moderation
  1. Friday, 27 September 2019 07:19 AM UTC
  2. PowerBuilder
  3. # 7

Thanks for all the help, but it is still not working.

I created a Postgres procedure
public.p_test(INOUT aol_error_nr bigint, INOUT aos_error_text varchar) ...

In the transaction object i added a local external function (via Paste Special / SQL)
function string p_test(ref longlong aol_error_nr,ref string aos_error_text) RPCFUNC

(in a second try i changed this to
subroutine p_test(ref longlong aol_error_nr,ref string aos_error_text) RPCFUNC
)

My script in PowerBuilder 2019:

longlong aol_error_nr
string aos_error_text
SQLCA.p_test(aol_error_nr, aos_error_text)

results in the error

SQLSTATE = 42809
ERROR: p_test(bigint, unknown) is a procedure;
Error while executing the query

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 26 September 2019 19:46 PM UTC
  2. PowerBuilder
  3. # 8

Hi Samuel;

  I always cheat (work smarter - LOL) by using a SP based DataWindow. The SP/DW will figure out all that SQL syntax at run time.

Tip: The DW Painter is actually a DataWindow. Try using an "Execute sp_name args " command in the iSQL pane and see if the result set is displayed. If it is, your looking at a DW object handling the SP call. Now, if your really into hand coding in-line SQL .. just set your DB Profile that connects to PostGreSQL to use a TRACE. Then execute the iSQL "execute" again. The trace file will show you how the SP based DWO does the in-line SQL. However, the DW is running this in its "assembler" code(@). Now inspect the DWO's DML to yours. Maybe you will see the SP issue (syntax wise).

HTH

Regards ... Chris

@ - Yes ... DW's use assembler and C++ at run-time - that is why they are a lot faster than your in-line SQL. 

Comment
There are no comments made yet.
David Peace (Powersoft)
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Thursday, 26 September 2019 13:28 PM UTC
  2. PowerBuilder
  3. # 9

Hi

If you open the help you will find that there are multiple pages describing how to do this for different database connections types. Oracle was very specific.

Hope that helps

David

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 26 September 2019 10:53 AM UTC
  2. PowerBuilder
  3. # 10

Not on Postgres, but probably you're best off by declaring your stored procedures as local or global external RPCFunctions.

See:

https://www.brucearmstrong.org/2018/02/powerbuilder-2017-r2-new-feature.html

 

 

Comment
Olan Knight
  1. Olan Knight
  2. Thursday, 26 September 2019 18:54 PM UTC
Great information, Michael! Thank you!
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 26 September 2019 20:07 PM UTC
Lol, you're welcome, though the merits are all for Bruce Armstrong who wrote that article.
  1. Helpful
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.