CMDBuild Forum

Add sql Data source

Old Versions
#1

hi,

 

how can i add a postgres function to Views -> from sql -> data source?

 

thanks.

#2
You can refer to the Administrator manual (see Views/Dashboards sections).
 
Best regards.
 
-- Davide Pavan
 
Previously Wilbur wrote:

hi,

 

how can i add a postgres function to Views -> from sql -> data source?

 

thanks.

 

#3
Hi, I saw in Adminstrator manual but the pages 47 to 49 are empty. Where can I find the instrutions?
 
Thanks!
#4

In the Administrator Manual, at pages 52-53

CMDBuild Team

#5

Previously Tecnoteca wrote:

In the Administrator Manual, at pages 52-53

CMDBuild Team

We have created our schema in postgresql.We created a function and view. But in (cmdBuild console ,FromSQL, we are not able to see our datasource. What are we missing? we have added postgres jar in tomcat. how should we identify that DB is getting connected?
#6

We can not know if your Postgres function is written correctly because you have not posted the sql creation script.

The first tip we can give you is to check that your function has a comment like:
 COMMENT ON FUNCTION your_function() IS 'TYPE: function';

The second tip is to go to the Administration Module and clear the cache (Setup - Management Server - Clear Cache), or you can restart Tomcat.

CMDBuild Team

#7
Hi
 
Im trying to create a view, and i wrote the database function as:
 
-- Function: cmf_active_cards_for_class()
 
-- DROP FUNCTION cmf_active_cards_for_class();
 
CREATE OR REPLACE FUNCTION cmf_enchufes_libres(OUT "Code" character varying, OUT "Description" character varying)
  RETURNS SETOF record AS
$BODY$
BEGIN
    RETURN QUERY 
EXECUTE 'SELECT "Code" as "Codi", "Description" as "Descripció"  FROM "ENCHUFE" ' ||
' WHERE "Id" NOT IN ' ||
' (SELECT "ENCHUFE" FROM "FUENTE" where "ENCHUFE" is not null ) ' ||
' and "Status" = ''A'' ORDER BY "Code" ';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION cmf_enchufes_libres()
  OWNER TO postgres;
COMMENT ON FUNCTION cmf_enchufes_libres() IS 'TYPE: function';
 
 
When I create a view using this datasource, I allways get the same error:
 
 
Call: services/json/viewmanagement/readsqlview?_dc=1391000962465
-----------------------------------------------------------------
Error: java.lang.NullPointerException
	at org.cmdbuild.logic.view.ViewLogic.isActive(ViewLogic.java:43)
	at org.cmdbuild.logic.view.ViewLogic.fetchViewsOfAllTypes(ViewLogic.java:33)
	at org.cmdbuild.logic.view.ViewLogic.read(ViewLogic.java:48)
	at org.cmdbuild.servlets.json.ViewManagement.readByType(ViewManagement.java:106)
	at org.cmdbuild.servlets.json.ViewManagement.readSQLView(ViewManagement.java:45)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	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:621)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.AuthFilter.doFilter(AuthFilter.java:142)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.PatchManagerFilter.doFilter(PatchManagerFilter.java:33)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.ConfCheckFilter.doFilter(ConfCheckFilter.java:31)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.TranslationFilter.doFilter(TranslationFilter.java:39)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
	at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:200)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:662)
 
 
 
Any aditional (than in page 52) information about how to do that ??
 
 
 
Previously Tecnoteca wrote:

We can not know if your Postgres function is written correctly because you have not posted the sql creation script.

The first tip we can give you is to check that your function has a comment like:
 COMMENT ON FUNCTION your_function() IS 'TYPE: function';

The second tip is to go to the Administration Module and clear the cache (Setup - Management Server - Clear Cache), or you can restart Tomcat.

CMDBuild Team

 

#8

We have checked your report.
It is a bug that was present in early versions 2.1 and has been fixed in version 2.1.5
Which version are you using?
CMDBuild Team

#9
Hy
 
I can not run this data source, please helpme with this example:
 
-- Function: cmf_APP_SOA()
 
-- DROP FUNCTION cmf_APP_SOA();
 
CREATE OR REPLACE FUNCTION cmf_APP_SOA (OUT "APP" integer, OUT "SOA" integer)
  RETURNS SETOF record AS
$BODY$
BEGIN
    RETURN QUERY EXECUTE
---------------------------------------------------------------------------------------- 
 
'SELECT  "IdObj1" AS "APP" , "IdObj2" AS "SOA" FROM "Map_ApplicationSOA" WHERE "Status" LIKE "A"';
 
-----------------------------------------------------------------------------------------
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION cmf_APP_SOA()
  OWNER TO postgres;
COMMENT ON FUNCTION cmf_APP_SOA() IS 'TYPE: function';
 
 
 
Previously Daniel wrote:
Hi
 
Im trying to create a view, and i wrote the database function as:
 
-- Function: cmf_active_cards_for_class()
 
-- DROP FUNCTION cmf_active_cards_for_class();
 
CREATE OR REPLACE FUNCTION cmf_enchufes_libres(OUT "Code" character varying, OUT "Description" character varying)
  RETURNS SETOF record AS
$BODY$
BEGIN
    RETURN QUERY 
EXECUTE 'SELECT "Code" as "Codi", "Description" as "Descripció"  FROM "ENCHUFE" ' ||
' WHERE "Id" NOT IN ' ||
' (SELECT "ENCHUFE" FROM "FUENTE" where "ENCHUFE" is not null ) ' ||
' and "Status" = ''A'' ORDER BY "Code" ';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION cmf_enchufes_libres()
  OWNER TO postgres;
COMMENT ON FUNCTION cmf_enchufes_libres() IS 'TYPE: function';
 
 
When I create a view using this datasource, I allways get the same error:
 
 
Call: services/json/viewmanagement/readsqlview?_dc=1391000962465
-----------------------------------------------------------------
Error: java.lang.NullPointerException
	at org.cmdbuild.logic.view.ViewLogic.isActive(ViewLogic.java:43)
	at org.cmdbuild.logic.view.ViewLogic.fetchViewsOfAllTypes(ViewLogic.java:33)
	at org.cmdbuild.logic.view.ViewLogic.read(ViewLogic.java:48)
	at org.cmdbuild.servlets.json.ViewManagement.readByType(ViewManagement.java:106)
	at org.cmdbuild.servlets.json.ViewManagement.readSQLView(ViewManagement.java:45)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	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:621)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.AuthFilter.doFilter(AuthFilter.java:142)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.PatchManagerFilter.doFilter(PatchManagerFilter.java:33)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.ConfCheckFilter.doFilter(ConfCheckFilter.java:31)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.TranslationFilter.doFilter(TranslationFilter.java:39)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
	at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:200)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:662)
 
 
 
Any aditional (than in page 52) information about how to do that ??
 
 
 
Previously Tecnoteca wrote:

We can not know if your Postgres function is written correctly because you have not posted the sql creation script.

The first tip we can give you is to check that your function has a comment like:
 COMMENT ON FUNCTION your_function() IS 'TYPE: function';

The second tip is to go to the Administration Module and clear the cache (Setup - Management Server - Clear Cache), or you can restart Tomcat.

CMDBuild Team

 

 

#10
Now works Ok for me, regards
 
-- Function: cmf_APP_SOA()
 
-- DROP FUNCTION cmf_APP_SOA();
 
CREATE OR REPLACE FUNCTION cmf_APP_SOA (OUT "Code" character varying, OUT "Application" character varying, OUT "Uri" character varying, OUT "Environment" character varying)
  RETURNS SETOF record AS
$BODY$
BEGIN
  
Return Query SELECT
 
"SOA"."Code" as "Code",
"Application"."Description" as "Application",
"SOA"."Description" as "Uri",
"Environment"."Description" as "Environment"
 
FROM "SOA", "Map_ApplicationSOA", "Application", "Environment"
 
WHERE
 
"SOA"."Id" = "Map_ApplicationSOA"."IdObj2"
AND "Application"."Id" = "Map_ApplicationSOA"."IdObj1"
AND "Application"."RfEnvironment" = "Environment"."Id"
AND "Application"."Status" = 'A' -- ESTADO ACTIVO
AND "Application"."RfEnvironment" = 1103; -- AMBIENTE
 
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION cmf_APP_SOA() OWNER TO postgres;
COMMENT ON FUNCTION cmf_APP_SOA() IS 'TYPE: function';
 
Previously Sebastian wrote:
Hy
 
I can not run this data source, please helpme with this example:
 
-- Function: cmf_APP_SOA()
 
-- DROP FUNCTION cmf_APP_SOA();
 
CREATE OR REPLACE FUNCTION cmf_APP_SOA (OUT "APP" integer, OUT "SOA" integer)
  RETURNS SETOF record AS
$BODY$
BEGIN
    RETURN QUERY EXECUTE
---------------------------------------------------------------------------------------- 
 
'SELECT  "IdObj1" AS "APP" , "IdObj2" AS "SOA" FROM "Map_ApplicationSOA" WHERE "Status" LIKE "A"';
 
-----------------------------------------------------------------------------------------
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION cmf_APP_SOA()
  OWNER TO postgres;
COMMENT ON FUNCTION cmf_APP_SOA() IS 'TYPE: function';
 
 
 
Previously Daniel wrote:
Hi
 
Im trying to create a view, and i wrote the database function as:
 
-- Function: cmf_active_cards_for_class()
 
-- DROP FUNCTION cmf_active_cards_for_class();
 
CREATE OR REPLACE FUNCTION cmf_enchufes_libres(OUT "Code" character varying, OUT "Description" character varying)
  RETURNS SETOF record AS
$BODY$
BEGIN
    RETURN QUERY 
EXECUTE 'SELECT "Code" as "Codi", "Description" as "Descripció"  FROM "ENCHUFE" ' ||
' WHERE "Id" NOT IN ' ||
' (SELECT "ENCHUFE" FROM "FUENTE" where "ENCHUFE" is not null ) ' ||
' and "Status" = ''A'' ORDER BY "Code" ';
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION cmf_enchufes_libres()
  OWNER TO postgres;
COMMENT ON FUNCTION cmf_enchufes_libres() IS 'TYPE: function';
 
 
When I create a view using this datasource, I allways get the same error:
 
 
Call: services/json/viewmanagement/readsqlview?_dc=1391000962465
-----------------------------------------------------------------
Error: java.lang.NullPointerException
	at org.cmdbuild.logic.view.ViewLogic.isActive(ViewLogic.java:43)
	at org.cmdbuild.logic.view.ViewLogic.fetchViewsOfAllTypes(ViewLogic.java:33)
	at org.cmdbuild.logic.view.ViewLogic.read(ViewLogic.java:48)
	at org.cmdbuild.servlets.json.ViewManagement.readByType(ViewManagement.java:106)
	at org.cmdbuild.servlets.json.ViewManagement.readSQLView(ViewManagement.java:45)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	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:621)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.AuthFilter.doFilter(AuthFilter.java:142)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.PatchManagerFilter.doFilter(PatchManagerFilter.java:33)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.ConfCheckFilter.doFilter(ConfCheckFilter.java:31)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.cmdbuild.filters.TranslationFilter.doFilter(TranslationFilter.java:39)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
	at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:200)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:662)
 
 
 
Any aditional (than in page 52) information about how to do that ??
 
 
 
Previously Tecnoteca wrote:

We can not know if your Postgres function is written correctly because you have not posted the sql creation script.

The first tip we can give you is to check that your function has a comment like:
 COMMENT ON FUNCTION your_function() IS 'TYPE: function';

The second tip is to go to the Administration Module and clear the cache (Setup - Management Server - Clear Cache), or you can restart Tomcat.

CMDBuild Team