Android problem with version 9 and PostgreSQL

[badmood]

Hi,

I have a simple code where I copy from a temporary SQLite table to the main PostgreSQL table from an Android phone (after checking if I am connected or not).

Code: Select all

    tblTemp.First;

    while not tblTemp.Eof do
    begin
      qryPg.SQL.Text :=
        'INSERT INTO foto_carico (id, nr_ordine, nr_foto, descrizione, immagine, dt_carico, hh_carico, sigla_abilitazione_foto, id_cliente_master) ' +
        'VALUES (:id, :nr_ordine, :nr_foto, :descrizione, :immagine, :dt_carico, :hh_carico, :sigla_abilitazione_foto, :id_cliente_master) ' +
        'ON CONFLICT (id, nr_ordine, nr_foto) DO NOTHING;';
      qryPg.ParamByName('id').AsString                      := tblTemp.FieldByName('id').AsString;
      qryPg.ParamByName('id_cliente_master').AsInteger      := tblTemp.FieldByName('id_cliente_master').AsInteger;
      qryPg.ParamByName('sigla_abilitazione_foto').AsString := tblTemp.FieldByName('sigla_abilitazione_foto').AsString;
      qryPg.ParamByName('nr_ordine').AsString               := tblTemp.FieldByName('nr_ordine').AsString;
      qryPg.ParamByName('nr_foto').AsInteger                := tblTemp.FieldByName('nr_foto').AsInteger;
      qryPg.ParamByName('descrizione').AsString             := tblTemp.FieldByName('descrizione').AsString;
      qryPg.ParamByName('dt_carico').AsDate                 := tblTemp.FieldByName('dt_carico').AsDateTime;
      qryPg.ParamByName('hh_carico').AsTime                 := tblTemp.FieldByName('hh_carico').AsDateTime;
      qryPg.ParamByName('immagine').Value                   := tblTemp.FieldByName('immagine').Value;
      qryPg.ExecSQL;

      tblFotoTemp.Delete;
    end;

This code works on 8.3.2 version of UniDAC.

Stop working with 9.0.1 giving this message: "invalid byte sequence for 'utf8' encoding".

- either SQLite and PostgreSQL are defined as UTF
- the code is untouched from 8.3.2 to 9.0.1, only recompiled

Can someone check what is worng on new version?

Thanks in advance.

Sergio

[evgeniym]

Hi Sergio!
Thank you for your request!
Unfortunately, we couldn't reproduce the issue on our side.
Please send us a sample that uses UniDAC and demonstrates the incorrect behavior, along with DDL scripts for creating and populating the database objects.
Please send them through the form on our website: https://devart.com/company/contactform.html
Should you have any questions, do not hesitate to ask!
Regards,
Evgeniy

[badmood]

Dear Evgeniy,

I was talking about UniDAC, not PgDAC.
I think you copied and pasted the "Devart standard answer" from another post.

I can understand that you prefer a code to test without the need to create it by yourself for debugging, but we are paying customers, and we can't always give you a crafted sample extracted from a maybe complex procedure or even we haven't the time to do it in a clean way.
I am using the 8.3 version because initially the 8.4 (and now the 9.0) are giving me troubles. But I always pay the subscription, and I am not happy with the quality of the upgrades.

That said, I will try to make a working sample to send, but PLEASE improve the quality control.

Sergio

[FCS]

Hi,

Did you try the 'insert' without this line ?

qryPg.ParamByName('immagine').Value := tblTemp.FieldByName('immagine').Value;

Your procedure copies record by record from SQLite into PG table.

On the fly you convert the DateTime values and probably BLOB field 'immagine'.
Maybe some changes were made in UniDac about BLOB fields.

What error message you get ?

Regards
M.

[badmood]

Dear M,

I've alredy tested without it last week, before having the idea of reverting to old 8.3.

The problem is that in version 8.3 is working great... but in 9.0 it gives the error "invalid byte sequence for 'utf8' encoding". writing the record (the ExecSQL statement, I've debugged the procedure).

Regards.

Sergio

[FCS]

Hi,

Maybe there are differences in char conversion option in UniConnection parameters ?

Or are added new options which by default working in different way.

Regards
M.

[badmood]

Hi M.

as I said I have only recompiled the project after upgrading to 9.0. No editing occurred at all.
Reverting to 8.3 and recompiling works without errors,

A problem with Params was inserted in version 8.4 and resolved in 8.4.4.
Maybe it's related to the same problem, that's been resolved on win32/64 but not on Android?
Or simply the ExecSQL uses an wrong coding without looking at the specificoptions of the UniConnection..

Regards.

Sergio

[evgeniym]

Hi Sergio!
Thank you for your reply!
Unfortunately, we are still unable to reproduce the described behavior.
As we wrote earlier, we need a small sample code that demonstrates the described behavior, as well as DDL scripts for creating and populating the database. Submit them via the form on our website: https://devart.com/company/contactform.html

Also please try to explicitly set ProtocolVersion = pv30 to connect to PostgreSQL and inform us about the results!
Regards,
Evgeniy

[badmood]

Hi all,

update to the topic.

Due to the complexity of the procedure (there are other components involved), I am trying to make a working (or better a non-working) proof of concept. But in this case I put the values in the SQLite fields by hand before writing them on the PostgreSQL table and... strange enough, in this case the program write the PostgreSQL row without errors (with the difference that I leave the blob field null in the SQLite table).
I the next days if I can I will try to redo the test with an image loaded in the SQLite table.


Regards.

Sergio

[evgeniym]

Thanks for the information. We are looking forward for an example.
May I kindly ask if you have tried setting PostgreSQL's protocol version pv30 instead of pvAuto (ProtocolVersion = pv30)?
Regards,
Evgeniy

[badmood]

Dear Evgeniy,

changing the ProtocolVersion to pv30 seems to solve the problem.

But, as you can understand, pvAuto is more "future proof", and it's easy to forget setting the parameter starting from scratch a new app. At the moment, anyway, this make my app run without any problem so as a workaround it's ok.

Thank you.

Regards.


Sergio

[evgeniym]

Hi Sergio!
Thank you for your reply!
We are glad to hear that your application is working now.
But we are still waiting for a small sample demonstrating the problematic behavior, as well as DDL scripts for creating a PostgreSQL database and a database file for SQLLite!
Please, provide the needed.
Regards,
Evgeniy

[badmood]

Dear Evgeniy,

I've sent the "demo" in support contact form titled
"Unidac 9.0 problem writing PostgreSQL data in Android application".

Regards.

Sergio

[evgeniym]

Hi Sergio!
Thank you for your reply!
Be informed that we have received your sample and are currently investigating it.
Please, rest assured that we will inform you immediately as soon as we have any results.
Should you have any questions, do not hesitate to ask!
Regards,
Evgeniy

[badmood]

Hi Evgeniy,

hope that the code works as expected! Or - better - that it... doesn't work like in my case.

Regards.


Sergio