Unicode, SQLite, and Database Library

[sp27]

I am building a front end to a Russian-English dictionary database (SQLite). The data is in Unicode. I've overcome several hurdles of Unicode in LC and have now gotten to the point where my scripts are ready to retrieve the data:

Code: Select all

   put "SELECT RecNum FROM RDTKeys WHERE (RDTKey = :1)" into locSQLString
   put the unicodeText of field "SearchTarget" into locTarget
   --field SearchTarget has a Russian or an English word
   put uniDecode(locTarget, "UTF16") into locSQLParams --now locSQLParams is UTF-8
   
   get revDataFromQuery(,, gDictDBID, locSQLString, "locSQLParams")
   if it is empty then
      answer "Not found"
   else
      answer it
   end if

Table RDTKeys has both Russian and English words in column RDTKey. When the word in field SearchTarget is English, locResult returns the expected results, and everything is fine. When it is a Russian word, the records are never found.

The database is in a local file--this is not an online database.

I can browse this database with a SQLite browser, I can retrieve data from it in other applications by doing similar searches, I know the records are there, and I know the Russian in this db is in Unicode.

What am I doing wrong?

How can I get deeper into the bowels of the Database Library to see what's happening?

Has anyone successfully worked in LC with a Unicode, non-English SQLite database?

Thanks, folks,

Slava

[sp27]

I am posting the solution: my script had a bug, pointed out by Dave C. Thanks, Dave!

To turn the UTF-16 text in the field into a UTF-8 string for use as a search target, I should have used uniDecode(myText, "UTF8"). Now this code works perfectly: a Russian word is found in my SQLite UTF-8 database:

Code: Select all

   --gDictDBID is the database ID returned when the db was opened
   put "SELECT *  FROM RDTKeys WHERE (RDTKey = :1) ORDER BY NormalizedIndexedHeadword" into locSQLString
   put the unicodeText of field "SearchTarget" into locSQLParams
   put uniDecode(locSQLParams, "UTF8") into locSQLParams
   
   get revQueryDatabase(gDictDBID, locSQLString, "locSQLParams") --this returns the ID of the recordset
   put it into locResult
   if locResult is not a number then
      answer "Invalid record set ID returned."
   else
      if revNumberOfRecords(locResult) < 1 then
         answer "No records found"   
      else
         put locResult into gKeysRecordSetID
         put revNumberOfRecords(gKeysRecordSetID) into locRecordCount
         answer "Found " & locRecordCount & " records."
      end if
   end if

I hope this helps someone else. I would have loved coming across a useful post yesterday as I searched this forum.

sp27

[bangkok]

Great trick. Thanks.