On Tue, Feb 05, 2019 at 08:20:22PM +0100, Petr Fischer via Pharo-users wrote:
But still... I do not expect anyone to create tables through binding values (like in your example), so following SQL command is still broken: db execute: 'create table A (ID INTEGER, T TEXT DEFAULT ''áÄÅ¡ÅÄá'')'.
Your original mail was about insert, not create.
Anyhow, I think I see the problem: SQLite's APIs accept UTF8-encoded SQL input strings. UDBCSQLite just uses UFFI's String to char* conversion to pass strings in Pharo to the FFI calls. But 'áÄÅ¡ÅÄá' is a WideString. Judiciously sending #utf8Encoded to the input SQL string seems to do the trick.
| db | db := UDBCSQLite3Connection openOn: '/tmp/u.db'. [ | rs | db execute: 'create table a (k, v varchar default ''ÄÅ¡ÄÅžýáÃéúůÄÅ ÄÅŽÃÃÃÃÃÅ®'')'. db execute: 'insert into a (k) values (NULL)'. rs := db execute: 'select k, v from a'. rs next inspect. ] ensure: [ db close ]
Above snippet evaluates to an inspector on a UDBCSQLite3Row instance with the expected data. See screenshot.
I should be able to fix this in UDBCSQLite over the next two days as it is Chinese New Year long holiday here.
Btw, you mentioned UDBC2. Not speaking for Torsten but I believe UDBC2 is WIP experimental stuff. Better to use UDBCSQLite. Easiest way to load is to install GlorpSQLite via the Catalog Browser. Ignore the warning that the package is not tested for Pharo 7 - that's a Catalog Browser bug.
Ok - I will go with UDBC via GlorpSQLite - thanks for your advice. I am never sure which of the SQLite drivers should be used and CatalogBrowser does not help at all... pf