* * *

Author Topic: IBX 2.1.0 restore parameter LC_CTYPE= throws an error  (Read 1189 times)

RedOctober

  • Full Member
  • ***
  • Posts: 218
IBX 2.1.0 restore parameter LC_CTYPE= throws an error
« on: February 12, 2018, 07:22:53 pm »
Platform:  Windows Server Essentials 2016, Laz 1.8.1, FPC 3.0.5

I'm trying to do a restore from an .FBK to a .FDB. During the restore, I want to change the default character set from UTF8 (in the .FBK) to ASCII (in the restored .FDB) (for example).   
There is no .CharSet property of the TIBRestoreService, so I thought I should be putting the LC_CTYPE=ASCII line in the .Parameters property.  However, upon restore attempt, an error is displayed:

[Debugger Exception Notification]

Project MyApp raised exception class 'EIBClientError' with message:
SPB Constant Unknown

 In file 'fbintf\IB.pas' at line 1220

[Ignore this exception type]

[Break] [Continue]

What am I doing wrong?  I changing Default Character Set not permitted during a restore?

taazz

  • Hero Member
  • *****
  • Posts: 5336
Re: IBX 2.1.0 restore parameter LC_CTYPE= throws an error
« Reply #1 on: February 12, 2018, 10:44:25 pm »
based on the underline code in IBServices.pas lc_Ctype is not a supported option for the service api. You can see the supported options around line 60 of the mentioned unit. I have no idea if there is a way to restore a db in to a different character set through the service API. Sorry
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

tonyw

  • Full Member
  • ***
  • Posts: 118
    • MWA Software
Re: IBX 2.1.0 restore parameter LC_CTYPE= throws an error
« Reply #2 on: February 12, 2018, 11:50:10 pm »
A gbak archive is a snapshot of a database, including metadata, and when you restore you get an exact copy of the original database. There are no options to transliterate characters or, indeed, to change any column type.

There are at least 3 ways that character sets are applied in Firebird:

1. There is the default database character set. That is specified when you create the database and is used as the default character set when you define a text column. Firebird 3 has introduced an ALTER DATABASE DEFAULT CHARACTER SET statement that allows you to change this. However, changing does not affect any existing column.

2. There is the character set specified for each text column. The character set is normally the default character set when the column is defined, but a different character set can be given. It is possible to alter the data type of a column. I have never tested changing the character set and whether transliteration takes place if you do. It may work.

3. There is the connection character set specified when you connect to a database. This is specified by the lc_ctype parameter that you tried to use. It is only available when you connect to a database. It is not available with the services API. When you specify a connection character set, Firebird will transliterate text data into that character set when you read data from the database. When you write to the database it will transliterate from the connection character set into whatever character set was defined for the target column.

IBX should recognise the character set for text data and set the codepage appropriately for each string that you read from the database and, if necessary, transliterate outgoing strings from the string's codepage to the connection character set. Recall that AnsiStrings in FPC each have a codepage associated with them and IBX is designed to set this correctly on read and to respect it on write, including using CP_NONE for character set OCTETS.

As a rule of thumb, unless you have a good reason not to, just use UTF8 everywhere including the lc_ctype.

RedOctober

  • Full Member
  • ***
  • Posts: 218
Re: IBX 2.1.0 restore parameter LC_CTYPE= throws an error
« Reply #3 on: February 13, 2018, 03:47:35 am »
Hi tonyw.  Very good explanation.  I envisioned that restoring to a different character set would only change the database default character set.  That can actually be changed later with  ALTER DATABASE DEFAULT CHARACTER SET as you say.  I will be able to use your explanations going forward.  Thanks.

tonyw

  • Full Member
  • ***
  • Posts: 118
    • MWA Software
Re: IBX 2.1.0 restore parameter LC_CTYPE= throws an error
« Reply #4 on: February 13, 2018, 10:46:07 am »
Hi tonyw.  Very good explanation. 
Glad to be of service.

If you really need to change a database schema such that all text columns now use a different character set then probably the easiest way to do this is with the ibx/examples/fbsql utility. You can use this with the "-A" option to dump an entire database to a text file including all metadata and user data. It should also be in standard SQL format with "INSERT" statements for the data. That is unless you have non-text blobs or arrays when it uses its own pseudo XML to save the data as hex characters.

You can then edit the dump with a standard text editor e.g. to change all instances of "UTF8" to "ASCII" and to uncomment (and edit) the initial "create database"  statement. Once you have done this, you can use the text file as an input script to fbsql (or Firebird's own isql if there are no arrays or non-text blobs) and it should then create and populate a new database with the updated schema. However you will get errors if any text characters in the file are not proper ASCII. So you may have to do some further editing if there are any.

Background: fbsql uses TIBExtract to dump the database and TIBXScript to execute the DDL and DML statements in the dump.

taazz

  • Hero Member
  • *****
  • Posts: 5336
Re: IBX 2.1.0 restore parameter LC_CTYPE= throws an error
« Reply #5 on: February 13, 2018, 11:50:37 am »
Hi tonyw.  Very good explanation.  I envisioned that restoring to a different character set would only change the database default character set.  That can actually be changed later with  ALTER DATABASE DEFAULT CHARACTER SET as you say.  I will be able to use your explanations going forward.  Thanks.
keep in mind that changing the database default character set has no effect what so ever on the data. Ignoring the fields that have different charset from the db default, firebird does not run any conversion on changing the default character set, it simple converts them on select, that may cause your database to loose some speed. unless its the development database, it is better for the long run, to recreate the database with the new character set and pump data to it.
« Last Edit: February 13, 2018, 11:54:09 am by taazz »
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus