ODBC PHP

Open Database Connectivity (ODBC) has become an industry standard for communicating with a database. The model is simple. Client software is designed to use an ODBC API. Vendors write drivers that implement this API on the client side and talk natively to their database on the server side. This allows application developers to write one application that can communicate with many different databases simply by changing the driver, which is an external file. ODBC uses SQL as its language for communicating with any database, even when the database isn't relational. Microsoft offers drivers that allow you to query text files and Excel workbooks.

Microsoft has offered free ODBC drivers for some time, but only for their operating systems. ODBC drivers for UNIX are harder to come by. Most database manufacturers offer drivers, and there are third parties, like Intersolv, that sell optimized drivers for both Windows and UNIX platforms. Most of the database with native support in PHP can also be accessed via ODBC. There are also numerous databases that can only be accessed viaODBC by PHP. Two that others have tried are Solid and Empress. Stig Bakken, Andreas Karajannis and Frank Kromann have contributed to the creation of the ODBC extension.

boolean odbc_autocommit(integer connection, boolean on)
The odbc_autocommit function sets whether queries are automatically committed when executed. By default this is on. The connection argument is an integer returned by the odbc_connect or odbc_pconnect functions. This function has to be used intelligently, as not all ODBC drivers support commits and rollbacks.

<?
$Connection = odbc_connect("store", "sa", "sa");
// turn off autocommit
odbc_autocommit($Connection, FALSE);
?>

boolean odbc_binmode(integer result, integer mode)
Use odbc_binmode to set the way binary columns return data for a result set. When binary data are returned by the driver, each byte is represented by hexadecimal codes. By default, PHP will convert these codes into raw binary data. If you have to use the odbc_longreadlen function to set the maximum length of long data to anything other than zero, then the modes in Table 13.8 apply. If the maximum read length is zero, the data are always converted to raw binary data.

<?
// get a GIF from a database and send it to browser
// connect to database
$Connection = odbc_connect("store", "admin", "secret");
// execute query
$Query = "SELECT picture ";
Core PHP Programming IT-SC book 408
$Query .= "FROM employee ";
$Query .= "WHERE id=17 ";
$Result = odbc_do($Connection, $Query);
// make sure binmode is set for binary pass through
odbc_binmode($Result, ODBC_BINMODE_PASSTHRU);
// make sure longreadlen mode
// is set for echo to browser
odbc_longreadlen($Result, 0);
// get the first row, ignore the rest
odbc_fetch_row($Result);
// send header so browser knows it's a gif
header("Content-type: image/gif");
// get the picture
odbc_result($Result, 1);
?>

ODBC-Binary-Data-Modes

odbc_close(integer connection)
Use odbc_close to close a connection to a database. If there are open transactions for the connection, an error will be returned and the connection will not be closed.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// execute query
$Query = "SELECT price ";
$Query .= "FROM catalog ";
$Query .= "WHERE id=10 ";
$Result = odbc_do($Connection, $Query);
odbc_fetch_row($Result)
$price = odbc_result($Result, 1);
print("$price<BR>n");
odbc_close($Connection);
?>

odbc_close_all()
The odbc_close_all function closes every connection you have open to ODBC data sources. Like odbc_close, it will report an error if you have an open transaction on one of the connections.

<?
// connect to database three times
$Connection1 = odbc_connect("store", "guest", "guest");
$Connection2 = odbc_connect("store", "guest", "guest");
$Connection3 = odbc_connect("store", "guest", "guest");
// close all the connections
odbc_close_all();
?>

boolean odbc_commit(integer connection)
Use odbc_commit to commit all pending actions for the specified connection. If automatic commit is turned on, as is default, this function has no effect. Also, make sure your driver supports transactions before using this function.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// turn off autocommit
odbc_autocommit($Connection, FALSE);
// put everything on sale
$Query = "UPDATE catalog ";
$Query .= "SET price = price * 0.9 ";
$Result = odbc_do($Connection, $Query);
// commit
if(odbc_commit($Connection))
{
print("Commit successful!<BR>n");
}
odbc_close($Connection);
?>

integer odbc_connect(string dsn, string user, string password, integer cursor_type)
Core PHP Programming IT-SC book 410 Use odbc_connect to connect to an ODBC data source. A connection identifier is returned, which is used by most of the other functions in this section. The user and password arguments are required, so if your driver does not require them, pass empty strings. The optional cursor_type argument forces the use of a particular cursor so that you may avoid problems with some ODBC drivers. Using the SQL_CUR_USE_ODBC constant for cursor type may avoid problems with calling stored procedures or getting row numbers.

string odbc_cursor(integer result)
Use odbc_cursor to fetch the name of a cursor for a result set.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// execute query
$Query = "SELECT name, price ";
$Query .= "FROM catalog ";
$Result = odbc_do($Connection, $Query);
print("Cursor: " . odbc_cursor($Result) . "<BR>n");
while(odbc_fetch_row($Result))
{
$name = odbc_result($Result, 1);
$price = odbc_result($Result, 2);
print("$name: $price<BR>n");
}
odbc_close($Connection);
?>

integer odbc_do(integer connection, string query)
Use odbc_do to execute a query on a connection. A result identifier is returned, and is used in many of the other functions for fetching result data.

integer odbc_exec(integer connection, string query)
The odbc_exec function is an alias for odbc_do.

integer odbc_execute(integer result, array parameters)
The odbc_execute function executes a prepared statement. The result argument is an identifier returned by odbc_prepare. The parameters argument is an array that must be Core PHP Programming IT-SC book 411 passed by reference and will be set with the value of the result columns. See odbc_prepare for an example of use.

integer odbc_fetch_into(integer result, array fields) integer dbc_fetch_into(integer result, integer row, array fields)
The odbc_fetch_into function gets the specified row for the specified result set and puts the columns into the fields array. The fields argument must be passed by reference. The number of columns in the result set is returned. The row argument may be omitted, in which case the next row in the set is returned.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// execute query
$Query = "SELECT name, price ";
$Query .= "FROM catalog ";
$Result = odbc_do($Connection, $Query);
while(odbc_fetch_into($Result, &$fields))
{
$name = $fields[0];
$price = $fields[1];
print("$name: $price<BR>n");
}
odbc_close($Connection);
?>

boolean odbc_fetch_row(integer result, integer row)
Use odbc_fetch_row to get a row of data from a result set. The data for the row is stored in internal memory, ready to be retrieved with the odbc_result function. The row argument is optional and, if left out, the next available row will be returned. FALSE will be returned when there are no more rows in the result set. See the odbc_result function for an example of use.

integer odbc_field_len(integer result, integer field)
Use odbc_field_len to get the length of a field in a result set. Fields are numbered starting with one.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
Core PHP Programming IT-SC book 412
// execute query
$Query = "SELECT name, price ";
$Query .= "FROM catalog ";
$Result = odbc_do($Connection, $Query);
print(odbc_field_len($Result, 1));
odbc_close($Connection);
?>

string odbc_field_name(integer result, integer field)
Use odbc_field_name to get the name of a field in a result set. Fields are numbered starting with one.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// execute query
$Query = "SELECT name, price ";
$Query .= "FROM catalog ";
$Result = odbc_do($Connection, $Query);
print(odbc_field_name($Result, 1));
odbc_close($Connection);
?>

string odbc_field_type(integer result, integer field)
Use odbc_field_type to get the type of a field in a result set. Fields are numbered starting with one.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// execute query
$Query = "SELECT name, price ";
$Query .= "FROM catalog ";
$Result = odbc_do($Connection, $Query);
print(odbc_field_type($Result, 1));
odbc_close($Connection);
?>

boolean odbc_free_result(integer result)
Use odbc_free_result to free the memory associated with the result set. This is not strictly necessary, but it's a good idea if you are worried about running out of memory. If autocommit is disabled and you free a result set before calling odbc_commit, the transaction will be rolled back.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// execute query
$Query = "SELECT name, price ";
$Query .= "FROM catalog ";
$Result = odbc_do($Connection, $Query);
// free the result set
odbc_free_result($Result);
odbc_close($Connection);
?>

boolean odbc_longreadlen(integer result, integer length)
Use odbc_longreadlen to set the maximum length for values of any columns of type long. This includes binary columns such as longvarbinary. By default the maximum length is zero, which has the special meaning of causing fetched columns to be echoed to the browser. Any other positive number will cause returned values to be truncated to the specified length. Note that it is not always apparent that a field is considered to be a long by the ODBC driver. For example, a memo column in Microsoft Access is a long. Data appearing in the wrong place are a sign of fetching a long where you didn't expect it. One strategy to avoid these problems is to always call longreadlen. See odbc_binmode for an example of use.

integer odbc_num_fields(integer result)
Use odbc_num_fields to find the number of fields in the result set.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
Core PHP Programming
IT-SC book 414
// execute query
$Query = "SELECT name, price ";
$Query .= "FROM catalog ";
$Result = odbc_do($Connection, $Query);
print(odbc_num_fields($Result));
odbc_close($Connection);
?>

integer odbc_num_rows(integer result)
The odbc_num_rows function returns the number of rows in the set, or the number ofrows affected by a delete or insert if the driver supports it.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// execute query
$Query = "SELECT name, price ";
$Query .= "FROM catalog ";
$Result = odbc_do($Connection, $Query);
print(odbc_num_rows($Result));
odbc_close($Connection);
?>

integer odbc_pconnect(string dsn, string user, string password)
The odbc_pconnect function operates similarly to odbc_connect. A connection is attempted to the specified Data Source Name (DSN) and a connection identifier is returned. The connection should not be closed with odbc_close. It will persist as long as the Web server process. The next time a script executes odbc_pconnect, PHP will first check for existing connections.

<?
// connect to database
$Connection = odbc_pconnect("store", "guest", "guest");
?>

integer odbc_prepare(integer connection, string query)
Core PHP Programming IT-SC book 415.The odbc_prepare function parses a query and prepares it for execution. A result identifier that may be passed to odbc_execute is returned. Preparing statements can be more efficient than making the driver reparse statements. This is usually the case where you have many rows to insert into the same table. To specify a value to be filled in later, use a question mark.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// prepare query
$Query = "INSERT INTO catalog (ID, Name, Price) ";
$Query .= "VALUES(?, ?, ?) ";
$Result = odbc_prepare($Connection, $Query);
// insert
// 0, 2000 Calendar, 20.00
// 1, 2001 Calendar, 20.50
// 2, 2002 Calendar, 21.00
for($index = 2000; $index = 2002; $index++)
{
$values[0] = $index-2000;
$values[1] = "$index Calendar";
$values[2] = 20.00 + (0.50 * ($index-2000));
odbc_execute($Result, $values);
}
odbc_close($Connection);
?>

string odbc_result(integer result, string field)
Use odbc_result to get the value of a field for the current row. Fields may be referenced by number or name. If by using numbers, start counting fields with 1. If you specify a field by name, do not include the table name. This function is affected by the settings controlled by odbc_binmode and odbc_longreadlen. An important fact to keep in mind is that while in most cases the value of the field will be returned, fields that contain long data will be echoed to the browser instead by default. Use odbc_longreadlen to change this behavior.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// execute query
$Query = "SELECT name, price ";
$Query .= "FROM catalog ";
Core PHP Programming IT-SC book 416
$Result = odbc_do($Connection, $Query);
while(odbc_fetch_row($Result))
{
$name = odbc_result($Result, 1);
$price = odbc_result($Result, 2);
print("$name: $price<BR>n");
}
odbc_close($Connection);
?>

integer odbc_result_all(integer result, string format)
The odbc_result_all function will dump all the rows for a result set to the browser. The number of rows is returned. The dumped rows will be formatted in a table. The field names will be printed in a header row with TH tags. The optional format argument will be inserted inside the initial table tag so that you may set table attributes.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// execute query
$Query = "SELECT name, price ";
$Query .= "FROM catalog ";
$Result = odbc_do($Connection, $Query);
// dump all results
odbc_result_all($Result, "BORDER=1");
odbc_close($Connection);
?>

boolean odbc_rollback(integer connection)
Use odbc_rollback to abandon all pending transactions. By default all queries are automatically committed, but this behavior may be modified with odbc_autocommit. Not all databases support transactions.

<?
// connect to database
$Connection = odbc_connect("store", "guest", "guest");
// turn off autocommit
odbc_autocommit($Connection, FALSE);
Core PHP Programming IT-SC book 417
// put everything on sale
$Query = "UPDATE catalog ";
$Query .= "SET price = price * 0.9 ";
$Result = odbc_do($Connection, $Query);
// rollback
odbc_rollback($Connection);
odbc_close($Connection);
?>

integer odbc_setoption(integer id, integer function, integer option, integer parameter)
The odbc_setoption function changes the configuration of the ODBC driver for an entire connection or a single result set. Its purpose is to allow access to any ODBC setting in order to avoid problems with buggy ODBC drivers. To use this function, you ought to understand ODBC in greater detail than the average user. You will need to know the values of the various options available to you. The id argument is either a connection identifier or a result set identifier. Since odbc_setoption wraps two C API functions, SQLSetConnectOption and SQLSetStmtOption, you must specify which to use with the function argument. The option argument is an integer that identifies one of the many options available on the ODBC driver. The parameter argument is the value to use with the option.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

PHP Topics