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
Next revisionBoth sides next revision
reference:portable_sql [2015/12/04 18:02] – mnewnhamv5:reference:portable_sql [2016/01/13 02:31] – ↷ Page moved from reference:portable_sql to v5:reference:portable_sql mnewnham
Line 134: Line 134:
 ADOdb provides the following functions for portably generating SQL functions as strings to be merged into your SQL statements. ADOdb provides the following functions for portably generating SQL functions as strings to be merged into your SQL statements.
  
-Function Description +^Function^Description^ 
-DBDate($date) Pass in a UNIX timestamp or ISO date and it will convert it to a date string formatted for INSERT/UPDATE +^DBDate($date)|Pass in a UNIX timestamp or ISO date and it will convert it to a date string formatted for INSERT/UPDATE| 
-DBTimeStamp($date) Pass in a UNIX timestamp or ISO date and it will convert it to a timestamp string formatted for INSERT/UPDATE +^DBTimeStamp($date)|Pass in a UNIX timestamp or ISO date and it will convert it to a timestamp string formatted for INSERT/UPDATE| 
-SQLDate($date, $fmt) Portably generate a date formatted using $fmt mask, for use in SELECT statements. +^SQLDate($date, $fmt)|Portably generate a date formatted using $fmt mask, for use in SELECT statements.| 
-OffsetDate($date, $ndays) Portably generate a $date offset by $ndays. +^OffsetDate($date, $ndays)|Portably generate a $date offset by $ndays.| 
-Concat($s1, $s2, ...) Portably concatenate strings. Alternatively, for mssql use mssqlpo driver, which allows || operator. +^Concat($s1, $s2, ...)|Portably concatenate strings. Alternatively, for mssql use mssqlpo driver, which allows ''||'' operator.| 
-IfNull($fld, $replaceNull) Returns a string that is the equivalent of MySQL IFNULL or Oracle NVL. +^IfNull($fld, $replaceNull)|Returns a string that is the equivalent of MySQL IFNULL or Oracle NVL.| 
-Param($name) Generates bind placeholders, using ? or named conventions as appropriate. +^Param($name)|Generates bind placeholders, using ? or named conventions as appropriate.| 
-$db->sysDate Property that holds the SQL function that returns today's date +^$db->sysDate|Property that holds the SQL function that returns today's date| 
-$db->sysTimeStamp Property that holds the SQL function that returns the current timestamp (date+time). +^$db->sysTimeStamp|Property that holds the SQL function that returns the current timestamp (date+time).| 
-$db->concat_operator Property that holds the concatenation operator +^$db->concat_operator|Property that holds the concatenation operator| 
-$db->length Property that holds the name of the SQL strlen function. +^$db->length|Property that holds the name of the SQL strlen function.| 
-$db->upperCase Property that holds the name of the SQL strtoupper function. +^$db->upperCase|Property that holds the name of the SQL strtoupper function.| 
-$db->random Property that holds the SQL to generate a random number between 0.00 and 1.00. +^$db->random|Property that holds the SQL to generate a random number between 0.00 and 1.00.| 
-$db->substr Property that holds the name of the SQL substring function.+^$db->substr|Property that holds the name of the SQL substring function.|
    
  
-DDL and Tuning+===== DDL and Tuning =====
  
-There are database design tools such as ERWin or Dezign that allow you to generate data definition language commands such as ALTER TABLE or CREATE INDEX from Entity-Relationship diagrams. +There are database design tools such as ERWin or Dezign that allow you to generate data definition language commands such as ''ALTER TABLE'' or ''CREATE INDEX'' from Entity-Relationship diagrams. 
-However if you prefer to use a PHP-based table creation scheme, adodb provides you with this feature. Here is the code to generate the SQL to create a table with:+However if you prefer to use a PHP-based table creation scheme, ADOdb provides you with this feature. Here is the code to generate the SQL to create a table with:
  
-Auto-increment primary key 'ID', +  Auto-increment primary key 'ID', 
-The person's 'NAME' VARCHAR(32) NOT NULL and defaults to '', +  The person's 'NAME' VARCHAR(32) NOT NULL and defaults to '', 
-The date and time of record creation 'CREATED', +  The date and time of record creation 'CREATED', 
-The person's 'AGE', defaulting to 0, type NUMERIC(16). +  The person's 'AGE', defaulting to 0, type NUMERIC(16). 
-Also create a compound index consisting of 'NAME' and 'AGE':+  Also create a compound index consisting of 'NAME' and 'AGE':
  
 +<code php>
 $datadict = NewDataDictionary($connection); $datadict = NewDataDictionary($connection);
 $flds = " $flds = "
Line 171: Line 172:
 $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');
-Data Types+ 
 +</code> 
 + 
 +===== Data Types =====
  
 Stick to a few data types that are available in most databases. Char, varchar and numeric/number are supported by most databases. Most other data types (including integer, boolean and float) cannot be relied on being available. I recommend using char(1) or number(1) to hold booleans. Stick to a few data types that are available in most databases. Char, varchar and numeric/number are supported by most databases. Most other data types (including integer, boolean and float) cannot be relied on being available. I recommend using char(1) or number(1) to hold booleans.
Line 177: Line 181:
 Different databases have different ways of representing dates and timestamps/datetime. ADOdb attempts to display all dates in ISO (YYYY-MM-DD) format. ADOdb also provides DBDate( ) and DBTimeStamp( ) to convert dates to formats that are acceptable to that database. Both functions accept Unix integer timestamps and date strings in ISO format. Different databases have different ways of representing dates and timestamps/datetime. ADOdb attempts to display all dates in ISO (YYYY-MM-DD) format. ADOdb also provides DBDate( ) and DBTimeStamp( ) to convert dates to formats that are acceptable to that database. Both functions accept Unix integer timestamps and date strings in ISO format.
  
 +<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>
 We also provide functions to convert database dates to Unix timestamps: We also provide functions to convert database dates to Unix timestamps:
  
 +<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
-For date calculations, we have 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>
  
 +For date calculations, we have [[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:
 +
 +<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 ...';
-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, UpdateBlob( ) and UpdateClob( ) that allow you to update fields holding Binary Large Objects (eg. pictures) and Character Large Objects (eg. HTML articles):+</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, [[reference:UpdateBlob()]] and [[reference:UpdateClob()]] that allow you to update fields holding Binary Large Objects (eg. pictures) and Character Large Objects (eg. HTML articles):
  
 +<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())');
Line 194: Line 207:
 $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>
 Null handling is another area where differences can occur. This is a mine-field, because 3-value logic is tricky. Null handling is another area where differences can occur. This is a mine-field, because 3-value logic is tricky.
  
 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 IfNull function, so you can define what to display if the field contains a null.+ADOdb also supports a portable [[reference:ifNull()]] function, so you can define what to display if the field contains a null.
  
-Stored Procedures+===== Stored Procedures =====
  
 Stored procedures are another problem area. Some databases allow recordsets to be returned in a stored procedure (Microsoft SQL Server and Sybase), and others only allow output parameters to be returned. Stored procedures sometimes need to be wrapped in special syntax. For example, Oracle requires such code to be wrapped in an anonymous block with BEGIN and END. Also internal sql operators and functions such as +, ||, TRIM( ), SUBSTR( ) or INSTR( ) vary between vendors. Stored procedures are another problem area. Some databases allow recordsets to be returned in a stored procedure (Microsoft SQL Server and Sybase), and others only allow output parameters to be returned. Stored procedures sometimes need to be wrapped in special syntax. For example, Oracle requires such code to be wrapped in an anonymous block with BEGIN and END. Also internal sql operators and functions such as +, ||, TRIM( ), SUBSTR( ) or INSTR( ) vary between vendors.
  
 An example of how to call a stored procedure with 2 parameters and 1 return value follows: An example of how to call a stored procedure with 2 parameters and 1 return value follows:
 +<code php>
 +switch ($db->databaseType)
 +{
 +case 'mssql':
 +    $sql = 'SP_RUNSOMETHING'; 
 +    break;
 +case 'oci8':
 +    $sql = "declare RETVAL integer;begin :RETVAL := SP_RUNSOMETHING(:myid,:group);end;";
 +    break;
 +default:
 +  die('Unsupported feature');
 +}
 +# @RETVAL = SP_RUNSOMETHING @myid,@group
 +$stmt = $db->PrepareSP($sql);
 +$db->inParameter($stmt,$id,'myid');
 +$db->Parameter($stmt,$group,'group');
 +# true indicates output parameter
 +$db->Parameter($stmt,$ret,'RETVAL',true);
 +$db->Execute($stmt); 
 +</code>
  
- switch ($db->databaseType) { 
- case 'mssql': 
-   $sql = 'SP_RUNSOMETHING'; break; 
- case 'oci8': 
-   $sql = 
-   "declare RETVAL integer;begin :RETVAL := SP_RUNSOMETHING(:myid,:group);end;"; 
-   break; 
- default: 
-   die('Unsupported feature'); 
- } 
- # @RETVAL = SP_RUNSOMETHING @myid,@group 
- $stmt = $db->PrepareSP($sql);  
- $db->Parameter($stmt,$id,'myid'); 
- $db->Parameter($stmt,$group,'group'); 
- # true indicates output parameter 
- $db->Parameter($stmt,$ret,'RETVAL',true); 
- $db->Execute($stmt);  
 As you can see, the ADOdb API is the same for both databases. But the stored procedure SQL syntax is quite different between databases and is not portable, so be forewarned! However sometimes you have little choice as some systems only allow data to be accessed via stored procedures. This is when the ultimate portability solution might be the only solution: treating portable SQL as a localization exercise... As you can see, the ADOdb API is the same for both databases. But the stored procedure SQL syntax is quite different between databases and is not portable, so be forewarned! However sometimes you have little choice as some systems only allow data to be accessed via stored procedures. This is when the ultimate portability solution might be the only solution: treating portable SQL as a localization exercise...
  
-SQL as a Localization Exercise+===== SQL as a Localization Exercise =====
  
 In general to provide real portability, you will have to treat SQL coding as a localization exercise. In PHP, it has become common to define separate language files for English, Russian, Korean, etc. Similarly, I would suggest you have separate Sybase, Intebase, MySQL, etc files, and conditionally include the SQL based on the database. For example, each MySQL SQL statement would be stored in a separate variable, in a file called 'mysql-lang.inc.php'. In general to provide real portability, you will have to treat SQL coding as a localization exercise. In PHP, it has become common to define separate language files for English, Russian, Korean, etc. Similarly, I would suggest you have separate Sybase, Intebase, MySQL, etc files, and conditionally include the SQL based on the database. For example, each MySQL SQL statement would be stored in a separate variable, in a file called 'mysql-lang.inc.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 = quot;SELECT * FROM articles WHERE match (title,body) against (%s)";
 +
 +</code>
 In our main PHP file: In our main PHP file:
  
 +<code php>
 # define which database to load... # define which database to load...
 $database = 'mysql'; $database = 'mysql';
Line 242: Line 264:
 # 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)));
-Note that we quote the $word variable using the qstr( ) function. This is because each database quotes strings using different conventions. 
  
-Final Thoughts+</code>
  
-The best way to ensure that you have portable SQL is to have your data tables designed using sound principles. Learn the theory of normalization and entity-relationship diagrams and model your data carefully. Understand how joins and indexes work and how they are used to tune performance.+Note that we quote the $word variable using the  [[reference:qstr()]] function. This is because each database quotes strings using different conventions.
  
-Visit the following page for more references on database theory and vendors: http://php.weblogs.com/sql_tutorial. Also read this article on Optimizing PHP.+===== Final Thoughts ===== 
 + 
 +The best way to ensure that you have portable SQL is to have your data tables designed using sound principles. Learn the theory of normalization and entity-relationship diagrams and model your data carefully. Understand how joins and indexes work and how they are used to tune performance.
  
 (c) 2002-2014 John Lim. (c) 2002-2014 John Lim.
v5/userguide/portable_sql.txt · Last modified: 2020/12/30 21:32 by peterdd