[Solved] Firebird sqldb dbgrid: how to insert?

[BigChimp]

Hi all,

Thanks to Lacak2's answer to a recent post of mine the SQL DB tutorial
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1#Editing_data_using_the_grid
allows editing data.

However, inserting data without a valid primary key fails with

Code: [Select]

SQLQuery1: Field CUST_NO is required, but not supplied
I see 2 options:

• Remove the CUST_NO from the grid, but not the select and tweak insertsql/updatesql/deletesql somehow
• Do something with tproviderflags, but I don't know what exactly

Hope somebody can answer here or edit the wiki... Of course, if you have a cleaner, clearer solution, please don't hesitate!

Thanks!

[BigChimp]

I've been able to hide the primary key (CUST_NO) column in the grid, but leave it in the SQLQuery.
Of course, that doesn't solve the problem that FPC/Lazarus thinks an autonumber/Firebird generator-bound primary key is required on insert, while it is not...

Any hints on this?

[JD]

I've been able to hide the primary key (CUST_NO) column in the grid, but leave it in the SQLQuery.
Of course, that doesn't solve the problem that FPC/Lazarus thinks an autonumber/Firebird generator-bound primary key is required on insert, while it is not...

Any hints on this?

The problem is not FPC/Lazarus. It is in your table in the Firebird database. If you have created the Customer table with an autoincrement primary key, you must create a BeforeInsert trigger in the database which calls a generator that autoincrements the primary key in the table.

Once that is done, you'll stop getting errors in the SQLQuery. You'll know it works because if you can insert data directly into the table from inside Firebird, your SQLQuery will also be able to insert data without problems.

[BigChimp]

The problem is not FPC/Lazarus. It is in your table in the Firebird database. If you have created the Customer table with an autoincrement primary key, you must create a BeforeInsert trigger which calls a generator that autoincrements the primary key in the table.

Once that is done, you'll stop getting errors in the SQLQuery. You'll know it works because if you can insert data directly into the table from inside Firebird, your SQLQuery will also be able to insert data without problems.

Hi JD, thanks for the answer.

I'm using the standard Firebird employee.fdb sample database that has a generator and a trigger:

CREATE TRIGGER SET_CUST_NO FOR CUSTOMER ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
    if (new.cust_no is null) then
    new.cust_no = gen_id(cust_no_gen, 1);
END^

I can insert data using e.g. Flamerobin:

Code: [Select]

INSERT INTO CUSTOMER (CUSTOMER, CONTACT_FIRST, CONTACT_LAST, PHONE_NO, ADDRESS_LINE1, ADDRESS_LINE2, CITY, STATE_PROVINCE, COUNTRY, POSTAL_CODE, ON_HOLD) VALUES ('Buttle, Griffith and Co.', 'James', 'Buttle', '(617) 488-1864', '2300 Newbury Street', 'Suite 101', 'Boston', 'MA', 'USA', '02115', NULL);

 but I think the problem is that FPC thinks the CUST_NO field is required so won't allow blank (NULL) entries.

Oh by the way, you can download the project as I have it now from my repository at
https://bitbucket.org/reiniero/fpc_laz_patch_playground/src
(ignore the db_export directory)
Thanks...

[JD]

In your FlameRobin insert data, I can see that you are assigning NULL to a field. What field is that? The CustNo field? If that is the case, remember that an autoincremented field cannot have a NULL value.

[BigChimp]

Nope, it's the ON_HOLD field (see the field list after INSERT INTO CUSTOMER)
And this Flamerobin statement works.
I don't specify the CUST_NO field, so that would implicitly be set to NULL.

If I do this in Flamerobin, it also works:

Code: [Select]

INSERT INTO CUSTOMER (CUST_NO, CUSTOMER) VALUES (NULL, 'Just a customer');

Why? Because the trigger checks for NULL CUST_NO values and then substitues a new generator value.

It's a really standard way of doing things in Firebird - it's a sample database, too 

[BigChimp]

If you want to have a look, you can download the latest source code and executable (with Firebird DLLS) of the tutorial at:

Edit Jan 2012:
https://bitbucket.org/reiniero/fpc_laz_patch_playground][url]https://bitbucket.org/reiniero/fpc_laz_patch_playground
then the download tab, choose SQLDBTutorial file.

this link doesn't seem to work anymore:
https://bitbucket.org/reiniero/fpc_laz_patch_playground/downloads/SQLDBTutorial1_3Sept2011.zip

[BigChimp]

Solved, thanks to a tip from Michael Van Canneyt in the FPC mailing list:

Code: [Select]

   SQLQuery1.Open;
    {
    Make sure we don't get problems with inserting blank (=NULL) CUST_NO values, e.g.:
    Field CUST_NO is required, but not supplied
    We need to tell Lazarus that, while CUST_NO is a primary key, it is not required
    when inserting new records.
    }
    SQLQuery1.FieldByName('CUST_NO').Required:=false;

I will adapt the tutorial:
http://wiki.lazarus.freepascal.org/index.php?title=SQLdb_Tutorial1

Thanks for the help & time, JD and everyone who read this thread,
BigChimp