Firebird database - alter column datatype using SQL after import from calc spreadsheet - errors

[frofa]

1. I’m using LO 6.1.5 (latest stable) and have data in a CALC spreadsheet that I have successfully imported (allowing the LO Base interface to add a autoincrement primary key).

2. At this stage (after the successful import process), all the columns (except for the PK) are showing as TEXT datatype of 255 characters in length - including a DATE column and two other numeric (currency) columns (which should be formatted at DECIMAL(7,2).

3. I have set all the columns in the original IMPORTED Calc sheet with the correct TEXT, DATE and CURRENCY formating. However, these above settings do NOT seem to have been respected in the COLUMN datatype settings as used by FIREBIRD. (i.e. They all show up as TEXT/VARACHAR columns of 255 length - as mentioned above).

4. To fix things, I have tried to change the datatype (and text-field length) of specific columns by running SQL code in the SQL window, such as:

ALTER TABLE “bank_list” ALTER COLUMN “date” DATE;

However, I always get an error message:

1: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 48
*DATE
caused by
‘isc_dsql_prepare’

So what am I doing wrong? Any help would be much appreciated. With thanks.

[Ratslinger]

Hello,

The correct syntax would be:

ALTER TABLE "bank_list" ALTER COLUMN "date" TYPE DATE;

However, this will cause another problem:

unsuccessful metadata update
ALTER TABLE bank_list failed
Cannot change datatype for column DATE from a character type to a non-character type.

This error is generated (Date and NUMERIC type fields tested) in embedded or server versions. There are a number of ways to get this converted. The easiest method I have found, and without creating data errors (existing bug reports on converting DATE and NUMERIC/DECIMAL field types), is to edit the table, add a new field with the correct type, then copy the Text field to the new field. You can then delete the old fields and, if desired, move the field positions to where they were in the original layout.

There is a document outlining this process in my answer on this post → Firebird Migration: Number [ NUMERIC ] Field Data Corruption. See the edited section for Conversion document ----- FirebirdTableMigration.odt

[mikekaganski]

Hi @Ratslinger! Sorry for an irrelevant question: do you have a reference to a manual to create a server connection to FB from current Base? Thanks!

[Ratslinger]

Hello @mikekaganski,
No. Don’t even remember looking for one. Basically used common material from other server connections (JDBC) such as MySQL and PostgreSQL. They are all similar. For Firebird using Jaybird connector:

Needs to be referenced in Java Class Path:

/path_to/jaybird-full-3.0.5.jar

For connection settings:

org.firebirdsql.jdbc.FBDriver                    is the JDBC driver class
jdbc:firebirdsql://localhost//database location/database_name.fdb     Datasource URL
              Linux default install is:  var/lib/firebird/3.0/data
SYSDBS               is default user name when set by Firebird install

That’s about it. Settings reference Linux paths. Fairly simple. Any other questions, I’d be glad to answer if I can.

[Ratslinger]

@mikekaganski I do stand corrected. Just refreshed my memory with some digging. In the Jaybird connector Jaybird 3.0.5, there is a folder named docs which has faq.html. This contains the relevant information. I needed the name of the class driver & this is where I found it.

[mikekaganski]

Than you very much!

And do you have any experience connecting using ODBC?

[Ratslinger]

@mikekaganski I typically try all connection types possible with a database. In the case of Firebird ODBC on Linux, have never gotten this to work nor have I discovered anyone else getting this to work with LO Base. Don’t recall any other OS having success in this area either.

I should note, it appears I can connect (see tables names, can open empty tables). It is just every time data is to be accessed, LO crashes. Again, no resolution ever found. Looked several times.

[mikekaganski]

It might be tdf#121092 (at least that was a discovery of one Windows person on our forum - rus) - can you check please?

[Ratslinger]

Did get farther but not with 6.2.x version. Loaded v5.4.5.1 and was able to see data in a table or two. Still crashes & all tables seem to be read only. Seems I have other problems but do confirm problem with “Advance Settings” being reset in v 6.2.x.

[mikekaganski]

thanks again!