Eclipse Community Forums: BIRT » Passing a parameter to an ORDER BY clause

Home » Archived » BIRT » Passing a parameter to an ORDER BY clause

Passing a parameter to an ORDER BY clause [message #105174]

Thu, 05 January 2006 17:57

Eclipse User

Originally posted by: kelly.penhallwilson.bricsnet.com

Using M3 currently - I'm having an interesting issue with trying to pass
a parameter to an ORDER BY clause. What's interesting is that it works
properly with a type 3 JDBC driver, but not for either of the type 4
drivers I've used (MSSQL server is the database - the type 3 driver is
an old weblogic driver, and the type 4 drivers are jdts and microsoft's
new beta type 4 jdbc driver).

The parameter is set in the Data Set - the ORDER BY clause is simple:
ORDER BY ? DESC

Here's the error message from the microsoft driver - the error from the
other type 4 driver was similar, but referred to the prepareStatement:

A Birt exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:1.0.1
Error Code:odaconsumer.CannotGetResultSetMetaData
Error Message:Cannot get the result set metadata.
SQL statement does not return a ResultSet object
The SELECT item identified by the ORDER BY number 1 contains a variable
as part of the expression identifying a column position. Variables are
only allowed when ordering by an expression referencing a column name.

Any idess would be helpful - thanks-

kpw

Re: Passing a parameter to an ORDER BY clause [message #106319 is a reply to message #105174]

Mon, 09 January 2006 12:10

Eclipse User

Originally posted by: wheeler.ninjart.com

Am Thu, 05 Jan 2006 14:57:46 -0800 schrieb kellypw:
I tried that too, but i didn`t work.
Is it possible at all to use parameters like this?
> ORDER BY ? DESC
Is there any other way to do this?

Hoping for answers ;)

Re: Passing a parameter to an ORDER BY clause [message #106621 is a reply to message #106319]

Tue, 10 January 2006 03:12

Eclipse User

Originally posted by: stefano.comarin.primeur.com

Thomas Opiolka wrote:
> Am Thu, 05 Jan 2006 14:57:46 -0800 schrieb kellypw:
> I tried that too, but i didn`t work.
> Is it possible at all to use parameters like this?
>
>>ORDER BY ? DESC
>
> Is there any other way to do this?
>
> Hoping for answers ;)

You could build the query via script in the beforeOpen method of the
Dataset. Just set the queryText variable according to the value of the
parameter.

queryText = "SELECT ... ORDER BY " + params["p1"];

Stefano

Re: Passing a parameter to an ORDER BY clause [message #107127 is a reply to message #106621]

Tue, 10 January 2006 16:57

Eclipse User

Originally posted by: kelly.penhallwilson.bricsnet.com

I'm very confused -then what would go into my Query?

Stefano Comarin wrote:
> Thomas Opiolka wrote:
>
>> Am Thu, 05 Jan 2006 14:57:46 -0800 schrieb kellypw:
>> I tried that too, but i didn`t work.
>> Is it possible at all to use parameters like this?
>>
>>> ORDER BY ? DESC
>>
>>
>> Is there any other way to do this?
>>
>> Hoping for answers ;)
>
>
> You could build the query via script in the beforeOpen method of the
> Dataset. Just set the queryText variable according to the value of the
> parameter.
>
> queryText = "SELECT ... ORDER BY " + params["p1"];
>
> Stefano

Re: Passing a parameter to an ORDER BY clause [message #107262 is a reply to message #107127]

Wed, 11 January 2006 05:07

Eclipse User

Originally posted by: stefano.comarin.primeur.com

You can put anything on the query in the dataset property window, it
will just be evaluated at design time when you click on "Preview
Results". At runtime (and when you preview the report) the query will be
set by the script.

kellypw wrote:
> I'm very confused -then what would go into my Query?
>
> Stefano Comarin wrote:
>
>> Thomas Opiolka wrote:
>>
>>> Am Thu, 05 Jan 2006 14:57:46 -0800 schrieb kellypw:
>>> I tried that too, but i didn`t work.
>>> Is it possible at all to use parameters like this?
>>>
>>>> ORDER BY ? DESC
>>>
>>>
>>>
>>> Is there any other way to do this?
>>>
>>> Hoping for answers ;)
>>
>>
>>
>> You could build the query via script in the beforeOpen method of the
>> Dataset. Just set the queryText variable according to the value of the
>> parameter.
>>
>> queryText = "SELECT ... ORDER BY " + params["p1"];
>>
>> Stefano

Re: Passing a parameter to an ORDER BY clause [message #108681 is a reply to message #107262]

Thu, 12 January 2006 14:59

Eclipse User

Originally posted by: kelly.penhallwilson.bricsnet.com

Thanks - I was very confused by that - I will try it...

Stefano Comarin wrote:
> You can put anything on the query in the dataset property window, it
> will just be evaluated at design time when you click on "Preview
> Results". At runtime (and when you preview the report) the query will be
> set by the script.
>
> kellypw wrote:
>
>> I'm very confused -then what would go into my Query?
>>
>> Stefano Comarin wrote:
>>
>>> Thomas Opiolka wrote:
>>>
>>>> Am Thu, 05 Jan 2006 14:57:46 -0800 schrieb kellypw:
>>>> I tried that too, but i didn`t work.
>>>> Is it possible at all to use parameters like this?
>>>>
>>>>> ORDER BY ? DESC
>>>>
>>>>
>>>>
>>>>
>>>> Is there any other way to do this?
>>>>
>>>> Hoping for answers ;)
>>>
>>>
>>>
>>>
>>> You could build the query via script in the beforeOpen method of the
>>> Dataset. Just set the queryText variable according to the value of
>>> the parameter.
>>>
>>> queryText = "SELECT ... ORDER BY " + params["p1"];
>>>
>>> Stefano

Previous Topic:	ORDER BY clause ignored in web viewer
Next Topic:	Ability to set the width in a cell via scripting?

Goto Forum:

-=] Back to Top [=-

Current Time: Sat Jul 19 08:56:42 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter