• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerServer
  • Dynamic SQL Type 4 Powerserver 2022 - null SQLDA.OutParmType values

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

Resolved Dynamic SQL Type 4 Powerserver 2022 - null SQLDA.OutParmType values

  1. Issue
0
Votes
Undo
  1. Jim Nesbitt
    Jim Nesbitt
  2. PowerServer
  3. Tuesday, 13 December 2022 11:37 AM UTC

Our applications use Dynamic SQL Type 4 to execute SQL server select statements and stored procedures which return a single row. 

We use Dynamic SQL Type 4 as we have a user object that executes the SQL and does all the necessary checking. It returns the result-set columns as an array of any values. 

This works fine in our existing Powerbuilder 2017 application. When I run the converted code in PowerServer 2022, I got errors assigning the returned values to typed powerbuilder variables.

I've attached a picture showing the debugger screen. All the SQLDA.OutParmType values are returned as NUL. The CASE statement executes the command assigningTypeString, so all the returned array any types are all strings. This causes an error when the returned value is assigned to a numeric variable.

It can be resolved by explicitly converting the any variable

ll_curr_connections =     aa_data [1]  to ll_curr_connections =     INTEGER ( aa_data [1] )

but this function is called numerous times. 

 

 

 

 

 

 

 

  1. PowerServer
  2. SQL Server
PowerServer App Updates
Missing interfaces tab in Powerserver project
Responses (6)
Jim Nesbitt
Jim Nesbitt Accepted Answer Pending Moderation
  1. Tuesday, 13 December 2022 17:18 PM UTC
  2. PowerServer
  3. # 1

Hi Armeen and Chris

Apologies - this is a little embarrassing - I checked the SQLDA.OutParmTypes in the PB2017 version and they are also null. I think it must have worked at one time as I did a lot of debugging to get the code working, but that was back in PB6.5

It's also not consistent, as some select statements are returning integer values correctly, which may be due to selecting a column as opposed to a calculation - I need to investigate this further to see what is going on.

I think Chris has probably got the correct explanation for the conversion error occurring, but I should be able to confirm this with further testing

Will get back on this. Thanks for all the suggestions.

Cheers

 

Jim

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 13 December 2022 18:04 PM UTC
Glad to hear you have narrowed it down and thanks for explaining!
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 13 December 2022 13:28 PM UTC
  2. PowerServer
  3. # 2

Hi Jim;

   I suspect that the requirement to now explicitly state the data type conversation is due to PB2022 being based on C++ 2019 VS PB 2017 which was based on C++ 2010. The newer C++ version being more strict of its handling of the ANY data type.

Regards... Chris 

Comment
Jim Nesbitt
  1. Jim Nesbitt
  2. Tuesday, 13 December 2022 15:17 PM UTC
Hi Chris



Thanks for the quick response. - I believe the problem is because the SQLDA.OutParmType values are returned as NULL - as per screenshot. Somehow the first case statement is executed for a NULL, so the returned array of datatypes contains all strings and assigning the numeric string to an integer variable fails,



Numeric values should have added to the array using the ELSE Case which uses GetDynamicNumber so that the value of OutParmType is TypeByte!, TypeInteger!, TypeDouble!, TypeLong!, TypeReal!, or TypeBoolean! and assigning this to a numeric data type would work.



Thanks



Jim

















  1. Helpful
Jim Nesbitt
  1. Jim Nesbitt
  2. Tuesday, 13 December 2022 20:38 PM UTC
Hi Chris - I think this is a Powerserver issue. The version change may have highlighted it. I've replied and attached a PDF showing details showing testing code. I wasn't sure if you would be updated or not. Cheers Jim

  1. Helpful
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 13 December 2022 15:06 PM UTC
  2. PowerServer
  3. # 3

Hi Jim, After you upgraded to PowerBuilder 2022, does it work correctly in client/server mode?  Trying to ascertain if it is a version upgrade issue as Chris mentioned or if it is an unsupported feature of PowerServer.

Comment
Load more comments
Jim Nesbitt
  1. Jim Nesbitt
  2. Tuesday, 13 December 2022 15:50 PM UTC
Hi Armeen



I've tried it in PowerBuilder 2022 and same is happening with SQLDA.OutParmTypes returning nan array of nulls as well. The app was upgraded from PB2017r3.



Thanks



Jim



  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 13 December 2022 15:53 PM UTC
So then this is a PowerBuilder upgrade issue not a PowerServer issue. I would focus your efforts first make sure app runs correctly as client/server before you evaluate PowerServer. The following guide outlines things to deal with when upgrading from to 2022: https://docs.appeon.com/pb/upgrading_pb_apps/index.html
  1. Helpful
Jim Nesbitt
  1. Jim Nesbitt
  2. Tuesday, 13 December 2022 20:37 PM UTC
Hi Armeen - I think there is a Powerserver issue. I've replied and attached a PDF showing details showing testing code. I wasn't sure if you will be updated or not. Cheers Jim



  1. Helpful
There are no comments made yet.
David Xiong @Appeon
David Xiong @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 14 December 2022 09:37 AM UTC
  2. PowerServer
  3. # 4

Hi Jim,

This is a PowerServer bug. We suggest that you submit a ticket through our Support Portal so that it can be tracked properly. You will also be updated when it is fixed. Thanks!

Regards,
David

Comment
There are no comments made yet.
Jim Nesbitt
Jim Nesbitt Accepted Answer Pending Moderation
  1. Tuesday, 13 December 2022 20:32 PM UTC
  2. PowerServer
  3. # 5

 

 

Hi Armeen and Chris

The debugger displays the SQLDA.OutParmTypes as null in PB2017, PB2022 and Powerserver. The values are correct in both Powerbuilder versions and the CASE statement operates correctly to set an integer value.. 

Powerserver seems to use TypeString when an integer is returned e.g. "5" instead of 5 resulting in the conversion error.

I've attached HTML of a PSR file showing the 2 versions running and displaying the conversion used.

The code I used is shown below (of_test). It just selects an integer from a table. I modified the app to call it and show the CASE option that executed in a MessageBox

Cheers

Jim

 

 

 

string Stringvar, Sqlstatement
integer Intvar
Long LongVar
Sqlstatement = "SELECT TOP 1 security_level FROM wm_user_profile"
// Sqlstatement = "SELECT COUNT (*)  FROM wm_user_profile"
PREPARE SQLSA FROM :Sqlstatement ;
DESCRIBE SQLSA INTO SQLDA ;
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
OPEN DYNAMIC my_cursor USING DESCRIPTOR SQLDA ;
FETCH my_cursor USING DESCRIPTOR SQLDA ;
// If the FETCH is successful, the output 
// descriptor array will contain returned 
// values from the first row of the result set.
// SQLDA.NumOutputs contains the number of 
// output descriptors.
// The SQLDA.OutParmType array will contain 
// NumOutput entries and each entry will contain
// a value of the enumerated datatype ParmType
// (such as TypeInteger!, TypeLongLong!, or 
// TypeString!).
CHOOSE CASE SQLDA.OutParmType[1]
		CASE TypeString!
			Stringvar = GetDynamicString(SQLDA, 1)
			MessageBox ('of_test', 'TypeString')
		CASE TypeInteger!
			Intvar = GetDynamicNumber(SQLDA, 1)
			MessageBox ('of_test', 'TypeInteger')
 		CASE TypeLongLong!
 			Longvar = GetDynamicDecimal(SQLDA, 1)
			MessageBox ('of_test', 'TypeLongLong')
		CASE ELSE
			Intvar = GetDynamicNumber(SQLDA, 1)
			MessageBox ('of_test', 'CASE ELSE')
END CHOOSE
CLOSE my_cursor ;

RETURN 1

 

 

 

 

Attachments (1)
SQLDA recording.pdf
Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Tuesday, 13 December 2022 20:50 PM UTC
Which version of SQL Server are you using?
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 13 December 2022 21:24 PM UTC
Note ...

1) the GetDynamicNumber() command returns a "Double" data type.

2) the GetDynamicDecimal() command returns a "Decimal" data type.

  1. Helpful
Jim Nesbitt
  1. Jim Nesbitt
  2. Tuesday, 13 December 2022 22:04 PM UTC
1) Version I am using currently is 2017, The code to get the SQL details hasn't changed in in almost 20 years, so should run in most. Powerbuilder uses SNC SQL Native Client(OLE DB).



2) I modified of_test to do some conversions as well using assignments (see below)

decimal 1.2 converted to int of 1

double 101.44456558 converted to int of 101

string "1" caused a conversion error

I think conversion of numeric types is probably implicit and works, but it doesn't apply for strings and is rejected.

PB2022 is converting according to OutParmType, but Powerserver isn't resulting in conversion error.



// Additional code

any aa_values[]

string ls_test, ls_classname, ls_classnamedec

dec ld_num = 1.2

int li_int



aa_values [1] = STRING ( '1' )

ls_classname = ClassName (aa_values [1] )

ls_test = aa_values [1]





aa_values [2] = ld_num

ls_classname = ClassName (aa_values [2] )

li_int = ld_num

li_int = aa_values [2]



aa_values [3] = DOUBLE (101.44456558)

li_int = aa_values [3]

// This dies

li_int = aa_values [1]

  1. Helpful
There are no comments made yet.
Jim Nesbitt
Jim Nesbitt Accepted Answer Pending Moderation
  1. Wednesday, 12 April 2023 19:28 PM UTC
  2. PowerServer
  3. # 6

 

Hi 

I received the message below in March, but 9835 isn't a link and I can't find details about it and no update to 9842. What it does mean?

Thanks in advance

Jim

 

Mark Lee @Appeon changed Bug 9482

What Removed Added
CC null 'mark.lee@appeon.com'
See Also null '9835'
Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 12 April 2023 20:21 PM UTC
Hi Jim;

FYI ...

Ticket # 9835 - current status is *scheduling*. That means it's in Engineering's work queue.

Ticket # 9482 - current status is *scheduling*. That means it's in Engineering's work queue.

Note that no further updates / status have been posted on these issues.

Regards ... Chris
  1. Helpful
Jim Nesbitt
  1. Jim Nesbitt
  2. Wednesday, 12 April 2023 21:04 PM UTC
Hi again Chris



Thanks for the reply. I was hoping it might be progressing, but I guess it's a minority problem. We use this everywhere instead of embedded SQL as the wrap around function has built in error checking. It only requires a few lines to make any SQL call. Embedded SQL is longer and problematic because it all has to be inline.



Cheers



Jim
  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.