Differences

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

--- database:text [2015/10/08 04:00] – created mnewnham
+++ v5:database:text [2018/07/01 22:45] – prefer functions start lowercase (#430) peterdd
@@ Line 1: / Line 1: @@
 ====== Plain Text ======
-The ''text''driver allows a
+<WRAP alert>
+This driver is broken in ADOdb version 5 when used with PHP version 5.3 and up. This is due to the fact that the **execute** method has a non-compliant 3rd argument to it. Absent any user requests, this driver will be removed in ADOdb version 6.
+</WRAP>
 ===== Description =====
-$db = NewADOConnection('text');
+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.
- 	$db->Connect($array,[$types],[$colnames]);
+-------------------
-	Parameter $array is the 2 dimensional array of data. The first row can contain the
+<WRAP right box 400px>
-	column names. If column names is not defined in first row, you MUST define $colnames,
+== Syntax ==
-	the 3rd parameter.
+  object connect(
-	Parameter $types is optional. If defined, it should contain an array matching
+        array $dataArray,
-	the number of columns in $array, with each element matching the correct type defined
+        optional mixed $dataTypes=false,
-	by MetaType: (B,C,I,L,N). If undefined, we will probe for $this->_proberows rows
+        optional mixec $columnNames=false
-	to guess the type. Only C,I and N are recognised.
+        )
-	Parameter $colnames is optional. If defined, it is an array that contains the
+</WRAP>
-	column names of $array. If undefined, we assume the first row of $array holds the
+===== The Connect Statement ====
-	column names.
+==== Parameters ====
- The Execute() function will return a recordset. The recordset works like a normal recordset.
+=== $dataArray ===
- We have partial support for SQL parsing. We process the SQL using the following rules:
+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.
-. SQL order by's always work for the first column ordered. Subsequent cols are ignored
+==== $dataTypes ====
-. All operations take place on the same table. No joins possible. In fact the FROM clause
+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.
-	is ignored! You can use any name for the table.
+----------------------
-. To simplify code, all columns are returned, except when selecting 1 column
+<WRAP right info 400px>
- 	$rs = $db->Execute('select col1,col2 from table'); // sql ignored, will generate all cols
+Column names, whether provided in the first row of data or in the ''$columnNames'' array, must be **upper case**
-	We special case handling of 1 column because it is used in filter popups
+</WRAP>
-	$rs = $db->Execute('select col1 from table');
-	// sql accepted and processed -- any table name is accepted
-	$rs = $db->Execute('select distinct col1 from table');
+==== $columnNames ====
-	// sql accepted and processed
+If the first row of the data array is not column names, the ''$columnNames'' parameter must provide a array of column names
-. Where clauses are ignored, but searching with the 3rd parameter of Execute is permitted.
-   This has to use PHP syntax and we will eval() it. You can even use PHP functions.
+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.
-	 $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
+----------------------------
-	// and all column names must be in upper case.
+<WRAP right box 400px>
-. Group by, having, other clauses are ignored
+== Syntax ==
-. Expression columns, min(), max() are ignored
+  mixed execute(
-. All data is readonly. Only SELECTs permitted.
+        string $sql
+       optional bool $ignored=false
+       optional string $evalStatement=false
+        )
+</WRAP>
+===== execute =====
+The execute() method will return a recordset. The recordset works like a normal recordset.
+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
+  - 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
+<code php>
+$rs = $db->execute('select col1,col2 from table'); // sql ignored, will generate all cols
+$rs = $db->execute('select col1 from table');
+// sql accepted and processed -- any table name is accepted
+$rs = $db->execute('select distinct col1 from table');
+// sql accepted and processed
+</code>
+Where clauses are ignored, but searching with the 3rd parameter of Execute is permitted.
+------------------------
+<WRAP right important 400px>
+Using eval may make code susceptible to SQL injection attacks
+</WRAP>
+==== 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
+---------------------
+<code php>
+$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>
+===== Limitations =====
+The following SQL operations are not supported:
+  * Group by, having, other clauses
+  * Expression columns such as min(), max()
+  * Joins are not permitted
+===== Usage =====
+<code php>
+include 'adodb/adodb.inc.php';
+$data =
+array(0=>array(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
+=>array(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
+=>array(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
+=>array(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
+=>array(10005,'1955-01-21','Kyoichi','Maliniak', 'M','1989-09-12'),
+=>array(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02')
+);
+$cols      = array('EMPNO','BIRTH_DATE','FIRST_NAME','LAST_NAME','HIRE_DATE');
+$metaTypes = array('I','D','C','C','C','D');
+$db = ADOnewConnection('text');
+$db->connect($data,$cols,$metaTypes);
+$sql = 'select * from table';
+$result = $db->execute($sql,"\$EMPNO=10005");
+while ($r = $result->fetchRow())
+  print_r($r);
+</code>