Stored procedure to update a record
Default:
The SQL to perform the update. For example, if you need to use the following stored procedure EditRow to update a record for a table with columns col1 and col2, we would use:
call EditRow({col1},{col2})
and the variables {col1} and {col2} will be substituted with the correct values. Only non-readonly fields are passed to spEdit.
Running PHP Code in spEdit
We support two modes using = and ==
Mode =
For example:
$lens->spEdit = '=ProcessRec({name},{postcode})';
will execute the above PHP code (the function ProcessRecmust be defined), passing in the "name" and "postcode" fields:
function ProcessRec($name, $postcode)
{
global $DB;
if (strlen($postcode) < 5) {
printError("Invalid postcode");
return -2;
}
return DoStoreRecord($name,$postcode);
}
Mode ==
The problem with the old = mode is that if you add a new field to your SQL statement, it would not be reflected in spEdit. So in PHPLens 5, we have the new == mode, where we pass in all the column values in an associative array, and provide helper functions to generate and execute the SQL:
$lens->spEdit = "==spedit__aProducts";
Then you define your function (autogenerated for you in the imp.inc.php file):
# $conn = ADOdb connection
# $table = table to update
# $where = where clause
# $key = primary key value
# $cols = array holding $cols[COLNAME] => COLVALUE
# $lens = phpLens object
function spedit__aProducts(&$conn, $table, $where, $key, &$cols, &$lens)
{
# $conn->StartTrans();
$sql = lens_gen_update($conn, $table, $where, $cols, 'null');
# 'null' is the string value to be replaced with sql NULL
$ok = $conn->Execute($sql);
if ($ok) {
if ($conn->Affected_Rows() == 0) $ret = $lens->lang->errNoUpdateRecs;
else $ret = '';
} else $ret = lens_sql_error($conn, $lens);
# $conn->CompleteTrans();
return array($ret);
}
In this mode, when new fields are added to the editLens, they are automatically reflected in the spEdit == mode. Source code for lens_gen_update and lens_sql_error can be found in phplens-dynedit.inc.php.
Now suppose you want to uppercase all data, then you could add at the top of the function:
function spedit__aProducts(&$conn, $table, $where, $key, &$cols, &$lens)
{
foreach($cols as $name => $val) {
$cols[$name] = strtoupper($val);
}
:
:
If you want to prevent a specific field, say "PRODUCT" from being saved, you could:
function spedit__aProducts(&$conn, $table, $where, $key, &$cols, &$lens)
{
unset($cols['PRODUCT']);
:
:
If you want to add a field to the update statement, say "TOTALPRICE" which is not shown in phplens but is inside the database:
function spedit__aProducts(&$conn, $table, $where, $key, &$cols, &$lens)
{
$cols['TOTALPRICE'] = $cols['UNITS_ORDERED']*$cols['UNITPRICE'];
:
:
If you do not want a field value to be quoted because it is calling a function, prefix * to the field name:
function spedit__aProducts(&$conn, $table, $where, $key, &$cols, &$lens)
{
$cols['*TOTALPRICE'] = 'calcprice('.conn->qstr($cols['ORDERID']).')';
:
:
In any mode, if you return:
-1: Show Edit Record form and display SQL error message
-2: Show Edit Record form, and do not display any error message
array($error): Show Edit Record form, and display $error message (phpLens 3.3+)
other values: Assume successful save, switch to the firstState.
Please note that spEdit is not supported if you are using input type checkbox or select multiple. To perform validation, use the eventPreUpdateField property.
Compatibility Between spEdit and editMultiple Properties
Only columns defined in editLens and readonlyLens will be processed by spEdit when editMultiple = false. When editMultiple is true, only columns defined in editLens and are also visible in the grid (gridLens) are processed. All other columns will be set to null.
Some people have complained that spEdit does not work the same when editMultiple is enabled, namely readonly fields are not passed as parameters to spEdit when editMultiple = true.
The reason for this is that we have multiple html rendering engines. One for the grid (GridRenderer), one for editing (EditRenderer). When editMultiple = true, the editable fields are rendered by the EditRenderer engine, but readonly fields are rendered by the GridRenderer engine. The GridRenderer engine does not generate invisible input tags for the readonly fields. So we cannot pass these readonly fields to spEdit currently.
The workaround is to read the readonly fields yourself, or pass them as hidden fields using a powerLens:
<input type=hidden name=VAL"{PRIMARYKEY}" value="{VALUECOL}">
{_PRIMARY_KEY_}
Since phpLens 2.4.10, {_PRIMARYKEY_} is a pre-defined variable you can place in your spEdit string. This is very useful when you allow the primary key to be modified, as this variable will hold the old value of the primary key, while the actual variable name will hold the new value.
$lens->spEdit = '=InsertRecord({_PRIMARY_KEY_}, {KEY})';
For compound keys, if spEdit generates an SQL statement, then {_PRIMARYKEY_} will not hold the field value, but will generate the WHERE conditions as a string as shown below:
$lens->spEdit = 'update table set col1 = {COL1} where {_PRIMARYKEY_}';
For compound keys, if spEdit is PHP code, then {_PRIMARYKEY_} will be an array consisting of the primary key values (in the order defined in $lens->keyCompound).
Commits
We use the default auto-commit behaviour. So two insert statements will be treated as two separate transactions by default.
If you want to control your own transactions, you will need to use $DB->StartTrans() and $DB->CompleteTrans(), assuming that $DB has been pre-defined as the database connection.
The only exception is in 3.1 or later, when editMultiple=true. Then saving multiple records is treated as a single transaction, and StartTrans/CompleteTrans is called automatically for you by phpLens.
Error Handling
Using this property means that you, the programmer, will have to code all the error-handling (eg. duplicate record errors, constraint violations) and error message display yourself. Database error messages can be retrieved using
$err = $DB->ErrorMsg();
assuming your database connection global variable is $DB. If no error occurred, $err will be empty.
History
In phpLens 2.0 is support for calling PHP programs. Simply prepend an = sign to your code.
Before phpLens 3.1, we always assume that spEdit succeeds and the return value is ignored. Syntax
# example 1
$lens->spEdit = 'call EditRow({col1},{col2})';
# example 2 - add an update timestamp for oracle/mysql
$lens->spEdit = 'update table set col1 = {col1}, mod=SYSDATE where id={id}';
# example 3
$lens->spEdit = '==PHPFunction'; // cols passed in as array, see above Basic:Yes Advanced/Enterprise:Yes DynamicEdit:Yes [Version 1.0]
|