GetAndDeleteRow
GetAndDeleteRow method returns a single row from the data table and then deletes it. If more than one row is found, only first is returned (SELECT TOP(1)...) and deleted.
This method is suitable to work with single-use, disposable data, such as promo codes or coupons. For example: each subscriber can receive an unique value from data table. The value cannot be used again, because it is immediately deleted after use.
The method has similar parameters to GetRows method — returned columns and filters can be specified.
1. Method signature
row = GetAndDeleteRow(table, columns, filters, filterOperator)Parameter definition
| Name | Type | Description |
|---|---|---|
| Table | string | Required. Table name. |
| columns | array of string | Optional. Array of columns that will be retrieved. If set to null, all columns will be retrieved. |
| filters | array of Filter objects | Optional. Array of objects defining elements of WHERE phrase of SELECT query. Use this parameter, to restrict selected rows to those matching your criteria. If null, no criteria will be applied. If several filters are defined, by default, returned rows will match all of them (equivalent to logical AND).Default behavior can be overridden using filterOperator parameter. |
| filterOperator | enumeration | Optional. Logical operator used for filters. Can be one of the following:AND (filter1 AND filter2 AND filter3…) — this is the default valueOR (filter1 OR filter2 OR filter3…)ANDNOT (NOT filter1 AND NOT filter2 AND NOT filter3…) |
| row | array of key-value pairs | Return value. Row is an array of key-value pairs (column name => column value). Column name is string. Column value type depends on actual type in the database. null is returned if no data was found. See examples for more information. |
Filter object parameter definition
| Name | Type | Description |
|---|---|---|
| Name | string | Column name |
| Modifier | enumeration | Operation modifier. Can be one of the following:EQ (equals, «=»)GT (greater than, «>»)LT (lower than, «<«)LIKE (SQL LIKE operator) |
| Value | object | Column value. Value type depends on column type, e.g. when filtering by number column, integer value should be supplied, string for text columns etc. Simple type conversion is also supported, e.g. string «123» representing correct integer value 123 will be accepted. This also works for dates, floating point numbers etc. |
NOTE: Typically, Filter objects are created using constructors, which is more convenient — see examples for more information.
2. Using values returned by GetAndDeleteRow method
Values returned by GetAndDeleteRow are arrays of column values (key-value pairs). To access them, they need to be enumerated or accessed using indexes.
2.1. Enumerating columns
This method is suitable when we want to e.g. display all columns returned by the method. We simply treat the returned value as an array and use «each» attribute to browse it.
<table>
<tr>
<td each="var column in GetAndDeleteRow('MyCouponsTable')">
${column.Value}
</td>
</tr>
</table>2.2. Accessing columns directly
Since returned value is an array, we may access individual columns directly, using array indexing. In this case, column names are used as index, so we are required to know what columns can be expected. Using wrong column name will result in an error.
<var row="GetAndDeleteRows('MyCouponsTable')"/>
<div>Column no 1: ${row['MyFirstColumn']}</div>
<div>Column no 2: ${row['MySecondColumn']}</div>
<div>Column no 3: ${row['MyThirdColumn']}</div>3. Using parameters to filter data and return specific columns
Below are several examples of using GetAndDeleteRow method parameters to create different requests to data tables.
Example 1 — The most simple call
First found record from the table is returned and deleted. All columns are returned
GetAndDeleteRow('MyCouponsTable')Example 2 — Returning only specific columns
First found records from the table is returned and deleted. Only columns specified in the request are returned.
GetAndDeleteRow('MyCouponsTable', new [] {'Coupon', 'ExpirationDate'})Example 3 — Filtering records
First row matching specified criteria is returned and deleted. All columns are returned.
GetAndDeleteRow('MyCouponsTable', null, new [] {new Filter('PromoName', EQ, 'XMas Promo')})