java.io.IOException: prepare query failed

Mark Hamilton-43486

Summary

java.io.IOException: prepare query failed[nQSError: 43113]

Content

Hi everyone. I hope you are all well.

I have a problem with BI Publisher in OBIEE. I have setup a BI Data Model (with a bursting definition) and a Report. I have then attempted to run a report job using the Use bursting definition option selected. I know the email services work as if I don't select "User Bursting Option" I receive an email successfully. I get an error message that looks like this. I have removed URLs for security. I don't have full administration rights. Please could anyone help? Thanks

Error Message

Job Name : POC burst

Job Executed Date : April 11, 2017 3:03:54 PM BST Status
: Job was failed.

View History : internal url (actual URL removed)

Detail Message:

::JOB_PROCESSOR_EXCEPTION::[INSTANCE_ID=internal server (actual server removed).1434126379636]
[INSTANCE_JOB_ID=1610]::::ERROR_GETTING_REPORT_DATA::[INSTANCE_ID=internal server (actual server removed).]  INSTANCE_JOB_ID=[1610]
oracle.xdo.XDOException: java.sql.SQLException: java.io.IOException: prepare
query failed[nQSError: 43113] Message returned from OBIS.

[nQSError: 27004] Unresolved table:
"Dummydata".

• oracle.xdo.XDOException: oracle.xdo.XDOException:
  java.sql.SQLException: java.io.IOException: prepa

Sherry George

The "Dummydata" should be an actual database table. The bursting definition should be able to run in the database without errors. The template name you used 'layout1' is the correct syntax.

Mark Hamilton-43486

Hi Sherry

Thank you so much for replying so soon. Here is the bursting definition.COLUMN13 has an email address within it.

I have 2 different error messages. I get the "Unresolved Table" error shown in your comment if I change the layout name to the filename and I get a syntax error if I use the name 'layout1' shown below.

The "Dummydata" table shown below is the name of the dataset.

select

   COLUMN13 KEY,

  'layout1' TEMPLATE,

  'en-US' LOCALE,

  'PDF' OUTPUT_FORMAT,

  'EMAIL' DEL_CHANNEL, 

   COLUMN13 PARAMETER1,

  'Fazal.Subhan2@networkrail.co.uk' PARAMETER2,

  'Mark.hamilton@networkrail.co.uk' PARAMETER3,

  'SUBJECT: POC BI Publisher Bursting' PARAMETER4,

  'BODY: Hi, Please find attached your Opex Performance.' PARAMETER5,

  'true' PARAMETER6,

  'Mark.hamilton@networkrail.co.uk' PARAMETER7,

  'Alan.Walsh@networkrail.co.uk' PARAMETER8

from

Dummydata

Sherry George

query failed[nQSError: 43113] Message returned from OBIS.[nQSError: 27004] Unresolved table:
"Dummydata".oracle.xdo.XDOException: oracle.xdo.XDOException:
java.sql.SQLException: java.io.IOException: prepa

Problem is with this table "Dummydata", is it available for query in your data model ? Post your bursting definition. Also update OBIEE and BI publisher version

Mark Hamilton-43486

Hi Sherry.

Thank you once more.

I will have to ask admin for a table name that has the email addresses and a column for the Key field (in this case a business cost centre) within it.

I will feedback once I have a new success or error message.

Kind Regards

Mark Hamilton

Sherry George

user10254366 wrote:Hi Sherry,Thanks again. I have just a few more questions. Does this mean that I need to reference a technical table name,

Yes an actual table or a view in the database. To test, take your bursting query and run it in the database, it should be returning results without error.

and that I am effectively joining this table to the data set, e.g. the Key field is what we are using to join the SQL query to the data set?

In a way yes, but the actual mapping between the bursting definition and dataset is done by bipublisher in the back end.  (From the same doc:"KEY is the Delivery key and must match the Deliver By element.The bursting engine uses the key to link delivery criteria to a specific section of the burst data.")

PS I think this confusion is caused by this guide, linked below. Adding Bursting Definitions It has an image of a reference to select from the data set rather than a data base table.This is shown in the section titled Passing a Parameter to the Bursting Query.

You cannot write the bursting query with select from dataset. As mentioned in my previous reply it has to be an actual table.

Hope this answers your questions.

Mark Hamilton-43486

Hi Sherry,

Thanks again. I have just a few more questions. Does  this mean that I need to reference a technical table name, and that I am effectively joining this table to the data set, e.g. the Key field is what we are using to join the SQL query to the data set?

If so, this would be useful as it would allow me not to need email addresses within all of my subject areas that I wish to burst from. Rather I would be able to link them using some other related field.

Thanks in advance again.

PS I think this confusion is caused by this guide, linked below. 

Adding Bursting Definitions

It has an image of a reference to select from the data set rather than a data base table.

This is shown in the section titled Passing a Parameter to the Bursting Query.

Regards

Mark