• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • PowerServer Web 2017 R3 - Can't call SQL Anywhere stored proc using output parms

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

Resolved PowerServer Web 2017 R3 - Can't call SQL Anywhere stored proc using output parms

  1. How-to
0
Votes
Undo
  1. Ronnie Po
    Ronnie Po
  2. PowerBuilder
  3. Thursday, 16 August 2018 00:39 AM UTC

Hello community,

Using the latest combination of PB 2017 R3 and PS Web 2017 R3 in Windows 10 Pro, I get the following error when calling a stored procedure in a SQL Anywhere 11 database using output parameters:

Error 10999

Parameter[0]: the Size property has an invalid size of 0.

The following steps will produce the error in my setup.

1. Create a simple dummy procedure in the SQL Anywhere database:

create procedure sp_testproc
  @application_name varchar(80) OUTPUT
as
  SELECT
    @application_name = 'Test Web Portal'
;

2. In a PowerBuilder SQLCA UserObject, declare under Local External Functions:

SUBROUTINE sp_testproc( ref string application_name ) RPCFUNC ALIAS FOR "~"dbo~".~"sp_testproc~""

(The ~" was necessary for deployment to Appeon Web and Mobile)

3. Execute the following PowerScript:

string ls_application_name
ls_application_name = fill(" ", 80)
SQLCA.sp_testproc( ls_application_name )

The stored procedure returns with an error, and the above message box displays.

The same code works as expected in client-server mode.

The same code works as expected when deployed to Appeon Web 2016 from PB 12.5.

Any ideas?

web service con parametros por medio de power bui...
SYSAM Problem
Responses (6)
Kai Zhao @Appeon
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Thursday, 16 August 2018 08:02 AM UTC
  2. PowerBuilder
  3. # 1

Hi Ronnie,

Please create ODBC data source by using SQL Anywhere 11 driver to connect to your database.

Regards,
ZhaoKai

Comment
Ronnie Po
  1. Ronnie Po
  2. Thursday, 16 August 2018 16:23 PM UTC
Hi Zhao Kai,



Thanks for the response. I am already using SQL Anywhere 11, and I have tried using ODBC data source, and I'm still getting the error.



One question: does it matter whether I'm using 32-bit or 64-bit PowerServer? I used the default PB 2017 R3 Universal installer, which installed the PowerServer shared components in the Program Files (x86) folder. However, in AEM, only the 64-bit ODBC data sources are visible.
  1. Helpful
Kai Zhao @Appeon
  1. Kai Zhao @Appeon
  2. Friday, 17 August 2018 00:31 AM UTC
Hi Ronnie,



Both 32-bit and 64-bit PowerServer work well on our end. I can reproduce this issue with SQL Anywhere 17 driver only.

Please open a support ticket here so we can resolve it: https://www.appeon.com/standardsupport/search



Ragards,

ZhaoKai
  1. Helpful
There are no comments made yet.
Kai Zhao @Appeon
Kai Zhao @Appeon Accepted Answer Pending Moderation
  1. Thursday, 16 August 2018 06:39 AM UTC
  2. PowerBuilder
  3. # 2

Hi Ronnie,

Thanks for reporting this problem! We reproduced it on our end and will do further research to figure it out. We will keep you posted about the progress.

Do you select SQL Anywhere 11 when install PowerServer? It seems a SQL Anywhere 17 driver issue, please temporary work around this issue by following steps below.
1 Install SQL Anywhere 11.
2 Install PowerServer and select SQL Anywhere 11.

Regards,
ZhaoKai

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 16 August 2018 06:47 AM UTC
Hi Zhao,

Can someone then correct the documentation? It's said not to be supported.

Cheers,

miguelL
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 16 August 2018 04:40 AM UTC
  2. PowerBuilder
  3. # 3

Sorry for the many separate answers, but I'm deploying (yawn ..) and things keep coming to my mind:

I'm using Oracle and I don't have to use the "~" in the declaration. I also remember that I've set somewhere to not delimit with ", let me see if I can find it.... Ah I found it: see the attachment.

 

I Hope It Helps

Attachments (1)
do not mark enclose in quotes.PNG do not mark enclose in quotes.PNG
Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 16 August 2018 04:33 AM UTC
  2. PowerBuilder
  3. # 4

Just to check if your declaration is being found:

 

try to define the test procedure without the variable being OUTPUT.

See if the call then works. If it does work then, then you know the output parameter is not supported. If it doesn't work either, then something is wrong with the declaration.

Comment
Ronnie Po
  1. Ronnie Po
  2. Thursday, 16 August 2018 04:49 AM UTC
Hi Miguel, thanks for the reply. Yes, the original proc was more complex, but I've isolated the issue to this one condition: the use of an OUTPUT parameter. The documentation had some conflicting statements regarding support for output parms, but the call did work in Appeon 2016 and earlier.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Thursday, 16 August 2018 05:13 AM UTC
Hi, Yes, I've also seen that the documentation can be improved. That's why I didn't just give my initial reply. I hope you solve it. Regards.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 16 August 2018 04:24 AM UTC
  2. PowerBuilder
  3. # 5

Have you tried doing the function call using the word REF?

 

SQLCA.sp_testproc( REF ls_application_name )

Comment
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 16 August 2018 04:21 AM UTC
  2. PowerBuilder
  3. # 6

I think Appeon WEB simply doesn't support it:

See all the way down on this page: https://www.appeon.com/support/documents/appeon_online_help/2017/features_help_for_appeon_web/ch07s06.html

"Appeon does not support calling a SQL Anywhere stored procedure with output arguments."

 

regards,

MiguelL

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.