Hi all -

I have just run across a problem reading back data from a connection
using the IB API. The query is

SELECT ((EXTRACT(YEAR FROM "Calcs"."datetime0") || '')) AS
"none:B11703:nk"
FROM "Calcs"
GROUP BY 1

the error is

File: db\firebirdprotocol.cpp, Line: 1534
Status: 335544321
arithmetic exception, numeric overflow, or string truncation

and the call is

bool
FirebirdProtocol::DBStatement::Fetch(

DBBindingsPtr bindings )
{
switch( m_api->isc_dsql_fetch( m_status, &m_stmt, bindings-

>version(), *bindings ) ) {

case 0:
return true;

case 100:
return false;
}

FBTHROWERR(m_status); // Error report line
return false;
}

Clearly the string is 4 characters wide. The width of the buffer in
the XSQLDA structure is 24 bytes, plus two more for SQL_VARYING. The
code works in a WIDE variety of other circumstances and has been in
the field for about a year. I get the same error for extracting
DAY, HOUR, MINUTE or SECOND, but not for MONTH, YEARDAY, WEEKDAY or
WEEK.

Even better, the following queries work fine and I get back correct
results:

SELECT ((EXTRACT(YEAR FROM CAST('2007-05-11' AS DATE)) || '')) AS
"none:B11703:nk"
FROM "Calcs"
HAVING ((COUNT(1) > 0))

SELECT ((EXTRACT(YEAR FROM CAST('2007-05-11 04:12:37.0000' AS
TIMESTAMP)) || '')) AS "none:B11703:nk"
FROM "Calcs"
HAVING ((COUNT(1) > 0))

SELECT ((2004 || '')) AS "none:B11703:nk"
FROM "Calcs"
HAVING ((COUNT(1) > 0))

SELECT ((cast(2004 as smallint) || '')) AS "none:B11703:nk"
FROM "Calcs"
HAVING ((COUNT(1) > 0))

SELECT ( '2004') AS "none:B11703:nk"
FROM "Calcs"
HAVING ((COUNT(1) > 0))

-- int3 has integers from 0-18 in it.
SELECT (((2000 + "Calcs"."int3") || '')) AS "none:B11703:nk"
FROM "Calcs"
GROUP BY 1

sooo:

- It is only some of the date parts.
- It is not the length of the string (4)
- It is not the data type (date vs timestamp)
- It is not the integer type (smallint vs int)

iSQL behaves correctly.

Any ideas?

TIA,
________________________________________________________
Richard Wesley Senior Software Developer Tableau
Software
Visit: http://www.trytableau.com/now.html