| JDBC: Executing a statement with no results [message #899018] |
Mon, 30 July 2012 07:49  |
 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 07: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 17:15  |
|
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 #899371 is a reply to message #899210] |
Tue, 31 July 2012 15:10 |
|
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 #899419 is a reply to message #899417] |
Tue, 31 July 2012 18:22 |
|
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 #899631 is a reply to message #899474] |
Wed, 01 August 2012 15:54 |
|
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?
|
|
|
Powered by
FUDForum. Page generated in 0.03360 seconds
] 
Search
Help
Register
Login
Home
