Ray,

I've just installed this on my server and it does work better now in ie7. Although that fix has just set that text area size to a fixed height by the look of it. It used to resize when the div was resized in firefox but doesn't any more. Still it is better than before for ie7.

One suggestion is that if you do a select * from parks for example off the cfdocexamples and try to column sort, it's really slow. Perhaps you could change the table view to page using say max of 100 records, or have a drop down where a user can set the max.

Dale, I don't need to add a max - you can just do a max in the sql. :)

Ray,

How? Did you know Derby doesn't support TOP perhaps there is another method i'm missing for Derby.

Also, I'd like to see the hide system tables ticked by default as I think most people would rarely query them.

You are right. It only allows for limits via JDBC. I'll consider it. I mean I have to be honest here - I didn't actually expect people to _use_ this in production, or use it outside of to play with CF8 stuff.

If you think it warrants inclusion in RIAFOrge, I'll add it. That way these suggestions can be added to a bug tracker because honestly - I don't have time now to work on it and I'll probably forget unless it is documented.

I really think it's worth it. When writing tutorials for learncf.com which need to use the included databases, how else would you browse them and play.

I was also thinking about hacking it so that I could give others access for select only. ie. No delete, insert or update statements.

Because Dale wouldn't stop bugging me (kidding!), I uploaded it:

http://cfdbexplorer.riaforg...

Dale found a bug that is fixed in the RIAForge version.

Ray,
I am trying to generate an Excel spreadsheet from my cfgrid at the click of a button. I've added a button to the grid using EXT that onclick calls a Javascript function to generate the Excel. My problem is in that Javascript. If it were query-based I see the approach you used here. However, mine is a bound datagrid, which does not allow use of the query attribute. It is also type=html (not flash), but I don't think that matters here. So I tried to tackle it from 2 angles, but am just not smart enough. Probably something simple I'm missing. My google search returned nothing useful.

1) A bound datagrid I think returns a JSON string, so I thought about using the new cf8 function ColdFusion.JSON.decode(json), but what variable is the grid coming back with since my CFC return is "queryConvertForGrid(qread, intGridPage, intPageSize)"? I played with Firebug, but couldn't find the answer. I'm still learning to use it.

2) I thought about using the grid datasource which I already use for various EXT operations. I have the datasource defined by a global variable, but not sure how to tap the query results embedded in it. I looked at the EXT docs, no help.

Another less attractive alternative would be to regenerate the query seperately, but that is just wrong. Another would be to remove the bindings and go with a query-based approach as you did here, but that is not my preference, since it seems to be cleaner to go directly to the CFC and back, but where I'm headed if I don't get another option.

Ideas?

Brian: I'd probably attack this problem another way. You are trying to get access to the data shown in the grid. That is probably possible if you dig enough into the Ext API. However, at the end of the day, you will need CF to spit out the data in Excel format which means going back to the server.

I'd consider looking at how the grid is bound. It is getting data from either a simple URL or a CFC, right? If so, you could simply use the button to load a CFM that gets the same data as a the grid and spits it out in the right format.

If you are concerned about sorting, then you need to examine the grid and figure out which column is currently being sorted. That is definitely possible via the API.

Are are suggesting as in my #1 approach of using the JSON string that the bound datagrid is pulling from my CFC? Not sure how to do that. Also I discovered that since I have paging turned on that I'd only get a subset of my desired results. Unless you have another idea, I'm thinking now that I need a similar CFC to what I use for the grid, but modified to bring back a query of all the grid data, not just the page. I hate to duplicate the call, but if needed I can. I suppose I could pass another arguement to the CFC that would determine whether to return the JSON string or a query to minimize the code duplication at least. Thoughts?

No, I'm not suggesting you use the JSON string _currently_ in the grid, ie, the data on the client side. What I said was, if your grid is bound, as you said it is, it is fetching data from the server, right? Either url:something.cfm or cfc:etc. You can use the same data in another CFM easily enough. Just make a new CFM, call the same url or CFC your grid is, and format the results.

I believe I understand you and it is a CFC, I guess the issue is I don't know how to format the results. To call the same CFC outside the grid still returns "queryConvertForGrid(qread, intGridPage, intPageSize)" which is a JSON string. How do you convert that into a spreadsheet without passing a seperate argument that returns a query, array or struct instead? I'm a JSON neophyte. Also wouldn't I still have the paging problem.

So, you have a CFC method that gets the data and then uses queryConvertForGrid. I'd split this into two methods. One method to just get the query, and the second method (the one your cfgird calls) that uses the first method for it's data, and then use queryConvertForGrid.

Your new CFM would just make use of the first CFC method.

Good idea. Shall do. I often get good ideas from your blogs. Thanks and Happy New Year!

That worked fine for one of my grids where I had only 2 arguments to be passed, but I have another where I pass 63 variables bound to a search form. Not all variables have values, but I never know which ones will be populated. My trouble (may not be impossible) I think is when my excel button is clicked I need to use Javascript to get each of those 63 variables using getElementByID, then pass them as URL variables to my CFM that does an invoke of my CFC (passing those variables as arguments) that queries the database and returns the results. Then the query results are passed from the CFC to the CFM for display. Seems like I'm passing the variables around too much (3 times here), plus at one time I recall hitting a limit trying to send too much as URL parameters. Is there a better way?

Well, if you truly do have 63 things that impact the view of data, then yes, you need to pass them all to generate the Excel. I can't think of a better way. I can say you may want to rethink things. I can't imagine using a data viewer with 63 different variables. My brain would melt into a pile of goo.

Oh, and if you are worried about URL size (you should be), you can always do a POST operation using Ajax. If your CFM uses cfcontent to spit out the Excel-formatted data, then the user won't even leave the current screen.

The Excel content is showing up in a new window using the method you proposed in this blog entry. Can you point to or provide an example how to "do a POST operation using Ajax"

Part of the reason I need to pass 63 variables is that currently cfgrid binding to a CFC does not allow optional variables. I wish they did. If they did, then I'd only send those variables that were being used by creating the grid on the fly and set most of the CFC arguments as not required. I could probably break my grid into 2 grids, which would also mean 2 seperate CFCs, but so far I've not needed to do that.

Brian - For something as complex as your needs, I'd make a CFC method with one (or a few) args where the main argument is a structure. For example, imagine you had an arg for sort col, and one for sort dir (I know that's "special" for cfgrid, but pretend it isn't). Instead of using 2 args, sortdir and sortcol, you coul dhave an argument named sort that is a complex object with 2 keys, col and dir.

So you are saying instead of:
bind="cfc:#strcomponentpath#.readdiadocumentgrid({cfgridpage}, {cfgridpagesize}, {cfgridsortcolumn}, {cfgridsortdirection}, {frmSearch:c1@none}, {frmSearch:c2@none}, {frmSearch:c3@none}, {frmSearch:c4@none}, {frmSearch:c6@none}, {frmSearch:c7@none}, {frmSearch:c8@none},...

I could send structs like:
bind="cfc:#strcomponentpath#.readdiadocumentgrid({cfgridpage}, {cfgridpagesize}, {cfgridsortcolumn}, {cfgridsortdirection}, {frmSearch:c@none}, ...

where c1 is defined in my CFM as c.c1, c2 is c.c2, etc
and then deconstructed in the CFC from the passed argument "c"?

Or even better, just pass the form: bind="cfc:#strcomponentpath#.readdiadocumentgrid({cfgridpage}, {cfgridpagesize}, {cfgridsortcolumn}, {cfgridsortdirection}, {frmSearch})"

No, you can't do it that simply. You would have to use something else instead of the bind, or bind to a JavaScript function and in that JS function use Ajax to do the form posts.

You may be in a situation where you need to use something completely native as opposed to CF8's ajax stuff.

Ray,
I think I'm close for the spreadsheet part. When the Excel button is clicked I have a JS that simplified looks like this:

function exportExcel() {
ColdFusion.Ajax.submitForm('frmSearch', 'showexcel.cfm', handleExcelResult, errorHandlerAJAX);
}

submitForm uses POST by default. showexcel.cfm invokes a CFC (using cfinvokeargument like <cfinvokeargument name="c1" value="#form.c1#" />) and correctly returns query results and using your approach to showing excel has the following:

<cfif isDefined("rstQuery.RecordCount") and rstQuery.RecordCount>
<cfsetting showdebugoutput=false>
<cfcontent type="application/msexcel" reset="true">
<cfheader name="content-disposition" value="attachment;filename=#strFilename#.xls">
<cfoutput>
<table border="1">
<tr>
<cfloop index="c" list="#rstQuery.columnlist#">
<th>#ReplaceNoCase(c,'DIA','')#</th>
</cfloop>
</tr>
<cfloop query="rstQuery">
<tr>
<cfloop index="c" list="#rstQuery.columnlist#">
<td>#rstQuery[c][currentRow]#</td>
</cfloop>
</tr>
</cfloop>
</table>
</cfoutput>
...

All works great except the spreadsheet fails to show up. Firebug showed me that the POST response is almost what I want (<table border="1"><tr><th>DOCNO</th>...) but the cfcontent is getting ignored. If I add an alert to the handleExcelResult function, it returns the html code as I saw in Firebug. For my other grid the cfcontent worked fine, but I was not doing the ColdFusion.Ajax.submitForm function. Ideas?

I can get an html table to popup if I change the handleExcelResult function to the following, but it it is not generating the Excel open/save dialog that I need:

function handleExcelResult(sResult){
var sOption="toolbar=yes,location=no,directories=yes,menubar=yes,";
sOption+="scrollbars=yes,width=750,height=600,left=100,top=25";
var win=window.open("","",sOption);
var doc=win.document;
doc.open();
doc.write('<html><head>')
doc.write('<meta http-equiv="Content-Type" content="application/msexcel">')
doc.write('<meta http-equiv="Content-disposition": attachment;filename="SearchData.xls">')
doc.write('</head><body>')
doc.write(sResult);
doc.write('</body></html>');
doc.close();
win.focus();
}

This seems like it would be a basic need out there, but my google search failed to get me a good Javascript approach.