Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » 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 Go to next message
J D is currently offline J D
Messages: 9
Registered: June 2011
Junior Member
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]

Report message to a moderator

Re: JDBC: Executing a statement with no results [message #899161 is a reply to message #899018] Mon, 30 July 2012 13:15 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 Go to previous messageGo to next message
J D is currently offline J D
Messages: 9
Registered: June 2011
Junior Member
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 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 Go to previous messageGo to next message
J D is currently offline J D
Messages: 9
Registered: June 2011
Junior Member
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 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 Go to previous messageGo to next message
J D is currently offline J D
Messages: 9
Registered: June 2011
Junior Member
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]

Report message to a moderator

Re: JDBC: Executing a statement with no results [message #899631 is a reply to message #899474] Wed, 01 August 2012 11:54 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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?
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:
  


Current Time: Thu Aug 28 01:16:24 EDT 2014

Powered by FUDForum. Page generated in 0.03297 seconds