   #PHP Manual Function Reference shm_remove sesam_affected_rows

   PHP Manual
   Prev  Next
   ______________________________________________________________________

XCIV. 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 PHP3 SESAM interface available which allows database
   operations via PHP-scripts.

     Note: Access to SESAM is only available with the latest CVS-Version
     of PHP3. 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.

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

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:

   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.

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
   ______________________________________________________________________

   Prev       Home                Next
   shm_remove  Up  sesam_affected_rows
