ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools

Trace:
v5:userguide:portable_sql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
v5:userguide:portable_sql [2016/01/13 16:23] – ↷ Page moved from v5:reference:portable_sql to v5:userguide:portable_sql mnewnhamv5:userguide:portable_sql [2020/12/30 21:32] (current) – [Prepare/Execute] fix obvious variable case typos peterdd
Line 26: Line 26:
 ^Oracle|select * from (select * from table) where rownum <= 10| ^Oracle|select * from (select * from table) where rownum <= 10|
  
-This feature of getting a subset of data is so useful that in the PHP class library ADOdb, we have a [[v5:reference:selectlimit]] function that allows you to hide the implementation details within a function that will rewrite your SQL for you:+This feature of getting a subset of data is so useful that in the PHP class library ADOdb, we have a [[v5:reference:connection:selectlimit|selectLimit()]] function that allows you to hide the implementation details within a function that will rewrite your SQL for you:
  
 <code php> <code php>
-$connection->SelectLimit('select * from table', 10);+$connection->selectLimit('select * from table', 10);
 </code> </code>
  
 ==== Fetch Modes Within Select Statements ==== ==== Fetch Modes Within Select Statements ====
  
-PHP allows you to retrieve database records as arrays. You can choose to have the arrays indexed by field name or number. However different low-level PHP database drivers are inconsistent in their indexing efforts. ADOdb allows you to determine your prefered mode. You set this by setting the variable [[v5:reference:adodb_fetch_mode]] to either of the constants:+PHP allows you to retrieve database records as arrays. You can choose to have the arrays indexed by field name or number. However different low-level PHP database drivers are inconsistent in their indexing efforts. ADOdb allows you to determine your prefered mode. You set this by setting the variable [[v5:reference:adodb_fetch_mode|$ADODB_FETCH_MODE]] to either of the constants:
   * ADODB_FETCH_NUM (for numeric indexes)   * ADODB_FETCH_NUM (for numeric indexes)
   * ADODB_FETCH_ASSOC (using field names as an associative index).   * ADODB_FETCH_ASSOC (using field names as an associative index).
Line 44: Line 44:
 Another problem with SELECTs is that some databases do not return the number of rows retrieved from a select statement. This is because the highest performance databases will return records to you even before the last record has been found. Another problem with SELECTs is that some databases do not return the number of rows retrieved from a select statement. This is because the highest performance databases will return records to you even before the last record has been found.
  
-In ADOdb, [[v5:reference:recordcount]] returns the number of rows returned, or will emulate it by buffering the rows and returning the count after all rows have been returned. This can be disabled for performance reasons when retrieving large recordsets by setting the global variable [[v5:reference:adodb_countrecs]] = false. This variable is checked every time a query is executed, so you can selectively choose which recordsets to count.+In ADOdb, [[v5:reference:connection:recordcount|recordCount()]] returns the number of rows returned, or will emulate it by buffering the rows and returning the count after all rows have been returned. This can be disabled for performance reasons when retrieving large recordsets by setting the global variable [[v5:reference:adodb_countrecs|$ADODB_COUNTRECS]] = false. This variable is checked every time a query is executed, so you can selectively choose which recordsets to count.
  
-If you prefer to set ''$ADODB_COUNTRECS'' = false, ADOdb still has the [[v5:reference:po_recordcount]] function. This will return the number of rows, or if it is not found, it will return an estimate using ''SELECT COUNT(*)'':+If you prefer to set ''$ADODB_COUNTRECS'' = false, ADOdb still has the [[v5:reference:connection:po_recordcount|po_recordCount()]] function. This will return the number of rows, or if it is not found, it will return an estimate using ''SELECT COUNT(*)'':
  
 <code php> <code php>
-$rs = $db->Execute("select * from table where state=$state"); +$rs = $db->execute("select * from table where state=$state"); 
-$numrows = $rs->PO_RecordCount('table', "state=$state");+$numrows = $rs->po_recordCount('table', "state=$state");
 </code> </code>
  
Line 60: Line 60:
  
 <code php> <code php>
-$connection->BeginTrans( ); +$connection->beginTrans( ); 
-$connection->RowLock($table, $where); +$connection->rowLock($table, $where); 
 # some operation # some operation
-if ($ok) $connection->CommitTrans( ); +if ($ok) $connection->commitTrans( ); 
-else $connection->RollbackTrans( );+else $connection->rollbackTrans( );
 </code> </code>
  
Line 98: Line 98:
 ===== Inserting Records ===== ===== Inserting Records =====
  
-When you create records, you need to generate unique id's for each record. There are two common techniques: (1) auto-incrementing columns and (2) sequences.+When you create records, you need to generate unique id's for each record. There are two common techniques:  
 +  * Auto-increment columns 
 +  * Sequences
  
-Auto-incrementing columns are not supported by some databases, so for portability, you have little choice but to use sequences. Sequences are special functions that return a unique incrementing number every time you call it, suitable to be used as database keys. In ADOdb, we use the [[v5:reference:genid]] function. It has takes a parameter, the sequence name. Different tables can have different sequences.+Auto-increment columns are not supported by some databases, so for portability, you have little choice but to use sequences. Sequences are special functions that return a unique incrementing number every time you call it, suitable to be used as database keys. In ADOdb, we use the [[v5:reference:connection:genid|genId()]] function. It has takes a parameter, the sequence name.  
 + 
 +Different tables can have different sequences.
  
 <code php> <code php>
-$id = $connection->GenID('sequence_name'); +$id = $connection->genID('sequence_name'); 
-$connection->Execute("insert into table (id, firstname, lastname)  +$connection->execute("insert into table (id, firstname, lastname)  
-    values ($id, $firstname, $lastname)");+          values ($id, $firstname, $lastname)");
  
 </code> </code>
-For databases that do not support sequences natively, ADOdb emulates sequences by creating a table for every sequence.+For databases that do not provide native support for sequence, ADOdb provides emulation by creating a table for every sequence.
  
 ===== Binding ===== ===== Binding =====
Line 116: Line 120:
 ===== Prepare/Execute ===== ===== Prepare/Execute =====
 <code php> <code php>
-$stmt = $db->Prepare('select * from customers where custid=? and state=?'); +$stmt = $db->prepare('select * from customers where custid=? and state=?'); 
-$rs = $db->Execute($stmt, array($id,'New York'));+$rs = $db->execute($stmt, array($id,'New York'));
 /* /*
 Oracle uses named bind placeholders, not "?", so to support portable binding, we have Param() that generates the correct placeholder Oracle uses named bind placeholders, not "?", so to support portable binding, we have Param() that generates the correct placeholder
 */ */
  
-$sql = 'insert into table (col1,col2) values ('.$DB->Param('a').','.$DB->Param('b').')';+$sql = 'insert into table (col1,col2) values ('.$db->param('a').','.$db->param('b').')';
 # generates 'insert into table (col1,col2) values (?,?)' # generates 'insert into table (col1,col2) values (?,?)'
 # or        'insert into table (col1,col2) values (:a,:b)' # or        'insert into table (col1,col2) values (:a,:b)'
-$stmt = $DB->Prepare($sql); +$stmt = $db->prepare($sql); 
-$stmt = $DB->Execute($stmt,array('one','two'));+$stmt = $db->execute($stmt, array('one','two'));
  
 </code> </code>
Line 170: Line 174:
   AGE N(16) DEFAULT 0   AGE N(16) DEFAULT 0
 "; ";
-$sql1 = $datadict->CreateTableSQL('tabname', $flds); +$sql1 = $datadict->createTableSQL('tabname', $flds); 
-$sql2 = $datadict->CreateIndexSQL('idx_name_age', 'tabname', 'NAME,AGE');+$sql2 = $datadict->createIndexSQL('idx_name_age', 'tabname', 'NAME,AGE');
  
 </code> </code>
Line 182: Line 186:
  
 <code php> <code php>
-$date1 = $connection->DBDate(time( )); +$date1 = $connection->dbDate(time( )); 
-$date2 = $connection->DBTimeStamp('2002-02-23 13:03:33');+$date2 = $connection->dbTimeStamp('2002-02-23 13:03:33');
 </code> </code>
 We also provide functions to convert database dates to Unix timestamps: We also provide functions to convert database dates to Unix timestamps:
  
 <code php> <code php>
-$unixts = $recordset->UnixDate('#2002-02-30#'); # MS Access date =gt; unix timestamp+$unixts = $recordset->unixDate('#2002-02-30#'); # MS Access date =gt; unix timestamp
 </code> </code>
  
-For date calculations, we have [[v5:reference:offsetdate]] which allows you to calculate dates such as yesterday and next week in a RDBMS independant fashion. For example, if we want to set a field to 6 hour from now, use:+For date calculations, we have [[v5:reference:connection:offsetdate|offsetDate()]] which allows you to calculate dates such as yesterday and next week in a RDBMS independant fashion. For example, if we want to set a field to 6 hour from now, use:
  
 <code php> <code php>
-$sql = 'update table set dtimefld='.$db->OffsetDate($db->sysTimeStamp, 6/24).' where ...';+$sql = 'update table set dtimefld='.$db->offsetDate($db->sysTimeStamp, 6/24).' where ...';
 </code> </code>
  
-The maximum length of a char/varchar field is also database specific. You can only assume that field lengths of up to 250 characters are supported. This is normally impractical for web based forum or content management systems. You will need to be familiar with how databases handle large objects (LOBs). ADOdb implements two functions, [[v5:reference:updateblob]] and [[v5:reference:updateclob]] that allow you to update fields holding Binary Large Objects (eg. pictures) and Character Large Objects (eg. HTML articles):+The maximum length of a char/varchar field is also database specific. You can only assume that field lengths of up to 250 characters are supported. This is normally impractical for web based forum or content management systems. You will need to be familiar with how databases handle large objects (LOBs). ADOdb implements two functions, [[v5:reference:connection:updateblob|updateBlob()]] and [[v5:reference:connection:updateclob|updateClob()]] that allow you to update fields holding Binary Large Objects (eg. pictures) and Character Large Objects (eg. HTML articles):
  
 <code php> <code php>
 # for oracle  # for oracle 
-$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1,empty_blob())'); +$conn->execute('INSERT INTO blobtable (id, blobcol) VALUES (1,empty_blob())'); 
-$conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1');+$conn->updateBlob('blobtable','blobcol',$blobvalue,'id=1');
  
 # non-oracle databases # non-oracle databases
-$conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); +$conn->execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); 
-$conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1');+$conn->updateBlob('blobtable','blobcol',$blobvalue,'id=1');
  
 </code> </code>
Line 213: Line 217:
 In general, I avoid using nulls except for dates and default all my numeric and character fields to 0 or the empty string. This maintains consistency with PHP, where empty strings and zero are treated as equivalent, and avoids SQL ambiguities when you use the ANY and EXISTS operators. However if your database has significant amounts of missing or unknown data, using nulls might be a good idea. In general, I avoid using nulls except for dates and default all my numeric and character fields to 0 or the empty string. This maintains consistency with PHP, where empty strings and zero are treated as equivalent, and avoids SQL ambiguities when you use the ANY and EXISTS operators. However if your database has significant amounts of missing or unknown data, using nulls might be a good idea.
  
-ADOdb also supports a portable [[v5:reference:ifnull]] function, so you can define what to display if the field contains a null.+ADOdb also supports a portable [[v5:reference:connection:ifnull|ifNull()]] function, so you can define what to display if the field contains a null.
  
 ===== Stored Procedures ===== ===== Stored Procedures =====
Line 233: Line 237:
 } }
 # @RETVAL = SP_RUNSOMETHING @myid,@group # @RETVAL = SP_RUNSOMETHING @myid,@group
-$stmt = $db->PrepareSP($sql); +$stmt = $db->prepareSP($sql);
 $db->inParameter($stmt,$id,'myid'); $db->inParameter($stmt,$id,'myid');
-$db->Parameter($stmt,$group,'group');+$db->parameter($stmt,$group,'group');
 # true indicates output parameter # true indicates output parameter
-$db->Parameter($stmt,$ret,'RETVAL',true); +$db->parameter($stmt,$ret,'RETVAL',true); 
-$db->Execute($stmt); +$db->execute($stmt); 
 </code> </code>
  
Line 249: Line 253:
 <code php> <code php>
 $sqlGetPassword = 'select password from users where userid=%s'; $sqlGetPassword = 'select password from users where userid=%s';
-$sqlSearchKeyword = quot;SELECT * FROM articles WHERE match (title,body) against (%s)";+$sqlSearchKeyword = "SELECT * FROM articles WHERE match (title,body) against (%s)";
  
 </code> </code>
Line 259: Line 263:
 include_once("$database-lang.inc.php"); include_once("$database-lang.inc.php");
  
-$db = NewADOConnection($database); +$db = newADOConnection($database); 
-$db->PConnect(...) or die('Failed to connect to database');+$db->pConnect(...) or die('Failed to connect to database');
  
 # search for a keyword $word # search for a keyword $word
-$rs = $db->Execute(sprintf($sqlSearchKeyWord,$db->qstr($word)));+$rs = $db->execute(sprintf($sqlSearchKeyWord,$db->qstr($word)));
  
 </code> </code>
  
-Note that we quote the $word variable using the  [[v5:reference:qstr]] function. This is because each database quotes strings using different conventions.+Note that we quote the $word variable using the  [[v5:reference:connection:qstr|qStr()]] function. This is because each database quotes strings using different conventions.
  
 ===== Final Thoughts ===== ===== Final Thoughts =====
v5/userguide/portable_sql.1452698627.txt.gz · Last modified: 2017/04/21 11:39 (external edit)