7:10 p.m.
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.
2:09 a.m.
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(a)yahoo.com>
To: keycloak-user(a)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(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/keycloak-user
6:50 p.m.
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(a)redhat.com>
To: Thomas Connolly <thomas_connolly(a)yahoo.com>
Cc: keycloak-user(a)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(a)yahoo.com>
To: keycloak-user(a)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(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/keycloak-user
3563
days inactive
3564
days old
2 comments
2 participants
participants (2)
-
Stian Thorgersen -
Thomas Connolly