How to use a postgresql function in a data set

[mscarantino]

Hi,

I installed Birt 4.17 this week to build some reports. When I tried to create a data set with a postgresql function I get the following error.

Is there a way to get around this? Thanks for your help.

[speckyspooky]

It is hard to support if we have no information about your function.
If your function doesn't support a kind of table result from PostgreSQL you won't have success.
In most cases if you will get a sacalar value your function should be used like a column.

Normally if you can execute the query on PostgreSQL via editor then you can use it to here on BIRT with the data set
because the string will be send via driver to your DB.

Can your try the function call with static value on your DB and afterwards at the same way on the data set.

Please avoid the semicolon at the end. This can cause trouble too.
Oracle-driver and Snowflage-driver doens't like it here because it will be set internally thorugh the driver.

[hvbtup]

The semicolon at the end is not part of SQL!
It is usually there to create a sequence of SQL statements, but you can only enter a single statement.

[mscarantino]

Thanks for your responses. I ran it again with a fixed value and got the same error message.

The function does work in an editor and returns a table with 1200 records and 30 columns. I was also able to run this function as a data source in the report builder for Jaspersoft.

When I was setting up the data source it couldn't find the postgres jar, so I downloaded the latest jar from postres's site and I am using that.

[hvbtup]

Probably you are using the wrong tool:
See this very old issue report: https://bugs.eclipse.org/bugs/show_bug.cgi?id=300471
You should use the JDBC Data Set Editor (just double-click the Data Set), not the SQL Builder, whatever that is.

[mscarantino]

Thanks. I'll take a look at that.

[mscarantino]

That worked. Thanks for your help.