Why could "insert (...) values (...)" not insert a new row?

Question

Hi, I have a simple SQL insert statement of the form:

insert into MyTable (...) values (...)

It is used repeatedly to insert rows and usually works as expected. It inserts exactly 1 row to MyTable, which is also the value returned by the Delphi statement AffectedRows:= myInsertADOQuery.ExecSQL.

After some time there was a temporary network connectivity problem. As a result, other threads of the same application perceived EOleExceptions (Connection failure, -2147467259 = unspecified error). Later, the network connection was reestablished, these threads reconnected and were fine.

The thread responsible for executing the insert statement described above, however, did not perceive the connectivity problems (No exceptions) - probably it was simply not executed while the network was down. But after the network connectivity problems myInsertADOQuery.ExecSQL always returned 0 and no rows were inserted to MyTable anymore. After a restart of the application the insert statement worked again as expected.

For SQL Server, is there any defined case where an insert statment like the one above would not insert a row and return 0 as the number of affected rows? Primary key is an autogenerated GUID. There are no unique or check constraints (which should result in an exception anyway rather than not inserting a row).

Are there any known ADO bugs (Provider=SQLOLEDB.1)?

Any other explanations for this behaviour?

Thanks, Nang.

Answer 1

If the values you're trying to insert are violating

• a CHECK constraint
• a FOREIGN KEY relationship
• a NOT NULL constraint
• a UNIQUE constraint

or any other constraints, then the row(s) will not be inserted.

Answer 2

Do you use transactions? Maybe your application has no autocommit? Some drivers do not commit data if there was error in transaction.

Answer 3

If you does not have any exceptions, then:

1. When a table has triggers without SET NOCOUNT ON, then actually the operation (INSERT / UPDATE / DELETE) may be finished successfully, but a number of affected records may be returned as 0.
2. Depending on a transaction activity in current session, other sessions may not see changes made by current session. But current session will see own changes and a number of affected records will be (may be) not 0.

So, the exact answer may depend on your table DDL (+ triggers if any) and on how you are checking the inserted rows.

Answer 4

Looks like your Insert thread lost silently the connection and is not checking on it to do an auto reconnect if needed but keeps queuing the inserts without actually sending them.
I would isolate this code in a small standalone app to debug it and see how it behaves when you voluntarily disconnect the network then reconnect it.
I would not be surprised if you either found a "swallowed" exception, or some code omitting to check for success/failure.
Hope it helps...