CommandTable report with pushed ResultSet

[Former Member]

I cannot get answer to my problem:

I have a few hundred reports designed with embedded sql (CommandTable). I would like to execute the query in advance and push the ResultSet into the ReportClientDocument. I tried the example for regular tables and it works, but I cannot get it to work with my embedded sql reports. I get this error: ReportSDKException: Unexpected database connector error

ResultSet rs = domyqueryhere

String tableAlias = rcd.getDatabaseController().getDatabase().getTables().getTable( 0 ).getAlias();

//rcd.getDatabaseController().setDataSource( rs, tableAlias, "" ); // also tried nothing for new name

rcd.getDatabaseController().setDataSource( rs, tableAlias, "ResultTable" );

I copied this code from Crystal example code. Like I said, it works fine for reports with acual tables, but not for a report with a CommandTable. Is there a different method to get this done? Or can I somehow add a new table with the pushed ResultSet and remove the old one?

Please, has anyone else tried this? I have searched and can't find any answers.

thx/d

Edited by: daniel d doyle on Jun 24, 2010 8:02 PM

Edited by: daniel d doyle on Jun 24, 2010 8:09 PM

[Former Member · Accepted solution]

Instead of this:

rcd.getDatabaseController().setDataSource( rs, tableAlias, "ResultTable" );

Try this:

rcd.getDatabaseController().setDataSource( rs, tableAlias, "Reports" );

Note that the last parameter values is "Reports". This was something that was required in previous versions and may still be required when passing a result set to a report.

[Former Member · Accepted solution]

Thanks for your response:

I tried:

rcd.getDatabaseController().setDataSource( rs, tableAlias, "Reports" );

but I get same ReportSDKException.

I am testing on very simple single table query like "select * from table". I created duplicate .rpt file with regular table- not CommandTable- and I can push ResultSet. So I know I have the right code-- I'm just using the example code from this forum.

All my reports have parameters which makes it difficult to convert to regular Crystal tables.

Does anyone know if you can push Resultset into CommandTable report? I would like to know if this is possible or not so I can move ahead with other solution.

Thanks for any help.

[Former Member · Accepted solution]

Have you tried using the public void setDataSource(java.lang.Object newDataSource) method and only passing the ResultSet?

Another alternative is to convert the table to an ICommandTable; I have found it neccessary to do that sometimes in order to make things work. For example this is a snippet I use when passing parameters to a command object:

if(table instanceof com.crystaldecisions.sdk.occa.report.data.CommandTable)

{

ICommandTable command = (IICommandTable)table;

ParameterField commandParam = (ParameterField) command.getParameters().get(0);

Values values = new Values();

ParameterFieldDiscreteValue pfdv = new ParameterFieldDiscreteValue();

pfdv.setValue("ANTON");

values.add(pfdv);

commandParam.setCurrentValues(values);

}

You can do something similar to convert your Table object into an ICommandTable, get the alias and pass this to the setDataSource.

Edited by: Merry Ware on Jul 1, 2010 9:17 AM

[Former Member · Accepted solution]

Thanks for the suggestion but it did not work: I tried:

rcd.getDatabaseController().setDataSource( rs );

with same error:

com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Unexpected database connector error---- Error code:-2147467259 Error code name:failed

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:2283)

-- not sure what you mean about iCommandTable. I am not referencing Table here, just DataSource.

I have also tried dozens of variations throughout today. I will give up on this and rework all my queries into database views to get around this unless someone can come up with an answer soon.

Again, has anyone tried to do setDataSource on CommandTable? Any help would be appreciated.

thx/d

note: I am retrieving correct table alias-- I'm logging the getAlias() statement and it is "Command".

Edited by: daniel d doyle on Jul 1, 2010 3:02 PM

[Former Member · Accepted solution]

I tried this to see if I would get the same error, and it turns out that I do. Not sure what that means.... it may be a bug or configuration issue with this version of the product.