It only took me 2 weeks to finally fix this one. I am using the ODP.NET driver to access my Oracle database from VB.NET. It seemed like every time I tried a dataAdapter.Update command, it would bomb out with an ArgumentException error with a message of Invalid Parameter Bindings (thanks Oracle). The first week was spent searching and trying to implement a SQL Trace (like SQL Profiler for MSSQL) so I could see what exactly the OracleCommandBuilder was sending off for the command. No luck there, I can't believe how outdated Oracle GUI management tools are. I would have thought at some point they would have written at least some sort of simple GUI management tool but alas, a Web version of iSQL plus and Enterprise Manager are all that is available to us who crave an easier way to manage.
So after giving up on that, I decided to read the documentation that Oracle provided with ODP.NET to see if it would shed some light on the problem. Basically, there were a whole bunch of misleading statements in the documentation that kept me lost for a few days. The perfect example is this code example:
[code lang='sql']// Indicate DataColumn EMPNO is unique
// This is required by the OracleCommandBuilder to update the EMP table
table.Columns["EMPNO"].Unique = true;[/code]
Now why would I have to set a Column to be unique if the schema info should already have that? This ended up getting me nowhere. The next red herring was a whole section on uniqueness to enable the CommandBuilder to update data properly. For those with the documentation, check out /ODP.NET/doc/html/featUnique.htm#sthref438. This is the killer statement, "Note that OracleCommandBuilder cannot update a DataTable created from PL/SQL statements because they do not return any key information in their metadata." This is a bunch of crap, I absolutely got metadata on SQL queries about the table structure.
The Actual Solution
The age old axiom, simplify the problem. I created a new solution to focus on the problem. A simple single TextBox, single Button form with my the code example from the documentation got me started. I ran the update and boom, ArgumentException ex.Message was "Invalid Parameter Binding". Ok, I was on track. I knew at this point that I wasn't going to get Oracle to tell me which parameter was bad but since I had a two column table, I know it couldn't be too hard. The first column in most of my tables is a GUID and the second in this table was a varchar(50) for a word. I had already run into a problem with GUID's in another SQL statement where I was hand-crafting the SQL to do the insert and I fixed it by using the GUID.ToString("N") to get the GUID to it's raw string form successfully (the N formatter removes the dashes from the string).
I decided to make it even simpler and instead of creating my own datarow, I extracted an existing datarow (e.g. dataRow = dataSet.Tables(0).Rows(0)), updated the word value and then ran the update again. This time I was successful in the update. Now I was on to something, I created one datarow and extracted another and compared the item values. Low and behold the GUID was being represented as a byte array in the extracted datacolumn. AH HA! Next step was to recreate my previous scenario and instead of GUID.ToString("N"), I assigned the dataRow("WordID") a GUID.ToByteArray and then tried the update. It worked, now it's time to go back and make sure all of the fields that are GUID based are converted to byte arrays before they are sent to the database.
Lesson Learned
Microsoft SQL Server is much more forgiving when you insert various datatypes, it will magically convert them to the proper format. Oracle is not forgiving, it will only accept the data preformatted to the exact datafields format. While there are quite a few conversion functions in Oracle, it is hard to know when you will need them. I truly feel that ODP.NET should have provided a better exception message such as "Field 'WORDID' of type RAW(16) is invalid". At least I would have known where to start instead of being lost for so long.
I hope this helps someone else, drop me a comment if it does.
Follow Up
I wrote the following method to scan and convert malformed GUID's to be suitable for Oracle. Input is welcome
[code lang='vb']
Private Function ConvertGUIDToByteArray(ByVal dataSet As DataSet) As DataSet
' This is an Oracle Wordaround to analyze each column and convert any string based columns to the proper format
Dim dataColumn As DataColumn
Dim dataRow As DataRow
Dim dataTable As DataTable
For Each dataTable In dataSet.Tables
Dim GUIDColumIndices As New ArrayList
Dim i As Integer
For i = 0 To dataTable.Columns.Count - 1
dataColumn = dataTable.Columns(i)
If dataColumn.DataType.Name = "Byte[]" Then
GUIDColumIndices.Add(i)
End If
Next
If GUIDColumIndices.Count > 0 Then
Dim GUIDColumnIndex As Integer
For Each GUIDColumnIndex In GUIDColumIndices
For Each dataRow In dataTable.Rows
If dataRow.RowState = DataRowState.Modified Then ' Only update modified rows
dataRow(GUIDColumnIndex) = New Guid(CType(dataRow(GUIDColumnIndex), String)).ToByteArray
End If
Next
Next
End If
Next
Return dataSet
End Function
[/code]
No comments:
Post a Comment