Database table update action on PostgreSQL

[renemulder]

Hi All,

We're using the database connector version 1.2.1 to provision users to
a database. We have an example project based on HASQL where all seems
to be working fine.

However, in our project when we use Postgres, there seems to be an
issue with column quotation.

The configuration 'Name Quoting' is set to 'double' and this seems to
work except for a select statement when checking whether a user
already exists (search).

From the log i found that the action resolves to the following query

SELECT "USER_ID" , "ILS_BIBLIOTHEEK" , "CRM_WACHTWOORD" ,
"CRM_EMAIL" , "ILS_PASNUMMER" , "ILS_WACHTWOORD" , "CRM_OPENID" FROM
"USER_PROFILE" WHERE USER_ID = ?

If i run this on the database directly, i indeed get the same error
message:

Error: ERROR: column "user_id" does not exist
SQLState: 42703
ErrorCode: 0

The error is in the final reference to USER_ID. This must be quoted as
well

SELECT "USER_ID" , "ILS_BIBLIOTHEEK" , "CRM_WACHTWOORD" ,
"CRM_EMAIL" , "ILS_PASNUMMER" , "ILS_WACHTWOORD" , "CRM_OPENID" FROM
"USER_PROFILE" WHERE "USER_ID" = ?


Below is the beginning of the stack trace:

********************************

==> syncope-core-connid.log <==
13:29:55.061 ERROR
org.identityconnectors.databasetable.DatabaseTableConnector.executeQuery
Query SELECT "USER_ID" , "ILS_BIBLIOTHEEK" , "CRM_WACHTWOORD" ,
"CRM_EMAIL" , "ILS_PASNUMMER" , "ILS_WACHTWOORD" , "CRM_OPENID" FROM
"USER_PROFILE" WHERE USER_ID = ? on ObjectClass: __ACCOUNT__ error
org.postgresql.util.PSQLException: ERROR: column "user_id" does not
exist
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:
2102) ~[postgresql-9.0-801.jdbc4.jar:na]
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:
1835) ~[postgresql-9.0-801.jdbc4.jar:na]
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
257) ~[postgresql-9.0-801.jdbc4.jar:na]
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:
500) ~[postgresql-9.0-801.jdbc4.jar:na]
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:
388) ~[postgresql-9.0-801.jdbc4.jar:na]
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:
273) ~[postgresql-9.0-801.jdbc4.jar:na]
at
org.identityconnectors.databasetable.DatabaseTableConnector.executeQuery(DatabaseTableConnector.java:
527) [org.connid.bundles.db.table-1.2.1.jar:na]
at
org.identityconnectors.databasetable.DatabaseTableConnector.executeQuery(DatabaseTableConnector.java:
111) [org.connid.bundles.db.table-1.2.1.jar:na]
at
org.identityconnectors.framework.impl.api.local.operations.SearchImpl.rawSearch(SearchImpl.java:
118) [framework-internal-1.3.1.jar:na]
at
org.identityconnectors.framework.impl.api.local.operations.SearchImpl.search(SearchImpl.java:
82) [framework-internal-1.3.1.jar:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:
1.6.0_20]
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
57) ~[na:1.6.0_20]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
43) ~[na:1.6.0_20]
at java.lang.reflect.Method.invoke(Method.java:616) ~[na:1.6.0_20]
at
org.identityconnectors.framework.impl.api.local.operations.ConnectorAPIOperationRunnerProxy.invoke(ConnectorAPIOperationRunnerProxy.java:
93) [framework-internal-1.3.1.jar:na]
at $Proxy122.search(Unknown Source) [na:na]
at
org.identityconnectors.framework.impl.api.local.operations.GetImpl.getObject(GetImpl.java:
65) [framework-internal-1.3.1.jar:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:
1.6.0_20]
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
57) ~[na:1.6.0_20]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
43) ~[na:1.6.0_20]
at java.lang.reflect.Method.invoke(Method.java:616) ~[na:1.6.0_20]
at
org.identityconnectors.framework.impl.api.local.operations.ThreadClassLoaderManagerProxy.invoke(ThreadClassLoaderManagerProxy.java:
107) [framework-internal-1.3.1.jar:na]
at $Proxy123.getObject(Unknown Source) [na:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:
1.6.0_20]
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
57) ~[na:1.6.0_20]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
43) ~[na:1.6.0_20]
at java.lang.reflect.Method.invoke(Method.java:616) ~[na:1.6.0_20]
at
org.identityconnectors.framework.impl.api.DelegatingTimeoutProxy.invoke(DelegatingTimeoutProxy.java:
107) [framework-internal-1.3.1.jar:na]
at $Proxy123.getObject(Unknown Source) [na:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:
1.6.0_20]
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
57) ~[na:1.6.0_20]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
43) ~[na:1.6.0_20]
at java.lang.reflect.Method.invoke(Method.java:616) ~[na:1.6.0_20]
at
org.identityconnectors.framework.impl.api.LoggingProxy.invoke(LoggingProxy.java:
76) [framework-internal-1.3.1.jar:na]
at $Proxy123.getObject(Unknown Source) [na:na]
at
org.identityconnectors.framework.impl.api.AbstractConnectorFacade.getObject(AbstractConnectorFacade.java:
227) [framework-internal-1.3.1.jar:na]
at
org.syncope.core.persistence.propagation.ConnectorFacadeProxy.getObject(ConnectorFacadeProxy.java:
376) [ConnectorFacadeProxy.class:na]
at
org.syncope.core.persistence.propagation.PropagationManager.propagate(PropagationManager.java:
610) [PropagationManager.class:na]
at
org.syncope.core.persistence.propagation.PropagationManager.provision(PropagationManager.java:
258) [PropagationManager.class:na]
at
org.syncope.core.persistence.propagation.PropagationManager.update(PropagationManager.java:
185) [PropagationManager.class:na]
at
org.syncope.core.rest.controller.UserController.update(UserController.java:
618) [UserController.class:na]
at
org.syncope.core.rest.controller.UserController.update(UserController.java:
638) [UserController.class:na]

[Francesco Chicchiriccò]

On 02/09/2011 10:52, renemulder wrote:
> Hi All,
>
> We're using the database connector version 1.2.1 to provision users to a database. We have an example project based on HASQL where all seems to be working fine.
>
> However, in our project when we use Postgres, there seems to be an issue with column quotation.
>
> The configuration 'Name Quoting' is set to 'double' and this seems to work except for a select statement when checking whether a user already exists (search).
>
> From the log i found that the action resolves to the following query
>
> SELECT "USER_ID" , "ILS_BIBLIOTHEEK" , "CRM_WACHTWOORD" ,
> "CRM_EMAIL" , "ILS_PASNUMMER" , "ILS_WACHTWOORD" , "CRM_OPENID" FROM
> "USER_PROFILE" WHERE USER_ID = ?
>
> If i run this on the database directly, i indeed get the same error message:
>
> Error: ERROR: column "user_id" does not exist
> SQLState: 42703
> ErrorCode: 0
>
> The error is in the final reference to USER_ID. This must be quoted as well
>
> SELECT "USER_ID" , "ILS_BIBLIOTHEEK" , "CRM_WACHTWOORD" ,
> "CRM_EMAIL" , "ILS_PASNUMMER" , "ILS_WACHTWOORD" , "CRM_OPENID" FROM
> "USER_PROFILE" WHERE "USER_ID" = ?

Hi Rene,
PostgreSQL is not supported by the current version of DatabaseTable
connector (see [1]).

However, it should not be too much hard to add PostgreSQL support (based
on your suggestions and some use cases we already had about this): I've
created an issue [2] for this.

Regards.

[1]
http://code.google.com/p/connid/wiki/DatabaseTable#Supported_Resource_Versions
[2] http://code.google.com/p/connid/issues/detail?id=13

--
Francesco Chicchiricc�

"Computer Science is no more about computers than astronomy
is about telescopes." (E. W. Dijkstra)

[René Mulder]

Hi,

I added support for quotation in the where clause (it follows the configuration now). I'm not sure whether there are other bugs when using it with postgres, but I haven't come across them yet.

I'm sorry for not writing unit tests yet, i've tested this in our environment and I can now update/create/delete users.

It's based on the 1.2.1 tag

(i hope the attachment "diff" shows up in the groups)

Regards,

René

2011/9/2 Francesco Chicchiriccò <chicch...@gmail.com>

--
Francesco Chicchiriccò

[Francesco Chicchiriccò]

On 02/09/2011 15:10, Ren� Mulder wrote:
> Hi,
>
> I added support for quotation in the where clause (it follows the
> configuration now). I'm not sure whether there are other bugs when
> using it with postgres, but I haven't come across them yet.
>
> I'm sorry for not writing unit tests yet, i've tested this in our
> environment and I can now update/create/delete users.
>
> It's based on the 1.2.1 tag
>
> (i hope the attachment "diff" shows up in the groups)

Hi Rene,
I've added your patch to the issue [1], thank you very much.

Hummmm, wouldn't you like to provide some unit tests as well so that we
can soon release a 1.3 version including PostgreSQL support? ;-)

Regards.

[1] http://code.google.com/p/connid/issues/detail?id=13

--
Francesco Chicchiricc�