SQLCE Insert Bug Found?

[Joe Handelsman]

Maybe this is already a know issue. I insert a lot of data into a SQLCE
database from a remote SQL database using a prepared insert statement,
basically imitating an RDA Pull. It seems to work well, but when I look at
the SQLCE database, the first row of the table has no values for any string
datatype. In other words, all the rows come back fine, except for the first
row returned, where then any string value is empty. Numeric data types are
fine. I've checked all the tables I use this method on and this error
happens everywhere, any string value has an empty value in its first row,
all subsequent rows are fine. I've stepped through the insert statement,
and it seems that the data is being passed OK. Below is the code, please
let me know if this is some kind of error, or if its just me.

Thanks,
Joe

'create table
g_sqlCommand.CommandText = "CREATE TABLE table_unit ("
g_sqlCommand.CommandText = g_sqlCommand.CommandText & "table_id_pk smallint
NOT NULL ,"
g_sqlCommand.CommandText = g_sqlCommand.CommandText & "table_name nvarchar
(12) NOT NULL) "
g_sqlCommand.ExecuteNonQuery()

'creat prepared statement
cmd.Parameters.Clear()
cmd.CommandText = "insert into table_unit (table_id_pk, table_name)
values(?,?);"
cmd.Parameters.Add("p1", SqlDbType.SmallInt)
cmd.Parameters.Add("p2", SqlDbType.NVarChar)
cmd.Prepare()

'do inserts
Me.m_sqlCommand.CommandText = "Select table_id_pk, table_name from
table_unit;"
sqlreader = Me.m_sqlCommand.ExecuteReader
While sqlreader.Read
cmd.Parameters("p1").Value = sqlreader.GetInt16(0)
cmd.Parameters("p2").Value = sqlreader.GetString(1)
cmd.ExecuteNonQuery()
End While

 

[Alok Sancheti]

Even I ran into this when using prepared statements. I programmed around it
that whenever I inserted those rows, I went back and updated the first row.

This has been working fine for me and now in production. Though do not know
if it is my program or SQL CE issue. Any comments from Microsoft will be
appreciated. Should you need the code I will be more than happy to pass
along.

Alok

 

[Joe Handelsman]

Yea, thats exactly what ive been doing also. I doubt its our coding, this
seems more like a bug.

 

[Paul [Paradise Solutions]]

I've found the same thing, but only when I adjusted my code to be how it
is 'supposed to be used'.
Previously, I had misunderstood the use of Prepare() - I'm using
paramerterised queries - and I was calling prepare before each execute
and all was well. When I changed the code to setting the command text,
preparing, and then looping through my parameter value lists I found
that each first execute created blank string values, as you've already
mentioned.

Anyone else care to agre, or correct me?


Thanks


Paul

 

[Federico Punzo]

Pals:
We have found this behaviour some time ago. It is definitely as you describe it.
We worked around it by adding some code in the loop to delete the "bad" record and rerun the same
query only in the "first loop of the loop", or to update the bad record. It's really dirty but it
works ok; however, we also found that in some cases preparing the query does not improve speed.
In cases where there are not many records to be inserted, the time the prepare method takes to
execute seems to actually exceed the performance improvement of the prepared query. So, we only use
this when adding a really significant number of records. However, we didn't perform any serious
benchmarks on this.
Anyone has more info on prepared vs. non-prepared queries performance?

Good luck,
Federico Punzo

 

[Alok Sancheti]

Paul:

So you are saying we need to use Prepare before each Execute. Wouldn't it
decrease the performance of the inserts. Correct me if I understood you
wrong.

 

[Paul [Paradise Solutions]]

Well, only if you don't want to use the 'messy fix'.
I agree that Prepare()ing each exeute has a performance hit (probably),
but it is still faster than using bespoke SQL statements for each insert.

 

[Tomer Verona [MS]]

After adding the string parameter, you should set its size to the maximum
size of the values the parameter is expected to take.

For example:
cmd.Parameters.Add("p2", SqlDbType.NVarChar)
cmd.Parameters("p2").Size = 100
cmd.Prepare()

Let me know if this solves your problem.

Thanks,
Tomer Verona
Microsoft SQL Server CE Team