Index: phpdoc/it/functions/sesam.xml
+++ phpdoc/it/functions/sesam.xml
SESAM database functionsSESAM
SESAM/SQL-Server is a mainframe database system, developed by
Fujitsu Siemens Computers, Germany. It runs on high-end
mainframe servers using the operating system BS2000/OSD.
In numerous productive BS2000 installations, SESAM/SQL-Server
has proven ...
the ease of use of Java-, Web- and client/server
connectivity,the capability to work with an availability of more
than 99.99%,the ability to manage tens and even hundreds of
thousands of users.
Now there is a PHP3 SESAM interface available which allows
database operations via PHP-scripts.
Configuration notes
There is no standalone support for the PHP SESAM interface,
it works only as an integrated Apache module. In the Apache PHP
module, this SESAM interface is
configured using Apache directives.
SESAM Configuration directivesDirectiveMeaningphp3_sesam_omlName of BS2000 PLAM library containing the
loadable SESAM driver modules.
Required for using SESAM functions.
Example:
php3_sesam_oml $.SYSLNK.SESAM-SQL.030php3_sesam_configfileName of SESAM application configuration file.
Required for using SESAM functions.
Example:
php3_sesam_configfile $SESAM.SESAM.CONF.AW
It will usually contain a configuration like (see SESAM
reference manual):
CNF=B
NAM=K
NOTYPEphp3_sesam_messagecatalogName of SESAM message catalog file.
In most cases, this directive is not neccessary. Only if the
SESAM message file is not installed in the system's BS2000
message file table, it can be set with this directive.
Example:
php3_sesam_messagecatalog $.SYSMES.SESAM-SQL.030
In addition to the configuration of the PHP/SESAM interface,
you have to configure the SESAM-Database server itself on your
mainframe as usual. That means:
starting the SESAM database handler (DBH), andconnecting the databases with the SESAM database
handler
To get a connection between a PHP script and the database
handler, the CNF and NAM
parameters of the selected SESAM configuration file must match
the id of the started database handler.
In case of distributed databases you have to start a
SESAM/SQL-DCN agent with the distribution table including the
host and database names.
The communication between PHP (running in the POSIX subsystem)
and the database handler (running outside the POSIX subsystem)
is realized by a special driver module called SQLSCI and SESAM
connection modules using common memory.
Because of the common memory access, and because PHP is a
static part of the web server, database accesses are very fast,
as they do not require remote accesses via ODBC, JDBC or UTM.
Only a small stub loader (SESMOD) is linked with PHP, and the
SESAM connection modules are pulled in from SESAM's OML PLAM
library. In the configuration, you must tell
PHP the name of this PLAM library, and the file link to use for
the SESAM configuration file (As of SESAM V3.0, SQLSCI is
available in the SESAM Tool Library, which is part of the
standard distribution).
Because the SQL command quoting for single quotes uses duplicated
single quotes (as opposed to a single quote preceded by a
backslash, used in some other databases), it is advisable to
set the PHP configuration directives php3_magic_quotes_gpc
and php3_magic_quotes_sybase
to On for all PHP scripts using the SESAM
interface.
Runtime considerations
Because of limitations of the BS2000 process model, the driver can
be loaded only after the Apache server has forked off its server child
processes. This will slightly slow down the initial SESAM request
of each child, but subsequent accesses will respond at full speed.
When explicitly defining a Message Catalog for SESAM, that
catalog will be loaded each time the driver is loaded (i.e., at
the initial SESAM request). The BS2000 operating system prints
a message after successful load of the message catalog, which
will be sent to Apache's error_log file. BS2000 currently does
not allow suppression of this message, it will slowly fill up
the log.
Make sure that the SESAM OML PLAM library and SESAM
configuration file are readable by the user id running the web server.
Otherwise, the server will be unable to load the driver, and
will not allow to call any SESAM functions. Also, access to
the database must be granted to the user id under which the
Apache server is running. Otherwise, connections to the
SESAM database handler will fail.
Cursor Types
The result cursors which are allocated for SQL "select type"
queries can be either "sequential" or "scrollable". Because
of the larger memory overhead needed by "scrollable" cursors,
the default is "sequential".
When using "scrollable" cursors, the cursor can be freely
positioned on the result set. For each "scrollable" query,
there are global default values for the scrolling type
(initialized to: SESAM_SEEK_NEXT) and
the scrolling offset which can either be set once
by sesam_seek_row or
each time when fetching a row using
sesam_fetch_row. When fetching a row
using a "scrollable" cursor, the following post-processing is
done for the global default values for the scrolling type
and scrolling offset:
Scrolled Cursor Post-ProcessingScroll TypeActionSESAM_SEEK_NEXTnoneSESAM_SEEK_PRIORnoneSESAM_SEEK_FIRSTset scroll type to
SESAM_SEEK_NEXTSESAM_SEEK_LASTset scroll type to
SESAM_SEEK_PRIORSESAM_SEEK_ABSOLUTEAuto-Increment internal offset valueSESAM_SEEK_RELATIVEnone. (maintain global default
offset value, which allows
for, e.g., fetching each 10th row backwards)
Porting note
Because in the PHP world it is natural to start indexes at zero
(rather than 1), some adaptions have been made to the SESAM
interface: whenever an indexed array is starting with index 1
in the native SESAM interface, the PHP interface uses
index 0 as a starting point. E.g., when retrieving columns with
sesam_fetch_row, the first column has the
index 0, and the subsequent columns have indexes up to (but not
including) the column count ($array["count"]).
When porting SESAM applications from other high level languages
to PHP, be aware of this changed interface. Where appropriate,
the description of the respective php sesam functions include a
note that the index is zero based.
Security concerns
When allowing access to the SESAM databases, the web server
user should only have as little privileges as possible. For most
databases, only read access privilege should be granted.
Depending on your usage scenario, add more access rights as
you see fit. Never allow full control to any database for
any user from the 'net! Restrict access to php scripts
which must administer the database by using password control
and/or SSL security.
Migration from other SQL databases
No two SQL dialects are ever 100% compatible. When porting
SQL applications from other database interfaces to SESAM,
some adaption may be required. The following typical
differences should be noted:
Vendor specific data typesSome vendor specific data types may have to be
replaced by standard SQL data types
(e.g., TEXT could be replaced by
VARCHAR(max. size)).
Keywords as SQL identifiersIn SESAM (as in standard SQL), such identifiers must
be enclosed in double quotes (or renamed).Display length in data typesSESAM data types have a precision, not a display
length. Instead of int(4) (intended use:
integers up to '9999'), SESAM requires simply
int for an implied size of 31 bits. Also,
the only datetime data types available in SESAM are:
DATE, TIME(3) and
TIMESTAMP(3).
SQL types with vendor-specific
unsigned, zerofill, or
auto_increment attributesUnsigned and
zerofill are not supported.
Auto_increment is automatic (use "INSERT ...
VALUES(*, ...)" instead of "... VALUES(0,
...)" to take advantage of SESAM-implied
auto-increment. int ... DEFAULT '0000'Numeric variables must not be initialized with
string constants. Use DEFAULT 0
instead. To initialize variables of the datetime
SQL data types, the initialization string must be
prefixed with the respective type keyword, as in:
CREATE TABLE exmpl (
xtime timestamp(3) DEFAULT TIMESTAMP '1970-01-01 00:00:00.000' NOT NULL
); $count = xxxx_num_rows();Some databases promise to guess/estimate the
number of the rows in a query result, even though
the returned value is grossly incorrect. SESAM
does not know the number of rows in a query result
before actually fetching them. If you REALLY need
the count, try SELECT COUNT(...) WHERE
..., it will tell you the number of hits.
A second query will (hopefully) return the results.
DROP TABLE thename;In SESAM, in the DROP TABLE
command, the table name must be either followed by
the keyword RESTRICT or
CASCADE. When specifying
RESTRICT, an error is returned if
there are dependent objects (e.g., VIEWs), while with
CASCADE, dependent objects will be
deleted along with the specified table.
Notes on the use of various SQL types
SESAM does not currently support the BLOB type. A future
version of SESAM will have support for BLOB.
At the PHP interface, the following type conversions are
automatically applied when retrieving SQL fields:
SQL to PHP Type ConversionsSQL TypePHP TypeSMALLINT, INTEGER"integer"NUMERIC, DECIMAL, FLOAT, REAL, DOUBLE"double"DATE, TIME, TIMESTAMP"string"VARCHAR, CHARACTER"string"
When retrieving a complete row, the result is returned as an
array. Empty fields are not filled in, so you will have to
check for the existence of the individual fields yourself
(use isset or
empty to test for empty fields).
That allows more user control over the appearance of empty
fields (than in the case of an empty string as the
representation of an empty field).
Support of SESAM's "multiple fields" feature
The special "multiple fields" feature of SESAM allows a column to
consist of an array of fields. Such a "multiple field" column can
be created like this:
Creating a "multiple field" column
CREATE TABLE multi_field_test
(
pkey CHAR(20) PRIMARY KEY,
multi(3) CHAR(12)
)
and can be filled in using:
Filling a "multiple field" column
INSERT INTO multi_field_test ( pkey, multi(2..3) )
VALUES ( 'Second', <'first_val','second_val'>)
Note that (like in this case) leading empty sub-fields are ignored,
and the filled-in values are collapsed, so that in the above
example the result will appear as multi(1..2) instead of
multi(2..3).
When retrieving a result row, "multiple columns" are accessed like
"inlined" additional columns. In the example above, "pkey" will have
the index 0, and the three "multi(1..3)" columns will be accessible
as indices 1 through 3.
For specific SESAM details, please refer to
the SESAM/SQL-Server
documentation (english) or
the SESAM/SQL-Server documentation (german),
both available online, or use the respective manuals.
sesam_connectOpen SESAM database connectionDescriptionboolean sesam_connectstring catalogstring schemastring user
Returns TRUE if a connection to the SESAM database was made, or
FALSE on error.
sesam_connect establishes a connection to an
SESAM database handler task. The connection is always
"persistant" in the sense that only the very first invocation will
actually load the driver from the configured SESAM OML PLAM
library. Subsequent calls will reuse the driver and will
immediately use the given catalog, schema, and user.
When creating a database, the "catalog"
name is specified in the
SESAM configuration directive //ADD-SQL-DATABASE-CATALOG-LIST
ENTRY-1 = *CATALOG(CATALOG-NAME = catalogname,...)
The "schema" references the desired
database schema (see SESAM handbook).
The "user" argument references one of the
users which are allowed to access this
"catalog" / "schema"
combination. Note that "user" is completely
independent from both the system's user id's and from HTTP
user/password protection. It appears in the SESAM configuration
only.
See also sesam_disconnect.
Connect to a SESAM database
<?php
if (! sesam_connect ("mycatalog", "myschema", "otto")
die("Unable to connect to SESAM";
?>
sesam_disconnectDetach from SESAM connectionDescriptionboolean sesam_disconnect
Returns: always TRUE.
sesam_disconnect closes the logical link to a
SESAM database (without actually disconnecting and unloading the
driver).
Note that this isn't usually necessary, as the open connection is
automatically closed at the end of the script's execution.
Uncommitted data will be discarded, because an implicit
sesam_rollback is executed.
sesam_disconnect will not close the
persistent link, it will only invalidate the currently defined
"catalog", "schema"
and "user" triple, so that any sesam
function called after sesam_disconnect will
fail.
See also: sesam_connect.
Closing a SESAM connection
if (sesam_connect ("mycatalog", "myschema", "otto")) {
.. some queries and stuff ...
sesam_disconnect(); }
sesam_settransactionSet SESAM transaction parametersDescriptionboolean sesam_settransactionint isolation_levelint read_only
Returns: TRUE if the values are valid, and the
settransaction operation was successful,
FALSE otherwise.
sesam_settransaction overrides the default
values for the "isolation level" and "read-only" transaction
parameters (which are set in the SESAM configuration file), in
order to optimize subsequent queries and guarantee database
consistency. The overridden values are used for the next transaction
only.
sesam_settransaction can only be called before
starting a transaction, not after the transaction has been started
already.
To simplify the use in php scripts, the following constants have
been predefined in php (see SESAM handbook for detailed
explanation of the semantics):
Valid values for <parameter>"Isolation_Level"</parameter> parameterValueConstantMeaning1SESAM_TXISOL_READ_UNCOMMITTEDRead Uncommitted2SESAM_TXISOL_READ_COMMITTEDRead Committed3SESAM_TXISOL_REPEATABLE_READRepeatable Read4SESAM_TXISOL_SERIALIZABLESerializable
Valid values for <parameter>"Read_Only"</parameter> parameterValueConstantMeaning0SESAM_TXREAD_READWRITERead/Write1SESAM_TXREAD_READONLYRead-Only
The values set by sesam_settransaction will
override the default setting specified in the SESAM configuration file.
Setting SESAM transaction parameters
<?php
sesam_settransaction(SESAM_TXISOL_REPEATABLE_READ,
SESAM_TXREAD_READONLY);
?>
sesam_commitCommit pending updates to the SESAM databaseDescriptionboolean sesam_commit
Returns: TRUE on success,
FALSE on errors
sesam_commit commits any pending updates to
the database.
Note that there is no "auto-commit" feature as in other databases,
as it could lead to accidental data loss. Uncommitted data at
the end of the current script (or when calling
sesam_disconnect) will be discarded by an
implied sesam_rollback call.
See also: sesam_rollback.
Committing an update to the SESAM database
<?php
if (sesam_connect ("mycatalog", "myschema", "otto")) {
if (!sesam_execimm("INSERT INTO mytable VALUES (*, 'Small Test', <0, 8, 15>)"))
die("insert failed");
if (!sesam_commit())
die("commit failed");
}
?>
sesam_rollbackDiscard any pending updates to the SESAM databaseDescriptionboolean sesam_rollback
Returns: TRUE on success,
FALSE on errors
sesam_rollback discards any pending updates to
the database. Also affected are result cursors and result
descriptors.
At the end of each script, and as part of the
sesam_disconnect function, an implied
sesam_rollback is executed, discarding any
pending changes to the database.
See also: sesam_commit.
Discarding an update to the SESAM database
<?php
if (sesam_connect ("mycatalog", "myschema", "otto")) {
if (sesam_execimm("INSERT INTO mytable VALUES (*, 'Small Test', <0, 8, 15>)")
&& sesam_execimm("INSERT INTO othertable VALUES (*, 'Another Test', 1)"))
sesam_commit();
else
sesam_rollback();
}
?>
sesam_execimmExecute an "immediate" SQL-statementDescriptionstring sesam_execimmstring query
Returns: A SESAM "result identifier" on success, or
FALSE on error.
sesam_execimm executes an "immediate"
statement (i.e., a statement like UPDATE, INSERT or DELETE which
returns no result, and has no INPUT or OUTPUT variables).
"select type" queries can not be used with
sesam_execimm. Sets the
affected_rows value for retrieval by the
sesam_affected_rows function.
Note that sesam_query can handle both
"immediate" and "select-type" queries. Use
sesam_execimm only if you know beforehand
what type of statement will be executed.
An attempt to use SELECT type queries with
sesam_execimm will return
$err["sqlstate"] == "42SBW".
The returned "result identifier" can not be used for retrieving
anything but the sesam_affected_rows; it is
only returned for symmetry with the
sesam_query function.
$stmt = "INSERT INTO mytable VALUES('one', 'two')";
$result = sesam_execimm ($stmt);
$err = sesam_diagnostic();
print("sqlstate = ".$err["sqlstate"]."\n".
"Affected rows = ".$err["rowcount"]." == ".
sesam_affected_rows($result)."\n");
See also: sesam_query and
sesam_affected_rows.
sesam_queryPerform a SESAM SQL query and prepare the resultDescriptionstring sesam_querystring queryboolean
scrollable
Returns: A SESAM "result identifier" on success, or
FALSE on error.
A "result_id" resource is used by other functions to retrieve the
query results.
sesam_query sends a query to the currently
active database on the server. It can execute both
"immediate" SQL statements and "select type" queries. If an
"immediate" statement is executed, then no cursor is allocated,
and any subsequent sesam_fetch_row or
sesam_fetch_result call will return an empty
result (zero columns, indicating end-of-result).
For "select type" statements, a result descriptor and a
(scrollable or sequential, depending on the optional
boolean scrollable parameter) cursor will
be allocated. If scrollable is omitted,
the cursor will be sequential.
When using "scrollable" cursors, the cursor can be freely
positioned on the result set. For each "scrollable" query,
there are global default values for the scrolling type
(initialized to: SESAM_SEEK_NEXT) and
the scrolling offset which can either be set once
by sesam_seek_row or
each time when fetching a row using
sesam_fetch_row.
For "immediate" statements, the number of affected
rows is saved for retrieval by the
sesam_affected_rows function.
See also: sesam_fetch_row and
sesam_fetch_result.
Show all rows of the "phone" table as a html table
<?php
if (!sesam_connect("phonedb", "demo", "otto"))
die("cannot connect");
$result = sesam_query("select * from phone");
if (!$result) {
$err = sesam_diagnostic();
die($err["errmsg"]);
}
echo "<TABLE BORDER>\n";
// Add title header with column names above the result:
if ($cols = sesam_field_array($result)) {
echo " <TR><TH COLSPAN=".$cols["count"].">Result:</TH></TR>\n";
echo " <TR>\n";
for ($col = 0; $col < $cols["count"]; ++$col) {
$colattr = $cols[$col];
/* Span the table head over SESAM's "Multiple Fields": */
if ($colattr["count"] > 1) {
echo " <TH COLSPAN=".$colattr["count"].">".$colattr["name"].
"(1..".$colattr["count"].")</TH>\n";
$col += $colattr["count"] - 1;
}
else
echo " <TH>" . $colattr["name"] . "</TH>\n";
}
echo " </TR>\n";
}
do {
// Fetch the result in chunks of 100 rows max.
$ok = sesam_fetch_result($result,100);
for ($row=0; $row < $ok["rows"]; ++$row) {
echo " <TR>\n";
for ($col = 0; $col < $ok["cols"]; ++$col) {
if (isset($ok[$col][$row]))
echo " <TD>" . $ok[$col][$row] . "</TD>\n";
else
echo " <TD>-empty-</TD>\n";
}
echo " </TR>\n";
}
} while ($ok["truncated"]); // while there may be more data
echo "</TABLE>\n";
// free result id
sesam_free_result($result);
?>
sesam_num_fieldsReturn the number of fields/columns in a result setDescriptionint sesam_num_fieldsstring result_id
After calling sesam_query with a
"select type" query, this function gives you the
number of columns in the result.
Returns an integer describing the total number of columns (aka.
fields) in the current result_id result
set or FALSE on error.
For "immediate" statements, the value zero is returned. The SESAM
"multiple field" columns count as their respective dimension,
i.e., a three-column "multiple field" counts as three columns.
See also: sesam_query and
sesam_field_array for a way to distinguish
between "multiple field" columns and regular columns.
sesam_field_name
Return one column name of the result set
Descriptionint sesam_field_namestring result_idint index
Returns the name of a field (i.e., the column name) in the result
set, or FALSE on error.
For "immediate" queries, or for dynamic columns, an empty string
is returned.
The column index is zero-based, not one-based as in SESAM.
See also: sesam_field_array. It provides an
easier interface to access the column names and types, and allows
for detection of "multiple fields".
sesam_diagnosticReturn status information for last SESAM callDescriptionarray sesam_diagnostic
Returns an associative array of status and return codes for the
last SQL query/statement/command.
Elements of the array are:
Status information returned by <function>sesam_diagnostic</function>
ElementContents$array["sqlstate"]5 digit SQL return code (see the SESAM manual for the
description of the possible values of SQLSTATE)$array["rowcount"]number of affected rows in last update/insert/delete
(set after "immediate" statements only)
$array["errmsg"]"human readable" error message string
(set after errors only)
$array["errcol"]error column number of previous error
(0-based; or -1 if undefined. Set after errors only)
$array["errlin"]error line number of previous error
(0-based; or -1 if undefined. Set after errors only)
In the following example, a syntax error (E SEW42AE ILLEGAL
CHARACTER) is displayed by including the offending SQL statement
and pointing to the error location:
Displaying SESAM error messages with error position
<?php
// Function which prints a formatted error message,
// displaying a pointer to the syntax error in the
// SQL statement
function PrintReturncode($exec_str)
{
$err = Sesam_Diagnostic();
$colspan=4; // 4 cols for: sqlstate, errlin, errcol, rowcount
if ($err["errlin"] == -1)
--$colspan;
if ($err["errcol"] == -1)
--$colspan;
if ($err["rowcount"] == 0)
--$colspan;
echo "<TABLE BORDER>\n";
echo "<TR><TH COLSPAN=".$colspan."><FONT COLOR=red>ERROR:</FONT> ".
htmlspecialchars($err["errmsg"])."</TH></TR>\n";
if ($err["errcol"] >= 0) {
echo "<TR><TD COLSPAN=".$colspan."><PRE>\n";
$errstmt = $exec_str."\n";
for ($lin=0; $errstmt != ""; ++$lin) {
if ($lin != $err["errlin"]) { // $lin is less or greater than errlin
if (! ($i = strchr($errstmt, "\n")))
$i = "";
$line = substr($errstmt, 0, strlen($errstmt)-strlen($i)+1);
$errstmt = substr($i, 1);
if ($line != "\n")
print htmlspecialchars($line);
}
else {
if (! ($i = strchr($errstmt, "\n")))
$i = "";
$line = substr($errstmt, 0, strlen($errstmt)-strlen($i)+1);
$errstmt = substr($i, 1);
for ($col=0; $col < $err["errcol"]; ++$col)
echo (substr($line, $col, 1) == "\t") ? "\t" : ".";
echo "<FONT COLOR=RED><BLINK>\\</BLINK></FONT>\n";
print "<FONT COLOR=\"#880000\">".htmlspecialchars($line)."</FONT>";
for ($col=0; $col < $err["errcol"]; ++$col)
echo (substr($line, $col, 1) == "\t") ? "\t" : ".";
echo "<FONT COLOR=RED><BLINK>/</BLINK></FONT>\n";
}
}
echo "</PRE></TD></TR>\n";
}
echo "<TR>\n";
echo " <TD>sqlstate=" . $err["sqlstate"] . "</TD>\n";
if ($err["errlin"] != -1)
echo " <TD>errlin=" . $err["errlin"] . "</TD>\n";
if ($err["errcol"] != -1)
echo " <TD>errcol=" . $err["errcol"] . "</TD>\n";
if ($err["rowcount"] != 0)
echo " <TD>rowcount=" . $err["rowcount"] . "</TD>\n";
echo "</TR>\n";
echo "</TABLE>\n";
}
if (!sesam_connect("mycatalog", "phoneno", "otto"))
die("cannot connect");
$stmt = "SELECT * FROM phone\n".
" WHERE@ LASTNAME='KRAEMER'\n".
" ORDER BY FIRSTNAME";
if (! ($result = sesam_query($stmt)))
PrintReturncode($stmt);
?>
See also: sesam_errormsg for simple access to the
error string only
sesam_fetch_resultReturn all or part of a query resultDescriptionmixed sesam_fetch_resultstring
result_idint
max_rows
Returns a mixed array with the query result entries, optionally
limited to a maximum of max_rows rows.
Note that both row and column indexes are zero-based.
Mixed result set returned by <function>sesam_fetch_result</function>
Array ElementContentsint $arr["count"]number of columns in result set (or zero if this was an
"immediate" query)int $arr["rows"]number of rows in result set (between zero and
max_rows)boolean $arr["truncated"]TRUE if the number of rows was at least
max_rows, FALSE otherwise. Note
that even when this is TRUE, the next
sesam_fetch_result call may return
zero rows because there are no more result entries.
mixed $arr[col][row]result data for all the fields at
row(row) and
column(col), (where the integer
index row is
between 0 and $arr["rows"]-1, and
col is between 0
and $arr["count"]-1). Fields
may be empty, so you must check for the existence of a
field by using the php isset
function. The type of the returned fields depend on the
respective SQL type declared for its column (see SESAM overview for the
conversions applied). SESAM "multiple
fields" are "inlined" and treated like a sequence
of columns.
Note that the amount of memory used up by a large query may be
gigantic. Use the max_rows parameter to
limit the maximum number of rows returned, unless you are
absolutely sure that your result will not use up all available
memory.
See also: sesam_fetch_row, and
sesam_field_array to check for "multiple
fields". See the description of the
sesam_query function for a complete example
using sesam_fetch_result.
sesam_affected_rowsGet number of rows affected by an immediate queryDescriptionint sesam_affected_rowsstring
result_idresult_id is a valid result id returned by
sesam_query.
Returns the number of rows affected by a query associated with
result_id.
The sesam_affected_rows function can only
return useful values when used in combination with "immediate" SQL
statements (updating operations like INSERT,
UPDATE and DELETE) because
SESAM does not deliver any "affected rows" information for "select
type" queries.
The number returned is the number of affected rows.
See also: sesam_query and
sesam_execimm
$result = sesam_execimm ("DELETE FROM PHONE WHERE LASTNAME = '".strtoupper($name)."'");
if (! $result) {
... error ...
}
print sesam_affected_rows($result).
" entries with last name ".$name." deleted.\n"
sesam_errormsgReturns error message of last SESAM callDescriptionstring sesam_errormsg
Returns the SESAM error message associated with the most
recent SESAM error.
if (!sesam_execimm($stmt))
printf("%s<br>\n", sesam_errormsg());
See also: sesam_diagnostic for the full set
of SESAM SQL status information
sesam_field_array
Return meta information about individual columns in a result
Descriptionarray sesam_field_arraystring result_idresult_id is a valid result id returned by
sesam_query.
Returns a mixed associative/indexed array with meta information
(column name, type, precision, ...) about individual columns of the result after the
query associated with result_id.
Mixed result set returned by <function>sesam_field_array</function>
Array ElementContentsint $arr["count"]Total number of columns in result set (or zero if
this was an "immediate" query). SESAM "multiple
fields" are "inlined" and treated like the
respective number of columns.
string $arr[col]["name"]column name for column(col),
where col is between 0 and
$arr["count"]-1. The returned
value can be the empty string (for dynamically
computed columns). SESAM "multiple fields" are
"inlined" and treated like the respective number of
columns, each with the same column name.
string $arr[col]["count"]The "count" attribute describes the repetition
factor when the column has been declared as a
"multiple field". Usually, the "count" attribute is
1. The first column of a "multiple field" column
however contains the number of repetitions (the
second and following column of the "multiple field"
contain a "count" attribute of 1). This can be used
to detect "multiple fields" in the result set. See
the example shown in the
sesam_query description for a
sample use of the "count" attribute.
string $arr[col]["type"]php variable type of the data for
column(col), where
col is between 0
and $arr["count"]-1. The returned
value can be one of
"integer""double""string"
depending on the SQL type of the result. SESAM
"multiple fields" are "inlined" and treated like the
respective number of columns, each with the same php
type.
string $arr[col]["sqltype"]SQL variable type of the column data for
column(col), where
col is between 0
and $arr["count"]-1. The returned
value can be one of
"CHARACTER""VARCHAR""NUMERIC""DECIMAL""INTEGER""SMALLINT""FLOAT""REAL""DOUBLE""DATE""TIME""TIMESTAMP"
describing the SQL type of the result. SESAM
"multiple fields" are "inlined" and treated like the
respective number of columns, each with the same SQL
type.
string $arr[col]["length"]The SQL "length" attribute of the SQL variable in
column(col), where
col is between 0 and
$arr["count"]-1. The "length"
attribute is used with "CHARACTER" and "VARCHAR" SQL
types to specify the (maximum) length of the string
variable. SESAM "multiple fields" are "inlined" and
treated like the respective number of columns, each with
the same length attribute.
string $arr[col]["precision"]The "precision" attribute of the SQL variable in
column(col), where
col is between 0 and
$arr["count"]-1. The "precision"
attribute is used with numeric and time data types.
SESAM "multiple fields" are "inlined" and
treated like the respective number of columns, each with
the same precision attribute.
string $arr[col]["scale"]The "scale" attribute of the SQL variable in
column(col), where
col is between 0 and
$arr["count"]-1. The "scale"
attribute is used with numeric data types.
SESAM "multiple fields" are "inlined" and
treated like the respective number of columns, each with
the same scale attribute.
See the sesam_query function
for an example of the sesam_field_array use.
sesam_fetch_rowFetch one row as an arrayDescriptionarray sesam_fetch_rowstring result_idint
whenceint
offset
Returns an array that corresponds to the fetched row,
or FALSE if there are no more rows.
The number of columns in the result set is returned in an
associative array element $array["count"]. Because some of the
result columns may be empty, the
count function can not be used on
the result row returned by
sesam_fetch_row.
result_id is a valid result id returned by
sesam_query (select type queries only!).
whence is an
optional parameter for a fetch operation on "scrollable"
cursors, which can be set to the following predefined
constants:
Valid values for <parameter>"whence"</parameter> parameterValueConstantMeaning0SESAM_SEEK_NEXTread sequentially (after fetch, the internal default is
set to SESAM_SEEK_NEXT)
1SESAM_SEEK_PRIORread sequentially backwards (after fetch, the internal
default is set to SESAM_SEEK_PRIOR)
2SESAM_SEEK_FIRSTrewind to first row (after fetch, the default is set to
SESAM_SEEK_NEXT)3SESAM_SEEK_LASTseek to last row (after fetch, the default is set to
SESAM_SEEK_PRIOR)4SESAM_SEEK_ABSOLUTEseek to absolute row number given as
offset (Zero-based. After fetch,
the internal default is
set to SESAM_SEEK_ABSOLUTE, and the
internal offset value is auto-incremented)5SESAM_SEEK_RELATIVEseek relative to current scroll position, where
offset can be a positive or
negative offset value.
This parameter is only valid for "scrollable" cursors.
When using "scrollable" cursors, the cursor can be freely
positioned on the result set. If the
whence parameter
is omitted, the global default values for the scrolling type
(initialized to: SESAM_SEEK_NEXT, and
settable by sesam_seek_row) are used. If
whence is
supplied, its value replaces the global default.
offset is an
optional parameter which is only evaluated (and required)
if whence is
either SESAM_SEEK_RELATIVE or
SESAM_SEEK_ABSOLUTE. This
parameter is only valid for "scrollable" cursors.
sesam_fetch_row fetches one row of data
from the result associated with the specified result
identifier. The row is returned as an array (indexed by
values between 0 and
$array["count"]-1). Fields may be empty,
so you must check for the existence of a field by using the
php isset function. The type of the
returned fields depend on the respective SQL type declared for
its column (see SESAM
overview for the conversions applied). SESAM "multiple
fields" are "inlined" and treated like a sequence of columns.
Subsequent calls to sesam_fetch_row would
return the next (or prior, or n'th next/prior, depending on
the scroll attributes) row in the result set, or
FALSE if there are no
more rows.
SESAM fetch rows
<?php
$result = sesam_query ("SELECT * FROM phone\n".
" WHERE LASTNAME='".strtoupper($name)."'\n".
" ORDER BY FIRSTNAME", 1);
if (! $result) {
... error ...
}
// print the table in backward order
print "<TABLE BORDER>\n";
$row = sesam_fetch_row ($result, SESAM_SEEK_LAST);
while (is_array($row)) {
print " <TR>\n";
for($col = 0; $col < $row["count"]; ++$col) {
print " <TD>".htmlspecialchars($row[$col])."</TD>\n";
}
print " </TR>\n";
// use implied SESAM_SEEK_PRIOR
$row = sesam_fetch_row ($result);
}
print "</TABLE>\n";
sesam_free_result ($result);
?>
See also: sesam_fetch_array which returns
an associative array, and sesam_fetch_result
which returns many rows per invocation.
sesam_fetch_arrayFetch one row as an associative arrayDescriptionarray sesam_fetch_arraystring result_idint
whenceint
offset
Returns an array that corresponds to the fetched row,
or FALSE if there are no more rows.
sesam_fetch_array is an alternative
version of sesam_fetch_row. Instead
of storing the data in the numeric indices of the result
array, it stores the data in associative indices, using the
field names as keys.
result_id is a valid result id returned by
sesam_query (select type queries only!).
For the valid values of the optional
whenceand
offset
parameters, see the sesam_fetch_row
function for details.
sesam_fetch_array fetches one row of data from
the result associated with the specified result identifier. The
row is returned as an associative array. Each result
column is stored with an associative index equal to its
column (aka. field) name. The column names are converted to lower case.
Columns without a field name (e.g., results of arithmetic
operations) and empty fields are not stored in the array.
Also, if two or more columns of the result have the same
column names, the later column will take precedence. In
this situation, either call
sesam_fetch_row or make an alias for
the column.
SELECT TBL1.COL AS FOO, TBL2.COL AS BAR FROM TBL1, TBL2
A special handling allows fetching "multiple field"
columns (which would otherwise all have the same column
names). For each column of a "multiple field", the
index name is constructed by appending the string "(n)"
where n is the sub-index of the multiple field column,
ranging from 1 to its declared repetition factor. The indices
are NOT zero based, in order to match the nomenclature
used in the respective query syntax.
For a column declared as:
CREATE TABLE ... ( ... MULTI(3) INT )
the associative indices used for the individual "multiple field"
columns would be "multi(1)",
"multi(2)", and
"multi(3)" respectively.
Subsequent calls to sesam_fetch_array would
return the next (or prior, or n'th next/prior, depending on
the scroll attributes) row in the result set, or
FALSE if there are no
more rows.
SESAM fetch array
<?php
$result = sesam_query ("SELECT * FROM phone\n".
" WHERE LASTNAME='".strtoupper($name)."'\n".
" ORDER BY FIRSTNAME", 1);
if (! $result) {
... error ...
}
// print the table:
print "<TABLE BORDER>\n";
while (($row = sesam_fetch_array ($result)) && count($row) > 0) {
print " <TR>\n";
print " <TD>".htmlspecialchars($row["firstname"])."</TD>\n";
print " <TD>".htmlspecialchars($row["lastname"])."</TD>\n";
print " <TD>".htmlspecialchars($row["phoneno"])."</TD>\n";
print " </TR>\n";
}
print "</TABLE>\n";
sesam_free_result ($result);
?>
See also: sesam_fetch_row which returns
an indexed array.
sesam_seek_row
Set scrollable cursor mode for subsequent fetches
Descriptionboolean sesam_seek_rowstring result_idint whenceint
offsetresult_id is a valid result id (select
type queries only, and only if a "scrollable" cursor was
requested when calling sesam_query).
whence sets the global default value
for the scrolling type, it specifies the scroll type
to use in subsequent fetch operations on "scrollable"
cursors, which can be set to the following predefined
constants:
Valid values for <parameter>"whence"</parameter> parameterValueConstantMeaning0SESAM_SEEK_NEXTread sequentially
1SESAM_SEEK_PRIORread sequentially backwards
2SESAM_SEEK_FIRSTfetch first row (after fetch, the default
is set to
SESAM_SEEK_NEXT)3SESAM_SEEK_LASTfetch last row (after fetch, the default is
set to
SESAM_SEEK_PRIOR)4SESAM_SEEK_ABSOLUTEfetch absolute row number given as
offset (Zero-based. After
fetch, the default is set to
SESAM_SEEK_ABSOLUTE, and the
offset value is auto-incremented)5SESAM_SEEK_RELATIVEfetch relative to current scroll position, where
offset can be a positive or
negative offset value (this also sets the default
"offset" value for subsequent fetches).
offset is an
optional parameter which is only evaluated (and required)
if whence is
either SESAM_SEEK_RELATIVE or
SESAM_SEEK_ABSOLUTE.
sesam_free_resultReleases resources for the queryDescriptionint sesam_free_resultstring result_id
Releases resources for the query associated with
result_id. Returns
FALSE on error.
