Hello,
I'm using PG (http://common-lisp.net/project/pg/) to connect to
Postgresql database. In that database I have a table 'task' containing
unicode strings.
When I'm trying to execute sql statement:
(defun execute-sql (sql)
(postgresql:with-pg-connection
(conn "database" "username"
:host "localhost" :password "password")
(postgresql:with-pg-transaction conn
(postgresql:pg-exec conn sql))))
(execute-sql "select * from task limit 10")
I'm receiving the following error message:
debugger invoked on a POSTGRESQL::BACKEND-ERROR in thread #<THREAD
"initial thread" {A6864E1}>:
PostgreSQL backend error: ERROR: could not convert UTF-8 character
0x00e2 to ISO8859-1
I have the following questions:
1. What's going on here and how could I work with unicode strings in
SBCL / PG?
2. Is there any reason for not using PG and using e.g. CLSQL instead?
I tried to install later via asdf-install, but it was missing
almost all .o libraries, and I decided that trying out PG is
faster.
Best regards,
Victor.
From: Christopher Browne
Subject: Re: Problem extracting unicode strings from Postgresql using PG
Date:
Message-ID: <87vel9a3hm.fsf@wolfe.cbbrowne.com>
Victor Kryukov <···················@gmail.com> wrote:
> Hello,
>
> I'm using PG (http://common-lisp.net/project/pg/) to connect to
> Postgresql database. In that database I have a table 'task' containing
> unicode strings.
>
> When I'm trying to execute sql statement:
>
> (defun execute-sql (sql)
> (postgresql:with-pg-connection
> (conn "database" "username"
> :host "localhost" :password "password")
> (postgresql:with-pg-transaction conn
> (postgresql:pg-exec conn sql))))
>
> (execute-sql "select * from task limit 10")
>
> I'm receiving the following error message:
>
> debugger invoked on a POSTGRESQL::BACKEND-ERROR in thread #<THREAD
> "initial thread" {A6864E1}>:
> PostgreSQL backend error: ERROR: could not convert UTF-8 character
> 0x00e2 to ISO8859-1
The trouble here is that you have chosen a database encoding that does
not recognize UTF-8.
You might try doing a createdb that specifies utf8...
$ createlang -E UTF8 mydatabase
--
let name="cbbrowne" and tld="gmail.com" in String.concat ·@" [name;tld];;
http://linuxdatabases.info/info/postgresql.html
Rules of the Evil Overlord #230. "I will not procrastinate regarding
any ritual granting immortality." <http://www.eviloverlord.com/>
Christopher Browne <········@acm.org> writes:
> Victor Kryukov <···················@gmail.com> wrote:
>> I'm receiving the following error message:
>>
>> debugger invoked on a POSTGRESQL::BACKEND-ERROR in thread #<THREAD
>> "initial thread" {A6864E1}>:
>> PostgreSQL backend error: ERROR: could not convert UTF-8 character
>> 0x00e2 to ISO8859-1
>
> The trouble here is that you have chosen a database encoding that does
> not recognize UTF-8.
>
> You might try doing a createdb that specifies utf8...
>
> $ createlang -E UTF8 mydatabase
So you're implying that the problem here is with my Postgresql
settings, not PG? I was concidering this as a posibility as well, but
I doubt this is the case - I can access the same database from python
without any problems, and I have (just) managed to make CLSQL work -
it returns correct results.
After brief exploration, I would probably stick with CLSQL - it seems
that it has more features and is backend-independent, which is handy.
Anyway, thanks Christopher for your suggestion.
Victor Kryukov <···················@gmail.com> writes:
> So you're implying that the problem here is with my Postgresql
> settings, not PG? I was concidering this as a posibility as well, but
> I doubt this is the case - I can access the same database from python
> without any problems, and I have (just) managed to make CLSQL work -
> it returns correct results.
>
> After brief exploration, I would probably stick with CLSQL - it seems
> that it has more features and is backend-independent, which is handy.
>
> Anyway, thanks Christopher for your suggestion.
There are some problems with some versions of SBCL and unicode. I
recommend you to download the source code of the latest version
(currently 0.9.18) and try to compile it by yourself following the
instructions and ensuring that unicode is turned on.
If the problem persist, go SBCL mailing list and submit the bug.
From: Eric Marsden
Subject: Re: Problem extracting unicode strings from Postgresql using PG
Date:
Message-ID: <87irh79ndq.fsf@free.fr>
>>>>> "vk" == Victor Kryukov <···················@gmail.com> writes:
vk> I'm using PG (http://common-lisp.net/project/pg/) to connect to
vk> Postgresql database. In that database I have a table 'task' containing
vk> unicode strings.
vk> debugger invoked on a POSTGRESQL::BACKEND-ERROR in thread #<THREAD
vk> "initial thread" {A6864E1}>:
vk> PostgreSQL backend error: ERROR: could not convert UTF-8 character
vk> 0x00e2 to ISO8859-1
you should bind *PG-CLIENT-ENCODING* to "UTF8" if your database
is encoded in UTF-8. However, this error message suggests that you
have some iso-8859-1 data in your table, since 0x00e2 is
#\LATIN_SMALL_LETTER_A_WITH_CIRCUMFLEX.
vk> 2. Is there any reason for not using PG and using e.g. CLSQL instead?
vk> I tried to install later via asdf-install, but it was missing
vk> almost all .o libraries, and I decided that trying out PG is
vk> faster.
CLSQL is much more sophisticated than PG, but also more difficult to
understand / debug. CLSQL works with several databases, whereas PG only
with PostgreSQL, but I believe that CLSQL does not support certain
PostgreSQL features such as prepared statements
(prepare/bind/execute).
--
Eric Marsden