Calling PL/SQL functions with PowerBuilder 12

[SWilbaux]

Hello,

We are developing a PowerBuilder application that, among other things, execute functions and procedures stored on an ORACLE database (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0).

Recently, we migrated the code from
PowerBuilder 9.0.3 build 8836
to
PowerBuilder 12 Classic build 5530

And some calls to these ORACLE functions and procedures do not work anymore.

In the PowerBuilder code, we have our own transaction object (inherited from 'Transaction').
In the 'Local External Functions' of our 'Transaction' object, we have the following code:

FUNCTION long cpt_exists (string snumcpt, ref Long rbExist) &
RPCFUNC ALIAS FOR "pfp_gstcpt_pkg.cpt_exists"

FUNCTION long cpt_exists (string snumcpt, REF Long rbExist &
   , REF string rs_msgkey, REF string rs_msg, REF string rs_msgdb ) &
RPCFUNC ALIAS FOR "pfp_gstcpt_pkg.cpt_exists"

In the ORACLE database, we have the following definitions for these stored functions:

CREATE OR REPLACE PACKAGE Pfp_Gstcpt_Pkg
...
    FUNCTION    cpt_exists (
                numcpt_in    IN        ARI_200_COMPTES.NUMCPT%TYPE,
                trouve       OUT       NUMBER,
                err_cod_out  OUT       VARCHAR2,
                err_msg_out  OUT       VARCHAR2,
                err_dbg_out  OUT       VARCHAR2)
    RETURN INTEGER;

    FUNCTION    cpt_exists (
                numcpt_in    IN        ARI_200_COMPTES.NUMCPT%TYPE,
                trouve       OUT       NUMBER)
    RETURN INTEGER;

When we test it in the development environment, everything works fine.

When we test it with the deployed version (.exe + .pbd) in the test environment, it seems like PowerBuilder is not able to call the function 'cpt_exists' (2 arguments) of the package 'Pfp_Gstcpt_Pkg' anymore [We have a logging system on the ORACLE database that shows that neither the function neither the package are executed].

What's weird, it's that PowerBuilder does not throw an exception.
What's weirder, it's that when we call other functions than the function 'cpt_exists', there is no problem.

In addition to the '.exe' et '.pbd' files, we install the following files during the deployment:
- atl71.dll
- Libjcc.dll
- Libjutils.dll
- msvcr71.dll
- msvcp71.dll
- pbdir120.dll
- pbdwe120.dll
- pbdwr120.dll
- pbdwr120.pbd
- pbjag120.pbd
- pbjvm120.dll
- pbo90120.dll
- pbo10120.dll
- pbrtc120.dll
- pbshr120.dll
- pbtra120.dll
- pbvm120.dll


Thanks in advance for your help,
Sophie.

Development and Test environments
- OS version: Windows XP
- PowerBuilder 12 Classic build 5530
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
- Connecting mode to the database: DBMS=O10 Oracle 10g
- Architecture: Client/Server

 

[miguelleeuwe]

from version 10 and up, pb is unicode. You might have to use the 'ansi' keyword in your local external function declarations.

regards,
Miguel L.

 

[thekl0wn]

As Miguel stated, you will need to use the ansi keyword in the function declaration when passing a string. From the PB 10.5 help file for external functions:

"ansi - Required if the function passes a string as an argument or returns a string that uses ANSI encoding. Even if you use the default name for an ANSI function, you must always use the ALIAS keyword if you want to specify that the string uses ANSI encoding, because you must qualify the ALIAS with the ansi keyword

 

[SWilbaux]

Thank you Miguel and thekl0wn.
I tested your option; but it seems like RPCFUNC does not support the notation " ALIAS FOR "extname{;ansi}" " that can be used for external functions stored in .DLL libraries.

 

[mbalent]

Have you tried eliminating the polymorphism of the functions? By this I mean duplicating one of them (and giving it a different name), changing the declaration in your transaction object, and changing the calls within your PB app?

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[miguelleeuwe]

sorry Sophie, been away for a few days.
the weird thing is that is works in development environment.

You could try using 'dependency walker' (downloadable for free) and check which dll's are being called in both environment. There might be a diference.

regards,
Miguel L.