How to make auto increament key to trigger ?

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
inageib
Posts: 177
Joined: Wed 26 Aug 2009 14:11

How to make auto increament key to trigger ?

Post by inageib » Wed 25 Jul 2018 01:28

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
Devart Team
Posts: 1030
Joined: Mon 06 Jul 2015 11:34

Re: How to make auto increament key to trigger ?

Post by MaximG » Thu 26 Jul 2018 10:30

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
Posts: 177
Joined: Wed 26 Aug 2009 14:11

Re: How to make auto increament key to trigger ?

Post by inageib » Fri 27 Jul 2018 16:38

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
Devart Team
Posts: 1030
Joined: Mon 06 Jul 2015 11:34

Re: How to make auto increament key to trigger ?

Post by MaximG » Mon 30 Jul 2018 09:38

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
Posts: 177
Joined: Wed 26 Aug 2009 14:11

Re: How to make auto increament key to trigger ?

Post by inageib » Thu 09 Aug 2018 19:24

I sent a demo please check it.

thanks

MaximG
Devart Team
Posts: 1030
Joined: Mon 06 Jul 2015 11:34

Re: How to make auto increament key to trigger ?

Post by MaximG » Fri 10 Aug 2018 09:38

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
Posts: 177
Joined: Wed 26 Aug 2009 14:11

Re: How to make auto increament key to trigger ?

Post by inageib » Fri 10 Aug 2018 17:08

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
Posts: 177
Joined: Wed 26 Aug 2009 14:11

Re: How to make auto increament key to trigger ?

Post by inageib » Wed 15 Aug 2018 09:16

Any replay please.

MaximG
Devart Team
Posts: 1030
Joined: Mon 06 Jul 2015 11:34

Re: How to make auto increament key to trigger ?

Post by MaximG » Wed 15 Aug 2018 14:29

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
Posts: 177
Joined: Wed 26 Aug 2009 14:11

Re: How to make auto increament key to trigger ?

Post by inageib » Wed 15 Aug 2018 22:10

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
Devart Team
Posts: 1030
Joined: Mon 06 Jul 2015 11:34

Re: How to make auto increament key to trigger ?

Post by MaximG » Fri 17 Aug 2018 08:35

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
Posts: 177
Joined: Wed 26 Aug 2009 14:11

Re: How to make auto increament key to trigger ?

Post by inageib » Fri 17 Aug 2018 09:24

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

Post Reply