null auto inc key from sqlite but not mysql

[dragonstar]

So I have UniDac 5.0.1 under Delphi 7 on a Windows 7 64-bit Ultimate machine.

I have the same table in both MySql and SqLite each having the same autoinc key.

If I do an Insert and then Post my data the value of the key field, for example "UniQuery.FieldByName('key').AsString", is populated with the new auto-inc value with MySql but IS NULL when done against SqLite.

Not sure why they're different but I need that key value to be there after I issue .Post against SqLite.

Tried .RefreshRecord after post and it fails with 0 records found.

Help.

Thanks in advance.


drc

[AlexP]

Hello,

We cannot reproduce the problem. Please run the code below and make sure you create the auto-incremental field correctly.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  Uni,
  SQLiteUniProvider;

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ProviderName := 'SQLite';
    UniConnection.Database := ':memory:';
    UniConnection.Connect;
    UniConnection.ExecSQL('CREATE TABLE t_autoinc (  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,  `name` varchar(255))');
    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.SQL.Text := 'select * from t_autoinc';
      UniQuery.Open;
      UniQuery.Insert;
      UniQuery.FieldByName('name').AsString := 'test';
      UniQuery.Post;
      Writeln(UniQuery.FieldByName('id').AsString);
    finally
      UniQuery.Free;
    end;
  finally
    UniConnection.Free;
    Readln;
  end;
end.

[dragonstar]

The provide code appears to work, so we need to sort out why mine doesn't.

One of the differences is that I'm using the UniQuery as a table by setting the UpdatingTable property to the table name so I'm using .Append and .Post methods to add and save a new record.

I note your sample does a select * and then does an open, as I'm on a data screen where the table already has multiple records the sql is a select for the last record displayed so say there are 5 records in a table the sql would be "select * from table where key = 3' if the 3rd record is being displayed when the .Appen is fired.

Is this possibly why after I post the key value isn't there?, because the next key would 6 and after I post that record wouldn't be in the dataset as 6 isn't = 3 which is what the sql says?

If that sounds reasonable, i.e. that the sql.text select is acting as a filter so my new record isn't in the dataset - why does the same code work when I'm connecting to the MySql version?

What else can I tell you to help sort this out?

[ZEuS]

Unfortunately, we still cannot reproduce the problem as you have described it. Please create a small sample project that demonstrates the problem, including a sample database, and send it to eugeniyz*devart*com.

[dragonstar]

Banged together a sample project and just sent.

Local time is 2:02 PM US Central Time Zone in case you're monitoring that email address.


drc

[ZEuS]

Thank you for the information.
We have reproduced the problem with the help of your example and fixed it. The fix will be included in the next UniDAC release. We plan to release the new version of UniDAC this month.
For now, in order to avoid the problem, please recreate the test table as following:

Code: Select all

CREATE TABLE sometypes(
  TypeKey INTEGER PRIMARY KEY,
  Description VARCHAR(32) NOT NULL
);