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

Resolved PowerServer Web - Are OUTPUT Parms Supported for MSS RPC?

  1. How-to
0
Votes
Undo
  1. Tom Peters
    Tom Peters
  2. PowerServer 2020 or older (Obsolete)
  3. Thursday, 8 November 2018 14:21 PM UTC

Good Day!

Based on what I see in the community and the docs, PowerServer Web does support OUTPUT parameters for SQL Server. However, my RPC does NOT work when depoyed to PowerServer, yet is works just fine in PB 2017. Same source...

RPC Declaration:
subroutine MySP( string P_TableName, long P_IDsRequested,ref long P_UIDStart, ref long P_IDsAllocated ) RPCFUNC ALIAS FOR "dbo.MySP"
 
SQL Server SP Signature:
CREATE PROCEDURE dbo.MySP
  @P_TableName      CHAR(30)
, @P_IDsRequested   INTEGER
, @P_UIDStart       INTEGER OUTPUT
, @P_IDsAllocated   INTEGER OUTPUT
AS...
 
When I call this SP via RPC, it works fine in PB2017, but when I call it in PowerServer Web deployment, I get no error messages or bad codes from SQLCA, yet my output/ref parms are NOT populated. None of my arguments are NULL, by the way. All are initialized prior to calling the RPC.
 
I checked the database before and after the call and it seems the SP is not even firing. I have a SEQUENCE I can check to see if it ran and it's not running.
 
Any ideas? 
 
Thanks,
Tom
  1. PowerServer
where I can download "PostgreSQL Native Drive" (fo...
timeframe for adding encryption to powerserver
Accepted Answer
Tom Peters
Tom Peters Accepted Answer Pending Moderation
  1. Monday, 12 November 2018 16:39 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # Permalink

OK, thanks to everyone who contributed! I found the issue and 99% blame is mine while 1% is PowerServer's, so I will focus on the 1%.

When I tried to see the difference between my app and the little test app I made, I created a second transaction object in my main app and tried to use it for testing. I had forgotten to add the transaction object to my PowerServer profile and I got a meaningful message from PS telling me that 'inv_tr_mssl' isn't defined. That was awesome!

That made me look more closely at my application's calls and I found 2 things PS didn't like:

  • SQLCA.DYNAMIC 
  • lnv_tr.DYNAMIC (I did not have lnv_tr defined in my profile)

My application supports both Oracle and SQL Server, so we instantiate n_tr as SQLCA at startup, creating an Oracle OR MSSQL descendent of n_tr, as appropriate. Therefore, all of our RPC calls are DYNAMIC.

In my little test app, I do call it as DYNAMIC, but since my SQLCA variable is only set to ONE transaction object, everything works just fine.

Once I changed my main application's RPC call to be static and I hard-coded my SQL Server transaction class into the SQLCA variable… everything worked.

So, the aforementioned was all me missing the nuances of DYNAMIC, but I wonder why PS didn't yell at me for not having lnv_tr defined. Regardless, the key was statically defining my SQL Server transaction class as SQLCA and doing only static calls to my RPC.

Thanks all for your input. It all helped me look in the right places. By the time I get this all working I should be able to get a CPD (Certified PowerServer Developer) Pro certificate.

Thanks, again!!!!!!

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 12 November 2018 16:45 PM UTC
Glad you worked it out (good info for me too by the way).
  1. Helpful
There are no comments made yet.
Responses (6)
Tom Peters
Tom Peters Accepted Answer Pending Moderation
  1. Thursday, 8 November 2018 20:43 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 1

OK, I created a vanilla application and went with the DBParm generated by PowerBuilder's Template Application. I am able to get my results back, so it's my DBParm, for sure. Stay tuned for the answer!

Comment
Tom Peters
  1. Tom Peters
  2. Monday, 12 November 2018 14:01 PM UTC
This is very odd. In my vanilla application I use the EXACT same SQLCA settings as in my real application. The SP and the calls are the same... It works in PS when I deploy my vanilla app, but not my real application.
  1. Helpful
There are no comments made yet.
Kai Zhao @Appeon
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Friday, 9 November 2018 02:50 AM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 2

Hi Tom,

The Input & output parameters of stored procedure are supported,
https://www.appeon.com/support/documents/appeon_online_help/2017/features_help_for_appeon_web/ch07s06.html

Please note that PowerServer does not support to pass in an Instance variable by REF, please use the local variable instead of the instance variable to work it around.

Regards,
ZhaoKai

Comment
Tom Peters
  1. Tom Peters
  2. Monday, 12 November 2018 13:12 PM UTC
The reference arguments are local variables... Hmmmm…. still thinking...
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 9 November 2018 09:44 AM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 3

have you tried using 

"PBNewSPInvocation='yes' in your dbparm parameter? 
(though that's ODBC only, not your case I think)

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 9 November 2018 09:50 AM UTC
As an addition to what Zhao Kai said: don't pass instance variables by ref, there's another thing powerserver doesn't like:



Any parameter with a "." (dot) in it, such as:

SQLCA.USERID

or

myobject.variable

etc.
  1. Helpful
There are no comments made yet.
Tom Peters
Tom Peters Accepted Answer Pending Moderation
  1. Monday, 12 November 2018 13:08 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 4

I do have PBNewSPInvocation='Yes' in DBParm, and it still doesn't work in PowerServer. What's interesting is that the client/server PB target works with or without that parameter. Let me look into the Instance Variable usage.

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 12 November 2018 13:12 PM UTC


yes, but I'd take it out. (As I said), it's only for ODBC connections.

Sorry if I caused any confusion.
  1. Helpful
Tom Peters
  1. Tom Peters
  2. Monday, 12 November 2018 13:13 PM UTC
Let me clarify. It doesn't work in PS with or without that parameter.
  1. Helpful
Tom Peters
  1. Tom Peters
  2. Monday, 12 November 2018 13:14 PM UTC
Sorry for my causing confusion...
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 12 November 2018 14:45 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 5

Is your powerserver installation 64 bit? (Which is what it will be when installing with default parameters).

Do you have a 32 and a 64 bit client installed on your pc for MS-SQLServer?

Powerbuilder needs the 32 bit client and powerserver would use the 64 bit client if I'm not wrong.

Can you call any other RPC's successfully? (without output parms maybe?)

Comment
Tom Peters
  1. Tom Peters
  2. Monday, 12 November 2018 15:50 PM UTC
I just tried a few of our standard RPCs and none seem to be working. Now, I only have one PowerServer with one DataSource. I use this as SQLCA in both my little test application (which works) and my marketed application (which does not). Same client, same PowerServer, same transaction object name and DataSource, same RPC calls, same transaction object settings in both targets. I'm running out of ideas, here...
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 12 November 2018 16:35 PM UTC
Could it be that it fails due to something else failing before you do the call? Have you tried calling the stored procedure right after you have established a connection?

You say your "vanilla" application does work on powerserver?
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Monday, 12 November 2018 16:36 PM UTC
Can you paste the code of the call to your procedure?
  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.