Error when displaying XML as text, with user selected parameters

Jenkinsj5
edited February 11, 2022 in Analytics #1
<p><span style="font-size:medium;">[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]</span></p><p><span style="font-size:medium;">[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]</span></p><p> </p><p><span style="font-size:medium;">[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]</span></p><p><span style="font-size:medium;">[font="calibri;"]Works ; [/font]</span><strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(127,0,85);]where[/color][/font]</span></strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(0,0,0);] OCMQM1.SUBMIT_DATE [/color][/font]</span><strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(127,0,85);]>=[/color][/font]</span></strong> <span style="font-size:10pt;">[font="'courier new';"][color=rgb(42,0,255);]'2013-12-01'[/color][/font]</span></p><p><span style="font-size:medium;">[font="calibri;"]Fails ; [/font]</span><strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(127,0,85);]where[/color][/font]</span></strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(0,0,0);] OCMQM1.SUBMIT_DATE [/color][/font]</span><strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(127,0,85);]>=[/color][/font]</span></strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(0,0,0);] ?[/color][/font]</span></p><p> </p><p><span style="font-size:medium;">[font="calibri;"]I have tried casting the field [/font]</span><strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(127,0,85);]varchar (1)[/color][/font]</span></strong><span style="font-size:medium;">[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]</span></p><p> </p><p> </p><p><span style="font-size:10pt;">[font="'courier new';"][color=rgb(0,0,0);], [/color][/font]</span><strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(127,0,85);]replace[/color][/font]</span></strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(0,0,0);] ([/color][/font]</span></p><p>   <strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(127,0,85);]cast[/color][/font]</span></strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(0,0,0);] (OCMQM1.SVC_OPTIONS [/color][/font]</span><strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(127,0,85);]as[/color][/font]</span></strong> <strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(127,0,85);]varchar[/color][/font]</span></strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(0,0,0);] (1) ) [/color][/font]</span></p><p><span style="font-size:10pt;">[font="'courier new';"][color=rgb(0,0,0);]   , [/color][/font]</span><span style="font-size:10pt;">[font="'courier new';"][color=rgb(42,0,255);]'<'[/color][/font]</span><span style="font-size:10pt;">[font="'courier new';"][color=rgb(0,0,0);] , [/color][/font]</span><span style="font-size:10pt;">[font="'courier new';"][color=rgb(42,0,255);]' '[/color][/font]</span></p><p><span style="font-size:10pt;">[font="'courier new';"][color=rgb(0,0,0);]   ) [/color][/font]</span><strong><span style="font-size:10pt;">[font="'courier new';"][color=rgb(127,0,85);]as[/color][/font]</span></strong> <span style="font-size:10pt;">[font="'courier new';"][color=rgb(42,0,255);]'Request_Details'[/color][/font]</span></p><p> </p><p><span style="font-size:medium;">[font="calibri;"]Error message[/font]</span></p><p><span style="font-size:12pt;">[font="'times new roman', serif;"][color=rgb(255,0,0);]The following items have errors: [/color][/font]</span></p><p> </p><p><span class='bbc_underline'><span style="font-size:12pt;">[font="'times new roman', serif;"][color=rgb(255,0,0);]Table (id = 1293): [/color][/font]</span></span></p><p><span style="font-size:12pt;">[font="'times new roman', serif;"][color=rgb(255,0,0);]+ Cannot execute the statement.<br />
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.<br />
SQL error #1:The value is not set for the parameter number 1.<br />
;<br />
com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1. (Element ID:1293) [/color][/font]</span></p><p> </p><p> </p><p><span style="font-size:medium;">[font="calibri;"]XML data example[/font]</span></p><p><span style="font-size:medium;">[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]</span></p><p> </p><p><span style="font-size:medium;">[font="calibri;"]<strong>Note</strong> 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]</span></p>
Warning No formatter is installed for the format ipb

Comments

  • micajblock
    edited December 20, 2013 #2
    <p>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?</p>
  • Jenkinsj5
    edited December 20, 2013 #3
    <p style="margin:0in;font-family:Calibri;font-size:11pt;">Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver (v3.0)</p><p style="margin:0in;font-family:Calibri;font-size:11pt;"> </p><p style="margin:0in;font-family:Calibri;font-size:11pt;">This is a production server, so changing anything is a process.</p><p style="margin:0in;font-family:Calibri;font-size:11pt;"> </p><p style="margin:0in;font-family:Calibri;font-size:11pt;">The data is (text, null)</p>
    Warning No formatter is installed for the format ipb
  • <p>Understood. Worth a try to see if it is an issue with the JDBC driver.</p><p> </p><p>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).</p>
  • <p>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? </p><p> </p><p>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)</p>
    Warning No formatter is installed for the format ipb
  • <p>I use the Actuate SQL Server Driver (OEM from Data Direct), so not sure how to answer. </p>