utf8/nucleus/libs/sql/pdo.php

   1 <?php
   2
   3 /*
   4  * Nucleus: PHP/MySQL Weblog CMS (http://nucleuscms.org/)
   5  * Copyright (C) 2002-2009 The Nucleus Group
   6  *
   7  * This program is free software; you can redistribute it and/or
   8  * modify it under the terms of the GNU General Public License
   9  * as published by the Free Software Foundation; either version 2
  10  * of the License, or (at your option) any later version.
  11  * (see nucleus/documentation/index.html#license for more info)
  12  */
  13 /**
  14  * @license http://nucleuscms.org/license.txt GNU General Public License
  15  * @copyright Copyright (C) 2002-2009 The Nucleus Group
  16  * @version $Id$
  17  */
  18
  19 /*
  20  * complete sql_* wrappers for mysql functions
  21  *
  22  * functions moved from globalfunctions.php: sql_connect, sql_disconnect, sql_query
  23  */
  24
  25
  26 $MYSQL_CONN = 0;
  27 global $SQL_DBH;
  28 $SQL_DBH = NULL;
  29
  30 if (!function_exists('sql_fetch_assoc'))
  31 {
  32 /**
  33  * Errors before the database connection has been made
  34  */
  35         function startUpError($msg, $title) {
  36                 ?>
  37                 <html xmlns="http://www.w3.org/1999/xhtml">
  38                         <head><title><?php echo htmlspecialchars($title)?></title></head>
  39                         <body>
  40                                 <h1><?php echo htmlspecialchars($title)?></h1>
  41                                 <?php echo $msg?>
  42                         </body>
  43                 </html>
  44                 <?php   exit;
  45         }
  46
  47 /**
  48  * Connects to mysql server
  49  */
  50         function sql_connect_args($mysql_host = 'localhost', $mysql_user = '', $mysql_password = '', $mysql_database = '') {
  51                 global $MYSQL_HANDLER;
  52
  53                 try {
  54                         if (strpos($mysql_host,':') === false) {
  55                                 $host = $mysql_host;
  56                                 $port = '';
  57                                 $portnum = '';
  58                         }
  59                         else {
  60                                 list($host,$port) = explode(":",$mysql_host);
  61                                 if (isset($port)) {
  62                                         $portnum = $port;
  63                                         $port = ';port='.trim($port);
  64                                 }
  65                                 else {
  66                                         $port = '';
  67                                         $portnum = '';
  68                                 }
  69                         }
  70
  71                         switch ($MYSQL_HANDLER[1]) {
  72                                 case 'sybase':
  73                                 case 'dblib':
  74                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
  75                                         else $port = '';
  76                                         $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
  77                                 break;
  78                                 case 'mssql':
  79                                         if (is_numeric($portnum)) $port = ','.intval($portnum);
  80                                         else $port = '';
  81                                         $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
  82                                 break;
  83                                 case 'oci':
  84                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
  85                                         else $port = '';
  86                                         $DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$mysql_database, $mysql_user, $mysql_password);
  87                                 break;
  88                                 case 'odbc':
  89                                         if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
  90                                         else $port = '';
  91                                         $DBH = new PDO($MYSQL_HANDLER[1].':DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME='.$host.$port.';DATABASE='.$mysql_database.';PROTOCOL=TCPIP;UID='.$mysql_user.';PWD='.$mysql_password);
  92
  93                                 break;
  94                                 case 'pgsql':
  95                                         if (is_numeric($portnum)) $port = ';port='.intval($portnum);
  96                                         else $port = '';
  97                                         $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
  98                                 break;
  99                                 case 'sqlite':
 100                                 case 'sqlite2':
 101                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
 102                                         else $port = '';
 103                                         $DBH = new PDO($MYSQL_HANDLER[1].':'.$mysql_database, $mysql_user, $mysql_password);
 104                                 break;
 105                                 default:
 106                                         //mysql
 107                                         $DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$mysql_database, $mysql_user, $mysql_password);
 108                                 break;
 109                         }
 110
 111
 112
 113                 } catch (PDOException $e) {
 114                         $DBH =NULL;
 115                         startUpError('<p>a1 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
 116                 }
 117 //echo '<hr />DBH: '.print_r($DBH,true).'<hr />';
 118                 return $DBH;
 119         }
 120
 121 /**
 122  * Connects to mysql server
 123  */
 124         function sql_connect() {
 125                 global $MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWORD, $MYSQL_DATABASE, $MYSQL_CONN, $MYSQL_HANDLER, $SQL_DBH;
 126                 $SQL_DBH = NULL;
 127                 try {
 128                         if (strpos($MYSQL_HOST,':') === false) {
 129                                 $host = $MYSQL_HOST;
 130                                 $port = '';
 131                         }
 132                         else {
 133                                 list($host,$port) = explode(":",$MYSQL_HOST);
 134                                 if (isset($port)) {
 135                                         $portnum = $port;
 136                                         $port = ';port='.trim($port);
 137                                 }
 138                                 else {
 139                                         $port = '';
 140                                         $portnum = '';
 141                                 }
 142                         }
 143
 144                         switch ($MYSQL_HANDLER[1]) {
 145                                 case 'sybase':
 146                                 case 'dblib':
 147                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
 148                                         else $port = '';
 149                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 150                                 break;
 151                                 case 'mssql':
 152                                         if (is_numeric($portnum)) $port = ','.intval($portnum);
 153                                         else $port = '';
 154                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 155                                 break;
 156                                 case 'oci':
 157                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
 158                                         else $port = '';
 159                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 160                                 break;
 161                                 case 'odbc':
 162                                         if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
 163                                         else $port = '';
 164                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME='.$host.$port.';DATABASE='.$MYSQL_DATABASE.';PROTOCOL=TCPIP;UID='.$MYSQL_USER.';PWD='.$MYSQL_PASSWORD);
 165
 166                                 break;
 167                                 case 'pgsql':
 168                                         if (is_numeric($portnum)) $port = ';port='.intval($portnum);
 169                                         else $port = '';
 170                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 171                                 break;
 172                                 case 'sqlite':
 173                                 case 'sqlite2':
 174                                         if (is_numeric($portnum)) $port = ':'.intval($portnum);
 175                                         else $port = '';
 176                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':'.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 177                                 break;
 178                                 default:
 179                                         //mysql
 180                                         $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 181                                 break;
 182                         }
 183
 184                         //$SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASSWORD);
 185
 186 // <add for garble measure>
 187                         if (strpos($MYSQL_HANDLER[1], 'mysql') === 0) {
 188                                 $resource = $SQL_DBH->query("show variables LIKE 'character_set_database'");
 189                                 $resource->bindColumn('Value', $charset);
 190                                 $resource->fetchAll();
 191                                 $SQL_DBH->exec("SET CHARACTER SET " . $charset);
 192                         }
 193 // </add for garble measure>*/
 194                 } catch (PDOException $e) {
 195                         $SQL_DBH = NULL;
 196                         startUpError('<p>a2 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
 197                 }
 198 //              echo '<hr />DBH: '.print_r($SQL_DBH,true).'<hr />';
 199                 $MYSQL_CONN &= $SQL_DBH;
 200                 return $SQL_DBH;
 201
 202         }
 203
 204 /**
 205  * disconnects from SQL server
 206  */
 207         function sql_disconnect(&$dbh=NULL) {
 208                 global $SQL_DBH;
 209                 if (is_null($dbh)) $SQL_DBH = NULL;
 210                 else $dbh = NULL;
 211         }
 212
 213         function sql_close(&$dbh=NULL) {
 214                 global $SQL_DBH;
 215                 if (is_null($dbh)) $SQL_DBH = NULL;
 216                 else $dbh = NULL;
 217         }
 218
 219 /**
 220  * executes an SQL query
 221  */
 222         function sql_query($query,$dbh=NULL) {
 223                 global $SQLCount,$SQL_DBH;
 224                 $SQLCount++;
 225 //echo '<hr />SQL_DBH: ';
 226 //print_r($SQL_DBH);
 227 //echo '<hr />DBH: ';
 228 //print_r($dbh);
 229 //echo '<hr />';
 230 //echo $query.'<hr />';
 231                 if (is_null($dbh)) $res = $SQL_DBH->query($query);
 232                 else $res = $dbh->query($query);
 233                 if ($res->errorCode() != '00000') {
 234                         $errors = $res->errorInfo();
 235                         print("SQL error with query $query: " . $errors[0].'-'.$errors[1].' '.$errors[2] . '<p />');
 236                 }
 237
 238                 return $res;
 239         }
 240
 241 /**
 242  * executes an SQL error
 243  */
 244         function sql_error($dbh=NULL)
 245         {
 246                 global $SQL_DBH;
 247                 if (is_null($dbh)) $error = $SQL_DBH->errorInfo();
 248                 else $error = $dbh->errorInfo();
 249                 if ($error[0] != '00000') {
 250                         return $error[0].'-'.$error[1].' '.$error[2];
 251                 }
 252                 else return '';
 253         }
 254
 255 /**
 256  * executes an SQL db select
 257  */
 258         function sql_select_db($db,&$dbh=NULL)
 259         {
 260                 global $MYSQL_HOST, $MYSQL_USER, $MYSQL_PASSWORD, $MYSQL_DATABASE, $MYSQL_CONN, $MYSQL_HANDLER, $SQL_DBH;
 261 //echo '<hr />'.print_r($dbh,true).'<hr />';
 262 //exit;
 263                 if (is_null($dbh)) {
 264                         try {
 265                                 $SQL_DBH = NULL;
 266                                 list($host,$port) = explode(":",$MYSQL_HOST);
 267                                 if (isset($port)) {
 268                                         $portnum = $port;
 269                                         $port = ';port='.trim($port);
 270                                 }
 271                                 else {
 272                                         $port = '';
 273                                         $portnum = '';
 274                                 }
 275                                 //$SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.trim($host).$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 276                                 //$SQL_DBH = sql_connect();
 277                                 switch ($MYSQL_HANDLER[1]) {
 278                                         case 'sybase':
 279                                         case 'dblib':
 280                                                 if (is_numeric($portnum)) $port = ':'.intval($portnum);
 281                                                 else $port = '';
 282                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 283                                         break;
 284                                         case 'mssql':
 285                                                 if (is_numeric($portnum)) $port = ','.intval($portnum);
 286                                                 else $port = '';
 287                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 288                                         break;
 289                                         case 'oci':
 290                                                 if (is_numeric($portnum)) $port = ':'.intval($portnum);
 291                                                 else $port = '';
 292                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':dbname=//'.$host.$port.'/'.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 293                                         break;
 294                                         case 'odbc':
 295                                                 if (is_numeric($portnum)) $port = ';PORT='.intval($portnum);
 296                                                 else $port = '';
 297                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':DRIVER={IBM DB2 ODBC DRIVER};HOSTNAME='.$host.$port.';DATABASE='.$db.';PROTOCOL=TCPIP;UID='.$MYSQL_USER.';PWD='.$MYSQL_PASSWORD);
 298
 299                                         break;
 300                                         case 'pgsql':
 301                                                 if (is_numeric($portnum)) $port = ';port='.intval($portnum);
 302                                                 else $port = '';
 303                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 304                                         break;
 305                                         case 'sqlite':
 306                                         case 'sqlite2':
 307                                                 if (is_numeric($portnum)) $port = ':'.intval($portnum);
 308                                                 else $port = '';
 309                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':'.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 310                                         break;
 311                                         default:
 312                                                 //mysql
 313                                                 $SQL_DBH = new PDO($MYSQL_HANDLER[1].':host='.$host.$port.';dbname='.$db, $MYSQL_USER, $MYSQL_PASSWORD);
 314                                         break;
 315                                 }
 316                                 return 1;
 317                         } catch (PDOException $e) {
 318                                 startUpError('<p>a3 Error!: ' . $e->getMessage() . '</p>', 'Connect Error');
 319                                 return 0;
 320                         }
 321                 }
 322                 else {
 323                         if ($dbh->exec("USE $db") !== false) return 1;
 324                         else return 0;
 325                 }
 326         }
 327
 328 /**
 329  * executes an SQL real escape
 330  */
 331         function sql_real_escape_string($val,$dbh=NULL)
 332         {
 333                 return addslashes($val);
 334         }
 335
 336 /**
 337  * executes an PDO::quote() like escape, ie adds quotes arround the string and escapes chars as needed
 338  */
 339         function sql_quote_string($val,$dbh=NULL) {
 340                 global $SQL_DBH;
 341                 if (is_null($dbh))
 342                         return $SQL_DBH->quote($val);
 343                 else
 344                         return $dbh->quote($val);
 345         }
 346
 347 /**
 348  * executes an SQL insert id
 349  */
 350         function sql_insert_id($dbh=NULL)
 351         {
 352                 global $SQL_DBH;
 353                 if (is_null($dbh))
 354                         return $SQL_DBH->lastInsertId();
 355                 else
 356                         return $dbh->lastInsertId();
 357         }
 358
 359 /**
 360  * executes an SQL result request
 361  */
 362         function sql_result($res, $row = 0, $col = 0)
 363         {
 364                 $results = array();
 365                 if (intval($row) < 1) {
 366                         $results = $res->fetch(PDO::FETCH_BOTH);
 367                         return $results[$col];
 368                 }
 369                 else {
 370                         for ($i = 0; $i < intval($row); $i++) {
 371                                 $results = $res->fetch(PDO::FETCH_BOTH);
 372                         }
 373                         $results = $res->fetch(PDO::FETCH_BOTH);
 374                         return $results[$col];
 375                 }
 376         }
 377
 378 /**
 379  * frees sql result resources
 380  */
 381         function sql_free_result($res)
 382         {
 383                 $res = NULL;
 384                 return true;
 385         }
 386
 387 /**
 388  * returns number of rows in SQL result
 389  */
 390         function sql_num_rows($res)
 391         {
 392                 return $res->rowCount();
 393         }
 394
 395 /**
 396  * returns number of rows affected by SQL query
 397  */
 398         function sql_affected_rows($res)
 399         {
 400                 return $res->rowCount();
 401         }
 402
 403 /**
 404  * Get number of fields in result
 405  */
 406         function sql_num_fields($res)
 407         {
 408                 return $res->columnCount();
 409         }
 410
 411 /**
 412  * fetches next row of SQL result as an associative array
 413  */
 414         function sql_fetch_assoc($res)
 415         {
 416                 $results = array();
 417                 $results = $res->fetch(PDO::FETCH_ASSOC);
 418                 return $results;
 419         }
 420
 421 /**
 422  * Fetch a result row as an associative array, a numeric array, or both
 423  */
 424         function sql_fetch_array($res)
 425         {
 426                 $results = array();
 427                 $results = $res->fetch(PDO::FETCH_BOTH);
 428                 return $results;
 429         }
 430
 431 /**
 432  * fetches next row of SQL result as an object
 433  */
 434         function sql_fetch_object($res)
 435         {
 436                 $results = NULL;
 437                 $results = $res->fetchObject();
 438                 return $results;
 439         }
 440
 441 /**
 442  * Get a result row as an enumerated array
 443  */
 444         function sql_fetch_row($res)
 445         {
 446                 $results = array();
 447                 $results = $res->fetch(PDO::FETCH_NUM);
 448                 return $results;
 449         }
 450
 451 /**
 452  * Get column information from a result and return as an object
 453  */
 454         function sql_fetch_field($res,$offset = 0)
 455         {
 456                 $results = array();
 457                 $obj = NULL;
 458                 $results = $res->getColumnMeta($offset);
 459                 foreach($results as $key=>$value) {
 460                         $obj->$key = $value;
 461                 }
 462                 return $obj;
 463         }
 464
 465 /**
 466  * Get current system status (returns string)
 467  */
 468         function sql_stat($dbh=NULL)
 469         {
 470                 //not implemented
 471                 global $SQL_DBH;
 472                 if (is_null($dbh))
 473                         return '';
 474                 else
 475                 return '';
 476         }
 477
 478 /**
 479  * Returns the name of the character set
 480  */
 481         function sql_client_encoding($dbh=NULL)
 482         {
 483                 //not implemented
 484                 global $SQL_DBH;
 485                 if (is_null($dbh))
 486                         return '';
 487                 else
 488                         return '';
 489         }
 490
 491 /**
 492  * Get SQL client version
 493  */
 494         function sql_get_client_info()
 495         {
 496                 global $SQL_DBH;
 497                 return $SQL_DBH->getAttribute(constant("PDO::ATTR_CLIENT_VERSION"));
 498         }
 499
 500 /**
 501  * Get SQL server version
 502  */
 503         function sql_get_server_info($dbh=NULL)
 504         {
 505                 global $SQL_DBH;
 506                 if (is_null($dbh))
 507                         return $SQL_DBH->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
 508                 else
 509                         return $dbh->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
 510         }
 511
 512 /**
 513  * Returns a string describing the type of SQL connection in use for the connection or FALSE on failure
 514  */
 515         function sql_get_host_info($dbh=NULL)
 516         {
 517                 global $SQL_DBH;
 518                 if (is_null($dbh))
 519                         return $SQL_DBH->getAttribute(constant("PDO::ATTR_SERVER_INFO"));
 520                 else
 521                         return $dbh->getAttribute(constant("PDO::ATTR_SERVER_INFO"));
 522         }
 523
 524 /**
 525  * Returns the SQL protocol on success, or FALSE on failure.
 526  */
 527         function sql_get_proto_info($dbh=NULL)
 528         {
 529                 //not implemented
 530                 global $SQL_DBH;
 531                 if (is_null($dbh))
 532                         return false;
 533                 else
 534                         return false;
 535         }
 536
 537 /**
 538  * Get the name of the specified field in a result
 539  */
 540         function sql_field_name($res, $offset = 0)
 541         {
 542                 $column = $res->getColumnMeta($offset);
 543                 if ($column) {
 544                         return $column['name'];
 545                 }
 546                 return false;
 547         }
 548
 549 /**************************************************************************
 550         Unimplemented mysql_* functions
 551
 552 # mysql_ data_ seek (maybe useful)
 553 # mysql_ errno (maybe useful)
 554 # mysql_ fetch_ lengths (maybe useful)
 555 # mysql_ field_ flags (maybe useful)
 556 # mysql_ field_ len (maybe useful)
 557 # mysql_ field_ name (maybe useful)
 558 # mysql_ field_ seek (maybe useful)
 559 # mysql_ field_ table (maybe useful)
 560 # mysql_ field_ type (maybe useful)
 561 # mysql_ info (maybe useful)
 562 # mysql_ list_ processes (maybe useful)
 563 # mysql_ ping (maybe useful)
 564 # mysql_ set_ charset (maybe useful, requires php >=5.2.3 and mysql >=5.0.7)
 565 # mysql_ thread_ id (maybe useful)
 566
 567 # mysql_ db_ name (useful only if working on multiple dbs which we do not do)
 568 # mysql_ list_ dbs (useful only if working on multiple dbs which we do not do)
 569
 570 # mysql_ pconnect (probably not useful and could cause some unintended performance issues)
 571 # mysql_ unbuffered_ query (possibly useful, but complicated and not supported by all database drivers (pdo))
 572
 573 # mysql_ change_ user (deprecated)
 574 # mysql_ create_ db (deprecated)
 575 # mysql_ db_ query (deprecated)
 576 # mysql_ drop_ db (deprecated)
 577 # mysql_ escape_ string (deprecated)
 578 # mysql_ list_ fields (deprecated)
 579 # mysql_ list_ tables (deprecated)
 580 # mysql_ tablename (deprecated)
 581
 582 *******************************************************************/
 583
 584
 585 }
 586 ?>