How to make auto increament key to trigger ?

[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 ?

[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;

[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

[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

[inageib]

I sent a demo please check it.

thanks

[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

[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

[inageib]

Any replay please.

[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

[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

[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.

[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

[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

[inageib]

thank but I already set defaultvalues = true

[inageib]

@MaximG

Did you able to test this ? I really can not find a solution