BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Preparing SQL Statements

Oracle Tips by Burleson Consulting

Not all SQL statements are queries.  The “query” method is not suited for dealing with statements that do not return result sets and are not queries. This section is devoted to the general prepare and execute methods for SQL statements. An illustration of these methods is shown in Example 23; a rewritten Example 18 from Chapter 3. This time, however, the file loaded into the database is the lyrics for George Thorogood’s song, “Bad to the Bone”.

Prepare and Execute

PEAR DB has an extremely simple interface for parsing and executing SQL.  The following is the syntax for the “prepare” phase:

    $sth=$db->prepare($SQL);

As with the OCI8 module, DB_Common::prepare does not return an error in case the statement parsed has a problem. The errors are reported during the execution phase only. The handle created by the prepare method is then passed to the execute method, which has the following syntax:

    $res DB_Common::execute($sth,$bind_array)

The resulting handle is used for examining whether an error has occurred. The handle returned by the execute method is a normal statement handle and can be examined by a block like the following:

    if (DB::isError($res))
      die($res->getUserInfo()
);

The following method is another method for executing SQL statements, which on occasions, can be extremely useful:

    $res DB_Common::executeMultiple($sth,$bind_array());

The syntax looks exactly like the original “execute” method, yet has a significant difference:  $bind_arrayis an array of arrays. Each element of the bind array is an array corresponding to a set of bind values. If a table is defined like this:

    create table TST (
    a number,
    b varchar2(1));

and an insert statement looks like this:

INSERT INTO TST VALUES (?,?)

Then, after the parse, the call inserts four rows in the TST table with a single call, like the following:

$data=array(
                      array(1,'a'),
                      array(2,'b'),
                      array(3,'c'),
                      array (4,'d'));
$res=$db->executeMultiple($sth,$data);

This is known as an array interface and can tremendously speed up large database uploads.

The following paragraph shows how to upload LOB objects into the database. The infrastructure is already present.  Chapter 3 uses the version of the login form to pass control to the HTML file load_file.html, which is a HTML form created by the Bluefish rapid application development tool. This form calls the PHP script, load_file.php, which does all the work. The HTML form is completely ordinary, except for the “enctype=multipart/form-data” part of the form tag, which is needed to activate the $_FILES array.

Example18.php in Chapter 3 uses OCI8.  Now, Example22.php is modified by placing the following line in it instead of the similar line invoking query2.php:

    header('Location: pear_file.html') ;

The HTML form pear_file.html is literally identical to the load_file.html with the only difference being the PHP script in the “action” part of the form tag. The new form, pear_file.html asks for the PHP script named pear_file.php. All the elements are shown except the part that does the actual load into the database. Here it is shown in Example 23:

Example 23

<html>
<head>
  <title>Upload File (PEAR)</title>
</head> 

<body>
  <?php
  require_once('DB.php');
  session_start(); 

  $DSN=$_SESSION['DSN'];
  $db=DB::connect($DSN);
  $db->setOption('portability',DB_PORTABILITY_NUMROWS
);
  $db->autoCommit(FALSE); 

  $INS="insert into poetry(file_desc,file_data)
             values (?,?)";
 

  try {
        $sth=$db->prepare($INS);
        if (DB::isError($sth)) {
           throw new Exception ($sth->getUserInfo()
);
        }
  }
  catch (Exception $e) {
         $db->rollback();
         die($e->getMessage());
  }
 

  $clob=file_get_contents($_FILES['file']['tmp_name']);
  $res=$db->execute($sth,array($_POST['desc'],$clob));
  if (DB::isError($res))
     die($res->getUserInfo());
  else $db->commit();
  ?>
  <center>
    <h2>File <?=$_FILES['file']['name']?>
     uploaded successfully!</h2>
  </center>
</body>
</html>
 

This upload is much simpler than the one using OCI8. No special descriptors to allocate are needed; just load the file into the $clob variable and make it a part of the bind array. The only thing that has not been seen before is the file_get_contents() function.

Here is what the PHP online manual says about it:

file_get_contents
(PHP 4 >= 4.3.0, PHP 5)
file_get_contents--Reads entire file into a string

Description

string file_get_contents ( string filename [, bool use_include_path [, resource context [, int offset]]])

Identical to file(), except that file_get_contents() returns the file in a string, starting at the specified offset. On failure, file_get_contents() will return FALSE.

file_get_contents() is the preferred way to read the contents of a file into a string. It will use memory mapping techniques if supported by your OS to enhance performance.

With the modern 64 bit architectures, the amount of data that can be read into a variable is enormous, much larger than the mythical 2GB. In an unlikely event that the file cannot be read into a variable,  it will have to be read piece by piece and written into the database using the DBMS_LOB.WRITE PL/SQL procedure.

The strength of the PEAR DB library is in keeping the simple things simple. Quite frankly, the OCI8 module despite offering more capabilities than PEAR DB is much more complex and difficult to use. PEAR DB is simpler, similar to the familiar Perl DBI module, but it cannot work with REF_Cursor type. The numRows() method is very different from the oci_num_rows() method. The OCI8 method returns the number of rows fetched, while the PEAR numRows() method works only before the fetch and returns the total number of rows returned by the query.

The beginning of this chapter mentioned that PEAR is a collection of modules. The HTML_Table module was used without it being explained. It is time to do so now.

SEE CODE DEPOT FOR FULL SCRIPTS


The above book excerpt is from  "Easy Oracle PHP: Creating Dynamic Web Pages with Oracle Data". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_2005_2_php_oracle.htm

 


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter