Insert Statement failed

Forum related to MS SQL Server

Moderators: gto, cipto_kh, EgonHugeist

Coolman1974
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 30.01.2013, 15:04

Insert Statement failed

Postby Coolman1974 » 30.01.2013, 15:16

Hello together,
i execute the follow code:

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

QueryDestination.ParamByName(QuerySource.Fields[I].FieldName).Value :=
QuerySource.Fields[I].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
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 30.01.2013, 15:04

More Informations

Postby Coolman1974 » 30.01.2013, 16:08

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.

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 30.01.2013, 21:51

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?
Image

Coolman1974
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 30.01.2013, 15:04

Postby Coolman1974 » 30.01.2013, 22:44

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

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1731
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 31.01.2013, 08:33

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..
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

Coolman1974
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 30.01.2013, 15:04

TZSQLMonitor result

Postby Coolman1974 » 31.01.2013, 09:00

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
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 30.01.2013, 15:04

I think i've found the issue

Postby Coolman1974 » 01.02.2013, 10:23

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?

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 01.02.2013, 13:01

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?
Image

Coolman1974
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 30.01.2013, 15:04

Postby Coolman1974 » 01.02.2013, 20:00

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.

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1731
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 03.02.2013, 14:43

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
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image


Return to “MS SQL Server”

Who is online

Users browsing this forum: No registered users and 1 guest