• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Powerbuilder 2019 - Running a Mssql function with INT parameter

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)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Friday, 17 March 2023 16:26 PM UTC
  2. PowerBuilder
  3. # 1

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.
Orazio Adamo
Orazio Adamo Accepted Answer Pending Moderation
  1. Monday, 20 March 2023 08:54 AM UTC
  2. PowerBuilder
  3. # 2

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 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 14:02 PM UTC
  2. PowerBuilder
  3. # 4

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.
Peter Pang @Appeon
Peter Pang @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 21 March 2023 02:25 AM UTC
  2. PowerBuilder
  3. # 5

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.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.