When I retrieve a text field (255 char) from an Access Database, the data is always truncated at about 130 characters.
When I retrieve a text field (255 char) from an Access Database, the data is truncated at about 130 characters.
I am using the Windows 7 OS, Access 2007 and Xojo 2013r2. I connect to the Access database using the ODBC connection string. I can open the database successfully using the connection string with or without DSN. The problem remains the same either way. I tried my application using Real Studio 2012r2 and got the same result.
I am able to use Xojo (as well as Real Studio) to insert records into the text fields in the Access table, and they are NOT truncated. However, when I read them using a RecordSet they are always truncated at around 130 characters.
Is there a limit in Xojo concerning the length of text strings returned from an ODBC connection? I tried increasing the buffer size in the connection string and got the same result.
Is there a limit in Access concerning sending data to an external application that I am unaware of?
Is there some buffer size in the Windows 7 OS that I am unaware of?
Trying to help rule things out - I can retrieve pages of information for a single field with the ODBC plugin with non Access databases. I don’t have access to Access to test. (tongue twister)
I have never attempted to get data from a non-Access database using the ODBC plugin. I know that the Oracle plugin works great with long Varchar(2) fields, while Access does the insert correctly but not the read. I just wish I could find out whether it’s Access, the ODBC plugin, or the Win 7 OS that’s causing the problem. Thank you for responding.
Thanks for bringing this to the forums. I followed your steps and can confirm that this is what is happening. I created a field in Microsoft Access (mdb) and added the following text to the field:
Hello, this bit of text has exactly 254 characters. How do we know this? I found a program online which counts all the characters in a paragraph made up of sentences and counts each one. As long as the program is correct, then this is 254 characters. ok?
Then, I used the following code in a Xojo program:
[code] //Getting Information from the Database
Dim db as ODBCDatabase
Dim rs as RecordSet
if db.Connect() then //Was the connection successful?
'MsgBox “Connected”
else
MsgBox "Connection failed: " + db.ErrorMessage
end if
rs = db.SQLSelect(“SELECT * FROM Widgets”)
if rs <> Nil then
LblText.Text = rs.Field(“254Chars”)
else
MsgBox “No RecordSet exists.”
end if
rs.Close
db.Close[/code]
The result was the following in a label:
Hello, this bit of text has exactly 254 characters. How do we know this? I found a program online which
counts all the
characte
It looks like Xojo is cutting off after exactly 128 characters - including a carriage return. If a variable in the plugin from Xojo is limited to 128 characters then this would happen.
This looks like a bug. Please report it with the feedback program and include an example.
I will have to figure out how to use the feedback program, and then I will report it – unless, of course, I can save time by convincing you to use the feedback program to send in the example you have already created.
I would like to concentrate on another problem I found using the ODBC plugin with Access. It involves creating queries with a varying number of “and columname = ‘value’” statements depending upon what the user selects on the screen. I did not bring this one to the attention of the forum because the query results are always short enough that I can use a full table scan and store the entire recordset in a doubly dimensioned array, and then do my own filtering depending upon what the user entered on the screen. That’s a clumsy way to do it, but it works whereas the SQLSelect does not.
[quote=28811:@Eugene Dakin] LblText.Text = rs.Field(“254Chars”)
[/quote]
Dont do this this way
This could contribute to the problem as it has to convert a variant to a string.
You should alwats define the encoding & explicitly ask for the specific value (string, double, etc)
LblText.Text = DefineEncoding(rs.Field(“254Chars”).StringValue, Encodings.ASCII) // or some suitable encoding
Since all of my database columns are text, I tried adding the “Encodings.ASCII” to the code snippet shown below:
Rst = App.DB.SQLSelect(sQy) // read ODBC database
iRows = -1
If Rst <> Nil Then
Rst.MoveFirst
While NOT Rst.EOF
iRows = iRows + 1
For i = 0 To iCols
RS(iRows,i) = DefineEncoding(Rst.Idxfield(i + 1).StringValue, Encodings.ASCII)
Next i
Rst.MoveNext
Wend
Rst.Close
End If
No Change. It still truncates two long text fields and everything else is fine.
I know nothing about encodings. Maybe I’m not doing it correctly. Any ideas?
Although a TEXT field should be able to send out the full 255 characters, it doesn’t seem to work. I tried the same modification of the example program and also had the same answer you did - no change. I tried all of the common Windows/Access encodings (WindowsANSI, ASCII, UTF8 through UTF32) and had some interesting ( ) results, and not the one we were wanting.
Workaround: The good news is that if the Data Type in Microsoft Access were changed to a Memo Data Type, then the full 255 characters is shown through a Xojo program (Backup your database before trying this).
Norman, do you have any other possible helpful suggestions we could try for a Text Field?
I did not use the Memo field in Access because I have to be able to “Select” using “like.” The last time I studied Access this was not permitted on a Memo field. I have considered putting a separate table in Access with a one-to-one relationship on the primary key. It would contain only 2 fields which would be the 2nd half of the two long fields in the actual table. This would be one heck of a lot of added code and complexity that hopefully won’t be necessary if the SQLSelect can be made to work right.
I changed both long text fields to memo fields and IT WORKED!!
What I was not expecting was that both of the following worked (with and without the LEFT):
sQy = “SELECT Title, FirstLine FROM Anthems WHERE Left(FirstLine,255) like ‘%He is a great King%’”
Rst = App.DB.SQLSelect(sQy)
sQy = “SELECT Title, FirstLine From Anthems WHERE FirstLine like ‘%He is a great King%’”
Rst = App.DB.SQLSelect(sQy)
I believe that my immediate problem is solved now. Sincere thanks to everyone who helped me!!!