At 05:44 PM 6/08/2005 +0000, you wrote:

>Hi Folks:
>
> Olivier told me this looks like an issue with Firebird.
>
> Developing on Win2K, MSVC6, IBPP 2.3.5.0, Firebird 1.5.2.4731
>embedded server.
>
> I'm using my first SQL UNION statement, perhaps I've got it
>wrong, but IBPP's execute is reporting the following problem:
>
> ----------------
>
>*** IBPP::Exception ***
>Context: Statement::Execute( SELECT
> *
>FROM
> PLAYER_TABLE_01
>WHERE
>(
> PLAYER_ID IN
> (
> SELECT
> OWNER_PLAYER_ID
> FROM
> INTEREST_TABLE_01
> WHERE
> (
> (
> INTEREST_NAME = 'Tennis'
> )
> )
> )
>)
>UNION
>SELECT
> *
>FROM
> PLAYER_TABLE_01
>WHERE
>(
> PLAYER_ID IN
> (
> SELECT
> OWNER_PLAYER_ID
> FROM
> INTEREST_TABLE_01
> WHERE
> (
> (
> INTEREST_NAME = 'Aerobics'
> )
>
> OR
> (
> INTEREST_NAME = 'Gymnastics'
> )
> )
> )
>)
> )
>Message: isc_dsql_execute failed
>
>SQL Message : -413
>Overflow occurred during data type conversion.
>
>Engine Code : 335544334
>Engine Message :
>conversion error from string "BLOB"
>
> ---------------
>
> This is dynamic SQL, and needless to say, this is a
>simplified query. I don't need a union for this
>example, but I believe one is needed when the subquery
>for the first clause of the UNION becomes more
>complicated.
>
> I'm not an SQL expert, do you see a fault in this
>query?
>
> PLAYER_TABLE_01 has an array, but no blob, and
>INTEREST_TABLE_01 has neither array or blob.
>
> I've queried records from both these tables with no
>problems.
>
> Suggestions?

The way UNION is currently implemented, the structure of the output of the
*first* SELECT statement sets up the structure for the all of the
succeeding SELECTs. Your problem here is that the first SELECT produces an
INTEREST_NAME column that is VARCHAR(6), while the succeeding one will
output INTEREST_NAME values that will overflow a VARCHAR(6).

In a fashion, it's God's way of reminding you that SELECT * queries are not
meant for serious development. With UNIONs, you are asking for trouble
using SELECT *, since it gives you no control over the output.

One solution would be to put the SELECT containing the longest potential
output for INTEREST_NAME first in the union. "Tennis" and "Aerobics" will
fit into the VARCHAR(10) created for "Gymnastics". However, if someone
comes along later and wants to include "Sumo Wrestling" in the picklist,
you're in trouble.

The right solution is to use CAST for INTEREST_NAME in the first SELECT, to
cast the output to the defined length of the INTEREST_NAME column. This,
of course, requires more typing (or, in the case of ad hoc queries, more
program code), since it forces you to specify your output list explicitly
so that you can cast the varchar fields.

It might be more complicated than this, though, given that you are getting
the wrong error text in the second (GDSCODE) message. The template for the
text of the string conversion error is as follows:

-413 | 335544334 | convert_error | Conversion error from string "%s"

Where is the word "BLOB" coming from in your actual statements? Have you
used a monitor to examine the statements that are actually passing across
the API? If you find that BCCP is consistently dropping the word "BLOB"
into parameters where it shouldn't, then you need to give a test case to
Olivier. If, on the other hand, you can produce a test case that produces
this exact error in isql, together with the simplest possible test case
that reproduces the problem.

BTW, Firebird 2 is a bit smarter about setting up the output structure for
chars and varchars in unions. It evaluates all of the SELECT clauses and
tries to determine the largest size needed. If necessary, it will
internally interpolate any casting that is necessary to avoid overflows.
(That's a somewhat simplified description - for details, pick up the Fb 2
Alpha 3 release notes from
http://firebird.sourceforge.net/download/prerelease

./heLen