Subject Re: [firebird-support] varchar(2048) holds 1012
Author Helen Borrie
At 10:20 AM 5/01/2004 -0800, you wrote:
>I did recently do a restore with a page size of 8192, and had always used
>a smaller
>page size before. Alan suggesteed the possibility of a problem there, and
>that does co-
>incide with my experience. I can do a restore with the smaller page size
>later and see
>if there is a difference.

Consider that theory absolute rubbish. A too-small page size can cause
performance problems. A too-big page size can cause performance
problems. 8192 is about right for normal usage on modern systems. Having
an unsuitable page size doesn't affect data. You have an application bug

>Regarding monitoring, I have a simple log in my application (in C++) and
>check the
>CString length before sending, as well as printing out the query string,
>its detected
>length, and other related data. All seems normal. But I'm not sure what
>monitoring tool
>you might be referring to, it may tell more?

An SQL monitoring tool, as found in nearly all GUI admin utilities and as a
component in IB Objects and some other dao component suites. I think ODBC
on Windows also has one.

>On 1 Jan 2004, at 12:35, Helen Borrie wrote:
> > At 04:06 PM 31/12/2003 -0800, you wrote:
> > >Using FB 1.0
> > >
> > >I have a varchar(2048) that holds only about 1012 actual characters. If I
> > >try to insert
> > >more than that It fails with:
> > >
> > >First Err: arithmetic exception, numeric overflow, or string truncation
> > >SqlCode: Arithmetic overflow or division by zero has occurred.
> > >
> > >The particular string consisted of numerals "12345670123etc", but of
> > >course that
> > >should not make any difference.

That depends. If your application is receiving data into variants and then
passing the variant to a varchar without casting it, then the engine first
has to cast a string of numbers from its assumed type (here it would be
decimal(18,0)) to string. If the string of number appeared to it as a
beyond-range number, then you would get exactly the exceptions you report.

Note also my previous comment:

> >
> > Are you certain that you are not passing an expression (function call,
> > etc.) that might be causing the exception? The ib_udf string functions
> > have a known bug that none of them will accept strings longer than 80
> > bytes, making them useless for processing longer strings.
> >

Btw, it has now been established by concensus in firebird-devel that the
UDF string length "bug" isn't a bug, but is "as designed". This means
that, if there is a string UDF in this picture, you need to drop it and
redeclare it with a CSTRING(N) input that is long enough to handle the
input you are passing to it.