1. Erwin Anema
  2. PowerBuilder
  3. Monday, 15 July 2024 19:27 PM UTC

Hi all,

While upgrading a legacy application from PB17R3 to PB2022R3 I run into a reproducible issue with calling an MSSQL stored procedure which has an integer output parameter.
The issue is that when the proc's output parameter is null, PB17R3 correctly returns null whereas with PB2022R3 I get 0.

DBMS: SQL Server 2016.
* PB17R3 using ADO.Net 4
DBParm: Namespace='System.Data.SqlClient',DataSource='MYSERVER',Database='MYDB',PROVIDERSTRING='Encrypt=true;TrustServerCertificate=false;App='MyApp',DefaultProcOwner='dbo',CommitOnDisconnect='No',Isolation='Read Committed',DelimitIdentifier='Yes',TrimSpaces=1,OJSyntax='ANSI',DisableBind=0,TrustedConnection=1

* PB2022R3 using ADO.Net 6
DBParm: Namespace='System.Data.SqlClient',Provider='SQL Server',DefaultProcOwner='dbo',DataSource='MYSERVER',Database='MYDB',PROVIDERSTRING='Integrated Security=true;Encrypt=true;Trust Server Certificate=true;App=MyApp',DefaultProcOwner='dbo',CommitOnDisconnect='No',Isolation='Read Committed',DelimitIdentifier='Yes',TrimSpaces=1,OJSyntax='ANSI',DisableBind=0

 

I can reproduce with the procedure simplified to:

create procedure dbo.FR_returnNull @ID int output
as
select @ID = null
go

In Powerbuilder, the procedure is mapped as:

PUBLIC:
subroutine FR_returnNull(REF long ID) rpcfunc alias for "dbo.FR_returnNull"


In PB we call the procedure with:

long ll_ID = 8
SQLCA.FR_returnNull(REF ll_ID)

Results:
Correct: PB17R3: after calling the proc, ll_ID == NULL
Incorrect: PB2022R3 after calling the proc ll_ID = 0

To be sure when I change the stored procedure to select @ID = 5, both PB17 as 2022 return the correct result.


I'm a bit at a loss.

1. There is a difference with ADO versions. Can this be caused by ADO 6. Am I missing some configuration parameter?
2. Or is this changed behaviour somewhere between PB17R3 and PB2022R3

Any idea?

Thanks in advance,
Erwin

 

Erwin Anema Accepted Answer Pending Moderation
  1. Tuesday, 16 July 2024 07:27 AM UTC
  2. PowerBuilder
  3. # 1

Hi Chris,

Thanks for your suggestion.

Unfortunately I don't get this quickly to work (Changed it to "MSO", resulted in "Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) is not installed."; installed the latest MSOLEDB, did not make a difference. I miss the msoledbsql in the OLE Microsoft OLE DB section. Found this topic: https://community.appeon.com/index.php/qna/q-a/pb-2019-r3-msoledbsql-sql-server-not-available-in-database-profiles?limitstart=0#reply-35343 and tried to re-register the DLL; does not make a difference).

Although with some additional work or reinstall while more closely following documentation I can get this to work, but from my perspective, using a different database driver may potentially introduce different issues or changes that I need to make. It would at least also require a time investment to ensure everything still works as was.

So for now I will accept the different behavior, whatever caused it, as a given and will run through the code to ensure it will deal with the different return values.

Kind regards,
Erwin

Comment
  1. Chris Pollach @Appeon
  2. Tuesday, 16 July 2024 11:13 AM UTC
Hi Erwin;

Thanks for letting us know that your problem was alleviated using the MSO DB Client driver!

Yes with any DB driver change, you need to do a full regression test IMHO to be safe. Which of course, can be quite labour intensive. In this case though, it might be worth doing this soon to start up-to-date SS wise. Food for thought.

Regards ... Chris
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 15 July 2024 21:24 PM UTC
  2. PowerBuilder
  3. # 2

Hi Erwin;

  In recent years Microsoft has been abandoning many DB client software for SS. MS has been recommending for a few years now to use it's new MSOLEDBSQL DB client to connect to SS.

  Newer PB versions (like 2022 R3) support this new DB Client as well for some time. I would suggest trying this SS interface. All you need to do is change your SQLCA as follows ...

SQLCA.DBMS = "MSO"

HTH 

Regards... Chris 

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.