Error when displaying XML as text, with user selected parameters

[font="calibri;"]In my report (BIRT 4.2) I am getting data from a SQL 2008 R2 data base.  The client has asked to add a new field to the report.  The field has xml data that is formatted as text (example data below).  I can display it fine, in html, or Excel; BUT if I have a user selected parameter the report crashes (error message below).  Works fine in previews (report & SQL).[/font]

[font="calibri;"]I don’t think there is any xml as test in the sample data base, so not seeing how I can create an example using the sample data.[/font]

 

[font="calibri;"]Having string parameters is fine, but as soon as I put the ‘?’ it crashes on deploy via Apache.  The parameter is a date.[/font]

[font="calibri;"]Works ; [/font][font="'courier new';"][color=rgb(127,0,85);]where[/color][/font][font="'courier new';"][color=rgb(0,0,0);] OCMQM1.SUBMIT_DATE [/color][/font][font="'courier new';"][color=rgb(127,0,85);]>=[/color][/font] [font="'courier new';"][color=rgb(42,0,255);]'2013-12-01'[/color][/font]

[font="calibri;"]Fails ; [/font][font="'courier new';"][color=rgb(127,0,85);]where[/color][/font][font="'courier new';"][color=rgb(0,0,0);] OCMQM1.SUBMIT_DATE [/color][/font][font="'courier new';"][color=rgb(127,0,85);]>=[/color][/font][font="'courier new';"][color=rgb(0,0,0);] ?[/color][/font]

 

[font="calibri;"]I have tried casting the field [/font][font="'courier new';"][color=rgb(127,0,85);]varchar (1)[/color][/font][font="calibri;"], etc  no matter how many characters, or what allowable format I cast to, I get the same error.  Even converting the xml to a single character of varchar and then replacing the character with a blank space in at the SQL give the same crash on deploy. [/font]

 

 

[font="'courier new';"][color=rgb(0,0,0);], [/color][/font][font="'courier new';"][color=rgb(127,0,85);]replace[/color][/font][font="'courier new';"][color=rgb(0,0,0);] ([/color][/font]

  [font="'courier new';"][color=rgb(127,0,85);]cast[/color][/font][font="'courier new';"][color=rgb(0,0,0);] (OCMQM1.SVC_OPTIONS [/color][/font][font="'courier new';"][color=rgb(127,0,85);]as[/color][/font] [font="'courier new';"][color=rgb(127,0,85);]varchar[/color][/font][font="'courier new';"][color=rgb(0,0,0);] (1) ) [/color][/font]

[font="'courier new';"][color=rgb(0,0,0);]   , [/color][/font][font="'courier new';"][color=rgb(42,0,255);]'<'[/color][/font][font="'courier new';"][color=rgb(0,0,0);] , [/color][/font][font="'courier new';"][color=rgb(42,0,255);]' '[/color][/font]

[font="'courier new';"][color=rgb(0,0,0);]   ) [/color][/font][font="'courier new';"][color=rgb(127,0,85);]as[/color][/font] [font="'courier new';"][color=rgb(42,0,255);]'Request_Details'[/color][/font]

 

[font="calibri;"]Error message[/font]

[font="'times new roman', serif;"][color=rgb(255,0,0);]The following items have errors: [/color][/font]

 

[font="'times new roman', serif;"][color=rgb(255,0,0);]Table (id = 1293): [/color][/font]

[font="'times new roman', serif;"][color=rgb(255,0,0);]+ Cannot execute the statement.

org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.

SQL error #1:The value is not set for the parameter number 1.

;

com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1. (Element ID:1293) [/color][/font]

 

 

[font="calibri;"]XML data example[/font]

[font="calibri;"]<?xml version="1.0" encoding="UTF-8" standalone="yes"?><form><text id="ExistingServerName" label="Server Name:" multiline="false" readonly="false">WINHPLNSPRD128</text><text id="ProjectFunding" label="How is this project funded?" button="4001" multiline="false" readonly="false">34542 - Enable HealthPlaNET Architecture to Support Multiple Customers</text><text id="ImplementationDate" label="Requested Implementation Date:" multiline="false" readonly="false">12/12/2013 00:00:00</text><text id="CPUNeeded" label="Additinal CPU Needed:" multiline="false" readonly="false">1</text><text id="MemoryNeeded" label="Additional Memory Needed (in GB):" multiline="false" readonly="false">0</text><text id="AdditionalStorage" label="How much additional disk storage is needed (in GB)?" multiline="false" readonly="false">0</text><text id="ExpandDrive" label="If this request is to expand an existing drive, what drive letter should be expanded?" multiline="false" readonly="false"></text></form>[/font]

 

[font="calibri;"]Note The user supplied parameters work fine, when the xml is not part of the report. Adding the field to the SQL, even if not added to the report table, and regardless of placement in the SQL causes the error. [/font]

Silence Bestows Consent

Comments

  • edited December 20, 2013

    This is a weird one. Which JDBC driver are you using? Can you try a different one? What is the data type of the XML field?

  • edited December 20, 2013

    Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver (v3.0)

     

    This is a production server, so changing anything is a process.

     

    The data is (text, null)

    Silence Bestows Consent
  • Understood. Worth a try to see if it is an issue with the JDBC driver.

     

    Since I do not have your database the only way I can help you further if you provide some sample data (best in form of DDL so I can upload to my SQL Server database).

  • I have my dev server up and kind of running with 4.3.1.  What is, or how can I figure out which is the optimal driver to use? 

     

    I am reporting from an SQL2008 R2 Data base, via Apache, with most reports as HTML, PDF, xls or xlsx (xlsx being primary motivator for upgrade)

    Silence Bestows Consent
  • I use the Actuate SQL Server Driver (OEM from Data Direct), so not sure how to answer. 

Sign In or Register to comment.