Insert Statement failed
Moderators: gto, cipto_kh, EgonHugeist
-
- Fresh Boarder
- Posts: 6
- Joined: 30.01.2013, 15:04
Insert Statement failed
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.
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.
-
- Fresh Boarder
- Posts: 6
- Joined: 30.01.2013, 15:04
More Informations
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.
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.
-
- Fresh Boarder
- Posts: 6
- Joined: 30.01.2013, 15:04
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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..
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..
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
-
- Fresh Boarder
- Posts: 6
- Joined: 30.01.2013, 15:04
TZSQLMonitor result
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)
)
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)
)
-
- Fresh Boarder
- Posts: 6
- Joined: 30.01.2013, 15:04
I think i've found the issue
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?
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
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
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?
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?
-
- Fresh Boarder
- Posts: 6
- Joined: 30.01.2013, 15:04
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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
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
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/