Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : SESAM Database Functions

SESAM Database Functions

Introduction

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.

There is a PHP 3 SESAM interface available which allows database operations via PHP-scripts.

Note:

Access to SESAM is only available with the latest CVS-Version of PHP 3. PHP 4 does not support the SESAM database.

Runtime Configuration

The behaviour of these functions is affected by settings in php.ini.

sesam_oml string

Name of BS2000 PLAM library containing the loadable SESAM driver modules. Required for using SESAM functions. The BS2000 PLAM library must be set ACCESS=READ,SHARE=YES because it must be readable by the apache server's user id.

sesam_configfile string

Name of SESAM application configuration file. Required for using SESAM functions. The BS2000 file must be readable by the apache server's user id.

The application configuration file will usually contain a configuration like (see SESAM reference manual):

CNF=B
NAM=K
NOTYPE
sesam_messagecatalog string

Name of SESAM message catalog file. In most cases, this directive is not necessary. Only if the SESAM message file is not installed in the system's BS2000 message file table, it can be set with this directive.

The message catalog must be set ACCESS=READ,SHARE=YES because it must be readable by the apache server's user id.

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.

Table280.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):

CNF=B
NAM=K
NOTYPE
php3_sesam_messagecatalog

Name of SESAM message catalog file. In most cases, this directive is not necessary. 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:

Table281.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:

Table282.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:

Example2205.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:

Example2206.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.

See Also

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_affected_rows — Get number of rows affected by an immediate query
sesam_commit — Commit pending updates to the SESAM database
sesam_connect — Open SESAM database connection
sesam_diagnostic — Return status information for last SESAM call
sesam_disconnect — Detach from SESAM connection
sesam_errormsg — Returns error message of last SESAM call
sesam_execimm — Execute an "immediate" SQL-statement
sesam_fetch_array — Fetch one row as an associative array
sesam_fetch_result — Return all or part of a query result
sesam_fetch_row — Fetch one row as an array
sesam_field_array — Return meta information about individual columns in a result
sesam_field_name — Return one column name of the result set
sesam_free_result — Releases resources for the query
sesam_num_fields — Return the number of fields/columns in a result set
sesam_query — Perform a SESAM SQL query and prepare the result
sesam_rollback — Discard any pending updates to the SESAM database
sesam_seek_row — Set scrollable cursor mode for subsequent fetches
sesam_settransaction — Set SESAM transaction parameters

Change Language


Follow Navioo On Twitter
.NET Functions
Apache-specific Functions
Alternative PHP Cache
Advanced PHP debugger
Array Functions
Aspell functions [deprecated]
BBCode Functions
BCMath Arbitrary Precision Mathematics Functions
PHP bytecode Compiler
Bzip2 Compression Functions
Calendar Functions
CCVS API Functions [deprecated]
Class/Object Functions
Classkit Functions
ClibPDF Functions [deprecated]
COM and .Net (Windows)
Crack Functions
Character Type Functions
CURL
Cybercash Payment Functions
Credit Mutuel CyberMUT functions
Cyrus IMAP administration Functions
Date and Time Functions
DB++ Functions
Database (dbm-style) Abstraction Layer Functions
dBase Functions
DBM Functions [deprecated]
dbx Functions
Direct IO Functions
Directory Functions
DOM Functions
DOM XML Functions
enchant Functions
Error Handling and Logging Functions
Exif Functions
Expect Functions
File Alteration Monitor Functions
Forms Data Format Functions
Fileinfo Functions
filePro Functions
Filesystem Functions
Filter Functions
Firebird/InterBase Functions
Firebird/Interbase Functions (PDO_FIREBIRD)
FriBiDi Functions
FrontBase Functions
FTP Functions
Function Handling Functions
GeoIP Functions
Gettext Functions
GMP Functions
gnupg Functions
Net_Gopher
Haru PDF Functions
hash Functions
HTTP
Hyperwave Functions
Hyperwave API Functions
i18n Functions
IBM Functions (PDO_IBM)
IBM DB2
iconv Functions
ID3 Functions
IIS Administration Functions
Image Functions
Imagick Image Library
IMAP
Informix Functions
Informix Functions (PDO_INFORMIX)
Ingres II Functions
IRC Gateway Functions
PHP / Java Integration
JSON Functions
KADM5
LDAP Functions
libxml Functions
Lotus Notes Functions
LZF Functions
Mail Functions
Mailparse Functions
Mathematical Functions
MaxDB PHP Extension
MCAL Functions
Mcrypt Encryption Functions
MCVE (Monetra) Payment Functions
Memcache Functions
Mhash Functions
Mimetype Functions
Ming functions for Flash
Miscellaneous Functions
mnoGoSearch Functions
Microsoft SQL Server Functions
Microsoft SQL Server and Sybase Functions (PDO_DBLIB)
Mohawk Software Session Handler Functions
mSQL Functions
Multibyte String Functions
muscat Functions
MySQL Functions
MySQL Functions (PDO_MYSQL)
MySQL Improved Extension
Ncurses Terminal Screen Control Functions
Network Functions
Newt Functions
NSAPI-specific Functions
Object Aggregation/Composition Functions
Object property and method call overloading
Oracle Functions
ODBC Functions (Unified)
ODBC and DB2 Functions (PDO_ODBC)
oggvorbis
OpenAL Audio Bindings
OpenSSL Functions
Oracle Functions [deprecated]
Oracle Functions (PDO_OCI)
Output Control Functions
Ovrimos SQL Functions
Paradox File Access
Parsekit Functions
Process Control Functions
Regular Expression Functions (Perl-Compatible)
PDF Functions
PDO Functions
Phar archive stream and classes
PHP Options&Information
POSIX Functions
Regular Expression Functions (POSIX Extended)
PostgreSQL Functions
PostgreSQL Functions (PDO_PGSQL)
Printer Functions
Program Execution Functions
PostScript document creation
Pspell Functions
qtdom Functions
Radius
Rar Functions
GNU Readline
GNU Recode Functions
RPM Header Reading Functions
runkit Functions
SAM - Simple Asynchronous Messaging
Satellite CORBA client extension [deprecated]
SCA Functions
SDO Functions
SDO XML Data Access Service Functions
SDO Relational Data Access Service Functions
Semaphore
SESAM Database Functions
PostgreSQL Session Save Handler
Session Handling Functions
Shared Memory Functions
SimpleXML functions
SNMP Functions
SOAP Functions
Socket Functions
Standard PHP Library (SPL) Functions
SQLite Functions
SQLite Functions (PDO_SQLITE)
Secure Shell2 Functions
Statistics Functions
Stream Functions
String Functions
Subversion Functions
Shockwave Flash Functions
Swish Functions
Sybase Functions
TCP Wrappers Functions
Tidy Functions
Tokenizer Functions
Unicode Functions
URL Functions
Variable Handling Functions
Verisign Payflow Pro Functions
vpopmail Functions
W32api Functions
WDDX Functions
win32ps Functions
win32service Functions
xattr Functions
xdiff Functions
XML Parser Functions
XML-RPC Functions
XMLReader functions
XMLWriter Functions
XSL functions
XSLT Functions
YAZ Functions
YP/NIS Functions
Zip File Functions
Zlib Compression Functions
eXTReMe Tracker