source: branches/comu-ver2/data/module/adodb/drivers/adodb-odbc_db2.inc.php @ 18701

Revision 18701, 9.1 KB checked in by nanasess, 14 years ago (diff)

Copyright の更新(#601)
  • Property svn:eol-style set to LF
  • Property svn:keywords set to Id Revision Date
  • Property svn:mime-type set to text/x-httpd-php
Line 
1<?php
2/*
3v4.992 10 Nov 2009  (c) 2000-2010 John Lim (jlim#natsoft.com). All rights reserved.
4  Released under both BSD license and Lesser GPL library license.
5  Whenever there is any discrepancy between the two licenses,
6  the BSD license will take precedence.
7Set tabs to 4 for best viewing.
8 
9  Latest version is available at http://adodb.sourceforge.net
10 
11  DB2 data driver. Requires ODBC.
12 
13From phpdb list:
14
15Hi Andrew,
16
17thanks a lot for your help. Today we discovered what
18our real problem was:
19
20After "playing" a little bit with the php-scripts that try
21to connect to the IBM DB2, we set the optional parameter
22Cursortype when calling odbc_pconnect(....).
23
24And the exciting thing: When we set the cursor type
25to SQL_CUR_USE_ODBC Cursor Type, then
26the whole query speed up from 1 till 10 seconds
27to 0.2 till 0.3 seconds for 100 records. Amazing!!!
28
29Therfore, PHP is just almost fast as calling the DB2
30from Servlets using JDBC (don't take too much care
31about the speed at whole: the database was on a
32completely other location, so the whole connection
33was made over a slow network connection).
34
35I hope this helps when other encounter the same
36problem when trying to connect to DB2 from
37PHP.
38
39Kind regards,
40Christian Szardenings
41
422 Oct 2001
43Mark Newnham has discovered that the SQL_CUR_USE_ODBC is not supported by
44IBM's DB2 ODBC driver, so this must be a 3rd party ODBC driver.
45
46From the IBM CLI Reference:
47
48SQL_ATTR_ODBC_CURSORS (DB2 CLI v5)
49This connection attribute is defined by ODBC, but is not supported by DB2
50CLI. Any attempt to set or get this attribute will result in an SQLSTATE of
51HYC00 (Driver not capable).
52
53A 32-bit option specifying how the Driver Manager uses the ODBC cursor
54library.
55
56So I guess this means the message [above] was related to using a 3rd party
57odbc driver.
58
59Setting SQL_CUR_USE_ODBC
60========================
61To set SQL_CUR_USE_ODBC for drivers that require it, do this:
62
63$db = NewADOConnection('odbc_db2');
64$db->curMode = SQL_CUR_USE_ODBC;
65$db->Connect($dsn, $userid, $pwd);
66
67
68
69USING CLI INTERFACE
70===================
71
72I have had reports that the $host and $database params have to be reversed in
73Connect() when using the CLI interface. From Halmai Csongor csongor.halmai#nexum.hu:
74
75> The symptom is that if I change the database engine from postgres or any other to DB2 then the following
76> connection command becomes wrong despite being described this version to be correct in the docs.
77>
78> $connection_object->Connect( $DATABASE_HOST, $DATABASE_AUTH_USER_NAME, $DATABASE_AUTH_PASSWORD, $DATABASE_NAME )
79>
80> In case of DB2 I had to swap the first and last arguments in order to connect properly.
81
82
83System Error 5
84==============
85IF you get a System Error 5 when trying to Connect/Load, it could be a permission problem. Give the user connecting
86to DB2 full rights to the DB2 SQLLIB directory, and place the user in the DBUSERS group.
87*/
88
89// security - hide paths
90if (!defined('ADODB_DIR')) die();
91
92if (!defined('_ADODB_ODBC_LAYER')) {
93    include(ADODB_DIR."/drivers/adodb-odbc.inc.php");
94}
95if (!defined('ADODB_ODBC_DB2')){
96define('ADODB_ODBC_DB2',1);
97
98class ADODB_ODBC_DB2 extends ADODB_odbc {
99    var $databaseType = "db2"; 
100    var $concat_operator = '||';
101    var $sysTime = 'CURRENT TIME';
102    var $sysDate = 'CURRENT DATE';
103    var $sysTimeStamp = 'CURRENT TIMESTAMP';
104    // The complete string representation of a timestamp has the form
105    // yyyy-mm-dd-hh.mm.ss.nnnnnn.
106    var $fmtTimeStamp = "'Y-m-d-H.i.s'";
107    var $ansiOuter = true;
108    var $identitySQL = 'values IDENTITY_VAL_LOCAL()';
109    var $_bindInputArray = true;
110     var $hasInsertID = true;
111    var $rsPrefix = 'ADORecordset_odbc_';
112   
113    function ADODB_DB2()
114    {
115        if (strncmp(PHP_OS,'WIN',3) === 0) $this->curmode = SQL_CUR_USE_ODBC;
116        $this->ADODB_odbc();
117    }
118   
119    function IfNull( $field, $ifNull )
120    {
121        return " COALESCE($field, $ifNull) "; // if DB2 UDB
122    }
123   
124    function ServerInfo()
125    {
126        //odbc_setoption($this->_connectionID,1,101 /*SQL_ATTR_ACCESS_MODE*/, 1 /*SQL_MODE_READ_ONLY*/);
127        $vers = $this->GetOne('select versionnumber from sysibm.sysversions');
128        //odbc_setoption($this->_connectionID,1,101, 0 /*SQL_MODE_READ_WRITE*/);
129        return array('description'=>'DB2 ODBC driver', 'version'=>$vers);
130    }
131   
132    function _insertid()
133    {
134        return $this->GetOne($this->identitySQL);
135    }
136   
137    function RowLock($tables,$where,$flds='1 as ignore')
138    {
139        if ($this->_autocommit) $this->BeginTrans();
140        return $this->GetOne("select $flds from $tables where $where for update");
141    }
142   
143    function &MetaTables($ttype=false,$showSchema=false, $qtable="%", $qschema="%")
144    {
145    global $ADODB_FETCH_MODE;
146   
147        $savem = $ADODB_FETCH_MODE;
148        $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
149        $qid = odbc_tables($this->_connectionID, "", $qschema, $qtable, "");
150       
151        $rs = new ADORecordSet_odbc($qid);
152       
153        $ADODB_FETCH_MODE = $savem;
154        if (!$rs) {
155            $false = false;
156            return $false;
157        }
158        $rs->_has_stupid_odbc_fetch_api_change = $this->_has_stupid_odbc_fetch_api_change;
159       
160        $arr =& $rs->GetArray();
161        //print_r($arr);
162       
163        $rs->Close();
164        $arr2 = array();
165       
166        if ($ttype) {
167            $isview = strncmp($ttype,'V',1) === 0;
168        }
169        for ($i=0; $i < sizeof($arr); $i++) {
170       
171            if (!$arr[$i][2]) continue;
172            if (strncmp($arr[$i][1],'SYS',3) === 0) continue;
173           
174            $type = $arr[$i][3];
175           
176            if ($showSchema) $arr[$i][2] = $arr[$i][1].'.'.$arr[$i][2];
177           
178            if ($ttype) {
179                if ($isview) {
180                    if (strncmp($type,'V',1) === 0) $arr2[] = $arr[$i][2];
181                } else if (strncmp($type,'T',1) === 0) $arr2[] = $arr[$i][2];
182            } else if (strncmp($type,'S',1) !== 0) $arr2[] = $arr[$i][2];
183        }
184        return $arr2;
185    }
186
187    function &MetaIndexes ($table, $primary = FALSE, $owner=false)
188    {
189        // save old fetch mode
190        global $ADODB_FETCH_MODE;
191        $save = $ADODB_FETCH_MODE;
192        $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
193        if ($this->fetchMode !== FALSE) {
194               $savem = $this->SetFetchMode(FALSE);
195        }
196        $false = false;
197        // get index details
198        $table = strtoupper($table);
199        $SQL="SELECT NAME, UNIQUERULE, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME='$table'";
200        if ($primary)
201            $SQL.= " AND UNIQUERULE='P'";
202        $rs = $this->Execute($SQL);
203        if (!is_object($rs)) {
204            if (isset($savem))
205                $this->SetFetchMode($savem);
206            $ADODB_FETCH_MODE = $save;
207            return $false;
208        }
209        $indexes = array ();
210        // parse index data into array
211        while ($row = $rs->FetchRow()) {
212            $indexes[$row[0]] = array(
213               'unique' => ($row[1] == 'U' || $row[1] == 'P'),
214               'columns' => array()
215            );
216            $cols = ltrim($row[2],'+');
217            $indexes[$row[0]]['columns'] = explode('+', $cols);
218        }
219        if (isset($savem)) {
220            $this->SetFetchMode($savem);
221            $ADODB_FETCH_MODE = $save;
222        }
223        return $indexes;
224    }
225   
226    // Format date column in sql string given an input format that understands Y M D
227    function SQLDate($fmt, $col=false)
228    {   
229    // use right() and replace() ?
230        if (!$col) $col = $this->sysDate;
231        $s = '';
232       
233        $len = strlen($fmt);
234        for ($i=0; $i < $len; $i++) {
235            if ($s) $s .= '||';
236            $ch = $fmt[$i];
237            switch($ch) {
238            case 'Y':
239            case 'y':
240                $s .= "char(year($col))";
241                break;
242            case 'M':
243                $s .= "substr(monthname($col),1,3)";
244                break;
245            case 'm':
246                $s .= "right(digits(month($col)),2)";
247                break;
248            case 'D':
249            case 'd':
250                $s .= "right(digits(day($col)),2)";
251                break;
252            case 'H':
253            case 'h':
254                if ($col != $this->sysDate) $s .= "right(digits(hour($col)),2)";   
255                else $s .= "''";
256                break;
257            case 'i':
258            case 'I':
259                if ($col != $this->sysDate)
260                    $s .= "right(digits(minute($col)),2)";
261                    else $s .= "''";
262                break;
263            case 'S':
264            case 's':
265                if ($col != $this->sysDate)
266                    $s .= "right(digits(second($col)),2)";
267                else $s .= "''";
268                break;
269            default:
270                if ($ch == '\\') {
271                    $i++;
272                    $ch = substr($fmt,$i,1);
273                }
274                $s .= $this->qstr($ch);
275            }
276        }
277        return $s;
278    }
279 
280   
281    function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputArr=false)
282    {
283        $nrows = (integer) $nrows;
284        if ($offset <= 0) {
285        // could also use " OPTIMIZE FOR $nrows ROWS "
286            if ($nrows >= 0) $sql .=  " FETCH FIRST $nrows ROWS ONLY ";
287            $rs =& $this->Execute($sql,$inputArr);
288        } else {
289            if ($offset > 0 && $nrows < 0);
290            else {
291                $nrows += $offset;
292                $sql .=  " FETCH FIRST $nrows ROWS ONLY ";
293            }
294            $rs =& ADOConnection::SelectLimit($sql,-1,$offset,$inputArr);
295        }
296       
297        return $rs;
298    }
299   
300};
301 
302
303class  ADORecordSet_odbc_db2 extends ADORecordSet_odbc {   
304   
305    var $databaseType = "db2";     
306   
307    function ADORecordSet_db2($id,$mode=false)
308    {
309        $this->ADORecordSet_odbc($id,$mode);
310    }
311
312    function MetaType($t,$len=-1,$fieldobj=false)
313    {
314        if (is_object($t)) {
315            $fieldobj = $t;
316            $t = $fieldobj->type;
317            $len = $fieldobj->max_length;
318        }
319       
320        switch (strtoupper($t)) {
321        case 'VARCHAR':
322        case 'CHAR':
323        case 'CHARACTER':
324        case 'C':
325            if ($len <= $this->blobSize) return 'C';
326       
327        case 'LONGCHAR':
328        case 'TEXT':
329        case 'CLOB':
330        case 'DBCLOB': // double-byte
331        case 'X':
332            return 'X';
333       
334        case 'BLOB':
335        case 'GRAPHIC':
336        case 'VARGRAPHIC':
337            return 'B';
338           
339        case 'DATE':
340        case 'D':
341            return 'D';
342       
343        case 'TIME':
344        case 'TIMESTAMP':
345        case 'T':
346            return 'T';
347       
348        //case 'BOOLEAN':
349        //case 'BIT':
350        //  return 'L';
351           
352        //case 'COUNTER':
353        //  return 'R';
354           
355        case 'INT':
356        case 'INTEGER':
357        case 'BIGINT':
358        case 'SMALLINT':
359        case 'I':
360            return 'I';
361           
362        default: return 'N';
363        }
364    }
365}
366
367} //define
368?>
Note: See TracBrowser for help on using the repository browser.