[firebird-python] Re: pyfirebirdsql: error with very simple parameterized query

[skoczian]

--- In firebird-python@yahoogroups.com, Philippe Makowski <makowski@...> wrote:
>
> skoczian  [2011-09-10 20:36] :
> >> Done. I hadn't tried this with the former version, but: if I use byte 
> >> sequences instead of strings as parameters, the queries run as expected.
> >>
> >> The character set I'm using doesn't seem to make a difference.
> >>
> > This is wrong. The bytes must be encoded with the connection character set. 
> what are you doing *exactly* ?
> please provide complete test case
> note , by default the connection char set is UTF8
>
Here is a test case. I used the fbase database which is created in tests.py and 
added a new record containing non-ASCII strings:

INSERT INTO foo (a, b, c) VALUES (4, "Ä", "äöüéàô")

Then I run this script:

# test_firebirdsql.py

import firebirdsql

def getConn(db, dbuser, pw, clientcharset=None):
    _dsn = 'localhost/3050:' + db
    if clientcharset is None:
        conn = firebirdsql.connect(dsn=_dsn, user=dbuser, password=pw)
    else:
        conn = firebirdsql.connect(dsn=_dsn, user=dbuser, password=pw, 
charset=clientcharset)
    return conn
    
def testSelect(conn, sql, params=None):
    curs = conn.cursor()
    try:
        if params is None:
            curs.execute(sql)
        else:
            curs.execute(sql, params)
        result = curs.fetchall()
    finally:
        curs.close()
        conn.commit()
    return result

def createParams(strlist):
    paramlist = []
    for s in strlist:
        paramlist.append((s, ))
        paramlist.append((s.encode('latin1'), ))
        paramlist.append((s.encode('utf8'), ))
    return paramlist

def main():
    database = 'fbase'
    dbuser = 'SYSDBA'
    dbpass = 'dbOber'
    select_alpha = "SELECT a, b, c FROM foo WHERE b = ?"
    conn = getConn(database, dbuser, dbpass)
    paramlist = createParams(["X", "Ä"])
    for params in paramlist:
        try:
            print("Aktuelle Parameter:", params)
            ll = testSelect(conn, select_alpha, params)
            print(ll)
        except firebirdsql.OperationalError as pe:
            print(pe)
    print("Done")
    
if __name__ == '__main__':
    main()

I got these results:

Python 3.2.2 (default, Sep  4 2011, 09:07:29) [MSC v.1500 64 bit (AMD64)] on 
Gespenst, Standard
>>> Aktuelle Parameter: ('X',)
[]
Aktuelle Parameter: (b'X',)
[[3, 'X', 'xyz']]
Aktuelle Parameter: (b'X',)
[[3, 'X', 'xyz']]
Aktuelle Parameter: ('Ä',)
[]
Aktuelle Parameter: (b'\xc4',)
Dynamic SQL Error
SQL error code = -303
Malformed string

Aktuelle Parameter: (b'\xc3\x84',)
[[4, 'Ä', 'äöüéàô']]
Done

Greetings
Sibylle