Buongiorno,
sto testando da qualche giorno CMDBUILD 2.1.6 e lo trovo abbastanza completo e flessibile anche in un'ottica multi-cliente, che si adatta al mio caso.
Tuttavia sto incontrando qualche difficoltà nel definire una View utilizzando una function SQL, nella fattispecie ottengo l'errore :
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. Il problema sembra essere nella traduzione della chiamata SQL, per selezionare il campo "Id", infatti :
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...Mentre la funzione restituisce risultato se invocata così :cmdb=# SELECT "Id" FROM "cmf_hosts_by_managedtype"() AS f limit 10;Id------1766161696199910091113100713119631097(10 rows)
Avete dei suggerimenti su come impostare la function o la chiamata in modo da rispettare la sintassi creata/voluta dal CMDBUILD durante la sua "traduzione" delle definizioni ?Di seguito la definizione della function.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$BEGINRETURN 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 VOLATILECOST 100ROWS 9000;
Grazie in anticipo, saluti,giuliano