|
Re: Property Binding with a PostgreSQL Database [message #759970 is a reply to message #759804] |
Wed, 30 November 2011 18:39 |
|
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 #759994 is a reply to message #759985] |
Wed, 30 November 2011 20: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
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 #760006 is a reply to message #759999] |
Wed, 30 November 2011 21:40 |
|
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 #760184 is a reply to message #760009] |
Thu, 01 December 2011 14:53 |
|
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 + "'";
>
|
|
|
Powered by
FUDForum. Page generated in 0.04163 seconds