Eclipse Community Forums: BIRT » Property Binding with a PostgreSQL Database

Help

Home

Home » Eclipse Projects » BIRT » Property Binding with a PostgreSQL Database

Show: Today's Messages :: Show Polls :: Message Navigator

Property Binding with a PostgreSQL Database [message #759804]

Wed, 30 November 2011 02:20

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

Report message to a moderator

Re: Property Binding with a PostgreSQL Database [message #759970 is a reply to message #759804]

Wed, 30 November 2011 13:39

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

Attachment: stringparmquery.zip
(Size: 2.76KB, Downloaded 45 times)

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Property Binding with a PostgreSQL Database [message #759985 is a reply to message #759970]

Wed, 30 November 2011 14:17

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

Report message to a moderator

Re: Property Binding with a PostgreSQL Database [message #759994 is a reply to message #759985]

Wed, 30 November 2011 15:07

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

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Property Binding with a PostgreSQL Database [message #759999 is a reply to message #759994]

Wed, 30 November 2011 15:47

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

Report message to a moderator

Re: Property Binding with a PostgreSQL Database [message #760006 is a reply to message #759999]

Wed, 30 November 2011 16:40

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

Jason Weathersby

BIRT Exchange

Report message to a moderator

Re: Property Binding with a PostgreSQL Database [message #760009 is a reply to message #760006]

Wed, 30 November 2011 17:18

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 + "'";

Report message to a moderator

Re: Property Binding with a PostgreSQL Database [message #760184 is a reply to message #760009]

Thu, 01 December 2011 09:53

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 + "'";
>

Jason Weathersby

BIRT Exchange

Report message to a moderator

Previous Topic:	birt 2.3.2 to birt 2.6.2
Next Topic:	Multiple groups on same row

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 23 12:06:26 EDT 2013