Eclipse Community Forums: BIRT » JDBC: Executing a statement with no results

Home » Archived » BIRT » JDBC: Executing a statement with no results(Can it be done?)

JDBC: Executing a statement with no results [message #899018]

Mon, 30 July 2012 03:49

Eclipse User

I'm creating a report that connects to a PostgreSQL database which uses schemas. Before running a SELECT statement, I must set the schema with SET SCHEMA 'myschema';.

The name of the schema must be dynamic but that's not the problem here. Instead, I'm facing the issue that BIRT - or maybe the PostgreSQL JDBC driver - cannot execute two statements sequentially (ie. SET SCHEMA followed by SELECT). I get

org.postgresql.util.PSQLException: No results were returned by the query

I can wrap SET SCHEMA in a stored procedure that returns something but then I get

org.postgresql.util.PSQLException: Multiple ResultSets were returned by the query

I realize that might be the driver's entire fault, but I can't change it. Is there some way for me to run SET SCHEMA from BIRT? I was hoping the DataSource object would have a method to run arbitrary SQL but I haven't found anything. (I could rewrite the SQL query as a store procedure but I'd prefer not to if I can avoid it).

Thanks!

[Updated on: Mon, 30 July 2012 03:52] by Moderator

Re: JDBC: Executing a statement with no results [message #899161 is a reply to message #899018]

Mon, 30 July 2012 13:15

Eclipse User

JD,

On the stored proc approach did you try setting the result set number?
http://www.eclipse.org/birt/phoenix/project/notable2.3.php#jump_12

Jason

On 7/30/2012 3:49 AM, JD Mising name wrote:
> I'm creating a report that connects to a PostgreSQL database which uses
> schemas. Before running a SELECT statement, I must set the schema with
> SET SCHEMA 'myschema';.
>
> The name of the schema must be dynamic but that's not the problem here.
> Instead, I'm facing the issue that BIRT - or maybe the PostgreSQL JDBC
> driver - cannot execute two statements sequentially (ie. SET SCHEMA
> followed by SELECT). I get org.postgresql.util.PSQLException: No results
> were returned by the query
>
> I can wrap SET SCHEMA in a stored procedure that returns something but
> then I get org.postgresql.util.PSQLException: Multiple ResultSets were
> returned by the query
>
> I realize that might be the driver's entire fault, but I can't change
> it. Is there some way for me to run SET SCHEMA from BIRT? I was hoping
> the DataSource object would have a method to run arbitrary SQL but I
> haven't found anything. (I could rewrite the SQL query as a store
> procedure but I'd prefer not to if I can avoid it).
>
> Thanks!

Re: JDBC: Executing a statement with no results [message #899210 is a reply to message #899161]

Tue, 31 July 2012 01:59

Eclipse User

Hi Jason,

Thanks for your reply. Unfortunately, in the Settings section of the Edit Data Set dialog, I only see the field sets "Data Fetch Setting" and "BIRT Engine settings for comparing data set column values". There is no "Result Set Selection". Maybe because of the Postgres driver? I'm running BIRT 3.7.2.

In the property editor there's a "Result set number" property, but it doesn't help.

Regards,
Julien

Re: JDBC: Executing a statement with no results [message #899371 is a reply to message #899210]

Tue, 31 July 2012 11:10

Eclipse User

Julien

was your dataset a stored procedure?

Jason

On 7/31/2012 1:59 AM, J D wrote:
> Hi Jason,
>
> Thanks for your reply. Unfortunately, in the Settings section of the
> Edit Data Set dialog, I only see the field sets "Data Fetch Setting" and
> "BIRT Engine settings for comparing data set column values". There is no
> "Result Set Selection". Maybe because of the Postgres driver? I'm
> running BIRT 3.7.2.
>
> In the property editor there's a "Result set number" property, but it
> doesn't help.
>
> Regards,
> Julien

Re: JDBC: Executing a statement with no results [message #899417 is a reply to message #899371]

Tue, 31 July 2012 14:16

Eclipse User

Jason Weathersby wrote on Tue, 31 July 2012 11:10

was your dataset a stored procedure?

I tried two statements:

UPDATE pg_settings ... ;
SELECT set_schema();

So the second one would be a stored procedure but I don't think that changes anything on the client side - the result set should just be one row / one varchar column. The raised exceptions belong to the PostgreSQL JDBC driver and are raised by an executeQuery() method whenever there are multiple result sets or when there is no data. Another method is offered for statements that return no data: executeUpdate(). I know nothing about JDBC, but it looks like no amount of rewriting the data set query is going to go around that limitation (maybe it's Postgres-specific?).

That's why I was hoping to be able to script the JDBC connection BIRT uses. I guess it can't be done, can it?

Re: JDBC: Executing a statement with no results [message #899419 is a reply to message #899417]

Tue, 31 July 2012 14:22

Eclipse User

I thought you were wrapping the multiple calls in a stored proc on the
db and then just calling the one stored proc from birt.

Jason

On 7/31/2012 2:16 PM, J D wrote:
> Jason Weathersby wrote on Tue, 31 July 2012 11:10
>> was your dataset a stored procedure?
>
>
> I tried two statements:
>
>
> UPDATE pg_settings ... ;
> SELECT set_schema();
>
>
> So the second one would be a stored procedure but I don't think that
> changes anything on the client side - the result set should just be one
> row / one varchar column. The raised exceptions belong to the PostgreSQL
> JDBC driver and are raised by an executeQuery() method whenever there
> are multiple result sets or when there is no data. Another method is
> offered for statements that return no data: executeUpdate(). I know
> nothing about JDBC, but it looks like no amount of rewriting the data
> set query is going to go around that limitation (maybe it's
> Postgres-specific?).
>
> That's why I was hoping to be able to script the JDBC connection BIRT
> uses. I guess it can't be done, can it?

Re: JDBC: Executing a statement with no results [message #899474 is a reply to message #899419]

Wed, 01 August 2012 01:40

Eclipse User

Sorry, my explanations were not clear. Wrapping the queries in a stored proc is what I want to avoid.

Is it possible to execute a SQL query programmatically in JavaScript on the same connection the data sets use?

[Updated on: Wed, 01 August 2012 01:41] by Moderator

Re: JDBC: Executing a statement with no results [message #899631 is a reply to message #899474]

Wed, 01 August 2012 11:54

Eclipse User

BIRT has a data engine API, but I am not certain if you can keep the
connection open to call two queries. It may be worth a try. Take a
look at the beforeFactory to get an idea on how to call it. BTW another
option is to extend the jdbc driver and you can get access to the
statement and connection and do what every you want. Take a look at
this for an example:

http://www.eclipse.org/birt/phoenix/project/notable3.7.php#jump_5

Jason

On 8/1/2012 1:40 AM, J D wrote:
> Sorry, I my explanations were not very clear. Wrapping the queries in
> the DB is what I want to avoid.
>
> Is it possible to execute a SQL query programmatically in JavaScript on
> the same connection the data sets use?

Attachment: calldataengine.zip
(Size: 2.41KB, Downloaded 269 times)

Previous Topic:	Rendering a BIRT chart as Flex or Flash (BIRT 2.6.1)
Next Topic:	BIRT Engine and server doesn't respond

Goto Forum:

-=] Back to Top [=-

Current Time: Sun Jul 20 15:01:25 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter