Thread: Charset encoding patch to JDBC driver
I have the necesity of to keep a PostgreSQL database with SQL-ASCII. As the actual version of pgjdbc only have support for Unicode (at least for jdbc3), I have make a patch that allow to configure the desired charset. The available charsets are the indicated in http://www.postgresql.org/docs/8.0/interactive/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED I only have probed this patch from JBoss. This a example of DataSource configuration file for JBoss: ########################################################################### <?xml version="1.0" encoding="UTF-8"?> <!-- ===================================================================== --> <!-- --> <!-- JBoss Server Configuration --> <!-- --> <!-- ===================================================================== --> <!-- $Id: postgres-ds.xml,v 1.1.2.1 2003/09/05 16:38:24 patriot1burke Exp $ --> <!-- ==================================================================== --> <!-- Datasource config for Postgres --> <!-- ==================================================================== --> <datasources> <local-tx-datasource> <jndi-name>clinical</jndi-name> <connection-url>jdbc:postgresql://192.168.1.1:5432/mydatabase</connection-url> <driver-class>org.postgresql.Driver</driver-class> <user-name>myusername</user-name> <connection-property name="charSet">LATIN1</connection-property> <password>mypassword</password> <!-- sql to call when connection is created <new-connection-sql></new-connection-sql> --> <!-- sql to call on an existing pooled connection when it is obtained from pool <check-valid-connection-sql></check-valid-connection-sql> --> </local-tx-datasource> </datasources> ##################################################################################### The property charSet indicates the charset (obvious). I have a patch for every modified file: org.postgresql.core.v3.ConnectionFactoryImpl.java org.postgresql.core.v3.QueryExecutorImpl.java org.postgresql.core.v3.SimpleParameterList.java org.postgresql.core.v3.SimpleQuery.java These are the patches: ################################################################################### Index: ConnectionFactoryImpl.java =================================================================== RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/core/v3/ConnectionFactoryImpl.java,v retrieving revision 1.9 diff -u -r1.9 ConnectionFactoryImpl.java --- ConnectionFactoryImpl.java 11 Jan 2005 08:25:44 -0000 1.9 +++ ConnectionFactoryImpl.java 10 Mar 2005 13:25:44 -0000 @@ -81,10 +81,14 @@ newStream = enableSSL(newStream, requireSSL, info); // Construct and send a startup packet. + String charSet = info.getProperty("charSet"); + if (charSet == null) { + charSet = "UNICODE"; + } String[][] params = { { "user", user }, { "database", database }, - { "client_encoding", "UNICODE" }, + { "client_encoding", charSet }, { "DateStyle", "ISO" } }; @@ -466,9 +470,7 @@ protoConnection.setServerVersion(value); else if (name.equals("client_encoding")) { - if (!value.equals("UNICODE")) - throw new PSQLException(GT.tr("Protocol error. Session setup failed."), PSQLState.CONNECTION_UNABLE_TO_CONNECT); - pgStream.setEncoding(Encoding.getDatabaseEncoding("UNICODE")); + pgStream.setEncoding(Encoding.getDatabaseEncoding(value)); } break; ###################################################################################### Index: QueryExecutorImpl.java =================================================================== RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/core/v3/QueryExecutorImpl.java,v retrieving revision 1.21 diff -u -r1.21 QueryExecutorImpl.java --- QueryExecutorImpl.java 1 Feb 2005 07:27:54 -0000 1.21 +++ QueryExecutorImpl.java 10 Mar 2005 13:28:02 -0000 @@ -47,14 +47,14 @@ // public Query createSimpleQuery(String sql) { - return parseQuery(sql, false); + return parseQuery(sql, false, protoConnection.getEncoding()); } public Query createParameterizedQuery(String sql) { - return parseQuery(sql, true); + return parseQuery(sql, true, protoConnection.getEncoding()); } - private static Query parseQuery(String query, boolean withParameters) { + private static Query parseQuery(String query, boolean withParameters, Encoding encoding) { // Parse query and find parameter placeholders; // also break the query into separate statements. @@ -120,7 +120,7 @@ if (statementList.size() == 1) { // Only one statement. - return new SimpleQuery((String[]) statementList.get(0)); + return new SimpleQuery((String[]) statementList.get(0), encoding); } // Multiple statements. @@ -131,7 +131,7 @@ { String[] fragments = (String[]) statementList.get(i); offsets[i] = offset; - subqueries[i] = new SimpleQuery(fragments); + subqueries[i] = new SimpleQuery(fragments, encoding); offset += fragments.length - 1; } @@ -476,7 +476,7 @@ } public ParameterList createFastpathParameters(int count) { - return new SimpleParameterList(count); + return new SimpleParameterList(count, protoConnection.getEncoding()); } private void sendFastpathCall(int fnid, SimpleParameterList params) throws SQLException, IOException { @@ -696,12 +696,12 @@ { if (i != 0) { - parts[j] = Utils.encodeUTF8("$" + i); + parts[j] = protoConnection.getEncoding().encode("$" + i); encodedSize += parts[j].length; ++j; } - parts[j] = Utils.encodeUTF8(fragments[i]); + parts[j] = protoConnection.getEncoding().encode(fragments[i]); encodedSize += parts[j].length; ++j; } @@ -913,7 +913,7 @@ Driver.debug(" FE=> ClosePortal(" + portalName + ")"); } - byte[] encodedPortalName = (portalName == null ? null : Utils.encodeUTF8(portalName)); + byte[] encodedPortalName = (portalName == null ? null : protoConnection.getEncoding().encode(portalName)); int encodedSize = (encodedPortalName == null ? 0 : encodedPortalName.length); // Total size = 4 (size field) + 1 (close type, 'P') + 1 + N (portal name) @@ -935,7 +935,7 @@ Driver.debug(" FE=> CloseStatement(" + statementName + ")"); } - byte[] encodedStatementName = Utils.encodeUTF8(statementName); + byte[] encodedStatementName = protoConnection.getEncoding().encode(statementName); // Total size = 4 (size field) + 1 (close type, 'S') + N + 1 (statement name) pgStream.SendChar('C'); // Close @@ -1553,7 +1553,7 @@ private final PGStream pgStream; private final boolean allowEncodingChanges; - private final SimpleQuery beginTransactionQuery = new SimpleQuery(new String[] { "BEGIN" }); + private final SimpleQuery beginTransactionQuery = new SimpleQuery(new String[] { "BEGIN" }, null); ; - private final static SimpleQuery EMPTY_QUERY = new SimpleQuery(new String[] { "" }); + private final static SimpleQuery EMPTY_QUERY = new SimpleQuery(new String[] { "" }, null); } ############################################################################################# Index: SimpleParameterList.java =================================================================== RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/core/v3/SimpleParameterList.java,v retrieving revision 1.8 diff -u -r1.8 SimpleParameterList.java --- SimpleParameterList.java 1 Feb 2005 07:27:54 -0000 1.8 +++ SimpleParameterList.java 10 Mar 2005 13:30:24 -0000 @@ -27,10 +27,11 @@ * @author Oliver Jowett (oliver@opencloud.com) */ class SimpleParameterList implements V3ParameterList { - SimpleParameterList(int paramCount) { + SimpleParameterList(int paramCount, Encoding encoding) { this.paramValues = new Object[paramCount]; this.paramTypes = new int[paramCount]; this.encoded = new byte[paramCount][]; + this.encoding = encoding; } private void bind(int index, Object value, int oid) throws SQLException { @@ -156,7 +157,7 @@ return (paramValues[index -1] instanceof StreamWrapper); } - int getV3Length(int index) { + int getV3Length(int index) throws IOException { --index; // Null? @@ -174,8 +175,8 @@ // Already encoded? if (encoded[index] == null) { - // Encode value and compute actual length using UTF-8. - encoded[index] = Utils.encodeUTF8(paramValues[index].toString()); + // Encode value and compute actual length. + encoded[index] = encoding.encode(paramValues[index].toString()); } return encoded[index].length; @@ -204,12 +205,12 @@ // Encoded string. if (encoded[index] == null) - encoded[index] = Utils.encodeUTF8((String)paramValues[index]); + encoded[index] = encoding.encode((String)paramValues[index]); pgStream.Send(encoded[index]); } public ParameterList copy() { - SimpleParameterList newCopy = new SimpleParameterList(paramValues.length); + SimpleParameterList newCopy = new SimpleParameterList(paramValues.length, encoding); System.arraycopy(paramValues, 0, newCopy.paramValues, 0, paramValues.length); System.arraycopy(paramTypes, 0, newCopy.paramTypes, 0, paramTypes.length); return newCopy; @@ -228,6 +229,7 @@ private final Object[] paramValues; private final int[] paramTypes; private final byte[][] encoded; + private Encoding encoding; /** Marker object representing NULL; this distinguishes ###################################################################################################### Index: SimpleQuery.java =================================================================== RCS file: /usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/core/v3/SimpleQuery.java,v retrieving revision 1.8 diff -u -r1.8 SimpleQuery.java --- SimpleQuery.java 1 Feb 2005 07:27:54 -0000 1.8 +++ SimpleQuery.java 10 Mar 2005 13:32:13 -0000 @@ -11,6 +11,8 @@ package org.postgresql.core.v3; import org.postgresql.core.*; + +import java.io.IOException; import java.lang.ref.PhantomReference; /** @@ -22,15 +24,18 @@ * @author Oliver Jowett (oliver@opencloud.com) */ class SimpleQuery implements V3Query { - SimpleQuery(String[] fragments) { + SimpleQuery(String[] fragments, Encoding encoding) { this.fragments = fragments; + if (encoding != null) { + this.encoding = encoding; + } } public ParameterList createParameterList() { if (fragments.length == 1) return NO_PARAMETERS; - return new SimpleParameterList(fragments.length - 1); + return new SimpleParameterList(fragments.length - 1, encoding); } public String toString(ParameterList parameters) { @@ -70,9 +75,9 @@ return fragments; } - void setStatementName(String statementName) { + void setStatementName(String statementName) throws IOException { this.statementName = statementName; - this.encodedStatementName = Utils.encodeUTF8(statementName); + this.encodedStatementName = encoding.encode(statementName); } void setStatementTypes(int[] paramTypes) { @@ -120,8 +125,9 @@ private byte[] encodedStatementName; private PhantomReference cleanupRef; private int[] preparedTypes; + private Encoding encoding = Encoding.defaultEncoding(); - final static SimpleParameterList NO_PARAMETERS = new SimpleParameterList(0); + final static SimpleParameterList NO_PARAMETERS = new SimpleParameterList(0, null); } ################################################################################### Javier Yáñez -- CIBAL Multimedia S.L. Edificio 17, C-10 ParcBIT Camino de Can Manuel s/n 07120 - Palma de Mallorca Spain
Javier Yáñez wrote: > I have the necesity of to keep a PostgreSQL database with SQL-ASCII. As > the actual version of pgjdbc only have support for Unicode (at least for > jdbc3), I have make a patch that allow to configure the desired charset. I'm uncomfortable with applying this sort of patch to the official driver, since it makes the driver more complex just to handle what is arguably a database misconfiguration. It also introduces a new class of error: a mismatch between the driver's configured charSet and the actual database. Comments on the patch itself: - it is missing changes to the v2 protocol path - why does it remove the client_encoding sanity check on connect? - since encoding does not change for the lifetime of the connection, can't you make the encoding a field of QueryExecutoryImpl rather than passing it around everywhere? - it may be better to pass encoding as a parameter to SimpleParameterList methods that need it, rather than storing the (same) value on every list instance. -O
Oliver Jowett wrote:
> I'm uncomfortable with applying this sort of patch to the official
> driver, since it makes the driver more complex just to handle what is
> arguably a database misconfiguration. It also introduces a new class of
> error: a mismatch between the driver's configured charSet and the actual
> database.
I think that this patch is necessary to resolve some problems of the
real life. In my particular case I have to make a j2ee application to
access a existing database. This database is SQL-ASCII encoding, with
the actual version of pgjdbc when the result of a query contains a 8
bits character (very common in Spanish) appears this error:
org.postgresql.util.PSQLException: 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.
Many people has similar problems:
http://www.google.es/search?q=%22Invalid+character+data+was+found%22&hl=es&lr=&start=10&sa=N
http://linux.kieser.net/java_pg_unicode.html
I can not say to my customer that changes the database encoding
because other applications (non-java) could not work or show strange
characters.
By other hand, I do not think that to use SQL-ASCII encoding is a
database misconfiguration. I do not think that storing 8bit data in a
SQL_ASCII database is incorrect. Others applications are using the same
database with ODBC without problem.
> Comments on the patch itself:
>
> - it is missing changes to the v2 protocol path
I have not proven it, but I think that the v2 protocol has the
functionality of choose the encoding.
> - why does it remove the client_encoding sanity check on connect?
my intention was to remove the verification of client_encoding is equals
to UNICODE. I agree with to check the client_encoding.
> - since encoding does not change for the lifetime of the connection,
> can't you make the encoding a field of QueryExecutoryImpl rather than
> passing it around everywhere?
I agree.
> - it may be better to pass encoding as a parameter to
> SimpleParameterList methods that need it, rather than storing the (same)
> value on every list instance.
I agree too. The encoding object only is used in 2 methods.
I'm going to try to improve the patch and post it.
Thank you for your time!
Javier Yáñez
--
CIBAL Multimedia S.L.
Edificio 17, C-10
ParcBIT
Camino de Can Manuel s/n
07120 - Palma de Mallorca
Spain
I have improved the patch:
Index: core/Encoding.java
===================================================================
RCS file:
/usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/core/Encoding.java,v
retrieving revision 1.20
diff -u -r1.20 Encoding.java
--- core/Encoding.java 11 Jan 2005 08:25:43 -0000 1.20
+++ core/Encoding.java 17 Mar 2005 15:38:31 -0000
@@ -29,7 +29,7 @@
/*
* Preferred JVM encodings for backend encodings.
*/
- private static final Hashtable encodings = new Hashtable();
+ public static final Hashtable encodings = new Hashtable();
static {
//Note: this list should match the set of supported server
Index: core/v3/ConnectionFactoryImpl.java
===================================================================
RCS file:
/usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/core/v3/ConnectionFactoryImpl.java,v
retrieving revision 1.9
diff -u -r1.9 ConnectionFactoryImpl.java
--- core/v3/ConnectionFactoryImpl.java 11 Jan 2005 08:25:44 -0000 1.9
+++ core/v3/ConnectionFactoryImpl.java 17 Mar 2005 15:38:31 -0000
@@ -81,10 +81,14 @@
newStream = enableSSL(newStream, requireSSL, info);
// Construct and send a startup packet.
+ String charSet = info.getProperty("charSet");
+ if (charSet == null) {
+ charSet = "UNICODE";
+ }
String[][] params = {
{ "user", user },
{ "database", database },
- { "client_encoding", "UNICODE" },
+ { "client_encoding", charSet },
{ "DateStyle", "ISO" }
};
@@ -466,9 +470,9 @@
protoConnection.setServerVersion(value);
else if (name.equals("client_encoding"))
{
- if (!value.equals("UNICODE"))
- throw new PSQLException(GT.tr("Protocol error.
Session setup failed."), PSQLState.CONNECTION_UNABLE_TO_CONNECT);
-
pgStream.setEncoding(Encoding.getDatabaseEncoding("UNICODE"));
+ if (!Encoding.encodings.containsKey(value))
+ throw new PSQLException(GT.tr("Protocol error.
The charset encoding " + value + " is not supported by the server.
Session setup failed."), PSQLState.CONNECTION_UNABLE_TO_CONNECT);
+
pgStream.setEncoding(Encoding.getDatabaseEncoding(value));
}
break;
Index: core/v3/QueryExecutorImpl.java
===================================================================
RCS file:
/usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/core/v3/QueryExecutorImpl.java,v
retrieving revision 1.21
diff -u -r1.21 QueryExecutorImpl.java
--- core/v3/QueryExecutorImpl.java 1 Feb 2005 07:27:54 -0000 1.21
+++ core/v3/QueryExecutorImpl.java 17 Mar 2005 15:38:32 -0000
@@ -54,10 +54,10 @@
return parseQuery(sql, true);
}
- private static Query parseQuery(String query, boolean withParameters) {
+ private Query parseQuery(String query, boolean withParameters) {
// Parse query and find parameter placeholders;
// also break the query into separate statements.
-
+
ArrayList statementList = new ArrayList();
ArrayList fragmentList = new ArrayList();
@@ -120,7 +120,7 @@
if (statementList.size() == 1)
{
// Only one statement.
- return new SimpleQuery((String[]) statementList.get(0));
+ return new SimpleQuery((String[]) statementList.get(0),
protoConnection.getEncoding());
}
// Multiple statements.
@@ -131,7 +131,7 @@
{
String[] fragments = (String[]) statementList.get(i);
offsets[i] = offset;
- subqueries[i] = new SimpleQuery(fragments);
+ subqueries[i] = new SimpleQuery(fragments,
protoConnection.getEncoding());
offset += fragments.length - 1;
}
@@ -497,7 +497,7 @@
if (params.isNull(i))
encodedSize += 4;
else
- encodedSize += 4 + params.getV3Length(i);
+ encodedSize += 4 + params.getV3Length(i,
protoConnection.getEncoding());
}
@@ -516,8 +516,8 @@
}
else
{
- pgStream.SendInteger4(params.getV3Length(i)); //
Parameter size
- params.writeV3Value(i, pgStream);
+ pgStream.SendInteger4(params.getV3Length(i,
protoConnection.getEncoding())); // Parameter size
+ params.writeV3Value(i, pgStream,
protoConnection.getEncoding());
}
}
pgStream.SendInteger2(1); // Binary result format
@@ -696,12 +696,12 @@
{
if (i != 0)
{
- parts[j] = Utils.encodeUTF8("$" + i);
+ parts[j] = protoConnection.getEncoding().encode("$" + i);
encodedSize += parts[j].length;
++j;
}
- parts[j] = Utils.encodeUTF8(fragments[i]);
+ parts[j] = protoConnection.getEncoding().encode(fragments[i]);
encodedSize += parts[j].length;
++j;
}
@@ -759,7 +759,7 @@
if (params.isNull(i))
encodedSize += 4;
else
- encodedSize += (long)4 + params.getV3Length(i);
+ encodedSize += (long)4 + params.getV3Length(i,
protoConnection.getEncoding());
}
encodedSize = 4
@@ -810,10 +810,10 @@
pgStream.SendInteger4( -1); //
Magic size of -1 means NULL
else
{
- pgStream.SendInteger4(params.getV3Length(i)); //
Parameter size
+ pgStream.SendInteger4(params.getV3Length(i,
protoConnection.getEncoding())); // Parameter size
try
{
- params.writeV3Value(i, pgStream);
// Parameter value
+ params.writeV3Value(i, pgStream,
protoConnection.getEncoding()); // Parameter value
}
catch (PGBindException be)
{
@@ -913,7 +913,7 @@
Driver.debug(" FE=> ClosePortal(" + portalName + ")");
}
- byte[] encodedPortalName = (portalName == null ? null :
Utils.encodeUTF8(portalName));
+ byte[] encodedPortalName = (portalName == null ? null :
protoConnection.getEncoding().encode(portalName));
int encodedSize = (encodedPortalName == null ? 0 :
encodedPortalName.length);
// Total size = 4 (size field) + 1 (close type, 'P') + 1 + N
(portal name)
@@ -935,7 +935,7 @@
Driver.debug(" FE=> CloseStatement(" + statementName + ")");
}
- byte[] encodedStatementName = Utils.encodeUTF8(statementName);
+ byte[] encodedStatementName =
protoConnection.getEncoding().encode(statementName);
// Total size = 4 (size field) + 1 (close type, 'S') + N + 1
(statement name)
pgStream.SendChar('C'); // Close
@@ -1553,7 +1553,7 @@
private final PGStream pgStream;
private final boolean allowEncodingChanges;
- private final SimpleQuery beginTransactionQuery = new
SimpleQuery(new String[] { "BEGIN" });
+ private final SimpleQuery beginTransactionQuery = new
SimpleQuery(new String[] { "BEGIN" }, null);
;
- private final static SimpleQuery EMPTY_QUERY = new SimpleQuery(new
String[] { "" });
+ private final static SimpleQuery EMPTY_QUERY = new SimpleQuery(new
String[] { "" }, null);
}
Index: core/v3/SimpleParameterList.java
===================================================================
RCS file:
/usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/core/v3/SimpleParameterList.java,v
retrieving revision 1.8
diff -u -r1.8 SimpleParameterList.java
--- core/v3/SimpleParameterList.java 1 Feb 2005 07:27:54 -0000 1.8
+++ core/v3/SimpleParameterList.java 17 Mar 2005 15:38:32 -0000
@@ -156,7 +156,7 @@
return (paramValues[index -1] instanceof StreamWrapper);
}
- int getV3Length(int index) {
+ int getV3Length(int index, Encoding encoding) throws IOException {
--index;
// Null?
@@ -174,14 +174,14 @@
// Already encoded?
if (encoded[index] == null)
{
- // Encode value and compute actual length using UTF-8.
- encoded[index] =
Utils.encodeUTF8(paramValues[index].toString());
+ // Encode value and compute actual length.
+ encoded[index] =
encoding.encode(paramValues[index].toString());
}
return encoded[index].length;
}
- void writeV3Value(int index, PGStream pgStream) throws IOException {
+ void writeV3Value(int index, PGStream pgStream, Encoding encoding)
throws IOException {
--index;
// Null?
@@ -204,7 +204,7 @@
// Encoded string.
if (encoded[index] == null)
- encoded[index] = Utils.encodeUTF8((String)paramValues[index]);
+ encoded[index] = encoding.encode((String)paramValues[index]);
pgStream.Send(encoded[index]);
}
@@ -228,7 +228,6 @@
private final Object[] paramValues;
private final int[] paramTypes;
private final byte[][] encoded;
-
/**
* Marker object representing NULL; this distinguishes
* "parameter never set" from "parameter set to null".
Index: core/v3/SimpleQuery.java
===================================================================
RCS file:
/usr/local/cvsroot/pgjdbc/pgjdbc/org/postgresql/core/v3/SimpleQuery.java,v
retrieving revision 1.8
diff -u -r1.8 SimpleQuery.java
--- core/v3/SimpleQuery.java 1 Feb 2005 07:27:54 -0000 1.8
+++ core/v3/SimpleQuery.java 17 Mar 2005 15:38:32 -0000
@@ -11,6 +11,8 @@
package org.postgresql.core.v3;
import org.postgresql.core.*;
+
+import java.io.IOException;
import java.lang.ref.PhantomReference;
/**
@@ -22,8 +24,11 @@
* @author Oliver Jowett (oliver@opencloud.com)
*/
class SimpleQuery implements V3Query {
- SimpleQuery(String[] fragments) {
+ SimpleQuery(String[] fragments, Encoding encoding) {
this.fragments = fragments;
+ if (encoding != null) {
+ this.encoding = encoding;
+ }
}
public ParameterList createParameterList() {
@@ -70,9 +75,9 @@
return fragments;
}
- void setStatementName(String statementName) {
+ void setStatementName(String statementName) throws IOException {
this.statementName = statementName;
- this.encodedStatementName = Utils.encodeUTF8(statementName);
+ this.encodedStatementName = encoding.encode(statementName);
}
void setStatementTypes(int[] paramTypes) {
@@ -120,6 +125,7 @@
private byte[] encodedStatementName;
private PhantomReference cleanupRef;
private int[] preparedTypes;
+ private Encoding encoding = Encoding.defaultEncoding();
final static SimpleParameterList NO_PARAMETERS = new
SimpleParameterList(0);
}
Javier Yáñez
--
CIBAL Multimedia S.L.
Edificio 17, C-10
ParcBIT
Camino de Can Manuel s/n
07120 - Palma de Mallorca
Spain
Javier Yáñez wrote: > I think that this patch is necessary to resolve some problems of the > real life. In my particular case I have to make a j2ee application to > access a existing database. This database is SQL-ASCII encoding, with > the actual version of pgjdbc when the result of a query contains a 8 > bits character (very common in Spanish) appears this error: Indeed. You should be using a LATIN1 or UNICODE database encoding in this case. > I can not say to my customer that changes the database encoding > because other applications (non-java) could not work or show strange > characters. You can change database encoding, then change the default client_encoding for clients that do not set client_encoding themselves. For example, translate your database to UNICODE or LATIN1. Set the default client_encoding to LATIN1. Then JDBC will explicitly set client_encoding=UNICODE, and other clients will get LATIN1 data unless they explicitly change client_encoding. This is how server_encoding / client_encoding are *meant* to work.. once you tell the database how its text data is encoded, clients can choose what format they get the data in and the server does the transcoding work automatically. > By other hand, I do not think that to use SQL-ASCII encoding is a > database misconfiguration. I do not think that storing 8bit data in a > SQL_ASCII database is incorrect. Others applications are using the same > database with ODBC without problem. Try SQL_ASCII + multibyte encoding (UNICODE anyone?) and you're in for a world of hurt.. ODBC just pushes the question of interpretation into application code, as I understand it (it does no encoding translation at all?). The reason that I argue that this is a database misconfiguration is that you are storing text data in the database and expecting the database to interpret it *as text*. If you are using 8-bit characters with SQL_ASCII, the database has to treat it as a bunch of bytes, not as meaningful text. It's not too surprising that the JDBC driver then has problems when it needs to interpret that alleged "text" as individual characters, not bytes. If you want to store a bunch of bytes, use bytea. ResultSet.getBytes() on bytea works just fine regardless of database encoding; ResultSet.getString() on text types works just fine if you only use 7-bit characters, or if you set the database encoding correctly. >> - it is missing changes to the v2 protocol path > > I have not proven it, but I think that the v2 protocol has the > functionality of choose the encoding. From memory this is only used for pre-7.2 servers which may not be compiled with encoding support, so we have to manually supply an encoding to use to interpret text data. Which is actually identical to the SQL_ASCII case: the database doesn't have sufficient information to do the raw data -> UNICODE translation, so the client has to be configured to do it itself. I think requiring this sort of configuration is a step backwards to the poor encoding support of the pre-7.3 era. We really should be encouraging people to move away from SQL_ASCII for anything other than 7-bit ASCII. If there was some sort of simple SQL_ASCII -> UNICODE (or other encoding) database conversion tool, would that be a viable alternative? Then set the default client_encoding appropriately for your existing non-Java clients. Personally I'd like to see one or more of (in rough order of severity): - JDBC driver emits a warning when connecting to a SQL_ASCII DB - Backend refuses to set client_encoding to anything but SQL_ASCII when server_encoding is SQL_ASCII (currently, it silently accepts other encodings, but does no translation -- which is what breaks the JDBC driver, as it's expecting to see UTF-8 when client_encoding=UNICODE) - Backend refuses to accept 8-bit text into a SQL_ASCII database. The last two seem unlikely to happen any time soon.. -O
Is that normal I can't commit a transaction when using refcursor in a stored procedure
From
David Gagnon
Date:
I all,
I just want to know if it' the way it should be .. and if the way I use
refcursor is correct. I have a stored procedure that return a refcursor.
I don't know if it's relevant but this store procedure creates TEMP table:
If I uncomment the commit below I get the exception (see the end of the
mail). If I just close the ResultSet everithing is oki. Is that the way
it should be?
try {
dbCon = ConnectionFactory.getConnection();
dbCon.startTransaction(Connection.TRANSACTION_READ_COMMITTED); //
Because we use a cursor
String sql = dao.getSqlMap().getMappedStatement("generalLedgerAnalysis."
+ WebOsConstants.DB_GET).getSql(null);
dbCon.prepareCall(sql);
CallableStatement cs = (CallableStatement) dbCon.getPreparedStatement();
cs.registerOutParameter(1, Types.OTHER);
cs.setString(2, (String) parameters.get("companyId"));
cs.setString(3, (String) parameters.get("periodIdFrom"));
cs.setString(4, (String) parameters.get("periodIdTo"));
cs.setString(5, (String) parameters.get("accountIdFrom"));
cs.setString(6, (String) parameters.get("accountIdTo"));
ResultSet results = dbCon.executePreparedStatementQueryCursor();
builder.process(results);
// dbCon.commitTransaction();
//
} catch (SQLException e) {
log.error("Problem with the db : " + e.getMessage(), e);
throw new DefectException(e.getMessage(), e);
} finally {
if (dbCon != null)
dbCon.closeAll();
}
Is that the way it should be? If this exception is not OKI do you have
any idea where the problem can be?
Thanks for your help!! It's really appreciated
/David
I put the exception I got below. And a part of my stored procedure.
CREATE OR REPLACE FUNCTION usp_Comptabilite_AnalyseGL(VARCHAR, VARCHAR,
VARCHAR, VARCHAR, VARCHAR) RETURNS refcursor AS '
DECLARE
companyId ALIAS FOR $1;
periodId1 ALIAS FOR $2;
periodId2 ALIAS FOR $3;
accountId1 ALIAS FOR $4;
accountId2 ALIAS FOR $5;
ref refcursor;
statement varchar(4000);
dateSolde DATE;
dateFinPer2 DATE;
BEGIN
EXECUTE ''
CREATE TEMP TABLE T_AUX (
ANUM INT NOT NULL,
ARRNUM VARCHAR(10) NOT NULL,
ADATE DATE,
AGENUM INT,
AGLNUM VARCHAR(10),
ADEBIT numeric(40, 2),
ACREDIT numeric(40, 2),
ANOM VARCHAR(150),
ADESC varchar(100) NULL
) ON COMMIT DROP'';
-- Comptes impliqu‚s
WARN [http8080-Processor4] (DbConnection.java:218) 2005-03-22
13:51:55,359 : DB: Error commiting Transaction: ERROR: relation 6013057
is still open
ERROR [http8080-Processor4] (GeneralLedgerAnalysisDocument.java:91)
2005-03-22 13:51:55,375 : Problem with the db : ERROR: relation 6013057
is still open
org.postgresql.util.PSQLException: ERROR: relation 6013057 is still open
at
org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.execSQL(AbstractJdbc1Connection.java:887)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.setAutoCommit(AbstractJdbc1Connection.java:1272)
at
org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:268)
at
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:293)
at
com.davecorp.webos.connectionFactory.DbConnection.commitTransaction(DbConnection.java:216)
at
com.unik.unikommerce.report.accounting.other.GeneralLedgerAnalysisDocument.parse(GeneralLedgerAnalysisDocument.java:87)
at
org.apache.xml.dtm.ref.DTMManagerDefault.getDTM(DTMManagerDefault.java:495)
at
org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:658)
at
org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:1129)
at
org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:1107)
at
com.davecorp.webos.reportManager.ReportManagerImpl.processReportPdf(ReportManagerImpl.java:278)
at
com.davecorp.webos.reportManager.ReportManager.processReportPdf(ReportManager.java:44)
at com.davecorp.webos.servlet.ReportServlet.service(ReportServlet.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:492)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700)
at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)
ERROR [http8080-Processor4] (ReportManagerImpl.java:288) 2005-03-22
13:51:55,375 : ERROR: relation 6013057 is still open
com.davecorp.webos.util.DefectException: ERROR: relation 6013057 is
still open
Re: Is that normal I can't commit a transaction when using refcursor in a stored procedure
From
Tom Lane
Date:
David Gagnon <dgagnon@siunik.com> writes:
> CREATE TEMP TABLE T_AUX (
> ...
> ) ON COMMIT DROP'';
> WARN [http8080-Processor4] (DbConnection.java:218) 2005-03-22
> 13:51:55,359 : DB: Error commiting Transaction: ERROR: relation 6013057
> is still open
Is this temp table used in your cursor? If so this is a recently-fixed
server bug ("recent" = 7.4.7 and later, I think). It was trying to drop
ON COMMIT DROP tables before closing cursors...
regards, tom lane
Yes and I am running 7.4.5 so I will update for the fix.
Thanks!
/David
Tom Lane wrote:
>David Gagnon <dgagnon@siunik.com> writes:
>
>
>>CREATE TEMP TABLE T_AUX (
>>...
>>) ON COMMIT DROP'';
>>
>>
>
>
>
>>WARN [http8080-Processor4] (DbConnection.java:218) 2005-03-22
>>13:51:55,359 : DB: Error commiting Transaction: ERROR: relation 6013057
>>is still open
>>
>>
>
>Is this temp table used in your cursor? If so this is a recently-fixed
>server bug ("recent" = 7.4.7 and later, I think). It was trying to drop
>ON COMMIT DROP tables before closing cursors...
>
> regards, tom lane
>
>
>