SQLite Android Insert row with AutoIncrement

[dominikkv]

Hello,

I have a problem with the return value of an autoincremented field on Android. With UniDAC version 5.3 everything works fine, but with version > 6.0 the return value is always 0.

Consider the following table:

Code: Select all

CREATE TABLE tbl_Name ( 
    ID_AutoInc INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
    SomeField INTEGER
)

and the following (simplified) code to insert a row into that table:

Code: Select all

var
  table: TUniTable;
begin
  table := TUniTable.Create(nil);
  table.Connection := MyConnection;
  table.TableName := 'tbl_Name';
  table.CachedUpdates := True;
  table.Options.RequiredFields := False;
  
  table.Open;
  table.Append;

  table.FieldByName('SomeField').AsInteger := 42;

  table.ApplyUpdates();

  ShowMessage(table.FieldByName('ID_AutoInc').AsInteger.ToString());
end;

On Win32, everything works fine and I see the value of ID_AutoInc, but on Android the ShowMessage() always returns 0.

Do I miss something?
Tested with Delphi XE6 and XE7.


Thanks
Dominik

[AlexP]

Hello,

This behavior is caused by the sqlite3 library built without the ENABLE_COLUMN_METADATA option on Android. You can use Direct Mode, in which there is no such problem.

P.S. We will try to avoid this situation with the built-in library in the next version.

[dominikkv]

Thank you, AlexP, for your answer.

Direct Mode is supported for Android since UniDAC version 6.1, but unfortunately this version has the CRConnectionPool bug (http://forums.devart.com/viewtopic.php?f=28&t=31604).

Maybe you have an estimated release date for the next UniDAC version?

[AlexP]

New version will be released within 2 days.

[dominikkv]

Hello,

we have now upgraded to UniDAC 6.1.6 for Delphi XE6 but the error still occurs, now even with Win32.
I have tested with DirectMode and without DirectMode.

Any other suggestions?

[AlexP]

We have already fixed the problem, and we can send you a night build in order for you to check this case on your sample. For this, please send your license number, the IDE version, and your e-mail address to alexp*devart*com .

[dominikkv]

Thank you, I have sent you a mail

[AlexP]

I have received your email. Tomorrow I will send you a fixed build of UniDAC.

[dominikkv]

Hi Alex,

thank you for the nightly build. I have tested the new version with Delphi XE7 with a good and a bad result.

1) On Windows, everything works now (direct mode and normal mode).
2) On Android, normal mode does not work (always returns zero), but with direct mode the problem is solved. BUT now, there is another problem: if I create an entity and update this entity in one transaction, the update throws following error:

Im Projekt xyz.apk ist eine Exception der Klasse EFOpenError mit der Meldung 'Datei /data/data/com.company.xyz/files/BP1GrdbdqCCblrN.tmp kann nicht geöffnet werden. No such file or directory' aufgetreten.

In english: Exception of class EFOpenError with message: File ...temp cannot be opened. No such file or directory.

The filename changes every time I try.
If not in transaction the entity could be updated successfully.

Is this a bug of the nightly build?


Thanks
Dominik

[AlexP]

The value of the auto-incremental field is returned correctly on your sample both in Direct Mode and when using the client library. Please make sure you have deleted all the old UniDAC files (.pas, *.dcu, *.bpl), and all the old files of your project. Then reinstall the night build and try to reproduce the problem again.

Please provide a small sample or the code, that would reproduce the error.

[dominikkv]

Hey AlexP,

I did some more testing on this issue and it seems that there is a problem with the declaration NOT NULL on fields that are not primary key autoincrement. There are two problems:

• Non-direct mode on android does not return autoincrement value
• Direct mode insertion and update on one entity in a transaction fails with "no such file" exception

Both errors do occur if a field of the table is declared as NOT NULL. The primary key autoincrement NOT NULL seems to be ok.

Here a code example to reproduce the two errors:

Code: Select all

   var
    table: TUniTable;
    id: Integer;
   begin
    MyConnection.ExecSQL('DROP TABLE IF EXISTS tbl_Name');

    // A: this DDL SUCCEEDS
    MyConnection.ExecSQL('CREATE TABLE tbl_Name (ID_AutoInc INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, SomeField INTEGER)');

    // B: this DDL FAILS
    MyConnection.ExecSQL('CREATE TABLE tbl_Name (ID_AutoInc INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, SomeField INTEGER NOT NULL)');

    MyConnection.StartTransaction;

    try
      table := TUniTable.Create(nil);
      try
        table.Connection := MyConnection;
        table.TableName := 'tbl_Name';

        table.CachedUpdates := True;
        table.Options.RequiredFields := False;

        table.Open;
        table.Append;

        table.FieldByName('SomeField').AsInteger := 42;

        table.ApplyUpdates();

        ShowMessage('ID_AutoInc Insert: ' + table.FieldByName('ID_AutoInc').AsInteger.ToString());

        id := table.FieldByName('ID_AutoInc').AsInteger;
      finally
        table.Free;
      end;

      if id = 0 then exit; // we got no id...

      table := TUniTable.Create(nil);
      try
        table.Connection := MyConnection;
        table.TableName := 'tbl_Name';

        table.CachedUpdates := True;
        table.Options.RequiredFields := False;

        table.Open;
        if not table.Locate('ID_AutoInc', id, []) then raise Exception.CreateFmt('Cannot find entity %d', [id]);
        table.Edit;

        table.FieldByName('SomeField').AsInteger := 43;

        table.ApplyUpdates();

        ShowMessage('ID_AutoInc Update: ' + table.FieldByName('ID_AutoInc').AsInteger.ToString());
      finally
        table.Free;
      end;

      MyConnection.Commit;
    except
      MyConnection.Rollback;
      raise;
    end;

You can comment out A or B to let the example fail or succeed.
Execute the example on android.

With DDL A:
-> You get two messages: "ID_AutoInc Insert: 1" and "ID_AutoInc Update: 1"

With DDL B:
-> On non-direct mode, you get the message "ID_AutoInc Insert: 0".
-> On direct mode, you get an error "no such file or directory"

[AlexP]

Thank you for the sample, we have reproduced the described case, and will fix it as soon as possible

[dominikkv]

Thanks! If you need a beta tester, let me know

[AlexP]

We have already fixed work with instant client. This fix will be included into the next UniDAC version, which will be released the next week.