change report database connection

[Former Member]

Hi

I'm trying to make a java application which changes the reports database connection.

My reports use odbc connection.

I read the examples and I succesfuly changed to jdbc.oracle or jdbc.odbc but I want

to chnage the connection to oracle or other odbc.

I wrote the code:

IConnectionInfo connectionInfo = table.getConnectionInfo();

//Set new table connection property attributes.

PropertyBag propertyBag = new PropertyBag();

//Overwrite any existing properties with updated values.

propertyBag.put("Trusted_Connection", "false");

propertyBag.put("Server Name", "aurel");

propertyBag.put("Connection String", "Server=s(aurel);User ID=s(aurel);Password=;Trusted_Connection=b(false)");

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

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

connectionInfo.setAttributes(propertyBag);

connectionInfo.setUserName(user);

connectionInfo.setPassword(pass);

table.setConnectionInfo(connectionInfo);

//Update old table in the report with the new table.

databaseController.setTableLocation(table, tables.getTable(i));

When I run it I got the error:

JRCAgent1 detected an exception: Database driver name is not supported: crdb_oracle.dll

com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Database driver name is not supported: crdb_oracle.dll

This happened also when I use odbc connection with crdb_odbc.dll

I want to know if I can do that or is it impossible?

I don't need to change the connection for each table I like to change it for the entire report

like in crystal reports when use set datasource location but I don't find any support for that.

Thanks.

[Former Member]

The Java Reporting Component (JRC) supports Java-based connectivity, including JDBC, JNDI, JavaBeans, POJO and XML.

Unfortunately, it does not support ODBC or native Oracle.

The error you're getting is JRC complaining it doesn't know how to support native Oracle connections.

Ted Ueda

[Former Member]

In my web searching I found this:

setTableLocation

public void setTableLocation(java.lang.String subreportName,

ITable curTable,

ITable newTable)

throws ReportSDKException

Sets the location of a table to a database that is different from the one originally specified when creating the subreport. Use this method to change the location of a database table that is active in a subreport. This is especially useful if a subreport uses a database that has a different location on your system, or if you have changed the directory or disk location of a database.

Note: This method does not physically move the database. It simply looks for the database table in a location other than the one originally specified when setting up the subreport.

This method also allows you to change the database driver DLL being used. For example, you can change from crdb_odbc.dll to crdb_oracle.dll. See the Crystal Reports Developer Runtime Help for a list of database driver DLLs.

In the last 4 lines it is said that I can do exactly what I need.Unfortunately I didn't find how anywhere.

And if this is true and work for subreports why shouldn't work for the entire report?

Or I didn't understand right what those lines mean?

[Former Member]

The JRC does not support native Oracle or ODBC - when it encounters an ODBC connection within a report, it tries to connect to a JNDI connection with the same name.

The setTableLocation method is used not just with the JRC SDK but with the RAS SDK - the RAS server supports native Oracle and ODBC.

The JRC uses the Java Report Engine runtime, and not the Crystal reports Developer Runtime.

The setTableLocation works for both the main and subreports. It's specifically the non-support of native Oracle that's throwing the exception you see.

Sincerely,

Ted Ueda

[Former Member]

Thanks for your replies Ted.

I'm clarified now.

[Former Member]

I have a problem when i am trying to open a report using the RAS-engine and to change the database connection. In the report designer i chose OracleDriver and i tried aswell a JDBC connection using the oracle driver class. at runtimer i tried to change to a jdbc-jndi connection and the oracle driver class. so anyways, the error always occure at the method setTableLocation()....

i think this is odd because i used the same code with the ReportClientDocument-class of the JRC and it just ran without any errors.

I also added the path of the "ojdbc14.jar" (jdbc-driver for oracle) to the CLASSPATH of my CRConfig.xml which ist stored in C:ProgrammeBusiness Objectscommon3.5java...because i thought it could be related with that.

first here is the error-output:

com.crystaldecisions.sdk.occa.report.lib.ReportSDKServerExc eption: Fehler beim Öffnen der Verbindung.

Fehler beim Öffnen der Verbindung.

C:WINDOWSTEMP{C5CE14C8-0E3C-405B-AA2E-8CB3465FDE40}.rpt- --- Error code:-2147482892 Error code name:failed

at com.crystaldecisions.sdk.occa.report.lib.ReportSDKServerExce ption.throwReportSDKServerException(Unknown Source)

at com.crystaldecisions.proxy.remoteagent.r.a(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.dd.a(Unknow n Source)

at com.crystaldecisions.sdk.occa.report.application.ag.a(Unknow n Source)

at com.crystaldecisions.sdk.occa.report.application.av.if(Unkno wn Source)

at com.crystaldecisions.sdk.occa.report.application.an.new(Unkn own Source)

at com.crystaldecisions.sdk.occa.report.application.cb.for(Unkn own Source)

at com.crystaldecisions.proxy.remoteagent.u.performDo(Unknown Source)

at com.crystaldecisions.proxy.remoteagent.u.a(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.DatabaseCon troller.a(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.DatabaseCon troller.setTableLocation(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.DatabaseCon troller.a(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.DatabaseCon troller.setTableLocationEx(Unknown Source)

at test.DbChanger.setDatabaseConnection(DbChanger.java:106)

at test.RASexample.main(RASexample.java:92)

now here are my example class and code:

public class RASexample

{

static final String EXPORT_FILE = "C:

myExportedReport.pdf";

public static void main(String[] args)

{

String REPORT_NAME = "D:

Crystal Reports

!!Reports

reports_name.rpt";

try {

//Open report.

com.crystaldecisions.sdk.occa.report.application.ReportClien tDocument rpt = new com.crystaldecisions.sdk.occa.report.application.ReportClien tDocument();

ReportAppSession ra = new ReportAppSession();

ra.createService("com.crystaldecisions.sdk.occa.report.appli cation.ReportClientDocument");

ra.setReportAppServer("localhost");

ra.initialize();

rpt.setReportAppServer(ra.getReportAppServer());

rpt.open("rassdk://" + REPORT_NAME, 0);

DbChanger dbchange = new DbChanger();

dbchange.setDBInformation();

dbchange.setDatabaseConnection(rpt.getDatabaseController());

InputStream byteArrayInputStream = rpt.getPrintOutputController().export(ReportExportFormat.PDF );

// Release report.

rpt.close();

//Use the Java I/O libraries to write the exported content to the file system.

byte byteArray[] = new byte[byteArrayInputStream.available()];

//Create a new file that will contain the exported result.

File file = new File(EXPORT_FILE);

FileOutputStream fileOutputStream = new FileOutputStream(file);

ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(byteArrayInputStream.available());

int x = byteArrayInputStream.read(byteArray, 0, byteArrayInputStream.available());

byteArrayOutputStream.write(byteArray, 0, x);

byteArrayOutputStream.writeTo(fileOutputStream);

//Close streams.

byteArrayInputStream.close();

byteArrayOutputStream.close();

fileOutputStream.close();

System.out.println("Successfully exported report to " + EXPORT_FILE);

}

catch(Exception ex) {

ex.printStackTrace();

}

}

-

-

---

CLASS DBchanger

-

-

---

-

public class DbChanger

{

public String dbUrl = "jdbc:oracle:thin:@*******:**:***";

public String driverClass = "oracle.jdbc.driver.OracleDriver";

public String dbUsername = "*****";

public String dbPassword = "*****";

public String dbUri = "";

public String dbName = "";

public void setDBInformation()

{

//set DataBaseName

int index = this.dbUrl.lastIndexOf(":");

if (index > 0) {

this.dbName = this.dbUrl.substring(index + 1);

}

//set DataBase URI

String[] split = this.dbUrl.split("@");

this.dbUri = "!" + this.driverClass + "!" + split[0] + this.dbUsername + "/" + this.dbPassword + "@" + split[1]

+ "!; DatabaseName=" + this.dbName + ";user=" + this.dbUsername + ";password=" + this.dbPassword + "!";

}

public void setDatabaseConnection(com.crystaldecisions.sdk.occa.report.a pplication.DatabaseController databaseController) throws Exception

{

//Declare the new connection properties that report's datasource will be switched to.

//NOTE: These are specific to using JDBC against a particular MS SQL Server database. Be sure to use the

//DisplayConnectionInfo sample to determine what your own connection properties need to be set to.

final String connectionString = "Use JDBC=b(true);Connection URL=s(" + this.dbUrl + ");" + "Database Class Name=s("

+ this.driverClass + "); User ID=s(" + this.dbUsername + ");Password=s(" + this.dbPassword

+ ");Trusted_Connection=b(false);";

final String databaseDll = "crdb_jdbc.dll";

final String tableNameQualifier = "";

//Obtain collection of tables from this database controller.

Tables tables = databaseController.getDatabase().getTables();

//Set the datasource for all main report tables.

//text was cut off if i used the char for

for (int i = 0; i tables.size(); i++)

{

ITable origTable = tables.getTable(i);

ITable newTable = (ITable)origTable.clone(true);

//Keep existing name and alias.

newTable.setName(origTable.getName());

newTable.setAlias(origTable.getAlias());

//Change properties that are different from the original datasource.

newTable.setQualifiedName(tableNameQualifier + origTable.getName());

//Change connection information properties.

IConnectionInfo connectionInfo = origTable.getConnectionInfo();

//Set new table connection property attributes.

PropertyBag propertyBag = new PropertyBag();

//Overwrite any existing properties with updated values.

propertyBag.put("Trusted_Connection", "false");

propertyBag.put("Connection String", connectionString);

propertyBag.put("Database Name", this.dbName);

propertyBag.put("Server Type", "JDBC (JNDI)");

propertyBag.put("URI", this.dbUri);

propertyBag.put("Use JDBC", "true");

propertyBag.put("Database DLL", databaseDll);

connectionInfo.setAttributes(propertyBag);

//Set database username and password.

//NOTE: Even if these the username and password properties don't change when switching databases, the

//database password is not saved in the report and must be set at runtime if the database is secured.

connectionInfo.setUserName(this.dbUsername);

connectionInfo.setPassword(this.dbPassword);

connectionInfo.setKind(ConnectionInfoKind.SQL);

newTable.setConnectionInfo(connectionInfo);

//Update old table in the report with the new table.

databaseController.setTableLocation(origTable, newTable);

}

}

}

[Former Member]

Are you using a SQL Command with parameters in your datasource?