Queries

  • Category:
  • ORM

Functions

  • DbPoolClear() clears all existing datasource connection
  • EncodeForSQL() Encodes the given string for safe output in a query to reduce the risk of SQL Injection attacks. _This method is not recommended_ - the use of query parameters are strongly encouraged as a stronger alternative.
  • GetMetadata() Returns back meta information depending on the object passed in
  • IsQuery() Determines whether value is a query.
  • IsValid() Tests whether a value meets a validation or data type rule.
  • IsWithinTransaction() Determines whether the current thread is within a transaction block or not.
  • Query() Create and populate a query object in the form query(columnName1:[1,2,3],columnName2:[4,5,6])
  • QueryAddColumn() Adds a column to a query and populates its rows with the contents of a one-dimensional array. Pads query columns, if necessary, to ensure that all columns have the same number of rows.
  • QueryAddRow() Adds rows to a query, either empty row(s) or you can add a row with data
  • QueryAppend() Adds all values of one query to the end of another query. Both queries need to have the same columns.
  • QueryClear() Clears all rows from a query
  • QueryClose() When using lazy="true", it is important to close the resultSet during a request or it will continue to use heap memory.
  • QueryColumnArray() return the column names as array
  • QueryColumnCount() column count of given query
  • QueryColumnData() Returns all the data in a query for a given column
  • QueryColumnExists() Determines whether a specific column is present in a query.
  • QueryColumnList() return the column names as list
  • QueryConvertForGrid() Use instead the function QuerySlice.
  • QueryCurrentRow() return the row where the pointer of the query is pointing ATM
  • QueryDeleteColumn() removes a row from a query and return removed row as array
  • QueryDeleteRow() Deletes the row within a query object. Modifies the original query object
  • QueryEach() call the given UDF/Closure with every row (struct) in the query.
  • QueryEvery() This function calls a given closure/function with every element in a given query and returns true, if all of the closure calls returns true.
  • QueryExecute() Executes a SQL query, returns the result.
  • QueryFilter() This function creates a new Query that returns all rows from an query that match the given filter.
  • QueryGetCell() return the value of a specific query cell
  • QueryGetCellByIndex() return the value of a specific query cell
  • QueryGetRow() this function is deprecated, use function QueryRowData instead.
  • QueryInsertAt() Adds data (query,struct or array) into query.
  • QueryIsEmpty() returns query empty or not
  • QueryKeyExists() Determines whether a specific column is present in a query.
  • QueryLazy() Executes a SQL query without loading the data entirely to memory. Instead it calls the given Function with every single row
  • QueryMap() Calls the given closure with every row in the query.
  • QueryNew() Creates an empty query (query object).
  • QueryPrepend() Adds all values of one query the begin to another query. Both queries need to have the same columns.
  • QueryRecordCount() row count of given query
  • QueryReduce() Iterates over every row of the given query and calls the closure with that row. This function will reduce the query to a single value and will return this value.
  • QueryRenameColumn() Rename a column
  • QueryReverse() reverse the row order of a complete query
  • QueryRowByIndex()
  • QueryRowData() Returns a struct with the data from a query for a given row number
  • QueryRowDataByIndex()
  • QueryRowSwap() swaps one row of a query with an other.
  • QuerySetCell() Sets a cell to a value. If no row number is specified, the cell on the last row is set.
  • QuerySetRow() Adds a row to a query, either by struct or array
  • QuerySlice() creates a new query containing a part of the given query
  • QuerySome() This function calls a given closure/function with every element in a given query and returns true, if one of the closure calls returns true.
  • QuerySort() Sorts the query based on the column specified and the order criteria given. Modifies the original query object
  • QueryToStruct() Copy the query columns data to struct by using the columnKey argument
  • TransactionCommit() commits a pending transaction
  • TransactionRollback() rolls back a pending transaction
  • TransactionSetSavePoint() Saves a specific state within a transaction
  • ValueArray() Returns an array of all the values, for a given column within the query. this function is deprecated, use instead the function queryColumnData
  • ValueList() Returns a list of all the values, for a given column within the query, delimited by the value given. this function is deprecated, use instead the function queryColumnData

Tags

  • <cfprocparam> Specifies parameter information, including type, name, value, and length. The cfprocparam tag is nested within a cfstoredproc tag.
  • <cfquery> Passes SQL statements to a data source. Not limited to queries.
  • <cfqueryparam> Checks the data type of a query parameter.
  • <cfstoredproc> Executes stored procedures by an ODBC or native connection to a server database. It specifies database connection information and identifies the stored procedure.
  • <cftransaction> Groups multiple queries into a single unit. The cftransaction tag provides commit and rollback processing.

Methods

  • query.addColumn() Adds a column to a query and populates its rows with the contents of a one-dimensional array. Pads query columns, if necessary, to ensure that all columns have the same number of rows.
  • query.addRow() Adds a specified number of empty rows to a query.
  • query.append() Adds all values of one query to the end of another query. Both queries need to have the same columns.
  • query.clear() Clears all rows from a query
  • query.columnArray() return the column names as array
  • query.columnCount() column count of given query
  • query.columnData() Returns all the data in a query for a given column
  • query.columnExists() Determines whether a specific column is present in a query.
  • query.columnList() return the column names as list
  • query.currentRow() return the row where the pointer of the query is pointing ATM
  • query.deleteColumn() removes a row from a query and return removed row as array
  • query.deleteRow() Deletes the row within a query object. Modifies the original query object
  • query.each() call the given UDF/Closure with every row (struct) in the query.
  • query.every() This function calls a given closure/function with every element in a given query and returns true, if all of the closure calls returns true.
  • query.filter() This function creates a new Query that returns all rows from an query that match the given filter.
  • query.getCell() return the value of a specific query cell
  • query.getCellByIndex() return the value of a specific query cell
  • query.getRow() this function is deprecated, use function QueryRowData instead.
  • query.insertAt() Adds data (query,struct or array) into query.
  • query.isEmpty() returns query empty or not
  • query.keyExists() Determines whether a specific column is present in a query.
  • query.map() Calls the given closure with every row in the given query. the function returns a query that contains all values returned by the closure.
  • query.prepend() Adds all values of one query the begin to another query. Both queries need to have the same columns.
  • query.recordCount() row count of given query
  • query.reduce() Iterates over every row of the given query and calls the closure with that row. This function will reduce the query to a single value and will return this value.
  • query.renameColumn() Rename a column
  • query.reverse() reverse the row order of a complete query
  • query.rowByIndex()
  • query.rowData() Returns a struct with the data from a query for a given row number
  • query.rowDataByIndex()
  • query.rowSwap() swaps one row of a query with an other.
  • query.setCell() Sets a cell to a value. If no row number is specified, the cell on the last row is set.
  • query.setRow() Adds a row to a query, either by struct or array
  • query.slice() creates a new query containing a part of the given query
  • query.some() This function calls a given closure/function with every element in a given query and returns true, if one of the closure calls returns true.
  • query.sort() Sorts the query based on the column specified and the order criteria given. Modifies the original query object
  • query.ToStruct() Copy the query columns data to struct by using the columnKey argument
  • string.encodeForSQL() Encodes the given string for safe output in a query to reduce the risk of SQL Injection attacks. _This method is not recommended_ - the use of query parameters are strongly encouraged as a stronger alternative.

Objects

Categories

  • ORM Object-relational mapping

Guides

  • Cache a query for the current request Cache a Query for the current request
  • Lazy Queries How to use lazy queries
  • Lucee Sql Types SQL types supported by lucee.
  • Query Handling In Lucee How to do SQL Queries with Lucee
  • Query Listeners A query listener is a hook which can be configured to run before and after a query is executed.
  • Query of Queries (QoQ) Query of queries (QoQ) is a technique for re-querying an existing (in memory) query without another trip to the database.
  • Query of Queries sometimes it rocks, sometimes it sucks ## The good, the bad and the ugly ## This document explains why Query of Queries (QoQ) may or not be best approach for your use case. - **PRO** it's nice to work with in memory datasets/queries using SQL. - **CON** is can be very slow, depending on the use case. Update, the performance of QoQ has been dramatically for single tables improved since 5
  • Query return type This document explains the different return types for a query with some examples.

See also