Page 1 of 2
How to make auto increament key to trigger ?
Posted: Wed 25 Jul 2018 01:28
by inageib
Hi
I am using UniDac with SQLite and I have field `id` that has auto increment to true but during run time and when I try to save a new record I get error that id need a value so what I should do ?
Re: How to make auto increament key to trigger ?
Posted: Thu 26 Jul 2018 10:30
by MaximG
The UniDAC components support autoincrement fields when using SQLite. Unfortunately, we could not reproduce the problem according to your description. The following code fragment demonstrates how to work with the autoincrement ID field. Verify the working capacity of this example in your environment, change it so that the problem is reproduced and inform us about the results.
Code: Select all
...
var
Query: TUniQuery;
begin
UniConnection.Connect;
UniConnection.ExecSql('Create Table TableAutoInc (ID Integer Primary Key Asc AutoIncrement, F_Char Char(250))');
Query := TUniQuery.Create(Nil);
try
Query.Connection := UniConnection;
Query.SQL.Text := 'Select * From TableAutoInc';
Query.Open;
Query.Append;
Query.FieldByName('F_Char').AsString := 'Record # 01';
Query.Post;
Query.Insert;
Query.FieldByName('F_Char').AsString := 'Record # 02';
Query.Post;
Query.Close;
finally
Query.Free;
end;
end;
Re: How to make auto increament key to trigger ?
Posted: Fri 27 Jul 2018 16:38
by inageib
I write same code but still get error that id need a value so there must be a property to change. I use TUNITable
Code: Select all
object tblPatients: TUniTable
TableName = 'patients'
Connection = UniConnection1
Transaction = UniTransaction1
UpdateTransaction = UniTransaction1
KeyFields = 'ptient_id'
Constraints = <>
IndexFieldNames = 'ptient_id'
Options.DefaultValues = True
Left = 144
Top = 96
end
Re: How to make auto increament key to trigger ?
Posted: Mon 30 Jul 2018 09:38
by MaximG
Unfortunately, the information you provided is not enough to reproduce the problem. We checked TUniTable according to your description and found no problems. Please compose and send us a small complete example, in which the problem reproduces. In addition, we will need a DDL script to create the table used in this example. You can send the sample using the contact form at our site:
https://www.devart.com/company/contactform.html
Re: How to make auto increament key to trigger ?
Posted: Thu 09 Aug 2018 19:24
by inageib
I sent a demo please check it.
thanks
Re: How to make auto increament key to trigger ?
Posted: Fri 10 Aug 2018 09:38
by MaximG
Working with SQLite auto increment fields in UniDAC entirely depends on the implementation of the behavior with them in this DB:
https://www.sqlite.org/autoinc.html Please make sure that in the database you are using, the fields which interest you are created with the type INTEGER PRIMARY KEY
Re: How to make auto increament key to trigger ?
Posted: Fri 10 Aug 2018 17:08
by inageib
I already did that here is the SQL
Code: Select all
CREATE TABLE patients (
ptient_id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
patient_name varchar(300) NOT NULL,
patient_age smallint,
patient_address varchar(300),
patient_phone varchar(50),
patient_weight float(50),
patient_notes text,
patient_mstatus smallint DEFAULT 0
);
the encryption key is in a txt file with the database
please advise what to do
Re: How to make auto increament key to trigger ?
Posted: Wed 15 Aug 2018 09:16
by inageib
Any replay please.
Re: How to make auto increament key to trigger ?
Posted: Wed 15 Aug 2018 14:29
by MaximG
We could not get access to the encrypted database doctordb.db using a password from the file ency key.txt We created a new database with the same name, in which we created the patients table using the sent SQL query. After then, we successfully inserted a couple of records using your application. To further investigate the issue, please send us the test database you are using without using encryption
Re: How to make auto increament key to trigger ?
Posted: Wed 15 Aug 2018 22:10
by inageib
I created a new demo with unencrypted db and a test button to insert new record. I sent it by the contact form. Please advise
Re: How to make auto increament key to trigger ?
Posted: Fri 17 Aug 2018 08:35
by MaximG
We tested the work of the sent sample in the Direct Mode, as well as using the latest version of the client library 3.24.0: The reason you get the described error message is the description of the ptient_id field when it is created. Create the ptient_id field as follows : ptient_id INTEGER PRIMARY KEY AUTOINCREMENT (instead of ptient_id integer NOT NULL PRIMARY KEY AUTOINCREMENT) and check your test application work using the client library 3.24.0 again.
Re: How to make auto increament key to trigger ?
Posted: Fri 17 Aug 2018 09:24
by inageib
thanks but this is can not be the reason. I use SQLite maestro and when I create a field it set it automatically as not null without any option to make it nullable. Beside inside sqlite maestro I can insert new records without getting the patient_id field must have a value so it got to be something I need to do from Delphi.
see picture
https://www.photobox.co.uk/my/photo/ful ... 0977236181
Re: How to make auto increament key to trigger ?
Posted: Sat 18 Aug 2018 15:07
by FCS
Hello,
Try use
Query.Options.DefaultValues:=true;
Maybe this solve your problem.
In the documentation is written:
A new record can be inserted by the Insert or Append method. The Append method adds record to the end of
dataset. The Insert method inserts record in the current position. After one of these methods is called, you should
assign values to the fields and call the Post method.
Probably you should change the Insert method to Append method in the second query.
Regards
Michal
Re: How to make auto increament key to trigger ?
Posted: Sat 18 Aug 2018 17:58
by inageib
thank but I already set defaultvalues = true
Re: How to make auto increament key to trigger ?
Posted: Sun 19 Aug 2018 11:45
by inageib
@MaximG
Did you able to test this ? I really can not find a solution