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)
inageib
Posts: 180
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: 1164
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: 180
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: 1164
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: 180
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: 1164
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: 180
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: 180
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: 1164
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: 180
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: 1164
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: 180
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

FCS
Posts: 148
Joined: Sat 23 Feb 2013 18:46

Re: How to make auto increament key to trigger ?

Post by FCS » Sat 18 Aug 2018 15:07

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

Re: How to make auto increament key to trigger ?

Post by inageib » Sat 18 Aug 2018 17:58

thank but I already set defaultvalues = true

inageib
Posts: 180
Joined: Wed 26 Aug 2009 14:11

Re: How to make auto increament key to trigger ?

Post by inageib » Sun 19 Aug 2018 11:45

@MaximG

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

Post Reply