Re: unable to set additional jdbc parameters in datasource

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 <[email protected]> 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&amp;escapeSyntaxCallMode=call
>
> cheers,
> On Wed, May 10, 2023 at 11:19 AM Marco Ferretti <[email protected]>
> 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 | [email protected]
> 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.
>