OleDb DataFactory bug. Watch out.

[Bob]

Hi,
Using the OleDb factory.
Asked it to insert 4K records into a SQLAnywhere database table.
Runs out of steam at record 244 and starts writing corrupted versions of
243 primary index column until at 248 it hits a variation it has already
written and fails with a non-unique primary index error.

Use a hand rolled OleDb command and the data is written OK. Likewise the SQL
Factory writes the same data to an SQLServer OK.

It is not a race condition as you can step through the failing iteration and
it still fails.
So...
Stay away from the OleDb Data factory until it is repaired.
Bob

 

[Nicholas Paldino [.NET/C# MVP]]

Bob,

I almost would NEVER use the OleDbFactory class to generate commands for
me. SqlFactory is one thing, since it is tailored specifically for Sql
Server. However, OleDb has to deal with a plethora of different providers
(the same thing applies for the OdbcFactory as well).

Are you writing a general-purpose tool, or are you writing something
more specific?

Hope this helps.

 

[Bob]

Hi Nicholas,
Thanks for your reply.
The app has to sit on top of either an SQlserver db or an SQLAnywhere db.
The idea of generic calls in the Dataclass was obviously appealing.
Narrowed it down in fact not to the factory, but to the DbParameter

When using the factory I was getting a generic DbParameter and setting its
DbType property to string. The corruption occured.

When I was not using the factory I was declaring an OleDbParameter and
setting its OleDbType property to Varchar. The data imported.

I found that if I didn't use the factory and declared an OleDbParameter and
only set its dbType property to String it gave the corruption.

Inspection of the OleDbType property in this situation showed that it had
been set to VarWChar by the Framework (the tooltip shows that VarWChar maps
to System.String)

I then set the OleDbParameter OleDbType to VarWChar and was able to bring
the corruption on.

So exactly how deep the weakness is remains unclear. It may be the
SqlAnywhere drivers.
Time for a cuppa coffee and a rethink.
regards
Bob

Bob,

I almost would NEVER use the OleDbFactory class to generate commands for
me. SqlFactory is one thing, since it is tailored specifically for Sql
Server. However, OleDb has to deal with a plethora of different providers
(the same thing applies for the OdbcFactory as well).

Are you writing a general-purpose tool, or are you writing something
more specific?

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi,
Using the OleDb factory.
Asked it to insert 4K records into a SQLAnywhere database table.
Runs out of steam at record 244 and starts writing corrupted versions of
243 primary index column until at 248 it hits a variation it has already
written and fails with a non-unique primary index error.

Use a hand rolled OleDb command and the data is written OK. Likewise the
SQL
Factory writes the same data to an SQLServer OK.

It is not a race condition as you can step through the failing iteration
and
it still fails.
So...
Stay away from the OleDb Data factory until it is repaired.
Bob

 

[Bob]

Hi Nicholas,
re my previous reply.
Had the cuppa coffee.
decided to test for factory type.(I am using DbProviderFactories)
If System.Data.OleDb.OleDbFactory then I cast the sensitive DbParameter as
an OleDbParameter and set its OleDbType to VarChar.
Otherwise just set the DbType to String.
regards
Bob

Bob,

I almost would NEVER use the OleDbFactory class to generate commands for
me. SqlFactory is one thing, since it is tailored specifically for Sql
Server. However, OleDb has to deal with a plethora of different providers
(the same thing applies for the OdbcFactory as well).

Are you writing a general-purpose tool, or are you writing something
more specific?

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi,
Using the OleDb factory.
Asked it to insert 4K records into a SQLAnywhere database table.
Runs out of steam at record 244 and starts writing corrupted versions of
243 primary index column until at 248 it hits a variation it has already
written and fails with a non-unique primary index error.

Use a hand rolled OleDb command and the data is written OK. Likewise the
SQL
Factory writes the same data to an SQLServer OK.

It is not a race condition as you can step through the failing iteration
and
it still fails.
So...
Stay away from the OleDb Data factory until it is repaired.
Bob

 

[ssamuel]

Bob,

For what it's worth, OLEDB is a ton more finicky than the SQL Server
libraries, largely because of the lower level library implementations.
I was doing some Sybase access a while back with the Intersolv drivers
(the Sybase drivers that come with the .NET framework), and I had a
whole pile of problems. Named parameters didn't work at all, not to
mention that SP calls made you jump through hoops, and multiple
recordset results would hang on NextRecordset() when you got to the
last recordset. Unless you got everything perfect, it would crash in a
sometimes unpredictable way.

I assume you can't use the regular SQL Server access mechanisms for
some reason. If that's not the case, use them and save yourself the
heartache.


Stephan

Hi Nicholas,
re my previous reply.
Had the cuppa coffee.
decided to test for factory type.(I am using DbProviderFactories)
If System.Data.OleDb.OleDbFactory then I cast the sensitive DbParameter as
an OleDbParameter and set its OleDbType to VarChar.
Otherwise just set the DbType to String.
regards
Bob

Bob,

I almost would NEVER use the OleDbFactory class to generate commands for
me. SqlFactory is one thing, since it is tailored specifically for Sql
Server. However, OleDb has to deal with a plethora of different providers
(the same thing applies for the OdbcFactory as well).

Are you writing a general-purpose tool, or are you writing something
more specific?

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi,
Using the OleDb factory.
Asked it to insert 4K records into a SQLAnywhere database table.
Runs out of steam at record 244 and starts writing corrupted versions of
243 primary index column until at 248 it hits a variation it has already
written and fails with a non-unique primary index error.

Use a hand rolled OleDb command and the data is written OK. Likewise the
SQL
Factory writes the same data to an SQLServer OK.

It is not a race condition as you can step through the failing iteration
and
it still fails.
So...
Stay away from the OleDb Data factory until it is repaired.
Bob