ADODB menggunakan php


berikut ini adalah contoh mengkoneksikan php dengan database mysql menggunakan ADODB

include "adodb.inc.php";
$conn = adodb.NewADOConnection('mysql');
$conn->Connect('server','user','pwd','db');
$rs = $conn->Execute('select * from table');

while (!$rs->EOF) {
print_r($rs->fields);
$rs->MoveNext();
}
$rs->Close();
$conn->Close();

include "adodb.inc.php";
ini digunakan untuk mengambil library adodb yang berisi objek dalam penggunaan odbc



$conn = adodb.NewADOConnection('mysql');
digunakan untuk menciptakan koneksi ke database mysql dengan menggunakan odbc

$conn->Connect('server','user','pwd','db');
melakukan login ke dalam database mysql

$rs = $conn->Execute('select * from table');
mengkompile atau mengeksekusi database

while (!$rs->EOF) {
print_r($rs->fields);
$rs->MoveNext();
}

menampilkan data sampai nilai EOF tidak sama dengan nol, fungsi MoveNext() untuk meletakkan kursor pada rekord berikutnya

$rs->Close();
$conn->Close();

menutup koneksi dan eksekusi ke database

contoh koneksi php ke database yang lainnnya menggunakan ODBC

# Oracle connection
import adodb
conn = adodb.NewADOConnection('oci8')
conn.Connect('scott/tiger@tns')
conn.Connect('tns', 'scott', 'tiger')

# Oracle using connection string
import adodb
conn = adodb.NewADOConnection('oci8://scott:tiger@tns/')

# MySQL
import adodb
conn = adodb.NewADOConnection('mysql')
conn.Connect('server', 'user', 'pwd', 'db')

# MySQL using connection string
import adodb
conn = adodb.NewADOConnection('mysql://user:pwd@server/db')

# PostgreSQL
import adodb
conn = adodb.NewADOConnection('postgres')
conn.Connect('server', 'user', 'pwd', 'db')
conn.Connect('host=server user=user password=pwd dbname=db port=4341')

# ODBC
import adodb
conn = adodb.NewADOConnection('access') # mxodbc required
dsn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\\inetpub\\adodb\\northwind.mdb;"
conn.Connect(dsn)

# ODBC for mssql
import adodb
conn = adodb.NewADOConnection('mssql') # mxodbc required
conn.Connect("Driver={SQL Server};Server=localhost;Database=northwind;")

# sqlite
import adodb
conn = adodb.NewADOConnection('sqlite') # pysqlite required
conn.Connect(database = "c:\\sqlite\\mydata.db")

FUNGSI YANG ADA PADA ODBC

Connection class
Execute(sql, [params])
Execute sql, returning a cursor object. The optional params is a dictionary that contains the bind variables. All blob fields are automatically and transparently retrieved for you.

SelectLimit(sql, limit, [offset])
Execute sql, retrieving only limit rows, an optional offset from the beginning of the recordset, returning a cursor object.

UpdateBlob(table, field, blob,whereclause, blobtype='BLOB')
Executes the equivalent following sql statement:
UPDATE table SET field = blob WHERE whereclause
The blobtype field should be set to either 'BLOB' or 'CLOB'. Any special encoding required for the blob is applied transparently.

UpdateBlobFile(table, field, filepath,whereclause, blobtype='BLOB')
Loads the binary file filepath into blob. Then calls UpdateBlob( ).

ErrorMsg( )
Returns last database error message. This function is not thread-safe.

IsConnected( )
Returns boolean indicating whether connected.

qstr(str)
Quotes a varchar or char variable.

quote(str)
Synonym for qstr( )

GetAll(sql)
Execute sql and return 2-dimensional array of tuples, the data recordset.

GetArray(sql)
Synonym for GetAll(sql).

GetRow(sql)
Execute sql and return first row of recordset as a tuple.

GetOne(sql)
Execute sql and return 1 element of first row of recordset.

GetAssoc(sql)
Returns a dictionary, with the first columns as the keys to the dictionary. If more than 2 columns are returned, then the dictionary values is a tuple of the 2nd to last columns. If 2 columns are returned, then the 2nd column becomes the dictionary values. If one column is returned, then the values are set to None.

GetDict(sql)
Synonym for GetAssoc().

GetCol(sql)
Returns the first column of each row as an array.

MetaType(fieldtype)
Returns the ADOdb metatype of a native field type.
* C: character fields that fit into a text input field.
* X: larger character fields that fit into a textarea.
* B: Blobs, or Binary Large Objects. Typically images.
* D: Date field
* T: Timestamp field
* L: Logical field (boolean or bit-field)
* I: Integer field
* N: Numeric field. Includes autoincrement, numeric, floating point, real and integer.

MetaColumns(table)
Returns a 2-dimensional array containing information on the fields of a table. Each row contains [fieldname, fieldtype, maxlength]. Maxlength can be -1, indicating that the maximum length is not known.
Note that some databases return more information in each row.

BeginTrans( )
ADOdb defaults to auto-commit mode. Call BeginTrans( ) to start a transaction. This might not be thread-safe.

RollbackTrans( )
Rollback transaction initiated by BeginTrans( ).

CommitTrans( )
Commit transaction initiated by BeginTrans( ).

Close( )
Close database connection. This is optional, as the connection is closed when the object is freed.

Module( )
Returns the DB module object.

Conn( )
Returns DB connection object.

DriverInfo( )
Returns the threadsafety, apilevel and paramstyle values

Cursor Class

RecordCount( )
Number of rows returned by SELECT statement, or number of rows affected by INSERT/UPDATE/DELETE. Returns -1 if not supported.

Affected_Rows( )
Synonym for RecordCount( ).

MoveNext( )
Move to next row of recordset. Returns current EOF value.

FetchRow( )
Retrieves the current row of the recordset, then moves to the next row. The row retrieved is returned as a tuple.

GetRowAssoc(upper=1)
Returns the current row as a dictionary, with the key's being the field names. Setting upper = 0 will lower-case the keys. Setting upper=1 will upper-case the keys. Setting upper to any other value, and the keys will be left in the natural case.

Insert_ID( )
Returns last insert id generated by an auto-incrementing field. Only supported by mysql and odbc_mssql drivers currently.

FetchField(fieldoffset)
Returns field information from a SELECT statement. The fieldoffset is zero-based, so to retrieve info on the 1st field use FetchField(0). A tuple is returned, consisting of:
(name, type_code,display_size, internal_size, precision, scale,null_ok).

Close( )
Close cursor. This is optional, as the cursor is closed when the object is freed.

Cursor( )
Returns DB cursor object.

contoh kode untuk menangani error

try:
curs = conn.Execute('select * from badtable'); # table does not exist
except:
print sys.exc_info()[1]; # retrieve the error message returned by database

Alternatively, you can use PHP style ErrorMsg( ) by setting the connection.useExceptions flag to True. ErrorMsg( ) is not thread-safe.

conn.useExceptions = False
curs = conn.Execute('select * from badtable'); # table does not exist
if curs == None:
print conn.ErrorMsg()