At 11:22 AM 2/12/2003 -0500, you wrote:

>I'm using the isc_dsql_* interface (instead of embedded sql), so it would
>need to be something like:
>
> INSERT INTO "TOWEROFBABEL" ("COL") VALUES (_ISO8859_1 ?)
>
>But sadly, that returns an error: rc = 335544569, sqlcode = -104 - Invalid
>token "?". So that would seem to leave the problem with connecting as NONE,
>plus it would require regenerating SQL to change the parameter references.

Correct, it seems to work only with literals, i.e. the client has to
prepend the introducer (as it is called) to the data, not to the
substitution character.

>The "somewhat good" news is that if I connect to the database as UNICODE_FSS
>and use the cast, i.e.
>
> SELECT CAST("COL" AS VARCHAR(24) CHARACTER SET UNICODE_FSS) FROM
>"TOWEROFBABEL"
>
>I don't get the internal conversion error. Firebird then considers all
>parameters to contain UNICODE_FSS strings, so that works OK as well.
>
>The down side is that I'm trying to use Firebird with a fairly DBMS neutral
>(depending on whom you ask) application. I can expose certain things (like
>DBMS specific data type names) to the application, but they are not set up
>to reformulate every column they reference based on the target column's
>size. In order for me to do it dynamically, I'd need to prepare, describe,
>and parse all of the SQL at prepare time, regenerate the SQL by changing the
>column references based on the describe info, and then reprepare the
>regenerated SQL. Not real efficient, plus it gets ugly for convoluted
>queries.

Agreed. Making applications DBMS-neutral severely restricts what you can
implement.

>In addition, the internal workings of Firebird prevent this from being a
>complete general purpose solution ... consider:
>
> create table BIG6(
> col1 char(3640) character set ISO8859_1,
> col2 char(3640) character set ISO8859_1,
> col3 char(3640) character set ISO8859_1,
> col4 char(3640) character set ISO8859_1,
> col5 char(3640) character set ISO8859_1,
> col6 char(3640) character set ISO8859_1)
>
>It is perfectly valid to create a row with all 6 columns filled with NLS
>characters.
>
>Now consider the humble SELECT * FROM BIG6. The best "safe" query I could
>regenerate would be one that reserved 1 UNICODE_FSS character for each
>character in the source column (the application's data bindings in the
>sqlvar are not available during prepare) ... which would require the
>following regenerated SQL:
>
> SELECT
> CAST(COL1 AS CHAR(3640) CHARACTER SET UNICODE_FSS),
> CAST(COL2 AS CHAR(3640) CHARACTER SET UNICODE_FSS),
> CAST(COL3 AS CHAR(3640) CHARACTER SET UNICODE_FSS),
> CAST(COL4 AS CHAR(3640) CHARACTER SET UNICODE_FSS),
> CAST(COL5 AS CHAR(3640) CHARACTER SET UNICODE_FSS),
> CAST(COL6 AS CHAR(3640) CHARACTER SET UNICODE_FSS)
> from BIG6
>
>Which returns an error, presumably because 3640 * 3 * 6 = 65520 which is
>larger than the max row size allowed. I'll grant you that this is a fairly
>extreme example - I suspect that I might be able to add a restriction
>indicating that the maximum row size from a create table operation is
>limited to (total number of characters) * 3 + size of all other types <
>Firebird max row size. Most of our applications would probably be fine with
>this rule, but it will probably prevent this being used for a general
>solution to the problem within the firebird source code.


>This also ignores the fact that Firebird's max column size means that you
>can't cats or bind a sufficiently large UNICODE_FSS buffer for some columns.
>Consider
>
> CREATE TABLE BIG1(COL1 char(12000) CHARACTER SET ISO8859_1)
>
>The regenerated SQL for SELECT * FROM BIG1 would be:
>
> SELECT CAST(COL1 AS CHAR(12000) CHARACTER SET UNICODE_FSS) FROM BIG1
>
>Which returns an error, presumably because 12000 * 3 is greater than the
>Firebird maximum column size.

A char has an absolute width limit of 32,767 bytes and a varchar 32,765. I
think this restriction possibly extends beyond the vendor implementation.

If you need to index these columns you are majorly-stuck. Index widths max
at 253 bytes. Divide that by 3 and deduct a few for knife-and-fork stuff
and none of these text columns comes close.

I guess that your generic DBMS interface requirement precludes using text
blobs to store these large text items...

>You'll only see the transliteration problem if you fetch a string whose
>connection character set representation (in bytes) exceeds the size of the
>declared column size (in bytes). Since most folks don't fill their columns
>completely with NLS characters and then fetch them as UNICODE_FSS, most
>don't see this issue, but it is there - as the example below shows. One of
>our application's requirements is to be able to query columns with different
>character sets and always return the results correctly using a single
>connection, so we are forced to use either NONE or UNICODE_FSS to connect.

You connect using the default character set of the database, i.e.
regardless of the character sets of individual columns (your requirement),
the connection and database character sets must match.

>I can always transcode the data after fetching it, so I don't care whether
>Firebird does it or not - I just need to get (and put via input parameters)
>the valid data.
>
>I posted the internal details of the problem to firebird-devel earlier, but
>I didn't get any responses.

Try firebird-architect instead. I suggest also going to the archive of that
list and reading up the previous threads on this topic. Not that you can
be guaranteed a crystal-clear solution but it would help you to get a
handle on the issues, at least, and maybe some extra insight from the
people who developed the character set support.

regards,
heLen