[Solved] Firebird sqldb dbgrid: how to insert?
lazarus / 16-Databases

----------------------------------------------------------------------
BigChimp · 2011-08-31 13:05

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 somehowDo 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 · 2011-09-02 10:33

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 · 2011-09-02 12:03

Quote from: BigChimp on September 02, 2011, 12:33:57 pmI'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 · 2011-09-02 12:08

Quote from: JD on September 02, 2011, 02:03:11 pmThe 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:
QuoteCREATE 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 · 2011-09-02 12:23

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 · 2011-09-02 12:43

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 · 2011-09-03 12:19

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://b
itbucket.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/SQLD
BTutorial1_3Sept2011.zip

----------------------------------------------------------------------
BigChimp · 2011-09-05 08:59

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