ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools


v5:database:text

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
v5:database:text [2015/10/14 16:48]
mnewnham
v5:database:text [2020/01/13 13:17] (current)
dregad fix invalid tag specification
Line 4: Line 4:
 </​WRAP> ​ </​WRAP> ​
 ===== Description ===== ===== Description =====
-The ''​text''​ driver allows an array of data to be processed like a read-only database table. The connect statement provides the data, as well as information about columns and names. No meta functions are supported. The *WHERE* SQL statement is ignored. In order to filter requests, An eval'd expression must be passed as a third argument. ​+The ''​text''​ driver allows an array of data to be processed like a read-only database table. The connect statement provides the data, as well as information about columns and names. No meta functions are supported. The *WHERE* SQL statement is ignored. In order to filter requests, An eval'd expression must be passed as a third argument.
 ------------------- -------------------
 <WRAP right box 400px> <WRAP right box 400px>
Line 17: Line 17:
 ==== Parameters ==== ==== Parameters ====
 === $dataArray === === $dataArray ===
-This is a 2 dimensional array of data. Optionally, ​The first row may contain the column names. If column names are not defined in first row, they must be defined in the third argument $columnNames.+This is a 2 dimensional array of data. Optionally, ​the first row may contain the column names. If column names are not defined in first row, they must be defined in the third argument $columnNames.
 ==== $dataTypes ==== ==== $dataTypes ====
-The optional parameter $dataTypes provides an array of [[dictionary:​metatype|metaTypes]] that match the columns in the table. This array forces a type onto each column. If the array is not provided, the driver probes each column in an attempt to discern the data types in the column. If results are ambiguous, the driver assigns a metaType of '​C'​. Better results are always obtained if the column types are defined, because the driver only recognize C,I and N types.+The optional parameter $dataTypes provides an array of [[v5:dictionary:​metatype|metaTypes]] that match the columns in the table. This array forces a type onto each column. If the array is not provided, the driver probes each column in an attempt to discern the data types in the column. If results are ambiguous, the driver assigns a metaType of '​C'​. Better results are always obtained if the column types are defined, because the driver only recognize C, I and N types.
 ---------------------- ----------------------
 <WRAP right info 400px> <WRAP right info 400px>
-Column names, whether provided in the first row of data or in the ''​$columnNames''​ array, must be **upper case**+Column names, whether provided in the first row of data or in the ''​$columnNames''​ array, must be **upper case**.
 </​WRAP>​ </​WRAP>​
  
   
 ==== $columnNames ==== ==== $columnNames ====
-If the first row of the data array is not column names, the ''​$columnNames''​ parameter must provide a array of column names+If the first row of the data array is not column names, the ''​$columnNames''​ parameter must provide a array of column names.
  
 If provided, the number of columns in the 2<​sup>​nd</​sup>​ and 3<​sup>​rd</​sup>​ arguments must exactly match the number in the first. If provided, the number of columns in the 2<​sup>​nd</​sup>​ and 3<​sup>​rd</​sup>​ arguments must exactly match the number in the first.
Line 44: Line 44:
  
 There is partial support for SQL parsing. We process the SQL using the following rules: There is partial support for SQL parsing. We process the SQL using the following rules:
-  - SQL order by's always work for the first column ordered. Subsequent cols are ignored +  - SQL ORDER BY's always work for the first column ordered. Subsequent cols are ignored. 
-  - All operations take place on the same table. In fact the FROM clause is ignored, ​You can use any name for the table.+  - All operations take place on the same table. In fact the FROM clause is ignored, ​you can use any name for the table.
  
-  - To simplify code, all columns are returned, except when selecting 1 column+  - To simplify code, all columns are returned, except when selecting 1 column.
  
 <code php> <code php>
-$rs = $db->Execute('​select col1,col2 from table'​);​ // sql ignored, will generate all cols +$rs = $db->execute('​select col1,col2 from table'​);​ // sql ignored, will generate all cols 
-$rs = $db->Execute('​select col1 from table'​);​+$rs = $db->execute('​select col1 from table'​);​
 // sql accepted and processed -- any table name is accepted // sql accepted and processed -- any table name is accepted
-$rs = $db->Execute('​select distinct col1 from table'​);​+$rs = $db->execute('​select distinct col1 from table'​);​
 // sql accepted and processed // sql accepted and processed
 </​code> ​ </​code> ​
-Where clauses are ignored, but searching with the 3rd parameter of Execute ​is permitted.+Where clauses are ignored, but searching with the 3rd parameter of execute() ​is permitted.
  
 ------------------------ ------------------------
 <WRAP right important 400px> <WRAP right important 400px>
-Using eval may make code susceptible to SQL injection attacks+Using eval may make code susceptible to SQL injection attacks.
 </​WRAP>​ </​WRAP>​
 ==== Using an eval statement ==== ==== Using an eval statement ====
-The 3rd argument to ''​execute''​ has to use PHP syntax and the driver will attempt to [[http://​php.net/​manual/​en/​function.eval.php|eval()]] it. You can even use PHP functions, for example+The 3rd argument to ''​execute''​ has to use PHP syntax and the driver will attempt to [[https://​php.net/​manual/​en/​function.eval.php|eval()]] it. You can even use PHP functions, for example
 --------------------- ---------------------
 <code php> <code php>
-$rs = $db->Execute('​select * from table',​false,"​\$COL1='​abc'​ and \$COL2=3"​);​ +// 3rd parameter is evaled PHP code, not SQL, so watch the '​=='​ vs. '​='​ 
- +$rs = $db->execute('​select * from table',​ false, "​\$COL1=='​abc'​ and \$COL2==3");
-/* +
-* the 3rd param is searched -- make sure that $COL1 is a legal column name +
-*/+
 </​code>​ </​code>​
  
Line 77: Line 74:
 ===== Limitations ===== ===== Limitations =====
 The following SQL operations are not supported: The following SQL operations are not supported:
-  * Group byhaving, other clauses+  * GROUP BYHAVING, other clauses
   * Expression columns such as min(), max()   * Expression columns such as min(), max()
   * Joins are not permitted   * Joins are not permitted
Line 85: Line 82:
 include '​adodb/​adodb.inc.php';​ include '​adodb/​adodb.inc.php';​
  
-$data =  +$data = array( 
-array(0=>​Array(10001,'​1953-09-02','​Georgi','​Facello','​M','​1986-06-26'​),​ +      ​0=>array(10001,'​1953-09-02','​Georgi','​Facello','​M','​1986-06-26'​),​ 
-      1=>Array(10002,'​1964-06-02','​Bezalel','​Simmel','​F','​1985-11-21'​),​ +      1=>array(10002,'​1964-06-02','​Bezalel','​Simmel','​F','​1985-11-21'​),​ 
-      2=>Array(10003,'​1959-12-03','​Parto','​Bamford','​M','​1986-08-28'​),​ +      2=>array(10003,'​1959-12-03','​Parto','​Bamford','​M','​1986-08-28'​),​ 
-      3=>Array(10004,'​1954-05-01','​Chirstian','​Koblick','​M','​1986-12-01'​),​ +      3=>array(10004,'​1954-05-01','​Christian','​Koblick','​M','​1986-12-01'​),​ 
-      4=>Array(10005,'​1955-01-21','​Kyoichi','​Maliniak',​ '​M','​1989-09-12'​),​ +      4=>array(10005,'​1955-01-21','​Kyoichi','​Maliniak',​ '​M','​1989-09-12'​),​ 
-      5=>Array(10006,'​1953-04-20','​Anneke','​Preusig','​F','​1989-06-02'​)+      5=>array(10006,'​1953-04-20','​Anneke','​Preusig','​F','​1989-06-02'​)
 ); );
  
-$cols      = array('​EMPNO','​BIRTH_DATE','​FIRST_NAME','​LAST_NAME','​HIRE_DATE'​);​+$cols = array('​EMPNO','​BIRTH_DATE','​FIRST_NAME','​LAST_NAME','​SEX','​HIRE_DATE'​);​
 $metaTypes = array('​I','​D','​C','​C','​C','​D'​);​ $metaTypes = array('​I','​D','​C','​C','​C','​D'​);​
  
-$db = ADOnewConnection('​text'​);​+$db = ADONewConnection('​text'​);​
  
- +$db->​connect($data,​ $metaTypes, $cols);
-$db->​connect($data,​$cols,$metaTypes);+
  
 $sql = '​select * from table';​ $sql = '​select * from table';​
  
-$result = $db->​execute($sql,"​\$EMPNO=10005"​);​+// Does not work with PHP 5.3 or later, see workaround below 
 +$result = $db->​execute($sql, false, "​\$EMPNO==10005"​);​
  
-while ($r = $result->​fetchRow())+while ($r = $result->​fetchRow()) ​{
   print_r($r);​   print_r($r);​
 +}
 +</​code>​
  
 +===== Workaround =====
 +
 +For PHP 5.3 or later, the following workaround can be used (tested with ADOdb 5.20.15).
 +
 +Replace the ''​$result = $db->​execute($sql,​ false, "​\$EMPNO==10005"​);''​ statement in the above sample by the following code.
 +
 +<code php>
 +$db->​evalAll = "​\$EMPNO==10005";​
 +$result = $db->​execute($sql);​
 +$db->​evalAll = false;
 </​code>​ </​code>​
 +
 +{{tag>​text deprecated}}
v5/database/text.1444834134.txt.gz · Last modified: 2017/04/21 11:23 (external edit)