Thread: Posgresql - openoffice-JDBC
Hi, I would like to know as to how can I make a JDBC connection to connect openoffice and Postgresql. I have already installed postgresql (./configure --with-java). But I am not able to make a connection to openoffice. Pls help me out... Parul
On 20 Jun 2002 10:18:07 +0530
Parul Agarwal <parul@netcore.co.in> wrote:
> Hi,
>
> I would like to know as to how can I make a JDBC connection to connect
> openoffice and Postgresql. I have already installed postgresql
> (./configure --with-java). But I am not able to make a connection to
> openoffice.
>
I had this same problem, and finally resolved it. What you need to do is
go to the file
../OpenOffice.org1.0/user/config/javarc
(where the '..' is where ever you installed it) and find the line:
SystemClasspath=...
and add the path to the jar file. On my system it is at:
/usr/share/pgsql/jdbc7.1-1.2.jar
Once I added that to the class path, all works well! I also use unixODBC
with PostgreSQL & OpenOffice.
On Mon, 24 Jun 2002 20:30:42 +0000
basis volume limited <bvol@b-vol.co.uk> wrote:
> On Monday 24 June 2002 01:58, you wrote:
> > Once I added that to the class path, all works well! I also use
> > unixODBC with PostgreSQL & OpenOffice.
>
> did you manage to use the OO/SO dialogs (General and ODBC tabs)
> through Tools>Data Sources or did you edit the .odbc.ini file. If it
> was the former could you let me know the settings please?
>
> I compiled an ODBC driver that came with postgres and had to edit
> .odbc.ini to get it to work.
With the driver supplied with PostgreSQL I had to manually edit the
odbc.ini file. When I changed and used the driver supplied with unixODBC
the GUI worked.
When I setup my connecition with ODBCConfig, the Driver is:
/usr/lib/libodbcpsql.so
and the Setup is:
/usr/lib/libodbcpsqlS.so
According to unixODBC, they have made changes to the code, and have given
it back to PostgreSQL. I do not know when PostgreSQL will incorperate
those changes.
Timothy Reaves wrote:
> I had this same problem, and finally resolved it. What you need to do is
> go to the file
>
> ../OpenOffice.org1.0/user/config/javarc
>
> (where the '..' is where ever you installed it) and find the line:
>
> SystemClasspath=...
>
> and add the path to the jar file. On my system it is at:
>
> /usr/share/pgsql/jdbc7.1-1.2.jar
>
> Once I added that to the class path, all works well!
I can't get that to work. I added (in our case)
/java/postgresql/pgjdbc2.jar
to the end of the SystemClasspath line, then started OpenOffice and
entered a data source of (again, for our case)
jdbc:postgresql://miranda:5432/catalog
with a driver class of org.postgresql.Driver. He could see the
table name in the database, but when I try to construct any SQL
with either the "New Query Design" or "New SQL Command" buttons,
I get a dialog saying "Error while connecting to the data source"
and "ERROR: parse: parse error at or near "("". Any ideas?
OpenOffice 1.0, Linux 2.4.18.
--
____________________________________________________________
Glenn Holmer gholmer@weycogroup.com
Programmer/Analyst phone: 414.908.1809
Weyco Group, Inc. fax: 414.908.1601
On Tue, 25 Jun 2002 07:53:40 -0500
Glenn Holmer <gholmer@weycogroup.com> wrote:
>
> I can't get that to work. I added (in our case)
>
> /java/postgresql/pgjdbc2.jar
>
> to the end of the SystemClasspath line, then started OpenOffice and
> entered a data source of (again, for our case)
>
> jdbc:postgresql://miranda:5432/catalog
>
Where did that driver come from? Also, the 5432 is redundent. You only
need to specify the port if not using the default.
Have you tried connecting to a local db?
Timothy Reaves wrote: > On Tue, 25 Jun 2002 07:53:40 -0500 > Glenn Holmer <gholmer@weycogroup.com> wrote: > > >>I can't get that to work. I added (in our case) >> >>/java/postgresql/pgjdbc2.jar >> >>to the end of the SystemClasspath line, then started OpenOffice and >>entered a data source of (again, for our case) >> >>jdbc:postgresql://miranda:5432/catalog >> > > > Where did that driver come from? jdbc.postgresql.org > Also, the 5432 is redundent. But not harmful, yes? > > Have you tried connecting to a local db? You mean on my machine using "localhost"? Yes, same story. BTW, the database on "miranda" is a production database which a Java program uses, so it's not a Postgres issue. -- ____________________________________________________________ Glenn Holmer gholmer@weycogroup.com Programmer/Analyst phone: 414.908.1809 Weyco Group, Inc. fax: 414.908.1601
On Wed, 26 Jun 2002 09:10:38 -0500
Glenn Holmer <gholmer@weycogroup.com> wrote:
> Timothy Reaves wrote:
> > On Tue, 25 Jun 2002 07:53:40 -0500
> > Glenn Holmer <gholmer@weycogroup.com> wrote:
> >
> >
> >>I can't get that to work. I added (in our case)
> >>
> >>/java/postgresql/pgjdbc2.jar
> >>
> >>to the end of the SystemClasspath line, then started OpenOffice and
> >>entered a data source of (again, for our case)
> >>
> >>jdbc:postgresql://miranda:5432/catalog
> >>
> >
> >
> > Where did that driver come from?
>
> jdbc.postgresql.org
>
> > Also, the 5432 is redundent.
>
> But not harmful, yes?
>
> >
> > Have you tried connecting to a local db?
>
> You mean on my machine using "localhost"? Yes, same story. BTW,
> the database on "miranda" is a production database which a Java
> program uses, so it's not a Postgres issue.
Correct; it will not harm to specify the port.
The jdbc driver I use is not the one directly available from the jdbc
site, but is the one that ships with PostgreSQL itself. Unfortunatly I
have found that there are at least thre different ODBC & three different
JDBC drivers. The ODBC shiped with PostgreSQL did not work - I use the
one from unixODBC, but the jdbc does work. Perhaps try that driver.
Does the postmaster log file show anything from when you attempt to
connect? Also, try the local connect string: jdbc:postgresql:databasename
when the db is local, no host name is needed.
Hello, all. I had a web application running on version 7.2 of Linux with
version 7.1.3 of PostgreSQL running together with version 7.1-1.2 of the
JDBC driver with version 3.2.3 of Tomcat. It worked fine.
Recently, I upgraded to version 7.2 of PostgreSQL and created a new
database from scratch. I put the latest version of the JDBC driver
(pgjdbc2.jar) in these directories:
/usr/local/jakarta/jakarta-tomcat/lib
/usr/local/driver
But when I start up Tomcat and try to run a query in the app against it, I
get the server output below, which I'm hoping someone can help me
understand. Thanks in advance.
-Stu
P.S. Here's the server output:
Something unusual has occured to cause the driver to fail. Please report
this exception: Exception: java.sql.SQLException: FATAL 1: No pg_hba.conf
entry for host 127.0.0.1, user postgres, database mayanlanguages
Stack Trace:
java.sql.SQLException: FATAL 1: No pg_hba.conf entry for host 127.0.0.1,
user postgres, database mayanlanguages
at org.postgresql.Connection.openConnection(Connection.java:178)
at org.postgresql.Driver.connect(Driver.java:149)
at java.sql.DriverManager.getConnection(DriverManager.java:517)
at java.sql.DriverManager.getConnection(DriverManager.java:177)
at
com.mayanlanguages.db.DBConnection.getConnection(DBConnection.java:31)
at
com.mayanlanguages.db.DBConnection.getConnection(DBConnection.java:18)
at
com.mayanlanguages.misc.BaseFactory.select(BaseFactory.java:262)
at
com.mayanlanguages.misc.BaseFactory.select(BaseFactory.java:253)
at com.mayanlanguages.misc.BaseObject.select(BaseObject.java:144)
at
com.mayanlanguages.servlets.LanguageListServlet.processRequest(Unknown
Source)
at
com.mayanlanguages.servlets.BaseServlet.preProcessRequest(Unknown Source)
at com.mayanlanguages.servlets.CoreServlet.doGet(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at
org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at
org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:812)
at
org.apache.tomcat.core.ContextManager.service(ContextManager.java:758)
at
org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:213)
at
org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at
org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
at java.lang.Thread.run(Thread.java:484)
End of Stack Trace
at org.postgresql.Driver.connect(Driver.java:166)
at java.sql.DriverManager.getConnection(DriverManager.java:517)
at java.sql.DriverManager.getConnection(DriverManager.java:177)
at
com.mayanlanguages.db.DBConnection.getConnection(DBConnection.java:31)
at
com.mayanlanguages.db.DBConnection.getConnection(DBConnection.java:18)
at
com.mayanlanguages.misc.BaseFactory.select(BaseFactory.java:262)
at
com.mayanlanguages.misc.BaseFactory.select(BaseFactory.java:253)
at com.mayanlanguages.misc.BaseObject.select(BaseObject.java:144)
at
com.mayanlanguages.servlets.LanguageListServlet.processRequest(Unknown
Source)
at
com.mayanlanguages.servlets.BaseServlet.preProcessRequest(Unknown Source)
at com.mayanlanguages.servlets.CoreServlet.doGet(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at
org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at
org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:812)
at
org.apache.tomcat.core.ContextManager.service(ContextManager.java:758)
at
org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:213)
at
org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
at
org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
at java.lang.Thread.run(Thread.java:484)
Something unusual has occured to cause the driver to fail. Please report
this exception: Exception: java.sql.SQLException: FATAL 1: No pg_hba.conf
entry for host 127.0.0.1, user postgres, database mayanlanguages
Stack Trace:
java.sql.SQLException: FATAL 1: No pg_hba.conf entry for host 127.0.0.1,
user postgres, database mayanlanguages
at org.postgresql.Connection.openConnection(Connection.java:178)
at org.postgresql
-- Stuart P. Robinson [stuart@zapata.org]
On Tue, 2002-07-02 at 09:11, Stuart Robinson wrote: > Something unusual has occured to cause the driver to fail. Please report > this exception: Exception: java.sql.SQLException: FATAL 1: No pg_hba.conf > entry for host 127.0.0.1, user postgres, database mayanlanguages You don't have a pg_hba entry for localhost. While you are on the upgrade path I would sugest moving to Tomcat 4.0.x and the latest jdk. Lots of stuff has changed but it is faster and easier to configure once you get the hang of it Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
Thanks, Tony. I went in, commented out the appropriate section of pg_hba.conf, and--voi la--it works. I don't remember having to do this for verison 7.1.3 of PostgreSQL. Did it work differently in that version? And thanks for the tip about Tomcat. I've been meaning to upgrade that too, but later. Tiny baby steps towards a good web application... Cheers, Stuart On 2 Jul 2002, tony wrote: > You don't have a pg_hba entry for localhost. > > While you are on the upgrade path I would sugest moving to Tomcat 4.0.x > and the latest jdk. Lots of stuff has changed but it is faster and > easier to configure once you get the hang of it > > Cheers > > Tony Grant > > -- Stuart P. Robinson [stuart@zapata.org] 688 Hampshire Street San Francisco, CA 94110 USA
I'm trying to use a function that returns a refcursor in a web application. I followed the example code I found at this URL (as suggested on this mailing list): http://fts.postgresql.org/db/mw/msg.html?mid=1354157. I wound up writing this code inside of my base factory class: System.out.println("-- SQL: " + stmt); ps = con.prepareStatement(stmt); ps = bindSelectStmt(ps, obj, selectCase); ResultSet rs = ps.executeQuery(); rs.next(); String cursorName = rs.getString(1); System.out.println("-- CURSOR NAME: " + cursorName); rs = ps.executeQuery("fetch all from " + cursorName); But when I try running that, I get the following output from Tomcat: -- SQL: SELECT get_examples_by_entry_id(?) -- CURSOR NAME: <unnamed cursor 1> java.sql.SQLException: ERROR: parser: parse error at or near "<" I believe the exception is caused by the cursor name, which I know from the system-out to be: <unnamed cursor 1> Any ideas as to what's going on here? Thanks in advance. Regards, Stuart -- Stuart P. Robinson [stuart@zapata.org] 688 Hampshire Street San Francisco, CA 94110 USA
I think I figured out what the problem is.
I wasn't not putting cursorName in quotes, like so:
rs = ps.executeQuery("fetch all from \"" + cursorName + "\"");
Thanks anyhow.
Regards,
Stuart
On Tue, 2 Jul 2002, Stuart
Robinson wrote:
> I'm trying to use a function that returns a refcursor in a web
> application.
>
> I followed the example code I found at this URL (as suggested on this
> mailing list):
>
> http://fts.postgresql.org/db/mw/msg.html?mid=1354157.
>
> I wound up writing this code inside of my base factory class:
>
> System.out.println("-- SQL: " + stmt);
> ps = con.prepareStatement(stmt);
> ps = bindSelectStmt(ps, obj, selectCase);
> ResultSet rs = ps.executeQuery();
> rs.next();
> String cursorName = rs.getString(1);
> System.out.println("-- CURSOR NAME: " + cursorName);
> rs = ps.executeQuery("fetch all from " + cursorName);
>
> But when I try running that, I get the following output from
> Tomcat:
>
> -- SQL: SELECT get_examples_by_entry_id(?)
> -- CURSOR NAME: <unnamed cursor 1>
> java.sql.SQLException: ERROR: parser: parse error at or near "<"
>
> I believe the exception is caused by the cursor name, which I know from
> the system-out to be:
>
> <unnamed cursor 1>
>
> Any ideas as to what's going on here?
>
> Thanks in advance.
>
> Regards,
> Stuart
>
>
--
Stuart P. Robinson [stuart@zapata.org]
688 Hampshire Street
San Francisco, CA 94110
USA
I've run into another roadblock while attempting to use a function that returns a refcursor. Here's what happens when I run a function that returns a refcursor for a query that I've already tested and know for sure returns one row: mayanlanguages=# select get_examples_by_entry_id(2879); get_examples_by_entry_id -------------------------- <unnamed cursor 1> (1 row) mayanlanguages=# FETCH ALL IN "<unnamed cursor 1>"; NOTICE: PerformPortalFetch: portal "<unnamed cursor 1>" not found FETCH 0 Why doesn't it find the cursor? Any ideas? Perhaps this isn't appropriate for this mailing list, but I figured that I'd post it here anyhow since ultimately I'm trying to get this to work with the JDBC. Thanks in advance. Regards, Stuart -- Stuart P. Robinson [stuart@zapata.org]
Stuart Robinson <stuart@zapata.org> writes:
> Why doesn't it find the cursor?
Do you have a begin/end block wrapped around this? Cursors only live
till the end of the current transaction ...
regards, tom lane