3 * Generic database access.
5 * The functions defined in this file are meant to provide a single API to the
6 * different PHP database APIs. Unfortunately, this is necessary since PHP
7 * does not yet have a common db API. The value of
8 * <var>$GLOBALS["db_type"]</var> should be defined somewhere to one of the
12 * - oracle (This uses the Oracle8 OCI API, so Oracle 8 libs are required)
19 * - This assumes a single connection to a single database for the sake of
20 * simplicity. Do not make a new connection until you are completely
21 * finished with the previous one. However, you can execute more than query
23 * - Rather than use the associative arrays returned with xxx_fetch_array(),
24 * normal arrays are used with xxx_fetch_row(). (Some db APIs don't support
27 * @author Craig Knudsen <cknudsen@cknudsen.com>
28 * @copyright Craig Knudsen, <cknudsen@cknudsen.com>, http://www.k5n.us/cknudsen
29 * @license http://www.gnu.org/licenses/gpl.html GNU GPL
30 * @package WebCalendar
33 * 09-Dec-2005 Craig Knudsen
34 * Added DB2 support (patch from Helmut Tessarek)
35 * 17-Mar-2005 Ray Jones
36 * Changed mssql_error to mssql_get_last_message
37 * 23-Jan-2005 Craig Knudsen <cknudsen@cknudsen.com>
38 * Added documentation to be used with php2html.pl
39 * 19-Jan-2005 Craig Knudsen <cknudsen@cknudsen.com>
40 * Add option for verbose error messages.
41 * 19-Jan-2004 Craig Knudsen <cknudsen@cknudsen.com>
43 * Code from raspail@users.sourceforge.net
44 * 02-Jul-2004 Craig Knudsen <cknudsen@cknudsen.com>
45 * Added mysqli support
46 * Code from Francesco Riosa
47 * 31-May-2002 Craig Knudsen <cknudsen@radix.net>
48 * Added support for Interbase contributed by
50 * 11-Jul-2001 Craig Knudsen <cknudsen@radix.net>
51 * Removed pass by reference for odbc_fetch_into()
52 * Removed ++ in call to pg_fetch_array()
53 * 22-Apr-2000 Ken Harris <kharris@lhinfo.com>
55 * 23-Feb-2000 Craig Knudsen <cknudsen@radix.net>
59 if ( empty ( $PHP_SELF ) && ! empty ( $_SERVER ) &&
60 ! empty ( $_SERVER['PHP_SELF'] ) ) {
61 $PHP_SELF = $_SERVER['PHP_SELF'];
63 if ( ! empty ( $PHP_SELF ) && preg_match ( "/\/includes\//", $PHP_SELF ) ) {
64 die ( "You can't access this file directly!" );
68 // Enable the following to show the actual database error in the browser.
69 // It is more secure to not show this info, so this should only be turned
70 // on for debugging purposes.
71 $phpdbiVerbose = false;
74 * Opens up a database connection.
76 * Use a pooled connection if the db supports it and
77 * the <var>db_persistent</var> setting is enabled.
80 * - The database type is determined by the global variable
82 * - For ODBC, <var>$host</var> is ignored, <var>$database</var> = DSN
83 * - For Oracle, <var>$database</var> = tnsnames name
84 * - Use the {@link dbi_error()} function to get error information if the connection
87 * @param string $host Hostname of database server
88 * @param string $login Database login
89 * @param string $password Database login password
90 * @param string $database Name of database
92 * @return resource The connection
94 function dbi_connect ( $host, $login, $password, $database ) {
95 if ( strcmp ( $GLOBALS["db_type"], "mysql" ) == 0 ) {
96 if ($GLOBALS["db_persistent"]) {
97 $c = mysql_pconnect ( $host, $login, $password );
99 $c = mysql_connect ( $host, $login, $password );
102 if ( ! mysql_select_db ( $database ) )
108 } else if ( strcmp ( $GLOBALS["db_type"], "mysqli" ) == 0 ) {
109 if ($GLOBALS["db_persistent"]) {
110 $c = @mysqli_connect ( $host, $login, $password, $database);
112 $c = @mysqli_connect ( $host, $login, $password, $database);
116 if ( ! mysqli_select_db ( $c, $database ) )
119 $GLOBALS["db_connection"] = $c;
124 } else if ( strcmp ( $GLOBALS["db_type"], "mssql" ) == 0 ) {
125 if ($GLOBALS["db_persistent"]) {
126 $c = mssql_pconnect ( $host, $login, $password );
128 $c = mssql_connect ( $host, $login, $password );
131 if ( ! mssql_select_db ( $database ) )
137 } else if ( strcmp ( $GLOBALS["db_type"], "oracle" ) == 0 ) {
138 if ( strlen ( $host ) && strcmp ( $host, "localhost" ) )
139 $c = OCIPLogon ( "$login@$host", $password, $database );
141 $c = OCIPLogon ( $login, $password, $database );
142 $GLOBALS["oracle_connection"] = $c;
144 } else if ( strcmp ( $GLOBALS["db_type"], "postgresql" ) == 0 ) {
145 if ( strlen ( $password ) ) {
146 if ( strlen ( $host ) ) {
147 $dbargs = "host=$host dbname=$database user=$login password=$password";
149 $dbargs = "dbname=$database user=$login password=$password";
152 if ( strlen ( $host ) ) {
153 $dbargs = "host=$host dbname=$database user=$login";
155 $dbargs = "dbname=$database user=$login";
158 if ($GLOBALS["db_persistent"]) {
159 $c = pg_pconnect ( $dbargs );
161 $c = pg_connect ( $dbargs );
163 $GLOBALS["postgresql_connection"] = $c;
165 echo "Error connecting to database\n";
169 } else if ( strcmp ( $GLOBALS["db_type"], "odbc" ) == 0 ) {
170 if ($GLOBALS["db_persistent"]) {
171 $c = odbc_pconnect ( $database, $login, $password );
173 $c = odbc_connect ( $database, $login, $password );
175 $GLOBALS["odbc_connection"] = $c;
177 } else if ( strcmp ( $GLOBALS["db_type"], "ibm_db2" ) == 0 ) {
178 if ($GLOBALS["db_persistent"]) {
179 $c = db2_pconnect ( $database, $login, $password );
181 $c = db2_connect ( $database, $login, $password );
183 $GLOBALS["ibm_db2_connection"] = $c;
185 } else if ( strcmp ( $GLOBALS["db_type"], "ibase" ) == 0 ) {
186 $host = $host . ":" . $database;
187 if ($GLOBALS["db_persistent"]) {
188 $c = ibase_pconnect ( $host, $login, $password );
190 $c = ibase_connect ( $host, $login, $password );
194 if ( empty ( $GLOBALS["db_type"] ) )
195 dbi_fatal_error ( "dbi_connect(): db_type not defined." );
197 dbi_fatal_error ( "dbi_connect(): invalid db_type '" .
198 $GLOBALS["db_type"] . "'" );
203 * Closes a database connection.
205 * This is not necessary for any database that uses pooled connections such as
206 * MySQL, but a good programming practice.
208 * @param resource $conn The database connection
210 * @return bool True on success, false on error
212 function dbi_close ( $conn ) {
213 if ( strcmp ( $GLOBALS["db_type"], "mysql" ) == 0 ) {
214 return mysql_close ( $conn );
215 } else if ( strcmp ( $GLOBALS["db_type"], "mysqli" ) == 0 ) {
216 return mysqli_close ( $conn );
217 } else if ( strcmp ( $GLOBALS["db_type"], "mssql" ) == 0 ) {
218 return mssql_close ( $conn );
219 } else if ( strcmp ( $GLOBALS["db_type"], "oracle" ) == 0 ) {
220 return OCILogOff ( $conn );
221 } else if ( strcmp ( $GLOBALS["db_type"], "postgresql" ) == 0 ) {
222 return pg_close ( $GLOBALS["postgresql_connection"] );
223 } else if ( strcmp ( $GLOBALS["db_type"], "odbc" ) == 0 ) {
224 return odbc_close ( $GLOBALS["odbc_connection"] );
225 } else if ( strcmp ( $GLOBALS["db_type"], "ibm_db2" ) == 0 ) {
226 return db2_close ( $GLOBALS["ibm_db2_connection"] );
227 } else if ( strcmp ( $GLOBALS["db_type"], "ibase" ) == 0 ) {
228 return ibase_close ( $conn );
230 dbi_fatal_error ( "dbi_close(): db_type not defined." );
234 // Select the database that all queries should use
235 //function dbi_select_db ( $database ) {
236 // if ( strcmp ( $GLOBALS["db_type"], "mysql" ) == 0 ) {
237 // return mysql_select_db ( $database );
238 // } else if ( strcmp ( $GLOBALS["db_type"], "oracle" ) == 0 ) {
239 // // Not supported. Must sent up a tnsname and user that uses
240 // // the correct tablesapce.
243 // dbi_fatal_error ( "dbi_select_db(): db_type not defined." );
248 * Executes a SQL query.
250 * <b>Note:</b> Use the {@link dbi_error()} function to get error information
251 * if the connection fails.
253 * @param string $sql SQL of query to execute
254 * @param bool $fatalOnError Abort execution if there is a database error?
255 * @param bool $showError Display error to user (including possibly the
256 * SQL) if there is a database error?
258 * @return mixed The query result resource on queries (which can then be
259 * passed to the {@link dbi_fetch_row()} function to obtain the
260 * results), or true/false on insert or delete queries.
262 function dbi_query ( $sql, $fatalOnError=true, $showError=true ) {
263 global $phpdbiVerbose;
264 if ( strcmp ( $GLOBALS["db_type"], "mysql" ) == 0 ) {
265 $res = mysql_query ( $sql );
267 dbi_fatal_error ( "Error executing query." .
268 $phpdbiVerbose ? ( dbi_error() . "\n\n<br />\n" . $sql ) : "" .
269 "", $fatalOnError, $showError );
271 } else if ( strcmp ( $GLOBALS["db_type"], "mysqli" ) == 0 ) {
272 $res = mysqli_query ( $GLOBALS["db_connection"], $sql );
274 dbi_fatal_error ( "Error executing query." .
275 $phpdbiVerbose ? ( dbi_error() . "\n\n<br />\n" . $sql ) : "" .
276 "", $fatalOnError, $showError );
278 } else if ( strcmp ( $GLOBALS["db_type"], "mssql" ) == 0 ) {
279 $res = mssql_query ( $sql );
281 dbi_fatal_error ( "Error executing query." .
282 $phpdbiVerbose ? ( dbi_error() . "\n\n<br />\n" . $sql ) : "" .
283 "", $fatalOnError, $showError );
285 } else if ( strcmp ( $GLOBALS["db_type"], "oracle" ) == 0 ) {
286 $GLOBALS["oracle_statement"] =
287 OCIParse ( $GLOBALS["oracle_connection"], $sql );
288 return OCIExecute ( $GLOBALS["oracle_statement"],
289 OCI_COMMIT_ON_SUCCESS );
290 } else if ( strcmp ( $GLOBALS["db_type"], "postgresql" ) == 0 ) {
291 @$GLOBALS["postgresql_row[\"$res\"]"] = 0;
292 $res = pg_exec ( $GLOBALS["postgresql_connection"], $sql );
294 dbi_fatal_error ( "Error executing query." .
295 $phpdbiVerbose ? ( dbi_error() . "\n\n<br />\n" . $sql ) : "" .
296 "", $fatalOnError, $showError );
297 $GLOBALS["postgresql_numrows[\"$res\"]"] = pg_numrows ( $res );
299 } else if ( strcmp ( $GLOBALS["db_type"], "odbc" ) == 0 ) {
300 return odbc_exec ( $GLOBALS["odbc_connection"], $sql );
301 } else if ( strcmp ( $GLOBALS["db_type"], "ibm_db2" ) == 0 ) {
302 $res = db2_exec ( $GLOBALS["ibm_db2_connection"], $sql );
304 dbi_fatal_error ( "Error executing query." .
305 $phpdbiVerbose ? ( dbi_error() . "\n\n<br />\n" . $sql ) : "" .
306 "", $fatalOnError, $showError );
308 } else if ( strcmp ( $GLOBALS["db_type"], "ibase" ) == 0 ) {
309 $res = ibase_query ( $sql );
311 dbi_fatal_error ( "Error executing query." .
312 $phpdbiVerbose ? ( dbi_error() . "\n\n<br />\n" . $sql ) : "" .
313 "", $fatalOnError, $showError );
316 dbi_fatal_error ( "dbi_query(): db_type not defined." );
320 // Determine the number of rows from a result
321 //function dbi_num_rows ( $res ) {
322 // if ( strcmp ( $GLOBALS["db_type"], "mysql" ) == 0 ) {
323 // return mysql_num_rows ( $res );
325 // dbi_fatal_error ( "dbi_num_rows(): db_type not defined." );
330 * Retrieves a single row from the database and returns it as an array.
332 * <b>Note:</b> We don't use the more useful xxx_fetch_array because not all
333 * databases support this function.
335 * <b>Note:</b> Use the {@link dbi_error()} function to get error information
336 * if the connection fails.
338 * @param resource $res The database query resource returned from
339 * the {@link dbi_query()} function.
341 * @return mixed An array of database columns representing a single row in
342 * the query result or false on an error.
344 function dbi_fetch_row ( $res ) {
345 if ( strcmp ( $GLOBALS["db_type"], "mysql" ) == 0 ) {
346 return mysql_fetch_array ( $res );
347 } else if ( strcmp ( $GLOBALS["db_type"], "mysqli" ) == 0 ) {
348 return mysqli_fetch_array ( $res );
349 } else if ( strcmp ( $GLOBALS["db_type"], "mssql" ) == 0 ) {
350 return mssql_fetch_array ( $res );
351 } else if ( strcmp ( $GLOBALS["db_type"], "oracle" ) == 0 ) {
352 if ( OCIFetchInto ( $GLOBALS["oracle_statement"], $row,
353 OCI_NUM + OCI_RETURN_NULLS ) )
356 } else if ( strcmp ( $GLOBALS["db_type"], "postgresql" ) == 0 ) {
357 if ( @$GLOBALS["postgresql_numrows[\"$res\"]"] > @$GLOBALS["postgresql_row[\"$res\"]"] ) {
358 $r = pg_fetch_array ( $res, @$GLOBALS["postgresql_row[\"$res\"]"] );
359 @$GLOBALS["postgresql_row[\"$res\"]"]++;
361 echo "Unable to fetch row\n";
369 } else if ( strcmp ( $GLOBALS["db_type"], "odbc" ) == 0 ) {
370 if ( ! odbc_fetch_into ( $res, $ret ) )
373 } else if ( strcmp ( $GLOBALS["db_type"], "ibm_db2" ) == 0 ) {
374 return db2_fetch_array ( $res );
375 } else if ( strcmp ( $GLOBALS["db_type"], "ibase" ) == 0 ) {
376 return ibase_fetch_row ( $res );
378 dbi_fatal_error ( "dbi_fetch_row(): db_type not defined." );
383 * Returns the number of rows affected by the last INSERT, UPDATE or DELETE.
385 * <b>Note:</b> Use the {@link dbi_error()} function to get error information
386 * if the connection fails.
388 * @param resource $conn The database connection
389 * @param resource $res The database query resource returned from
390 * the {@link dbi_query()} function.
392 * @return int The number or database rows affected.
394 function dbi_affected_rows ( $conn, $res ) {
395 if ( strcmp ( $GLOBALS["db_type"], "mysql" ) == 0 ) {
396 return mysql_affected_rows ( $conn );
397 } else if ( strcmp ( $GLOBALS["db_type"], "mysqli" ) == 0 ) {
398 return mysqli_affected_rows ( $conn );
399 } else if ( strcmp ( $GLOBALS["db_type"], "mssql" ) == 0 ) {
400 return mssql_affected_rows ( $conn );
401 } else if ( strcmp ( $GLOBALS["db_type"], "oracle" ) == 0 ) {
402 if ( $GLOBALS["oracle_statement"] >= 0 ) {
403 return OCIRowCount ( $GLOBALS["oracle_statement"] );
407 } else if ( strcmp ( $GLOBALS["db_type"], "postgresql" ) == 0 ) {
408 return pg_affected_rows ( $res );
409 } else if ( strcmp ( $GLOBALS["db_type"], "odbc" ) == 0 ) {
410 return odbc_num_rows ( $res );
411 } else if ( strcmp ( $GLOBALS["db_type"], "ibm_db2" ) == 0 ) {
412 return db2_num_rows ( $res );
413 } else if ( strcmp ( $GLOBALS["db_type"], "ibase" ) == 0 ) {
414 return ibase_affected_rows ( $conn );
416 dbi_fatal_error ( "dbi_free_result(): db_type not defined." );
421 * Frees a result set.
423 * @param resource $res The database query resource returned from
424 * the {@link dbi_query()} function.
426 * @return bool True on success
428 function dbi_free_result ( $res ) {
429 if ( strcmp ( $GLOBALS["db_type"], "mysql" ) == 0 ) {
430 return mysql_free_result ( $res );
431 } else if ( strcmp ( $GLOBALS["db_type"], "mysqli" ) == 0 ) {
432 return mysqli_free_result ( $res );
433 } else if ( strcmp ( $GLOBALS["db_type"], "mssql" ) == 0 ) {
434 return mssql_free_result ( $res );
435 } else if ( strcmp ( $GLOBALS["db_type"], "oracle" ) == 0 ) {
436 // Not supported. Ingore.
437 if ( $GLOBALS["oracle_statement"] >= 0 ) {
438 OCIFreeStatement ( $GLOBALS["oracle_statement"] );
439 $GLOBALS["oracle_statement"] = -1;
441 } else if ( strcmp ( $GLOBALS["db_type"], "postgresql" ) == 0 ) {
442 return pg_freeresult ( $res );
443 } else if ( strcmp ( $GLOBALS["db_type"], "odbc" ) == 0 ) {
444 return odbc_free_result ( $res );
445 } else if ( strcmp ( $GLOBALS["db_type"], "ibm_db2" ) == 0 ) {
446 return db2_free_result ( $res );
447 } else if ( strcmp ( $GLOBALS["db_type"], "ibase" ) == 0 ) {
448 return ibase_free_result ( $res );
450 dbi_fatal_error ( "dbi_free_result(): db_type not defined." );
455 * Gets the latest database error message.
457 * @return string The text of the last database error. (The type of
458 * information varies depending on the which type of database
461 function dbi_error () {
462 if ( strcmp ( $GLOBALS["db_type"], "mysql" ) == 0 ) {
463 $ret = mysql_error ();
464 } else if ( strcmp ( $GLOBALS["db_type"], "mysqli" ) == 0 ) {
465 $ret = mysqli_error ($GLOBALS["db_connection"]);
466 } else if ( strcmp ( $GLOBALS["db_type"], "mssql" ) == 0 ) {
467 // no real mssql_error function. this is as good as it gets
468 $ret = mssql_get_last_message ();
469 } else if ( strcmp ( $GLOBALS["db_type"], "oracle" ) == 0 ) {
470 $ret = OCIError ( $GLOBALS["oracle_connection"] );
471 } else if ( strcmp ( $GLOBALS["db_type"], "postgresql" ) == 0 ) {
472 $ret = pg_errormessage ( $GLOBALS["postgresql_connection"] );
473 } else if ( strcmp ( $GLOBALS["db_type"], "odbc" ) == 0 ) {
474 // no way to get error from ODBC API
475 $ret = "Unknown ODBC error";
476 } else if ( strcmp ( $GLOBALS["db_type"], "ibm_db2" ) == 0 ) {
477 $ret = db2_conn_errormsg ();
479 $ret = db2_stmt_errormsg ();
480 } else if ( strcmp ( $GLOBALS["db_type"], "ibase" ) == 0 ) {
481 $ret = ibase_errmsg ();
483 $ret = "dbi_error(): db_type not defined.";
485 if ( strlen ( $ret ) )
488 return "Unknown error";
492 * Displays a fatal database error and aborts execution.
494 * @param string $msg The database error message
495 * @param bool $doExit Abort execution?
496 * @param bool $showError Show the details of the error (possibly including
497 * the SQL that caused the error)?
499 function dbi_fatal_error ( $msg, $doExit=true, $showError=true ) {
501 echo "<h2>Error</h2>\n";
502 echo "<!--begin_error(dbierror)-->\n";
504 echo "<!--end_error-->\n";