SQLite3 [UDBC2] - "Could not coerce arguments" error for UTF8 chars

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite3 [UDBC2] - "Could not coerce arguments" error for UTF8 chars

Pharo Smalltalk Users mailing list
Hello, there is a little mess in SQLite 3 drivers for Pharo, so I am using Pharo 7 and newest UDBC2 sqlite driver from here: http://smalltalkhub.com/#!/~TorstenBergmann/UDBC2

When I executing this insert SQL command:
insert into "TABLE" (..., "VARCHAR_FIELD") values (..., 'ěščřžýáíéúůĚŠČŘŽÝÁÍÉÚŮ');

this UDBC error occurs (what's wrong?):

Error: "Could not coerce arguments"

Stack:
ExternalLibraryFunction(Object)>>error:
ExternalLibraryFunction(Object)>>externalCallFailed
ExternalLibraryFunction(ExternalFunction)>>invokeWithArguments:
UDBCSQLite3Library>>apiPrepare:withSQLText:with:with:with:
UDBCSQLite3Library>>prepare:on:with:
UDBCSQLite3Connection(UDBCSQLite3BaseConnection)>>prepare:
UDBCSQLite3ResultSet>>prepareStatement:
UDBCSQLite3ResultSet>>execute:withCollection:
UDBCSQLite3ResultSet>>execute:with:on:
UDBCSQLite3Connection>>execute:with:
UDBCSQLite3Connection>>execute:
...

Thanks, pf
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 [UDBC2] - "Could not coerce arguments" error for UTF8 chars

Pierce Ng-3
On Tue, Feb 05, 2019 at 02:51:42PM +0000, Petr Fischer via Pharo-users wrote:
> When I executing this insert SQL command:
> insert into "TABLE" (..., "VARCHAR_FIELD") values (..., 'ěščřžýáíéúůĚŠČŘŽÝÁÍÉÚŮ');
> this UDBC error occurs (what's wrong?):
> Error: "Could not coerce arguments"

Please show complete code.

Below code completes without error.

    | db |
    db := UDBCSQLite3Connection openOn: '/tmp/u.db'.
    [   db basicExecute: 'create table x (xv varchar)'.
        db execute: 'insert into x values (?)'
            with: (Array with: 'ěščřžýáíéúůĚŠČŘŽÝÁÍÉÚŮ').
    ] ensure: [ db close ]

Below checks the database outside Pharo.

    % sqlite3 /tmp/u.db
    SQLite version 3.26.0 2018-12-01 12:34:55
    Enter ".help" for usage hints.
    sqlite> .tables
    x
    sqlite> .schema x
    CREATE TABLE x (xv varchar);
    sqlite> .header on
    sqlite> select xv from x;
    xv
    ěščřžýáíéúůĚŠČŘŽÝÁÍÉÚŮ
    sqlite>



Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 [UDBC2] - "Could not coerce arguments" error for UTF8 chars

Pharo Smalltalk Users mailing list
> On Tue, Feb 05, 2019 at 02:51:42PM +0000, Petr Fischer via Pharo-users wrote:
> > When I executing this insert SQL command:
> > insert into "TABLE" (..., "VARCHAR_FIELD") values (..., 'ěščřžýáíéúůĚŠČŘŽÝÁÍÉÚŮ');
> > this UDBC error occurs (what's wrong?):
> > Error: "Could not coerce arguments"
>
> Please show complete code.
>
> Below code completes without error.
>
>     | db |
>     db := UDBCSQLite3Connection openOn: '/tmp/u.db'.
>     [   db basicExecute: 'create table x (xv varchar)'.
>         db execute: 'insert into x values (?)'
>             with: (Array with: 'ěščřžýáíéúůĚŠČŘŽÝÁÍÉÚŮ').
>     ] ensure: [ db close ]

Yes, via. binding a value ala prepared statement, this works. Thanks!

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 ''áěšřčá'')'.

Error: "Could not coerce arguments"

pf

Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 [UDBC2] - "Could not coerce arguments" error for UTF8 chars

Pierce Ng-3
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.



sqlite-widestring.png (135K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQLite3 [UDBC2] - "Could not coerce arguments" error for UTF8 chars

Pharo Smalltalk Users mailing list
> 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