Accessing oracle lob using php in the bsstructure

PHP, known as "PHP: Hypertext Preprocessor", is a multi-purpose scripting language widely used in Open Source (Open Source) and can be embedded in HTML. Its syntax is similar to C, Java, and Perl and is easy to learn. The main goal of this language is to allow Web developers to quickly write dynamically generated Web pages. However, PHP functions are not limited to this. PHP is generally considered to be able to implement complex programming tasks faster and more effectively, it is precisely because it is more stable and consumes less resources that it has become a necessary WEB script design language for the development of B/S structure systems, playing a role similar to middleware, that is, Syntax Parsing and execution.

Oracle lob Data Model

In B/S (Browser/Server, Browser/Server) application systems, more than just simple text information needs to be stored, it also includes images, audio and video materials, or ultra-long texts. For example, if a document system is developed, common characters or other types of data descriptions cannot be used in binary files or ultra-long texts such as charts and attachments in documents, this requires the backend database to have the ability to store the data. Oracle LOB objects can be used to implement this function.

Oracle LOB is a data type used to store a large amount of binary and text data (a LOB field can store up to 4 GB of data ). Currently, it can be divided into two types: Internal LOB and external LOB. Internal LOB stores data in the database as byte streams. Therefore, many internal LOB operations can be involved in transactions, or they can be backed up and restored just like normal data. Oracle8i supports three types of internal LOB: BLOB (binary data), CLOB (single-byte character data), and NCLOB (Multi-byte country character data ). The CLOB and NCLOB types are suitable for storing ultra-long text data, and the BLOB field is suitable for storing a large amount of binary data, such as, video, and audio. Currently, Oracle8i only supports one type of external LOB, that is, BFILE. In the database, this type only stores the location information of data in the operating system, and the file form other than the data entity exists in the operating system file system. Therefore, the data of this type is read-only and does not participate in transactions. This type helps users manage a large number of files accessed by external programs.

PHP Oracle 8 Function Analysis

There are two ORACLE Function Extension libraries in PHP. The ORACLE8 functions allow access to the Oracle8 and Oracle7 databases. These functions use Oracle8 Call-Interface (OCI8 ), you can bind local and global PHP variables to the Oracle location flag. Full support for LOB, FILE, and ROWID allows you to use User-Defined variables, that is, User-Defined Object Classes of your database.

The OCIFetchInto function in the Oracle8 function library is used to retrieve a row of data records and put them into an array. the syntax of this function is described as follows:

Int OCIFetchInto (array & result, int [mode])

The mode parameter can be omitted, and the internal value is OCI_NUM. When accessing Oracle LOB, if you want to return a LOB object, the mode should be set to OCI_ASSOC + OCI_RETURN_LOBS.

The OCIBindByName function is used to bind PHP variables to Oracle Objects to establish data communication between PHP and Oracle. The function syntax is described as follows:

Boolean OCIBindByName (int stmt, string ph_name, mixed & variable, int length, int [type])

The stmt parameter is a string indicator parsed by the Oracle parsing function OCIParse. The ph_name parameter is the name of the ORACLE return variable to be bound. The & symbol must be added before the parameter variable. The parameter length is the length of the variable. If it is set to-1, the maximum value of the specified variable is used. The parameter type can be omitted. Its values include OCI_ B _FILE (binary file) and OCI_ B _CFILE (text file) OCI_ B _CLOB, OCI_ B _BLOB, and OCI_ B _ROWID (ROWID. It is worth noting that when using the LOB/ROWID/BFILE data type in Oracle8, you must first execute the OCINewDescriptor () function and set the length parameter to-1.

The OCINewDescriptor function is used to initialize the new LOB/FILE description. The function syntax is described as follows:

String OCINewDescriptor (int connection, int [type])

The type is the same as the type definition in the OCIBindByName function.

Required environment Configuration

Oracle 8 client library is required to use the Oracle 8 function library of PHP. Before using this extension, make sure that you have correctly set Oracle environment variables for Oracle users and WEB daemon users. The environment variables to be set are listed below:

ORACLE_HOME # ORACLE installation path
ORACLE_SID # ORACLE Database ID
LD_LIBRARY_PATH # LD connection LIBRARY PATH
NLS_LANG # ORALCE region (language) settings
ORA_NLS33 # ORA_NLS33 path
Verify that the preceding variables are correct in a Linux environment. The best way is to execute them in the oracle user and nobody respectively:

# Env

Determine whether the preceding environment variables are consistent based on the output results.

After setting environment variables for WEB Server users, you also need to add the WEB server users (nobody, www) to the oracle group.

For detailed instructions on ORACLE8 client and PHP installation settings, refer to the relevant technical manual.

Example

An example of uploading and storing files in the oracle lob field in PHP is as follows:

// LOB object initialization, get PHP variable pointer

$ Lob = OCINewDescriptor ($ conn, OCI_D_LOB );

// Add a record to the relevant file record table, oracle SQL syntax explanation

$ Stmt = OCIParse ($ conn, "update T_FILE set FILENAME = '". $ _ FILES ['fj1'] ['name'].
"', FILETYPE = '". $ _ FILES ['f1'] ['type']. "', FILES = EMPTY_BLOB () where FILEID = ". $ newid. "returning FILES into: lob ");

// Bind the LOB variable

OCIBindByName ($ stmt, ': lob', & $ lob,-1, OCI_ B _BLOB );

OCIExecute ($ stmt, OCI_DEFAULT); // execute the statement

If ($ lob-> savefile ($ F1) {// save the file submitted by the form to the ORACLE database through the lob pointer

OCICommit ($ conn );

// Upload successfully

} Else {

// Failed to upload

}

OCIFreeDesc ($ lob );

OCIFreeStatement ($ stmt );

// End

An example of an application that extracts and downloads files from a database in PHP is as follows:

$ Stmt = OCIParse ($ conn, "SELECT * from t _ file where fileid = $ ID ");

OCIExecute ($ stmt );

If (OCIFetchInto ($ stmt, $ result, OCI_ASSOC + OCI_RETURN_LOBS )){

// Output file type information for the browser to determine

Header ("Content-type:". $ result [FILETYPE]);

// Output file name. The browser prompts whether to open or download the file.

Header ("Content-Disposition: attachment; filename =". $ result [FILENAME]);

// For more information about how to use the header function, see PHP manual. For more information about the HTML header, see Internet RFC 2616.

// Output the file stream. Here, the browser obtains the file content and prompts that the file is being downloaded or opened directly.

Echo $ result [FILES];

}

OCIFreeStatement ($ stmt );

// End

The above example is only a key statement of the application, and has passed the verification on the Linux + PHP + Apache + Oracle8i platform. Readers can complete and supplement it according to their own needs.

Conclusion

This article discusses the implementation of PHP's access to oracle lob, which is only one aspect of PHP's basic application technology in the B/S structure system. The author has compared the execution efficiency of JDBC and PHP-OCI, with JDBC as the Access Engine oas http Server (HTTP Server as apache2.0) the response speed is inferior to the WEB Server built on the Linux + Aache + PHP-OCI platform, which of course has the outstanding performance of Linux, but it is undeniable that, the combination of PHP and OCI is excellent.

Due to the lack of attempts to combine oracle lob and PHP ORACLE8 function libraries, to manage large data files in the Development Information Management System, PHP's functions in this area have not been effectively used. I hope this article will be helpful to programmers who are engaged in PHP database technology but have encountered such difficulties.