XUL Template problem - Parameters

[wonea]

Hello I have a problem, been trying to get my SQL template to work. I
want it to return both the name forename , surname. However it's
returns something like

Barry , surname
Tom , surname

obviously I want

Barry , white
Tom , Jackson

My code;

<template>
<query> SELECT forename AS field1, :thesur AS field2 FROM students;
<param name="thesur" id="thesur">surname</param>
</query>
<action>
<menupopup>
<menuitem label="**Add new person***" oncommand="changeFilter
('0');"/>
<menuitem uri="?" label="?field1 , ?field2"
id="specificfilter_menuitem"/>
</menupopup>
</action>
</template>

I will be eternally grateful for any help!

[wonea]

If there's an alternative way. It's just I'm hoping to get this
method working, so I can vary different fields/table to look at.

[Igor Tandetnik]

wonea <wone...@gmail.com> wrote:
> Hello I have a problem, been trying to get my SQL template to work. I
> want it to return both the name forename , surname. However it's
> returns something like
>
> Barry , surname
> Tom , surname
>
> obviously I want
>
> Barry , white
> Tom , Jackson
>
> My code;
>
> <template>
> <query> SELECT forename AS field1, :thesur AS field2 FROM students;
> <param name="thesur" id="thesur">surname</param>

What is the parameter supposed to achieve? You effectively have

SELECT forename AS field1, 'surname' AS field2 FROM students;

That is, the second column is a hard-coded literal 'surname', in all
rows. You probably want

SELECT forename AS field1, surname AS field2 FROM students;

and drop the parameter.

Igor Tandetnik

[wonea]

Thanks for your response. My idea is to subsitute the column names,
at run time. I've got two listboxes which are generated
automatically, the first detailing category, buildings, loans, etc.
The second detailing the specifics. So I was hoping to subsitute the
column names as per query.

For example

Listbox 1 Listbox 2
************ ****************** **********************
* Students * * Barry,White * *Effiel, Tower *
* Buildings* * Jackson,Tom* or *Blackpool, Tower* or.....
* Loans * * Rice,Sue * *Liverpool, Gallery*
************ ******************* **********************

So my idea was to return the fields as field1, field2 and subsitute
the column names, and table name. Something like this;

<query> SELECT criteria1 AS field1, criteria2 AS field2 FROM
tablename;

<param name="criteria1" id="criteria1"></param>
<param name="criteria2" id="criteria2"></param>

<param name="tablename" id="tablename"></param>
</query>

I can set values of these parameters through a function called by
listbox1.

As you mentioned I've got a problem with quotes 'criteria' which are
automatically added to string parameters. Need to find a way around
this.

[Igor Tandetnik]

wonea <wone...@gmail.com> wrote:
> Thanks for your response. My idea is to subsitute the column names,
> at run time.

You can't. Parameters can only appear where literals can appear. The
closest you can come is something like this:

select (case :colName when 'forename' then forename when 'surname' then
surname else 'unknown column' end) as field
from students;

Or, you could simply build the SELECT statement on the fly, listing the
columns you want.

Igor Tandetnik

[wonea]

Wow, cheers. I played around with the code you sent which work
perfectly. However, when I try to extend it for table names I can
unstuck;

<query> select (case :colName when 'forename' then forename when
'surname' then surname else 'unknown column' end) as field1 from
(case :tableName when 'students' then student else 'unknown table'
end) as table;
<param name="colName" id="colName">surname</param>
<param name="tableName" id="tableName">nodes</param>

So close I can almost taste it!

I toyed with idea of replacing the whole statement. However, stupidly
I replaced the whole statement as a parameters. lol

I had another idea;

<query value="SELECT forename AS field1, surname AS field2 FROM
nodes;" id="thequery"/>

document.getElementById("thequery").value;

Any of ideas, of how to manipulate the query statement outside the
template?

[Igor Tandetnik]

wonea <wone...@gmail.com> wrote:
> Wow, cheers. I played around with the code you sent which work
> perfectly. However, when I try to extend it for table names I can
> unstuck;
>
> <query> select (case :colName when 'forename' then forename when
> 'surname' then surname else 'unknown column' end) as field1 from
> (case :tableName when 'students' then student else 'unknown table'
> end) as table;

It won't work with table names quite this way. If you really want to
push it, you could probably do something like this:

select ... from
(select * from table1 limit (case when :tableName = 'table1' then -1
else 1 end)) join
(select * from table2 limit (case when :tableName = 'table2' then -1
else 1 end)) join
(select * from table3 limit (case when :tableName = 'table3' then -1
else 1 end));

For anything but small tables, performance would likely be abysmal
though. I don't recommend this approach - I only show it because it
proved to be an interesting mental exercise.

> I had another idea;
>
> <query value="SELECT forename AS field1, surname AS field2 FROM
> nodes;" id="thequery"/>
>
> document.getElementById("thequery").value;
>
> Any of ideas, of how to manipulate the query statement outside the
> template?

You should be able to do

document.getElementById("thequery").textContent = "SELECT ...";
document.getElementById("mylistbox").builder.rebuild();

where "mylistbox" is the ID of the element containing the <template>
tag.

Igor Tandetnik

[wonea]

Thanks replacing the query string, worked perfectly. Very impressed
with your idea of joining the tables together, after sitting down to
work out exactly what you meant. Made a note for future reference.
Never knew you could do something like that. Again, very grateful for
your advice!

Wonea