338 lines
12 KiB
PHP
338 lines
12 KiB
PHP
|
<?php
|
||
|
|
// include library db.php
|
||
|
|
// database abstraction layer, supports multiple databases
|
||
|
|
|
||
|
|
/*
|
||
|
|
usage:
|
||
|
|
- db_setdatabase will be called automatically each time you use db_sql (it will guarantee database availability)
|
||
|
|
- you may call db_unsetdabatase at the end of your work (e.g. at end of script)
|
||
|
|
- in normal operation use control constants in your main code and just call db_sql and similar functions
|
||
|
|
|
||
|
|
control constants:
|
||
|
|
- SERVER_DB_TYPE declares database type, e.g. "POSTGRESQL", "ODBC", "MYSQL" or others
|
||
|
|
each database type needs some more special constants:
|
||
|
|
POSTGRESQL:
|
||
|
|
- SERVER_DB_HOST, e.g. "localhost"
|
||
|
|
- SERVER_DB_PORT, e.g. 5432
|
||
|
|
- SERVER_DB_NAME, e.g. "testdatabase"
|
||
|
|
- SERVER_DB_USER, e.g. "testuser"
|
||
|
|
- SERVER_DB_PASS, e.g. "testpass"
|
||
|
|
ODBC:
|
||
|
|
- SERVER_DB_NAME, e.g. "testdatabase"
|
||
|
|
- SERVER_DB_USER, e.g. "testuser"
|
||
|
|
- SERVER_DB_PASS, e.g. "testpass"
|
||
|
|
|
||
|
|
available functions:
|
||
|
|
- db_setdb = checks/initializes database, will be called automatically
|
||
|
|
- db_unsetdb = closes database manually, will be done automatically at the end of the parent script
|
||
|
|
- db_sql = executes SQL statement and returns resultset
|
||
|
|
- db_row = delivers a specific row from resultset, uses constants DB_ROW_*
|
||
|
|
- db_fields = delivers field list from resultset, uses constants DB_FIELD_*
|
||
|
|
*/
|
||
|
|
|
||
|
|
|
||
|
|
define( "DB_TYPE_POSTGRESQL", "POSTGRESQL" );
|
||
|
|
define( "DB_TYPE_ODBC", "ODBC" );
|
||
|
|
|
||
|
|
define( "DB_ROW_FIRST", 0 );
|
||
|
|
define( "DB_ROW_NEXT", -1 );
|
||
|
|
define( "DB_ROW_LAST", -2 );
|
||
|
|
|
||
|
|
define( "DB_FIELD_NAME", 0 );
|
||
|
|
define( "DB_FIELD_TYPE", 1 );
|
||
|
|
define( "DB_FIELD_LEN", 2 );
|
||
|
|
|
||
|
|
|
||
|
|
// **********************************************************************
|
||
|
|
function db_setdb ( &$intdbconnid, &$strdberror )
|
||
|
|
// checks/opens database connection
|
||
|
|
// updates parameter intdbconnid and strdberror
|
||
|
|
// returns true if database had already been opened or has been opened successfully,
|
||
|
|
// otherwise returns false and sets strdberror
|
||
|
|
{
|
||
|
|
$bolretcode = true;
|
||
|
|
$strdberror = '';
|
||
|
|
|
||
|
|
if ( $intdbconnid == 0 )
|
||
|
|
{
|
||
|
|
// database is dead or closed -> (re)open it
|
||
|
|
switch ( trim(constant("SERVER_DB_TYPE")) )
|
||
|
|
{
|
||
|
|
case constant("DB_TYPE_POSTGRESQL"):
|
||
|
|
$intdbconnid = pg_connect ("host=" . trim(constant("SERVER_DB_HOST")) . " port=" . trim(strval(constant("SERVER_DB_PORT"))) . " dbname=" . trim(constant("SERVER_DB_NAME")) . " user=" . trim(constant("SERVER_DB_USER")) . " password=" . trim(constant("SERVER_DB_PASS")));
|
||
|
|
$strdberror = pg_last_error( $intdbconnid );
|
||
|
|
break;
|
||
|
|
|
||
|
|
case constant("DB_TYPE_ODBC"):
|
||
|
|
$intdbconnid = odbc_connect ( trim(constant("SERVER_DB_NAME")), trim(constant("SERVER_DB_USER")), trim(constant("SERVER_DB_PASS")), SQL_CUR_USE_ODBC );
|
||
|
|
$strdberror = odbc_errormsg( $intdbconnid );
|
||
|
|
break;
|
||
|
|
|
||
|
|
default:
|
||
|
|
// SERVER_DB_TYPE not supported!
|
||
|
|
$intdbconnid = 0;
|
||
|
|
$strdberror = 'Sorry, database of type "' . trim(constant("SERVER_DB_TYPE")) . '" is not supported.';
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
|
||
|
|
// set return code on success
|
||
|
|
$bolretcode = ( $intdbconnid != 0 );
|
||
|
|
}
|
||
|
|
|
||
|
|
return $bolretcode;
|
||
|
|
}
|
||
|
|
|
||
|
|
// **********************************************************************
|
||
|
|
function db_unsetdb ( &$intdbconnid, &$strdberror )
|
||
|
|
// checks/closes database connection
|
||
|
|
// updates parameter intdbconnid and strdberror
|
||
|
|
// returns true if database had already been closed or has been closed successfully,
|
||
|
|
// otherwise returns false and sets strdberror
|
||
|
|
{
|
||
|
|
$bolretcode = true;
|
||
|
|
|
||
|
|
if ( $intdbconnid != 0 )
|
||
|
|
{
|
||
|
|
// database is still opened -> close it
|
||
|
|
switch ( constant("SERVER_DB_TYPE") )
|
||
|
|
{
|
||
|
|
case constant("DB_TYPE_POSTGRESQL"):
|
||
|
|
$bolretcode = pg_close ( $intdbconnid );
|
||
|
|
$strdberror = pg_last_error( $intdbconnid );
|
||
|
|
break;
|
||
|
|
|
||
|
|
case constant("DB_TYPE_ODBC"):
|
||
|
|
$bolretcode = odbc_close ( $intdbconnid );
|
||
|
|
$strdberror = odbc_errormsg( $intdbconnid );
|
||
|
|
break;
|
||
|
|
|
||
|
|
default:
|
||
|
|
// SERVER_DB_TYPE not supported!
|
||
|
|
$bolretcode = false;
|
||
|
|
$strdberror = 'Sorry, database of type "' . constant("SERVER_DB_TYPE") . '" is not supported.';
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
|
||
|
|
// clear database connection id on success
|
||
|
|
if ( $bolretcode )
|
||
|
|
{
|
||
|
|
$intdbconnid = 0;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
return $bolretcode;
|
||
|
|
}
|
||
|
|
|
||
|
|
// **********************************************************************
|
||
|
|
function db_sql ( &$intdbconnid, $strdbsql, &$intdbresult, &$lngdbrows, &$strdberror )
|
||
|
|
// executes SQL statement on given database connection
|
||
|
|
// updates parameter intdbconnid, intdbresult, lngdbrows and strdberror
|
||
|
|
// returns true and sets intdbresult and lngdbrows if SQL statement had been executed successfully,
|
||
|
|
// otherwise returns false and sets strdberror
|
||
|
|
{
|
||
|
|
$bolretcode = false;
|
||
|
|
$intdbresult = 0;
|
||
|
|
$lngdbrows = 0;
|
||
|
|
$strdberror = '';
|
||
|
|
|
||
|
|
if ( db_setdb( $intdbconnid, $strdberror ))
|
||
|
|
{
|
||
|
|
// database is ok
|
||
|
|
|
||
|
|
switch ( constant("SERVER_DB_TYPE") )
|
||
|
|
{
|
||
|
|
case constant("DB_TYPE_POSTGRESQL"):
|
||
|
|
$intdbresult = pg_query ( $intdbconnid, $strdbsql );
|
||
|
|
$strdberror = pg_last_error( $intdbconnid );
|
||
|
|
if ( $intdbresult != 0 )
|
||
|
|
{
|
||
|
|
$lngdbrows = pg_num_rows( $intdbresult ) + pg_affected_rows( $intdbresult );
|
||
|
|
$bolretcode = true;
|
||
|
|
}
|
||
|
|
break;
|
||
|
|
|
||
|
|
case constant("DB_TYPE_ODBC"):
|
||
|
|
$intdbresult = odbc_exec ( $intdbconnid, $strdbsql );
|
||
|
|
$strdberror = odbc_errormsg( $intdbconnid );
|
||
|
|
if ( $intdbresult != 0 )
|
||
|
|
{
|
||
|
|
// Attention: odbc_num_rows may return wrong record count in SELECT-statements!
|
||
|
|
// to retrieve exact number of records in SELECT statements you should walk through the recordset
|
||
|
|
$lngdbrows = odbc_num_rows( $intdbresult );
|
||
|
|
$bolretcode = true;
|
||
|
|
}
|
||
|
|
break;
|
||
|
|
|
||
|
|
default:
|
||
|
|
// SERVER_DB_TYPE not supported!
|
||
|
|
$bolretcode = false;
|
||
|
|
$strdberror = 'Sorry, database of type "' . constant("SERVER_DB_TYPE") . '" is not supported.';
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
return $bolretcode;
|
||
|
|
}
|
||
|
|
|
||
|
|
// **********************************************************************
|
||
|
|
function db_row ( $intdbresult, &$strdbrow, $intdbrow, &$strdberror )
|
||
|
|
// fetches row on position intdbrow from intdbresult as associative array into strdbrow
|
||
|
|
// position intdbrow may be DB_RESULT_NEXTROW to fetch the next row of intdbresult
|
||
|
|
// updates parameter strdbrow, strdberror
|
||
|
|
// returns true and sets strdbrow if row has been fetched successfully,
|
||
|
|
// otherwise returns false and sets strdberror
|
||
|
|
{
|
||
|
|
$bolretcode = false;
|
||
|
|
$strdbrow = '';
|
||
|
|
$strdberror = '';
|
||
|
|
|
||
|
|
if ( $intdbresult != 0 )
|
||
|
|
{
|
||
|
|
// resultset is ok
|
||
|
|
|
||
|
|
switch ( constant("SERVER_DB_TYPE") )
|
||
|
|
{
|
||
|
|
case constant("DB_TYPE_POSTGRESQL"):
|
||
|
|
switch ( $intdbrow )
|
||
|
|
{
|
||
|
|
case constant("DB_ROW_FIRST"):
|
||
|
|
$bolretcode = ( $strdbrow = pg_fetch_assoc( $intdbresult, 0 ));
|
||
|
|
break;
|
||
|
|
|
||
|
|
case constant("DB_ROW_NEXT"):
|
||
|
|
$bolretcode = ( $strdbrow = pg_fetch_assoc( $intdbresult ));
|
||
|
|
break;
|
||
|
|
|
||
|
|
case constant("DB_ROW_LAST"):
|
||
|
|
$bolretcode = ( $strdbrow = pg_fetch_assoc( $intdbresult, pg_num_rows( $intdbresult ) - 1 ));
|
||
|
|
break;
|
||
|
|
|
||
|
|
default:
|
||
|
|
// specific $intdbrow given -> use it directly, offer start at position 1
|
||
|
|
$bolretcode = ( $strdbrow = pg_fetch_assoc( $intdbresult, $intdbrow - 1 ));
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
$strdberror = pg_result_error( $intdbresult );
|
||
|
|
break;
|
||
|
|
|
||
|
|
case constant("DB_TYPE_ODBC"):
|
||
|
|
switch ( $intdbrow )
|
||
|
|
{
|
||
|
|
case constant("DB_ROW_FIRST"):
|
||
|
|
$bolretcode = ( $strdbrow = odbc_fetch_array( $intdbresult, 0 ));
|
||
|
|
break;
|
||
|
|
|
||
|
|
case constant("DB_ROW_NEXT"):
|
||
|
|
$bolretcode = ( $strdbrow = odbc_fetch_array( $intdbresult ));
|
||
|
|
break;
|
||
|
|
|
||
|
|
case constant("DB_ROW_LAST"):
|
||
|
|
// Attention: odbc_num_rows may return wrong record count in SELECT-statements!
|
||
|
|
// to retrieve exact number of records in SELECT statements you should walk through the recordset
|
||
|
|
$bolretcode = ( $strdbrow = odbc_fetch_array( $intdbresult, odbc_num_rows( $intdbresult ) - 1 ));
|
||
|
|
break;
|
||
|
|
|
||
|
|
default:
|
||
|
|
// specific $intdbrow given -> use it directly, offer start at position 1
|
||
|
|
$bolretcode = ( $strdbrow = odbc_fetch_array( $intdbresult, $intdbrow - 1));
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
// ODBC does not support error messages on resultset level
|
||
|
|
if ( !$bolretcode )
|
||
|
|
{
|
||
|
|
$strdberror = 'Sorry, could not read result row on position ' . $intdbrow ;
|
||
|
|
}
|
||
|
|
break;
|
||
|
|
|
||
|
|
default:
|
||
|
|
// SERVER_DB_TYPE not supported!
|
||
|
|
$bolretcode = false;
|
||
|
|
$strdberror = 'Sorry, database of type "' . constant("SERVER_DB_TYPE") . '" is not supported.';
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
else
|
||
|
|
{
|
||
|
|
$bolretcode = false;
|
||
|
|
$strdberror = 'Sorry, no data available to read.';
|
||
|
|
}
|
||
|
|
|
||
|
|
return $bolretcode;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
// **********************************************************************
|
||
|
|
function db_fields ( $intdbresult, &$strdbfields, &$strdberror )
|
||
|
|
// returns list of field names, field type and field length from intdbresult as associative arrays into strdbfields
|
||
|
|
// strdbfields will contain following partial arrays: DB_FIELD_NAME, DB_FIELD_TYPE, DB_FIELD_LEN
|
||
|
|
// use as following: $strdbfields[constant("DB_FIELD_*")][$intdbfield or $strdbfieldname]
|
||
|
|
// example: $intdbfieldtype[$strdbfieldname] = $strdbfields[constant("DB_FIELD_TYPE")][$strdbfieldname]
|
||
|
|
// updates parameter strdbfields, strdberror
|
||
|
|
// returns true and sets strdbfields if field names have been retrieved successfully,
|
||
|
|
// otherwise returns false and sets strdberror
|
||
|
|
{
|
||
|
|
$bolretcode = false;
|
||
|
|
$strdbfields = array (constant("DB_FIELD_NAME") => array(),
|
||
|
|
constant("DB_FIELD_TYPE") => array(),
|
||
|
|
constant("DB_FIELD_LEN") => array());
|
||
|
|
$strdberror = '';
|
||
|
|
$intindex = 0;
|
||
|
|
|
||
|
|
if ( $intdbresult != 0 )
|
||
|
|
{
|
||
|
|
// resultset is ok
|
||
|
|
|
||
|
|
//no errors expected at all
|
||
|
|
$bolretcode = true;
|
||
|
|
|
||
|
|
switch ( constant("SERVER_DB_TYPE") )
|
||
|
|
{
|
||
|
|
case constant("DB_TYPE_POSTGRESQL"):
|
||
|
|
// field numeration in PostgreSQL starts with 0
|
||
|
|
$intindex = 0;
|
||
|
|
while ( $intindex < pg_num_fields( $intdbresult ))
|
||
|
|
{
|
||
|
|
array_push ( $strdbfields[constant("DB_FIELD_NAME")], pg_field_name( $intdbresult, $intindex ));
|
||
|
|
array_push ( $strdbfields[constant("DB_FIELD_TYPE")], pg_field_type( $intdbresult, $intindex ));
|
||
|
|
array_push ( $strdbfields[constant("DB_FIELD_LEN")], pg_field_prtlen( $intdbresult, $intindex ));
|
||
|
|
$intindex++;
|
||
|
|
}
|
||
|
|
$strdberror = pg_result_error( $intdbresult );
|
||
|
|
break;
|
||
|
|
|
||
|
|
case constant("DB_TYPE_ODBC"):
|
||
|
|
// field numeration in ODBC starts with 1
|
||
|
|
$intindex = 1;
|
||
|
|
while ( $intindex <= odbc_num_fields( $intdbresult ))
|
||
|
|
{
|
||
|
|
$strfieldname = odbc_field_name( $intdbresult, $intindex );
|
||
|
|
array_push ( $strdbfields[constant("DB_FIELD_NAME")], $strfieldname );
|
||
|
|
array_push ( $strdbfields[constant("DB_FIELD_TYPE")], odbc_field_type( $intdbresult, $intindex ));
|
||
|
|
array_push ( $strdbfields[constant("DB_FIELD_LEN")], odbc_field_len( $intdbresult, $intindex ));
|
||
|
|
$intindex++;
|
||
|
|
}
|
||
|
|
// ODBC does not support error messages on resultset level
|
||
|
|
if ( !$bolretcode )
|
||
|
|
{
|
||
|
|
$strdberror = 'Sorry, could not read field names.';
|
||
|
|
}
|
||
|
|
break;
|
||
|
|
|
||
|
|
default:
|
||
|
|
// SERVER_DB_TYPE not supported!
|
||
|
|
$bolretcode = false;
|
||
|
|
$strdberror = 'Sorry, database of type "' . constant("SERVER_DB_TYPE") . '" is not supported.';
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
else
|
||
|
|
{
|
||
|
|
$bolretcode = false;
|
||
|
|
$strdberror = 'Sorry, no data available to read.';
|
||
|
|
}
|
||
|
|
|
||
|
|
return $bolretcode;
|
||
|
|
}
|
||
|
|
|
||
|
|
?>
|