Kbase P124987: SQL statement using COUNT function fails in Delphi 5 after upgrading to OpenEdge 10.1B
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
17/07/2007 |
|
Status: Unverified
FACT(s) (Environment):
OpenEdge 10.1B
SYMPTOM(s):
Delphi 5
Application has been designed in Delphi 5
SQL statement uses COUNT function:
SELECT COUNT(<field>) FROM pub.<table>
Delphi application returns error when executing this statement:
CAN'T OPEN QUERY : select count(<field>) as <alias> from pub.<table>
Error: <QueryName>: Type mismatch for field "<alias>", expecting: Integer actual: Float
Delphi uses the Borland Database Engine (BDE) to establish connection via ODBC
Same code and query worked correctly in Progress 9.1x
Problem cannot be reproduced in Visual Basic 6
ODBC trace log shows that VB6 and Delphi are returning different data-types:
VB6 141c-1334 ENTER SQLBindCol
HSTMT 038E1D58
UWORD 1
SWORD -25 <SQL_C_SBIGINT>
PTR 0x00000050
SQLLEN 19
SQLLEN * 0x0000004C
DELPHI 4ec-ba4 ENTER SQLBindCol
HSTMT 03303158
UWORD 1
SWORD 8 <SQL_C_DOUBLE>
PTR 0x01B2F1DC
SQLLEN 8
SQLLEN * 0x01B2F14C
CHANGE:
Upgraded to OpenEdge 10.1B
CAUSE:
Delphi 5 was released in 1999 and does not support BigInt data-types. In previous Progress/OpenEdge releases, a COUNT returned a 32-bit integer.
OpenEdge 10.1B uses a 64 bit integer (a SQL BigInt type) for the result of a SQL COUNT(). This is because of the large increase in database and table sizes possible in OpenEdge 10.1B with 64-bit rowids.
FIX:
CAST the result of the COUNT function as an INTEGER data-type in the query:
SELECT CAST(COUNT(<field>) AS INTEGER) AS <alias> FROM pub.<table>
Alternatively this can be done in the application code.