Creating reports and dashboards | Stimulsoft community forum

Reporting tool and data analytics tools for creating reports and dashboards in ASP.NET, ASP.NET MVC, .NET Core, Blazor, Angular, PHP, Python, WPF, JavaScript, and Java applications.
https://forum.stimulsoft.com/

Oracle database connection

https://forum.stimulsoft.com/viewtopic.php?t=38416

Page 1 of 1

Oracle database connection

Posted: Wed May 28, 2014 3:55 pm
by aescot
Good afternoon,

I am evaluating your software in order to maybe use it.

I tried to connect Oracle 11g to your software in order to retrieve tables structures.

The connection to Oracle seems to be OK but i don't know how I have to use it then.

You can see my code below :

Code: Select all

 
String oracleConnection = "server=SERVEUR;database=SID;user=USER;password=PASSWORD;";
report.getDictionary().getDatabases().add(new StiOracleDatabase("OracleConnection", oracleConnection));

 StiOracleSource productDS = new StiOracleSource("OracleConnection", "PRODUCT", "PRODUCT", "SELECT * FROM PRODUCT");
 report.getDictionary().getDataSources().add(productDS);

 productDS.connect();
I have a NullPointerException so I think I have forgotten something.

Please, could you give me an exemple of how to use a SQL connection ?

Thanks for your helps.

Regards,

Alex

Re: Oracle database connection

Posted: Thu May 29, 2014 12:41 pm
by Vadim
Hello aescot.

Can you send us full stack trace of your error.
aescot wrote:Good afternoon,

I am evaluating your software in order to maybe use it.

I tried to connect Oracle 11g to your software in order to retrieve tables structures.

The connection to Oracle seems to be OK but i don't know how I have to use it then.

You can see my code below :

Code: Select all

 
String oracleConnection = "server=SERVEUR;database=SID;user=USER;password=PASSWORD;";
report.getDictionary().getDatabases().add(new StiOracleDatabase("OracleConnection", oracleConnection));

 StiOracleSource productDS = new StiOracleSource("OracleConnection", "PRODUCT", "PRODUCT", "SELECT * FROM PRODUCT");
 report.getDictionary().getDataSources().add(productDS);

 productDS.connect();
I have a NullPointerException so I think I have forgotten something.

Please, could you give me an exemple of how to use a SQL connection ?

Thanks for your helps.

Regards,

Alex

Re: Oracle database connection

Posted: Mon Jun 02, 2014 7:13 am
by aescot
Hello Vadim,

Thanks for your answer, I will give you the full stack trace. But do you know if I use it correctly ?

Because I haven't found documentations or examples of how make a connection to a database and use it in java. Please, could you give me a sample ?

You can see here the full stack trace

Code: Select all

java.lang.NullPointerException
	at com.stimulsoft.report.dictionary.dataSources.StiDataSource.getDatabase(StiDataSource.java:1052)
	at com.stimulsoft.report.dictionary.dataSources.StiDataStoreSource.connect(StiDataStoreSource.java:90)
	at org.apache.jsp.admin.partials.editReports_jsp$1.getEditedReport(org.apache.jsp.admin.partials.editReports_jsp:146)
	at com.stimulsoft.webdesigner.StiWebDesigner.getDesigner(StiWebDesigner.java:125)
	at com.stimulsoft.webdesigner.StiWebDesignerHelper.getWebDesigner(StiWebDesignerHelper.java:59)
	at com.stimulsoft.webdesigner.taglib.StiWebDesignerTag.doStartTag(StiWebDesignerTag.java:34)
	at org.apache.jsp.admin.partials.editReports_jsp._jspx_meth_stiwebdesigner_webdesigner_0(org.apache.jsp.admin.partials.editReports_jsp:285)
	at org.apache.jsp.admin.partials.editReports_jsp._jspService(org.apache.jsp.admin.partials.editReports_jsp:241)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:93)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:373)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:470)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:364)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:669)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:455)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:137)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:536)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1072)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:382)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1006)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:154)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
	at org.eclipse.jetty.server.Server.handle(Server.java:365)
	at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:485)
	at org.eclipse.jetty.server.AbstractHttpConnection.headerComplete(AbstractHttpConnection.java:926)
	at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.headerComplete(AbstractHttpConnection.java:988)
	at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:635)
	at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:235)
	at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82)
	at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:628)
	at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:52)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543)
	at java.lang.Thread.run(Thread.java:724)

Thanks for your helps.

Re: Oracle database connection

Posted: Mon Jun 02, 2014 7:24 am
by aescot
if i try to do it, I have not the same error. I don't think it is the good way to do it, I want to retrieve my colomn from my table Product and put into a datasource.

Code: Select all

String oracleConnection = "server=SERVEUR;database=SID;user=USER;password=PASSWORD;";
report.getDictionary().getDatabases().add(new StiOracleDatabase("OracleConnection", oracleConnection));

 StiOracleSource productDS = new StiOracleSource("OracleConnection", "PRODUCT", "PRODUCT", "SELECT * FROM PRODUCT");
 report.getDictionary().getDataSources().add(productDS);

report.getDictionary().getDatabases().get("OracleConnection").connect(productDS);

Code: Select all

com.stimulsoft.base.exception.StiException: StiJDBCDatabase [connectionString=server=SERVEUR;database=DB;user=USER;password=PASSWORD;, promptUserNameAndPassword=false, url=jdbc:oracle:thin:@SERVEUR:1521:DB, user=USER, password=PASSWORD, driver=oracle.jdbc.OracleDriver, other={}]
	at com.stimulsoft.report.dictionary.databases.StiJDBCDatabase.connect(StiJDBCDatabase.java:223)
	at org.apache.jsp.admin.partials.editReports_jsp$1.getEditedReport(org.apache.jsp.admin.partials.editReports_jsp:150)
	at com.stimulsoft.webdesigner.StiWebDesigner.getDesigner(StiWebDesigner.java:125)
	at com.stimulsoft.webdesigner.StiWebDesignerHelper.getWebDesigner(StiWebDesignerHelper.java:59)
	at com.stimulsoft.webdesigner.taglib.StiWebDesignerTag.doStartTag(StiWebDesignerTag.java:34)
	at org.apache.jsp.admin.partials.editReports_jsp._jspx_meth_stiwebdesigner_webdesigner_0(org.apache.jsp.admin.partials.editReports_jsp:285)
	at org.apache.jsp.admin.partials.editReports_jsp._jspService(org.apache.jsp.admin.partials.editReports_jsp:241)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:93)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:373)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:470)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:364)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:669)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:455)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:137)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:536)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1072)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:382)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1006)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:154)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
	at org.eclipse.jetty.server.Server.handle(Server.java:365)
	at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:485)
	at org.eclipse.jetty.server.AbstractHttpConnection.headerComplete(AbstractHttpConnection.java:926)
	at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.headerComplete(AbstractHttpConnection.java:988)
	at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:635)
	at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:235)
	at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82)
	at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:628)
	at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:52)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543)
	at java.lang.Thread.run(Thread.java:724)
Caused by: java.lang.NullPointerException
	at com.stimulsoft.report.dictionary.dataSources.StiDataSource.setDataTable(StiDataSource.java:1487)
	at com.stimulsoft.report.dictionary.databases.StiJDBCDatabase.fillTable(StiJDBCDatabase.java:242)
	at com.stimulsoft.report.dictionary.databases.StiJDBCDatabase.connect(StiJDBCDatabase.java:221)
	... 37 more

Re: Oracle database connection

Posted: Mon Jun 02, 2014 10:07 am
by Vadim
Hello aescot.
You should not use .connect() method independently. Connection to DB will happen automatically when report render.

Re: Oracle database connection

Posted: Mon Jun 02, 2014 11:48 am
by aescot
Hello Vadim,

Thanks for your answer. Ok i will delete this connect() method from my code.

But do you know why my datasource object does not containt columns of my product table ? Why datasource does not retrieve automatically associated column of my SQL request ?

In my report I have my Product datasource but there is no associated columns.

You can see here the code :

Code: Select all

 public void onNewReportTemplate(StiReport report, HttpServletRequest request) {

            //create database
            String oracleConnection = "server=SERVER;database=DB;user=USER;password=PASSWORD;";
            report.getDictionary().getDatabases().add(new StiOracleDatabase("OracleConnection", oracleConnection));

            //create datasource for this database
            StiOracleSource productDS = new StiOracleSource("OracleConnection", "PRODUCT", "PRODUCT", "SELECT * FROM PRODUCT");
            report.getDataSources().add(productDS);

            System.out.println(report.getDataSources().get("PRODUCT").getColumns().size());          
}


In this code, the size of the columns array is 0.


if a create manually my columns like this, it works fine. But colums are not automatically retrieved...Please, Is there a way to do it ?

Code: Select all

StiOracleSource productDS = new StiOracleSource("OracleConnection", "PRODUCT", "PRODUCT", "SELECT * FROM PRODUCT");
productDS.setDictionary(report.getDictionary());
report.getDataSources().add(productDS);
productDS.columns.add(new StiDataColumn("PRICE", "PRICE", StiSystemType.getSystemType("System.Int32")));
productDS.columns.add(new StiDataColumn("STATUS", "STATUS", StiSystemType.getSystemType("System.Int32")));
productDS.columns.add(new StiDataColumn("WEIGHT", "WEIGHT", StiSystemType.getSystemType("System.Int32")));
Thanks for your helps.

Re: Oracle database connection

Posted: Tue Jun 03, 2014 7:28 am
by Vadim
Hello aescot.
Here is example (you can see it in \Samples\webdesigner\index.jsp):

Code: Select all

                                    report.getDictionary().getDatabases().add(new StiXmlDatabase("Demo", xsdPath, xmlPath));
			            try{
			                StiXmlTableFildsRequest tables = StiDataColumnsUtil.parceXSDSchema(new FileInputStream(xsdPath));
			                for (StiXmlTable table : tables.getTables()){
			                    StiDataTableSource tableSource = new StiDataTableSource("Demo." + table.getName(), table.getName(), table.getName());
			                    tableSource.setColumns(new StiDataColumnsCollection());
			                    for (StiSqlField field : table.getColumns()){
			                        StiDataColumn column = new StiDataColumn(field.getName(), field.getName(), field.getSystemType());
			                        tableSource.getColumns().add(column);
			                    }
			                    tableSource.setDictionary(report.getDictionary());
			                    report.getDictionary().getDataSources().add(tableSource);   
			                }	                
			            } catch (Exception e){
			             	e.printStackTrace();   
			            }
aescot wrote:Hello Vadim,

Thanks for your answer. Ok i will delete this connect() method from my code.

But do you know why my datasource object does not containt columns of my product table ? Why datasource does not retrieve automatically associated column of my SQL request ?

In my report I have my Product datasource but there is no associated columns.

You can see here the code :

Code: Select all

 public void onNewReportTemplate(StiReport report, HttpServletRequest request) {

            //create database
            String oracleConnection = "server=SERVER;database=DB;user=USER;password=PASSWORD;";
            report.getDictionary().getDatabases().add(new StiOracleDatabase("OracleConnection", oracleConnection));

            //create datasource for this database
            StiOracleSource productDS = new StiOracleSource("OracleConnection", "PRODUCT", "PRODUCT", "SELECT * FROM PRODUCT");
            report.getDataSources().add(productDS);

            System.out.println(report.getDataSources().get("PRODUCT").getColumns().size());          
}


In this code, the size of the columns array is 0.


if a create manually my columns like this, it works fine. But colums are not automatically retrieved...Please, Is there a way to do it ?

Code: Select all

StiOracleSource productDS = new StiOracleSource("OracleConnection", "PRODUCT", "PRODUCT", "SELECT * FROM PRODUCT");
productDS.setDictionary(report.getDictionary());
report.getDataSources().add(productDS);
productDS.columns.add(new StiDataColumn("PRICE", "PRICE", StiSystemType.getSystemType("System.Int32")));
productDS.columns.add(new StiDataColumn("STATUS", "STATUS", StiSystemType.getSystemType("System.Int32")));
productDS.columns.add(new StiDataColumn("WEIGHT", "WEIGHT", StiSystemType.getSystemType("System.Int32")));
Thanks for your helps.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited