Thread: UTF encoding error

UTF encoding error

From

Felipe Schnack

Date:

04 December 2002, 08:14:10

  I'm getting this error, I'm pretty sure it's a driver error, but can't
imagine why... I'm just fetching string (in br-pt language)

java.lang.ArrayIndexOutOfBoundsException: 15
    at org.postgresql.core.Encoding.decodeUTF8(Encoding.java:254)
    at org.postgresql.core.Encoding.decode(Encoding.java:165)
    at org.postgresql.core.Encoding.decode(Encoding.java:181)
    at
org.postgresql.jdbc1.AbstractJdbc1ResultSet.getString(AbstractJdbc1ResultSet.java:97)
    at
org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java)
    at com.w2.infra.db.CtlDatabase.getField(CtlDatabase.java:60)
    at com.w2.infra.mvc.CtlModel.importResultRow(CtlModel.java:48)
    at com.w2.infra.db.DbVector.getLinkedList(DbVector.java:310)
    at com.w2.infra.db.CachedVector.<init>(CachedVector.java:48)
    at com.w2.infra.tags.CtlInvokerTag.doEnd(CtlInvokerTag.java:87)
    at com.w2.infra.tags.BodyTagLib.doEndTag(BodyTagLib.java:86)
    at
org.apache.jsp.questionariocopia_jsp._jspx_meth_w2_invoker_0(questionariocopia_jsp.java:158)
    at
org.apache.jsp.questionariocopia_jsp._jspService(questionariocopia_jsp.java:64)
    at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java)
    at org.apache.jasper.servlet.JspServletWrapper.service(Unknown Source)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown Source)
    at org.apache.jasper.servlet.JspServlet.service(Unknown Source)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java)
    at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unknown
Source)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown
Source)
    at
com.w2.infra.servlets.ErrorPageFilter.doFilter(ErrorPageFilter.java:25)
    at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unknown
Source)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown
Source)
    at org.apache.catalina.core.StandardWrapperValve.invoke(Unknown Source)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
    at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
    at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
    at org.apache.catalina.core.StandardContextValve.invoke(Unknown Source)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
    at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
    at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
    at org.apache.catalina.core.StandardContext.invoke(Unknown Source)
    at org.apache.catalina.core.StandardHostValve.invoke(Unknown Source)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
    at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Unknown
Source)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
    at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown Source)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
    at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
    at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
    at org.apache.catalina.core.StandardEngineValve.invoke(Unknown Source)
    at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
    at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
    at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
    at
org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
    at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405)
    at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:380)
    at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508)
    at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:533)
    at java.lang.Thread.run(Thread.java:536)


--

Felipe Schnack
Analista de Sistemas
felipes@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893

Faculdade Ritter dos Reis
www.ritterdosreis.br
felipes@ritterdosreis.br
Fone/Fax.: (51)32303328

Re: UTF encoding error

From

Fernando Nasser

Date:

04 December 2002, 12:59:48

Oi Felipe,

The code in the driver is straight forward so the only way you can get one of
those is that the string sent by the backend is encoded wrong.  (I guess we will
have to catch this error and give a better message anyway).

As of 7.3, the backend does the conversion between your encoding (databse
encoding) and UNICODE (client encoding set with "set client_encoding =
'UNICODE';") and sends everything as UTF8 to the driver.  If the stream of bytes
send has something wrong and says that there is a 3 byte sequence and only two
are there we could get this exception.  But this means that the conversion was
made wrong in the backend.

Have you initialized your cluster with -E  ?  Isn't it possible that you did not
tell the backend that it is supposed to work with Brazilian Portuguese and
loaded some encoded data there using some binary form?

Cheers,
Fernando



Felipe Schnack wrote:>   I'm getting this error, I'm pretty sure it's a driver
error, but can't
> imagine why... I'm just fetching string (in br-pt language)
>
> java.lang.ArrayIndexOutOfBoundsException: 15
>     at org.postgresql.core.Encoding.decodeUTF8(Encoding.java:254)
>     at org.postgresql.core.Encoding.decode(Encoding.java:165)
>     at org.postgresql.core.Encoding.decode(Encoding.java:181)
>     at
> org.postgresql.jdbc1.AbstractJdbc1ResultSet.getString(AbstractJdbc1ResultSet.java:97)
>     at
> org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java)
>     at com.w2.infra.db.CtlDatabase.getField(CtlDatabase.java:60)
>     at com.w2.infra.mvc.CtlModel.importResultRow(CtlModel.java:48)
>     at com.w2.infra.db.DbVector.getLinkedList(DbVector.java:310)
>     at com.w2.infra.db.CachedVector.<init>(CachedVector.java:48)
>     at com.w2.infra.tags.CtlInvokerTag.doEnd(CtlInvokerTag.java:87)
>     at com.w2.infra.tags.BodyTagLib.doEndTag(BodyTagLib.java:86)
>     at
> org.apache.jsp.questionariocopia_jsp._jspx_meth_w2_invoker_0(questionariocopia_jsp.java:158)
>     at
> org.apache.jsp.questionariocopia_jsp._jspService(questionariocopia_jsp.java:64)
>     at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source)
>     at javax.servlet.http.HttpServlet.service(HttpServlet.java)
>     at org.apache.jasper.servlet.JspServletWrapper.service(Unknown Source)
>     at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown Source)
>     at org.apache.jasper.servlet.JspServlet.service(Unknown Source)
>     at javax.servlet.http.HttpServlet.service(HttpServlet.java)
>     at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unknown
> Source)
>     at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown
> Source)
>     at
> com.w2.infra.servlets.ErrorPageFilter.doFilter(ErrorPageFilter.java:25)
>     at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unknown
> Source)
>     at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown
> Source)
>     at org.apache.catalina.core.StandardWrapperValve.invoke(Unknown Source)
>     at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
>     at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
>     at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
>     at org.apache.catalina.core.StandardContextValve.invoke(Unknown Source)
>     at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
>     at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
>     at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
>     at org.apache.catalina.core.StandardContext.invoke(Unknown Source)
>     at org.apache.catalina.core.StandardHostValve.invoke(Unknown Source)
>     at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
>     at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Unknown
> Source)
>     at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
>     at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown Source)
>     at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
>     at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
>     at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
>     at org.apache.catalina.core.StandardEngineValve.invoke(Unknown Source)
>     at
> org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
>     at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
>     at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
>     at
> org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
>     at
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405)
>     at
> org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:380)
>     at
> org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508)
>     at
> org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:533)
>     at java.lang.Thread.run(Thread.java:536)
>
>



--
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Re: UTF encoding error

From

Felipe Schnack

Date:

04 December 2002, 13:11:36

  I was with serious problems with brazilian portuguese... and I have no
idea how to tell the postmaster to use pt-br... but now I added
"&charSet=LATIN1" to my jdbc URLS.. what else I could do?

On Wed, 2002-12-04 at 15:53, Fernando Nasser wrote:
> Oi Felipe,
>
> The code in the driver is straight forward so the only way you can get one of
> those is that the string sent by the backend is encoded wrong.  (I guess we will
> have to catch this error and give a better message anyway).
>
> As of 7.3, the backend does the conversion between your encoding (databse
> encoding) and UNICODE (client encoding set with "set client_encoding =
> 'UNICODE';") and sends everything as UTF8 to the driver.  If the stream of bytes
> send has something wrong and says that there is a 3 byte sequence and only two
> are there we could get this exception.  But this means that the conversion was
> made wrong in the backend.
>
> Have you initialized your cluster with -E  ?  Isn't it possible that you did not
> tell the backend that it is supposed to work with Brazilian Portuguese and
> loaded some encoded data there using some binary form?
>
> Cheers,
> Fernando
>
>
>
> Felipe Schnack wrote:>   I'm getting this error, I'm pretty sure it's a driver
> error, but can't
> > imagine why... I'm just fetching string (in br-pt language)
> >
> > java.lang.ArrayIndexOutOfBoundsException: 15
> >     at org.postgresql.core.Encoding.decodeUTF8(Encoding.java:254)
> >     at org.postgresql.core.Encoding.decode(Encoding.java:165)
> >     at org.postgresql.core.Encoding.decode(Encoding.java:181)
> >     at
> > org.postgresql.jdbc1.AbstractJdbc1ResultSet.getString(AbstractJdbc1ResultSet.java:97)
> >     at
> > org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java)
> >     at com.w2.infra.db.CtlDatabase.getField(CtlDatabase.java:60)
> >     at com.w2.infra.mvc.CtlModel.importResultRow(CtlModel.java:48)
> >     at com.w2.infra.db.DbVector.getLinkedList(DbVector.java:310)
> >     at com.w2.infra.db.CachedVector.<init>(CachedVector.java:48)
> >     at com.w2.infra.tags.CtlInvokerTag.doEnd(CtlInvokerTag.java:87)
> >     at com.w2.infra.tags.BodyTagLib.doEndTag(BodyTagLib.java:86)
> >     at
> > org.apache.jsp.questionariocopia_jsp._jspx_meth_w2_invoker_0(questionariocopia_jsp.java:158)
> >     at
> > org.apache.jsp.questionariocopia_jsp._jspService(questionariocopia_jsp.java:64)
> >     at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source)
> >     at javax.servlet.http.HttpServlet.service(HttpServlet.java)
> >     at org.apache.jasper.servlet.JspServletWrapper.service(Unknown Source)
> >     at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown Source)
> >     at org.apache.jasper.servlet.JspServlet.service(Unknown Source)
> >     at javax.servlet.http.HttpServlet.service(HttpServlet.java)
> >     at
> > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unknown
> > Source)
> >     at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown
> > Source)
> >     at
> > com.w2.infra.servlets.ErrorPageFilter.doFilter(ErrorPageFilter.java:25)
> >     at
> > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unknown
> > Source)
> >     at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown
> > Source)
> >     at org.apache.catalina.core.StandardWrapperValve.invoke(Unknown Source)
> >     at
> > org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
> >     at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
> >     at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> >     at org.apache.catalina.core.StandardContextValve.invoke(Unknown Source)
> >     at
> > org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
> >     at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
> >     at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> >     at org.apache.catalina.core.StandardContext.invoke(Unknown Source)
> >     at org.apache.catalina.core.StandardHostValve.invoke(Unknown Source)
> >     at
> > org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
> >     at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Unknown
> > Source)
> >     at
> > org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
> >     at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown Source)
> >     at
> > org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
> >     at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
> >     at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> >     at org.apache.catalina.core.StandardEngineValve.invoke(Unknown Source)
> >     at
> > org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(Unknown Source)
> >     at org.apache.catalina.core.StandardPipeline.invoke(Unknown Source)
> >     at org.apache.catalina.core.ContainerBase.invoke(Unknown Source)
> >     at
> > org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
> >     at
> > org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405)
> >     at
> > org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:380)
> >     at
> > org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508)
> >     at
> > org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:533)
> >     at java.lang.Thread.run(Thread.java:536)
> >
> >
>
>
>
> --
> Fernando Nasser
> Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
> 2323 Yonge Street, Suite #300
> Toronto, Ontario   M4P 2C9
>
--

Felipe Schnack
Analista de Sistemas
felipes@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893

Faculdade Ritter dos Reis
www.ritterdosreis.br
felipes@ritterdosreis.br
Fone/Fax.: (51)32303328

JDBC driver - first impressions and comments

From

"j.random.programmer"

Date:

04 December 2002, 13:24:55

Folks:

Having used mysql for some time, I decided to try
postgres 7.3. I thought I'd post some initial jdbc
specific comments and initial impressions. Some of the
issues that I mention may be known and/or fixed in
development versions of the driver; therefore please
treat this post as a "newbie's" point of view.

Let me start by saying that I am writing a java based
db object/abstraction layer that can examine any
database and generate various java objects an
interface to those objects. Automatic validation,
application caching etc., will also be provided. For
this code to work, I need to get a lot of meta-data
about the database, tables and fields.

**NOTE**
For purposes of discussion, "md" in the following
examples will refer to reference to a
DatabaseMetaData object.

I feel there is a definite difference between the
mysql JDBC driver and the postgres one. The postgres
driver is the lastest compiled driver for JDK 1.4
against postgres 7.3

1) postgres' JDBC does not *seem* to have a "owner" or
a person finally responsible for the driver. (at least
I haven't found this going thru the archives). This
simply may be how jdbc development is done by many
people but ultimately someone has to be responsible
for it. That somone then should be mentioned as the
contact person for all JDBC development requests and
bug reports.

2) The jdbc compliance test results page has not been
updated for quite some time. It's not clear how
compliant the latest drivers are and if they are ready
for serious production use.

3) I am getting exceptions when I call the following
methods:

md.supportsSavepoints()
md.supportsGetGeneratedKeys()

4) The md.getColumnInfo() returns "int2" and "int4"
for the TYPE_NAME field in the result set
(corresponding to columns defined as smallint and
integer). These should be "smallint" and "integer"
instead.

5) The md.getColumnInfo() returns a result set in
which the columns are sorted by column *name* and not
by the ORDINAL_POSITION field. This is opposite of the
JDBC spec which requires that columns be sorted by the
ORDINAL_POSITION field. If I create a table such as:

create table foo ( zoo int, abc int, baba timestamp);

I should get my columns in the following order: [zoo,
abc, baba] but getColumnInfo() returns them as: [abc,
baba, zoo]

Note, getColumnInfo() does return the correct
ORDINAL_POSITION data each column but sorts them by
alpha - which is wrong. Moreover, all returned
columns are in all-lowercase [this may be the correct
behavior - I don't know, but if I create a column
fooBAR, JDBC returns that as foobar].

5) The md.getImportedKeys() and md.getExportedKeys()
methods take 30-50 seconds to return. There is
something very strange going on (it's not the
connection itself, I am on a private LAN and other
jdbc methods return information immediately).

6) The md.getTypeInfo() method returns every single
tablename, sequence etc. as a type. This method should
only return types like "integer", "timestamp" etc, NOT
the name of every single table !

7) There is no way to change the catalog of the JDBC
connection. What I want is:

myconnection.setCatalog("template1")
get a connection to the "template1" database
then say: myconnection.setCatalog("foo")
get a connection to the "foo" database.

This does not seem to be possible with the postgres
driver. Why does the driver not treat a postgres
database as a "catalog" or least pretend to ? The
current state of affairs is a serious deficiency.

8) It would be nice if the driver supported an auto
reconnect feature. There is no documentation or readme
file that comes with the compiled driver that talks
about connection timeautos, auto-reconnecting
connections etc.

9) The MD5 documentation is missing and the JDBC
driver does not work if passwords are used on the
server. (if we choose password authentication, then
the latest 7.3 postgres release defaults to MD5 when I
compile it out of the box). Currently, I've been
unable to connect using "password" but can only
connect using "trust".

Best regards,

--j


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: UTF encoding error

From

Fernando Nasser

Date:

04 December 2002, 14:27:33

Felipe Schnack wrote:>   I was with serious problems with brazilian
portuguese... and I have no
> idea how to tell the postmaster to use pt-br... but now I added
> "&charSet=LATIN1" to my jdbc URLS.. what else I could do?
>

That is a no-op for backends 7.3 on.  What version of PostgreSQL are you using?

It is not the postmaster you must tell which encoding to use.  You must have
specified the encoding when you did the "createdb", with the "-E" option.

Or specify the ENCODING clause when using the SQL "CREATE DATABASE" command.

Abracos,
Fernando

--
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Re: UTF encoding error

From

Felipe Schnack

Date:

04 December 2002, 14:36:40

  No I didn't... maybe I should recreate my database? Specify charset in
jdbc connection will help in something?
  Hey, you're brazilian and work at redhat or something?

On Wed, 2002-12-04 at 17:21, Fernando Nasser wrote:
> Felipe Schnack wrote:>   I was with serious problems with brazilian
> portuguese... and I have no
> > idea how to tell the postmaster to use pt-br... but now I added
> > "&charSet=LATIN1" to my jdbc URLS.. what else I could do?
> >
>
> That is a no-op for backends 7.3 on.  What version of PostgreSQL are you using?
>
> It is not the postmaster you must tell which encoding to use.  You must have
> specified the encoding when you did the "createdb", with the "-E" option.
>
> Or specify the ENCODING clause when using the SQL "CREATE DATABASE" command.
>
> Abracos,
> Fernando
>
>
>
> --
> Fernando Nasser
> Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
> 2323 Yonge Street, Suite #300
> Toronto, Ontario   M4P 2C9
>
--

Felipe Schnack
Analista de Sistemas
felipes@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893

Faculdade Ritter dos Reis
www.ritterdosreis.br
felipes@ritterdosreis.br
Fone/Fax.: (51)32303328

Re: JDBC driver - first impressions and comments

From

Fernando Nasser

Date:

04 December 2002, 14:46:46

j.random.programmer wrote:> Folks:
>
> 1) postgres' JDBC does not *seem* to have a "owner" or
> a person finally responsible for the driver. (at least
> I haven't found this going thru the archives). This
> simply may be how jdbc development is done by many
> people but ultimately someone has to be responsible
> for it. That somone then should be mentioned as the
> contact person for all JDBC development requests and
> bug reports.
>

Just post to the list and they will see it.  (I am not one of them).

> 2) The jdbc compliance test results page has not been
> updated for quite some time. It's not clear how
> compliant the latest drivers are and if they are ready
> for serious production use.
>

I will see if can find some time and work on that.  But lots of things depend on
the PostgreSQL backend which, BTW, as of 7.3 is far more SQL compliant than
MySQL (has schemas and everything).

> 3) I am getting exceptions when I call the following
> methods:
>
> md.supportsSavepoints()
> md.supportsGetGeneratedKeys()
>

You are right.  I believe they should just return false.  I will investigate and
  post a patch.

> 4) The md.getColumnInfo() returns "int2" and "int4"
> for the TYPE_NAME field in the result set
> (corresponding to columns defined as smallint and
> integer). These should be "smallint" and "integer"
> instead.
>

User error ;-)  This is the "Data source dependent type name".

You want the DTA_TYPE, which gives you the equivalent SQL Type.

> 5) The md.getColumnInfo() returns a result set in
> which the columns are sorted by column *name* and not
> by the ORDINAL_POSITION field. This is opposite of the
> JDBC spec which requires that columns be sorted by the
> ORDINAL_POSITION field. If I create a table such as:
>
> create table foo ( zoo int, abc int, baba timestamp);
>
> I should get my columns in the following order: [zoo,
> abc, baba] but getColumnInfo() returns them as: [abc,
> baba, zoo]
>
> Note, getColumnInfo() does return the correct
> ORDINAL_POSITION data each column but sorts them by
> alpha - which is wrong. Moreover, all returned
> columns are in all-lowercase [this may be the correct
> behavior - I don't know, but if I create a column
> fooBAR, JDBC returns that as foobar].
>

A patch has just been posted to fix it.  But thanks for reporting it anyway.

As for case, PostgreSQL stores everything as lowercase unless you use "delimited
identifiers" (which allow mixed case, spaces etc.  -- I would stay away from
them, it is not portable).  The SQL standard is case insensitive and despite the
examples showing things in uppercase that is nothing that requires that
explicitly, just that 'fooBAR' is exactly the same thing as 'FOObar'.

I will let someone else reply to the second half.

Thank you for giving PostgreSQL a try.  If you persist a little you will soon
find that there are many features in it that you don't find in other Open Source
databases and that can be very handy.

Regards,
Fernando

--
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Re: UTF encoding error

From

Fernando Nasser

Date:

04 December 2002, 14:48:57

Felipe Schnack wrote:>   No I didn't... maybe I should recreate my database?
Specify charset in
> jdbc connection will help in something?

It all depends on which backend version you are using.  You still haven't told us.

Also, how did you load this strings into the database?


--
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Re: JDBC driver - first impressions and comments

From

Kris Jurka

Date:

04 December 2002, 14:52:38


On Wed, 4 Dec 2002, j.random.programmer wrote:

> Folks:
>
> Having used mysql for some time, I decided to try
> postgres 7.3. I thought I'd post some initial jdbc
> specific comments and initial impressions. Some of the
> issues that I mention may be known and/or fixed in
> development versions of the driver; therefore please
> treat this post as a "newbie's" point of view.
>
> Let me start by saying that I am writing a java based
> db object/abstraction layer that can examine any
> database and generate various java objects an
> interface to those objects. Automatic validation,
> application caching etc., will also be provided. For
> this code to work, I need to get a lot of meta-data
> about the database, tables and fields.
>
> **NOTE**
> For purposes of discussion, "md" in the following
> examples will refer to reference to a
> DatabaseMetaData object.
>
> I feel there is a definite difference between the
> mysql JDBC driver and the postgres one. The postgres
> driver is the lastest compiled driver for JDK 1.4
> against postgres 7.3
>
> 1) postgres' JDBC does not *seem* to have a "owner" or
> a person finally responsible for the driver. (at least
> I haven't found this going thru the archives). This
> simply may be how jdbc development is done by many
> people but ultimately someone has to be responsible
> for it. That somone then should be mentioned as the
> contact person for all JDBC development requests and
> bug reports.

Barry Lind and Dave Cramer are two developers with CVS commit rights who
focus solely on the JDBC driver.  The main contact point for development
requests and bug reports is this list which is actively followed by a
significant number of developers and users.

> 2) The jdbc compliance test results page has not been
> updated for quite some time. It's not clear how
> compliant the latest drivers are and if they are ready
> for serious production use.

The driver is what it is.  People use it in production environments all
the time.  I don't know what other options are out there, but the only way
you'll know if it's suitable for your production environment is by doing
testing which it appears you have done.

> 3) I am getting exceptions when I call the following
> methods:
>
> md.supportsSavepoints()
> md.supportsGetGeneratedKeys()

These are JDBC3 methods which do not have support in the driver yet.
They have been added solely to allow the JDBC driver to be compiled under
the 1.4 JDK

> 4) The md.getColumnInfo() returns "int2" and "int4"
> for the TYPE_NAME field in the result set
> (corresponding to columns defined as smallint and
> integer). These should be "smallint" and "integer"
> instead.

The smallint and integer types are simply aliases for the int2 and int4
types which are resolved upon table creation.  The spec does indicate that
this is a data source dependent type name.  Perhaps you could use
DATA_TYPE for your application.


> 5) The md.getColumnInfo() returns a result set in
> which the columns are sorted by column *name* and not
> by the ORDINAL_POSITION field. This is opposite of the
> JDBC spec which requires that columns be sorted by the
> ORDINAL_POSITION field. If I create a table such as:
>
> create table foo ( zoo int, abc int, baba timestamp);
>
> I should get my columns in the following order: [zoo,
> abc, baba] but getColumnInfo() returns them as: [abc,
> baba, zoo]
>
> Note, getColumnInfo() does return the correct
> ORDINAL_POSITION data each column but sorts them by
> alpha - which is wrong. Moreover, all returned
> columns are in all-lowercase [this may be the correct
> behavior - I don't know, but if I create a column
> fooBAR, JDBC returns that as foobar].

For case sensitivity see storesXXXXXCaseIdentifiers() where XXXXX is one
of Upper,Lower,Mixed.

For column ordering, if you are referring to getColumns(), I have already
supplied a patch to fix this.

> 5) The md.getImportedKeys() and md.getExportedKeys()
> methods take 30-50 seconds to return. There is
> something very strange going on (it's not the
> connection itself, I am on a private LAN and other
> jdbc methods return information immediately).

This is a known issue related to the complexity of retrieving the desired
info.  I am working on a solution to this.

> 6) The md.getTypeInfo() method returns every single
> tablename, sequence etc. as a type. This method should
> only return types like "integer", "timestamp" etc, NOT
> the name of every single table !

Tables always have a first class type associated with them consisting of a
field for each column, so they are in fact types.

>
> 7) There is no way to change the catalog of the JDBC
> connection. What I want is:
>
> myconnection.setCatalog("template1")
> get a connection to the "template1" database
> then say: myconnection.setCatalog("foo")
> get a connection to the "foo" database.
>
> This does not seem to be possible with the postgres
> driver. Why does the driver not treat a postgres
> database as a "catalog" or least pretend to ? The
> current state of affairs is a serious deficiency.

The database server itself does not support changing the database on a
given connection.  This is related to how the backend starts up and is not
likely to change anytime soon.  You must use multiple connections to
connect to multiple databases.  With 7.3 and the addition of schemas this
problem is not so severe as it once was.


> 8) It would be nice if the driver supported an auto
> reconnect feature. There is no documentation or readme
> file that comes with the compiled driver that talks
> about connection timeautos, auto-reconnecting
> connections etc.
>
> 9) The MD5 documentation is missing and the JDBC
> driver does not work if passwords are used on the
> server. (if we choose password authentication, then
> the latest 7.3 postgres release defaults to MD5 when I
> compile it out of the box). Currently, I've been
> unable to connect using "password" but can only
> connect using "trust".

I believe "crypt" is the correct setting for MD5 passwords.


Kris Jurka

Re: JDBC driver - first impressions and comments

From

Dave Cramer

Date:

04 December 2002, 20:07:50

On Wed, 2002-12-04 at 13:19, j.random.programmer wrote:
> Folks:
>
> Having used mysql for some time, I decided to try
> postgres 7.3. I thought I'd post some initial jdbc
> specific comments and initial impressions. Some of the
> issues that I mention may be known and/or fixed in
> development versions of the driver; therefore please
> treat this post as a "newbie's" point of view.
>
> Let me start by saying that I am writing a java based
> db object/abstraction layer that can examine any
> database and generate various java objects an
> interface to those objects. Automatic validation,
> application caching etc., will also be provided. For
> this code to work, I need to get a lot of meta-data
> about the database, tables and fields.
>
> **NOTE**
> For purposes of discussion, "md" in the following
> examples will refer to reference to a
> DatabaseMetaData object.
>
> I feel there is a definite difference between the
> mysql JDBC driver and the postgres one. The postgres
> driver is the lastest compiled driver for JDK 1.4
> against postgres 7.3
>
> 1) postgres' JDBC does not *seem* to have a "owner" or
> a person finally responsible for the driver. (at least
> I haven't found this going thru the archives). This
> simply may be how jdbc development is done by many
> people but ultimately someone has to be responsible
> for it. That somone then should be mentioned as the
> contact person for all JDBC development requests and
> bug reports.

postgres in general does not have owners, for any part of it. There are
maintainers, but nobody "owns" sections of code.
One thing to keep in mind is that postgres is one of the oldest open
source projects so the model is quite robust.

That being said, I am one of the maintainers, but certain people on this
list have adopted certain sections of the code. As a maintainer Barry
and I manage patches.

>
> 2) The jdbc compliance test results page has not been
> updated for quite some time. It's not clear how
> compliant the latest drivers are and if they are ready
> for serious production use.

As stated by others, many people myself included use the driver in
production environments.
>
> 3) I am getting exceptions when I call the following
> methods:
>
> md.supportsSavepoints()
> md.supportsGetGeneratedKeys()
>
> 4) The md.getColumnInfo() returns "int2" and "int4"
> for the TYPE_NAME field in the result set
> (corresponding to columns defined as smallint and
> integer). These should be "smallint" and "integer"
> instead.
Kris already addressed this, and in many cases the spec is vague
>
> 5) The md.getColumnInfo() returns a result set in
> which the columns are sorted by column *name* and not
> by the ORDINAL_POSITION field. This is opposite of the
> JDBC spec which requires that columns be sorted by the
> ORDINAL_POSITION field. If I create a table such as:
>
> create table foo ( zoo int, abc int, baba timestamp);
>
> I should get my columns in the following order: [zoo,
> abc, baba] but getColumnInfo() returns them as: [abc,
> baba, zoo]
>
> Note, getColumnInfo() does return the correct
> ORDINAL_POSITION data each column but sorts them by
> alpha - which is wrong. Moreover, all returned
> columns are in all-lowercase [this may be the correct
> behavior - I don't know, but if I create a column
> fooBAR, JDBC returns that as foobar].
The server itself does not create the column fooBAR, but foobar, unless
your coerce it.

> 5) The md.getImportedKeys() and md.getExportedKeys()
> methods take 30-50 seconds to return. There is
> something very strange going on (it's not the
> connection itself, I am on a private LAN and other
> jdbc methods return information immediately).

>
> 6) The md.getTypeInfo() method returns every single
> tablename, sequence etc. as a type. This method should
> only return types like "integer", "timestamp" etc, NOT
> the name of every single table !
As Kris pointed out each table is a class, or type.
>
> 7) There is no way to change the catalog of the JDBC
> connection. What I want is:
>
> myconnection.setCatalog("template1")
> get a connection to the "template1" database
> then say: myconnection.setCatalog("foo")
> get a connection to the "foo" database.

> This does not seem to be possible with the postgres
> driver. Why does the driver not treat a postgres
> database as a "catalog" or least pretend to ? The
> current state of affairs is a serious deficiency.
again Kris addressed this
>
> 8) It would be nice if the driver supported an auto
> reconnect feature. There is no documentation or readme
> file that comes with the compiled driver that talks
> about connection timeautos, auto-reconnecting
> connections etc.

>
> 9) The MD5 documentation is missing and the JDBC
> driver does not work if passwords are used on the
> server. (if we choose password authentication, then
> the latest 7.3 postgres release defaults to MD5 when I
> compile it out of the box). Currently, I've been
> unable to connect using "password" but can only
> connect using "trust".
>
> Best regards,
>
> --j
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Dave Cramer <Dave@micro-automation.net>