SELECT (string field) returns part of contents,then asterisks

Feb 22, 2011 at 2:26 PM
Hello list,

I'm in the process of writing a little interface tool for notes and
adress databases from an iPad.

Using MS Word VBA and SQLite3_StdCall.dll.
My query looks quite simply put:
SELECT ROWID,creation_date,title FROM Note

and it runs fine and returns w/o problems.

When I iterate through it, though, and I try to read a long note (saved
in field 'title') I happen to get some 2700 characters back plus a
sequence of asterisks.

Am I missing something here?

Thank you for your efforts,

/T

Coordinator
Feb 22, 2011 at 7:42 PM

Hi,

Is the data like that in the database? I wonder if you can check the database with some other SQLite tool - maybe sqliteman  sqliteman.com or SQLite Expert. Maybe the iPad stores long notes in a special way, i.e. the rest of the note is elsewhere and the asterisks denote something...? You can also try something like "SELECT ROWID, SUBSTR(title, 2690) FROM Note" to see what's going on in that long field.

I don't know of any length-related restrictions in the SQLiteForExcel functions.

-Govert

Feb 23, 2011 at 11:44 AM
Hello,

no the data isn't like that.
When I look at it with Firefox add-in sqlite Manager, I can see all of the text in the respective note.
So I guess there must be something in the DLL preventing me from getting the complete contents of the field.
I have not been digging too much into it, but it seemed like the number of asterisks that was delivered by the query was somehow related to the size of the text string, i.e. a longer text string led to more trailing asterisks. Strange, to say the least.

I have now resorted to ODBC, using http://www.ch-werner.de/sqliteodbc/ which works like a charm.

Anyway, thanks for your time and effort,

Thomas
Coordinator
Apr 9, 2011 at 10:21 AM

I see that the asterisks come from the SQLiteForExcel conversion routine Utf8PtrToString, so I think this is a problem in that SQLiteForExcel conversion routine.

But I see no obvious length issue in the code or how I call the API, and I have tested inserting and then retrieving long strings into a SQLite database and it works fine. I wonder if there might be some unexpected embedded characters in the iPad string, or if the encoding might not be UTF8?

So, I can't reproduce this problem yet, but if anyone runs into this again, I'd like to sort it out - please let me know.

-Govert

Coordinator
Jun 25, 2011 at 8:18 PM

I finally got another database which exhibited this problem. The Unicode string conversion bug was fixed in SQLite for Excel version 0.7. 

If anyone runs into the ***s bug since version 0.7, please let me know.

-Govert