1<?php 2 /** 3 * SAPDB database class 4 * @author Kai Hofmann <khofmann@probusiness.de> 5 * @copyright Copyright (C) 2003,2004 Free Software Foundation, Inc. http://www.fsf.org/ 6 * @license http://www.fsf.org/licenses/lgpl.html GNU Lesser General Public License 7 * @package phpgwapi 8 * @subpackage database 9 * @version $Id: class.db_sapdb.inc.php 15462 2004-11-06 15:34:27Z powerstat $ 10 */ 11 12 /** 13 * SAPDB database class 14 * 15 * @package phpgwapi 16 * @subpackage database 17 * @ignore 18 */ 19 class db extends db_ 20 { 21 /** 22 * @var integer $UseODBCCursor Type of cursor 23 * @access private 24 */ 25 var $UseODBCCursor = 0; 26 27 /** 28 * @var string $type Connection type 29 */ 30 var $type = 'odbc'; 31 /** 32 * @var string $revision Api revision 33 */ 34 var $revision = '1.0'; 35 /** 36 * @var string $Driver Database driver 37 * @access private 38 */ 39 var $Driver = 'SAP DB'; 40 41 42 /** 43 * Connect to database 44 * 45 * @param string $Database Database name 46 * @param string $Host Database host 47 * @param string $User Database user 48 * @param string $Password Database users password 49 * @return resource Database connection_id 50 */ 51 function connect($Database = '', $Host = '', $User = '', $Password = '') 52 { 53 /* Handle defaults */ 54 if ($Database == '') 55 { 56 $Database = $this->Database; 57 } 58 if ($Host == '') 59 { 60 $Host = $this->Host; 61 } 62 if ($User == '') 63 { 64 $User = $this->User; 65 } 66 if ($Password == '') 67 { 68 $Password = $this->Password; 69 } 70 $Driver = $this->Driver; 71 /* establish connection, select database */ 72 if (!$this->Link_ID) 73 { 74 $dsn = 'Driver={' . $Driver . '};Server=' . $Host . ';Database=' . $Database; 75 if ($GLOBALS['phpgw_info']['server']['db_persistent']) 76 { 77 $this->Link_ID = odbc_pconnect($dsn, $User, $Password, $this->UseODBCCursor); 78 } 79 else 80 { 81 $this->Link_ID = odbc_connect($dsn, $User, $Password, $this->UseODBCCursor); 82 } 83 if (!$this->Link_ID) 84 { 85 $this->halt(($GLOBALS['phpgw_info']['server']['db_persistent'] ? 'p' : '') . "connect($Host, $User, \$Password) failed."); 86 return 0; 87 } 88 } 89 return $this->Link_ID; 90 } 91 92 93 94 /** 95 * Disconnect database connection 96 * 97 * This only affects systems not using persistant connections 98 * @return integer 1: ok; 0: not possible/already closed 99 */ 100 function disconnect() 101 { 102 if($this->Link_ID != 0) 103 { 104 @odbc_close($this->Link_ID); 105 $this->Link_ID = 0; 106 return 1; 107 } 108 else 109 { 110 return 0; 111 } 112 } 113 114 115 /** 116 * Escape strings before sending them to the database 117 * 118 * @param string $str the string to be escaped 119 * @return string escaped sting 120 */ 121 function db_addslashes($str) 122 { 123 return str_replace("'","''",$str); 124 } 125 126 127 /** 128 * Convert a unix timestamp to a rdms specific timestamp 129 * 130 * @param int unix timestamp 131 * @return string rdms specific timestamp 132 */ 133 function to_timestamp($epoch) 134 { 135 return date('Y-m-d H:i:s',$epoch); 136 } 137 138 /** 139 * Convert a rdms specific timestamp to a unix timestamp 140 * 141 * @param string rdms specific timestamp 142 * @return int unix timestamp 143 */ 144 function from_timestamp($timestamp) 145 { 146 ereg('([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})',$timestamp,$parts); 147 148 return mktime($parts[4],$parts[5],$parts[6],$parts[2],$parts[3],$parts[1]); 149 } 150 151 /** 152 * Discard the current query result 153 */ 154 function free() 155 { 156 @odbc_free_result($this->Query_ID); 157 $this->Query_ID = 0; 158 } 159 160 161 /** 162 * Execute a query 163 * 164 * @param string $Query_String the query to be executed 165 * @param mixed $line the line method was called from - use __LINE__ 166 * @param string $file the file method was called from - use __FILE__ 167 * @return integer Current result if sucesful and null if failed 168 */ 169 function query($Query_String, $line = '', $file = '') 170 { 171 /* No empty queries, please, since PHP4 chokes on them. 172 * The empty query string is passed on from the constructor, 173 * when calling the class without a query, e.g. in situations 174 * like these: '$db = new db_Subclass;' 175 */ 176 if (($Query_String == '') || (!$this->connect())) 177 { 178 return 0; 179 } 180 181 // New query, discard previous result. 182 if ($this->Query_ID) 183 { 184 $this->free(); 185 } 186 187 if ($this->Debug) 188 { 189 printf("Debug: query = %s<br />\n", $Query_String); 190 } 191 192 $this->Query_ID = @odbc_exec($this->Link_ID,$Query_String); 193 $this->Row = 0; 194 195 odbc_binmode($this->Query_ID, 1); 196 odbc_longreadlen($this->Query_ID, 4096); 197 198 199 if (! $this->Query_ID) 200 { 201 $this->Errno = odbc_error($this->Link_ID); 202 $this->Error = odbc_errormsg($this->Link_ID); 203 $this->halt("Invalid SQL: ".$Query_String, $line, $file); 204 } 205 else 206 { 207 $this->Errno = 0; 208 $this->Error = ''; 209 } 210 211 // Will return nada if it fails. That's fine. 212 return $this->Query_ID; 213 } 214 215 216 /** 217 * Execute a query with limited result set 218 * 219 * @param string $Query_String the query to be executed 220 * @param integer $offset row to start from 221 * @param mixed $line the line method was called from - use __LINE__ 222 * @param string $file the file method was called from - use __FILE__ 223 * @param int $num_rows number of rows to return (optional), if unset will use $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs'] 224 * @return integer Current result if sucesful and null if failed 225 */ 226 function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = '') 227 { 228 if (! $num_rows) 229 { 230 $num_rows = $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs']; 231 } 232 233 if ($offset == 0) 234 { 235 $Query_String .= ' LIMIT ' . $num_rows; 236 } 237 else 238 { 239 $Query_String .= ' LIMIT ' . $offset . ',' . $num_rows; 240 } 241 242 if ($this->Debug) 243 { 244 printf("Debug: limit_query = %s<br />offset=%d, num_rows=%d<br />\n", $Query_String, $offset, $num_rows); 245 } 246 247 return $this->query($Query_String, $line, $file); 248 } 249 250 251 /** 252 * Move to the next row in the results set 253 * 254 * @return boolean was another row found? 255 */ 256 function next_record() 257 { 258 if (!$this->Query_ID) 259 { 260 $this->halt('next_record called with no query pending.'); 261 return 0; 262 } 263 264 // $this->Record = array(); 265 // $stat = odbc_fetch_into($this->Query_ID, ++$this->Row, &$this->Record); 266 267 $this->Record = @odbc_fetch_array($this->Query_ID); 268 $this->Row += 1; 269 $this->Errno = odbc_error(); 270 $this->Error = odbc_errormsg(); 271 272 $stat = is_array($this->Record) ? (count($this->Record) > 0) : 0; 273 if (!$stat && $this->Auto_Free) 274 { 275 $this->free(); 276 } 277 return $stat; 278 } 279 280 281 /** 282 * Move to position in result set 283 * 284 * @param integer $pos Required row (optional), default first row 285 * @return integer 1 if sucessful or 0 if not found 286 */ 287 function seek($pos = 0) 288 { 289 $this->Row = $pos; 290 return 1; 291 } 292 293 294 /** 295 * Begin Transaction 296 * 297 * @return integer current transaction id 298 */ 299 function transaction_begin() 300 { 301 return $this->query('COMMIT',__LINE__,__FILE__); 302 } 303 304 /** 305 * Complete the transaction 306 * 307 * @return boolean True if sucessful, False if failed 308 */ 309 function transaction_commit() 310 { 311 if ((!$this->Errno) && ($this->Link_ID != 0)) 312 { 313 return odbc_exec($this->Link_ID,'COMMIT'); 314 } 315 else 316 { 317 return False; 318 } 319 } 320 321 /** 322 * Rollback the current transaction 323 * 324 * @return boolean True if sucessful, False if failed 325 */ 326 function transaction_abort() 327 { 328 if ($this->Link_ID != 0) 329 { 330 return @odbc_exec($this->Link_ID,'ROLLBACK'); 331 } 332 } 333 334 335 /** 336 * Find the primary key of the last insertion on the current db connection 337 * 338 * @param string $table name of table the insert was performed on 339 * @param string $field the autoincrement primary key of the table 340 * @return integer The id, -1 if failed 341 */ 342 function get_last_insert_id($table, $field) 343 { 344 /* This will get the last insert ID created on the current connection. Should only be called 345 * after an insert query is run on a table that has an auto incrementing field. $table and 346 * $field are required, but unused here since it's unnecessary for mysql. For compatibility 347 * with pgsql, the params must be supplied. 348 */ 349 if (!isset($table) || $table == '' || !isset($field) || $field == '') 350 { 351 return -1; 352 } 353 354 355 $result = @odbc_exec($this->Link_ID, "select max($field) from $table"); 356 if (!$result) 357 { 358 return -1; 359 } 360 $Record = @odbc_result($result,1); 361 @odbc_free_result($result); 362 if (is_array($Record)) 363 { 364 return -1; 365 } 366 367 return $Record; 368 } 369 370 371 /** 372 * Lock a table 373 * 374 * @param string $table name of table to lock 375 * @param string $mode type of lock required (optional), default write 376 * @return boolean True if sucessful, False if failed 377 */ 378 function lock($table, $mode = 'write') 379 { 380 $result = $this->transaction_begin(); 381 382 if ($mode == 'write') 383 { 384 if (is_array($table)) 385 { 386 while ($t = each($table)) 387 { 388 $result = odbc_exec($this->Link_ID,'lock table ' . $t[1] . ' in share mode'); 389 } 390 } 391 else 392 { 393 $result = odbc_exec($this->Link_ID, 'lock table ' . $table . ' in share mode'); 394 } 395 } 396 else 397 { 398 $result = 1; 399 } 400 401 return $result; 402 } 403 404 /** 405 * Unlock a table 406 * 407 * @return boolean True if sucessful, False if failed 408 */ 409 function unlock() 410 { 411 return $this->transaction_commit(); 412 } 413 414 415 /** 416 * Get the number of rows affected by last update 417 * 418 * @return integer number of affected rows 419 */ 420 function affected_rows() 421 { 422 return odbc_num_rows($this->Query_ID); 423 } 424 425 /** 426 * Number of rows in current result set 427 * 428 * @return integer number of rows 429 */ 430 function num_rows() 431 { 432 // Many ODBC drivers don't support odbc_num_rows() on SELECT statements. 433 $num_rows = odbc_num_rows($this->Query_ID); 434 435 // This is a workaround. It is intended to be ugly. 436 if ($num_rows < 0) 437 { 438 $i=10; 439 while (odbc_fetch_row($this->Query_ID, $i)) 440 { 441 $i*=10; 442 } 443 444 $j=0; 445 while ($i!=$j) 446 { 447 $k= $j+intval(($i-$j)/2); 448 if (odbc_fetch_row($this->Query_ID, $k)) 449 { 450 $j=$k; 451 } 452 else 453 { 454 $i=$k; 455 } 456 if (($i-$j)==1) 457 { 458 if (odbc_fetch_row($this->Query_ID, $i)) 459 { 460 $j=$i; 461 } 462 else 463 { 464 $i=$j; 465 } 466 } 467 //printf("$i $j $k <br />"); 468 } 469 $num_rows=$i; 470 } 471 return $num_rows; 472 } 473 474 /** 475 * Number of fields in current row 476 * 477 * @return integer number of fields 478 */ 479 function num_fields() 480 { 481 return count($this->Record)/2; 482 return @odbc_num_fields($this->Query_ID); 483 } 484 485 486 /** 487 * Get the id for the next sequence 488 * 489 * @param string $seq_name Name of the sequence 490 * @return integer sequence id 491 */ 492 function nextid($seq_name) 493 { 494 $this->connect(); 495 496 if ($this->lock($this->Seq_Table)) 497 { 498 /* get sequence number (locked) and increment */ 499 $q = sprintf("select nextid from %s where seq_name = '%s'", 500 $this->Seq_Table, 501 $seq_name); 502 $id = @odbc_exec($this->Link_ID,$q); 503 $res = @odbc_fetch_array($id); 504 505 /* No current value, make one */ 506 if (!is_array($res)) 507 { 508 $currentid = 0; 509 $q = sprintf("insert into %s values('%s', %s)", 510 $this->Seq_Table, 511 $seq_name, 512 $currentid); 513 $id = @odbc_exec($this->Link_ID,$q); 514 } 515 else 516 { 517 $currentid = $res["nextid"]; 518 } 519 $nextid = $currentid + 1; 520 $q = sprintf("update %s set nextid = '%s' where seq_name = '%s'", 521 $this->Seq_Table, 522 $nextid, 523 $seq_name); 524 $id = @odbc_exec($this->Link_ID,$q); 525 $this->unlock(); 526 } 527 else 528 { 529 $this->halt("cannot lock ".$this->Seq_Table." - has it been created?"); 530 return 0; 531 } 532 return $nextid; 533 } 534 535 536 /** 537 * Get description of a table 538 * 539 * @param string $table name of table to describe 540 * @param boolean $full optional, default False summary information, True full information 541 * @return array Table meta data 542 */ 543 function metadata($table,$full=false) 544 { 545 $count = 0; 546 $id = 0; 547 $res = array(); 548 549 $this->connect(); 550 $id = odbc_exec($this->Link_ID, "select * from $table"); 551 if (!$id) 552 { 553 $this->Errno = odbc_error($this->Link_ID); 554 $this->Error = odbc_errormsg($this->Link_ID); 555 $this->halt('Metadata query failed.'); 556 } 557 $count = odbc_num_fields($id); 558 559 for ($i=1; $i <= $count; ++$i) 560 { 561 $res[$i]['table'] = $table; 562 $name = odbc_field_name ($id, $i); 563 $res[$i]['name'] = $name; 564 $res[$i]['type'] = odbc_field_type ($id, $name); 565 $res[$i]['len'] = 0; // can we determine the width of this column? 566 $res[$i]['flags'] = ''; // any optional flags to report? 567 } 568 odbc_free_result($id); 569 return $res; 570 } 571 572 573 574 /** 575 * Error handler 576 * 577 * @param string $msg error message 578 * @param integer $line line of calling method/function (optional) 579 * @param string $file file of calling method/function (optional) 580 * @access private 581 */ 582 function halt($msg, $line = '', $file = '') 583 { 584 $this->Error = @odbc_errormsg($this->Link_ID); // need to be BEFORE unlock, 585 $this->Errno = @odbc_error($this->Link_ID); // else we get its error or none 586 587 if ($this->Link_ID) // only if we have a link, else infinite loop 588 { 589 $this->unlock(); /* Just in case there is a table currently locked */ 590 } 591 if ($this->Halt_On_Error == "no") 592 { 593 return; 594 } 595 /* Just in case there is a table currently locked */ 596 $this->transaction_abort(); 597 598 $this->haltmsg($msg); 599 600 if ($file) 601 { 602 printf("<br /><b>File:</b> %s",$file); 603 } 604 if ($line) 605 { 606 printf("<br /><b>Line:</b> %s",$line); 607 } 608 609 if ($this->Halt_On_Error != "report") 610 { 611 echo "<p><b>Session halted.</b>"; 612 $GLOBALS['phpgw']->common->phpgw_exit(True); 613 } 614 } 615 616 617 /** 618 * Display database error 619 * 620 * @param string $msg Error message 621 */ 622 function haltmsg($msg) 623 { 624 printf("<b>Database error:</b> %s<br />\n", $msg); 625 if ($this->Errno != "0" && $this->Error != "()") 626 { 627 printf("<b>ODBC Error</b>: %s (%s)<br />\n",$this->Errno,$this->Error); 628 } 629 } 630 631 632 /** 633 * Get a list of table names in the current database 634 * 635 * @return array List of the tables 636 */ 637 function table_names() 638 { 639 if (!$this->Link_ID) 640 { 641 $this->connect(); 642 } 643 if (!$this->Link_ID) 644 { 645 return array(); 646 } 647 $return = array(); 648 $this->query("SELECT TABLENAME FROM DOMAIN.TABLES where owner like '" . strtoupper($this->User) . "'"); 649 $i=0; 650 while ($this->next_record()) 651 { 652 $return[$i]['table_name'] = strtolower($this->Record['TABLENAME']); 653 $return[$i]['tablespace_name'] = $this->Database; 654 $return[$i]['database'] = $this->Database; 655 ++$i; 656 } 657 return $return; 658 } 659 660 661 /** 662 * Create a new database 663 * 664 * @param string $adminname Name of database administrator user (optional) 665 * @param string $adminpasswd Password for the database administrator user (optional) 666 */ 667 function create_database($adminname = '', $adminpasswd = '') 668 { 669 $currentUser = $this->User; 670 $currentPassword = $this->Password; 671 $currentDatabase = $this->Database; 672 673 if ($adminname != '') 674 { 675 $this->User = $adminname; 676 $this->Password = $adminpasswd; 677 $this->Database = 'sapdb'; 678 } 679 $this->disconnect(); 680 $this->query('create user ' . $currentUser . ' password ' . $currentPassword . ' resource not exclusive',__LINE__,__FILE__); 681 $this->disconnect(); 682 683 $this->User = $currentUser; 684 $this->Password = $currentPassword; 685 $this->Database = $currentDatabase; 686 $this->connect(); 687 } 688 689 690 /** 691 * Return the value of a filed 692 * 693 * @param string $Field_Name name of field 694 * @return string The field value 695 */ 696 function f($Field_Name) 697 { 698 return $this->Record[strtoupper($Field_Name)]; 699 } 700 701 702 703 /** 704 * Prepare SQL statement 705 * 706 * @param string $query 707 * @return Result identifier for query_prepared_statement() or FALSE 708 * @see query_prepared_statement() 709 */ 710 function prepare_sql_statement($query) 711 { 712 if (($query == '') || (!$this->connect())) 713 { 714 return(FALSE); 715 } 716 return(odbc_prepare($this->connect(),$query)); 717 } 718 719 720 /** 721 * Execute prepared SQL statement 722 * 723 * @param resource $result_id Result identifier from prepare_sql_statement() 724 * @param array $parameters_array Parameters for the prepared SQL statement 725 * @return boolean TRUE on success or FALSE on failure 726 * @see prepare_sql_statement() 727 */ 728 function query_prepared_statement($result_id, $parameters_array) 729 { 730 if ((!$this->connect()) || (!$result_id)) 731 { 732 return(FALSE); 733 } 734 return(odbc_execute($result_id,$parameters_array)); 735 } 736 737 738 } 739?> 740