SQLite database query question

[KennyR]

I'll try and make this as simple to read as possible....

I have multiple databases set up to store various bits of user information that they can save and retrieve....(duh, right). For instance, the very first screen that the user sees on my application is a text field that the user can enter a persons name and save that name into one of the databases for later use. When the user wants to retrieve that name at a later time, they click a button called "saved names" and it presents them with an option menu button that then lists the contents of the database. This is nice since it is a item picker "scroller" since I am making the application for IOS. The user can simply scroll the list of saved names and choose the one they want to work with. The whole process works great and names are saved without any problems except one....When a new name is saved and listed in the option menu box, the trailing end of the name has various characters of gibberish i.e. ( &#4e7a) so that it looks like this... John Smith7a#$@ instead of just "John Smith". This is weird, but the strange part is when you enter another name into the database and it is displayed in the option menu, the gibberish is then attached to the newest entry and the first entry looks the way it should i.e. "John Smith". So to clarify, the two entrys looks like this below:

John Smith
Mary Smith#$#@&

Here the snippets of how my tables are created, data inserted and displayed....

Code: Select all

on databaseCreateTables
   ## Add a contact_details table to the database
   put getDatabaseID() into tDatabaseID
   put "CREATE TABLE patientName (name text)" into tSQL
   revExecuteSQL tDatabaseID, tSQL
end databaseCreateTables

Code: Select all

on databaseInsertContactDetails   
   ## Insert names and email addresses into the database
   put getDatabaseID() into tDatabaseID
   
   global vName
   put the label of btn "patientName" into vName
   put "INSERT into patientName VALUES ('" & vName & "');" into tSQL
   answer "Your entry has been saved!"
   
   revExecuteSQL tDatabaseID, tSQL
end databaseInsertContactDetails

Code: Select all

function databaseGetContactDetails
   ## Query the database for contact details to be displayed in the field
   put getDatabaseID() into tDatabaseID
   put "SELECT * from patientName" into tSQL
   put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords
   return tRecords
end databaseGetContactDetails

any ideas on why this is happening? Just FYI, I am using the examples of creating SQLite databases from the lessons section of LiveCode....

[Mark]

Hi,

I don't know what's going on here. Perhaps SQLite returns UTF8 and a few characters have been encoded in a weird way. LiveCode has some difficulties with retrieving binary data, particularly NULLs, which means that you may be unable to convert the data correctly from UTF8 to UTF16, but you can try.

Change

Code: Select all

put "INSERT into patientName VALUES ('" & vName & "');" into tSQL

into

Code: Select all

put "INSERT into patientName VALUES ('" & unidecode(uniEncode(vName),"UTF8") & "');" into tSQL

and change

Code: Select all

return tRecords

into

Code: Select all

return uniDecode(uniEncode(tRecords,"UTF8"))

Note that this may not work but it is worth a try.

Are you sure you need a semicolon at the end of each SQL statement?

Kind regards,

Mark

[KennyR]

Hey Mark...I will give this a try when I get back to the house and report back...I appreciate the help and new direction to take the code....as for the semi colons, I had been following some examples and doing some trial and error with my code to see how it works....To be honest, I do a lot of coding on the fly and learn as I go... Most of my limited experience is with C++.....I will try and take the semi colons out and try the code you provided and see what happens....Thanks Mark!

[KennyR]

Hey Mark,

Just wanted to give you an update on my problem...The changes in my code you suggested did not fix the issue, but I did discover something interesting. Just for the heck of it, I changed the option menu to a drop down box and all the other choices just to see if it was specifically happening when using the option menu and I was right. The extra characters are not being added to my name entries when using the drop down menu or others...just the option menu choice. Kind of stinks because I like the option menu's look and feel on the iPhone. Im going to mess around with the option menu and see if I can make some changes to the switch, or take the switch out of the equation all together...if any of this makes any sense, please feel free to chime in! Thanks again for your help!