Using PostgreSQL on OPS

Hello,

I've tried to use a PostgreSQL database on OPS but I have this error:

  Cannot load JDBC driver for class: org.postgresql.Driver

What am I supposed to do ? I've put the JDBC driver file,
'postgresql-8.0-315.jdbc3.jar', in '/webapps/ops/WEB-INF/lib/' but it
didn't solve my problem. I must have forgotten something :-)

Cheers,

--
Damiano ALBANI



--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Damiano,

Are you using Tomcat to handle connection pooling? In other words, do
you have a "META-INF/context.xml" file, or a block in your web.xml,
something like this?

Context antiResourceLocking="false" antiJARLocking="false"
allowLinking="true" reloadable="false">
     <Resource name="jdbc/postgres-sql"
          auth="Container"
          type="javax.sql.DataSource"
          driverClassName="org.postgresql.Driver"
         (etc...)/>
</Context>

If so, then your Postgres driver JAR file needs to be in
$(tomcat_home)/common/lib, so that Tomcat can load it *before* it opens
your web application. Tomcat manages JDBC resources separately from web
applications, and at the time it tries to initialize your database
connection, it hasn't yet looked at your webapp.

Putting the driver .jar file in common/lib makes it accessible both to
the servlet engine and to your webapp, so you don't need to keep a copy
in WEB-INF/lib.

I hope this helps.

Jeff Jones
The Weather Channel Interactive


Damiano Albani wrote:

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

On 3/1/06, Jeff Jones <[hidden email]> wrote:
> Damiano,
>
> Are you using Tomcat to handle connection pooling? In other words, do
> you have a "META-INF/context.xml" file, or a block in your web.xml,
> something like this?

Hello,

I do use OPS with Tomcat. For my own curiosity, is the solution you
gave me the only one possible ? I mean, is it possible to configure
PostgreSQL only for OPS, instead of Tomcat-wide ?

I suppose that, in addition to your instructions, I have to configure
OPS, as shown on
<http://www.orbeon.com/ops/doc/processors-sql#d956032e126> ?

Thanks a lot for your help Jeff!

--
Damiano ALBANI



--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Damiano,

The anchor in that URL (#d956032e126) didn't work for me, but I think I
can answer your question anyway. If anyone more knowledgeable can chime
in, please do so.

Basically, there are two ways to connect the SQL processor to a database.

Option 1:
If you have Tomcat manage your database connection, as I described in my
last email, then in every <sql:config> block/document you have, you need
to have something like this:

<sql:config>
     <sql:connection>
         <sql:datasource>postgres-sql</sql:dasource>
         <sql:query>
             <!-- Your query goes here, as usual -->
         </sql:query>
     </sql:connection>
</sql:config>

The content of that <sql:datasource> element will be the same as the
"name" attribute of the "Resource" element in your Tomcat config,
without the leading "jdbc/".

Option 2:
You can have the SQL processor connect directly to the database, if you
want, instead of using Tomcat to manage the connection. In this case,
you omit the <sql:datasource> element above. Instead, you provide the
database connection to the SQL processor through its "datasource" input.
That configuration looks like this:

<datasource>
   <!-- Specify the driver for the database -->
   <driver-class-name>org.postgresql.Driver</driver-class-name>
   <!-- The JDBC URI of the database -->
   <uri>jdbc:postgres-sql:i-dont-know-what-this-part-looks:like</uri>
   <!-- Optional username and password -->
   <username>sa</username>
   <password/>
</datasource>

This is what the documentation refers to (somewhat confusingly) as an
"external database definition", and they have a very well-stated caution
about using them:

"External datasource definitions do not use connection pooling at the
moment. Because creating database connections is usually an expensive
operation, they should be used only for development or demo purposes."

On the other hand, if you use an external connection, your .jar file can
probably stay in your WEB-INF/lib, and you don't need to put anything
database-related in your Tomcat config, because Tomcat will not have
anything to do with your database connection.

Does that make it any clearer?

Jeff Jones
The Weather Channel Interactive


Damiano Albani wrote:

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

On 3/1/06, Jeff Jones <[hidden email]> wrote:
OK, so I chose this option 1 :

in model.xpl :

      <sql:config xmlns:sql="http://orbeon.org/oxf/xml/sql">
        <departements>
          <sql:connection>
            <sql:datasource>PostgreSQL</sql:datasource>
            <sql:execute>
              <sql:query>SELECT * FROM Departement</sql:query>
              <sql:result-set>
                <sql:row-iterator>
                  <departement>
                    <sql:get-columns format="xml">
                    </sql:get-columns>
                  </departement>
                </sql:row-iterator>
              </sql:result-set>
            </sql:execute>
          </sql:connection>
        </departements>
      </sql:config>

in conf/server.xml of Tomcat 5.5:

<Resource name="jdbc/PostgreSQL" auth="Container"
type="javax.sql.DataSource" username="snotling" password=""
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5432/xforms1?autoReconnect=true"
maxActive="8" maxIdle="4"/>

However, I get the error :

  javax.naming.NameNotFoundException
  Name jdbc is not bound in this Context

I'm a bit lost in all those different configuration files by the way :-)
Maybe the error is related to 'java:comp/env/jdbc' thingy ?

Cheers,

--
Damiano ALBANI



--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Hi Damiano,

As you are using "option 1", PresentationServer delegates the creation
of the database connection to Tomcat. When Tomcat loads the driver for
your database, it does not take into consideration jar files in your
WEB-INF/lib. This means that you need to put your PostgreSQL driver in
Tomcat's common/lib.

This is really something that happens at the Tomcat level, so there
might be a configuration there you can do to have Tomcat look for the
database driver in your application WEB-INF/lib. I don't know of any,
but maybe someone can give you a better answer on the Tomcat mailing
list.

Alex

On 3/1/06, Damiano Albani <[hidden email]> wrote:
--
Blog (XML, Web apps, Open Source):
http://www.orbeon.com/blog/



--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

I know in the last OPS, when I set it up to connect to SQL server, I had
to put the following in web.xml in webapps/ops

   <resource-ref>
        <description>DataSource</description>
        <res-ref-name>jdbc/db</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
        </resource-ref>


I put the JDBC driver in common/lib like Alex suggested,.

In sql config, my matching sql config started like this:
   <sql:config>
                <sql:connection>
                    <sql:datasource>db</sql:datasource>

The easiest way to make sure tomcat is working tright with PostGres
maybe to configure tomcat to use JDBC reals for authenticateion
In server.xml in tomcatm this required

<Realm className="org.apache.catalina.realm.JDBCRealm" debug="99"
      driverName="com.microsoft.jdbc.sqlserver.SQLServerDriver"
 
connectionURL="jdbc:microsoft:sqlserver://btsword:1433;user=tomcat;passw
ord=########;SelectMethod=cursor;DatabaseName=tax"
       userTable="users" userNameCol="username" userCredCol="password"
   userRoleTable="userroles" roleNameCol="rolename"/>


http://tomcat.apache.org/tomcat-3.3-doc/JDBCRealm-howto.html


-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of
Alessandro Vernet
Sent: Wednesday, March 01, 2006 8:29 PM
To: [hidden email]
Subject: Re: [ops-users] Using PostgreSQL on OPS


Hi Damiano,

As you are using "option 1", PresentationServer delegates the creation
of the database connection to Tomcat. When Tomcat loads the driver for
your database, it does not take into consideration jar files in your
WEB-INF/lib. This means that you need to put your PostgreSQL driver in
Tomcat's common/lib.

This is really something that happens at the Tomcat level, so there
might be a configuration there you can do to have Tomcat look for the
database driver in your application WEB-INF/lib. I don't know of any,
but maybe someone can give you a better answer on the Tomcat mailing
list.

Alex

On 3/1/06, Damiano Albani <[hidden email]> wrote:

--
Blog (XML, Web apps, Open Source):
http://www.orbeon.com/blog/





--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

It now works here, I put a ops.xml in conf/Catalina/localhost :

<Context path="/ops"
  debug="0" privileged="false">

<Resource name="jdbc/PostgreSQL" auth="Container"
type="javax.sql.DataSource" username="snotling" password="snotling"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost/xforms1?autoReconnect=true"
maxActive="8" maxIdle="4"/>

</Context>

Don't know if it's the recommended way of setting up Tomcat, but at
least it works ! :-)

Thanks all for your help !

--
Damiano ALBANI



--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws