Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Property Binding with a PostgreSQL Database
Property Binding with a PostgreSQL Database [message #759804] Wed, 30 November 2011 02:20 Go to next message
JB . is currently offline JB .
Messages: 9
Registered: November 2011
Junior Member
Hello,

I am passing user entered information (string) to a query that accesses a PostgreSQL database.

I have tried this by doing the following:
-in the Query window of the Data Set window typed the following:
select * from "Report_JobStatusReport"() where "ProjectManager" = ?
-created a report parameter and bound it to the Data Set parameter.
This method creates an error. I have however, been able to do this on a column that contain integers by providing an integer in the parameter.

I have created a solution that works by using Property Binding:

Integer case:
"select * from" + '"Report_JobStatusReport"' + "() where" + '"SalesOrder_ID"' + "=" + params["SalesOrder_ID_91"].value

where params["SalesOrder_ID_91"].value is a Report Value with an integer value of 91

String case:
"select * from" + '"Report_JobStatusReport"' + "() where" + '"ProjectManager"' + "=" + "'" + params["ProjectManager_Roger"].value + "'"

where params["ProjectManager_Roger"].value is a Report Value with a string value of Roger


Is there a more elegant way of doing this?

I had a lot of trouble getting this to work so I would like to be able to see what the SQL command looks like as it is passed to the database. Is this possible?

Thanks,
JB

Re: Property Binding with a PostgreSQL Database [message #759970 is a reply to message #759804] Wed, 30 November 2011 13:39 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Why are you having to quote the column name?
Attached is an example using the sample db that does strings.

Jason

On 11/30/2011 2:20 AM, JB wrote:
> Hello,
>
> I am passing user entered information (string) to a query that accesses
> a PostgreSQL database.
>
> I have tried this by doing the following:
> -in the Query window of the Data Set window typed the following:
> select * from "Report_JobStatusReport"() where "ProjectManager" = ?
> -created a report parameter and bound it to the Data Set parameter.
> This method creates an error. I have however, been able to do this on a
> column that contain integers by providing an integer in the parameter.
>
> I have created a solution that works by using Property Binding:
>
> Integer case:
> "select * from" + '"Report_JobStatusReport"' + "() where" +
> '"SalesOrder_ID"' + "=" + params["SalesOrder_ID_91"].value
>
> where params["SalesOrder_ID_91"].value is a Report Value with an integer
> value of 91
>
> String case:
> "select * from" + '"Report_JobStatusReport"' + "() where" +
> '"ProjectManager"' + "=" + "'" + params["ProjectManager_Roger"].value + "'"
>
> where params["ProjectManager_Roger"].value is a Report Value with a
> string value of Roger
>
>
> Is there a more elegant way of doing this?
>
> I had a lot of trouble getting this to work so I would like to be able
> to see what the SQL command looks like as it is passed to the database.
> Is this possible?
>
> Thanks,
> JB
>
>
Re: Property Binding with a PostgreSQL Database [message #759985 is a reply to message #759970] Wed, 30 November 2011 14:17 Go to previous messageGo to next message
JB . is currently offline JB .
Messages: 9
Registered: November 2011
Junior Member
Thanks for the response Jason.

I looked at your example and noticed that it is using a different database driver than I am. I am using a PostgreSQL jdbc driver to connect to my database. It is the postgresql-9.1-901.jdbc4.jar version of the driver.

I have tried connecting to my database using the following SQL command in the Query window but it fails. If I add quotation marks around tblClients the command executes successfully.
select *
from tblClients

The following error results from the SQL above.

A BIRT exception occurred.
Plug-in Provider:Eclipse BIRT Project
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:3.7.1.v20110905
Error Code:odaconsumer.CannotGetResultSetMetaData
Error Message:Cannot get the result set metadata.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:ERROR: relation "tblclients" does not exist
Position: 15
;
org.postgresql.util.PSQLException: ERROR: relation "tblclients" does not exist
Position: 15


JB

Re: Property Binding with a PostgreSQL Database [message #759994 is a reply to message #759985] Wed, 30 November 2011 15:07 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

could you try quoting the report parameter?

ie set its default value to "testparm"
or quote it when you enter it.

This does seem strange.

Jason

On 11/30/2011 2:17 PM, JB wrote:
> Thanks for the response Jason.
>
> I looked at your example and noticed that it is using a different
> database driver than I am. I am using a PostgreSQL jdbc driver to
> connect to my database. It is the postgresql-9.1-901.jdbc4.jar version
> of the driver.
>
> I have tried connecting to my database using the following SQL command
> in the Query window but it fails. If I add quotation marks around
> tblClients the command executes successfully.
> select *
> from tblClients
>
> The following error results from the SQL above.
>
> A BIRT exception occurred.
> Plug-in Provider:Eclipse BIRT Project
> Plug-in Name:BIRT Data Engine
> Plug-in ID:org.eclipse.birt.data
> Version:3.7.1.v20110905
> Error Code:odaconsumer.CannotGetResultSetMetaData
> Error Message:Cannot get the result set metadata.
> org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does
> not return a ResultSet object.
> SQL error #1:ERROR: relation "tblclients" does not exist
> Position: 15
> ;
> org.postgresql.util.PSQLException: ERROR: relation "tblclients" does not
> exist
> Position: 15
>
>
> JB
>
>
Re: Property Binding with a PostgreSQL Database [message #759999 is a reply to message #759994] Wed, 30 November 2011 15:47 Go to previous messageGo to next message
JB . is currently offline JB .
Messages: 9
Registered: November 2011
Junior Member
In the Query section I am able to pass the parameter if I add an apostrophe around it in the default value.

Postgres requires quotations around table and stored procedure names and apostrophes around string values. I have also experienced this in other programs interacting with Postgres and even when they use different drivers.

I cannot get this to work in the Property Binding area because if I remove most of the quotations (see below) I still need to have them around the stored procedure name and field name. I end up getting an error in the Expression Builder saying that "[Row:1, Column:40] missing ; before statement" It is pointing to the quotation mark after Report_JobStatusReport.

"select * from '"Report_JobStatusReport"'() where '"ProjectManager"' =" + params["ProjectManager_Roger"].value


JB

Jason Weathersby wrote on Wed, 30 November 2011 15:07
could you try quoting the report parameter?

ie set its default value to "testparm"
or quote it when you enter it.

This does seem strange.

Jason


Re: Property Binding with a PostgreSQL Database [message #760006 is a reply to message #759999] Wed, 30 November 2011 16:40 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Have you tried:


"select * from '"Report_JobStatusReport"' +"() where '"ProjectManager"'
= '" + params["ProjectManager_Roger"].value + "'";


On 11/30/2011 3:47 PM, JB wrote:
> In the Query section I am able to pass the parameter if I add an
> apostrophe around it in the default value.
>
> Postgres requires quotations around table and stored procedure names and
> apostrophes around string values. I have also experienced this in other
> programs interacting with Postgres and even when they use different
> drivers.
>
> I cannot get this to work in the Property Binding area because if I
> remove most of the quotations (see below) I still need to have them
> around the stored procedure name and field name. I end up getting an
> error in the Expression Builder saying that "[Row:1, Column:40] missing
> ; before statement" It is pointing to the quotation mark after
> Report_JobStatusReport.
>
> "select * from '"Report_JobStatusReport"'() where '"ProjectManager"' ="
> + params["ProjectManager_Roger"].value
>
>
> JB
>
> Jason Weathersby wrote on Wed, 30 November 2011 15:07
>> could you try quoting the report parameter?
>>
>> ie set its default value to "testparm"
>> or quote it when you enter it.
>>
>> This does seem strange.
>>
>> Jason
>
>
Re: Property Binding with a PostgreSQL Database [message #760009 is a reply to message #760006] Wed, 30 November 2011 17:18 Go to previous messageGo to next message
JB . is currently offline JB .
Messages: 9
Registered: November 2011
Junior Member
The result is the same as the previous method.


Quote:
Have you tried:


"select * from '"Report_JobStatusReport"' +"() where '"ProjectManager"'
= '" + params["ProjectManager_Roger"].value + "'";
Re: Property Binding with a PostgreSQL Database [message #760184 is a reply to message #760009] Thu, 01 December 2011 09:53 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Could you log a bugzilla entry for this?

Jason

On 11/30/2011 5:18 PM, JB wrote:
> The result is the same as the previous method.
>
>
> Quote:
>> Have you tried:
>>
>>
>> "select * from '"Report_JobStatusReport"' +"() where '"ProjectManager"'
>> = '" + params["ProjectManager_Roger"].value + "'";
>
Previous Topic:birt 2.3.2 to birt 2.6.2
Next Topic:Multiple groups on same row
Goto Forum:
  


Current Time: Wed Aug 20 14:47:52 EDT 2014

Powered by FUDForum. Page generated in 0.02041 seconds