Thread: Posgresql - openoffice-JDBC

Posgresql - openoffice-JDBC

From
Parul Agarwal
Date:
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


Re: Posgresql - openoffice-JDBC

From
Timothy Reaves
Date:
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.



Re: Posgresql - openoffice-JDBC

From
Timothy Reaves
Date:
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.






Re: Posgresql - openoffice-JDBC

From
Glenn Holmer
Date:
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




Re: Posgresql - openoffice-JDBC

From
Timothy Reaves
Date:
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?



Re: Posgresql - openoffice-JDBC

From
Glenn Holmer
Date:
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




Re: Posgresql - openoffice-JDBC

From
Timothy Reaves
Date:
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.



upgraded from 7.1.3 to 7.2.4 and can't get JDBC to work with Tomcat

From
Stuart Robinson
Date:
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]




Re: upgraded from 7.1.3 to 7.2.4 and can't get JDBC to work

From
tony
Date:
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




Re: upgraded from 7.1.3 to 7.2.4 and can't get JDBC to work

From
Stuart Robinson
Date:
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




handling refcursors

From
Stuart Robinson
Date:
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





Re: handling refcursors

From
Stuart Robinson
Date:
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




not able to fetch from refcursor

From
Stuart Robinson
Date:
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]




Re: not able to fetch from refcursor

From
Tom Lane
Date:
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