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

[Thomas Connolly]

Hi Stian
Thank you, by default the Informix hibernate dialect is selected, thus I used the substitutions.See below.2015-04-07 08:48:41,160 INFO  [org.hibernate.dialect.Dialect] (MSC service thread 1-6) HHH000400: Using dialect: org.hibernate.dialect.InformixDialect
As you suggested I also set the dialect explicitly the result was the same error as above.
I'll work this a little more, as I believe this may be an issue with hibernate on Informix. But I'll verify.
Regards
 Tom Connolly.
      From: Stian Thorgersen <stian at redhat.com>
 To: Thomas Connolly <thomas_connolly at yahoo.com> 
Cc: keycloak-user at lists.jboss.org 
 Sent: Tuesday, April 7, 2015 5:09 PM
 Subject: Re: [keycloak-user] Using Informix DB with Keycloak ...
   
To start with we can most likely accept PRs to make Informix to work, but we can't support all dbs out there.

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.

Try setting the "driverDialect" to org.hibernate.dialect.InformixDialect and see if you're still having issues.

Another problem you may encounter is that Liquibase change-sets may need to be tweaked to work with Informix.



----- Original Message -----
> From: "Thomas Connolly" <thomas_connolly at yahoo.com>
> To: keycloak-user at lists.jboss.org
> Sent: Tuesday, 7 April, 2015 2:10:33 AM
> Subject: [keycloak-user] Using Informix DB with Keycloak ...
> 
> 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.
> 
> _______________________________________________
> keycloak-user mailing list
> keycloak-user at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-user


  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/keycloak-user/attachments/20150408/f61f14c9/attachment-0001.html