How to programatically add a table?

[Former Member]

<p>Hi,</p><p>It would be very useful if I could programmatically create the table structures in my report files (rather than relying on the POJO drag & drop). Programmatically creating the tables at design time, combined with converting my POJOs to a resultset at runtime could help me workaround the lack of support for nested POJO properties. The idea here is that I wouldn&#39;t have to create special classes and special properties for every data source I want to have in a report and just make the whole thing more dynamic.</p><p>Anyway, here&#39;s a little test code snippet that I tried without success. I&#39;m wondering if anyone can point out where the problem might be. Thanks.</p><p>&nbsp;</p><p> ReportClientDocument reportClientDoc = new ReportClientDocument();<br /> reportClientDoc.setReportAppServer("inproc:jrc");<br /> reportClientDoc.open(REPORT_NAME, 1);</p><p>&nbsp;</p><p> Field f1 = new DBField();<br /> f1.setName("field1");<br /> f1.setType(FieldValueType.numberField);<br /> <br /> Fields fields = new Fields();<br /> fields.add(f1);<br /> <br /> Table table = new Table();<br /> table.setName("Table 1");<br /> table.setDataFields(fields);<br /> <br /> reportClientDoc.getDatabaseController().addTable(table, new TableLinks());<br /> <br /> reportClientDoc.save();<br /> System.out.println("Done!"); </p><p>&nbsp;</p><p>&nbsp;</p><p>&nbsp;</p><p>--

---

gives me this -

---

</p><p>&nbsp;</p><p> com.businessobjects.reports.sdk.JRCCommunicationAdapter: JRCAgent1 detected an exception: Invalid argument<br /> at com.crystaldecisions.reports.reportdefinition.datainterface.k.case(Unknown Source)<br /> at com.businessobjects.reports.sdk.b.w.a(Unknown Source)<br /> at com.businessobjects.reports.sdk.b.w.int(Unknown Source)<br /> at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(Unknown Source)<br /> at com.crystaldecisions.proxy.remoteagent.x.a(Unknown Source)<br /> at com.crystaldecisions.proxy.remoteagent.q.a(Unknown Source)<br /> at com.crystaldecisions.sdk.occa.report.application.dd.a(Unknown Source)<br /> at com.crystaldecisions.sdk.occa.report.application.ag.a(Unknown Source)<br /> at com.crystaldecisions.sdk.occa.report.application.bu.if(Unknown Source)<br /> at com.crystaldecisions.sdk.occa.report.application.bu.void(Unknown Source)<br /> at com.crystaldecisions.sdk.occa.report.application.a6.for(Unknown Source)<br /> at com.crystaldecisions.proxy.remoteagent.u.performDo(Unknown Source)<br /> at com.crystaldecisions.proxy.remoteagent.u.a(Unknown Source)<br /> at com.crystaldecisions.sdk.occa.report.application.DatabaseController.a(Unknown Source)<br /> at com.crystaldecisions.sdk.occa.report.application.DatabaseController.new(Unknown Source)<br /> at com.crystaldecisions.sdk.occa.report.application.DatabaseController.addTable(Unknown Source)<br /> at test.ReportTest.launchApplication(ReportTest.java:58)<br /> at test.ReportTest$1.run(ReportTest.java:79)<br /> at java.awt.event.InvocationEvent.dispatch(Unknown Source)<br /> at java.awt.EventQueue.dispatchEvent(Unknown Source)<br /> at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)<br /> at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)<br /> at java.awt.EventDispatchThread.pumpEvents(Unknown Source)<br /> at java.awt.EventDispatchThread.pumpEvents(Unknown Source)<br /> at java.awt.EventDispatchThread.run(Unknown Source)<br /><br />com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Invalid argument-- Error code:-2147467259 Error code name:failed<br /> </p><p>&nbsp;</p>

[SJohnson]

You can use the following code to add a Table:

---

 import java.io.IOException;

import com.crystaldecisions.sdk.occa.report.application.DatabaseController;
import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;
import com.crystaldecisions.sdk.occa.report.data.ConnectionInfo;
import com.crystaldecisions.sdk.occa.report.data.DBField;
import com.crystaldecisions.sdk.occa.report.data.FieldValueType;
import com.crystaldecisions.sdk.occa.report.data.Fields;
import com.crystaldecisions.sdk.occa.report.data.Table;
import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;
import com.crystaldecisions.sdk.occa.report.lib.ReportSDKException;

public class AddTable {

   /**
   * @param args
   * @throws ReportSDKException
   * @throws IOException
   */
   public static void main(String[] args) throws ReportSDKException, IOException {
      ReportClientDocument clientDoc = new ReportClientDocument();
      clientDoc.setReportAppServer("inproc:jrc");
     Â
      // Open report
      clientDoc.newDocument();
      DatabaseController ctr = clientDoc.getDatabaseController ();

      Table table = new Table ();
      String tableName = "myNewTable";
      table.setAlias (tableName);
       table.setName (tableName);
       table.setQualifiedName (tableName);
       table.setDescription (tableName);

       Fields fields = new Fields ();
       DBField field = new DBField ();

       String fieldName = "myNewField";
       field.setDescription (fieldName);
       field.setHeadingText (fieldName);
       field.setName (fieldName);
       field.setType (FieldValueType.stringField);
       field.setLength(255);
       fields.add (field);

       table.setDataFields (fields);

       PropertyBag props = new PropertyBag ();
       props.put ("Database DLL", "crdb_javabeans.dll"); //$NON-NLS-1$ //$NON-NLS-2$

       ConnectionInfo info = new ConnectionInfo ();
       info.setAttributes (props);

       table.setConnectionInfo (info);
       ctr.addTable (table, null);
       clientDoc.saveAs("AddTable.rpt", "C:/Samples", 0);
       System.out.println("Done");

   }

}

---

For some reason the String size always displays half the value that I am passing to it. I believe this has something to do with the fact that we are adding an empty value. We can use the work-around discussed in the other POJO threads to correct this in the interim.

Regards,
Sean Johnson (CR4E Product Manager)

<a href="http://www.eclipseplugincentral.com/Web_Links-index-req-ratelink-lid-639.html">Rate this plugin @ Eclipse Plugin Central</a> </p>

[Former Member]

That works. Thanks, I never would&#39;ve figured that one out on my own!

[Former Member]

Hi Sean,

 I get the following error when I try to use some code that you wrote in an earlier post (AddStoredProcedure) that is not inaccessible: http://diamond.businessobjects.com/node/520

 I was not sure where else to post this:

----

---

Exception in thread "main" 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(Unknown Source)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(Unknown Source)

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

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

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

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

at com.crystaldecisions.sdk.occa.report.application.bu.if(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.bu.void(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.a6.for(Unknown 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.DatabaseController.a(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.DatabaseController.new(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.DatabaseController.addTable(Unknown Source)

at .reports.storedproc.AddStoredProcedure.main(AddStoredProcedure.java:98)

Caused by: com.crystaldecisions.reports.reportdefinition.datainterface.e: Unexpected database connector error

at com.crystaldecisions.reports.reportdefinition.datainterface.a.a(Unknown Source)

at com.crystaldecisions.reports.reportdefinition.datainterface.a.a(Unknown Source)

at com.crystaldecisions.reports.reportdefinition.datainterface.a.a(Unknown Source)

at com.crystaldecisions.reports.reportdefinition.datainterface.g.a(Unknown Source)

at com.crystaldecisions.reports.reportdefinition.datainterface.g.a(Unknown Source)

at com.crystaldecisions.reports.reportdefinition.datainterface.m.new(Unknown Source)

at com.crystaldecisions.reports.common.as.a(Unknown Source)

at com.crystaldecisions.reports.common.ae.a(Unknown Source)

at com.businessobjects.reports.sdk.b.k.a(Unknown Source)

at com.businessobjects.reports.sdk.b.w.int(Unknown Source)

... 14 more

Caused by: com.crystaldecisions.reports.queryengine.am: Unexpected database connector error

at com.crystaldecisions.reports.queryengine.driverImpl.o.if(Unknown Source)

at com.crystaldecisions.reports.queryengine.ax.new(Unknown Source)

at com.crystaldecisions.reports.queryengine.ax.byte(Unknown Source)

... 24 more

Caused by

com.crystaldecisions.reports.reportdefinition.datainterface.e: Unexpected database connector error

at com.crystaldecisions.reports.reportdefinition.datainterface.a.a(Unknown Source)

at com.crystaldecisions.reports.reportdefinition.datainterface.a.a(Unknown Source)

at com.crystaldecisions.reports.reportdefinition.datainterface.a.a(Unknown Source)

at com.crystaldecisions.reports.reportdefinition.datainterface.g.a(Unknown Source)

at com.crystaldecisions.reports.reportdefinition.datainterface.g.a(Unknown Source)

at com.crystaldecisions.reports.reportdefinition.datainterface.m.new(Unknown Source)

at com.crystaldecisions.reports.common.as.a(Unknown Source)

at com.crystaldecisions.reports.common.ae.a(Unknown Source)

at com.businessobjects.reports.sdk.b.k.a(Unknown Source)

at com.businessobjects.reports.sdk.b.w.int(Unknown Source)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(Unknown Source)

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

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

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

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

at com.crystaldecisions.sdk.occa.report.application.bu.if(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.bu.void(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.a6.for(Unknown 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.DatabaseController.a(Unknown Source)

at


com.crystaldecisions.sdk.occa.report.application.DatabaseController.new(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.DatabaseController.addTable(Unknown Source)

at com.senecatank.reports.storedproc.AddStoredProcedure.main(AddStoredProcedure.java:98)

Caused by: com.crystaldecisions.reports.queryengine.am: Unexpected database connector error

at com.crystaldecisions.reports.queryengine.driverImpl.o.if(Unknown Source)

at com.crystaldecisions.reports.queryengine.ax.new(Unknown Source)

at com.crystaldecisions.reports.queryengine.ax.byte(Unknown Source)

... 24 more

 ===================================

The original code:

<font size="2" color="#7f0055">import</font>java.io.IOException;

<font size="2" color="#7f0055"><p align="left">import<font size="2"><font color="#000000"> com.crystaldecisions.sdk.occa.report.application.DatabaseController;</font></font></p></font>

importcom.crystaldecisions.sdk.occa.report.application.ReportClientDocument;

<font size="2" color="#7f0055"><p align="left">import<font size="2"><font color="#000000"> com.crystaldecisions.sdk.occa.report.application.ReportSaveAsOptions;</font></font></p></font>

importcom.crystaldecisions.sdk.occa.report.data.ConnectionInfo;

<font size="2" color="#7f0055"><p align="left">import<font size="2"><font color="#000000"> com.crystaldecisions.sdk.occa.report.data.FieldValueType;</font></font></p></font>

importcom.crystaldecisions.sdk.occa.report.data.IConnectionInfo;

<font size="2" color="#7f0055"><p align="left">import<font size="2"><font color="#000000"> com.crystaldecisions.sdk.occa.report.data.IParameterField;</font></font></p></font>

importcom.crystaldecisions.sdk.occa.report.data.ParameterField;

<font size="2" color="#7f0055"><p align="left">import<font size="2"><font color="#000000"> com.crystaldecisions.sdk.occa.report.data.ParameterFieldDiscreteValue;</font></font></p></font>

importcom.crystaldecisions.sdk.occa.report.data.Procedure;

<font size="2" color="#7f0055"><p align="left">import<font size="2"><font color="#000000"> com.crystaldecisions.sdk.occa.report.data.TableLinks;</font></font></p></font>

importcom.crystaldecisions.sdk.occa.report.data.Values;

<font size="2" color="#7f0055"><p align="left">import<font size="2"><font color="#000000"> com.crystaldecisions.sdk.occa.report.lib.PropertyBag;</font></font></p></font>

importcom.crystaldecisions.sdk.occa.report.lib.PropertyBagHelper;

<font size="2" color="#7f0055"><p align="left">import<font size="2"><font color="#000000"> com.crystaldecisions.sdk.occa.report.lib.ReportSDKException;</font></font></p></font>public <font size="2" color="#7f0055">class</font>AddStoredProcedure {

/*</p><font size="2" color="#7f9fbf">@param</font>args

<font size="2" color="#000000"> </font>@throws<font size="2" color="#000000"> </font><font size="2" color="#3f5fbf">ReportSDKException</font><font size="2"><font color="#000000"> </font></font></p></font><font size="2" color="#3f5fbf"><p align="left"><font size="2" color="#7f9fbf">@throws</font>IOException

/</p></font>

public static

<font size="2" color="#000000"> </font>void<font size="2" color="#000000"> main(String[] args) </font>throws*ReportSDKException, IOException {

// <font size="2" color="#7f9fbf">TODO</font>Auto-generated method stub

ReportClientDocument rptDoc =<font size="2" color="#7f0055">new</font>ReportClientDocument();

String ServerName = <font size="2" color="#7f0055">null</font>;String DatabaseName =




<font size="2" color="#7f0055">null</font>;

String JDBC_URL = <font size="2" color="#7f0055">null</font>;String JDBC_Class =

<font size="2" color="#7f0055">null</font>;

String UserName = <font size="2" color="#7f0055">null</font>;String Password =

<font size="2" color="#7f0055">null</font>;

String TableName = <font size="2" color="#7f0055">null</font>;String ParameterName =

<font size="2" color="#7f0055">null</font>;

String TableAlias = <font size="2" color="#7f0055">null</font>;String TableQualifier =

<font size="2" color="#7f0055">null</font>;

TableLinks dummyLink = <font size="2" color="#7f0055">null</font>;

//change the following properties to suit your needs

ServerName = "hostname";DatabaseName = "DBNAME";

JDBC_URL="jdbc:microsoft:sqlserver://hostname:1433";JDBC_Class =

"com.microsoft.sqlserver.jdbc.SQLServerDriver"; UserName = "user";

Password = "AAA";TableName =

"FETCH_AAA_REPORT;1";

TableAlias= "FETCH_AAA_REPORT;1";TableQualifier =

"dbo.FETCH_AAA_REPORT;1";ParameterName = "@prm1_decimal15p5s"; rptDoc.setReportAppServer("inproc:jrc");

rptDoc.newDocument();

PropertyBag attrs = <font size="2" color="#7f0055">new</font>PropertyBag();attrs.put(PropertyBagHelper.

<font size="2" color="#0000c0">CONNINFO_DATABASE_DLL</font>, "crdb_jdbc.dll");

attrs.put(PropertyBagHelper.<font size="2" color="#0000c0">CONNINFO_SERVER_NAME</font>, ServerName);attrs.put(PropertyBagHelper.

<font size="2" color="#0000c0">CONNINFO_DATABASE_NAME</font>, DatabaseName);

attrs.put(PropertyBagHelper.<font size="2" color="#0000c0">CONNINFO_SERVER_TYPE</font>, "JDBC (JNDI)");attrs.put(PropertyBagHelper.

<font size="2" color="#0000c0">CONNINFO_JDBC_DATABASECLASSNAME</font>, JDBC_Class);

attrs.put(PropertyBagHelper.<font size="2" color="#0000c0">CONNINFO_JDBC_CONNECTION_URL</font>, JDBC_URL);IConnectionInfo connInfo =

<font size="2" color="#7f0055">new</font>ConnectionInfo();

connInfo.setAttributes(attrs);

connInfo.setUserName(UserName);

connInfo.setPassword(Password);

Procedure table =<font size="2" color="#7f0055">new</font>Procedure ();

table.setAlias (TableAlias);

table.setName (TableName);

table.setQualifiedName (TableQualifier);

table.setConnectionInfo(connInfo);

//uncomment the following code if your Stored Proc requires parameters

/</p><p align="left">IParameterField param = new ParameterField (); </p><p align="left">param.setType(FieldValueType.numberField);</p></font><font size="2" color="#3f7f5f"><p align="left">param.setName(ParameterName); </p><p align="left">param.setDescription("param"); </p><p align="left">Values newParamValues = new Values ();</p></font><font size="2" color="#3f7f5f"><p align="left">ParameterFieldDiscreteValue newValue = new ParameterFieldDiscreteValue ();</p></font><font size="2" color="#3f7f5f"><p align="left">newValue.setValue (Double.valueOf("1234.567")); </p></font><font size="2" color="#3f7f5f"><p align="left">newParamValues.add (newValue);</p></font><font size="2" color="#3f7f5f"><p align="left">param.setCurrentValues (newParamValues);</p></font><font size="2" color="#3f7f5f"><p align="left">table.getParameters ().add (param);</p><p align="left">/<font size="2" color="#0000c0">out</font>.println("Done");

}

}

[SJohnson]

<p>Hi,</p><p>Can you tell me if you are using Crystal Reports for Eclipse or not? The code I supplied will only work with the JRC which ships in CR4E.</p><p>Also, if you debug through the code, could you tell me which line it is failing on? This may help identifying the culprit. </p><p>Regards,<br />Sean Johnson (CR4E Product Manager) <br /><br /> <strong><a href="http://www.eclipseplugincentral.com/Web_Links-index-req-ratelink-lid-639.html">Rate this plugin @ Eclipse Plugin Central</a></strong> </p>

[Former Member]

Hi Sean,

I tried the AddStoredProcedure.java from http://diamond.businessobjects.com/node/869 for invoking the stored procedure. I am getting 'Unexpected Database Connector Error" on encountering the dbctr.addTable() method. (where we are trying add stored procedure to the report)

I am not sure if there is any problem with opening the database connection. Because the same settings is working for other sample report, where I am trying to add table to the report instead of stored procedure to the report.

Exception in thread "main" 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(Unknown Source)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(Unknown Source)

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

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

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

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

at com.crystaldecisions.sdk.occa.report.application.bu.if(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.bu.void(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.a6.for(Unknown 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.DatabaseController.a(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.DatabaseController.new(Unknown Source)

at com.crystaldecisions.sdk.occa.report.application.DatabaseController.addTable(Unknown Source)

at AddStoredProcedure.main(AddStoredProcedure.java)

Please help me to resolve this issue. This is urgent.

Thanks,

Lakshmi