auto queries

[kentyler]

For simple queries I have made a ColdFusion template that will accept a select statement in its query string and return an xml file.

The column values are sent back as attributes. In this example the attribute names are hard coded. Is there any way I can dynamically generate them ? That is to say do the <text datapath="...derive attribute name here.."/> in a loop that loops over the attributes ?

<canvas>
<dataset type="http" name="languages" autorequest="true" src="http://www.seedwiki.com/test/testcompare.cfm?querystring=select%20*%20from%20ph rase" />

<view>
<simplelayout/>

<view datapath="languages:/data/row">
<simplelayout axis = "x" />
<text datapath="@phraseid"/>
<text datapath="@phrase"/>
</view>
</view>
</canvas>

[antun]

Hey kentyler

Yes you can set them dynamically. Use the setDataSet() method of the HTTPDataset:


// Here's the text field
<inputtext id="myQS" width="100" />

// Do this onclick:
var d = canvas.datasets.languages;
var myQS = myTextField.getText()
d.setQueryString( { querystring : myQS } );
d.doRequest();


However for something like this you wouldn't bind the query from a text field.

-Antun


Originally posted by kentyler
The column values are sent back as attributes. In this example the attribute names are hard coded. Is there any way I can dynamically generate them ?

[kentyler]

once I can dynamically send different queries, how do I output them on the page ? I have to be able to dynamically detect the columns in the query and then output the data ?

[antun]

I was trying to test it out to make sure I gave yo ua working reply, but the url you gave does not work (it bounces me to the seed wiki home page).

So I'll give you the theory:


<canvas>
<dataset type="http" name="languages" autorequest="false"
src="http://www.seedwiki.com/test/testcompare.cfm" />

<view>
<simplelayout/>
<inputtext id="query" width="300" />
<button>
<method event="onclick">
var d = canvas.datasets.languages;
var myQS = query.getText()
d.setQueryString( { querystring : myQS } );
d.doRequest();
</method>
</button>

<view datapath="languages:/data/row" ondata="debug.write( this.data );">
<simplelayout axis = "x" />
<text datapath="@phraseid"/
ondata="debug.write( this.data )">
<text datapath="@phrase"/>
</view>

</view>
</canvas>


If you want to use data replication you'll need to know the structure of the XML document, as above. However if you want to be more fancy, you will have to use a datapointer to navigate the data. Declare the datapointer first:

<datapointer id="mydp" xpath="mydata:/data[1]"/>

Then reference it in script (using the mydp id) and call the datapointer's methods e.g.


// retrieve data using:
getNodeAttribute()
getNodeName()
getNodeText()

// search using
selectChild()
selectNext()
selectParent()
selectPrev()


It would be helpful if you made the script available...

-Antun

[kentyler]

I deleted the page because it allowed you to run arbitary sql against my database. I'll modify it to only allow selects against certain tables and put it back up.

I'll incorportate your suggestions and post the script back, and I'll also post the cf code template.

Thanks for the help

The ultimate goal here is an application that you can point at a datasource and then use to browse the tables in that datasource and do edit, update and deletes on them. This is often very useful for maintenance of a database that sits behind a web application.

[antun]

Great! Just let me know when the script is back up!

-Antun

[kentyler]

Good morning Antun,

With your great start I've gotten this far:

<canvas debug="true">
<dataset type="http" name="languages" src="http://www.seedwiki.com/test/testcompare.cfm" />
<simplelayout/>
<text>Enter the query here.</text>
<windowtext name="myQS" width="200">You can only process select queries</windowtext>
<button>Submit Query
<method event="onclick">
var d = canvas.datasets.languages;
var myQS = parent.myQS.getText()
var p=new LzParam();
//need to encode the string to deal with spaces
p.addValue( "querystring", myQS, true);
d.setQueryString( p );
d.doRequest();
</method>
</button>

<view>
<text> The Fields </text>
<simplelayout/>
<view datapath="languages:/query/columnlist/column">
<text datapath="@name"/>
</view>
</view>
<text> The Data </text>
<view>
<simplelayout/>
<view datapath="languages:/data/row">
<simplelayout axis = "x" />
<text datapath="@phraseid"/>
<text datapath="@phrase"/>
</view>
</view>

</canvas>

You can only enter the SQL statement SELECT * FROM phrase

as you can see we can retrieve the column names, the next step is to substitute them for @phraseID and @phrase in the last view that displays the data.

I've left the CF template up so you can try it.

[antun]

Hey kentyler

There were a couple of issues:

[list=1]
The XML was not well formed. XML needs to have a single root node, and your server was outputting two. For that reason I used a local dataset instead with a single <result> root node.
Your server is outputting text/html HTTP headers which makes it harder to view the XML for debugging. I think it should be text/xml or text/plain for XML files.
This isn't really the right way to approach the design of the problem. XML attributes shouldn't be dynamic. It would be better if the values were dynamic.
[/list=1]

That said, here's what I did:


<canvas debug="true" width="800" height="800">
<!-- <dataset type="http" name="languages"
src="http://www.seedwiki.com/test/testcompare.cfm" /> -->

<!-- My local dataset -->
<dataset name="languages" src="query.xml" />

<simplelayout spacing="10" />
<text>Enter the query here.</text>
<windowtext name="myQS" width="200">
SELECT * FROM phrase
</windowtext>
<button>Submit Query
<method event="onclick">
var d = canvas.datasets.languages;
var myQS = parent.myQS.getText();
debug.write( escape( myQS ) );
var p = new LzParam();
//need to encode the string to deal with spaces
p.addValue( "querystring", myQS, true);
d.setQueryString( p );
d.doRequest();
</method>
</button>

<view>
<text> <b>The Fields</b> </text>
<simplelayout/>
<view datapath="languages:/result/query/columnlist/column">
<text name="columnnames" datapath="@name"/>
</view>
</view>
<text> <b>The Data</b> </text>
<view>
<simplelayout/>
<view datapath="languages:/result/data/row">
<simplelayout axis = "x" />
<view name="cells"
datapath="languages:/result/query/columnlist/column">
<simplelayout axis="x" spacing="2" />
<view>
<method event="oninit">
var colname = parent.datapath.getNodeAttribute('name');
var cell =
parent.parent.datapath.getNodeAttribute( colname );
this.outputField.setText( cell );
</method>
<text name="outputField" />
</view>
</view>
</view>
</view>

</canvas>


For each row I generated the cells using data replication based on the column names. Then within the replicated view I had got the name of the column name using the getNodeAttribute() method. I was then able to pass that to a call to the rows datapath, and ask for the attribute by name.

Like I said, this isn't really the best way of approachign the issue. A better structure for your XML might be:


<data>
<row>
<column name="PHRASE" value="Home" />
<column name="PHRASEID" value="1" />
</row>
...
</data>


-Antun

[kentyler]

Antun,
By changing the structure of the xml (i made it well formed as well) I was able to get what I wanted without having to do scripting.


<canvas debug="true">
<dataset type="http" name="languages" src="http://www.seedwiki.com/test/testcompare.cfm" />

<simplelayout/>
<text>Enter the query here.</text>
<windowtext name="myQS" width="200">You can only process select queries</windowtext>
<button>Submit Query
<method event="onclick">
var d = canvas.datasets.languages;
var myQS = parent.myQS.getText()
var p=new LzParam();
//need to encode the string to deal with spaces
p.addValue( "querystring", myQS, true);
d.setQueryString( p );
d.doRequest();
</method>
</button>
<view>
<simplelayout axis="x"/>
<view datapath="languages:/controller/query/columnlist/column">
<text datapath="@name"/>
</view>
</view>
<view>
<simplelayout/>
<view datapath="languages:/controller/data/row">
<simplelayout axis = "x" />
<view datapath="column">
<simplelayout />
<text datapath="@value"/>
</view>
</view>
</view>

</canvas>


The next trick will be to display a list of available tables.

[kentyler]

ok, I have a list of tables now, but the form displays very strangly. When you submit a query that works (some of the tables have values in their fields that need to be escaped, because they break the xml that is sent) first the list of tables is doubled ? then the data is displayed, then the column headers are added. Try Select * from Categories.

Do you happen to know what characters need to be escaped for XML, it seems that ' is one, are there others ?


<canvas debug="true">
<dataset autorequest="true" type="http" name="languages" src="http://www.seedwiki.com/test/testcompare.cfm" />

<simplelayout/>
<text><b>Tables</b></text>
<view datapath = "languages:/controller/tablelist">
<simplelayout/>
<text datapath="table/@name"/>
</view>

<text><br/><b>Enter the query here.</b></text>

<windowtext name="myQS" width="200">You can only process select queries</windowtext>
<button>Submit Query
<method event="onclick">
var d = canvas.datasets.languages;
var myQS = parent.myQS.getText()
var p=new LzParam();
//need to encode the string to deal with spaces
p.addValue( "querystring", myQS, true);
d.setQueryString( p );
d.doRequest();
</method>
</button>
<view>
<simplelayout axis="x"/>
<view datapath="languages:/controller/query/columnlist/column">
<text datapath="@name"/>
</view>
</view>
<view>
<simplelayout/>
<view datapath="languages:/controller/data/row">
<simplelayout axis = "x" />
<view datapath="column">
<simplelayout />
<text datapath="@value"/>
</view>
</view>
</view>

</canvas>

[antun]

Hey kentyler

With regards to the strange behaviour when running the query, that appears to be a bug and I've filed it. It's great that you happend on it because it means we're aware of it now, but I wouldn't recommend your design:

Instead have multiple datasets for various things. In your case, you might have one dataset called "tables" that has autorequest="true" that returns all the table names when the application loads.

Then you could have a second dataset called "userQuery" that deals with user queries.


<!-- Declare datasets -->
<dataset autorequest="true" type="http"
name="tables"
src="http://www.seedwiki.com/test/testcompare.cfm" />

<dataset autorequest="false" type="http"
name="userQuery"
src="http://www.seedwiki.com/test/testcompare.cfm" />


This way you're dealing with less data, and you're not getting the same data twice.

I think for XML you have to escape quotes, greater-than/less-than signs (< and > go to &lt; and &gt;) and the ampersand.

-Antun

[smramesh]

Hi Antun,

Could u help me out for the same above functionality but data are in the form of XML file. i.e., search for some data with query/keyword in XML File (without HTTP and JSP).

pls advise me how to approach this issue.

Thanks in advance.

Regards,
Ramesh