Hi all,
i'm testing the 2.1.6 version of CMDBUILD from a while, and i think that is a very complete and powerful software, even in a multi-tenant environment.
Unfortunately i'm encountering some problems defining/configuring a View based on a SQL function
Here the Error with the stacktrace :
Call: services/json/management/modcard/getsqlcardlist?_dc=1390237885523
------------------------------------------------------------------------
Error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT *, (SELECT count(*) FROM (
SELECT
"cmf_hosts_by_managedtype"()."Id" FROM "cmf_hosts_by_managedtype"() AS f
WHERE TRUE ) AS main) AS _f__RowsCount FROM (
SELECT
f."SERIAL" AS "f#SERIAL", f."HOSTNAME" AS "f#HOSTNAME", f."MANAGED_TYPE" AS "f#MANAGED_TYPE", f."SERVICE_TYPE" AS "f#SERVICE_TYPE", f."MANUFACTURER" AS "f#MANUFACTURER", f."MODEL" AS "f#MODEL", f."OS" AS "f#OS", f."IS_DOMAIN_CONTROLLER" AS "f#IS_DOMAIN_CONTROLLER", f."FORWARD_TO_SYSLOG" AS "f#FORWARD_TO_SYSLOG", f."RAM_GB" AS "f#RAM_GB", f."CPU_NUM" AS "f#CPU_NUM", f."CPU_FREQ" AS "f#CPU_FREQ", f."CPU_TYPE" AS "f#CPU_TYPE", f."DISK_NUM" AS "f#DISK_NUM", f."DISK_SIZE" AS "f#DISK_SIZE", f."IS_VIRTUAL" AS "f#IS_VIRTUAL", f."IP_mgmt"::inet AS "f#IP_mgmt", f."IP_bck"::inet AS "f#IP_bck", f."IP_erogazione"::inet AS "f#IP_erogazione", f."IP_nat"::inet AS "f#IP_nat", f."IP_ilo"::inet AS "f#IP_ilo", f."IP_console_nat"::inet AS "f#IP_console_nat", f."AssetProperty" AS "f#AssetProperty", f."Description" AS "f#Description", f."NOTE" AS "f#NOTE", f."TEMPNOTE" AS "f#TEMPNOTE"
FROM "cmf_hosts_by_managedtype"() AS f
WHERE TRUE ) AS main
LIMIT 20
OFFSET 0]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
Position: 72
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:602)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:685)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:693)
at org.cmdbuild.dao.driver.postgres.EntryQueryCommand.run(EntryQueryCommand.java:70)
at org.cmdbuild.dao.driver.postgres.PostgresDriver.query(PostgresDriver.java:192)
at org.cmdbuild.dao.view.DBDataView.executeQuery(DBDataView.java:413)
at org.cmdbuild.dao.query.QuerySpecsBuilder.run(QuerySpecsBuilder.java:492)
at org.cmdbuild.logic.data.access.DefaultDataAccessLogic.fetchSQLCards(DefaultDataAccessLogic.java:462)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:196)
at com.sun.proxy.$Proxy102.fetchSQLCards(Unknown Source)
at org.cmdbuild.servlets.json.management.ModCard.getSQLCardList(ModCard.java:223)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.cmdbuild.servlets.JSONDispatcher.dispatch(JSONDispatcher.java:94)
at org.cmdbuild.servlets.JSONDispatcher.doGet(JSONDispatcher.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.cmdbuild.filters.AuthFilter.doFilter(AuthFilter.java:144)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.cmdbuild.filters.PatchManagerFilter.doFilter(PatchManagerFilter.java:33)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.cmdbuild.filters.ConfCheckFilter.doFilter(ConfCheckFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.cmdbuild.filters.TranslationFilter.doFilter(TranslationFilter.java:39)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:744)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "."
Position: 72
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:643)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
... 49 more Ma, eseguendo la function dal psql direttamente, la query stampa in risultati attesi.
It seems that the problem is related to the "translation" operated by CMDBUILD for the invocation of the SQL function.
In fact, if I call the function in PSQL in the same way CMDBUILD does, i get an error :
cmdb=# SELECT "cmf_hosts_by_managedtype"()."Id" FROM "cmf_hosts_by_managedtype"() AS f ;
ERROR: syntax error at or near "."
LINE 1: SELECT "cmf_hosts_by_managedtype"()."Id" FROM "cmf_hosts_b...
But, selecting only the "Id" field from the output, manually, does not give any error (so, from my point of view, the function is ok):
cmdb=# SELECT "Id" FROM "cmf_hosts_by_managedtype"() AS f limit 10;
Id
------
1766
1616
961
999
1009
1113
1007
1311
963
1097
(10 rows)
Does anyone could suggest me how to implement an SQL function ready to be called by CMDBUILD "View" feature or does anyone have a working sample ?
Following the SQL function definition :
CREATE OR REPLACE FUNCTION cmf_hosts_by_ManagedType(
OUT "Id" integer,
OUT "SERIAL" character varying,
OUT "HOSTNAME" character varying,
OUT "MANAGED_TYPE" character varying,
OUT "SERVICE_TYPE" character varying,
OUT "MANUFACTURER" character varying,
OUT "MODEL" character varying,
OUT "OS" character varying,
OUT "IS_DOMAIN_CONTROLLER" integer,
OUT "FORWARD_TO_SYSLOG" integer,
OUT "RAM_GB" integer,
OUT "CPU_NUM" integer,
OUT "CPU_FREQ" numeric(4,3),
OUT "CPU_TYPE" character varying,
OUT "DISK_NUM" integer,
OUT "DISK_SIZE" integer,
OUT "IS_VIRTUAL" integer,
OUT "IP_mgmt" inet,
OUT "IP_bck" inet,
OUT "IP_erogazione" inet,
OUT "IP_nat" inet,
OUT "IP_ilo" inet,
OUT "IP_console_nat" inet,
OUT "AssetProperty" character varying,
OUT "Description" character varying,
OUT "NOTE" character varying,
OUT "TEMPNOTE" text
)
RETURNS SETOF record AS
$BODY$
BEGIN
RETURN QUERY EXECUTE
' select "HOST1"."Id", '
' "HOST1"."SERIAL", '
' "HOST1"."HOSTNAME", '
' "LOOKUP1"."Description" AS "ManagedType", '
' "LOOKUP2"."Description" AS "ServiceType", '
' "LOOKUP3"."Description" AS "Manufacturer", '
' "LOOKUP4"."Description" AS "Model", '
' "LOOKUP5"."Description" AS "OS", '
' "HOST1"."IS_DOMAIN_CONTROLLER" AS "IsDomainController", '
' "HOST1"."FORWARD_TO_SYSLOG" AS "Forward to syslog", '
' "HOST1"."RAM_GB", '
' "HOST1"."CPU_NUM", '
' "HOST1"."CPU_FREQ", '
' "LOOKUP6"."Description" AS "CPU_TYPE", '
' "HOST1"."DISK_NUM", '
' "HOST1"."DISK_SIZE", '
' "HOST1"."IS_VIRTUAL", '
' "HOST1"."IP_mgmt", '
' "HOST1"."IP_bck", '
' "HOST1"."IP_erogazione", '
' "HOST1"."IP_nat", '
' "HOST1"."IP_ilo", '
' "HOST1"."IP_console_nat", '
' "LOOKUP7"."Description" AS "AssetProperty", '
' "HOST1"."Description", '
' "HOST1"."NOTE", '
' "HOST1"."TEMPNOTE" '
''
' from "HOST" as "HOST1" join '
' "LookUp" as "LOOKUP1" on "HOST1"."MANAGED_TYPE" = "LOOKUP1"."Id" and "LOOKUP1"."Type"= ' || quote_literal('ManagedType') || ' join '
' "LookUp" as "LOOKUP2" on "HOST1"."SERVICE_TYPE" = "LOOKUP2"."Id" and "LOOKUP2"."Type"= ' || quote_literal('ServiceType') || ' join '
' "LookUp" as "LOOKUP3" on "HOST1"."MANUFACTURER" = "LOOKUP3"."Id" and "LOOKUP3"."Type"= ' || quote_literal('Manufacturer') || ' join '
' "LookUp" as "LOOKUP4" on "HOST1"."MODEL" = "LOOKUP4"."Id" and "LOOKUP4"."Type"= ' || quote_literal('Model') || ' join '
' "LookUp" as "LOOKUP5" on "HOST1"."OS" = "LOOKUP5"."Id" and "LOOKUP5"."Type"= ' || quote_literal('OS') || ' join '
' "LookUp" as "LOOKUP6" on "HOST1"."CPU_TYPE" = "LOOKUP6"."Id" and "LOOKUP6"."Type"= ' || quote_literal('CpuType') || ' join '
' "LookUp" as "LOOKUP7" on "HOST1"."AssetProperty" = "LOOKUP7"."Id" and "LOOKUP7"."Type"= ' || quote_literal('AssetProperty') ||
' where "HOST1"."Status" = ' || quote_literal('A') ||
' AND "HOST1"."IdClass" not IN (SELECT _cm_subtables_and_itself(_cm_table_id(' || quote_literal('Activity') || ')))'
' ORDER BY "LOOKUP1"."Description" DESC, "LOOKUP2"."Description", "HOST1"."HOSTNAME" ';
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 9000;
THIS IS A POINTBACK to the italian forum : http://www.cmdbuild.org/forum/tecniche/318457997
Thanks in advance, best regards,
giuliano