LXXXIX. SESAM database functions
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.
Table 1. SESAM Configuration directives
| Directive |
Meaning |
| php3_sesam_oml |
Name of BS2000 PLAM library containing the loadable SESAM
driver modules. Required for using SESAM functions.
Example:
php3_sesam_oml $.SYSLNK.SESAM-SQL.030
|
|
| php3_sesam_configfile |
Name 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):
|
| php3_sesam_messagecatalog |
Name 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), and
-
connecting 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:
Table 2. Scrolled Cursor Post-Processing
| Scroll Type |
Action |
| SESAM_SEEK_NEXT |
none |
| SESAM_SEEK_PRIOR |
none |
| SESAM_SEEK_FIRST |
set scroll type to SESAM_SEEK_NEXT |
| SESAM_SEEK_LAST |
set scroll type to SESAM_SEEK_PRIOR |
| SESAM_SEEK_ABSOLUTE |
Auto-Increment internal offset value |
| SESAM_SEEK_RELATIVE |
none. (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 types
Some 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 identifiers
In SESAM (as in standard SQL), such identifiers must be enclosed in double quotes (or
renamed).
-
Display length in data types
SESAM 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 attributes
Unsigned 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:
Table 3. SQL to PHP Type Conversions
| SQL Type |
PHP Type |
| SMALLINT, INTEGER |
integer |
| NUMERIC, DECIMAL, FLOAT, REAL, DOUBLE |
float |
| 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:
|
Example 1. 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:
|
Example 2. 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.
- Table of Contents
- sesam_connect -- Open SESAM database
connection
- sesam_disconnect -- Detach from SESAM
connection
- sesam_settransaction -- Set SESAM
transaction parameters
- sesam_commit -- Commit pending updates to
the SESAM database
- sesam_rollback -- Discard any pending
updates to the SESAM database
- sesam_execimm -- Execute an "immediate"
SQL-statement
- sesam_query -- Perform a SESAM SQL query and
prepare the result
- sesam_num_fields -- Return the number
of fields/columns in a result set
- sesam_field_name -- Return one column
name of the result set
- sesam_diagnostic -- Return status
information for last SESAM call
- sesam_fetch_result -- Return all or
part of a query result
- sesam_affected_rows -- Get number of
rows affected by an immediate query
- sesam_errormsg -- Returns error message of
last SESAM call
- sesam_field_array -- Return meta
information about individual columns in a result
- sesam_fetch_row -- Fetch one row as an
array
- sesam_fetch_array -- Fetch one row as
an associative array
- sesam_seek_row -- Set scrollable cursor
mode for subsequent fetches
- sesam_free_result -- Releases resources
for the query
|
