Updating Crystal Reports with Java APIs

[Former Member]

I've been struggling with figuring out some inconsistencies I'm seeing with the different Crystal Reports Java APIs, and online help has been quite vague or indeterminate in finding a clear solution. So I thought I'd ask here

Pardon the length, but some background is in order...

We have about 1500 or so Crystal Reports managed by our BOE 4.1 FP11 server that targets our data warehouse. Due to an ongoing warehouse migration to another vendor's platform, we have a need to update these reports with new DSNs, and a subset of reports will require some SQL tweaks in their CommandTable objects. I'm attempting to use one of your Java SDKs to automate this process. Bear in mind that I'm not a BOE or Crystal user or expert by any means, I'm just a coder with a lot of experience using SDKs and APIs to integrate/automate stuff.

The warehouse vendor is assisting us with this migration, and they asked for a dump of all custom SQL (CommandTable.CommandText) from the affected reports so they could review it and make any required changes. I was able to easily provide this using the Crystal Reports for Eclipse (CR4E) API and going directly after the .RPT files on the BOE filesystem. Even though these were managed reports, interrogating the RPT files directly worked fine. I was able to walk the filesystem to gather all RPT files, identify the ones affected, and dump the report metadata including the CommandTable custom SQL to a text file provided to the vendor. So far so good...

The vendor then took this text dump and ran their tools against it to validate the SQL and make any required updates. They returned an updated file to us, and now I'm supposed to read those files and then update the DSNs and the modified SQL (CommantTable.CommandText) for those reports.

So now we're up to my current dilemma:

I realize the CR4E SDK isn't intended for managed reports, but I thought I'd grab a couple of these managed RPTs and bring them down as local copies, just to experiment with the update code before trying to access them remotely through a managed API. I've written some very simple code to work out the update dynamics, and the code runs without error. However no change is actually written back to the file when I call ReportClientDocument.save() and .close(). The file timestamp does change, indicating the file was updated, but the file size is identical indicating the contents weren't changed. I also tried ReportClientDocument.saveAs() with a different filename -- the new file is also written, but again is identical to the original file.

While stepping through debug, I can see the SQL change in the CommandTable.CommandText field in memory, so I know the CommandTable.setCommandText() call is working. But why does it never make it to the target file? The file is not read-only, as I check for ReportClientDocument.isReadOnly() which returns false. And again, saveAs() writes a file but with the original contents.

Is it because the CR4E SDK knows this is actually a managed report and refuses to change it? If so, why isn't an exception being thrown instead of failing silently and implying success? I was hoping to keep this as quick and simple and possible, so first tried the same CR4E API I started with. I also have the RAS SDK and the BIP41 SDK available to try through the managed API, and kinda half expected I'd have to do that anyway to ensure the metadata database is updated correctly. But before I chase my tail again, is there anything I'm missing or doing wrong?

Here's a code snippet showing a very quick & dirty example, using a local hardcoded report file that has 3 CommandTable objects, and I'm attempting to change the first CommandTable.CommandText to a dummy SQL string.

File rptFile = new File("test/update_test1.rpt");
ReportClientDocument clientDoc = ReportClientDocument.openReport(rptFile);
if ( clientDoc.isReadOnly() )
  throw new Exception("Report is read-only!");
IDatabase database = clientDoc.getDatabaseController().getDatabase();
String cmdName = null;
CommandTable cmdTable = null;
for ( ITable t : database.getTables() )
{
  if ( t instanceof CommandTable )
  {
    cmdTable = (CommandTable)t;
    cmdName = cmdTable.getAlias();
    cmdTable.setCommandText("SELECT * FROM TEST_CRYSTAL_UPDATE;");
    break;
  }
}
clientDoc.save();
System.out.println("Changed SQL for Command '" + cmdName + "' in report '" + clientDoc.displayName() + "'");
clientDoc.close();

It's not shown in the code above, but there is a surrounding try/catch block that catches all exceptions. None are thrown, and again the file timestamp is updated as if something was written. Here's a before & after screenshot from the debugger, showing that the .setCommandText() call actually changes the SQL in the CommandTable object.

Before:

After:

Thanks for any help you can provide. I guess I'll start diving into the RAS API next...

[former_member11696]

I don't have Java but here's a sample using .NET to get the SQL COmmand:

for (int x = 0; x < dbConCount; x++)
{
    try
    {
        DBDriver = rptClientDoc.DatabaseController.GetConnectionInfos()[x].Attributes.get_StringValue("Database DLL").ToString();
        btnDBDriver.Text += DBDriver + " :";
        if (((dynamic)rptClientDoc.Database.Tables[0].Name) == "Command")
        {
            CrystalDecisions.ReportAppServer.Controllers.DatabaseController databaseController = rpt.ReportClientDocument.DatabaseController;
            ISCRTable oldTable = (ISCRTable)databaseController.Database.Tables[0];

            btnSQLStatement.Text = "Report is using Command Object: \n" + ((dynamic)oldTable).CommandText.ToString();
            btnSQLStatement.Text += "\n";

            IsLoggedOn = false;
            IsCMD = true;
        }
        if (DBDriver.ToString() == "crdb_bwmdx.dll")
            IsBEX = true;

    }
    catch (Exception ex)
    {
        //btnDBDriver.Text = "ERROR: " + ex.Message;
        btnDBDriver.Text += "Main Report has no Data Driver";
    }
}

To update the SQL here's an example:

static void testSetSQLCommandTable_CRPE(CrystalDecisions.CrystalReports.Engine.ReportDocument rpt)
{
    //CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
    //ISCDReportClientDocument rcd;

    //rcd = rpt.ReportClientDocument;

    CrystalDecisions.Shared.ConnectionInfo crConnInfo = new CrystalDecisions.Shared.ConnectionInfo();

    CrystalDecisions.ReportAppServer.Controllers.DatabaseController databaseController = rpt.ReportClientDocument.DatabaseController;
    CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos connectionInfos = databaseController.GetConnectionInfos(null);
    CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnectionInfo = connectionInfos[0];
    CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnectionInfo = CreateConnectionInfo();
    databaseController.ReplaceConnection(oldConnectionInfo, newConnectionInfo, null, CrDBOptionsEnum.crDBOptionMapFieldByRowsetPosition);

    ISCRTable oldTable = (ISCRTable)databaseController.Database.Tables[0];
    CommandTable newTable = new CommandTable();
    newTable.Alias = "Command";
    newTable.Name = "Command";
    newTable.QualifiedName = "Command";
    newTable.ConnectionInfo = newConnectionInfo;
    newTable.CommandText = @" SELECT 'Customer'.'Contact Last Name'  FROM 'xtreme'.'dbo'.'Customer' 'Customer'";
    //newTable.CommandText = @"SELECT 'Checks'.'CheckID', 'Checks'.'LoopType', 'Checks'.'RunType' FROM 'astellastest'.'dbo'.'Checks' 'Checks' WHERE 'Checks'.'CheckID'<100";

    databaseController.SetTableLocation(oldTable, newTable);
    //IsRpt = false;
}

Hope that helps...

Don

[Former Member]

Don,

First, thanks for the detailed reply!

But wow, that's a lot to do that I was hoping could be avoided. If it were one or a handful of reports it likely wouldn't be a big deal. Over 1000 I expect will take awhile to churn through.

I will give it a shot and if I can get it to work with a single report, I'll grab a moderate sample size and see how that works to determine feasibility. Maybe we just have to divide and conquer, if nothing else.

I'm using the Java SDK, as indicated in the thread title. I have all of the references you linked to, as well as .NET and Java JSP examples, which is what I was basing my approach on, but none of the examples or docs dealt directly with updating Command SQL so I've been mostly flying blind. And I don't recall seeing ANYWHERE the workflow you describe above being required to do this. It will be great if it leads to a working solution, but I'd suggest some clearer documentation on it if that's the case.

I'll report back what I find out.

[former_member11696]

Hi David,

First thing to do is do all of this in a report first. Open the Designer, open one of your reports, click on Database menu option and then Set Location.....

Select the Client, fill in the log on info, once the connection is made then click on the new data source and then click on the old data source and then the Map button. If any field type mapping issues are detected a Mapping UI will pop up and you need to map the field individually.

Set location for each data source listed, if you have a subreport do the same.

Now click on Database... and then Verify, this then updates the table info in the report with the new table info selected.

Now use the Same work flow in the SDK. In this case you would need to use ReplaceConnection(OldConn, NewConn, DoNotVerify) so the database info gets updated accordingly, even when using a Command.

Both of those calls are a global setting so it validates every table in the collection, so you don't call it in the loop to set location, once that loop is completed then make the calls only once, Set subreport connections need to be set also, then you make those calls.

And you need to actually be connected to the database, you can't simply call them and expect the connection to work or verify the table info.

Work flow would be:

Open the report

Get the SQL from the Command object

Change the SQL and set it for the main and subreports

Now set the Connection properties for logging on.

If the reprot is using a Stored Procedure set the SP's parameters first before logging on.

If not then set any CR Parameter values that need to be changed or do not have default values saved

Now Logon to the DB

Check connectivity here, it doesn't actually check the reports data info just tests if the connection info works.

Set Parameter default values, if required.

Now SetLocation()

Now Verify the database.

And now you can save the report, if you use RAS to update any parts then you need to use the ReportClientDocument object when saving. It has the updated info in it. The ClientDocument may not, some of the basic properties can be saved but for changing the DB source you need to use the RCD object.

Just to be clear, are you using Java or .NET?

I don't have any examples but you may be able to find some here:

https://wiki.scn.sap.com/wiki/display/BOBJ/Java+%28Crystal+Reports+for+Eclipse%29+SDK

And more here:

https://wiki.scn.sap.com/wiki/display/BOBJ/Java+SDK

For .NET examples go here, I wrote 2 test apps, one for Parameter/Logon and one for Printing:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

Don

[Former Member]

Aha, I think you might have been referring to the ReportClientDocument.verifyDatabase() call.

Same concern as with the other call however, as this one also attempts to connect to the database and validate all the columns. I did try it and got an exception that the JNDI name (the DSN) was not found (which is true as it doesn't exist from my local machine where this test file lives). It might work from a managed RAS connection, but again I'd be worried about the overhead of checking every connection.

[Former Member]

I also tried by actually changing the SQL to some real SQL that should be valid in the existing context. Same behavior. No errors reported, everything looks like it executes fine. The report is saved to disk, but with the old SQL.

Is the verifyTableConnectivity() required to make the update, or is it just best practice? If it's just best practice, I understand why for an individual report update, but given the batch volume I'm updating (~1500 reports) it just doesn't seem practical to do this for every report.

Trying anyway, I added the following line after setting the new SQL and before saving:

      if ( !clientDoc.getDatabaseController().verifyTableConnectivity(cmdTable) )
        throw new Exception("Table verification failed!");

The call returns false and thus throws my exception, but doesn't return an error code or anything to indicate what actually failed. As I just picked this report at random for testing, I don't have any idea why. I would assume it's due to something with the DB credentials, maybe an expired password or inactive ID, or else it's expecting me to have authenticated the report already. Neither seem like feasible approaches given the task at hand. I need to just be able to change the DSN and maybe the SQL, save it, and let the chips fall where they may when someone attempts to next run the report.

Is there another way to do this type of batch CR update? I've been banging my head against this particular wall now for awhile, and I need to deliver something to the warehouse migration team ASAP.

[Former Member]

I tried just reformatting the existing SQL as an update test. That way I'm not changing the functional SQL, just the whitespace around it. Unless CR is smart enough to recognize that nothing substantial changed other than whitespace and ignores it, it's still not working.

Also, verify() is not a method on the Database object. There is a verifyTableConnectivity() method on the parent DatabaseController object that takes an ITable as its parameter. Not sure if it works with a CommandTable, but the way it reads it sounds like it validates the connection to the Database. Which is something I'd prefer to avoid as I didn't intend to connect each of these reports live in order to update them. Is that actually a requirement just to update the report file, or am I reading that correctly?

[Former Member]

Don, thank you! I will try that. I wondered if something like that might be the reason, but I would have expected an exception or something to be thrown rather than a silent failure.

[former_member11696]

Hi David,

It's because CR has a copy of the Tables and fields in the RPT file, you cannot simply change it to Select *.

It needs to be valid SQL for that report.

Try adding a WHERE clause or Sort or change the filter in the WHERE to see if it saves it.

You also should call Database.Verify() so the engine can validate the SQL you are setting it to. BE aware if the field info does not match the engine will simply remove the fields from the report.

Don