IBM DB2 Functions
Table of Contents
- db2_autocommit — Returns or sets the AUTOCOMMIT state for a database connection
- db2_bind_param — Binds a PHP variable to an SQL statement parameter
- db2_client_info — Returns an object with properties that describe the DB2 database client
- db2_close — Closes a database connection
- db2_column_privileges — Returns a result set listing the columns and associated privileges for a table
- db2_columns — Returns a result set listing the columns and associated metadata for a table
- db2_commit — Commits a transaction
- db2_conn_error — Returns a string containing the SQLSTATE returned by the last connection attempt
- db2_conn_errormsg — Returns the last connection error message and SQLCODE value
- db2_connect — Returns a connection to a database
- db2_cursor_type — Returns the cursor type used by a statement resource
- db2_escape_string — Used to escape certain characters
- db2_exec — Executes an SQL statement directly
- db2_execute — Executes a prepared SQL statement
- db2_fetch_array — Returns an array, indexed by column position, representing a row in a result set
- db2_fetch_assoc — Returns an array, indexed by column name, representing a row in a result set
- db2_fetch_both — Returns an array, indexed by both column name and position, representing a row in a result set
- db2_fetch_object — Returns an object with properties representing columns in the fetched row
- db2_fetch_row — Sets the result set pointer to the next row or requested row
- db2_field_display_size — Returns the maximum number of bytes required to display a column
- db2_field_name — Returns the name of the column in the result set
- db2_field_num — Returns the position of the named column in a result set
- db2_field_precision — Returns the precision of the indicated column in a result set
- db2_field_scale — Returns the scale of the indicated column in a result set
- db2_field_type — Returns the data type of the indicated column in a result set
- db2_field_width — Returns the width of the current value of the indicated column in a result set
- db2_foreign_keys — Returns a result set listing the foreign keys for a table
- db2_free_result — Frees resources associated with a result set
- db2_free_stmt — Frees resources associated with the indicated statement resource
- db2_get_option — Retrieves an option value for a statement resource or a connection resource
- db2_last_insert_id — Returns the auto generated ID of the last insert query that successfully
executed on this connection
- db2_lob_read — Gets a user defined size of LOB files with each invocation
- db2_next_result — Requests the next result set from a stored procedure
- db2_num_fields — Returns the number of fields contained in a result set
- db2_num_rows — Returns the number of rows affected by an SQL statement
- db2_pclose — Closes a persistent database connection
- db2_pconnect — Returns a persistent connection to a database
- db2_prepare — Prepares an SQL statement to be executed
- db2_primary_keys — Returns a result set listing primary keys for a table
- db2_procedure_columns — Returns a result set listing stored procedure parameters
- db2_procedures — Returns a result set listing the stored procedures registered in a database
- db2_result — Returns a single column from a row in the result set
- db2_rollback — Rolls back a transaction
- db2_server_info — Returns an object with properties that describe the DB2 database server
- db2_set_option — Set options for connection or statement resources
- db2_special_columns — Returns a result set listing the unique row identifier columns for a table
- db2_statistics — Returns a result set listing the index and statistics for a table
- db2_stmt_error — Returns a string containing the SQLSTATE returned by an SQL statement
- db2_stmt_errormsg — Returns a string containing the last SQL statement error message
- db2_table_privileges — Returns a result set listing the tables and associated privileges in a database
- db2_tables — Returns a result set listing the tables and associated metadata in a database
Коментарии
If you wish to connect to an iSeries Server, such as an AS/400, there are two options. Installing DB2 Express-C v9.5 and purchasing DB2 Connect Personal Edition for about $500 or getting System I Access for Linux, which is a free download. Please note the server requirements for each of these. For example, we originally went with DB2 Express-C and decided upon Ubuntu 7.10, but learned about System I Access, from an IBM rep, which requires redhat package manager, after the entire server was up and running.
DB2 Express-C
http://www-306.ibm.com/software/data/db2/express/
DB2 Connect Personal
http://www-306.ibm.com/software/data/db2/db2connect/edition-pe.html
System I Access for Linux
http://www-03.ibm.com/systems/i/software/access/index.html
NOTE: System I Access requires redhat package manager.
The DB/2 Run-Time-Client can be found here:http://www-1.ibm.com/support/docview.wss?rs=71&uid=swg21255394
Select the 'Runtime Client Installable for Windows' further down the page and download it.
Clients for other platform (incl. 64-Bit Windows) are also available from that page.
Spent a lot of time trying to get this to work on a 64bit box. You'll need to make sure you set your db2 client to 64 bit mode! If you didn't set your instance to 64bit, you can run ./db2iupdt -w64 db2instance as root to set it. Hope this helps someone!There seems to be a lot of good documentation
for Linux users compiling PHP with DB2 support,
but decent Windows notes are minimal.
You do not need to install full DB2 clients to get DB2
working with DB2, all you need is the IBM Data
Server Driver for ODBC, CLI, and .NET which is only
16.1 meg.
You can download the driver from here:
Direct Link:
ftp://ftp.software.ibm.com/ps/products/db2/fixes2/englsh-us/
db2_v95/dsdriver/fp2/v9.5fp2_nt32_dsdriver_EN.exe
Home Page:
http://www-01.ibm.com/support/docview.wss?rs=71&uid=swg21287889
This includes both the drivers required and the PHP
dll php_ibm_db2_5.2.2.dll
Once installed the drivers do not setup the correct
path environmental variable,
so add the following to your path:
C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin
Once thats done all should work! No massive
400meg client downloads required.
Whats even better about these drivers is that you
dont need to install them,
you can simply copy the bin directory to any server,
add it to your path and it will just work.
This is great for anyone developing PHP-GTK applications,
I copy the bin directory into my php-gkt2 directory
and execute using the following batch script:
path = %PATH%;.\IBM DATA SERVER DRIVER\bin
php-win.exe %*
This lets me role out lightweight DB2 client desktop
apps that dont have to be installed,
can just be coppied from PC to PC or ran over a
network or from USB stick.
As your only installing the client drivers you wont be
able to catalog databases,
so always use the full connection string. Here is a
quick bit of code to get you started:
$database = 'databasename';
$user = 'user';
$password = 'password';
$hostname = '127.0.0.1';
$port = 50000;
$conn_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$database;" .
"HOSTNAME=$hostname;PORT=$port;".
"PROTOCOL=TCPIP;UID=$user;PWD=$password;";
$conn = db2_connect($conn_string, '', '');
$query = 'SELECT * FROM TABLE';
$res = db2_prepare($conn, $query);
db2_execute($res);
while ($row = db2_fetch_array($res)) {
print_r($row);
}
// IBM DB2 funcitons like MySQL (ODBC based)
// "Ighor Toth" <igtoth@gmail.com>
// Date: 08/05/2014
// usage:
// db2_connect(verbose,instance,username,password); -> also reads config file if nothing declared db2.conf.inc.php
// db2_query(db2_connect_return,sql)
// db2_fetch_array(result);
// db2_fetch_object(result);
// db2_display_table(db2_connect_return,sql); // select only
function db2_connect($verbose = null,$db2name = null,$username = null,$password = null) {
if(!isset($verbose)){
$verbose = TRUE; // TRUE or FALSE, if not set TRUE
}
if(!isset($db2name)){ // NOT DECLARED
include("db2.conf.inc.php"); // CHECK CONFIG FILE
if(!isset($db2name)){
if ($verbose == TRUE){
echo ("DB2 Instance not selected");
exit();
} else {
exit();
}
}
} else if (!isset($username)){
echo ("DB2 Instance username not specified");
exit();
}
$db2conn = odbc_connect($db2name, $username, $password);
if (($verbose == TRUE) && ($db2conn == 0)) {
echo("Connection to database failed.");
$sqlerror = odbc_errormsg($db2conn);
echo($sqlerror);
}
return($db2conn);
}
function db2_query($db2conn,$sql){
$result = odbc_exec($db2conn, $sql);
if ($result == 0) {
echo("QUERY = '$sql' FAILED.<br>\n");
$sqlerror = odbc_errormsg($db2conn);
echo($sqlerror);
} else {
// odbc_result_all prints all of the rows
// for a result set ID as an HTML table
return $result;
}
}
function db2_fetch_array($result, $rownumber=null){
$array = array();
if (!($cols = odbc_fetch_into($result, $result_array, $rownumber))) {
return false;
}
for ($i = 1; $i <= $cols; $i++) {
$array[odbc_field_name($result, $i)] = $result_array[$i - 1];
}
return $array;
}
function db2_fetch_object($result){
if(function_exists("db2_fetch_object")) return db2_fetch_object($result);
$rs = array();
$rs_obj = false;
if( odbc_fetch_into($result, $rs) ){
foreach( $rs as $key=>$value ){
$fkey = odbc_field_name($result, $key+1);
$rs_obj->$fkey = trim($value);
}
}
return $rs_obj;
}
function db2_display_table($db2conn,$sql) {
// select all rows from the table
if(!isset($db2conn)||!isset($sql)){
echo("ERROR db2_display_table: Function missing arguments");
exit();
}
$check = explode(" ",$sql);
if($check[0]!="SELECT"){
echo("ERROR db2_display_table: Not SELECT SQL query");
}
if ($db2conn != 0) {
// odbc_exec returns 0 if the statement fails;
// otherwise it returns a result set ID
$result = odbc_exec($db2conn, $sql);
if ($result == 0) {
echo("SELECT statement failed.");
$sqlerror = odbc_errormsg($db2conn);
echo($sqlerror);
} else {
// odbc_result_all prints all of the rows
// for a result set ID as an HTML table
odbc_result_all($result);
}
}
}