Check for table existence

I am trying to check for the existence of a table before attempting to fetch rows from a table. I have reports that run for different clients with different accesses to tables. Is there a way in BIRT to check for the existence of a table and if it exists, process the report normally and if it does not exist, then display an error message instead. What we do not want is for the report to just crash with some obscure BIRT error. Thanks.

Find more posts tagged with

Comments

mwilliams

Are you talking about an SQL table from different sources? Or a table within your design?

Jodin

I'm talking about a table within my design.

mwilliams

Can you descibe the scenario in which you'd get an error so I can understand better before trying to suggest something? Thanks!

Jodin

Ok, let me break try to explain it more clearly. Say we have Table A. The table may or may not exist on the client system. What I want to do is check for Table A in a dataset for the report. I came up with a query similar to the following that basically checks to see if Table A exists:

SELECT COUNT(*) AS ROW_COUNT
FROM qsys.qadbxref
WHERE dbxlib = 'LWFILES608'
AND dbxlfi = 'TABLE_A'

If the row count comes back with a value greater than zero, then I know the table exists. If Table A exists, then I want the report to use its data. If Table A does not exist, then I simply want to display an error message stating that the table does not exist.

mwilliams

Ok, so you're looking for a table in their database, not a table element in your design? What kind of error do you get if "Table_A" doesn't exist?

Jodin

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75754" data-time="1302549082" data-date="11 April 2011 - 12:11 PM"><p>
Ok, so you're looking for a table in their database, not a table element in your design? What kind of error do you get if "Table_A" doesn't exist?<br /></p></blockquote>
<br />
I'm not getting any error yet. I'm still in the design phase of this project. Let me attach my report design. Maybe that will help some.

mwilliams

So, if this table doesn't exist, you'd potentially need to not run a dataSet or you could end up with an error trying to access a table that doesn't exist? If this is the case, you'll have to know this value prior to running the BIRT report so that you can drop the report elements that use this other dataSet so that it doesn't run. You might be able to avoid doing the check outside of BIRT by changing the query in the beforeOpen of the query in question to a query that will return the correct columns, so you don't have a binding issue, but then hide the element that uses this fake data and display your error message. Am I understanding your potential issue now?

Jodin

Yes Michael I think you are understanding my issue. I think I see what you are saying as well and that is along the lines of what I was thinking we might have to do. In a worse case scenario, I would check the existence of the table in an initial report and then have a hyperlink become visible that would execute a drill through report that uses the table we need. I would hate to have our users jump through hoops though and I was hoping it could all be done in a single report.

mwilliams

If that table doesn't exist, it's just one other table that it'd be? If this is the case, you could make both dataSets in one report and then use the visibility to show the correct table in the one report without having to link to a second report.

Jodin

Actually if the table does not exist, we want to display an error message stating this fact. We basically want the report to die eloquently. I have code in my report design to display the error message. I'm not sure how to implement your suggestion. If the table for a dataset does not exist, then you are saying make the report table invisible? That would be cool. I'm just not sure how to get that to work. If I do a row count on a non-existent table would that still work in BIRT? I'm using version 2.3.1. Thanks.

mwilliams

I'm assuming that your query returns a count of 0 if "table_a" doesn't exist, so you'd just grab this value in the onFetch and store it in a variable that you initialized in your initialize method of your report design and use that variable to check for 0 or greater than 0 to do the appropriate action on both your table and on your label that says, "Error: No data returned".