none
DataTable Failed to enable constraints

    Question

  • I am extracting data from a table where one field contains XML. The data provider is DB2 which has a field type for XML. So the following SQL statement returns properly formatted XML.

     

    SELECT CONTACT_XML FROM CUSTOMER_CLOB;

    The following code fails on loading the DataTable with the exception shown in the catch portion of the Try statement. So I am wondering if there are any suggestions on how to stop the constraint issue. The good thing is the table has loaded but really do not want to have this constraint issue. Thanks for any thoughts to assist fixing this.

     

       Private Sub DemoLoadTableFailureOnCustomers()
          Using cn As New iDB2Connection("datasource=REVxxx;DefaultCollection=GALLAGHE")
             Dim dt As New DataTable With {.TableName = "Customers"}
             Dim cmd As New IBM.Data.DB2.iSeries.iDB2Command
             Dim Statement As String = "SELECT CONTACT_XML As Info FROM CUSTOMER_CLOB"
             cmd.Connection = cn
    
             cn.Open()
    
             cmd.CommandText = Statement
             Try
                dt.Load(cmd.ExecuteReader())
             Catch ex As Exception
                ' Failed to enable constraints. One or more rows contain values violating 
                ' non-null, unique, or foreign-key constraints.
                Console.WriteLine(ex.Message)
             End Try
    
             If dt.HasRows Then
                MsgBox(dt(0).Field(Of String)("Info"))
             End If
    
          End Using
       End Sub
    

    KSG
    Tuesday, April 06, 2010 4:11 PM

Answers

  • Definitely.  4 won't cut it.  Wow.  You might want to look for a forum or bug submission site for that provider to report the problem.

    You can manually add DataColumns to a DataTable prior to calling DataTable.Load or DataAdapter.Fill, and set the MaxLength property as you see fit.  If you need to use the older version of the DB2 provider, you can use that as a workaround.

    I hope this information proves helpful


    David Sceppa
    Thursday, April 08, 2010 8:14 PM
  • Okay I have maxLength value for the newer version of the IBM provider which is 10,485,760. Older version is 4.

    Now that is a huge difference in both versions.


    KSG
    Thursday, April 08, 2010 3:12 PM

All replies

  • I don't see where you're creating any constraints in the DataTable.  That's pretty wild.  What's in the DataTable's Constraints collection when you catch the exception.  I would have expected there to be any constraints defined based on the code snippet you've provided.
    David Sceppa
    Tuesday, April 06, 2010 9:49 PM
  • As you can see from the code there are imposed constraints. Now here is something I find interesting.

    I have two machines that have VS2008 installed with SP1.
    One machine has an product version 10 and the other version 12 of IBM Data Provider.

    On the machine with version 10 of the provider I get the exception mentioned above.

    Take the following code for version 10 of the provider (which now checks for errors in the DataTable) and the exception type is my XML column field exceeds the MaxLength limit. Under product version 12 it would appear to read the data just fine. To go one step farther I stepped up the anty and retrieved a very large XML CLOB field which was read in with no problem.

    So at this point my thought is my code is fine and I need to see why version 12 of the provider is working fine with a recieving DataTable. I would have thought the issue was with the DataTable and not the IBM provider.

        Private Sub DemoLoadTableFailureOnCustomers()
            Using cn As New iDB2Connection("datasource=Revenue3.dor.local;DefaultCollection=GALLAGHE")
                Dim dt As New DataTable With {.TableName = "Customers"}
                Dim cmd As New IBM.Data.DB2.iSeries.iDB2Command
                Dim Statement As String = "SELECT CONTACT_XML As Info FROM CUSTOMER_CLOB"
                cmd.Connection = cn
    
                cn.Open()
                cmd.CommandText = Statement
    
                'Try
                '    dt.Load(cmd.ExecuteReader())
                'Catch ex As Exception
                '    Console.WriteLine(ex.Message)
                'End Try
    
                dt.Load(cmd.ExecuteReader())
    
                If dt.HasErrors Then
                    Dim rowsInError() As Data.DataRow = dt.GetErrors
                    For i As Integer = 0 To rowsInError.GetUpperBound(0)
                        For Each myCol As DataColumn In dt.Columns
                            Console.WriteLine(String.Format("{0}:{1}", _
                                  myCol.ColumnName, rowsInError(i).GetColumnError(myCol)))
    
                        Next
                    Next
    
                End If
                If dt.HasRows Then
                    Console.WriteLine(dt(0).Field(Of String)("Info"))
                End If
                Console.ReadLine()
            End Using
        End Sub
    

     

     

     


    KSG
    Wednesday, April 07, 2010 2:05 PM
  • I don't see where you're creating any constraints in the DataTable.  That's pretty wild.  What's in the DataTable's Constraints collection when you catch the exception.  I would have expected there to be any constraints defined based on the code snippet you've provided.
    David Sceppa

    Thanks for the thoughts. As per my reply this morning I narrowed down the issue to the XML CLOB field length being the issue and not constraints which as you said (and I think too) is pretty wild. Staying with wild, as per my new info it would appear even stranger.
    KSG
    Wednesday, April 07, 2010 2:08 PM
  • Kevin,

    I agree that's pretty surprising to see a difference in behavior based on the provider.

    I don't have DB2 or that provider installed, but I did run a similar scenario using the SQL Server provider and an ntext column.  I do see that DataTable.MaxLength is set as a result of calling DataTable.Load(DataReader).  What values do you see for MaxLength with the different versions of the DB2 provider?  Also, is there a value in that column in the resultset whose length exceeds that value?


    David Sceppa
    Wednesday, April 07, 2010 5:12 PM
  • Right now I am working from home so both machines are not available and will have to check tomorrow. My guess without knowing the difference in MaxLength that at some point the field value could exceed the max as this is variable length data from the goverment where the data requirements seem to get bigger each year. In the event more folks are interested I will reply back tomorrow in regards to MaxLength differences on the two different versions of the provider.
    KSG
    Wednesday, April 07, 2010 9:19 PM
  • Understood.  I look forward to the update.  On the plus side, it sounds like using the most recent version of the DB2 provider gives you the expected behavior.  There may be a couple other options but I'll wait for your response before trying to suggest possible workarounds.
    David Sceppa
    Wednesday, April 07, 2010 9:51 PM
  • Okay I have maxLength value for the newer version of the IBM provider which is 10,485,760. Older version is 4.

    Now that is a huge difference in both versions.


    KSG
    Thursday, April 08, 2010 3:12 PM
  • Definitely.  4 won't cut it.  Wow.  You might want to look for a forum or bug submission site for that provider to report the problem.

    You can manually add DataColumns to a DataTable prior to calling DataTable.Load or DataAdapter.Fill, and set the MaxLength property as you see fit.  If you need to use the older version of the DB2 provider, you can use that as a workaround.

    I hope this information proves helpful


    David Sceppa
    Thursday, April 08, 2010 8:14 PM
  • Thanks David, I added the columns and set MaxLength, this solved the problem and this time on a larger CLOB field to boot.

     

       Private Sub DemoLoadTableFailureOnSubmissions()
          Using cn As New IBM.Data.DB2.iSeries.iDB2Connection("datasource=Revenue3;DefaultCollection=GALLAGHE")
             Dim dt As New DataTable With {.TableName = "Submissions"}
    
             Dim Col1 As New DataColumn("ELECTRONIC_TRANSMITTER_ID", GetType(System.String)) _
                With {.MaxLength = 10485760}
    
             Dim Col2 As New DataColumn("Info", GetType(System.String)) _
                With {.MaxLength = 10485760}
    
             dt.Columns.AddRange(New DataColumn() _
                                 {Col1, Col2})
    
    
             Dim cmd As New IBM.Data.DB2.iSeries.iDB2Command
             Dim Statement As String = _
             <SQL>
                SELECT 
                   ELECTRONIC_TRANSMITTER_ID, 
                   STATE_RETURN_XML As Info
                FROM 
                   FSE_SUBMISSION 
                FETCH FIRST 3 ROWS ONLY
             </SQL>.Value
    
             cmd.Connection = cn
    
             cn.Open()
    
             cmd.CommandText = Statement
             Try
                dt.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges)
                Console.WriteLine("Done loading")
             Catch ex As Exception
                Console.WriteLine("---------------------------------------")
                Console.WriteLine(ex.Message)
                Console.WriteLine("---------------------------------------")
             End Try
    
             If dt.HasRows Then
                Dim Dump As String = dt(0).Field(Of String)("Info").ToString
                Dim Doc As New XDocument
                Doc = XDocument.Parse(Dump)
                Doc.Save("ViewFSE.xml")
                Console.WriteLine("Rows [{0}]", dt.Rows.Count)
                Console.WriteLine("Data written")
             End If
    
             Console.ReadLine()
          End Using
       End Sub
    

    KSG
    Friday, April 09, 2010 6:29 PM
  • Glad to hear it, Kevin!
    David Sceppa
    Friday, April 09, 2010 7:54 PM