Hi,
I have created a rpt file using crystal report plugin for eclipse.
This report uses 3 Tables. Works fine. but when i want to push a java sql resultset as the datasource the result isn't good, only get one row.
( I need to push resultset because i need to dinamically manage the where clause of my query. the where clause might be linked to each of the three tables)
Here's my jsp code.
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<%@ page import="java.io.ByteArrayInputStream"%>
<%@ page import="java.io.IOException"%>
<%@ page import="java.sql.*"%>
<%@ page import="com.crystaldecisions.sdk.occa.report.lib.*"%>
<%@ page import="com.crystaldecisions.sdk.occa.report.application.ParameterFieldController"%>
<%@ page import="com.crystaldecisions.sdk.occa.report.application.ReportClientDocument"%>
<%@ page import="com.crystaldecisions.sdk.occa.report.exportoptions.ReportExportFormat"%>
<%@ page import="com.crystaldecisions.sdk.occa.report.data.Tables"%>
<%
/* Connection à la base de données */
Context ctx = new InitialContext();
Context envCtx = (Context)ctx.lookup("java:comp/env");
DataSource ds = (DataSource)envCtx.lookup("MYDB");
Connection connection = ds.getConnection();
/* Requète SQL sur mes 3 tables */
String query="SELECT TABLE1.KEY_TABLE1, TABLE1.LIB_TABLE1, TABLE2.LIB_TABLE2, TABLE3.LIB_TABLE3 ";
query+=" FROM TABLE1 ";
query+=" LEFT OUTER JOIN TABLE2 ON TABLE1.KEY_TABLE2=TABLE2.KEY_TABLE2 ";
query+=" LEFT OUTER JOIN TABLE3 TABLE3 ON TABLE2.KEY_TABLE3=TABLE3.KEY_TABLE3";
query+=" WHERE TABLE1.KEY_TABLE1 <> 'KEY3'";
/* Execution et recuperation du resultset */
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet res = stmt.executeQuery(query);
/* Creation d'un ReportClientDocument */
ReportClientDocument reportClientDocument = new ReportClientDocument();
reportClientDocument.setReportAppServer(ReportClientDocument.inprocConnectionString);
/* Ouverture du rapport */
reportClientDocument.open("/report/TEST.rpt", 0);
Tables tables=reportClientDocument.getDatabaseController().getDatabase().getTables();
reportClientDocument.getDatabaseController().setDataSource(res, tables.getTable(2).getAlias() , tables.getTable(2).getAlias());
reportClientDocument.getDatabaseController().setDataSource(res, tables.getTable(1).getAlias() , tables.getTable(1).getAlias());
reportClientDocument.getDatabaseController().setDataSource(res, tables.getTable(0).getAlias() , tables.getTable(0).getAlias());
/* Export en PDF */
ByteArrayInputStream byteArrayInputStream = (ByteArrayInputStream) reportClientDocument.getPrintOutputController().export(ReportExportFormat.PDF);
/* Fermeture de la connection db */
connection.close();
/* Export du flux PDF vers le navigateur */
response.reset();
response.setHeader("Content-disposition", "inline;filename=crreport.pdf");
response.setContentType("application/pdf");
int bytesRead;
byte[] byteArray = new byte[1024];
while((bytesRead = byteArrayInputStream.read(byteArray)) != -1)
{
response.getOutputStream().write(byteArray, 0, bytesRead);
}
response.getOutputStream().flush();
response.getOutputStream().close();
/* Fermeture du ReportClientDocument */
reportClientDocument.close();
%>
The point might be here
Tables tables=reportClientDocument.getDatabaseController().getDatabase().getTables(); reportClientDocument.getDatabaseController().setDataSource(res, tables.getTable(2).getAlias() , tables.getTable(2).getAlias()); reportClientDocument.getDatabaseController().setDataSource(res, tables.getTable(1).getAlias() , tables.getTable(1).getAlias()); reportClientDocument.getDatabaseController().setDataSource(res, tables.getTable(0).getAlias() , tables.getTable(0).getAlias());
if i only use
Tables tables=reportClientDocument.getDatabaseController().getDatabase().getTables(); reportClientDocument.getDatabaseController().setDataSource(res, tables.getTable(2).getAlias() , tables.getTable(2).getAlias());
i get an connection error message
same thing with any other combination like this
Tables tables=reportClientDocument.getDatabaseController().getDatabase().getTables(); reportClientDocument.getDatabaseController().setDataSource(res, tables.getTable(0).getAlias() , tables.getTable(0).getAlias()); reportClientDocument.getDatabaseController().setDataSource(res, tables.getTable(1).getAlias() , tables.getTable(1).getAlias()); reportClientDocument.getDatabaseController().setDataSource(res, tables.getTable(2).getAlias() , tables.getTable(2).getAlias());
do someone have any explanation?