I have created the following Classes and Domains
1. clsSupplier
2. clsContract
3. dmnSupplierContract (reference between clsSupplier and clsContract)
4. clsDocument
5. dmnSupplierDocument (reference between clsSupplier and clsDocument)
6. dmnContractDocument (reference between clsContract and clsDocument)
In clsDocument I have two important attributes attSupplier (reference to clsSupplier) and attContract (reference to clsContract). I want the user to select Supplier and then I would filter the relevant Contract for user selection. attSupplier is a normal attribute type reference to dmnSupplierDocument. While attContract is also a normal attribute type reference to dmnContractDocument but with CQL filter configured.
This is what my filter looked like.
from clsContract where Id in
(/(
select "Id" from "clsContract"
Where "Status"='A'
and "attListed"=1
and "attSupplier"={client:attSupplier}
)/)
The operation spit out a few error message box but the filter seems to be working in getting the right selection set. I include the error message below but upon scrutinizing the error I know for fact that the {client:attSupplier} variable was not sent to the query builder. Perhaps the query got executed even after it lost focus of the dataset and don't have the value any more.
This is the error message I get:
Call: services/json/management/modcard/getcardlistshort?_dc=1384765933714
--------------------------------------------------------------------------
Error: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [(
select "Id" from "clsContract"
Where "Status"='A' and "attListed"=1 and "attSupplier"=
)]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
Position: 102
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:459)
at org.cmdbuild.cql.facade.CQLAnalyzer.sqlQuery(CQLAnalyzer.java:530)
at org.cmdbuild.cql.facade.CQLAnalyzer.convert(CQLAnalyzer.java:508)
at org.cmdbuild.cql.facade.CQLAnalyzer.values(CQLAnalyzer.java:408)
at org.cmdbuild.cql.facade.CQLAnalyzer.handleSimpleField(CQLAnalyzer.java:297)
at org.cmdbuild.cql.facade.CQLAnalyzer.handleField(CQLAnalyzer.java:281)
at org.cmdbuild.cql.facade.CQLAnalyzer.handleWhereElement(CQLAnalyzer.java:250)
at org.cmdbuild.cql.facade.CQLAnalyzer.init(CQLAnalyzer.java:222)
at org.cmdbuild.cql.facade.CQLAnalyzer.analyze(CQLAnalyzer.java:193)
at org.cmdbuild.cql.facade.CQLAnalyzer.analyze(CQLAnalyzer.java:169)
at org.cmdbuild.cql.facade.CQLFacade.compileAndAnalyze(CQLFacade.java:53)
at org.cmdbuild.logic.data.access.QuerySpecsBuilderFiller.fillQuerySpecsBuilderWithFilterOptions(QuerySpecsBuilderFiller.java:207)
at org.cmdbuild.logic.data.access.QuerySpecsBuilderFiller.create(QuerySpecsBuilderFiller.java:96)
at org.cmdbuild.logic.data.access.DataViewCardFetcher.fetch(DataViewCardFetcher.java:192)
Hello Zahri,
when you create a new card into clsDocument class, the value for the attribute attSupplier used in your CQL query is null. In this case the SQL query embedded into the CQL is not well formatted and thrown a PSQLException.
The solution is to cast the value of the attribute attSupplier to String (SQL varchar) like this:
from clsContract where Id in (/(
select "Id" from "clsContract"
and "attSupplier"::varchar='{client:attSupplier.Id}'
I suggest to always use the ".Id" notation in the CQL expression {client:...} to get the Id of the Reference Object.
Alternatively you can use the Metadata functionality as this:
FILTER = from clsContract {js:whereClauseContract}
now you can insert the key/value couple into the Metadata:
KEY = whereClauseContract
VALUE = parseInt({client:attSupplier.Id})?'where Id in (/(select "Id" from "clsContract" Where "Status"=/'A/' and "attListed"=1 and "attSupplier"='+parseInt({client:attSupplier.Id})+')/)':'where Id=0';
the whereClauseContract will be parsed as a JavaScript clause.
Best Regards
Alberto
Previously Zahri wrote:
I have created the following Classes and Domains
1. clsSupplier
2. clsContract
3. dmnSupplierContract (reference between clsSupplier and clsContract)
4. clsDocument
5. dmnSupplierDocument (reference between clsSupplier and clsDocument)
6. dmnContractDocument (reference between clsContract and clsDocument)
In clsDocument I have two important attributes attSupplier (reference to clsSupplier) and attContract (reference to clsContract). I want the user to select Supplier and then I would filter the relevant Contract for user selection. attSupplier is a normal attribute type reference to dmnSupplierDocument. While attContract is also a normal attribute type reference to dmnContractDocument but with CQL filter configured.
This is what my filter looked like.
from clsContract where Id in
(/(
select "Id" from "clsContract"
Where "Status"='A'
and "attListed"=1
and "attSupplier"={client:attSupplier}
)/)
The operation spit out a few error message box but the filter seems to be working in getting the right selection set. I include the error message below but upon scrutinizing the error I know for fact that the {client:attSupplier} variable was not sent to the query builder. Perhaps the query got executed even after it lost focus of the dataset and don't have the value any more.
This is the error message I get:
Call: services/json/management/modcard/getcardlistshort?_dc=1384765933714
--------------------------------------------------------------------------
Error: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [(
select "Id" from "clsContract"
Where "Status"='A' and "attListed"=1 and "attSupplier"=
)]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
Position: 102
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:459)
at org.cmdbuild.cql.facade.CQLAnalyzer.sqlQuery(CQLAnalyzer.java:530)
at org.cmdbuild.cql.facade.CQLAnalyzer.convert(CQLAnalyzer.java:508)
at org.cmdbuild.cql.facade.CQLAnalyzer.values(CQLAnalyzer.java:408)
at org.cmdbuild.cql.facade.CQLAnalyzer.handleSimpleField(CQLAnalyzer.java:297)
at org.cmdbuild.cql.facade.CQLAnalyzer.handleField(CQLAnalyzer.java:281)
at org.cmdbuild.cql.facade.CQLAnalyzer.handleWhereElement(CQLAnalyzer.java:250)
at org.cmdbuild.cql.facade.CQLAnalyzer.init(CQLAnalyzer.java:222)
at org.cmdbuild.cql.facade.CQLAnalyzer.analyze(CQLAnalyzer.java:193)
at org.cmdbuild.cql.facade.CQLAnalyzer.analyze(CQLAnalyzer.java:169)
at org.cmdbuild.cql.facade.CQLFacade.compileAndAnalyze(CQLFacade.java:53)
at org.cmdbuild.logic.data.access.QuerySpecsBuilderFiller.fillQuerySpecsBuilderWithFilterOptions(QuerySpecsBuilderFiller.java:207)
at org.cmdbuild.logic.data.access.QuerySpecsBuilderFiller.create(QuerySpecsBuilderFiller.java:96)
at org.cmdbuild.logic.data.access.DataViewCardFetcher.fetch(DataViewCardFetcher.java:192)
1 Like
Dear Alberto,
This procedure work very well in the Modify Card function. Thank you very much.
I have a follow on question though, The code seems to affect also in the field when included in the filter attribute when chosen as Input Parameter. When I try to run the filter, the combobox is empty. Perhaps because the system cannot find the attSupplier attribute in the filter set it only display a blank combobox. Is this by design or it is a bug.
Previously Tecnoteca wrote:
Hello Zahri,
when you create a new card into clsDocument class, the value for the attribute attSupplier used in your CQL query is null. In this case the SQL query embedded into the CQL is not well formatted and thrown a PSQLException.
The solution is to cast the value of the attribute attSupplier to String (SQL varchar) like this:
from clsContract where Id in (/(
select "Id" from "clsContract"
and "attSupplier"::varchar='{client:attSupplier.Id}'
I suggest to always use the ".Id" notation in the CQL expression {client:...} to get the Id of the Reference Object.
Alternatively you can use the Metadata functionality as this:
FILTER = from clsContract {js:whereClauseContract}
now you can insert the key/value couple into the Metadata:
KEY = whereClauseContract
VALUE = parseInt({client:attSupplier.Id})?'where Id in (/(select "Id" from "clsContract" Where "Status"=/'A/' and "attListed"=1 and "attSupplier"='+parseInt({client:attSupplier.Id})+')/)':'where Id=0';
the whereClauseContract will be parsed as a JavaScript clause.
Best Regards
Alberto