UniDump - BLOB column problems

[ertank]

Hello,

I am using Delphi 10.3.2, UniDAC 8.0.1, FirebirdSQL 2.5.9.27139, targeting Win32 platform.

My database have SpecificOption Codepage = WIN1254

1) I have a table created as below:

Code: Select all

CREATE TABLE DOCUMENTFILE (
  DOCUMENTFILEID INTEGER NOT NULL PRIMARY KEY,
  DOCUMENTFILEDOC BLOB SUB_TYPE BINARY SEGMENT SIZE 20,
  DOCUMENTFILEDOCTYPE VARCHAR(5),
  DOCUMENTFILEDOCNAME VARCHAR(100),
  NOTE VARCHAR(100)
);

I do take above table backup using TUniDump. It is used with default options. There is no FirebirdSQL related SpecificOptions that can be modified.

When I take a backup, generated file cannot be restored. First exception says:

Code: Select all

Dynamic SQL Error
SQL error code = -104
Unexpected end of command - line 1, column 38.

Second exception says:

Code: Select all

Parser: The unknown symbol $000B ''

That file contains report designs in BLOB format. It seems UniDump is not taking care of binary BLOB objects for FirebaseSQL. So I wonder if there is any workaround that can be used to be able to do *complete* database backup and restores without any problems.

2) Moreover, I have another table created as following:

Code: Select all

CREATE DOMAIN "BOOLEAN" AS
  SMALLINT
  CHECK (value is null or value in (0,1));
  
CREATE TABLE EINVOICEIN (
  UUID                  VARCHAR(38) NOT NULL PRIMARY KEY,
  ID                    VARCHAR(20),
  DOCUMENTID            INTEGER,
  SENDER                VARCHAR(100),
  RECEIVER              VARCHAR(100),
  SUPPLIER              VARCHAR(100),
  CUSTOMER              VARCHAR(100),
  ISSUEDATE             DATE,
  CREATEDATETIME        TIMESTAMP,
  CURRENCY              VARCHAR(5),
  AMOUNT                NUMERIC(18,4),
  INVOICETYPECODE       VARCHAR(5),
  STATUS                VARCHAR(20),
  STATUSDESCRIPTION     VARCHAR(100),
  GIBSTATUSCODE         SMALLINT,
  GIBSTATUSDESCRIPTION  VARCHAR(100),
  RESPONSECODE          VARCHAR(20) CHARACTER SET NONE,
  RESPONSEDESCRIPTION   VARCHAR(100),
  CONTENT               BLOB SUB_TYPE TEXT SEGMENT SIZE 4096,
  LOCKED                SMALLINT DEFAULT 0,
  INOUT                 "BOOLEAN",
  REPLYTOINVOICE        "BOOLEAN",
  REJECTEDINVOICE       "BOOLEAN",
  ROOTINVOICE           "BOOLEAN",
  COMMERCIALINVOICE     "BOOLEAN",
  RETURNEDINVOICE       "BOOLEAN"
);

That table contains electronic invoices as XML text files in CONTENT column. I am also having problem restoring that table backup even if it is plain text readable in backed up file. Received error is very similar to binary BLOB one:

Code: Select all

Dynamic SQL Error
SQL error code = -104
Unexpected end of command - line 91, column 296.

3) Lastly, I fail to restore below small script. I get no error, nothing and data is not restored. I see nothing even in debug mode. No first chance exceptions. I am thinking that I might be doing something wrong about that.

Script:

Code: Select all

-- UniDAC version: 8.0.1
-- InterBase server version: 2.5.9.27139
-- InterBase client version: 2.5
-- Script date 27.08.2019 17:40:32
DELETE FROM ACCOUNTINGFORMTYPE;
INSERT INTO ACCOUNTINGFORMTYPE VALUES (10, 'Satış');
INSERT INTO ACCOUNTINGFORMTYPE VALUES (20, 'Alış');
INSERT INTO ACCOUNTINGFORMTYPE VALUES (30, 'Ödeme');
INSERT INTO ACCOUNTINGFORMTYPE VALUES (40, 'Tahsilat');

Delphi code used for restore:

Code: Select all

  UniDump1.RestoreFromFile('20190827_174032.bak');

Table DDL:

Code: Select all

CREATE TABLE ACCOUNTINGFORMTYPE (
  ACCOUNTINGFORMTYPEID INTEGER NOT NULL,
  ACCOUNTINGFORMTYPENAME VARCHAR(100)
);

Database is accessed with SpecificOption CodePage = Win1254.

I have deleted first record in above script "10, Satış" from table and it is not coming back after executing restore at above line.

Any help is appreciated.

[ViktorV]

Please, specify the locale on the client computer.
To learn the current locale, you can try to perform the following steps:
1. Click Start, then Control Panel
2. Click Clock, Language and Region
3. Windows 10, Windows 8: Click Region
Windows 7: Click Region and Language
Windows XP: Click Regional and Language Options
The Region and Language options dialog appears.
4. Click the Administrative tab
On Windows XP, click the Advanced tab
If there is no Advanced tab, then you are not logged in with administrative privileges.
5. The current locale is specified in the "Current language for non-Unicode programs"

[ertank]

Client is Windows 10 64Bit. Language is indicated as Turkish. You can see below image.
https://imgur.com/zwVzKL5

As a side note, database connection codepage parameter WIN1254 is also Turkish character set.
Also, UseUniCode = False in SpecificOptions.

[ViktorV]

To answer questions 1-3, we need a code sample that demonstates that behavior. Please send us using the contact form https://devart.com/company/contactform.html a sample along with scripts that create database objects and populate them with data.
To solve the task 4, try executing the UniDump.Connection.DefaultTransaction.Commit method after RestoreFromFile method.
Also, please try setting the UseUnicode property to True. Let us know whether the issue persists.

[ertank]

Number 4 problem solved using UniDump.Connection.DefaultTransaction.Commit method after restore. This behavior of TUniDump is not in documentation. It would be helpful to have that added in documentation, please.

I have provided demo application thru contact form for re-producing other items.

Thanks.

[ViktorV]

The TUniDump component supports the following providers: MySQL, SQL Server, SQLite and PostgeSQL. Functionality of TUniDump is greatly limited for the InterBase provider (for example, as in your case BLOB is not supported). This functionality cannot be extended because a full-fledged backup and restore of Firebird (InterBase) DB objects can only be achieved with special DBMS measures. This is why we recommend using IBDAC - it has has the TIBCBackupService and TIBCRestoreService components with which you can execute a full backup and restore of Firebird DB objects: https://devart.com/ibdac/docs/devart.ib ... ervice.htm, https://devart.com/ibdac/docs/?devart.i ... ervice.htm.