Error in <queryString>...

[2001 JI Open Discussion]

By: Uniq - uniq_2002

Error in <queryString>...

2002-09-12 03:01

Hi,

 

i have problem with my queryString in my XML.

i want my query is :

1.in this query if we are input id = 1 and then id begginning 1,10,11,111,... will be execute .

 

query in my code:

SELECT * FROM "MENU" WHERE "ID" LIKE (' "+id+"%') ORDER BY "ID"

 

in xml :

<queryString><![CDATA[sELECT * FROM "MENU" WHERE "ID" LIKE ($P{id}+'%') ORDER BY "ID"]]></queryString>

 

if i execute(ant view) i get error :

dori.jasper.engine.JRException: Invalid page index : 0 of 0

 

2. in my xml :

<parameter name="orderBy" class="java.lang.String"/>

 

<queryString><![CDATA[sELECT * FROM "MENU" WHERE "ID" LIKE $P{id} ORDER BY $P{orderBy}]]></queryString>

 

in my class :

Map parameters = new HashMap();

parameters.put("id", "0100");

parameters.put("orderBy", "ID");

 

i want to make ORDER BY is flexible input

but i getting error :

 

java.sql.SQLException: ERROR: Non-integer constant in ORDER BY

 

i am very apreciate for any suggestion.

Thanks...:-)

 

 

regards,

 

uniQ

 

 

 

By: Teodor Danciu - teodord

RE: Error in <queryString>...

2002-09-14 02:08

 

Hi,

 

1. The query does not return any rows,

so the report will contain no pages.

Check the new "whenNoDataType" attribute

that comes with the 0.4.0 version.

 

Question: Why do you place "MENU" and "ID"

between quoutes? Why can't you just write:

SELECT * FROM MENU WHERE ID LIKE (' "+id+"%') ORDER BY ID

 

2. What you want to do is possible only since

0.4.0 because the parameter syntax was enhanced.

Here's the query string you need (pay attention to

the $P!{} parameter syntax):

 

<queryString><![CDATA[sELECT * FROM "MENU" WHERE "ID" LIKE $P{id} ORDER BY $P!{orderBy}]]></queryString>

 

I hope this helps.

Teodor

 

 

 

By: Uniq - uniq_2002

RE: Error in <queryString>...

2002-09-16 01:02

Hi theodord,

 

thanks for replaying my question...:-)

now i am just trying to download 0.4.0 version.

 

why i use "MENU" not MENU ??

i am use database PostgreSQL, and in this database the syntax is with "".

and i trying without "" in xml file for first time and i am getting error the field is undefined.

 

are u can help me how to write this query syntax :

LIKE (' "+id+"%')

in xml file ??

because when i'll try to write like this : ($P{id}+'%')

my query doesn't get result so i getting error.

 

thanks for any sugesstion...:-)

 

regards,

 

uniQ

 

 

 

 

 

 

 

By: Teodor Danciu - teodord

RE: Error in <queryString>...

2002-09-16 01:38

 

Hi,

 

The string concatenation operator in PostgreSQL

is ||, not +

 

<queryString><![CDATA[sELECT * FROM "MENU" WHERE "ID" LIKE ($P{id} || '%') ORDER BY "ID"]]></queryString>

 

http://www.commandprompt.com/ppbook/c7547.htm#USINGOPERATORS

 

I hope this helps.

Teodor