Ask Your Question
0

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

asked 2019-03-04 10:39:13 +0200

frofa gravatar image

updated 2019-03-04 11:33:36 +0200

  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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-03-04 18:05:12 +0200

Ratslinger gravatar image

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

edit flag offensive delete link more

Comments

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!

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-04 22:46:46 +0200 )edit
1

Hello @Mike Kaganski, 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 gravatar imageRatslinger ( 2019-03-05 01:24:49 +0200 )edit
1

@Mike Kaganski 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.

Ratslinger gravatar imageRatslinger ( 2019-03-05 01:40:37 +0200 )edit

Than you very much!

And do you have any experience connecting using ODBC?

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-05 04:56:39 +0200 )edit

@Mike Kaganski 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.

Ratslinger gravatar imageRatslinger ( 2019-03-05 17:18:23 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-06 05:13:21 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2019-03-06 07:27:40 +0200 )edit

thanks again!

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-06 07:30:57 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2019-03-04 10:39:13 +0200

Seen: 81 times

Last updated: Mar 04