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