PRB: Empty Strings Causes ADO Update Error Against Oracle Servers (245482)

MORE INFORMATION

This problem is seen due to the difference in behavior between the Oracle Storage Engine and ADO with respect to empty strings. Oracle is interpreting a zero length buffer as NULL. This problem can be reproduced with the following piece of code and can be replicated against any Oracle server irrespective of its version.

Execute the following ADO code against an Oracle server:

   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset

   Set cnn = New ADODB.Connection
   cnn.Open "Provider=MSDAORA;Data Source=myoracleserver;User ID=myuserid;Password=mypassword"
   cnn.Execute "CREATE TABLE mytable (a int primary key, b varchar2(5) null)"
   cnn.Execute "INSERT INTO mytable VALUES (5, 'value')"

   Set rst = New ADODB.Recordset
   rst.CursorLocation = adUseClient
   rst.Open "SELECT * FROM mytable", cnn, adOpenStatic, adLockOptimistic
   rst.MoveFirst
   rst(1) = ""
   rst.Update
   rst(1) = "new"
   rst.Update  ' Error occurs here

   rst.Close
   cnn.Execute "DROP TABLE mytable"
   cnn.Close

   Set rst = Nothing
   Set cnn = Nothing
				

The error is seen at the second update statement in the code. This is because the Oracle Engine converts the empty string to NULL. The ADO Recordset stores the empty string as a zero length buffer.