JBoss CLI escaping SELECT 1

Question

Using JBoss (EAP 6.4) CLI to deploy, and at this point, want to add a datasource.

The cli command is like this

/profile=XXX/subsystem=datasources/data-source=XXX:add\(connection-url=XXX,url-delimiter="|",jndi-name=java:jboss/datasources/XXX,driver-name=postgresql,user-name=XXX,password=XXX,check-valid-connection-sql="SELECT 1",validate-on-match=true,background-validation=false,share-prepared-statements=false\)

The exact add call looks this:

/data-source=${DS_NAME}:add(                     \
      connection-url=${DS_CONNECTION_URL},           \
      jndi-name=${DS_JNDI_NAME},                     \
      driver-name=${DS_DRIVER_NAME},                 \
      user-name=${DB_USER},                          \
      password=${DB_USER_PASSWD},                    \
      check-valid-connection-sql=\"SELECT 1\",       \
      validate-on-match=true,                        \
      background-validation=false,                   \
      share-prepared-statements=false)

Actually only this part is interesting

check-valid-connection-sql="SELECT 1"

After a successfull deployment, the whitespace will be replace with a double apostrophe. This is what I get in the pgsql log:

ERROR: syntax error at or near "1" at character 9
STATEMENT: SELECT''1

The JBoss admin console also shows SELECT''1 in the Configuration -> Datasources -> Validation > Check Valid Sql attribute

Tried to escape it properly several ways, without success. Any idea?

score 0 · Answer 1 · answered Mar 10 '17 at 18:00

You can define the datasource using the data-source add command, specifying the appropriate argument values.

Domain mode:

data-source add --profile=<default | ha | full | full-ha> --name=<Resource ID> --connection-url=<Connection URL> --jndi-name=<JNDI Name> --driver-name=<Driver Name>

Standalone mode:

data-source add --name=<Resource ID> --connection-url=<Connection URL> --jndi-name=<JNDI Name> --driver-name=<Driver Name>

A working example:

data-source add \
    --name=TEST \
    --check-valid-connection-sql="Select 1" \
    --driver-name=h2 --jndi-name=java:jboss/test \
    --connection-url=jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE

To get a list of the available options:

[standalone@localhost:9999 /] data-source add --help

Thanks for it, but the setup is looks ok and works! The only problem is with the whitespace escaping for some reason. I've added my exact call of the add method to the description. — Adam Biro, Mar 13 '17 at 12:25

score 0 · Answer 2 · answered Mar 12 '17 at 15:48

You can edit your standalone(-).xml or domain(-).xml to configure a datasource:

<subsystem xmlns="urn:jboss:domain:datasources:1.2">
    <datasources>
      <datasource jndi-name="java:jboss/datasources/OracleDS" pool-name="OracleDS" enabled="true" use-java-context="true">
        <connection-url>jdbc:oracle:thin:@myhostname:1521:oracle</connection-url>
        <driver>oracle</driver>
        <pool>
          <min-pool-size>10</min-pool-size>
          <max-pool-size>20</max-pool-size>
          <prefill>true</prefill>
        </pool>
        <security>
          <user-name>myuser</user-name>
          <password>mypass</password>
        </security>
        <validation>
          <validate-on-match>true</validate-on-match>
          <check-valid-connection-sql>select 1</check-valid-connection-sql>
          <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"></exception-sorter>
        </validation>
      </datasource>
      <drivers>
        <driver name="oracle" module="com.oracle.jdbc">
          <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
        </driver>
      </drivers>
    </datasources>
  </subsystem>

Yep, it's correct, but my problem is not being able to set the proper value during the deployment process. It's not an option to edit the config files every time when a new deployment happens. — Adam Biro, Mar 13 '17 at 12:12

score 0 · Accepted Answer · answered Jun 12 '17 at 11:34

0

Missed to share the result yet, so:

JBoss EAP had a bug with the whitespaces, before version 7.1.2.Final (EAP) https://issues.jboss.org/browse/AS7-4263

answered Jun 12 '17 at 11:34

Adam Biro

11
4

JBoss CLI escaping SELECT 1

3 Answers3