Insert Statement failed

[Coolman1974]

Hello together,
i execute the follow code:

...
for I := 0 to QuerySource.FieldCount - 1 do
begin
if (not FieldIsEmpty(QuerySource.Fields)) then
begin
QueryDestination.ParamByName(QuerySource.Fields.FieldName).DataType :=
QuerySource.Fields.DataType;

QueryDestination.ParamByName(QuerySource.Fields.FieldName).Value :=
QuerySource.Fields.Value;
end;
end;

QueryDestination.ExecSql.
....

With the execution of the ExecSql i get the Error:
EOleException "Cannot insert the Value NULL into column LFDNR" Column does not allow nulls. Insert failed.

I use a TZQuery for the Insert.
The Field LFDNR is a PK-Field with Flag NOT NULL.

When I debug the code than i have currently a 0 in .Value for the Param and not NULL.

When i try to write a 1 for LFDNR than it is OK.

I've wondered why i cant insert the Value 0 in a PK Field with NOT NULL Flag.

Need help!

Thanks.

[Coolman1974]

FIELD1 = PK Integer not null
FIELD2 = PK Integer not null
FIELD3 = PK Integer not null

This works:

QueryDestination.Close;
QueryDestination.SQL.Clear;
QueryDestination.SQL.Add('INSERT INTO MATABLE (FIELD1, FIELD2, FIELD3) VALUES (1, 2, 3)');
QueryDestination.ExecSql;

This not:

QueryDestination.Close;
QueryDestination.SQL.Clear;
QueryDestination.SQL.Add('INSERT INTO MATABLE (FIELD1, FIELD2, FIELD3) VALUES (:FIELD1, :FIELD2, :FIELD3)');
QueryDestination.ParamByName('FIELD1').AsInteger := 1;
QueryDestination.ParamByName('FIELD2').AsInteger := 2;
QueryDestination.ParamByName('FIELD3').AsInteger := 0;
QueryDestination.ExecSql;

Than i get the Error message:
"Cannot insert the Value NULL into column FIELD3" Column does not allow nulls. Insert failed.

[mdaems]

This is strange. Seems like he's not using the 0 you assign to parameter :FIELD3.
Have you tried to use a TZSqlMonitor component to log what's exactly sent to the database?

[Coolman1974]

No, i will do it tomorrow. I post the Result here.
Thanks for your Answer.

[EgonHugeist]

Coolman1974, mdaems,

Hmm that's not strange at all. It might be possible that the compare of a null value(0, ftInteger) fails(null-variant as integer = 0). Let's see what the logs do tell us. If the assigned 0 value is really loggen as 'NULL' than the internal IsNull isn't set corectly. We need a testcase..

[Coolman1974]

Here is the Result of the Monitor:
2013-01-31 08:48:31 cat: Connect, proto: ado, msg: CONNECT TO "Provider=SQLOLEDB.1;Password=myPW;Persist Security Info=True;User ID=myUSER;Initial Catalog=MSSQL_DBMOVER;Data Source=mySERVER\SQLEXPRESS" AS USER ""

2013-01-31 08:48:31 cat: Execute, proto: ado, msg: SET CATALOG

2013-01-31 08:48:41 cat: Execute, proto: ado, msg: INSERT INTO MYTABLE (FIELD1, FIELD2, FIELD3) VALUES (?, ?, ?), errcode: 0, error: Cannot insert the value NULL into column 'FIELD3', table 'MSSQL_DBMOVER.dbo.MYTABLE'; column does not allow nulls. INSERT fails

Here is the Table declaration:
CREATE TABLE MYTABLE
(
FIELD1 INTEGER NOT NULL,
FIELD2 INTEGER NOT NULL,
FIELD3 INTEGER NOT NULL,
FIELD4 NVARCHAR(40),
FIELD5 NVARCHAR(40),
CONSTRAINT PK_MYTABLE PRIMARY KEY (FIELD1,FIELD2,FIELD3)
)

[Coolman1974]

The compare of "P.Value <> V" failed.

In this case:
P.Value = unassigned
V = 0
The result of the compare is FALSE. This is wrong.

Unit: ZDbcAdoStatement
Class: TZAdoPreparedStatement
Method: SetInParam
Line: 433

i've changed:
if P.Value <> V then
P.Value := V;

to:

if (P.Value = unassigned) or (P.Value <> V) then
P.Value := V;

I think that could be better. or?

[mdaems]

Hi Coolman1974,

Did you effectively make the change you propose (literally) and retest the program with the new version? Because the change makes sense I think, but I can't really test it as I don't have the test suite running against ADO. So comparing old results to new results is not possible.
I suppose you never ran our test suite yourself? Because that would be ideal : run it, save results, change the code and compare the results again to see if it doesn't break anything. But it's not so easy to start working with the test suite.

Can you confirm this change does fix the problem and does not (visibly) change the parameter functionality of your other queries?

[Coolman1974]

Hi Mark,
yes you right, i never run the test suite. I've tested some different cases. I tested the Querys with a connection to firebird and mssql(ado). I've tested with some different tables. All test results are ok.

Now, i'll find out how i can use the test suite you talked from.

[EgonHugeist]

Coolman1974,

i've tested your patch.. Nope that's not the solution at all. I've added your example as failing test to our test-suites. I'll continue to find a smarter solution.

Accordingly the test-suites: http://zeos.firmos.at/viewtopic.php?t=3671