Devart Forums

There is a problem when a query has to work with empty strings (send/receive) in SQL Lite.

This problem was reported in early December v6.0.1 and promised to be fixed in January (next release) but the problem still persists

Hello,

Please clarify: what issue are you talking about?

If you execute this query:

Q.sql.text:='select * from customer';

And any of the fields returned are empty, then an error is fired when working with this field.

We cannot reproduce the problem. Please send the scripts for creating all the used DB objects and a sample reproducing the problem to alexp*devart*com .

This is my scenario:

I have 2 databases, a remote (SQL Server) and a local (SQL Lite, Firemonkey Android/iOS), I try to migrate data from remote to local (mobile) and when there are empty string fields an Assertion error ocurrs.

...
// qQryRemote (tUniQuery) connects to a SQL Server with the new technology in Direct mode // and imports data to a SQL Lite (local) database executing command by cCmdLocal (tUniSQL)
qQryRemote.SQL.Clear;
qQryRemote.SQL.Add('SELECT cus_Code,cus_Name,cus_Phone FROM cxc_CUSTOMER');
qQryRemote.Execute;
qQryRemote.First;
while not qQryRemote.eof do
begin
cCmdLocal.SQL.Clear;
cCmdLocal.SQL.Add('INSERT INTO cxc_CUSTOMER (cus_Code,cus_Name,cus_Phone)');
cCmdLocal.SQL.Add(' VALUES (:cod,:nam,:tel)';
cCmdLocal.ParamByName('cod').Value:=qQryRemote.FieldByName('cus_Code').Value;
cCmdLocal.ParamByName('nam').Value :=qQryRemote.FieldByName('cus_Name').Value;
cCmdLocal.ParamByName('tel').Value :=qQryRemote.FieldByName('cus_Phone').Value;
//THIS LINE RISES AN EXCEPTION WHEN cus_Phone IS EMPTY IN REMOTE/SQL SERVER
cCmdLocal.Execute;
qQryRemote.Next;
end;
...

*-*-*-*-*-*-*-*
CHANGING:
qQryRemote.SQL.Add('SELECT cus_Code,cus_Name,cus_Phone FROM cxc_CUSTOMER');
BY:
qQryRemote.SQL.Add('SELECT cus_Code,cus_Name,''_''+cus_Phone cus_Phone FROM cxc_CUSTOMER');
FIXES THE ERROR, BUT IT ALTERS THE DATA

This was previously reported...
Can you reproduce it?

Please send the complete script for creating the tables.

Sent by email...

We have received your tables, but the problem wasn't reproduced. We checked with an empty string and NULL. Please send your project demonstrating the problem and the script for inserting test data.

Hi Alex!
I just sent you an email with all the files needed to reproduce the error.

Everything looks like the problem is when OleDBProvider property is set to prDirect, which is needed in mobile environments.

I hope that now you could reproduce and fix the problem.

Thank you in advanced.

The problem is not reproduced even on your sample. Please specify the exact versions of SQL Server, SQLite, IDE, and the device you tested this sample on.

P.S. In, addition, send a script for populating the table with test data.

I sent you a backup of the sql server database with data populated (dbDEVART.BAK) and the database for SQL Lite

SQL Server 2014 64-bits
Windows 8.1 64-bits
Delphi XE7
SQL Lite (The one integrated with UNIDAC)
Android 4.4 on a Sony XPeria Z3 and a Samsung Galaxy S4 Android 4.3

*Something you changed (I think), I began from Zero and I even test it in another computer, anf failed too.

Do you want me to send a video demonstrating it?

The error is triggered when try to INSERT the new data, not in the query itself.

in my sample:
...
qQry.SQL.Text:='SELECT id,name,phone FROM tab_DEVART';
// NEXT LINE FIXES THE ASSERTION ERROR
// IT IS ONLY FIRED WHEN OLEdbProvider IS 'prDirect' WHICH IS NEEDED FOR MOBILES
// qQry.SQL.Text:='SELECT id,name,''_''+phone phone FROM tab_DEVART';
...
This previous line fixes the problem, but it is not fired in that line, but in next lines:

...
cCmd.ParamByName('nam').Value:=qQry.FieldByName('name').Value;
cCmd.ParamByName('pho').Value:=qQry.FieldByName('phone').Value; // This is the one
// When phone field is empty or null
cCmd.Execute;


***
Just to confirm...
...

I NEED SUPPORT!

Do you need a TeamViewer ID to connect to my PC and confirm?

Do you need that I record a video and send it?

Do you need pictures of the errors?

Do you need me to pay again for the subscription?

Do you want that I connect to your PC and show you?

Please, help me!
i can not even use v6.0.2

We cannot reproduce the issue even on your sample. If you have a source code version, we can check this case on your PC using a Remote Desktop Manager (e.g., TeamViewer).

Alex, me again, I sent you a video/steps capture with the error reproduction.

I also included the info for you to connect to the pc when you are available.

Thanks in advanced!