none
OLE DB and Sybase and Text Fieldtype errors RRS feed

  • Question

  • Hi all,

     

    I have set up a DAL for connecting to a Sybase db via OLE. All connections work and I can retrieve all data EXCEPT any data in a field of type TEXT.

     

    I get the following message on the exception generated in my vb code:

    - The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value.

     

    I can retrieve data from other fields in the same table without issues as long as their type is not TEXT.

    int, char, varchar etc etc don't pose any problems BUT the TEXT type fields do.

     

    Does anyone know how to resiolve this?

     

    Thasks

    Wednesday, March 5, 2008 4:31 PM

All replies

  • How do you retrieve that text data from the database? How do you read it inside of your code? Try to move column name that of TEXT type to the end of the list of selected columns in your SELECT SQL statement, like

     

    SELECT Column1, Column2,.....ColumnN, MyTextColumnHere FROM .....

     

    With OLEDB providers it is possible that any BLOB data not retrieved if they are in a middle of the list, but you should not get an error.

     

    Thursday, March 6, 2008 10:48 AM
    Moderator
  • Well, that was worth a try and I'll keep the tip in mind BUT, it didn't work.

     

    I have 12 cols in the table and 4 are of TEXT type.

    Reduced the query to retrieve only one of the TEXT cols after a couple other cols as per your message but still get the same error.

     

    Still open to ANY ideas guys!!

     

    Thanks

     

     

    Thursday, March 6, 2008 1:57 PM
  • Could you post part of code that retrieves data?

    Friday, March 7, 2008 10:37 AM
    Moderator
  •  

    Need help.

     

    I am trying to retrieve a TEXT column value from Sybase database table, using a datareader.

    But the datareader is only returning part of the value (the first  32768  chars) .

     

    Any idea?

     

    I tried a dataset also but same issue repeated.

     

    Then I tried the following that I got from MSDNSmile even this is not working.

     

    query.Parameters.Add(methodParameters[0]);

    query.CommandType = System.Data.CommandType.Text;

    query.CommandTimeout = 600000;

    AseDataReader myReader = query.ExecuteReader(CommandBehavior.SequentialAccess);

    while (myReader.Read())

    {

    // The BLOB data is in column two. Must get the first column

    // before the BLOB data.

    //empID = myReader.GetInt32(0); // First column

    // Read the bytes into outbyte[] and retain the number of bytes returned.

    retval = myReader.GetBytes(0, startIndex, outbyte, 0, bufferSize);

    // Continue reading and writing while there are bytes beyond the

    // Size of the buffer.

    while (retval == bufferSize)

    {

    startIndex += bufferSize;

    retval = myReader.GetBytes(0, startIndex, outbyte, 0, bufferSize);

    }

    }

     

    What am I doing wrong??

    Thanks.

     

     

     

    Friday, September 26, 2008 7:54 PM
  • And TEXT column is the last one in a list of columns inside of your SQL statement? I see that commnets specify that BLOB column is the second one, but that does not mean it is the last one.

     

    Monday, September 29, 2008 9:44 AM
    Moderator
  • I'm having the same problem but I'm using Enterprise Database object, stored procedures and custom OO framework, I therefore have the luxury of being able to jump between DataProviders, my results are as follows:

     

    OleDb - if I set the value of the Text Blob to be 198 characters or less long then there is no problem, any longer then blank is returned.

     

    ODBC - handles the Text Blob without any issue for any acceptable length.

     

    I've tested the possibility of the problem being down to invalid characters (rich text) and this is not an issue.

     

    I need to stay using OleDb because of performance issues when creating OO DAL objects where objects have more than 10 properties to populate (number of properties not being an issue), using a DataReader and attempting to return more than 10000 records. I've also used a DataSet and performance remains very slow (slow being having to wait approximately a minute for the objects to be filled and page life cycle to run its course). OleDb does it all in seconds.

     

    I've also tried casting the text blob as a long varchar and varchar to no avail.

     

    I agree with previous posts that putting the text blob to the end of the select statements (and preferably having them placed at the end of the column list when creating a new table- but if this is not possible then select * statements cannot be used) and this should be followed as a standard.

     

    198 characters has no significant meaning in terms of data types, or C# string types.

     

    When I figure it out I'll send you the solution.

    Wednesday, November 12, 2008 2:40 PM