Hello,
We are attempting to use a .NET app to query data from 4D Text fields (4d-Server, a native Macintosh database). There appears to be a problem with how the ODBC driver or .NET interprets Carriage Returns (CR, Hex D, ascii 13) when retrieving data from a Text field. By adding or subtracting CR's in the Text field you can create or eliminate the failure.
We wrote a Quick Report that dumps the field contents with Control Characters replace by their ASCII values, so you can see CR's in the data examples as <13>. Here is data that can be retreived with the .NET app using the 4D ODBC driver.
Mary Jago had donated her own bone marrow to save the life of someone she didn't know-an act of generosity that led directly to the bitter break-up of her affair with Alistair. <13><13>For him, it was as though her beauty had been plundered. <13>But the man whose life she had saved would change Mary's life in a way she could never have imagined
We turned on the Trace Logging for the datasource and here are the results from the trace log.
Test4DServer4 1ee8-1020 EXIT SQLGetData with return code 1 (SQL_SUCCESS_WITH_INFO) HSTMT 03043F68 UWORD 1 SWORD -8 <SQL_C_WCHAR> PTR 0x03CFF008 [ 674] "Mary Jago had donated her own bone marrow to save the life of someone she didn't know-an act of generosity that led directly to the bitter break-up of her affair with Alistair. \ d\ dFor him, it was as though her beauty had been plundered. \ d\ aBut the man whose life she had saved would change Mary's life in a way she could never have imagine" SQLLEN 4094 SQLLEN * 0x0012F0B4 (674)
DIAG [01004] [Simba][Simba ODBC Driver]Data truncated column 1. (0)
Notice in the results the first set of CR's show the HEX equivalent of \ d\ d while the next CR show \ d\ a which is HEX for CR/LF. When this "hybrid" situation occurs, adding additional CR's to the Text field results in an error attempting to retrieve the data.
Here is the data that fails in the query
Mary Jago had donated her own bone marrow to save the life of someone she didn't know-an act of generosity that led directly to the bitter break-up of her affair with Alistair. <13><13>For him, it was as though her beauty had been plundered. <13><13>But the man whose life she had saved would change Mary's life in a way she could never have imagined
The error message returned by the .NET app is
1/26/2007 1:21:31 PM: Executing Query...EXCEPTION OCCURRED while executing query with DataSet: System.Data.Odbc.OdbcException: ERROR [S1000] [Simba][Simba ODBC Driver][Codebase File Library] at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode) at System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, Int32 cb) at System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i) at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap) at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i) at System.Data.Odbc.OdbcDataReader.GetValues(Object[] values) at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues, Boolean acceptChanges) at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at Test4DServer.Form2.btnExecuteDS_Click(Object sender, EventArgs ev)
The only "Error" found in the Datasource trace log is
Test4DServer4 1f48-1964 EXIT SQLGetData with return code -1 (SQL_ERROR) HSTMT 03695160 UWORD 1 SWORD -8 <SQL_C_WCHAR> PTR 0x03CF1EA8 SQLLEN 4094 SQLLEN * 0x0012F0B4
DIAG [S1000] [Simba][Simba ODBC Driver][Codebase File Library] (122)
With experimenting, we've found it is not directly related to the number or CR's in the Text field. The following text was successfully returned in the query.
test adding more text so that there are enough characters to force a word wrap in the Description field entry screen.<13><13><13><13><13>What if I enter more text here<13><13><13><13><13>
Has anyone experience similar problems and have any suggestions on how to correct this?
Thanks,
Kamran Zafar