<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_1428362768319_3173"><span>Hi All</span></div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3180"><span><br></span></div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178">As part of a standardisation process, I'm trying to get Keycloak working with Informix DB, the enterprise DB!</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178">I understand that Keycloak does not officially support Informix however given the use of JPA / Hibernate I though there was a good chance it would work out of the box.</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178">However this has proven not the case. The connection dialect does indeed recognise Informix but does not appear to correctly map to this DB.</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178"><br></div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style="">The issue I've come across is the handling of boolean conditional in SQL.</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178"><br></div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178">In RoleEntry.java the query</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> @NamedQuery(name="getRealmRoleByName", query="select role from RoleEntity role where role.applicationRole = false and role.name = :name and role.realm = :realm")</div><div class="" style="" id="yui_3_16_0_1_1428362768319_3413"><br class="" style=""></div><div class="" style="" id="yui_3_16_0_1_1428362768319_3413" dir="ltr">Generates the following </div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style="">select</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> roleentity0_.id as id1_15_,</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> roleentity0_.APP_REALM_CONSTRAINT as APP_REAL2_15_,</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> roleentity0_.APPLICATION as APPLICAT7_15_,</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> roleentity0_.APPLICATION_ROLE as APPLICAT3_15_,</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> roleentity0_.DESCRIPTION as DESCRIPT4_15_,</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> roleentity0_.NAME as NAME5_15_,</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> roleentity0_.REALM as REALM8_15_,</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> roleentity0_.REALM_ID as REALM_ID6_15_ </div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> from</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> KEYCLOAK_ROLE roleentity0_ </div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> where</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> <b id="yui_3_16_0_1_1428362768319_3383"><i id="yui_3_16_0_1_1428362768319_3382">roleentity0_.APPLICATION_ROLE=0</i></b></div><div></div><div id="yui_3_16_0_1_1428362768319_3110"> </div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr">The above does not work with Informix as 't', 'f', true and false respectively is used. So it should be</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> where</div><div dir="ltr" id="yui_3_16_0_1_1428362768319_3178" class="" style=""> <b class="" style="" id="yui_3_16_0_1_1428362768319_3381"><i class="" style="" id="yui_3_16_0_1_1428362768319_3380">roleentity0_.APPLICATION_ROLE='f'</i></b></div><div class="" style=""></div></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><br></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr">Looking through the code, it appears I cannot configure this. <i id="yui_3_16_0_1_1428362768319_3361">Happy to be proven wrong here!</i></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr">I found I could override within Hibernate using the property</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"> hibernate.query.substitutions=true t, false f<br></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><br></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr">I've added this change into DefaultJpaConnectionProviderFactory.java </div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><br></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""><span class="" style="white-space:pre"> </span>String querySubstitutions = config.get("querySubstitutions");</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""><span class="" style="white-space:pre"> </span>if (querySubstitutions != null) {</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""><span class="" style="white-space:pre"> </span>logger.debug("hibernate.query.substitutions=" + querySubstitutions);</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""> properties.put("hibernate.query.substitutions", querySubstitutions);</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""><span class="" style="white-space:pre"> </span>}</div></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><i><br></i></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr">Adding the setting in keycloak-server.json</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""> "connectionsJpa": {</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""> "default": {</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""> "dataSource": "java:jboss/datasources/KeycloakDS",</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""> "databaseSchema": "update",</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""><span class="" style="white-space:pre" id="yui_3_16_0_1_1428362768319_3686"> </span>"querySubstitutions" : "true t, false f"</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr" class="" style=""> }</div></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><i><br></i></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr">However I've found the translation is not occurring as I would have expected. </div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><br></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr">Can you please advise on the approach I'm taking or any alternatives I have not looked into?</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><br></div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr">Also once we get this working with Informix how can I get it added to the supported DB list?</div><div id="yui_3_16_0_1_1428362768319_3110" dir="ltr"><br></div><div id="yui_3_16_0_1_1428362768319_3175"><div id="yui_3_16_0_1_1428362768319_3174">Regards
<br> Tom Connolly.</div></div></div></body></html>