1. John J
  2. PowerBuilder
  3. Wednesday, 8 January 2020 04:31 AM UTC

Hi All,

I'm trialing PowerBuilder 2019 to upgrade an existing application currently on PB9. Did an auto upgrade using PB2019 and everything compiled well. Modified the code block to connect to MS SQL 2014 DB using OLE DB (I assume thats the best choice?). However while running the code, the code block is not able to get return values from Stored Procedures. Looks like it could be a small Database connection configuration issue?

Can someone please throw some light? Let me know if you need more info.

 

Below are the detals:-

Envronment : Windows 10 64 bit , Powerbuilder 2019 build 2082, MS SQL Server 2014

=====Error Details=====

sqlca.sqlcode = -1

sqlca.sqlerrtext = "The Procedure Has No Return Value. "

sqlca.SQLDBCode = 999

====Connection Parameters====

sqlca.dbms = OLE DB

sqlca.dbparm = PROVIDER='SQLNCLI11',DATASOURCE='<<myDBServer>>',PROVIDERSTRING='trusted_connection=yes'

(Using Windows authentication for DB)

====Copy of failing PB code block (same code runs fine in PB9, connected to same SQL 2014 DB) ======

DECLARE GetConnectionDatabase PROCEDURE FOR @li_retcode = dbo.GetConnectionDatabase
@ApplicationName = :as_appname
,@UserID = :as_userid
,@UsageMode = :ai_mode
,@ReturnPillar = :ls_returnpillar
USING SQLCA ;

EXECUTE GetConnectionDatabase;

If SQLCA.SQLCode = -1 Then
messagebox( iobject.DisplayName, 'Proc Failed: GetConnectionDatabase Error:' + sqlca.sqlerrtext )
CLOSE GetConnectionDatabase ;
Return -1
End If

============================

 

John J Accepted Answer Pending Moderation
  1. Thursday, 9 January 2020 06:04 AM UTC
  2. PowerBuilder
  3. # 1

Thanks Miguel and Chris.

Finally I got this working by simply adding the database name in dbparm variable as below, while all other setting remains intact. Now I can see the results from the SP. Not sure why it wasn't working earlier even though the DB name was explicitly mentioned in the sqlca.database variable?

Anyway now I have others issues in DWs like datetime conflicts and other data truncation. I believe that's something which I need to manually fix as part of upgradation? (Unicode issues?)

 

Modified working SQLCA Setting:-

sqlca.dbparm = 'PROVIDER='SQLNCLI11',DATASOURCE='<<myDBServer>>',PROVIDERSTRING='database=<<MyDBName>>;trusted_connection=yes''

sqlca.dbms = OLE DB
sqlca.database = <<MyDBName>>
sqlca.servername = <<DBServername>>

 

OLD Erroneous SQLCA Setting:-

sqlca.dbparm = 'PROVIDER='SQLNCLI11',DATASOURCE='<<myDBServer>>',PROVIDERSTRING='trusted_connection=yes''

sqlca.dbms = OLE DB
sqlca.database = <<MyDBName>>
sqlca.servername = <<DBServername>>

Comment
  1. Chris Pollach @Appeon
  2. Thursday, 9 January 2020 16:26 PM UTC
That is excellent news John! :-)
  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.