3

I am converting a application from Delphi 5 to Delphi XE2 and came across a situation using the BDE.

In Delphi XE2 he does not accept pass null for a field that is a foreign key that follows is how this currently working in Delphi 5:

ParamByName('id').datatype: = ftInteger;

Error that occurs in Delphi XE2 with BDE: In the primary key value for foreign key 'XX' in table YY

Tested in the following ways with the same error:

ParamByName('id').datatype: = ftInteger;
ParamByName('id').value: = NULL;

ParamByName('id').DataType: = ftstring;
ParamByName('id').clear;

ParamByName('id').DataType: = ftstring;
ParamByName('id').Bound: = true;

ParamByName('id').Value: = null;

ParamByName('id').IsNull;

How to pass a field with null value?

UPDATE:

Database:

CREATE TABLE TEST_1 (
    ID_1 INTEGER NOT NULL,
    ID_TEST_2 INTEGER NULL,
    DESC_1 VARCHAR(10) NULL,
    PRIMARY KEY (ID_1)
);

CREATE TABLE TEST_2 (
    ID_2 INTEGER NOT NULL,
    DESC_2 VARCHAR(10) NULL,
    PRIMARY KEY (ID_2)
);

ALTER TABLE TEST_1 ADD FOREIGN KEY FK_TEST(ID_TEST_2) REFERENCES TEST_2(ID_2);

Delphi XE2:

Close;
SQL.Clear;
SQL.Add('insert into test_1 (id_1, id_test_2, desc_1) values (:id_1, :id_test_2, :desc_1)');
ParamByName('ID_1').AsInteger := 1;
ParamByName('ID_TEST_2').DataType := ftInteger;
ParamByName('ID_TEST_2').Clear;
ParamByName('ID_TEST_2').Bound := True;
ParamByName('DESC_1').AsString := 'DESCRIPTION TEST';
ExecSQL;

Result: Key violation.

Leonardo Gregianin
  • 206
  • 3
  • 12

2 Answers2

8

To pass NULL as a parameter value, use TParameter.Clear.

ParamByName('id').Clear;

As an aside: You really should have gotten rid of the BDE by now. It's been deprecated for more than a decade now, it is difficult to configure and use on modern versions of Windows, and may simply disappear from the Delphi distribution without notice at any time. (I'm surprised this hasn't happened yet, personally.) It also does not support any of the cross-platform targets that are available now. Surely 10 years is long enough to have been able to replace it.

Ken White
  • 123,280
  • 14
  • 225
  • 444
  • Yes, I am considering changing the BDE for the near future. The error continues: Field "XX" is of an unknown type. But the error is not in the BDE but Delphi. Null is not inserted, insert blank. The same discussion in FreePascal: http://free-pascal-general.1045716.n5.nabble.com/How-to-clear-the-value-of-a-field-in-a-table-using-parameters-td5716247.html – Leonardo Gregianin Apr 24 '14 at 14:49
  • Same error in this post, with Delphi XE4 and Dbexpress: http://stackoverflow.com/questions/17042429/passing-null-value-into-parameterized-delphi-sql-server-query?rq=1 – Leonardo Gregianin Apr 24 '14 at 16:03
  • 1
    What I posted works. If you have to pass either a NULL or a string, you'll need an `if..else` block, where it either assigns the string using AsString or uses Clear if it's NULL. (I use it all the time, although not with the BDE. If the error is in Delphi, please post the **exact** error message you're getting. – Ken White Apr 24 '14 at 16:10
1

When passing a NULL, you have to set Bound=True manually even when calling Clear(), and if it is the first time using the parameter than you usually have to set DataType manually as well. I tend to use code like this:

Param := ParamByName('id');
if (shound assign a value) then
begin
  Param.AsInteger := ...;
end else
begin
  Param.DataType := ftInteger;
  Param.Clear;
  Param.Bound := True;
end;
Remy Lebeau
  • 555,201
  • 31
  • 458
  • 770
  • Updated the main question showing the test I did and the Key violation error occurs. – Leonardo Gregianin Apr 28 '14 at 13:35
  • What is the **complete** error message? It should have told you what exactly what was violated. A *key violation* could be caused by one of several different reasons. If I had to guess (and you should never make people guess), I would guess that `TEST_1` already contains a row with a value of 1 in its `ID_1` field, since `ID_1` is a primary key and so each row must have a unique value. – Remy Lebeau Apr 28 '14 at 19:53
  • After created tables no data in them. When inserting the first record in the table ID_TEST_1 error occurs "key violation", because the field is getting ID_TEST_2 EMPTY and not NULL. Empty = Key Violation. Null = correct recording. – Leonardo Gregianin Apr 29 '14 at 12:26