[Knowledge] PDO knowledge

Auto: http://apps.hi.baidu.com/share/detail/50250843#content

Example 1654. Connecting to MySQL
<? PHP
$ DBH = new PDO ('mysql: host = localhost; dbname = test', $ user, $ pass );
?>   Example 1655. Handling connection errors
<? PHP
Try {
$ DBH = new PDO ('mysql: host = localhost; dbname = test', $ user, $ pass );
Foreach ($ DBH-> query ('select * From foo') as $ row ){
Print_r ($ row );
}
$ DBH = NULL;
} Catch (pdoexception $ e ){
Print "error! : ". $ E-> getmessage ()." <br/> ";
Die ();
}
?>   Example 1656. Closing a connection
<? PHP
$ DBH = new PDO ('mysql: host = localhost; dbname = test', $ user, $ pass );
// Use the connection here

// And now we're re done; close it
$ DBH = NULL;
?> Example 1657. Persistent connections
<? PHP
$ DBH = new PDO ('mysql: host = localhost; dbname = test', $ user, $ pass, array (
PDO: attr_persistent => true
));
?>   Example 1658. Executing a batch in a transaction
<? PHP
Try {
$ DBH = new PDO ('odbc: sample', 'db2inst1', 'ibmdb ',
Array (PDO: attr_persistent => true ));
Echo "connected \ n ";
$ DBH-> setattribute (PDO: attr_errmode, PDO: errmode_exception );
$ DBH-> begintransaction ();
$ DBH-> exec ("insert into staff (ID, first, last) values (23, 'job', 'bloggs ')");
$ DBH-> exec ("insert into salarychange (ID, amount, changedate)
Values (23,500 00, now ())");
$ DBH-> commit ();

} Catch (exception $ e ){
$ DBH-> rollback ();
Echo "failed:". $ e-> getmessage ();
}
?> Sample 1659. Repeated inserts using prepared statements
This example performs an insert query by substituting a name and a value for the named placeholders.
<? PHP
$ Stmt = $ DBH-> prepare ("insert into registry (name, value) values (: name,: Value )");
$ Stmt-> bindparam (': name', $ name );
$ Stmt-> bindparam (': value', $ value );
// Insert one row
$ Name = 'one ';
$ Value = 1;
$ Stmt-> execute ();
// Insert another row with different values
$ Name = 'two ';
$ Value = 2;
$ Stmt-> execute ();
?>     Sample 1660. Repeated inserts using prepared statements
This example performs an insert query by substituting a name and a value for the positional? Placeholders.
<? PHP
$ Stmt = $ DBH-> prepare ("insert into registry (name, value) values (?, ?) ");
$ Stmt-> bindparam (1, $ name );
$ Stmt-> bindparam (2, $ value );
// Insert one row
$ Name = 'one ';
$ Value = 1;
$ Stmt-> execute ();
// Insert another row with different values
$ Name = 'two ';
$ Value = 2;
$ Stmt-> execute ();
?>   Example 1661. Fetching data using prepared statements
This example fetches data based on a key value supplied by a form. The user input is automatically quoted, so there is no risk of a SQL injection attack.
<? PHP
$ Stmt = $ DBH-> prepare ("select * from registry where name =? ");
If ($ stmt-> execute (Array ($ _ Get ['name']) {
While ($ ROW = $ stmt-> fetch ()){
Print_r ($ row );
}
}
?>     Sample 1662. Calling a stored procedure with an output parameter
<? PHP
$ Stmt = $ DBH-> prepare ("Call sp_returns_string (?) ");
$ Stmt-> bindparam (1, $ return_value, PDO: param_str, 4000 );
// Call the Stored Procedure
$ Stmt-> execute ();
Print "procedure returned $ return_value \ n ";
?>   For example, 1663. Calling a stored procedure with an input/output parameter
<? PHP
$ Stmt = $ DBH-> prepare ("Call sp_takes_string_returns_string (?) ");
$ Value = 'hello ';
$ Stmt-& gt; bindparam (1, $ value, PDO: param_str | PDO: param_input_output, 4000 );
// Call the Stored Procedure
$ Stmt-> execute ();
Print "procedure returned $ value \ n ";
?>

Example 1664. Invalid use of placeholder
<? PHP
$ Stmt = $ DBH-> prepare ("select * from registry where name like '%? % '");
$ Stmt-> execute (Array ($ _ Get ['name']);
// Placeholder must be used in the place of the whole value
$ Stmt = $ DBH-> prepare ("select * from registry where name like? ");
$ Stmt-> execute (Array ("% $ _ Get [name] % "));
?>   Example 1665. displaying an image from a database
This example binds the LOB into the variable named $ lob and then sends it to the browser using fpassthru (). since the lob is represented as a stream, functions such as fgets (), fread () and stream_get_contents () can be used on it.
<? PHP
$ Db = new PDO ('odbc: sample', 'db2inst1', 'ibmdb ');
$ Stmt = $ db-> prepare ("select contenttype, imagedata from images where id =? ");
$ Stmt-> execute (Array ($ _ Get ['id']);
$ Stmt-> bindcolumn (1, $ type, PDO: param_str, 256 );
$ Stmt-> bindcolumn (2, $ lob, PDO: param_lob );
$ Stmt-> fetch (PDO: fetch_bound );
Header ("Content-Type: $ type ");
Fpassthru ($ LOB );
?>

For example, 1666. inserting an image into a database
This example opens up a file and passes the file handle to PDO to insert it as a lob. PDO will do its best to get the contents of the file up to the database in the most efficient manner possible.
<? PHP
$ Db = new PDO ('odbc: sample', 'db2inst1', 'ibmdb ');
$ Stmt = $ db-> prepare ("insert into images (ID, contenttype, imagedata) values (?, ?, ?) ");
$ Id = get_new_id (); // some function to allocate a new ID
// Assume that we are running as part of a file upload form
// You can find more information in the PHP documentation
$ Fp = fopen ($ _ FILES ['file'] ['tmp _ name'], 'rb ');
$ Stmt-> bindparam (1, $ id );
$ Stmt-> bindparam (2, $ _ FILES ['file'] ['type']);
$ Stmt-> bindparam (3, $ FP, PDO: param_lob );
$ Db-> begintransaction ();
$ Stmt-> execute ();
$ Db-> commit ();
?>

For example, 1667. inserting an image into a database: Oracle
Oracle requires a slightly different syntax for inserting a lob from a file. it's also essential that you perform the insert under a transaction, otherwise your newly inserted lob will be committed with a zero-length as part of the implicit commit that happens when the query is executed:
<? PHP
$ Db = new PDO ('oss: ', 'Scott', 'tiger ');
$ Stmt = $ db-> prepare ("insert into images (ID, contenttype, imagedata )".
"Values (?, ?, Empty_blob () Returning imagedata? ");
$ Id = get_new_id (); // some function to allocate a new ID
// Assume that we are running as part of a file upload form
// You can find more information in the PHP documentation
$ Fp = fopen ($ _ FILES ['file'] ['tmp _ name'], 'rb ');
$ Stmt-> bindparam (1, $ id );
$ Stmt-> bindparam (2, $ _ FILES ['file'] ['type']);
$ Stmt-> bindparam (3, $ FP, PDO: param_lob );
$ Stmt-> begintransaction ();
$ Stmt-> execute ();
$ Stmt-> commit ();
?>   Example 1668. The pdoexception class
<? PHP
Class pdoexception extends exception
{
Public $ errorinfo = NULL; // corresponds to PDO: errorinfo ()
// Or pdostatement: errorinfo ()
Protected $ message; // textual error message
// Use exception: getmessage () to access it
Protected $ code; // sqlstate error code
// Use exception: getcode () to access it
}
?> Example 1669. Using PDO: attr_driver_name
<? PHP
If ($ db-> getattribute (PDO: attr_driver_name) = 'mysql '){
Echo "running on MySQL; doing something MySQL specific here \ n ";
}
?> Additional information: Http://www.cnblogs.com/aspxphpjsprb/archive/2010/05/01/1725467.html