Skip to Content
0
Former Member
Oct 23, 2009 at 08:05 AM

Java - SetDatasource - JOIN query

189 Views

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?