Using ODBC to read XL file returns a Blob instead of string

Targets Web
1

Xojo 2023r2 Windows 10 Xojo Web
My web app is reading an XL file using ODBC and then writes each field to a SQLite database. For some reason, one of the fields is written to SQlite as a BLOB instead of a string. When I read the SQlite Blob field into a listbox it has a bunch of foreign looking characters. Is there a way to read and write as a string only? Here is the code.

var sql as string
var rs as rowset

//open the xl file for output
DB1 = NEW ODBCDatabase
db1.DataSource="DSN=Stocklist"

try 
  db1.connect
catch e as DatabaseException
  messagebox(e.message)
  return
end try

Var EstDate as DateTime
sql = "Select * from [Sheet1$]"
rs = db1.SelectSQL(sql)
if rs <> nil then
  if not rs.AfterLastRow then
    session.db.BeginTransaction
    for each row as databaserow in rs
      //if column 0 = "" then skip it
      if rs.columnat(0).StringValue = "" then
        exit For
      else
        Var rows as new DatabaseRow
        rows.column("br").StringValue = rs.columnat(0).StringValue
        rows.column("n_u").StringValue = rs.columnat(1).StringValue
        rows.column("stockno").StringValue = rs.columnat(2).StringValue
        rows.column("class").StringValue = rs.columnat(3).StringValue
        rows.column("attach_to").StringValue = rs.columnat(4).StringValue
        rows.column("make").StringValue = rs.columnat(5).StringValue
        rows.column("model").StringValue = rs.columnat(6).StringValue
        rows.column("my").StringValue = rs.columnat(7).StringValue
        rows.column("modelname").StringValue = rs.columnat(8).StringValue
        rows.column("cat").StringValue = rs.columnat(9).StringValue
        rows.column("subcat").StringValue = rs.columnat(10).StringValue
        rows.column("variant").StringValue = rs.columnat(11).StringValue
        rows.column("usage").StringValue = rs.columnat(12).StringValue
        rows.column("age").StringValue = rs.columnat(13).StringValue
        rows.column("serial").StringValue = rs.columnat(14).StringValue
        rows.column("options").StringValue = rs.columnat(15).StringValue
        rows.column("notes").StringValue = rs.columnat(16).StringValue
        rows.column("cost").StringValue = rs.columnat(17).StringValue
        rows.column("price").StringValue = rs.columnat(18).StringValue
        rows.column("acv").StringValue = rs.columnat(19).StringValue
        rows.column("meter").StringValue = rs.columnat(20).StringValue
        rows.column("custtradedfrom").StringValue = rs.columnat(21).StringValue
        rows.column("salesman").StringValue = rs.columnat(22).StringValue
        rows.column("fdd").StringValue = rs.columnat(23).StringValue
        rows.column("stock_status").StringValue = rs.columnat(24).StringValue
        
        try
          session.db.AddRow("StockList", rows)
        catch e as DatabaseException
          messagebox(e.message)
          session.db.RollbackTransaction
          return
        end try
      end if
    next
  end if
  rs.close
end if
session.db.CommitTransaction
2

Wouldn’t it help to set the string encoding to UTF8?

3

I think the Xojo-Database-Magic does some crazy things here (like nearly everywhere).
Write real sql and put the values in. Sqlite should then safe that as text. If not, you can do something like:
"INSERT INTO sqlitetable (br, ... ) VALUES (CAST(? AS TEXT) ", rs.columnat(0).StringValue

4

I tried setting the encoding to UTF8 using convertencodings(encodings.UTF8)
Is that the correct way?
It did not work. Thanks

5

I will give that a try. Thanks

6

No. Use DefineEncoding instead.

2 Likes
7

Tim, That fixed the “Foreign” characters in the string. I can now read the string value in the listbox. But the string is still being saved as a Blob into the SQLite database. I guess that does not matter as long as I can write the contents as a string into the listbox. Thanks