TUniLoader error loading data from SQLite3 into PostgreSQL

[ertank]

Hello,

I am using Delphi 10.2.2, SQLite3, PostgreSQL 10.1 64bit on Windows 64bit, Delphi project targeting Win32 platform.

I have following SQLite table:

Code: Select all

CREATE TABLE replies (
  autoinc integer not null primary key autoincrement,
  year smallint,
  month smallint,
  turu smallint,
  emailid uuid,
  reply text,
  createtime timestamp,
  ip text );

I have following PostgreSQL table:

Code: Select all

CREATE TABLE if not exists cevaplar (
  autoinc serial not null primary key,
  yil smallint,
  ay smallint,
  turu smallint,
  emailid uuid,
  verilencevap boolean,
  cevapzamani timestamp(0) without time zone, 
  cevapverenipnumarasi cidr,
  kaydeden varchar(20),
  kayitzamani timestamp(0) without time zone );

I am opening SQLite3 table using following SQL

Code: Select all

select year as yil, month as ay, turu, emailid, reply as verilencevap, createtime as cevapzamani, ip as cevapverenipnumarasi, :kaydeden as kaydeden, :kayitzamani as kayitzamani
from replies

And assigning following values to parameters:

Code: Select all

  UniQuery1.ParamByName('kaydeden').AsString := 'AUTO';
  UniQuery1.ParamByName('kayitzamani').AsDateTime := Now();

When executing TUniLoader:

Code: Select all

  UniQuery1.Open();
  UniLoader1.LoadFromDataSet(UniQuery1);

Errors I get are:
1) EVariantBadVarTypeError: "Invalid variant type" (this error can only seen if project is running in debug mode in Delphi IDE)
2) EPgError: "insufficient data left in message"

Raw data in SQLite3 table is just 10 rows and as following:

Code: Select all

"1"	"1"	"2018"	"1"	"0"	"{8FF9F31A-8BCF-407F-AADA-1010BD54660A}"	"TRUE"	"2018-01-03 01:28:19.000"	""
"2"	"2"	"2018"	"1"	"0"	"{0F5EF1D4-B407-4B58-A276-A42D948980BD}"	"TRUE"	"2018-01-03 01:28:53.000"	""
"3"	"3"	"2018"	"1"	"0"	"{74A5BFD9-D740-42E5-BAE1-E9A9E589C609}"	"TRUE"	"2018-01-03 01:29:05.000"	""
"4"	"4"	"2018"	"1"	"0"	"{74A5BFD9-D740-42E5-BAE1-E9A9E589C609}"	"FALSE"	"2018-01-03 01:30:26.000"	""
"5"	"5"	"2018"	"1"	"0"	"{8FF9F31A-8BCF-407F-AADA-1010BD54660A}"	"TRUE"	"2018-01-03 02:11:42.000"	""
"6"	"6"	"2018"	"1"	"0"	"{74A5BFD9-D740-42E5-BAE1-E9A9E589C609}"	"FALSE"	"2018-01-03 02:12:18.000"	""
"7"	"7"	"2018"	"1"	"0"	"{74A5BFD9-D740-42E5-BAE1-E9A9E589C609}"	"FALSE"	"2018-01-03 13:17:15.000"	"94.122.19.189/32"
"8"	"8"	"2018"	"1"	"0"	"{74A5BFD9-D740-42E5-BAE1-E9A9E589C609}"	"FALSE"	"2018-01-03 13:17:22.000"	"94.122.19.189/32"
"9"	"9"	"2018"	"1"	"0"	"{74A5BFD9-D740-42E5-BAE1-E9A9E589C609}"	"FALSE"	"2018-01-03 13:17:25.000"	"94.122.19.189/32"
"10"	"10"	"2018"	"1"	"0"	"{74A5BFD9-D740-42E5-BAE1-E9A9E589C609}"	"TRUE"	"2018-01-03 13:17:27.000"	"94.122.19.189/32"

I have tried to identify column raising error and tried below:
A- Make all rows null for "ip" column in SQLite3 table (just for test, I cannot remove that column in my data transfer)
B- Created reply column using "reply boolean" in the SQL. When filling in data, assigned values using ".AsBoolean" and values appeared as "Y" and "N" in the column.
C- Used above provided SQLite create SQL and modified "Y" for true and "N" for false manually the reply column in SQLite3 table

All above failed and I could not identify which column has problem.

Lastly, after error there is following memory leak:

Code: Select all

21 - 28 bytes: TCompressedBlobData x 4, Unknown x 1
29 - 36 bytes: TCompressedBlob x 4
37 - 44 bytes: Unknown x 1
53 - 60 bytes: Unknown x 1

I have already submitted a support form with small project and SQLite3 database file itself attached which re-produces the error.

I appreciate to know if there is something I can do as a workaround as I am stuck at last stage of my project that should be complete by the end of this week.

Thanks & regards,
Ertan Küçükoğlu

[MaximG]

We got your example, studied it and came to the following conclusions. The error EVariantBadVarTypeError is due to loading the values of the following columns: 'verilencevap', 'kayitzamani' and 'emailid'. To solve the problems with the first two columns, apply the following DataTypeMapping rules:

Code: Select all

  UniQuery.DataTypeMap.AddFieldNameRule ('verilencevap', ftBoolean);
  UniQuery.DataTypeMap.AddFieldNameRule ('kayitzamani', ftDateTime);

Unfortunately, the DataTypeMapping mechanism cannot be used when the 'emailid' values are loaded, because conversion to ftGuid is not supported in UniDAC (The UUID data type is not native for SQLite, therefore, fields declared as UUID in SQLite will be created in UniDAC with the Memo type by default). Currently, you can try the following solution:

a) make the type of the 'emailid' field of the replies table varchar (38)
b) after that, use the 2nd version protocol when loading the received values in the PostgreSQL:

Code: Select all

UniConnection.SpecificOptions.Values ['ProtocolVersion']: = 'pv20';

[ertank]

Dear MaximG,

I appreciate your feedback and possible solution. Unfortunately, it is not possible to convert emailid column data type to varchar(38) at the moment because;
- TGuid consumes much less space on disc
- Performance of the table will degrade dramatically as this will be a high row quantity table.
- That also requires code changes in the application as same application is designed to work also with SQL Server database.

I will use your suggestions as following:
- Create a temporary table at run-time using emailid column data type as varchar(38)
- After import completes, execute an SQL statement to copy data from temporary table into the real one. Leave conversion to database itself.

BTW, I know that I can use my votes for feature requests. I just wonder if there is/are already any kind of plan(s) for UniDAC to have support for TGuid fields, please.

Thanks.

[ertank]

After making a small test using above suggestions, I still cannot import data using TUniLoader. PostgreSQL connection specific parameter set to protocol "pv20" at design time. My code in my test application:

Code: Select all

  UniQuery1.Close();
  UniQuery1.ParamByName('kaydeden').AsString := 'AUTO';
  UniQuery1.ParamByName('kayitzamani').AsString := FormatDateTime('yyyy-mm-dd hh:nn:ss', Now());
  UniQuery1.DataTypeMap.AddFieldNameRule ('verilencevap', ftBoolean);
  UniQuery1.DataTypeMap.AddFieldNameRule ('kayitzamani', ftDateTime);
  UniQuery1.Open();

  UniLoader1.LoadFromDataSet(UniQuery1);

Error I am receiving:

Code: Select all

Error on data writing to the connection:
An existing connection was forcibly closed by the remote host.
Socket Error Code: 10054($2746)

I see following communication in PostgreSQL 10.1 logs:

Code: Select all

2018-01-06 02:12:38.998 +03 [1704] LOG:  connection received: host=127.0.0.1 port=3843
2018-01-06 02:12:39.000 +03 [1704] LOG:  connection authorized: user=postgres database=GRAPOR
2018-01-06 02:12:39.001 +03 [1704] LOG:  statement: SELECT VERSION()
2018-01-06 02:12:39.001 +03 [1704] LOG:  statement: SET client_encoding='WIN1254'
2018-01-06 02:12:39.003 +03 [1704] LOG:  statement: show integer_datetimes
2018-01-06 02:12:39.003 +03 [1704] LOG:  statement: show bytea_output
2018-01-06 02:12:40.166 +03 [1704] LOG:  statement: SELECT * FROM cevaplar WHERE 1=0
2018-01-06 02:12:40.167 +03 [1704] LOG:  statement: COPY cevaplar(autoinc, yil, ay, turu, emailid, verilencevap, cevapzamani, cevapverenipnumarasi, kaydeden, kayitzamani) FROM STDIN
2018-01-06 02:12:40.167 +03 [1704] ERROR:  missing data for column "verilencevap"
2018-01-06 02:12:40.167 +03 [1704] CONTEXT:  COPY cevaplar, line 1: "\N	2018	1	0	"
2018-01-06 02:12:40.167 +03 [1704] STATEMENT:  COPY cevaplar(autoinc, yil, ay, turu, emailid, verilencevap, cevapzamani, cevapverenipnumarasi, kaydeden, kayitzamani) FROM STDIN
2018-01-06 02:12:40.167 +03 [1704] FATAL:  terminating connection because protocol synchronization was lost
2018-01-06 02:12:40.167 +03 [1704] LOG:  disconnection: session time: 0:00:01.169 user=postgres database=GRAPOR host=127.0.0.1 port=3843

I double checked and that cevaplar table has emailid as varchar(38) in that test database.

In this case, I will import that table manually by code as I am out of time. However, I would like to know if there is a solution for sure. Because it is still possible for me to update my application in the future.

Thanks & regards,
Ertan Küçüloğlu

[azyk]

Currently, we are investigating the specified behavior and will let you know the results.

As a workaround, you can use the Load method with the onGetColumnData event handler instead of the LoadFromDataSet method
In order for the loader to use the pv20 protocol, set the specific option 'TextMode' to 'True'. For example:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  UniQuery1.Close();
  UniQuery1.SQL.Text :=
    'select autoinc, year as yil, month as ay, turu, emailid, reply as verilencevap, '
    + ' createtime as cevapzamani, ip as cevapverenipnumarasi, ' +
    ' :kaydeden as kaydeden, :kayitzamani as kayitzamani from replies';
  UniQuery1.ParamByName('kaydeden').AsString := 'AUTO';
  UniQuery1.ParamByName('kayitzamani').AsString := FormatDateTime('yyyy-mm-dd hh:nn:ss', Now());

  UniQuery1.Open();
  UniLoader1.SpecificOptions.Values['TextMode'] := 'True';
//   UniLoader1.LoadFromDataSet(UniQuery1);
  UniLoader1.Load;
end;

Code: Select all

procedure TForm1.UniLoader1GetColumnData(Sender: TObject; Column: TDAColumn;
  Row: Integer; var Value: Variant; var IsEOF: Boolean);
var
  TmpField: TField;
begin
  IsEOF := Row > UniQuery1.RecordCount;

  if not IsEOF then
  begin

    if Row > UniQuery1.RecNo then
      UniQuery1.Next;
    TmpField := UniQuery1.FindField(Column.Name);

    if TmpField <> nil then
    begin
      if (Column.Name = 'cevapverenipnumarasi') and (TmpField.Value = '') then
        Value := Null
      else
        Value := TmpField.Value;
    end
    else
      Value := Null;
  end;
end;

[ertank]

Last workaround did not work for me and produced following log on PostgreSQL:

Code: Select all

2018-01-11 22:53:36.922 +03 [2116] LOG:  statement: SELECT * FROM cevaplar WHERE 1=0
2018-01-11 22:53:36.923 +03 [2116] LOG:  statement: COPY cevaplar(autoinc, yil, ay, turu, emailid, verilencevap, cevapzamani, cevapverenipnumarasi, kaydeden, kayitzamani) FROM STDIN
2018-01-11 22:53:36.930 +03 [2116] ERROR:  null value in column "autoinc" violates not-null constraint
2018-01-11 22:53:36.930 +03 [2116] DETAIL:  Failing row contains (null, 2018, 1, 0, {8FF9F31A-8BCF-407F-AADA-1010BD54660A}, 2018-01-03 01:28:19, null, AUTO, 2018-01-11 22:53:36, t).
2018-01-11 22:53:36.930 +03 [2116] CONTEXT:  COPY cevaplar, line 1: "\N	2018	1	0	{8FF9F31A-8BCF-407F-AADA-1010BD54660A}	t	2018-01-03 01:28:19.000000	\N	AUTO	2018-01-11 2..."
2018-01-11 22:53:36.930 +03 [2116] STATEMENT:  COPY cevaplar(autoinc, yil, ay, turu, emailid, verilencevap, cevapzamani, cevapverenipnumarasi, kaydeden, kayitzamani) FROM STDIN
2018-01-11 22:53:36.930 +03 [2116] FATAL:  terminating connection because protocol synchronization was lost
2018-01-11 22:53:36.930 +03 [2116] LOG:  disconnection: session time: 0:00:02.192 user=postgres database=GRAPOR host=127.0.0.1 port=2913

Error I receive is:

Code: Select all

Error on data writing to the connection:
An existing connection was forcibly closed by the remote host.
Socket Error Code: 10054($2746)

Even my source Query doesn't have autoinc column selected, table itself has this column and TUniLoader assigns null value to that primary key column in OnGetColumnData() event is what I observe.

Normally, TUniLoader handles these autoincrement primary keys internally. I am not sure how to handle them when using TUniLoader.Load() and TUniLoader.OnGetColumnData() events.

[azyk]

The specified error occurs, because there is a NULL value for the autoinc field in SQL SELECT. If you want the autoinc field to be filled with values on PostgreSQL side, delete this field from the columns of the UniLoader1 component. For example:

Code: Select all

  ...
  if UniLoader1.Columns.Items[0].Name = 'autoinc' then
    UniLoader1.Columns.Delete(0);
  UniLoader1.Load;

[ertank]

After removing 'autoinc' column in TUniLoader.Columns everything seems to fine. My latest test code which is working is below:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  UniQuery1.Close();
  UniQuery1.ParamByName('kaydeden').AsString := 'AUTO';
  UniQuery1.ParamByName('kayitzamani').AsString := FormatDateTime('yyyy-mm-dd hh:nn:ss', Now());
  UniQuery1.DataTypeMap.AddFieldNameRule ('verilencevap', ftBoolean);
  UniQuery1.DataTypeMap.AddFieldNameRule ('kayitzamani', ftDateTime);
  UniQuery1.Open();

  UniLoader1.SpecificOptions.Values['TextMode'] := 'True';
  UniLoader1.Columns.Delete(0);  // Column[0] is 'autoinc' column
  UniLoader1.Load;
end;

procedure TForm1.UniLoader1GetColumnData(Sender: TObject; Column: TDAColumn;
  Row: Integer; var Value: Variant; var IsEOF: Boolean);
var
  TmpField: TField;
begin
  IsEOF := Row > UniQuery1.RecordCount;

  if not IsEOF then
  begin

    if Row > UniQuery1.RecNo then
      UniQuery1.Next;
    TmpField := UniQuery1.FindField(Column.Name);

    if TmpField <> nil then
    begin
      if (Column.Name = 'cevapverenipnumarasi') and (TmpField.Value = '') then
        Value := Null
      else
        Value := TmpField.Value;
    end
    else
      Value := Null;
  end;
end;

I have one question remaining. If I use TUnioader using above code. Will it still be able to bulk load records as it used to do depending on Database capabilities (PostgreSQL, SQL Server, SQLite etc)?

In another words, will it be as fast if I would be able to use

Code: Select all

UniLoader1.LoadFromDataSet(Query);

instead of above workaround code?

[azyk]

As in the solution I proposed, the loader uses the pv20 protocol, then mapping rules for the 'verilencevap' and 'kayitzamani' fields should be disabled. Also, leave the check before calling UniLoader1.Columns.Delete(0); otherwise another call of the code will delete the 'yil' column in the loader, another one will delete 'ay', etc.

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  UniQuery1.Close();
  UniQuery1.ParamByName('kaydeden').AsString := 'AUTO';
  UniQuery1.ParamByName('kayitzamani').AsString := FormatDateTime('yyyy-mm-dd hh:nn:ss', Now());
//  UniQuery1.DataTypeMap.AddFieldNameRule ('verilencevap', ftBoolean); // remove line
//  UniQuery1.DataTypeMap.AddFieldNameRule ('kayitzamani', ftDateTime); // remove line
  UniQuery1.Open();

  UniLoader1.SpecificOptions.Values['TextMode'] := 'True';
  if UniLoader1.Columns.Items[0].Name = 'autoinc' then // add line
    UniLoader1.Columns.Delete(0);  // Column[0] is 'autoinc' column
  UniLoader1.Load;
end;


procedure TForm1.UniLoader1GetColumnData(Sender: TObject; Column: TDAColumn;
  Row: Integer; var Value: Variant; var IsEOF: Boolean);
var
  TmpField: TField;
begin
  IsEOF := Row > UniQuery1.RecordCount;

  if not IsEOF then
  begin

    if Row > UniQuery1.RecNo then
      UniQuery1.Next;
    TmpField := UniQuery1.FindField(Column.Name);

    if TmpField <> nil then
    begin
      if (Column.Name = 'cevapverenipnumarasi') and (TmpField.Value = '') then
        Value := Null
      else
        Value := TmpField.Value;
    end
    else
      Value := Null;
  end;
end;

Currently, we are investigating the error cause when calling the TUniLoader.LoadFromDataSet method while loading uud values using the pv30 protocol. We will let you know the results.

You can measure the data loading time when using TUniLoader.LoadFromDataSet and TUniLoader.Load in your environment by yourself.

[azyk]

We fixed the error with calling the TUniLoader.LoadFromDataSet method when loading uud values using the pv30 protocol. This fix will be included in the next UniDAC build. If you want to get this fix before the official release, please let us know using the contact form at our website: https://www.devart.com/company/contactform.html and we will send you a night build.