Transcript ODBC DSN-less Connections

Chapter 7
PHP
Interacts
with Ms. Access
(Open DataBase Connectivity (ODBC))
Introduction
PHP provides support for Open DataBase Connectivity
(ODBC) which makes it possible to access any ODBC
compliant database management system (DBMS) as long as
a Data Source Name (DSN) is available on your system or
network or a DSN-less connection string is available.
This includes access to relational databases such as Oracle,
DB2, MS SQL Server, MySQL, and MS Access.
Since PHP includes DSN-less functions for accessing MySQL
databases that do not require ODBC, this section will use MS
Access to demonstrate ODBC methods.
ODBC DSN Connections
To connect to our database using ODBC we must first create a system
data source name.
Here is how to create an ODBC connection to a MS Access Database:
1.
2.
3.
4.
5.
6.
7.
8.
Open the Administrative Tools icon in your Control Panel.
Double-click on the Data Sources (ODBC) icon inside.
Choose the System DSN tab.
Click on Add in the System DSN tab.
Select the Microsoft Access Driver. Click Finish.
In the next screen, click Select to locate the database.
Give the database a Data Source Name (DSN).
Click OK.
The DSN configuration has to be setup on the computer where your web
site is located. If you are running your site on a remote server, you must
setup the configuration on the server.
After the ODBC Connection is establish, you can use PHP odbc functions
to connect to your database and retrieve records. The PHP odbc functions
are described in the next slide:
ODBC DSN-less Connections
DSN less connections don't require creation of system level
DSNs for connecting to databases and provide an alternative
to DSNs.
Instead of using a DSN to connect to a database, the
developer specifies the necessary information right in the
application.
With a DSN-less connection the developer is free to use
connection standards other than ODBC, such as OLE DB.
DSN-less connections should be used when you do not have
access to the server to register DSNs yourself.
For Microsoft Access, the following connection string is used
to create DSN-less connections:
Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\path\to\database.mdb
ODBC Functions
odbc_connect(dsn/dsn-less connection string,username,password)
function used to connect to an ODBC datasource. The function takes four
parameters: the data source name or a dsn-less connection string,
username, password, and an optional cursor type. In cases where a
username and password, and cursor type are not required, the
parameters can be replaced with a null string - ''. The connection id
returned by this functions is needed by other ODBC functions. You can
have multiple connections open at once as long as they either use
different db or different credentials.
odbc_exec(connection_id,SQL query_string)
function used to execute an SQL statement. The function takes two
parameters: a connection object created using the odbc_connect()
function and a SQL statement. Returns FALSE on error. Returns a record
set if the SQL command was executed successfully.
ODCB Functions
odbc_fetch_array(recordset name)
used to retrieve records or rows from a record set as an
associative array. The recordset name is created when the
odbc_exec() function is called. This function returns TRUE if it
is able to return rows, otherwise FALSE.
odbc_num_rows(recordset name)
returns the number of rows in an ODBC result set. The
function will return -1 if an error occurs. For INSERT,
UPDATE and DELETE statements odbc_num_rows() returns
the number of rows affected. For a SELECT clause this can
be the number of rows available. Note: Using
odbc_num_rows() to determine the number of rows available
after a SELECT will return -1 with MS Access drivers.
odbc_close(connection_id)
Function will close down the connection to the database
server associated with the given connection identifier.
The end of Chapter 7
Thanks for your paying attention