Accessing an OpenOffice data source through JDBC API?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Accessing an OpenOffice data source through JDBC API?

Martin Desruisseaux
Hello all

I'm writing an OpenOffice add-in for Calc in Java as part of Apache
Spatial Information System (SIS) project. I would like to access a
database registered as an OpenOffice data source through the JDBC API. I
see how to obtain a com::sun::star::sdbc::XConnection [1] from
OpenOffice database context by following the developer guide [2]. I can
see how I could use XConnection and related interfaces (XStatement,
etc.) as their design seems similar to JDBC. But I need to access the
database through java.sql.* interfaces rather than the
com.sun.start.sdbc.* ones, for use with an existing Java library (namely
Apache SIS). So I would like to know:

  * If a com::sun::star::sdbc::XConnection is connecting to a database
    using the "jdbc:" protocol, can we get the underlying
    java.sql.Connection?
  * If the answer to above question is "no", do we have wrappers
    somewhere that allow me to use a XConnection as if it was a
    java.sql.Connection?

As a bonus, it would be nice if OpenOffice configured a JNDI environment
for applications running in the JVM started by OpenOffice. That JNDI
environment would give access to the OpenOffice data sources in the form
of javax.sql.DataSource. I could volunteer for this part if there is
interest.

    Regards,

        Martin


[1] https://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/XConnection.html
[2] https://wiki.openoffice.org/wiki/Documentation/DevGuide/Database/Connecting_Through_a_DataSource


Reply | Threaded
Open this post in threaded view
|

Re: Accessing an OpenOffice data source through JDBC API?

Carl Marcum
On 07/01/2016 12:07 PM, Martin Desruisseaux wrote:

> Hello all
>
> I'm writing an OpenOffice add-in for Calc in Java as part of Apache
> Spatial Information System (SIS) project. I would like to access a
> database registered as an OpenOffice data source through the JDBC API. I
> see how to obtain a com::sun::star::sdbc::XConnection [1] from
> OpenOffice database context by following the developer guide [2]. I can
> see how I could use XConnection and related interfaces (XStatement,
> etc.) as their design seems similar to JDBC. But I need to access the
> database through java.sql.* interfaces rather than the
> com.sun.start.sdbc.* ones, for use with an existing Java library (namely
> Apache SIS). So I would like to know:
>
>    * If a com::sun::star::sdbc::XConnection is connecting to a database
>      using the "jdbc:" protocol, can we get the underlying
>      java.sql.Connection?
>    * If the answer to above question is "no", do we have wrappers
>      somewhere that allow me to use a XConnection as if it was a
>      java.sql.Connection?
>
> As a bonus, it would be nice if OpenOffice configured a JNDI environment
> for applications running in the JVM started by OpenOffice. That JNDI
> environment would give access to the OpenOffice data sources in the form
> of javax.sql.DataSource. I could volunteer for this part if there is
> interest.
>
>      Regards,
>
>          Martin
>
>
> [1] https://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/XConnection.html
> [2] https://wiki.openoffice.org/wiki/Documentation/DevGuide/Database/Connecting_Through_a_DataSource
>
>
>
Hi Martin,

You can access databases directly using java.sql, connection pooling
etc. without using XConnection at all as long as you have the connection
parameters.

Is there a reason you have to start with the XConnection?

Thanks,
Carl


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Accessing an OpenOffice data source through JDBC API?

Martin Desruisseaux
Hello Carl

Thanks for the reply.

Le 02/07/16 à 23:55, Carl Marcum a écrit :
> You can access databases directly using java.sql, connection pooling
> etc. without using XConnection at all as long as you have the
> connection parameters.
>
> Is there a reason you have to start with the XConnection?

I could include a Derby' or HSQL' JAR file in the add-in .pkg file. But
that would increase the .pkg file size for a driver that may already
exists in the OpenOffice installation. More annoying, I didn't found a
way to shutdown a Derby database properly when the user quits OpenOffice
(closing the connection is not enough). I was used to do that with Java
shutdown hooks, but those hooks are apparently not executed when
OpenOffice is shutdown. I also tried to override the dispose() method of
com.sun.star.lang.XComponent without success (I suspect that its purpose
was not the one I was looking for anyway). I was hopping that if I use a
com.sun.star.sdbc.XDataSource, OpenOffice would shutdown itself the
databases behind the XDataSources.

Another reason is that I need a directory where to create the Derby or
HSQL database. For now, I prefer to leave that choice to the user (I
realize that we may need a more automatic process in the future). For
applications outside OpenOffice, we currently ask the user to define an
environment variable. For OpenOffice, setting such environment variable
seems unconvenient. I though that instead, I could ask the user to
create an initially empty database of a given name as an OpenOffice data
source. Even if in the future we make the process more automatic, I
still wish to make easier for advanced users to query the database
themselves using Base.

Thanks,

    Martin



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Accessing an OpenOffice data source through JDBC API?

Martin Desruisseaux
In reply to this post by Carl Marcum
So following on my initial question, do we have an API for getting a
JDBC connection from an existing XConnection? If there is none, I
presume that we can write a JDBC implementation as a wrapper around the
XConnection. A quick search on google did not allowed me to find an
existing wrapper. Is anyone aware of such attempt?

Thanks,

    Martin


Le 02/07/16 à 23:55, Carl Marcum a écrit :

> On 07/01/2016 12:07 PM, Martin Desruisseaux wrote:
>> Hello all
>>
>> I'm writing an OpenOffice add-in for Calc in Java as part of Apache
>> Spatial Information System (SIS) project. I would like to access a
>> database registered as an OpenOffice data source through the JDBC API. I
>> see how to obtain a com::sun::star::sdbc::XConnection [1] from
>> OpenOffice database context by following the developer guide [2]. I can
>> see how I could use XConnection and related interfaces (XStatement,
>> etc.) as their design seems similar to JDBC. But I need to access the
>> database through java.sql.* interfaces rather than the
>> com.sun.start.sdbc.* ones, for use with an existing Java library (namely
>> Apache SIS). So I would like to know:
>>
>>    * If a com::sun::star::sdbc::XConnection is connecting to a database
>>      using the "jdbc:" protocol, can we get the underlying
>>      java.sql.Connection?
>>    * If the answer to above question is "no", do we have wrappers
>>      somewhere that allow me to use a XConnection as if it was a
>>      java.sql.Connection?
>>
>> As a bonus, it would be nice if OpenOffice configured a JNDI environment
>> for applications running in the JVM started by OpenOffice. That JNDI
>> environment would give access to the OpenOffice data sources in the form
>> of javax.sql.DataSource. I could volunteer for this part if there is
>> interest.
>>
>>      Regards,
>>
>>          Martin
>>
>> [1]
>> https://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/XConnection.html
>> [2]
>> https://wiki.openoffice.org/wiki/Documentation/DevGuide/Database/Connecting_Through_a_DataSource
>>
> Hi Martin,
>
> You can access databases directly using java.sql, connection pooling
> etc. without using XConnection at all as long as you have the
> connection parameters.
>
> Is there a reason you have to start with the XConnection?
>
> Thanks,
> Carl


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]