<html><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue-Light, Helvetica Neue Light, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:10px"><div dir="ltr" id="yui_3_16_0_1_1428536680164_9457"><span id="yui_3_16_0_1_1428536680164_9456">Hi Stian</span></div><div dir="ltr" id="yui_3_16_0_1_1428536680164_9457"><span><br></span></div><div dir="ltr" id="yui_3_16_0_1_1428536680164_9457"><span id="yui_3_16_0_1_1428536680164_9540">Thank you, by default the Informix hibernate dialect is selected, thus I used the substitutions.</span></div><div dir="ltr" id="yui_3_16_0_1_1428536680164_9457"><span>See below.</span></div><div dir="ltr" id="yui_3_16_0_1_1428536680164_9457"><span></span></div><div dir="ltr" id="yui_3_16_0_1_1428536680164_9457" class="" style="">2015-04-07 08:48:41,160 INFO [org.hibernate.dialect.Dialect] (MSC service thread 1-6) HHH000400: Using dialect: org.hibernate.dialect.InformixDialect</div><div class="" style="" id="yui_3_16_0_1_1428536680164_9484"><br class="" style=""></div><div class="" style="" id="yui_3_16_0_1_1428536680164_9484" dir="ltr">As you suggested I also set the dialect explicitly the result was the same error as above.</div><div class="" style="" id="yui_3_16_0_1_1428536680164_9484" dir="ltr"><br></div><div class="" style="" id="yui_3_16_0_1_1428536680164_9484" dir="ltr">I'll work this a little more, as I believe this may be an issue with hibernate on Informix. But I'll verify.</div><div class="" style="" id="yui_3_16_0_1_1428536680164_9484" dir="ltr"><br></div><div id="yui_3_16_0_1_1428536680164_9453"><div id="yui_3_16_0_1_1428536680164_9452">Regards
<br> Tom Connolly.</div></div><br> <div style="font-family: HelveticaNeue-Light, Helvetica Neue Light, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 10px;" id="yui_3_16_0_1_1428536680164_9515"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;" id="yui_3_16_0_1_1428536680164_9514"> <div dir="ltr" id="yui_3_16_0_1_1428536680164_9513"> <hr size="1"> <font size="2" face="Arial" id="yui_3_16_0_1_1428536680164_9521"> <b><span style="font-weight:bold;">From:</span></b> Stian Thorgersen <stian@redhat.com><br> <b><span style="font-weight: bold;">To:</span></b> Thomas Connolly <thomas_connolly@yahoo.com> <br><b><span style="font-weight: bold;">Cc:</span></b> keycloak-user@lists.jboss.org <br> <b><span style="font-weight: bold;">Sent:</span></b> Tuesday, April 7, 2015 5:09 PM<br> <b id="yui_3_16_0_1_1428536680164_9520"><span style="font-weight: bold;" id="yui_3_16_0_1_1428536680164_9519">Subject:</span></b> Re: [keycloak-user] Using Informix DB with Keycloak ...<br> </font> </div> <div class="y_msg_container" id="yui_3_16_0_1_1428536680164_9516"><br>To start with we can most likely accept PRs to make Informix to work, but we can't support all dbs out there.<br clear="none"><br clear="none">That being said it looks like either the Informix dialect is not being used, or it's not working properly. What you've done below hibernate.query.substitutions should be taken care of by Hibernate dialects.<br clear="none"><br clear="none">Try setting the "driverDialect" to org.hibernate.dialect.InformixDialect and see if you're still having issues.<br clear="none"><br clear="none">Another problem you may encounter is that Liquibase change-sets may need to be tweaked to work with Informix.<br clear="none"><div class="qtdSeparateBR"><br><br></div><div class="yqt1300850564" id="yqtfd50264"><br clear="none">----- Original Message -----<br clear="none">> From: "Thomas Connolly" <<a shape="rect" ymailto="mailto:thomas_connolly@yahoo.com" href="mailto:thomas_connolly@yahoo.com">thomas_connolly@yahoo.com</a>><br clear="none">> To: <a shape="rect" ymailto="mailto:keycloak-user@lists.jboss.org" href="mailto:keycloak-user@lists.jboss.org">keycloak-user@lists.jboss.org</a><br clear="none">> Sent: Tuesday, 7 April, 2015 2:10:33 AM<br clear="none">> Subject: [keycloak-user] Using Informix DB with Keycloak ...<br clear="none">> <br clear="none">> Hi All<br clear="none">> <br clear="none">> As part of a standardisation process, I'm trying to get Keycloak working with<br clear="none">> Informix DB, the enterprise DB!<br clear="none">> I understand that Keycloak does not officially support Informix however given<br clear="none">> the use of JPA / Hibernate I though there was a good chance it would work<br clear="none">> out of the box.<br clear="none">> However this has proven not the case. The connection dialect does indeed<br clear="none">> recognise Informix but does not appear to correctly map to this DB.<br clear="none">> <br clear="none">> The issue I've come across is the handling of boolean conditional in SQL.<br clear="none">> <br clear="none">> In RoleEntry.java the query<br clear="none">> @NamedQuery(name="getRealmRoleByName", query="select role from RoleEntity<br clear="none">> role where role.applicationRole = false and role.name = :name and role.realm<br clear="none">> = :realm")<br clear="none">> <br clear="none">> Generates the following<br clear="none">> select<br clear="none">> roleentity0_.id as id1_15_,<br clear="none">> roleentity0_.APP_REALM_CONSTRAINT as APP_REAL2_15_,<br clear="none">> roleentity0_.APPLICATION as APPLICAT7_15_,<br clear="none">> roleentity0_.APPLICATION_ROLE as APPLICAT3_15_,<br clear="none">> roleentity0_.DESCRIPTION as DESCRIPT4_15_,<br clear="none">> roleentity0_.NAME as NAME5_15_,<br clear="none">> roleentity0_.REALM as REALM8_15_,<br clear="none">> roleentity0_.REALM_ID as REALM_ID6_15_<br clear="none">> from<br clear="none">> KEYCLOAK_ROLE roleentity0_<br clear="none">> where<br clear="none">> roleentity0_.APPLICATION_ROLE=0<br clear="none">> The above does not work with Informix as 't', 'f', true and false<br clear="none">> respectively is used. So it should be<br clear="none">> where<br clear="none">> roleentity0_.APPLICATION_ROLE='f'<br clear="none">> <br clear="none">> Looking through the code, it appears I cannot configure this. Happy to be<br clear="none">> proven wrong here!<br clear="none">> I found I could override within Hibernate using the property<br clear="none">> hibernate.query.substitutions=true t, false f<br clear="none">> <br clear="none">> I've added this change into DefaultJpaConnectionProviderFactory.java<br clear="none">> <br clear="none">> String querySubstitutions = config.get("querySubstitutions");<br clear="none">> if (querySubstitutions != null) {<br clear="none">> logger.debug("hibernate.query.substitutions=" + querySubstitutions);<br clear="none">> properties.put("hibernate.query.substitutions", querySubstitutions);<br clear="none">> }<br clear="none">> <br clear="none">> Adding the setting in keycloak-server.json<br clear="none">> "connectionsJpa": {<br clear="none">> "default": {<br clear="none">> "dataSource": "java:jboss/datasources/KeycloakDS",<br clear="none">> "databaseSchema": "update",<br clear="none">> "querySubstitutions" : "true t, false f"<br clear="none">> }<br clear="none">> <br clear="none">> However I've found the translation is not occurring as I would have expected.<br clear="none">> <br clear="none">> Can you please advise on the approach I'm taking or any alternatives I have<br clear="none">> not looked into?<br clear="none">> <br clear="none">> Also once we get this working with Informix how can I get it added to the<br clear="none">> supported DB list?<br clear="none">> <br clear="none">> Regards<br clear="none">> Tom Connolly.</div><br clear="none">> <br clear="none">> _______________________________________________<br clear="none">> keycloak-user mailing list<br clear="none">> <a shape="rect" ymailto="mailto:keycloak-user@lists.jboss.org" href="mailto:keycloak-user@lists.jboss.org">keycloak-user@lists.jboss.org</a><br clear="none">> <a shape="rect" href="https://lists.jboss.org/mailman/listinfo/keycloak-user" target="_blank">https://lists.jboss.org/mailman/listinfo/keycloak-user</a><div class="yqt1300850564" id="yqtfd57183"><br clear="none"></div><br><br></div> </div> </div> </div></body></html>