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.