cancel
Showing results for 
Search instead for 
Did you mean: 

Jdbc Connection And Crystal Reports

Former Member
0 Kudos

Hi there;

A tipical jdbc connection , that i found in my "googles" was something like this;

PropertyBag connInfoProperties = new PropertyBag();

connInfoProperties.putBooleanValue("Trusted_Connection", false);

connInfoProperties.put("PreQEServerName", SERVICE);

connInfoProperties.put("Server Type", "Oracle Server");

connInfoProperties.put("Database DLL", "crdb_oracle.dll");

connInfoProperties.put("ServerName", "");

connInfoProperties.put("Server", SERVICE);

And so - one .....

But What I'm looking for is some thing is about passing one connection aready stabilished ; I mean , I want to prepare my connection envirorment before I pass it to crystal;

Some thing like this connection "getConnection" result function; how can i pass it into crystal report?

final class GetConnection {

/** Uses JNDI and Datasource (preferred style). */

static Connection getJNDIConnection(){

String DATASOURCE_CONTEXT = "java:comp/env/jdbc/blah";

Connection result = null;

try {

Context initialContext = new InitialContext();

if ( initialContext == null){

log("JNDI problem. Cannot get InitialContext.");

}

DataSource datasource = (DataSource)initialContext.lookup(DATASOURCE_CONTEXT);

if (datasource != null) {

result = datasource.getConnection();

}

else {

log("Failed to lookup datasource.");

}

}

catch ( NamingException ex ) {

log("Cannot get connection: " + ex);

}

catch(SQLException ex){

log("Cannot get connection: " + ex);

}

return result;

}

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hello,

Have you created your report in CR Designer first and set the path or copied your JDBC drivers JAR files into the Class path?

Also, you set your driver to our Native Oracle dll, you should be setting it to crdb_jdbc.dll.

Try using Cr Designer first to set location to your JDBC driver. Then use the same setting in code and it should work for you.

Also need to know what version of CR and which SDK you are using?

Thank you

Don

Edited by: Don Williams on Dec 16, 2010 7:45 AM

Former Member
0 Kudos

>

> Hello,

>

> Have you created your report in CR Designer first and set the path or copied your JDBC drivers JAR files into the Class path?

>

> Also, you set your driver to our Native Oracle dll, you should be setting it to crdb_jdbc.dll.

>

> Try using Cr Designer first to set location to your JDBC driver. Then use the same setting in code and it should work for you.

>

> Also need to know what version of CR and which SDK you are using?

>

> Thank you

> Don

>

> Edited by: Don Williams on Dec 16, 2010 7:45 AM

Thanks , for your Reply

My CR Designer version is 10, and my cr Viewer is 12 ( I think that is ok as the viewer is greater than the disginer) ;

My CR viewer is crjava-runtime_12.2.208;

The Designer, I use oleDb to connect , that`s ok. works fine;

In my application code, I want to replace or at least I want to get the connection transaction control , before the report begins, as I can run one procedure passing some database's session parameters ;

That's because , I'm using parameterized views, so those views "reads" some database session parameters; and filters the correct's rows to the report;

Many Thanks Again Don;

0 Kudos

I don't recall now if CR 10 support JDBC drivers. If not then what you are doing, or the way your are doing it won't work. If the reports are designed off of the native Oracle driver you have to use the ReplaceConnection method in code. This is what it looks like to set location to an XML file source:

private void XMLDBSource_Click(object sender, EventArgs e)

{

CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

ISCDReportClientDocument rcd;

rcd = rptClientDoc;

//string connString = "Provider=SQLOLEDB;Data Source=vancsdb02.crystald.net;Database=Northwind;User ID=devtech;Password=devtech";

string connString = "Provider=SQLOLEDB;Data Source=dwcb12003;Database=xtreme;User ID=sb;Password=1Oem2000";

string sqlString = "Select * From Orders";

//string sqlString = "SELECT 'Orders'.'Customer ID', 'Orders'.'Order Date' FROM 'xtreme'.'dbo'.'Orders' 'Orders'";

//string sqlString2 = "Select * From \"Orders Detail\"";

OleDbConnection oleConn = new OleDbConnection(connString);

OleDbDataAdapter oleAdapter = new OleDbDataAdapter(sqlString, oleConn);

//OleDbDataAdapter oleAdapter2 = new OleDbDataAdapter(sqlString2, oleConn);

DataTable dt1 = new DataTable("Orders");

//DataTable dt2 = new DataTable("\"Orders Detail\"");

oleAdapter.Fill(dt1);

//oleAdapter2.Fill(dt2);

System.Data.DataSet ds = new System.Data.DataSet();

ds.Tables.Add(dt1);

//ds.Tables.Add(dt2);

//ds.WriteXml("c:
CrystalReports
sc.xml");

ds.WriteXml("c:
CrystalReports
sc.xml", XmlWriteMode.WriteSchema);

//Create a new Database Table to replace the reports current table.

CrystalDecisions.ReportAppServer.DataDefModel.Table boTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();

Edited by: Don Williams on Dec 16, 2010 10:54 AM

0 Kudos

//boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object

PropertyBag boMainPropertyBag = new PropertyBag();

//boInnerPropertyBag: These hold the attributes for the QE_LogonProperties

//In the main property bag (boMainPropertyBag)

PropertyBag boInnerPropertyBag = new PropertyBag();

//Set the attributes for the boInnerPropertyBag

boInnerPropertyBag.Add("File Path ", @"C:\CrystalReports\sc.xml");

boInnerPropertyBag.Add("Internal Connection ID", "{680eee31-a16e-4f48-8efa-8765193dccdd}");

//Set the attributes for the boMainPropertyBag

boMainPropertyBag.Add("Database DLL", "crdb_adoplus.dll");

boMainPropertyBag.Add("QE_DatabaseName", "");

boMainPropertyBag.Add("QE_DatabaseType", "");

//Add the QE_LogonProperties we set in the boInnerPropertyBag Object

boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);

boMainPropertyBag.Add("QE_ServerDescription", "NewDataSet");

boMainPropertyBag.Add("QE_SQLDB", "False");

boMainPropertyBag.Add("SSO Enabled", "False");

//Create a new ConnectionInfo object

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo =

new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

//Pass the database properties to a connection info object

boConnectionInfo.Attributes = boMainPropertyBag;

//Set the connection kind

boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

//*EDIT* Set the User Name and Password if required.

boConnectionInfo.UserName = "";

boConnectionInfo.Password = "";

//Pass the connection information to the table

boTable.ConnectionInfo = boConnectionInfo;

//Get the Database Tables Collection for your report

CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;

boTables = rptClientDoc.DatabaseController.Database.Tables;

//For each table in the report:

// - Set the Table Name properties.

// - Set the table location in the report to use the new modified table

boTable.Name = "Orders";

boTable.QualifiedName = "Orders";

boTable.Alias = "Orders";

rptClientDoc.DatabaseController.SetTableLocation(boTables[0], boTable);

//Verify the database after adding substituting the new table.

//To ensure that the table updates properly when adding Command tables or Stored Procedures.

rptClientDoc.VerifyDatabase();

MessageBox.Show("Data Source Set", "RAS", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

You'll have to adjust for your driver.

Thank you

Don

Former Member
0 Kudos

Hi Don;

So Sorry , but I do not see , where an opened connection was passed.

Both examples, you posted shows "connection info" passed to cystal ( to be connected by crystal);

I need or to execute some procedure with parameters inside crystal without returning any dataset , or I need to pass an already connect connection to it;

Crystal Reports, may do not accept aread connect database sessions ! , I do not know ( I'm newbe in crystal )

Many Thanks !!

Marcos Ortega

0 Kudos

Hi Marco,

You need to build the report off the data first so it has a reference to the data structure.

This line sets the table info: rptClientDoc.DatabaseController.SetTableLocation(boTables[0], boTable);

As I said, create a report off the JDBC driver to your data set first in the designer. Then it should all work in code.

Thank you

Don

Former Member
0 Kudos

>

> This line sets the table info: rptClientDoc.DatabaseController.SetTableLocation(boTables[0], boTable);

>

Yep , I'm doing quite the same ;

And it is working , the report is working , as I pass the connection info to the tables...

for (int i = 0; i < tables.size(); i++) { // begin loop
               ITable table = tables.getTable(i);
               .......
               // setting connectinon info ... properties...
              IConnectionInfo connectionInfo = table.getConnectionInfo();

              PropertyBag connInfoProperties = new PropertyBag();

              connInfoProperties.putBooleanValue("Trusted_Connection", false);
              connInfoProperties.put("PreQEServerName", SERVICE);
             connInfoProperties.put("Server Type", "Oracle Server");


            table.setConnectionInfo(connectionInfo);
      
           databaseController.setTableLocation(table, tables.getTable(i));
     } // end Loop

> As I said, create a report off the JDBC driver to your data set first in the designer. Then it should all work in code.

That is what I'm, doing here; And My report is Working when I call it from the application

Look, I'm afraid that I'm not making myself clear ; sorry for that !

What I need here is the folowing steps;

1. Get an JDNI connection from webApplication Server;

2. Run some procedures to prepare report data; into the connection's session getted in step 1

3. Pass this connection (the same from steps 1 and 2 ) to the crystal report !

4. print the report using the same connection from step 1 and 2

Step 3 is my problem, steps 1 2 4 , are working fine;

When we pass, connection info properties, crystal will get ANY connection from the JDNI Connection Pool; I need that crystal assumes one especific connection that I had prepared it;

Sorry for this long thread ! Don;

And Thank you Again;

Marcos Ortega

Former Member
0 Kudos

hi Marcos,

I know it has been a while, but did you have any luck in running into a resolution for your issue/requirement? I also want to be able to obtain the connection, do ALTER SESSIONS statements to it and then re-run the report.

Regards.

Answers (0)