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

Resolved How To Declare Function Call

  1. How-to
0
Votes
Undo
  1. Olan Knight
    Olan Knight
  2. PowerBuilder
  3. Thursday, 21 July 2022 21:27 PM UTC

PowerBuilder 2019R3 b2703
Windows 64 bit platform
PostgreSQL database


I have declared a FUNCTION in the PG database as follows:
         f_cabs_glextract_master (vin_date IN character varying, session_id OUT character varying) 

What I need to know is how to both DECALRE and to CALL this function from my PowerBuilder code. It's not in a DLL, it's in the database.
The goal is to run the function, then read the output variable "session_id" so I can use it in my PB code.

Thank you!

~~~~~~~~~~~~~~~~~


Note that for a stored procedure, this works; sqlcG is the transaction object connected to the PG database:

// Start the dynamic call to run the stored procedure
DECLARE seq_cursor1 PROCEDURE FOR sp_cabs_glextract_master
@vIn_Date = :ls_date
USING SQLCG;


EXECUTE seq_cursor1;

If sqlcG.SQLCode < 0 Then
    ls_hdr = 'Error Calling Stored Procedure'
    ls_err = 'Error running seq_cursor1():  ~r~n~r~n' + sqlcG.SQLErrText
    GOTO Exit_script
End If

               <...... code ....>

Exit_script:
    // Close the cursor
    CLOSE seq_cursor1;

    IF (ls_err = "") THEN
        
        sqlcG.of_commit();

    ELSE
        sqlcG.of_Rollback()
        cb_connect.POST SetFocus ()
    END IF

Can I drag file names from File Manager and drop t...
Theme specific color column
Responses (3)
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Friday, 22 July 2022 07:00 AM UTC
  2. PowerBuilder
  3. # 1

I do see that your function has an out param. I didn't pay attention when I first read your question.

As it has only on out param, and it currently doesn't returns something, can it be modified so the out param become a return value?

Also, I don't have experience with postgreSQL, but as it' doesn't currently have a return value, can it be transformed to a procedure with the second argument as out? Maybe then the declaretion you tested (DECLARE seq_cursor1 PROCEDURE FOR sp_cabs_glextract_master) will work...

Andreas.

Comment
Olan Knight
  1. Olan Knight
  2. Friday, 22 July 2022 23:41 PM UTC
Thanks for the response, Andreas!
  1. Helpful
There are no comments made yet.
Andreas Mykonios
Andreas Mykonios Accepted Answer Pending Moderation
  1. Friday, 22 July 2022 06:01 AM UTC
  2. PowerBuilder
  3. # 2

Hi.

We usually called such functions using select statements. In sql anywhere we were using something like that:

select myFunction(:arg1, :arg2)
into :retVal
from dummy;

I think this was working in a similar way in sybase ASE and sql server.

Never tried to do the same in Oracle or PostgreSQL.

Also, solution provided by René is a good one.

Andreas.

Comment
There are no comments made yet.
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Friday, 22 July 2022 05:22 AM UTC
  2. PowerBuilder
  3. # 3

Have you tried to define the db function as external function?

https://docs.appeon.com/pb2019/application_techniques/ch12s03.html

I know it works with Oracle but I have never tried it with PostgreSQL.

Comment
Olan Knight
  1. Olan Knight
  2. Friday, 22 July 2022 23:41 PM UTC
Thanks for the response, Rene!
  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.
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.