You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomee.apache.org by Marco Ferretti <ma...@axiante.com> on 2023/05/10 16:19:06 UTC
unable to set additional jdbc parameters in datasource
Hi,
I am working on a development configuration of a TomEE 8.4, trying to add additional jdbc parameters to a (working) datasource.
Here's the datasource:
<Resource id="jdbc/mydatasource" type="DataSource">
JdbcDriver org.postgresql.Driver
JdbcUrl jdbc:postgresql://database:5432/postgres
UserName myusername
Password mypassword
</Resource>
In my application I need to call stored procedures using JPA: it seems that, in order to use 'call' rather than 'select', I have to add additional parameter to the jdbc driver (https://jdbc.postgresql.org/documentation/callproc/), namely escapeSyntaxCallMode.
Now, since I want to have a default schema (other than public) set, I also have to set the parameter currentSchema=myschema.
If I were to create a jdbc url string I would concatenate both at the end of the current url as JdbcUrl jdbc:postgresql://database:5432/postgres?currentSchema=myschema&escapeSyntaxCallMode=call
Unfortunately this generates a sax exception.
Checking both TomEE and Tomcat documentation I have found that I am supposed to pass additional params using the connectionProperties property (https://tomee.apache.org/datasource-config.html#connectionProperties). Unfortunately the props don't seem to be passed to the jdbc driver because I am getting errors such as
05/10/2023 6:00:19 PM
2023-05-10 16:00:19.005 UTC [53] ERROR: p_mui_update_esito_pubblicaz() is a procedure at character 15
05/10/2023 6:00:19 PM
2023-05-10 16:00:19.005 UTC [53] HINT: To call a procedure, use CALL.
Here's my questions :
How do I check how TomEE opens a connetion to the database (is there any logging I can set in order to inspect that? )
Has anyone had issues with (extra) connection properties ?
Before you ask: I have the correct jdbc driver (the one that supports the named param: postgresql-42.6.0.jar) and postgres server (14.7)
Any help would greatly be appreciated
Regards,
Marco F.
Re: unable to set additional jdbc parameters in datasource
Posted by "Jonathan S. Fisher" <ex...@gmail.com>.
Ask on StackOverflow and link here, or start a new thread ;) We use
eclipselink every day; might be able to help you out.
On Thu, May 11, 2023 at 10:55 AM Marco Ferretti <ma...@axiante.com>
wrote:
> Thanks a ton !
> It was so simple yet so sneaky I couldn't see it :)
>
> Now I have to undestand why eclipselink keeps on querying with SELECT
> rather than using CALL ... but that's another ML :)
> regards
> Marco
> On May 11 2023, at 1:47 am, Jonathan S. Fisher <ex...@gmail.com> wrote:
> > Hello Marco:
> >
> > The SAX Exception is because the ampersand character is a reserved
> > character in XML. This is unfortunate because it's also a special
> character
> > in RFC URLs.
> >
> > There's a couple of ways to solve this.
> > If this is a remote production or staging box, we like to use
> > environment variables and pass the URL in that way, which sidesteps the
> > issue:
> > JdbcUrl = ${JDBC_URL}
> >
> > If you'd like to embed the URL in the tomee.xml, you can simply xml
> escape
> > the JDBC url:
> > JdbcUrl
> > =
> jdbc:postgresql://database:5432/postgres?currentSchema=myschema&escapeSyntaxCallMode=call
> >
> > cheers,
> > On Wed, May 10, 2023 at 11:19 AM Marco Ferretti <
> marco.ferretti@axiante.com>
> > wrote:
> >
> > >
> > > Hi,
> > > I am working on a development configuration of a TomEE 8.4, trying to
> add
> > > additional jdbc parameters to a (working) datasource.
> > > Here's the datasource:
> > >
> > > <Resource id="jdbc/mydatasource" type="DataSource">
> > > JdbcDriver org.postgresql.Driver
> > > JdbcUrl jdbc:postgresql://database:5432/postgres
> > > UserName myusername
> > > Password mypassword
> > > </Resource>
> > >
> > > In my application I need to call stored procedures using JPA: it seems
> > > that, in order to use 'call' rather than 'select', I have to add
> additional
> > > parameter to the jdbc driver (
> > > https://jdbc.postgresql.org/documentation/callproc/), namely
> > > escapeSyntaxCallMode.
> > > Now, since I want to have a default schema (other than public) set, I
> also
> > > have to set the parameter currentSchema=myschema.
> > > If I were to create a jdbc url string I would concatenate both at the
> end
> > > of the current url as JdbcUrl
> > >
> jdbc:postgresql://database:5432/postgres?currentSchema=myschema&escapeSyntaxCallMode=call
> > > Unfortunately this generates a sax exception.
> > > Checking both TomEE and Tomcat documentation I have found that I am
> > > supposed to pass additional params using the connectionProperties
> property (
> > > https://tomee.apache.org/datasource-config.html#connectionProperties).
> > > Unfortunately the props don't seem to be passed to the jdbc driver
> because
> > > I am getting errors such as
> > >
> > > 05/10/2023 6:00:19 PM
> > > 2023-05-10 16:00:19.005 UTC [53] ERROR: p_mui_update_esito_pubblicaz()
> is
> > > a procedure at character 15
> > > 05/10/2023 6:00:19 PM
> > > 2023-05-10 16:00:19.005 UTC [53] HINT: To call a procedure, use CALL.
> > >
> > > Here's my questions :
> > > How do I check how TomEE opens a connetion to the database (is there
> any
> > > logging I can set in order to inspect that? )
> > >
> > > Has anyone had issues with (extra) connection properties ?
> > >
> > > Before you ask: I have the correct jdbc driver (the one that supports
> the
> > > named param: postgresql-42.6.0.jar) and postgres server (14.7)
> > > Any help would greatly be appreciated
> > > Regards,
> > > Marco F.
> > >
> > >
> >
> > --
> > Jonathan | exabrial@gmail.com
> > Pessimists, see a jar as half empty. Optimists, in contrast, see it as
> half
> > full.
> > Engineers, of course, understand the glass is twice as big as it needs to
> > be.
> >
>
>
--
Jonathan | exabrial@gmail.com
Pessimists, see a jar as half empty. Optimists, in contrast, see it as half
full.
Engineers, of course, understand the glass is twice as big as it needs to
be.
Re: unable to set additional jdbc parameters in datasource
Posted by Marco Ferretti <ma...@axiante.com>.
Thanks a ton !
It was so simple yet so sneaky I couldn't see it :)
Now I have to undestand why eclipselink keeps on querying with SELECT rather than using CALL ... but that's another ML :)
regards
Marco
On May 11 2023, at 1:47 am, Jonathan S. Fisher <ex...@gmail.com> wrote:
> Hello Marco:
>
> The SAX Exception is because the ampersand character is a reserved
> character in XML. This is unfortunate because it's also a special character
> in RFC URLs.
>
> There's a couple of ways to solve this.
> If this is a remote production or staging box, we like to use
> environment variables and pass the URL in that way, which sidesteps the
> issue:
> JdbcUrl = ${JDBC_URL}
>
> If you'd like to embed the URL in the tomee.xml, you can simply xml escape
> the JDBC url:
> JdbcUrl
> = jdbc:postgresql://database:5432/postgres?currentSchema=myschema&escapeSyntaxCallMode=call
>
> cheers,
> On Wed, May 10, 2023 at 11:19 AM Marco Ferretti <ma...@axiante.com>
> wrote:
>
> >
> > Hi,
> > I am working on a development configuration of a TomEE 8.4, trying to add
> > additional jdbc parameters to a (working) datasource.
> > Here's the datasource:
> >
> > <Resource id="jdbc/mydatasource" type="DataSource">
> > JdbcDriver org.postgresql.Driver
> > JdbcUrl jdbc:postgresql://database:5432/postgres
> > UserName myusername
> > Password mypassword
> > </Resource>
> >
> > In my application I need to call stored procedures using JPA: it seems
> > that, in order to use 'call' rather than 'select', I have to add additional
> > parameter to the jdbc driver (
> > https://jdbc.postgresql.org/documentation/callproc/), namely
> > escapeSyntaxCallMode.
> > Now, since I want to have a default schema (other than public) set, I also
> > have to set the parameter currentSchema=myschema.
> > If I were to create a jdbc url string I would concatenate both at the end
> > of the current url as JdbcUrl
> > jdbc:postgresql://database:5432/postgres?currentSchema=myschema&escapeSyntaxCallMode=call
> > Unfortunately this generates a sax exception.
> > Checking both TomEE and Tomcat documentation I have found that I am
> > supposed to pass additional params using the connectionProperties property (
> > https://tomee.apache.org/datasource-config.html#connectionProperties).
> > Unfortunately the props don't seem to be passed to the jdbc driver because
> > I am getting errors such as
> >
> > 05/10/2023 6:00:19 PM
> > 2023-05-10 16:00:19.005 UTC [53] ERROR: p_mui_update_esito_pubblicaz() is
> > a procedure at character 15
> > 05/10/2023 6:00:19 PM
> > 2023-05-10 16:00:19.005 UTC [53] HINT: To call a procedure, use CALL.
> >
> > Here's my questions :
> > How do I check how TomEE opens a connetion to the database (is there any
> > logging I can set in order to inspect that? )
> >
> > Has anyone had issues with (extra) connection properties ?
> >
> > Before you ask: I have the correct jdbc driver (the one that supports the
> > named param: postgresql-42.6.0.jar) and postgres server (14.7)
> > Any help would greatly be appreciated
> > Regards,
> > Marco F.
> >
> >
>
> --
> Jonathan | exabrial@gmail.com
> Pessimists, see a jar as half empty. Optimists, in contrast, see it as half
> full.
> Engineers, of course, understand the glass is twice as big as it needs to
> be.
>
Re: unable to set additional jdbc parameters in datasource
Posted by "Jonathan S. Fisher" <ex...@gmail.com>.
Hello Marco:
The SAX Exception is because the ampersand character is a reserved
character in XML. This is unfortunate because it's also a special character
in RFC URLs.
There's a couple of ways to solve this.
If this is a remote production or staging box, we like to use
environment variables and pass the URL in that way, which sidesteps the
issue:
JdbcUrl = ${JDBC_URL}
If you'd like to embed the URL in the tomee.xml, you can simply xml escape
the JDBC url:
JdbcUrl
= jdbc:postgresql://database:5432/postgres?currentSchema=myschema&escapeSyntaxCallMode=call
cheers,
On Wed, May 10, 2023 at 11:19 AM Marco Ferretti <ma...@axiante.com>
wrote:
>
> Hi,
> I am working on a development configuration of a TomEE 8.4, trying to add
> additional jdbc parameters to a (working) datasource.
> Here's the datasource:
>
> <Resource id="jdbc/mydatasource" type="DataSource">
> JdbcDriver org.postgresql.Driver
> JdbcUrl jdbc:postgresql://database:5432/postgres
> UserName myusername
> Password mypassword
> </Resource>
>
> In my application I need to call stored procedures using JPA: it seems
> that, in order to use 'call' rather than 'select', I have to add additional
> parameter to the jdbc driver (
> https://jdbc.postgresql.org/documentation/callproc/), namely
> escapeSyntaxCallMode.
> Now, since I want to have a default schema (other than public) set, I also
> have to set the parameter currentSchema=myschema.
> If I were to create a jdbc url string I would concatenate both at the end
> of the current url as JdbcUrl
> jdbc:postgresql://database:5432/postgres?currentSchema=myschema&escapeSyntaxCallMode=call
> Unfortunately this generates a sax exception.
> Checking both TomEE and Tomcat documentation I have found that I am
> supposed to pass additional params using the connectionProperties property (
> https://tomee.apache.org/datasource-config.html#connectionProperties).
> Unfortunately the props don't seem to be passed to the jdbc driver because
> I am getting errors such as
>
> 05/10/2023 6:00:19 PM
> 2023-05-10 16:00:19.005 UTC [53] ERROR: p_mui_update_esito_pubblicaz() is
> a procedure at character 15
> 05/10/2023 6:00:19 PM
> 2023-05-10 16:00:19.005 UTC [53] HINT: To call a procedure, use CALL.
>
> Here's my questions :
> How do I check how TomEE opens a connetion to the database (is there any
> logging I can set in order to inspect that? )
>
> Has anyone had issues with (extra) connection properties ?
>
> Before you ask: I have the correct jdbc driver (the one that supports the
> named param: postgresql-42.6.0.jar) and postgres server (14.7)
> Any help would greatly be appreciated
> Regards,
> Marco F.
>
>
--
Jonathan | exabrial@gmail.com
Pessimists, see a jar as half empty. Optimists, in contrast, see it as half
full.
Engineers, of course, understand the glass is twice as big as it needs to
be.