Friday, January 1, 2010

New Query Functions Released (Open BlueDragon Blog)

New Query Functions released

Published: 2:01 PM GMT, Friday, 1 January 2010

We've justed finished the a whole new set of functions to help with the manipulation of Query objects and their respective datasources.

The following table highlights all the current functions within the Query category available in the OpenBD (nightly build) release:

DatasourceCreateAdds a new datasource to the system for use with any database functions. This does not persist over server restarts
DatasourceDeleteRemoves the given datasource. Note, it will not remove any datasource that was registered with the underlying bluedragon.xml file
DatasourceIsValidChecks to see if a given datasource has been previously registered using DataSourceCreate()
QueryAddColumnAdds a new column of data to the exist query object, returning the column number
QueryAddRowAdds the specified the number of rows to the end of the query
QueryColumnArrayReturns all the data in a query for a given column
QueryColumnListReturns all the data in a query for a given row but as a structure
QueryDeleteColumnDeletes the column from the query, returning the deleted column data as an array
QueryDeleteRowDeletes the row within a query object. Modifies the original query object
QueryIsEmptyDetermines if the query has any rows
QueryNewCreates a new query object with the columns past in of the optional types
QueryOfQueryRunExecutes a Query-of-Query against a previous SQL result sets. Function version of CFQUERY
QueryRowStructReturns all the data in a query for a given row but as a structure
QueryRunExecutes the given SQL query against the given datasource, optionally passing in paramters. Function version of CFQUERY
QuerySetCellSets the given column within a query with the value at the given row, or the last row if not specified
QuerySortSorts the query based on the column specified and the order criteria given. Modifies the original query object
QuotedValueListReturns a quoted list of all the values, for a given column within the query, delimited by the value given
ToCsvTransforms the query object into a Comma Separated Value (CSV) block
ToHtmlTransforms the query object into an HTML TABLE block
ValueListReturns a list of all the values, for a given column within the query, delimited by the value given

These functions will greatly increase the speed and efficiency to which you can work with Query objects.

Many of the functions where available using other means. For example, QueryDeleteColumn could have been achieved by performing a query-of-queries leaving out the column you wanted to remove. This however had a huge overhead, as well as duplicating the data.

You can read more about the DataSource functions over at Alan Williamson's blog.

Thanks to Peter J Farrell for many of his suggestions.

One word. Woot! Happy New Year!

1 comment:

  1. Have you actually tried the thing?