GetAndDeleteRow - ExpertSender Help Center

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')})