Bonjour,
Je m'arrache les cheveux depuis quelques jours sur ce problème que je n'arrives pas à résoudre (en tout cas proprement), je rajoute un transformer sur la requête de mon combi, lequel génère une requête correcte mais lors de son execution, j'ai une erreur car dans la requête est ajouté un
qui m'empêche d'utiliser DISTINCT ou GROUP BY ainsi qu'une condition
dont je ne sais d'où elle sors et qui renvoi toujours FALSE.
Code : Tout sélectionner
// à chaque changement de client, on ré-initialise le combo avec les sites du client
comboClient.addModelListener("wantedID", new PropertyChangeListener() {
@Override
public void propertyChange(PropertyChangeEvent evt) {
int wantedID = comboClient.getWantedID();
if (wantedID != SQLRow.NONEXISTANT_ID && wantedID >= SQLRow.MIN_VALID_ID) {
final SQLRow rowClient = getTable().getForeignTable("ID_CLIENT").getRow(wantedID);
/*Where clause = new Where(comboSite.getRequest().getPrimaryTable().getField("ID_CLIENT"), "=", rowClient.getID());
Where clause2 = new Where(comboSite.getRequest().getPrimaryTable().getField("ID_CLIENT_GERANT"), "=", rowClient.getID());
SQLElement managersElement = Configuration.getInstance().getDirectory().getElement("GESTIONNAIRE_SITE");
SQLTable managersTable = managersElement.getListRequest().getPrimaryTable();
Where clause3 = new Where(managersTable.getField("ID_CLIENT_MANAGER"), "=", rowClient.getID());
Where clause4 = new Where(comboSite.getRequest().getPrimaryTable().getField("ID_CLIENT"), "=", managersTable.getField("ID_CLIENT_SITES"));
Where clause5 = new Where(comboSite.getRequest().getPrimaryTable().getField("ID_CLIENT"), "<>", rowClient.getID());
comboSite.getRequest().setWhere(clause.or(clause2).or(clause3.and(clause4)));
*/
comboSite.getRequest().setSelectTransf(new ITransformer<SQLSelect, SQLSelect>() {
@Override
public SQLSelect transformChecked(SQLSelect sel) {
SQLElement managersElement = Configuration.getInstance().getDirectory().getElement("GESTIONNAIRE_SITE");
SQLTable managersTable = managersElement.getListRequest().getPrimaryTable();
SQLTable siteTable = comboSite.getRequest().getPrimaryTable();
TableRef aTableRef = new AliasedTable(managersTable, "g");
Where joinClause = new Where(siteTable.getField("ID_CLIENT"), "=", aTableRef.getField("ID_CLIENT_SITES"));
final SQLSelectJoin join = sel.addJoin("INNER", aTableRef, joinClause);
Where w = new Where(siteTable.getField("ID_CLIENT"), "=", rowClient.getID());
w = w.or(new Where(siteTable.getField("ID_CLIENT_GERANT"), "=", rowClient.getID()));
w = w.or(new Where(join.getJoinedTable().getField("ID_CLIENT_MANAGER"), "=", rowClient.getID()));
join.setWhere(w);
sel.setDistinct(true);
System.err.println(sel.asString());
return sel;
}
});
} else {
comboSite.getRequest().setWhere(null);
}
}
});
Et l'erreur qui en résulte:
Code : Tout sélectionner
SELECT DISTINCT "SITE_CLIENT"."ID", "SITE_CLIENT"."NUMERO", "SITE_CLIENT"."CODE_IMPLANT", "SITE_CLIENT"."NOM", "SITE_CLIENT"."ORDRE"
FROM "OpenConcerto"."OpenConcerto48"."SITE_CLIENT"
INNER JOIN "OpenConcerto"."OpenConcerto48"."GESTIONNAIRE_SITE" "g" on ((("SITE_CLIENT"."ID_CLIENT" = "g"."ID_CLIENT_SITES") AND ("g"."ARCHIVE" = 0)) AND ("g"."ID" <> -1)) AND ((("SITE_CLIENT"."ID_CLIENT" = 2) OR ("SITE_CLIENT"."ID_CLIENT_GERANT" = 2)) OR ("g"."ID_CLIENT_MANAGER" = 2))
WHERE ("SITE_CLIENT"."ID" <> 1) AND ("SITE_CLIENT"."ARCHIVE" = 0)
ORDER BY "SITE_CLIENT"."ORDRE"
java.util.concurrent.ExecutionException: java.lang.IllegalStateException: Impossible d'accéder au résultat de SELECT DISTINCT "SITE_CLIENT"."ID", "SITE_CLIENT"."NUMERO", "SITE_CLIENT"."CODE_IMPLANT", "SITE_CLIENT"."NOM", "SITE_CLIENT"."ORDRE"
FROM "OpenConcerto"."OpenConcerto48"."SITE_CLIENT"
INNER JOIN "OpenConcerto"."OpenConcerto48"."GESTIONNAIRE_SITE" "g" on ((("SITE_CLIENT"."ID_CLIENT" = "g"."ID_CLIENT_SITES") AND ("g"."ARCHIVE" = 0)) AND ("g"."ID" <> -1)) AND ((("SITE_CLIENT"."ID_CLIENT" = 2) OR ("SITE_CLIENT"."ID_CLIENT_GERANT" = 2)) OR ("g"."ID_CLIENT_MANAGER" = 2))
WHERE ("SITE_CLIENT"."ID" <> 1) AND (("SITE_CLIENT"."ARCHIVE" = 0) AND (1=0))
ORDER BY "SITE_CLIENT"."ORDRE" FOR SHARE OF "SITE_CLIENT"
in jdbc:postgresql://127.0.0.1:5432/OpenConcerto
at java.util.concurrent.FutureTask$Sync.innerGet(Unknown Source)
at java.util.concurrent.FutureTask.get(Unknown Source)
at org.openconcerto.utils.SwingWorker2.get(SwingWorker2.java:412)
at org.openconcerto.sql.sqlobject.IComboModel$4.done(IComboModel.java:351)
at org.openconcerto.utils.SwingWorker2$5.run(SwingWorker2.java:536)
at org.openconcerto.utils.SwingWorker2$DoSubmitAccumulativeRunnable.run(SwingWorker2.java:619)
at sun.swing.AccumulativeRunnable.run(Unknown Source)
at org.openconcerto.utils.SwingWorker2$DoSubmitAccumulativeRunnable.actionPerformed(SwingWorker2.java:631)
at javax.swing.Timer.fireActionPerformed(Unknown Source)
at javax.swing.Timer$DoPostEvent.run(Unknown Source)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$400(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.IllegalStateException: Impossible d'accéder au résultat de SELECT DISTINCT "SITE_CLIENT"."ID", "SITE_CLIENT"."NUMERO", "SITE_CLIENT"."CODE_IMPLANT", "SITE_CLIENT"."NOM", "SITE_CLIENT"."ORDRE"
FROM "OpenConcerto"."OpenConcerto48"."SITE_CLIENT"
INNER JOIN "OpenConcerto"."OpenConcerto48"."GESTIONNAIRE_SITE" "g" on ((("SITE_CLIENT"."ID_CLIENT" = "g"."ID_CLIENT_SITES") AND ("g"."ARCHIVE" = 0)) AND ("g"."ID" <> -1)) AND ((("SITE_CLIENT"."ID_CLIENT" = 2) OR ("SITE_CLIENT"."ID_CLIENT_GERANT" = 2)) OR ("g"."ID_CLIENT_MANAGER" = 2))
WHERE ("SITE_CLIENT"."ID" <> 1) AND (("SITE_CLIENT"."ARCHIVE" = 0) AND (1=0))
ORDER BY "SITE_CLIENT"."ORDRE" FOR SHARE OF "SITE_CLIENT"
in jdbc:postgresql://127.0.0.1:5432/OpenConcerto
at org.openconcerto.sql.model.SQLDataSource.execute(SQLDataSource.java:640)
at org.openconcerto.sql.model.SQLDataSource.execute(SQLDataSource.java:545)
at org.openconcerto.sql.model.SQLRowValuesListFetcher.fetch(SQLRowValuesListFetcher.java:1217)
at org.openconcerto.sql.model.SQLRowValuesListFetcher.fetch(SQLRowValuesListFetcher.java:1150)
at org.openconcerto.sql.model.SQLRowValuesListFetcher.fetch(SQLRowValuesListFetcher.java:1132)
at org.openconcerto.sql.request.ComboSQLRequest.getComboItems(ComboSQLRequest.java:317)
at org.openconcerto.sql.request.ComboSQLRequest.getComboItems(ComboSQLRequest.java:293)
at org.openconcerto.sql.sqlobject.IComboModel$4.doInBackground(IComboModel.java:333)
at org.openconcerto.sql.sqlobject.IComboModel$4.doInBackground(IComboModel.java:1)
at org.openconcerto.utils.SwingWorker2$1.call(SwingWorker2.java:115)
at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at org.openconcerto.utils.SwingWorker2.run(SwingWorker2.java:163)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: second exec failed: ERREUR: FOR SHARE n'est pas autorisé avec la clause DISTINCT
at org.openconcerto.sql.model.SQLDataSource.executeTwice(SQLDataSource.java:965)
at org.openconcerto.sql.model.SQLDataSource.execute(SQLDataSource.java:608)
... 15 more
Caused by: org.postgresql.util.PSQLException: ERREUR: FOR SHARE n'est pas autorisé avec la clause DISTINCT
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:395)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.openconcerto.sql.model.SQLDataSource$ExecutorThread.run(SQLDataSource.java:1157)
Qqn peux t'il me donner un petit coup de main, j'aimerais faire de manière propre plutôt quel méthode que j'utilise actuellement.
Merci.