Stored procedure to insert a new record
Default:
The SQL to perform the insert. For example, if you need to use the following stored procedure NewRow to insert a record for a table with columns col1 and col2, we would use:
call NewRow({col1},{col2})
and the variables {col1} and {col2} will be substituted with the correct values. Only columns that are defined in newLens and readonlyLens will be substituted in.
Running PHP Code in spNew
We support two modes using = and ==
Mode =
For example:
$lens->spNew = '=InsertRec({name},{postcode})';
will execute the above PHP code (the function InsertRec must be defined), passing in the "name" and "postcode" fields:
function InsertRec($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 spNew. 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->spNew = "==spNew__aProducts";
Then you define your function (autogenerated for you in the imp.inc.php file):
# $conn = ADOdb connection
# $table = table to insert
# $cols = array holding $cols[COLNAME] => COLVALUE
# $lens = phpLens object
function spnew__aProducts(&$conn, $table, &$cols, &$lens)
{
# $conn->StartTrans();
$sql = lens_gen_insert($conn, $table, $cols, 'null');
# 'null' is the string value to be replaced with sql NULL
$ok = $conn->Execute($sql);
if ($ok) $ret = '';
else $ret = lens_sql_error($conn, $lens);
# $conn->CompleteTrans();
return array($ret);
}
In this mode, when new fields are added to the newLens, they are automatically reflected in the spNew == mode. Source code for lens_gen_insert 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 spnew__aProducts(&$conn, $table, &$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 spnew__aProducts(&$conn, $table, &$cols, &$lens)
{
unset($cols['PRODUCT']);
:
:
If you want to add a field to the insert statement, say "TOTALPRICE" which is not shown in phplens but is inside the database:
function spnew__aProducts(&$conn, $table, &$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 spnew__aProducts(&$conn, $table, &$cols, &$lens)
{
$cols['*TOTALPRICE'] = 'calcprice('.conn->qstr($cols['ORDERID']).')';
:
:
In all modes, if you return:
-1: Show New Record form and display SQL error message
-2: Show New Record form, and do not display any error message
array($error): Show New Record form, display $error msg (phpLens 3.3+)
other values: Assume successful save, switch to the firstState.
To perform validation, use the eventPreInsertField property.
Commits
We use the default auto-commit behaviour. If you want to control your own transactions, you will need to use $DB->BeginTrans() and $DB->EndTrans(), assuming that $DB has been pre-defined as the database connection.
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 spNew succeeds and the return value is ignored.Syntax
$lens->spNew = 'call NewRow({col1},{col2})';
or
$lens->spNew = 'insert into table values({col1},{col2})';
or
$lens->spNew = '=PHPFunction({col1},{col2})';
or
$lens->spNew = '==PHPFunction'; // cols passed in as array Basic:Yes Advanced/Enterprise:Yes DynamicEdit:Yes [Version 1.0]
|