Commented by: @alexpotapchenko

http://www.firebirdsql.org/manual/migration-mssql-data-types.html - Firebird can hold up to 32,765 characters.
If you use UTF8 or UNICODE_FSS then max VARCHAR length is less.

Commented by: Martin J. Evans (bohica)

But surely you know wether this is the case. In any case I don't see how the page you refer to explains this issue. It says:

Variable-length non-Unicode data with a maximum of 8,000 characters. Firebird can hold up to 32,765 characters. In 6.5, maximum was 255.

I don't use firebird some of my users do. I don't know what UNICODE_FSS is but you should and the ODBC driver should. Reporting 32765 for the length then erroring when I try to create a varchar of 32765 is nonesense.

I don't want to get into silly arguments but having written a number of commerical ODBC drivers I would count this as a bug and yet you dismiss it with little explanation.

Commented by: @hvlad

Martin,

it will be much more useful if you show us error message when CREATE TABLE fails. Statement text is also useful.
Also it is good to know what FIrebird version do you use.
Then we could decide what is nonsence and why ;)

Firebird *really* supports varchar columns with length of 32765 *bytes*. Count of *characters* depends on character set of particular column.
AFAIU, SQLGetTypeInfo have no knowledge about character set user going to use, so all it can do it is
a) return 32765, as it do now, or
b) assume default character set of database and return 32765 / max_bytes_per_char

I don't know what is better.

Also i don't know why the Perl driver tried to create table using maximum available length for varchar filed. It is at least unnecessary and lead to inefficient memory using. Also, note, there is limit on record length - it is few bytes less than 64KB, so you can't create table with, say, 3 VARCHAR(32765) fields.

Commented by: Martin J. Evans (bohica)

The SQL being executed is:

create table PERL_DBD_TEST (COL_A SMALLINT NOT NULL PRIMARY KEY , COL_B VARCHAR(32765), COL_C BLOB SUB_TYPE TEXT, COL_D TIMESTAMP)

The error is:

#⁠ Failed to create table - [unixODBC][ODBC Firebird Driver][Firebird]Dynamic SQL
Error
#⁠ SQL error code = -204
#⁠ Data type unknown
#⁠ Implementation limit exceeded
#⁠ COLUMN COL_B (SQL-42S02)

The code arrived at this SQL because it calls SQLGetTypeInfo for VARCHAR which returns (annotated):

$VAR1 = [
'VARCHAR',
'12',
From ODBC docs:
The maximum column size that the server supports for this data type. For numeric data, this is the maximum precision. For string data, this is the length in characters.
'32765',
'\'',
'\'',
From ODBC docs:
A list of keywords, separated by commas, corresponding to each parameter that the application may specify in parentheses when using the name that is returned in the TYPE_NAME field. The keywords in the list can be any of the following: length, precision, or scale. They appear in the order that the syntax requires them to be used. For example, CREATE_PARAMS for DECIMAL would be "precision,scale"; CREATE_PARAMS for VARCHAR would equal "length." NULL is returned if there are no parameters for the data type definition; for example, INTEGER.
'length',
'1',
'1',
'3',
undef,
'0',
undef,
'VARCHAR',
undef,
undef,
'12',
undef,
undef,
undef
];

SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION') from rdb$database;

returns

2.1.3

I take, to a degree your comment on SQLGetTypeInfo not knowing what charset you are going to use but if you read the ODBC spec it is a count of characters not bytes ("For string data, this is the length in characters") so your comment on bytes is not relevant. For instance Oracle returns 4000 and can store 4000 7 bit ASCII characters or 4000 unicode UTF-8 encoded characters (or 4000 chrs encoded some other way) - either way it is 4000.

It sounds like you are saying Firebird can store 32765 bytes in total so the count of characters is dependent on the encoding used internally. I agree, this makes it tougher for you but I still think your option (b) is better than option (a) as it is now, as it at least does not lead to a possible error condition and you'll still allow more characters on the create if the database can fit them in 32765 bytes. The documentation you pointed to says "Variable-length non-Unicode data with a maximum of 8,000 characters" so perhaps the answer is 8000 not 32765 bytes.

As for the code in question in Perl DBD::ODBC, it is not code part of the driver, it is part of the test suite that to my knowledge runs this test code successfully in dozens of ODBC drivers including over 14 Easysoft ODBC drivers (including a Firebird driver), commercial drivers from Microsoft (who came up with ODBC) including MSAccess and MS SQL Server, Oracle's ODBC Driver, Teradata, Postgres, MySQL, DB2 etc.

If you don't want to change it that is fine - it is your choice. However, people are using the Firebird ODBC driver in Perl and when they try to install it and the test suite fails they badger me, not you. I can workaround it (and have for now) but I still think it is wrong in Firebird ODBC and have documented it as such.

I guess some of my previous response boils down to a reaction to what looked like a rather curt response to me to my bug report. I don't personally use Firebird but I have to support it and your apparent dismisall of my report with one sentence somewhat annoyed me. Remember, anyone providing the level of detail I did spent a lot of time looking in to it and I also reported other issues at the same time (which you have fixed) so a single line basically saying wontfix and RTFM is a bit annoying. I'd rather work with you. Not withstanding any of that you will make your own decision and I will workaround whatever you decide.

Commented by: @hvlad

Martin,

at first, thank you for all reports you made about Firebird ODBC driver. It allows us to make it better.

i have few points to make all this more clear :

a) the documenation link provided by Alexander is not what you need, better read this one http://www.firebirdsql.org/index.php?op=guide&id=techspec
b) the life is that Firebird supports varchar fields up to 32765 bytes, not characters, seems you got this. Not 4000, not 8000, but 32765. Bytes, not characters.
c) what is default character set of database where your CREATE TABLE fails ? You can found it using SELECT RDB$CHARACTER_SET_NAME FROM RDB$DATABASE
d) just for info - what value returned by Easysoft ODBC at this database ?
e) did you noted that you talk with two different people ? ;)

Alexander,
i think ticket should be re-opened and fixed using max bytes per char of database default character set (and collation for 2.5).

Commented by: @alexpotapchenko

I have downloaded Easy Soft ODBC Driver 1.5 for Firebird and It returns 32765 for varchar in database with UTF-8 default charset.

Commented by: Martin J. Evans (bohica)

Thanks Vlad.

a) that confirms it as 32767 reduced by chr size

b & c) I understood the bytes vs chrs.

create table fred (a varchar(8192)) fails
create table fred (a varchar(8191)) fails

SELECT RDB$CHARACTER_SET_NAME FROM RDB$DATABASE
UTF8

Since UTF-8 is a variable size encoding the database itself has decided 32767/4 so the ODBC driver could at least do the same.

I am no expert on Firebird so it is possilbe you can attach a different encoding to a column which would make 32767 / max encoding bytes needed still wrong but in my mind less wrong than simply returning 32765.

d) 32765 as Alexander says but it has been changed to return less depending on encoding

e) no I didn't - sorry.

Please realise this has no direct benefit to me other than fending off people complaining Firebird does not pass the DBD::ODBC test suite. You should also know I work for Easysoft (in case that affects what you decide to do).

Commented by: @alexpotapchenko

Martin,
It is fixed in CVS for HEAD and 2.0 (B2_0_0 branch).

Commented by: Martin J. Evans (bohica)

Thanks Alexander - I'll check it out.