[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Arithmetic overflow occured - SQL Server - PowerBuilder Application

Posted on 2006-03-28
13
Medium Priority
?
1,396 Views
Last Modified: 2013-12-26
When I Execute the following Select Statement I am getting Arithmetic overflow occured.
But my scenario is quiet different.

I am using PowerBuilder as my client application development tool.
In that, When I execute,

Select Isnull(Max(PersonCode),0)+1 Into :NextPerson From Person

PersonCode is of type Numeric(6,0).
Personcode is the PrimaryKey and it has already 10500 rows.
Variable is of type long which can accomodate upto (-2,147,483,648 to +2,147,483,647).

If I execute the above statment in the SQL Query Analyzer, it executes well and it returns the maximum + 1 value.
If I execute the same through my PowerBuilder application, it gives this error.

This is very very urgent.

0
Comment
Question by:batchakamal
  • 6
  • 6
13 Comments
 
LVL 18

Expert Comment

by:diasroshan
ID: 16309066
Hi,

why dont u try the following... see if the error persists...

Select Isnull(Max(PersonCode),0) Into :NextPerson From Person

If IsNull(NextPerson) Then NextPerson = 0
NextPerson ++


Hope this helps...

Cheers,
Rosh

0
 
LVL 4

Author Comment

by:batchakamal
ID: 16309090
The problem is with the sql server statement not with the variable. Note that I already have PersonCode 1 to 10500 already.
It returns a database error.
As per your idea I will get 1, which will give Violation of Primary Key,
but I want to get the 10501.
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 16309100
Hi,

sorry... a small mistake...
remove the isnull from ur select

Select Max(PersonCode) Into :NextPerson From Person

If IsNull(NextPerson) Then NextPerson = 0
NextPerson ++


Cheers,
Rosh
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 18

Expert Comment

by:diasroshan
ID: 16309110
hi,

the last post of mine will return the right value...

in ur case... 10501


Cheers,
Rosh
0
 
LVL 4

Author Comment

by:batchakamal
ID: 16309203
Problem still exist.
When I debug, the select statement got error "Arithmetic overflow occured".
Hence your statement returns 0 to NextPerson.
Then NextPerson++ returns 1, so I got Primary Key Violation error.

I want to solve the Select Statement Problem. I hope then, my problem will be solved.
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 16309218
Hi,

can u write the same code that im posting below...

Long NextPerson
Select Max(PersonCode) Into :NextPerson From Person;

If IsNull(NextPerson) Then NextPerson = 0
NextPerson ++


Ok... also can u tell me the result of the below select in Query Analyser
Select Max(PersonCode) From Person;

Cheers,
Rosh
0
 
LVL 4

Author Comment

by:batchakamal
ID: 16309243
I put the same code.
When I Debug  I put a breakpoint in that Select Statment, it puts 0 in NextPerson.
And it has the following sqlca error message.

sqlca.sqldbcode = 3606
sqlca.sqlcode = -1
sqlca.sqlerrtext = "Arithmetic overflow occured"

If I execute the select statement in Quey Analyzer, it returns, 10500
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 16309284
Hi,

im lookin into the same... in the mean time can u tell me if this code was workin b4 and the problem started now after reaching a count of 10500

also can u just check this select in PB and see if it gives any error...

Select PersonCode
Into :NextPerson
From Person
Where personcode = 10500;

also check this select in PB and see if it give an error...
Select PersonCode
From Person
Where personcode = 10500;

Cheers,
Rosh
0
 
LVL 4

Author Comment

by:batchakamal
ID: 16309329
Select PersonCode
Into :NextPerson
From Person
Where personcode = 10500;
 
Gives the same error "Arithmetic overflow occured."

The same application, I have it in PB7, it works fine.
This one migrated to PB8. But it was working fine, for the past few months even in PB8.


0
 
LVL 18

Accepted Solution

by:
diasroshan earned 1000 total points
ID: 16309351
hi,

what abt
Select PersonCode
From Person
Where personcode = 10500;

does it give an error... i wanna know if the prob is with the variable NextPerson or with the select...

Cheers,
Rosh
0
 
LVL 4

Author Comment

by:batchakamal
ID: 16309386
Yes it gives error too inside the application script.

When I execute the same in Database Painter, Interactive SQL and Query Analyzer, it works fine.
0
 
LVL 5

Expert Comment

by:berXpert
ID: 16313815
Hi,

Just a bet, but maybe there is an error in a previous SQL statement, maybe a call to a stored procedure or a dw.Retrieve( ),  try to see if this error is present before you call your select. If your Select works OK in DB painter, then it MUST work on scritp.

Other idea, try to change your NextPerson variable from long to decimal.

Regards,

BerX
0
 
LVL 4

Author Comment

by:batchakamal
ID: 16319734
I solved this problem by, executing

SET ARITHABORT ON
reconfigure

on master database of SQL SERVER.

Thanks diasroshan, for ur continuos support.
I am giving this point for ur support (not for the answers.)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
Suggested Courses
Course of the Month17 days, 22 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question