Remote DBA Plans
Remote DBA Service

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