Re: JDBC and long TEXT fields

First of all I would like to thank all those who helped me solve my slow
odbc access problem. Shutting off odbc logs helped & recompiling the odbc
driver on NT really boosted the performance a bit. This can only happen on
the net.....I couldn't have afforded a consultant !!

I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
Application accessing the DB through postgrSQL ODBC driver v. 6.50 and
another accesing through JDBC.

Now I want to pool my connections on the postgreSQL server. Can some people
give me some pointers? Some Web Resources for studying this subject? My
experience on connection pooling is limited to MTS in MS environs.

With best regards.

Sanjay.

Let me also ask this question and add another request that may be helpful to
Sanjay and myself. All references and examples I have seen to date that
explain COM, DCOM, COM+ and MTS all make reference to a PUB database that
comes with MSSQL. If anyone out there has ported this to postgresql, I
could sure use any configuration scripts you might have.

Thanks,
Bryan

----- Original Message -----
From: "Sanjay Arora" <sk(at)pobox(dot)com>
To: <pgsql-interfaces(at)postgresql(dot)org>
Sent: Friday, December 01, 2000 10:26 PM
Subject: [INTERFACES] Thanks.....& How do I implement Connection
Pooling....;-))

> First of all I would like to thank all those who helped me solve my slow
> odbc access problem. Shutting off odbc logs helped & recompiling the odbc
> driver on NT really boosted the performance a bit. This can only happen on
> the net.....I couldn't have afforded a consultant !!
>
> I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
> Application accessing the DB through postgrSQL ODBC driver v. 6.50 and
> another accesing through JDBC.
>
> Now I want to pool my connections on the postgreSQL server. Can some
people
> give me some pointers? Some Web Resources for studying this subject? My
> experience on connection pooling is limited to MTS in MS environs.
>
> With best regards.
>
> Sanjay.
>

I am trying to insert rows into a table containing a field of type "TEXT".
The documentation says there is no limit to the size of this field; yet,
when I try to insert or update a row with a field contents of around 10k, I
get an error from the JDBC driver:

The SQL Statement is too long - insert into Customer_History (id,
id_customer, id_admin, timestamp, description) values (2021, 37515, null,
'2000-12-02 21:29:48.279', '...very very very long TEXT field')

at org.postgresql.Connection.ExecSQL(Connection.java:324)
at org.postgresql.jdbc2.Statement.execute(Statement.java:273)
at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
at
org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:
126)

I am using jdbc7.0.2-1.2.jar.

Any help would be appreciated.

Thank you in advance,
Bryan

There is actually an 8K (or so) limit to text field length. Although I
believe that is going away
soon, if it hasn't already.

For text fields of that length you have to use Blobs. In fact, as I
understand it, eventually the
JDBC driver will do this for you automatically.

Greg Speegle
Baylor University

Bryan (Mailing Lists) wrote:

> I am trying to insert rows into a table containing a field of type "TEXT".
> The documentation says there is no limit to the size of this field; yet,
> when I try to insert or update a row with a field contents of around 10k, I
> get an error from the JDBC driver:
>
> The SQL Statement is too long - insert into Customer_History (id,
> id_customer, id_admin, timestamp, description) values (2021, 37515, null,
> '2000-12-02 21:29:48.279', '...very very very long TEXT field')
>
> at org.postgresql.Connection.ExecSQL(Connection.java:324)
> at org.postgresql.jdbc2.Statement.execute(Statement.java:273)
> at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
> at
> org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java:
> 126)
>
>
> I am using jdbc7.0.2-1.2.jar.
>
> Any help would be appreciated.
>
> Thank you in advance,
> Bryan
>
>
>
>

> I am trying to insert rows into a table containing a field of type "TEXT".
> The documentation says there is no limit to the size of this field; yet,
> when I try to insert or update a row with a field contents of around 10k, I
> get an error from the JDBC driver...

As Greg points out, there is an upper limit. I would hope that the docs
say that TEXT has no *fixed* limit, unlike SQL9x types char() and
varchar().

Let us know if that is not what the docs say (and where they don't say
that ;) and we'll get them fixed up. Though for the next release there
is only a huge upper limit, in principle.

- Thomas

Looking at the User's Guide, Chapter 3, Character Types:

http://www.postgresql.org/docs/user/x1069.htm

It describes char, char(n), text, and varchar(n).

char is described as "single character"
char(n) is described as "fixed length"
text is described as "variable-length"
varchar(n) is described as "variable-length with limit"

Since three out of four of these types are described as having limits, my
interpretation is that the TEXT type does not have any limit. Evidently that
was a faulty assumption on my part, but the docs could stand to be a little
more clear.

Docs aside, I'm looking for a data type that will hold lengthy stings, > 8k.
Evidently a blob is what I need to use? But I don't see blobs listed as a
data type under Table 3-1, "Postgres Data Types".

http://www.postgresql.org/docs/user/datatype.htm

More direction would be appreciated..

Thanks again in advance,
Bryan

----- Original Message -----
From: "Thomas Lockhart" <lockhart(at)alumni(dot)caltech(dot)edu>
To: "Bryan (Mailing Lists)" <bryan_lists(at)netmeme(dot)org>
Cc: <pgsql-interfaces(at)postgresql(dot)org>
Sent: Saturday, December 02, 2000 9:28 PM
Subject: Re: [INTERFACES] JDBC and long TEXT fields

> > I am trying to insert rows into a table containing a field of type
"TEXT".
> > The documentation says there is no limit to the size of this field; yet,
> > when I try to insert or update a row with a field contents of around
10k, I
> > get an error from the JDBC driver...
>
> As Greg points out, there is an upper limit. I would hope that the docs
> say that TEXT has no *fixed* limit, unlike SQL9x types char() and
> varchar().
>
> Let us know if that is not what the docs say (and where they don't say
> that ;) and we'll get them fixed up. Though for the next release there
> is only a huge upper limit, in principle.
>
> - Thomas

Here are the last two entries from the PostgreSQL 7.0.3
/src/interfaces/jdbc/CHANGELOG:

Tue Jun 06 12:00:00 BST 2000 petermount(at)it(dot)maidstone(dot)gov(dot)uk
- Added org/postgresql/DriverClass.java to the list of files removed
by make clean (it's dynamically built)
- Fixed Statement, so that the update count is valid when an SQL
DELETE operation is done.
- While fixing the update count, made it easier to get the OID of
the last insert as well. Example is in example/basic.java

Tue Jun 06 08:37:00 BST 2000 petermount(at)it(dot)maidstone(dot)gov(dot)uk
- Removed a hardwired 8K limit on query strings
- Added some missing org.'s in Connection that prevented
the use of the geometric types.

This one above says it removed the 8k limit on query strings. I can't
remember all the release dates of 7.0.2 etc, but maybe these changes above
only made it into 7.0.3, not 7.0.2.

The jar file is now just called postgresql.jar in 7.0.3.

Hope this info is helpful.

On Saturday 02 December 2000 22:33, Bryan \(Mailing Lists\) wrote:
> I am trying to insert rows into a table containing a field of type "TEXT".
> The documentation says there is no limit to the size of this field; yet,
> when I try to insert or update a row with a field contents of around 10k, I
> get an error from the JDBC driver:
>
> The SQL Statement is too long - insert into Customer_History (id,
> id_customer, id_admin, timestamp, description) values (2021, 37515, null,
> '2000-12-02 21:29:48.279', '...very very very long TEXT field')
>
> at org.postgresql.Connection.ExecSQL(Connection.java:324)
> at org.postgresql.jdbc2.Statement.execute(Statement.java:273)
> at org.postgresql.jdbc2.Statement.executeUpdate(Statement.java:73)
> at
> org.postgresql.jdbc2.PreparedStatement.executeUpdate(PreparedStatement.java
>: 126)
>
>
> I am using jdbc7.0.2-1.2.jar.
>
> Any help would be appreciated.
>
> Thank you in advance,
> Bryan

--
-------- Robert B. Easter reaster(at)comptechnews(dot)com ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------

On Sat, 2 Dec 2000, Bryan (Mailing Lists) wrote:
>
> Docs aside, I'm looking for a data type that will hold lengthy stings, > 8k.
> Evidently a blob is what I need to use? But I don't see blobs listed as a
> data type under Table 3-1, "Postgres Data Types".
>
> http://www.postgresql.org/docs/user/datatype.htm
>
> More direction would be appreciated..
>
> Thanks again in advance,
> Bryan

My understanding is that JDBC blobs == PG large objects. The data type
you'll need to use is OID (Object ID). Have a look in the docs under
interfaces, Large Objects (largeobjects.htm) and JDBC (jdbc.htm)

-Cedar

Greg Speegle wrote:
>
> There is actually an 8K (or so) limit to text field length. Although I
> believe that is going away
> soon, if it hasn't already.

There is an 8K limit on the size of a tuple, not the text field. You
can recompile postgres to make the limit higher, but until 7.1 the limit
will not go away.

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com