function unicode_convert (s)
  return  utils.utf8encode (string.match (s, "^&#(%d+);$"))
end -- unicode_convert 

db = sqlite3.open(GetInfo (66) .. "utf8_test.db")  -- open

db:exec "CREATE TABLE IF NOT EXISTS test (name TEXT NOT NULL);"

hello = string.gsub ("大家好", "&#%d+;", unicode_convert)

print ("original string=", hello)

-- insert a record
db:exec ("INSERT INTO test (name) VALUES ('" .. utils.base64encode (hello) .. "')") 
      
for row in db:nrows ("SELECT * from test") do
  print ("string from database=", row.name)
  print ("string converted back=", utils.base64decode ( row.name ) )
end -- for loop

db:close()  -- close

function unicode_convert (s)
  return  utils.utf8encode (string.match (s, "^&#(%d+);$"))
end -- unicode_convert  

db = sqlite3.open(GetInfo (66) .. "utf8_test.db")  -- open

db:exec "CREATE TABLE IF NOT EXISTS test (name TEXT NOT NULL);"

hello = string.gsub ("大家好", "&#%d+;", unicode_convert)

print ("original string=", hello)

-- insert a record
db:exec ("INSERT INTO test (name) VALUES ('" .. hello .. "')") 
      
for row in db:nrows ("SELECT * from test") do
  print ("string from database=", row.name)
end -- for loop

db:close()  -- close

-- helper function to convert Unicode sequences
function unicode_convert (s)
  return  utils.utf8encode (tonumber (string.match (s, "^&#(%d+);$")))
end -- unicode_convert  


DatabaseOpen ("db", GetInfo (66) .. "utf8_test.db", 6)

DatabaseExec ("db", "CREATE TABLE IF NOT EXISTS test (name TEXT NOT NULL);")

hello = string.gsub ("大家好", "&#%d+;", unicode_convert)

print ("original string=", hello)

-- insert a record
DatabaseExec ("db", "INSERT INTO test (name) VALUES ('" .. utils.base64encode (hello) .. "')") 
      
-- prepare a query
DatabasePrepare ("db", "SELECT * from test")

-- execute to get the first row
rc = DatabaseStep ("db")  -- read first row

-- now loop, displaying each row, and getting the next one
while rc == 100 do
  
  values = DatabaseColumnValues ("db")

  print ("string from database=", values [1])

  print ("string converted back=", utils.base64decode ( values [1]) )

  rc = DatabaseStep ("db")  -- read next row

end -- while loop

-- finished with the statement
DatabaseFinalize ("db")


DatabaseClose ("db")  -- close it

print (utils.base64encode (utils.fromhex ("E5A4A7E5AEB6E5A5BD"))) --> 5aSn5a625aW9

length before add= 9
length after add= 9
name= E5A4A7E5AEB6E5A5BD
length from SQL= 3

length before add= 9
length after add= 8
name= E5A4A7E5AEB6E5A5
length from SQL= 3