Postgres Pro
Downloads
Home   >   mailing lists

Thread: charset property is not working

charset property is not working

From
"Katalina Marcos"
Date:

Hi all!

 

Please help me!

 

I’m using postgresql  7.3.2 (datasource) and jboss…

 

When I try to query a table , I get  this error:

 

Invalid character data was found.  This is most likely caused by stored data containing characters that are invalid f

or the character set the database was created in.  The most common example of this is storing 8bit data in a SQL_ASCII database.

 

That is because in a row I have the character ¥… In the postgresql’s log I get…

 

LOG:  query: select * from t_migracion_historico where lower(tps_migrhist_basedato) = 'repuestos' and lower(ts_migrhist_tablorig) = 't_moneda' and (upper(ts_migrhist_procesado)='N' or ts_migrhist_procesado is null)

LOG:  query: select * from t_migracion_historico;

LOG:  query: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end;

LOG:  query: ÿþs

ERROR:  parser: parse error at or near "ÿþs" at character 1

FATAL:  Socket command type e unknown

 

So, I try to set the connection property in the datasource descriptor:

 

<connection-property name="charSet">UNICODE</connection-property>

 

Because I set client_encoding to UNICODE in postgresql.config.

 

 

But when I try to run the application again, it throws

 

Could not create connection; - nested throwable: (Something unusual has occured to cause the driver to fail. Please report this exception: Exception: java.sql.SQLException:

Stack Trace:

 

java.sql.SQLException:

       at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)

       at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:482)

       at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:461)

       at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJdbc1Connection.java:376)

       at org.postgresql.Driver.connect(Driver.java:122)

       at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:150)

       at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:477)

...

 

Could anybody help to me? What is the right way to do this query?

 

Thanks in advance!

Katalina

Re: charset property is not working

From
Kris Jurka
Date:

On Thu, 13 May 2004, Katalina Marcos wrote:

> I'm using postgresql  7.3.2 (datasource) and jboss

Is this a 7.3.2 server or 7.3.2 jdbc driver or both?

>
> When I try to query a table , I get  this error:
>
> Invalid character data was found.  This is most likely caused by stored
> data containing characters that are invalid for the character set the
> database was created in.  The most common example of this is storing
> 8bit data in a SQL_ASCII database.
>
> LOG:  query: ÿþs
> ERROR:  parser: parse error at or near "ÿþs" at character 1
> FATAL:  Socket command type e unknown

What encoding did you create the database in?  (psql's \l command will
show you.)  If it's not unicode you won't be able to store the full range
of unicode characters correctly in it.

>
> So, I try to set the connection property in the datasource descriptor:
> <connection-property name="charSet">UNICODE</connection-property>

The charSet property is only valid when connecting to 7.2 and earlier
databases.  Starting with the 7.3 series multibyte support was enabled in
the default install, so the client_encoding is always set to unicode by
the driver.


Kris Jurka