A Long overflow in an int overflow + ASP statement in an SQL statement

More than 5 o'clock in the evening, colleagues in QQ told me that a user responded to him, when he logged in to display the error message, we in the management platform to view the user's basic information, also display error message.

After a preliminary analysis, an int overflow occurred while executing the SQL statement:

sql = "Select sum (fileSize) as Fstotal from pic where UserID = 1632"

Originally, this SQL was used to get a total of all file sizes uploaded by a user before.

fileSize field type int

An int overflow occurs when a user uploads a file with a cumulative size of more than 2G (2147483648 bytes) and then executes sum (fileSize).

After you find the cause of the problem, start modifying the SQL statement:

sql = "Select sum (CAST (fileSize as bigint)) as Fstotal from pic where UserID = 1632"

After the modification, the test found that there are still errors, but this time the error is not because of this SQL, but from the ASP long overflow.

sql = "Select sum (CAST (fileSize as bigint)) as Fstotal from pic where UserID = 1632"
Set Re=conn.execute (SQL)
If not re.eof then
Fstotal = Re.fields ("Fstotal")
Fstotal = fstotal/1024/1024 ' converted to MB-a long overflow occurred
End If

To modify the code:

Fstotal = Re.fields ("Fstotal")
Fstotal = CDbl (fstotal) ' convert to double type before proceeding
Fstotal = fstotal/1024/1024 ' converted to MB

-----------------------------------------------------------------------------------------------------------

SQL server,int Type value max 2147483647 (2^31-1)
Asp,long (Long Integer) 4 bytes-2,147,483,648 to 2,147,483,647

2010-11-03

A Long overflow in an int overflow + ASP statement in an SQL statement