Page 1 of 1
null auto inc key from sqlite but not mysql
Posted: Thu 29 Aug 2013 20:43
by 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
Re: null auto inc key from sqlite but not mysql
Posted: Fri 30 Aug 2013 08:08
by 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.
Re: null auto inc key from sqlite but not mysql
Posted: Fri 30 Aug 2013 18:13
by 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?
Re: null auto inc key from sqlite but not mysql
Posted: Wed 04 Sep 2013 10:47
by 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.
Re: null auto inc key from sqlite but not mysql
Posted: Wed 04 Sep 2013 19:02
by 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
Re: null auto inc key from sqlite but not mysql
Posted: Thu 05 Sep 2013 06:43
by 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
);