ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:database:ibm_db2

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
database:ibm_db2 [2015/11/26 18:47] – [Creating Tables] mnewnhamv5:database:ibm_db2 [2023/01/24 03:53] (current) mnewnham
Line 1: Line 1:
 ====== IBM DB2 ===== ====== IBM DB2 =====
 +~~NOTOC~~
 +<WRAP right box round 300px>
 +== Specification ==
 +^Driver Name|db2|
 +^Data Provider|db2|
 +^Status|Active((This driver is actively supported by ADOdb project members))|
 +^Windows|Yes|
 +^Unix|Yes|
 +^ADOdb V5|Yes|
 +^ADOdb V6|Yes|
 +
 +== Alternative Drivers ==
 +[[v5:database:odbc#odbc_db2|ODBC Driver For IBM DB2]]\\
 +== See Also ==
 +[[v5:reference:connection:setconnectionparameter#ibmdb2|setConnectionParameter()]]
 +</WRAP>
 ===== Description ===== ===== Description =====
-This driver provides a connection to IBM DB2 Databases, using the IBM Data Client. +This driver provides a connection to IBM DB2 Databases, using the IBM Data Client. This driver replaces the original [[v5:database:odbc#odbc_db2|DB2 ODBC]] driver. 
-===== Support Status ===== +== PHP Drivers For Windows == 
-This driver is supported by ADOdb project members +PHP 7 and 8 drivers for windows are currently available [[https://github.com/ibmdb/php_ibm_db2|here]] 
-===== Specification ====== +
-|Driver Name|db2| +
-|Status|Active| +
-|Supported OS|Windows,Unix| +
-===== Generic Driver Option Support ===== +
-|[[reference:adodb_fetch_mode|ADODB_FETCH_BOTH]]|Yes| +
-|[[reference:startTrans|Transactions]]|Yes| +
-|[[reference:recordCount|Record Counting]]|Yes| +
-|[[reference:Sequences|Sequences]]|Yes|+
  
-===== Driver Specific Options ===== +----------------------------------
----------------------------+
 ==== uCaseTables ==== ==== uCaseTables ====
 <WRAP right box> <WRAP right box>
Line 25: Line 31:
 </WRAP> </WRAP>
  
-This variable sets the keys in [[dictionary:MetaColumns]],[[dictionary:MetaPrimaryKeys]] and [[dictionary:MetaForeignKeys]] to upper-case when retrieved. The default is true.+This variable sets the keys in [[v5:dictionary:metacolumns]],[[v5:dictionary:metaprimarykeys]] and [[v5:dictionary:metaforeignkeys]] to upper-case when retrieved. The default is true.
  
 <code php> <code php>
Line 36: Line 42:
 </code> </code>
 -------------------------------- --------------------------------
 +-------------------------------------------------------
 ===== Connection Example Using Instance ===== ===== Connection Example Using Instance =====
-<WRAP right info 300px>+<WRAP info>
 The DB2 database must be catalogued for a connection like this to work. The DB2 database must be catalogued for a connection like this to work.
 </WRAP> </WRAP>
 +==== Building a catalog entry ====
 +This is a simple example building a catalogued connection from a windows client to a remote DB2 server running on a machine named adodb-db2.local. The server is running on port 25010. We will add a DSN entry called //**adodb-test**//
 +
 +<code>
 +db2cli writecfg add -database SAMPLE -host adodb-db2.local -port 25010 
 +db2cli writecfg add -dsn adodb-test -database SAMPLE -host adodb-db2.local -port 25010
 +</code>
 +To use this DSN
 <code php> <code php>
 include '../adodb/adodb.inc.php'; include '../adodb/adodb.inc.php';
-$database = 'SAMPLE';+$dsn = 'adodb-test';
 $user = 'db2inst1'; $user = 'db2inst1';
 $password = 'instance'; $password = 'instance';
 $conn = ADOnewConnection('db2'); $conn = ADOnewConnection('db2');
-$conn->connect($database, $user, $password);+$conn->connect($dsn, $user, $password);
 </code> </code>
  
Line 52: Line 67:
 <code php> <code php>
 include '../adodb/adodb.inc.php'; include '../adodb/adodb.inc.php';
-$dsn = 'hostname=192.168.0.30;protocol=tcpip;port=50000;database=SAMPLE;uid=db2inst1;pwd=instance';+$dsn = 'hostname=adodb-db2.local;protocol=tcpip;port=25010;database=SAMPLE;uid=db2inst1;pwd=instance';
 $conn = ADOnewConnection('db2'); $conn = ADOnewConnection('db2');
 $conn->connect($dsn); $conn->connect($dsn);
 </code> </code>
-===== Alternative Drivers ===== 
-==== odbc_db2 ==== 
-|Driver Name|odbc_db2| 
-|Status|Unknown| 
-|Supported OS|Windows,Unix| 
  
-Connects to DB2 database using either the IBM or 3rd party ODBC driver +------------------------------ 
-<WRAP tip+==== db2oci ==== 
-If the driver supports it setting the Cursor Type to ''SQL_CUR_USE_ODBC'' can provide substantial performance improvements+<WRAP right box round 300px
 +== Specification == 
 +^Driver Name|db2oci| 
 +^Data Provider|db2| 
 +^Status|Deprecated((This driver is deprecated as of ADOdb version 5.21 and will be removed in ADOdb version 6))| 
 +^Windows|Yes| 
 +^Unix|Yes| 
 +^ADOdb V5|Yes| 
 +^ADOdb V6|No|
  
-To set SQL_CUR_USE_ODBC for drivers that require it, do this: 
-<code php> 
-$db = NewADOConnection('odbc_db2'); 
-$db->curMode = SQL_CUR_USE_ODBC; 
-$db->Connect($dsn, $userid, $pwd); 
-</code> 
 </WRAP> </WRAP>
-<WRAP tip> +This driver re-maps ibm :0 bind variables to oracle compatible ? variables. Use this driver to enhance compatiblity between [[v5:database:oracle]] and DB2 drivers.
-== USING CLI INTERFACE ==+
  
-There were reports that the $host and $database params have to be reversed in Connect() when using the CLI interface. +The driver is deprecated. To write portable code that uses bind parameters and can be shared between DB2 and Oracle, use the [[v5:reference:connection:param|param()]] method.
-</WRAP>+
  
-<WRAP tip> +------------------------------------------------- 
-== System Error 5 == +==== db2ora ====
-IF you get a System Error 5 when trying to Connect/Load, it could be a permission problem. Give the user connecting +
-to DB2 full rights to the DB2 SQLLIB directory, and place the user in the DBUSERS group.+
  
 +<WRAP right box round 300px>
 +== Specification ==
 +^Driver Name|db2|
 +^Data Provider|db2|
 +^Status|Obsolete((This driver is not supported, and will be removed in ADOdb V6))|
 +^Windows|Yes|
 +^Unix|Yes|
 +^ADOdb V5|Yes|
 +^ADOdb V6|No|
 </WRAP> </WRAP>
-==== db2oci ==== 
-|Driver Name|db2oci| 
-|Status|Active| 
-|Supported OS|Windows,Unix| 
-This driver re-maps ibm :0 bind variables to oracle compatible ? variables. Use this driver to enhance compatiblity between [[database:Oracle]] and DB2 drivers  
- 
-------------------------------------------------- 
-<WRAP right alert round> 
-This driver will be removed in version 6.0.0 
-</WRAP> 
-==== db2ora ==== 
-|Driver Name|db2ora| 
-|Status|Obsolete| 
-|Supported OS|Windows,Unix| 
 This driver provides undocumented bind variable mapping from ibm to oracle.The functionality appears to overlap the db2_oci driver This driver provides undocumented bind variable mapping from ibm to oracle.The functionality appears to overlap the db2_oci driver
-{{tag>[ibm db2]}}+{{tag>[ibm db2 tier1]}}
  
 ===== Case Sensitivity In Table And Column Names ===== ===== Case Sensitivity In Table And Column Names =====
Line 133: Line 136:
 Any table name containing special characters will need to be quoted in all queries. Any table name containing special characters will need to be quoted in all queries.
  
-==== Using metaCasing with DB2 ==== 
-When using DB2, it is important to remember that when using [[dictionary:setmetacaseoption|metaCasing]] and having mixed or lowercase table names in the database may mean that tables may become difficult to access if the are not in the same casing convention as to what is normally used.  
- 
-Generally, development standards mean that table names in a database are created to a casing standard, for example, all upper-case or lower-case table and column names. In that case, metaCasing will work the same as in other databases, e.g. 
-<code php> 
-/* 
-* Connection to DB2 smple database, accessing a table called ACT 
-* Set the casing to lower case 
-*/ 
-$db->setMetaCaseOption($db:METACASE_LOWER); 
-$cols = $db->metaColumnNames('ACT'); 
-print_r($cols); 
-/* 
- * Prints 
-Array( 
-  [0] => actno, 
-  [1] => actname, 
-  [2] => actdesc 
-  ) 
-  */ 
- 
-</code> 
------------------ 
-<WRAP right info 300px> 
-These rules also apply to column naming 
-</WRAP> 
-===== The Impact Of METACASE_NATIVE ===== 
-Setting the metaCasing option to ''METACASE_NATIVE'' has an impact on the entire life cycle of a table, and it's use should be understood. 
-==== Creating Tables ==== 
-Consider the following case-sensitive table name: ''accountTable'', and how it is created and referenced in ADOdb. 
- 
-^MetaCase^Created As^SQL Retrieval^Presentation in MetaFunctions^  
-|METACASE_UPPER|ACCOUNTTABLE|SELECT FROM ACCOUNTTABLE,accountttable,accountTable etc.|ACCOUNTTABLE| 
-|METACASE_LOWER|ACCOUNTTABLE|SELECT FROM ACCOUNTTABLE,accountttable,accountTable etc.|accounttable| 
-|METACASE_NATIVE|accountTable|SELECT FROM "accountTable"|accountTable|  
- 
-As shown, once a table is created in non-upper case when the metaCasing value is ''METACASE_NATIVE'', it **must always** be retrieved using the same casing, and all SQL operations must quote the table names. The functions [[reference:autoexecute|autoExecute()]],[[reference:getinsertsql|getInsertSql()]], and [[reference:getupdatesql|getUpdateSql()]] will automatically quote table and column names if ''METACASE_NATIVE'' is set. For DB2, this behaviour overrides the ''$quoteFieldnames'' parameter. 
  
 {{tag>[db2 supported]}} {{tag>[db2 supported]}}
v5/database/ibm_db2.1448560028.txt.gz · Last modified: 2017/04/21 11:25 (external edit)