ADOdb

Database Abstraction Layer for PHP

User Tools

Site Tools

Trace:
v5:userguide:oracle_tutorial

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revisionBoth sides next revision
userguide:oracle_tutorial [2015/12/26 18:35] – mnewnhamuserguide:oracle_tutorial [2015/12/27 15:20] – mnewnham
Line 127: Line 127:
 And if you are interested in having the data returned in a 2-dimensional array, you can use: And if you are interested in having the data returned in a 2-dimensional array, you can use:
  
-$arr = $db->GetArray("select * from emp where empno>:emp", array('emp' => 7900));+  $arr = $db->GetArray("select * from emp where empno>:emp", array('emp' => 7900));
 Now to obtain only the first row as an array: Now to obtain only the first row as an array:
  
-$arr = $db->GetRow("select * from emp where empno=:emp", array('emp' => 7900));+  $arr = $db->GetRow("select * from emp where empno=:emp", array('emp' => 7900));
 Or to retrieve only the first field of the first row: Or to retrieve only the first field of the first row:
  
-$arr = $db->GetOne("select ename from emp where empno=:emp", array('emp' => 7900));+  $arr = $db->GetOne("select ename from emp where empno=:emp", array('emp' => 7900));
 For easy pagination support, we provide the SelectLimit function. The following will perform a select query, limiting it to 100 rows, starting from row 201 (row 1 being the 1st row): For easy pagination support, we provide the SelectLimit function. The following will perform a select query, limiting it to 100 rows, starting from row 201 (row 1 being the 1st row):
  
-$offset = 200; $limitrows = 100; +  $offset = 200; $limitrows = 100; 
-$rs = $db->SelectLimit('select * from table', $limitrows, $offset);+  $rs = $db->SelectLimit('select * from table', $limitrows, $offset); 
 The $offset parameter is optional. The $offset parameter is optional.
  
Line 144: Line 145:
 Using Prepare() For Frequently Used Statements Using Prepare() For Frequently Used Statements
  
-Prepare( ) is for compiling frequently used SQL statement for reuse. For example, suppose we have a large array which needs to be inserted into an Oracle database. The following will result in a massive speedup in query execution (at least 20-40%), as the SQL statement only needs to be compiled once:+Prepare() is for compiling frequently used SQL statement for reuse. For example, suppose we have a large array which needs to be inserted into an Oracle database. The following will result in a massive speedup in query execution (at least 20-40%), as the SQL statement only needs to be compiled once:
  
 <code php> <code php>
Line 179: Line 180:
 Note that LogError( ) is a user-defined function, and not part of ADOdb. Note that LogError( ) is a user-defined function, and not part of ADOdb.
  
-Inserting LOBs is more complicated. Since ADOdb 4.55, we allow you to do this (assuming that the photo field is a BLOB, and we want to store $blob_data into this field, and the primary key is the id field):+Inserting LOBs is more complicated. Assuming in the following example that the photo field is a BLOB, and we want to store $blob_data into this field, and the primary key is the id field):
  
 <code php> <code php>
Line 233: Line 234:
 </code> </code>
  
-PrepareSP( ) is a special function that knows about bind parameters. The main limitation currently is that IN OUT parameters do not work.+[[reference:PrepareSP()]] is a special function that knows about bind parameters.
  
 Bind Parameters and REF CURSORs Bind Parameters and REF CURSORs
  
-We could also rewrite the REF CURSOR example to use InParameter (requires ADOdb 4.53 or later):+We could also rewrite the REF CURSOR example to use InParameter:
  
 +<code php>
 $stmt = $db->PrepareSP("BEGIN adodb.open_tab(:refc,:tabname); END;"); $stmt = $db->PrepareSP("BEGIN adodb.open_tab(:refc,:tabname); END;");
 $input = 'A%'; $input = 'A%';
Line 244: Line 246:
 $rs = $db->ExecuteCursor($stmt,'refc'); $rs = $db->ExecuteCursor($stmt,'refc');
 while ($arr = $rs->FetchRow()) print_r($arr); while ($arr = $rs->FetchRow()) print_r($arr);
-Bind Parameters and LOBs+</code> 
 + 
 +===== Bind Parameters and LOBs =====
  
 You can also operate on LOBs. In this example, we have IN and OUT parameters using CLOBs. You can also operate on LOBs. In this example, we have IN and OUT parameters using CLOBs.
- +<code php> 
- $text = 'test test test'; +$text = 'test test test'; 
- $sql = "declare rs clob; begin :rs := lobinout(:sa0); end;"; +$sql = "declare rs clob; begin :rs := lobinout(:sa0); end;"; 
- $stmt = $conn -> PrepareSP($sql); +$stmt = $conn -> PrepareSP($sql); 
- $conn -> InParameter($stmt,$text,'sa0', -1, OCI_B_CLOB); # -1 means variable length +$conn -> InParameter($stmt,$text,'sa0', -1, OCI_B_CLOB); # -1 means variable length 
- $rs = ''; +$rs = ''; 
- $conn -> OutParameter($stmt,$rs,'rs', -1, OCI_B_CLOB); +$conn -> OutParameter($stmt,$rs,'rs', -1, OCI_B_CLOB); 
- $conn -> Execute($stmt); +$conn -> Execute($stmt); 
- echo "return = ".$rs."<br>";+echo "return = ".$rs."<br>"; 
 +</code>
 Similarly, you can use the constant OCI_B_BLOB to indicate that you are using BLOBs. Similarly, you can use the constant OCI_B_BLOB to indicate that you are using BLOBs.
  
-Reusing Bind Parameters with CURSOR_SHARING=FORCE+====Reusing Bind Parameters with CURSOR_SHARING=FORCE ====
  
 Many web programmers do not care to use bind parameters, and prefer to enter the SQL directly. So instead of: Many web programmers do not care to use bind parameters, and prefer to enter the SQL directly. So instead of:
  
-$arr = $db->GetArray("select * from emp where empno>:emp", array('emp' => 7900));+  $arr = $db->GetArray("select * from emp where empno>:emp", array('emp' => 7900)); 
 They prefer entering the values inside the SQL: They prefer entering the values inside the SQL:
  
-$arr = $db->GetArray("select * from emp where empno>7900");+  $arr = $db->GetArray("select * from emp where empno>7900"); 
 This reduces Oracle performance because Oracle will reuse compiled SQL which is identical to previously compiled SQL. The above example with the values inside the SQL is unlikely to be reused. As an optimization, from Oracle 8.1 onwards, you can set the following session parameter after you login: This reduces Oracle performance because Oracle will reuse compiled SQL which is identical to previously compiled SQL. The above example with the values inside the SQL is unlikely to be reused. As an optimization, from Oracle 8.1 onwards, you can set the following session parameter after you login:
  
-ALTER SESSION SET CURSOR_SHARING=FORCE+  ALTER SESSION SET CURSOR_SHARING=FORCE 
 This will force Oracle to convert all such variables (eg. the 7900 value) into constant bind parameters, improving SQL reuse. This will force Oracle to convert all such variables (eg. the 7900 value) into constant bind parameters, improving SQL reuse.
  
-More speedup tips. +===== More speedup tips =====
- +
- +
  
-7. Dates and Datetime in ADOdb+==== Dates and Datetime in ADOdb ====
  
 There are two things you need to know about dates in ADOdb. There are two things you need to know about dates in ADOdb.
Line 283: Line 289:
 Secondly, since Oracle treats dates and datetime as the same data type, we decided not to display the time in the default date format. So on login, ADOdb will set the NLS_DATE_FORMAT to 'YYYY-MM-DD'. If you prefer to show the date and time by default, do this: Secondly, since Oracle treats dates and datetime as the same data type, we decided not to display the time in the default date format. So on login, ADOdb will set the NLS_DATE_FORMAT to 'YYYY-MM-DD'. If you prefer to show the date and time by default, do this:
  
 +<code php>
 $db = NewADOConnection('oci8'); $db = NewADOConnection('oci8');
 $db->NLS_DATE_FORMAT =  'RRRR-MM-DD HH24:MI:SS'; $db->NLS_DATE_FORMAT =  'RRRR-MM-DD HH24:MI:SS';
 $db->Connect($tns, $user, $pwd); $db->Connect($tns, $user, $pwd);
 +</code>
 +
 Or execute: Or execute:
  
-$sql = quot;ALTER SESSION SET NLS_DATE_FORMAT = 'RRRR-MM-DD HH24:MI:SS'";+<code php> 
 +$sql = "ALTER SESSION SET NLS_DATE_FORMAT = 'RRRR-MM-DD HH24:MI:SS'";
 $db->Execute($sql); $db->Execute($sql);
 +</code>
 +
 If you are not concerned about date portability and do not use ADOdb's portability layer, you can use your preferred date format instead. If you are not concerned about date portability and do not use ADOdb's portability layer, you can use your preferred date format instead.
  
-8. Database Portability Layer+===== Database Portability Layer =====
  
 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.| 
 ADOdb also provides multiple oracle oci8 drivers for different scenarios: ADOdb also provides multiple oracle oci8 drivers for different scenarios:
  
Line 336: Line 349:
 The author's benchmarks suggest that using non-persistent connections and the Shared Server configuration offer the best performance. If Shared Server is not an option, only then consider using persistent connections. The author's benchmarks suggest that using non-persistent connections and the Shared Server configuration offer the best performance. If Shared Server is not an option, only then consider using persistent connections.
  
-===== Connection Examples ===== 
- 
-Just in case you are having problems connecting to Oracle, here are some examples: 
- 
-a. PHP and Oracle reside on the same machine, use default SID, with non-persistent connections: 
- 
-  $conn = NewADOConnection('oci8'); 
-  $conn->Connect(false, 'scott', 'tiger'); 
-b. TNS Name defined in tnsnames.ora (or ONAMES or HOSTNAMES), eg. 'myTNS', using persistent connections: 
- 
-  $conn = NewADOConnection('oci8'); 
-  $conn->PConnect(false, 'scott', 'tiger', 'myTNS'); 
- 
-or 
- 
-  $conn->PConnect('myTNS', 'scott', 'tiger'); 
- 
-c. Host Address and SID 
- 
-  $conn->connectSID = true; 
-  $conn->Connect('192.168.0.1', 'scott', 'tiger', 'SID'); 
- 
-d. Host Address and Service Name 
- 
-  $conn->Connect('192.168.0.1', 'scott', 'tiger', 'servicename'); 
- 
-e. Oracle connection string: 
- 
-  $cstr = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port))(CONNECT_DATA=(SID=$sid)))"; 
-  $conn->Connect($cstr, 'scott', 'tiger'); 
- 
-f. ADOdb data source names (dsn): 
- 
-  $dsn = 'oci8://user:pwd@tnsname/?persist';  # persist is optional 
-  $conn = ADONewConnection($dsn);  # no need for Connect/PConnect 
- 
-  $dsn = 'oci8://user:pwd@host/sid'; 
-  $conn = ADONewConnection($dsn); 
- 
-  $dsn = 'oci8://user:pwd@/';   # oracle on local machine 
-  $conn = ADONewConnection($dsn); 
- 
-With ADOdb data source names, you don't have to call Connect( ) or PConnect( ). 
  
  
v5/userguide/oracle_tutorial.txt · Last modified: 2019/12/29 21:36 by mnewnham