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

Resolved Powerbuilder 2019 - Running a Mssql function with INT parameter

  1. Issue
0
Votes
Undo
  1. Orazio Adamo
    Orazio Adamo
  2. PowerBuilder
  3. Friday, 17 March 2023 14:00 PM UTC

Hi


I am integrating my application with MS SQL server. When I execute a sql function passing a LONG parameter, the function fails with the code SQLSTATE = 22003
[Microsoft][ODBC SQL Server Driver]Numerical value out of range.
if I use an integer variable everything works fine

PB 2019 R3 2728
MS SQL Server 2019

------------- MSSQL 

CREATE OR ALTER function [dba].[fn_test_long](@ad_number int) 
RETURNS int
AS
begin
 return(@ad_number)
end
GO


------------- POWERBUILDER
long ll_value, ll_return

ll_value = 2288

select  dba.fn_test_long(:ll_value) into :ll_return;


After runnig this code the ll_return value is equal to 0 and sqlerrtext contains 
SQLSTATE = 22003
[Microsoft][ODBC SQL Server Driver]Numerical value out of range

Can you help me ? 

Thanks in advance

 

Unable to read *.PBP file
Wrong fonts after export to pdf
Responses (5)
Peter Pang @Appeon
Peter Pang @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 21 March 2023 02:25 AM UTC
  2. PowerBuilder
  3. # 1

Hi Orazio,

 

Thanks for reporting this issue.

We reproduced it on our end. The issue seems to be the ODBC Driver version.

  1. SQL Server: 10.00.19041.2251 can reproduce the same issue.
  2. SQL Server Native Client 11.0: 2011.110.7001.00 works fine.

 

We recommend using PB direct drive MSOLEDBSQL SQL Server interface to connect to SQL Server 2019. You can refer to:

https://docs.appeon.com/pb/whats_new/MS_SQL_Server_enhancements.html

 

Best Regards,

Peter

Comment
There are no comments made yet.
Orazio Adamo
Orazio Adamo Accepted Answer Pending Moderation
  1. Monday, 20 March 2023 14:02 PM UTC
  2. PowerBuilder
  3. # 2

Hi John,

I used a table called dba.test_dummy with one row to test the select.

The PB script works without any problem if I use a different type of variable. Any other type of variable works fine.

Integer, numeric, ...

I have this issue only with Long type even if, as you say, "A Pb Long is pratically the same as a SQL server Int"

 

Comment
There are no comments made yet.
John Raghanti
John Raghanti Accepted Answer Pending Moderation
  1. Monday, 20 March 2023 11:39 AM UTC
  2. PowerBuilder
  3. # 3

Hi, 

SQL Server does not allow "from dummy" like ASA or ASE. John Fauss' FROM statement will work . In our applications, we settled on this as a standard:

FROM (SELECT 1 AS TmpValue) TmpTable

Try changing that and see if it works. A PB Long is practically the same as a SQL Server INT.

Comment
There are no comments made yet.
Orazio Adamo
Orazio Adamo Accepted Answer Pending Moderation
  1. Monday, 20 March 2023 08:54 AM UTC
  2. PowerBuilder
  3. # 4

Hi John 

I'm sorry. The PB script I copied was wrong. 
Sure, I used "from dummy" in the select. 

The problem is in the type of variable. If instead of a long variable I use an integer it works normally. 
This only occurs with a MSSQL db. Not with Sap SQL Anywhere
Comment
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Friday, 17 March 2023 16:26 PM UTC
  2. PowerBuilder
  3. # 5

Hi, Orazio - 

In-line SQL SELECT statements issued from PB PowerScript require a "FROM" clause. Try:

select dba.fs_long_test(:ll_value) into :ll_return from (select a=1) as t;

Best regards, John

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.