Re: [GENERAL] Errors in other languages
Quoting Peter Eisentraut [EMAIL PROTECTED]: Luis Magaa writes: It is possible to display error messages from PostgreSQL in other languages rather than English ? No The JDBC driver does return it's own error messages in French, Dutch and Italian if the VM is in one of those locales. Peter -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] How to make PostgreSQL JDBC drive get PGTZ?
Quoting Raymond Chui [EMAIL PROTECTED]:
My system time zone (TZ) is set to US Eastern Standard Time is -5 hours
of
GMT time
I set
export PGTZ=GMT
then go to psql do
insert into a_table (a_column) values ('2001-02-08 18:30:00+00');
select a_column from a_table;
will get result exactly what I inserted
2001-02-08 18:30:00+00
But when I used JDBC drive from org.postgresql.Driver to insert the
same
value,
I got
2001-02-08 23:30:00+00
This is the value of GMT time at 18:30 of EST time! Which tell me
the Postgres JDBC drive insert the value in EST time. Why is that?
JDBC ( Java in general) works on a standard timezone so your local date is
converted into GMT (or rather UTC) when it's stored, so what you should use is
getDate() and then use a Calendar object (eg GregorianCalendar) to convert to
your local timezone.
7.1 has the extra methods that can pass a Calendar object implemented (not
fully tested yet, but will be in CVS by the weekend).
There are still a few date/time problems. I'm due in the next few evenings to
start writing the TestCases for Date/Time/Timestamp's so when they are done,
we'll have a firm base to work on (rather than guess work used so far).
I already did
Properties p = new Properties();
p.put("PGTZ", "GMT");
before connect to PostgreSQL server, but no luck!
The JDBC driver only accepts a few properties, and PGTZ isn't one of them, and
never will because Java uses it's own time zone handling.
I'd advise you read up on Calendar to see how timezones are handled.
Peter
--
Peter Mount [EMAIL PROTECTED]
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] jdbc connection pool settings
Quoting Culley Harrelson [EMAIL PROTECTED]: I'm in the process of implementing connection pooling and the setup I'm using (http://www.javaexchange.com - really slick!) has settings for min # connections and max # connection. Any suggestions on where I should set these values? min=2, max=6? My site will be outside the firewall, open to the public for all to trash. Although you're using JDBC here, this is actually a basic postgres admin problem. The size of your pool would be based on what resources you have available and how much traffic you're expecting. Peter Culley __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] Auto-timestamp generator (attached)
Quoting Einar Karttunen [EMAIL PROTECTED]: I think that modules like this could be included in the distribution or archieved at the ftp. They'd make it easier for people new to sql to start using postgresql. Also there would be no performance loss in the backend code, as these "modules" don't need any support. This is what /contrib in the source is for ;-) Peter - Einar On Thu, 8 Feb 2001, Richard Huxton wrote: Following the 'new type proposal' discussion recently I decided to have a play at creating an automatic trigger generator. Attached is the sql and an example of its use. Basically you call a function: select lastchg_addto(mytable,mycol); where mycol is of type timestamp. The function builds the To use it you will need plpgsql enabled (man createlang) and also version 7.1 After use, there are two functions left - you can remove these with: drop function lastchg_addto(text,text); drop function lastchg_remove(text,text); I've tried to layout the plpgsql for ease of understanding - if you want to see how the trigger gets created, you can return exec1 or exec2 instead of the success message. This just a demo - obviously it's fairly simple to put together triggers for this purpose, but I'd appreciate any thoughts about the approach. TIA people Oh - 2 questions for any of the developers/clued up 1. Is there any way to parse a variable-length list of parameters in plpgsql? 2. Is there any chance of a different quoting method for functions? e.g. create function ... as q[ ...body here ...]; So we can avoid the stuff - it's a lot of static - Richard Huxton -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] JDBC geo objects
Quoting elein [EMAIL PROTECTED]: Redirecting to the jdbc list... I'm a Java novice and I would like to be able to use JDBC to get Points and Paths into a Java program as native Java objects. Note that the binary form of Paths is more than a simple array. I know how to do this in C, but getting the object definition and reading the object into Java are not in my scope. Right now, the only thing I can thing of is to write a java export routine for the objects and figure out how to make JDBC push it along. I am getting conflicting info about whether java can read byte wads into object--I think it needs to extract the pieces and assign the attributes individually. But I may be talking in circles here. Actually since 6.2 the JDBC driver supports provides class equivalents of the geometric types. Check the jdbc source for org.postgresql.geometric.* You use the getObject()/setObject() methods to use them. Peter Examples, help and advice are very welcome. Thanks for your help, elein --- Elein Mustain NextBus Information Services, Inc. [EMAIL PROTECTED] (510)420-3120 -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] jdbc, use of nested ResultSet loops.(longish, interesting I hope)
Quoting "[EMAIL PROTECTED]" [EMAIL PROTECTED]:
A few days ago I posted a query regarding a problem I had with errors I
was
incurring doing some nested queries with the jdbc driver. (having
recently
done a re-install my email history is in limbo for the moment)
Found some of my problems were in messy ugly code, fixed some of the
problems by creating a new connection for the loop that was throwing
errors.
Still curious on what would be 'good' style to use.
Curious if anyone knows what can/should work and if this varies with
jdbc/odbc drivers. I assume the jdbc standards dictate certain behaviour
for jdbc conformance, is there a spec I should be reading? Does the
conformance of drivers with the spec vary widely from one implementation
to
another?? (I'm interested in the differences between the Oracle
classes12
and Postgresql-jdbc drivers specifically but general comments sought)
The JDBC Specifications (1.2, 2.0 and soon 3.0 [May I was told yesterday]).
Look on http://java.sun.com
pseudocode examples
1. Non nested loops.
get connection
create statement object.
use statement object to create ResultSet object using SQL query.
scroll thru ResulSet object to retrieve data.
close ResultSet object.
use statement object to create ResultSet object using SQL query.
scroll thru ResulSet object to retrieve data.
close ResultSet object.
repeat as many times as required.
Perfectly fine. Technically you can't have two ResultSet's open from one
Statement, but you may reuse Statement once the ResultSet's been closed. In
fact the specs say that Statement should implicitly close it's open ResultSet
before returning the new one.
close statement object
close database connection object.
2. Nested loops, I use this format when I'm say printing a record of
information to a table, and need to present a combo box selection (inner
nested loop) that can only be generated once the row data is known
(outer
loop).
get connection
create statement object A.
create statement object B.
use statement object A to create ResultSet object AA using SQL query.
scroll thru ResulSet object AA to retrieve data.
use statement object B to create ResultSet object BB using SQL query.
scroll thru ResulSet object BB to retrieve data.
close ResultSet object BB.
close ResultSet object AA.
close statement object
close database connection object.
Perfectly fine according to the specs. You can have as many
Statement/PreparedStatement/CallableStatement objects open at any one time.
Some drivers have problems because they don't manage the network protocol
correctly and get in a mess. Not sure about Oracle (I use 8i) but PostgreSQL
works fine here (see the Thread Safety tests in the source).
I found (by trial and error, and a little commonsense)
- nested Resultsets do not seem to be the way to go. (couldn't get this
style to work with Oracle classes12 jdbc driver)
Hmmm, what version are you using of oracle's classes? I think this is one point
we beat them then ;-)
- Using multiple statements off the one database connection seems to
work OK.
Other 'thing' I picked up was that Prepared Statements are supposed to
be
much faster (by 20-40%) according to the sun jdbc API reference book.
Yes, except we currently implement them in the driver. When the backend handles
prepared statements, the performance boost will suddenly appear. The boost is
where the Query Planner precompiles the query once, then the plan is executed
for each update. Currently the plan is compiled for each call.
Comments anyone??
brief code example below.
(this code compiled and ran OK, you may need to change to suit your
postgres datafile name.)
import java.sql.*;
import java.util.*;
import java.io.*;
import java.lang.*;
import javax.servlet.*;
import javax.servlet.http.*;
/*
Adds forecast to nominated project.
*/
public class simpleExample extends HttpServlet {
Ooo Servlets, started playing with them yesterday ;-)
[snip]
Peter
--
Peter Mount [EMAIL PROTECTED]
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] Connection pooling
Quoting Guillaume Lmery [EMAIL PROTECTED]: Hi, my database has to handle quickly several number of queries per second from numerous clients. Because of the waste of time for connection/disconnection, Im' looking for a connection pooling tool. Where can I find a such tool or can somebody send me one ? What interface are you using? JDBC/ODBC/LibPQ? Peter Many thanks in advance, Guillaume. -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [JDBC] [GENERAL] JDBC: java.lang.ArrayIndexOutOfBoundsException: 256
Quoting Mirko Zeibig [EMAIL PROTECTED]: Redirecting to the jdbc only list as that's where all jdbc emails should now go. Hello, my system: RedHat 7.0 postgresql 7.1 (CVS) IBMJava2-SDK-1.3-1.1 After having set JAVA_HOME "ant" is working like a charm, now it will build postgresql.jar and postgresql-example.jar. I noticed however that there is a difference between the ant and the make built: whereas with ant the jar has about 83kB, with "make jdbc2" it is about 180kB make had the -0 flag while ant doesn't so the 83k one is compressed. Needs checking, but in the early days, .jar files could not be compressed. If jdk1.2 and later are ok, this is a bonus. But I wonder how browsers and jdk1.1.x handle them. [snip] this exception: java.lang.ArrayIndexOutOfBoundsException: 256 at java.lang.Throwable.init(Throwable.java:84) at java.lang.Exception.init(Exception.java:35) at java.sql.SQLException.init(SQLException.java:100) at org.postgresql.util.PSQLException.init(PSQLException.java:42) at org.postgresql.Driver.connect(Driver.java:127) at java.sql.DriverManager.getConnection(DriverManager.java:523) at java.sql.DriverManager.getConnection(DriverManager.java:183) at example.psql.init(psql.java:31) at example.psql.main(psql.java:207) Thanks. There is a bug recently introduced in Driver PSQLException. I added the fuller stack trace this morning to try to find it. This will prove useful. Peter -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] Why does the JDBC driver not support prepareCall?
Quoting "Brett W. McCoy" [EMAIL PROTECTED]: On Fri, 19 Jan 2001, mg wrote: I have troubles with jsp-based websites that have been generated by Macromedia DreamWeaver UltraDev 1.0. My analysis revealed that the problem lies in the JDBC driver not supporting prepareCall (prepareCall throws the PSQLException "postgresql.con.call" instead). Is there any workaround or intermediate solution? Is there anybody working to fill this gap, and when can a solution be expected? Is there anybody working on this subject? prepareCall is reportedly going to be implemented in 7.1. It depends on how I get on this weekend (although at the rate I've been catching up over the last few days, its pretty good so far). It's mainly a hardware issue for me at the moment... Peter -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] Large files on linux
Quoting Fernan Aguero [EMAIL PROTECTED]: Dear all: I am having trouble with large files on a Linux box (RH 6.2). I know there is a limit of 2 GB on the file size, but do not know if this is kernel related, filesystem related or both. IIRC Partly Kernel, Partly FileSystem. For large tables we split the tables at the 1Gb point. The Storage Manager then deals with which file it needs to open when it knows that the file position is beyond any one 1Gb point. NB: The source was doing this at the 2Gb point but (6.3.x or 6.4.x I think) I did some tests for another project and found that Linux didn't like files exactly 2Gb in size. After discussion here, 1Gb was chosen as a nice round figure. -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] Unanswered questions about Postgre
Quoting Tom Lane [EMAIL PROTECTED]: The JDBC support for over-the-wire access to large objects used to have some bugs, but AFAIK those are cleaned up in current sources (right Peter?) Yes except for DatabaseMetaData.getTables() but thats not directly to do with large objects. As long as things settle down here by Saturday, I'll be sorting out what's outstanding... Peter -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] Tree structure
On Fri, 26 Feb 1999, Kaare Rasmussen wrote:
I can't figure this one out. I need a tree structure like this
Number Pointer
10
21
31
42
50
61
75
This should somehow show up like this
Number
1
2
4
3
6
5
7
The whole excercise is because I'd like to show a tree structure:
1
- 2
- - 4
- 3
- 6
5
- 7
Is this possible with PostgreSQL?
This does the trick:
select textcat(text_substr('- '::text,10-pointer,pointer+1),int4_text(num)) as
ptr from a order by num;
It produces the output:
test= select textcat(text_substr('- '::text,10-pointer,pointer+1),
int4_text(num)) as ptr from a order by num;
ptr
---
1
- 2
- 3
-- 4
5
- 6
- 7
(7 rows)
Turning off headings and alignment:
test= \a
turned off field alignment
test= \t
turned off output headings and row count
test= select textcat(text_substr('- '::text,10-pointer,pointer+1),
int4_text(num)) as ptr from a order by num;
1
- 2
- 3
-- 4
5
- 6
- 7
Peter
--
Peter T Mount [EMAIL PROTECTED]
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
Re: [GENERAL] GIS/GPS Experiences with pgsql?
On Thu, 18 Feb 1999, Gregory Maxwell wrote: On Wed, 17 Feb 1999, Peter T Mount wrote: [snip] If the TIGER/Line data is raster, and each feature (polygon, line, circle, etc) doesn't exceed the block size, then postgresql should be able to handle it. [snip] Vector not raster. Right? Yes, the Tiger data is vector. Peter -- Peter T Mount [EMAIL PROTECTED] Main Homepage: http://www.retep.org.uk PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres Java PDF Generator: http://www.retep.org.uk/pdf
[GENERAL] Re: [INTERFACES] Re: connecting: unix socket? Yes. TCPIP port? No.-i? Yes.
On Sun, 7 Feb 1999, Bob VonMoss wrote: Tom Lane wrote: Bob VonMoss [EMAIL PROTECTED] writes: [ can connect via unix socket, but not via TCP ] The administrator says this is how postmaster is invoked: /usr/local/pgsql/bin/postmaster -S -i -D /usr/local/pgsql/data -p 5432 OK, that eliminates the "forgot -i" gotcha. James Thompson is almost certainly right that the problem is that Postgres' pg_hba.conf file is not set up to allow connections from whichever IP address you are connecting from. We heard about a similar problem recently which turned out to be due to use of "virtual server" IP addresses. The pgsql administrator put a line in pg_hba.conf that looks like this, where my_db is substituted for my database name: host my_db 0.0.0.0 0.0.0.0 ident sameuser I'm still getting the same 'User authentication failed' messages. Same thing from JDBC. Do I need access to the 'template1' table also? no. Is the machine you are running the java app or psql on running ident? Here's a transcript of a session from the command -- Peter T Mount [EMAIL PROTECTED] Main Homepage: http://www.retep.org.uk PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres Java PDF Generator: http://www.retep.org.uk/pdf
Re: [GENERAL] Callable Statement in JDBC
On Fri, 8 Jan 1999, Sze Yuen Wong wrote: Hi, I'm using Postgre in my Java program. The Java bean (JCDatasource from KL group) require callable statement when saving to the DB. But Postgre report Callable statement is not supported. Anybody knows if 6.4 support callable statement? Nope. CallableStatement is still unsupported, mainly because I haven't been able to figure out how CallableStatement works. -- Peter T Mount [EMAIL PROTECTED] Main Homepage: http://www.retep.org.uk PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres Java PDF Generator: http://www.retep.org.uk/pdf
Re: [GENERAL] PostgreSQL and WWW
On Sat, 28 Nov 1998, Dmitry Iv Popov wrote:
Greetings,
great thanks to Oleg Broytmann for his useful advice, I've
understood the proper fromat for input data at last and the
problem with copying DB from file was successfully solved.
Well, I hope you will not be angry with me for one more
(and I want belive the last) lame question: how do I make my
DB accessible from the Web (via JDBC)? With what parameters
should I call 'Class.forName()' and
'DriverManager.getConnection()' in case of PostgreSQL?
Connection con =
DriverManager.getConnection("jdbc:postgresql:rscexamdb","username","password");
I must notice that I'm going to write a servlet, but not an
applet.
It works best with servlet's and applications, but (personally) it's not
that good for applets.
For applets, it would be better to have your own protocol to a servlet,
then jdbc to the database.
--
Peter T Mount [EMAIL PROTECTED]
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
[GENERAL] Re: [INTERFACES] pb compiling JDBC interface
On Wed, 8 Jul 1998, Cyril Ferrand wrote: Thanks for your answer but I find the Pb it was a bad version of JDK (1.0.2) now I have installed the 1.1.3 and there is no error. Ah yes, JDBC was quite different for 1.0.2... But now I have a pb with exeample, because I don't understand what to to put as user and passwd ... Postgresql manages it's own users and passwords (it doesn't use any unix accounts apart from the DBA [usually 'postgres']). The CREATE USER, ALTER USER and DROP USER sql statements do this. In JDBC, you need to supply the user password that postgres understands. -- * * Cyril Ferrand : mailto:[EMAIL PROTECTED]* *http://www.mygale.org/05/ironmask * * -- Peter T Mount [EMAIL PROTECTED] or [EMAIL PROTECTED] Main Homepage: http://www.retep.org.uk Someday I may rebuild this signature completely ;-) Work Homepage: http://www.maidstone.gov.uk Work EMail: [EMAIL PROTECTED]
