[keycloak-user] Using Informix DB with Keycloak ...

[Thomas Connolly]

Hi All
As part of a standardisation process, I'm trying to get Keycloak working with Informix DB, the enterprise DB!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.However this has proven not the case. The connection dialect does indeed recognise Informix but does not appear to correctly map to this DB.
The issue I've come across is the handling of boolean conditional in SQL.
In RoleEntry.java the query        @NamedQuery(name="getRealmRoleByName", query="select role from RoleEntity role where role.applicationRole = false and role.name = :name and role.realm = :realm")
Generates the following select        roleentity0_.id as id1_15_,        roleentity0_.APP_REALM_CONSTRAINT as APP_REAL2_15_,        roleentity0_.APPLICATION as APPLICAT7_15_,        roleentity0_.APPLICATION_ROLE as APPLICAT3_15_,        roleentity0_.DESCRIPTION as DESCRIPT4_15_,        roleentity0_.NAME as NAME5_15_,        roleentity0_.REALM as REALM8_15_,        roleentity0_.REALM_ID as REALM_ID6_15_     from        KEYCLOAK_ROLE roleentity0_     where        roleentity0_.APPLICATION_ROLE=0 The above does not work with Informix as 't', 'f', true and false respectively is used. So it should be    where        roleentity0_.APPLICATION_ROLE='f'
Looking through the code, it appears I cannot configure this. Happy to be proven wrong here!I found I could override within Hibernate using the property    hibernate.query.substitutions=true t, false f

I've added this change into DefaultJpaConnectionProviderFactory.java 
 String querySubstitutions = config.get("querySubstitutions"); if (querySubstitutions != null) { logger.debug("hibernate.query.substitutions=" + querySubstitutions);    properties.put("hibernate.query.substitutions", querySubstitutions); }
Adding the setting in keycloak-server.json    "connectionsJpa": {        "default": {            "dataSource": "java:jboss/datasources/KeycloakDS",            "databaseSchema": "update", "querySubstitutions" : "true t, false f"        }
However I've found the translation is not occurring as I would have expected. 
Can you please advise on the approach I'm taking or any alternatives I have not looked into?
Also once we get this working with Informix how can I get it added to the supported DB list?
Regards
 Tom Connolly.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/keycloak-user/attachments/20150407/e49c6971/attachment.html