Php oracle database function library

: This article mainly introduces the oracle database function library of php. if you are interested in the PHP Tutorial, please refer to it. Class DB_ SQL {
Var $ Debug = false;
Var $ Home = "/u01/app/Oracle/PRoduct/8.0.4 ";
Var $ Remote = 1;
/* This Query will be sent directly after the first connection
Example:
Var $ ConnectQuery = "ALTER session SET nls_date_language = german nls_date_format = 'DD. MM. RRRR '";
-> Set the date format for this session, this is fine when your ora-role
Cannot be altered */
Var $ ConnectQuery = '';
/* Due to a strange error with Oracle 8.0.5, Apache and php3.0.6
You don't need to set the ENV-on my system Apache
Will change to a zombie, if I don't set this to FALSE!
Instead I set these ENV-vars before the startup of apache.
If unsure try it out, if it works .*/
Var $ OraPutEnv = true;

Var $ Database = "";
Var $ User = "";
Var $ PassWord = "";

Var $ Link_ID = 0;
Var $ Query_ID = 0;
Var $ Record = array ();
Var $ Row;

Var $ Errno = 0;
Var $ Error = "";
Var $ ora_no_next_fetch = false;


/* Copied from db_MySQL for completeness */
/* Public: identification constant. never change this .*/
Var $ type = "oracle ";
Var $ revision = "Revision: 1.3 ";
Var $ Halt_On_Error = "yes"; # "yes" (halt with message), "no" (ignore errors quietly), "report" (ignore errror, but spit a warning)

/* Public: constructor */
Function DB_ SQL ($ query = ""){
$ This-> query ($ query );
}

/* Public: some trivial reporting */
Function link_id (){
Return $ this-> Link_ID;
}

Function query_id (){
Return $ this-> Query_ID;
}

Function connect (){
# See above why we do this
If ($ this-> OraPutEnv ){
PutEnv ("ORACLE_SID = $ this-> Database ");
PutEnv ("ORACLE_HOME = $ this-> Home ");
}
If (0 = $ this-> Link_ID ){
If ($ this-> Debug ){
Printf ("
Connect () ing to $ this-> Database...
N ");
}
If ($ this-> Remote ){
If ($ this-> Debug ){
Printf ("
Connect () $ this-> User/****** @ $ this-> Database
N ");
}
$ This-> Link_ID = ora_plogon
("$ This-> User/$ this-> Password @ $ this-> Database ","");
/************** (Comment by SSilk)
This dosn't work on my system:
$ This-> Link_ID = ora_plogon
("$ This-> User @ $ this-> Database. world", "$ this-> Password ");
***************/
} Else {
If ($ this-> Debug ){
Printf ("
Connect () $ this-> User, $ this-> Password
N ");
}
$ This-> Link_ID = ora_plogon ("$ this-> User", "$ this-> Password ");
/* (Comment by SSilk: don't know how this cocould work, but I leave this untouched !) */
}
If ($ this-> Debug ){
Printf ("
Connect () Link_ID: $ this-> Link_ID
N ");
}
If (! $ This-> Link_ID ){
$ This-> halt ("connect () Link-ID = false ".
"($ This-> Link_ID), ora_plogon failed ");
} Else {
// Echo "commit on

";
Ora_commiton ($ this-> Link_ID );
}
If ($ this-> Debug ){
Printf ("
Connect () Obtained the Link_ID: $ this-> Link_ID
N ");
}
# Execute Connect Query
If ($ this-> ConnectQuery ){
$ This-> query ($ this-> ConnectQuery );
}
}
}

# In order to increase the # of cursors per system/user go edit
# Init. ora file and increase the max_open_cursors parameter. Yours is on
# The default value, which is 100 per user.
# We tried to change the behaviour of query () in a way, that it tries
# To safe cursors, but on the other side be carefull with this, that you
# Don't use an old result.
##
# You can also make extensive use of-> disconnect ()!
# The unused QueryIDs will be recycled sometimes.

Function query ($ Query_String)
{

/* No empty query please .*/
If (empty ($ Query_String ))
{
Return 0;
}

$ This-> connect ();
$ This-> lastQuery = $ Query_String;

If (! $ This-> Query_ID ){
$ This-> Query_ID = ora_open ($ this-> Link_ID );
}
If ($ this-> Debug ){
Printf ("Debug: query = % s
N ", $ Query_String );
Printf ("
Debug: Query_ID: % d
N ", $ this-> Query_ID );
}

If (! @ Ora_parse ($ this-> Query_ID, $ Query_String )){
$ This-> Errno = ora_errorcode ($ this-> Query_ID );
$ This-> Error = ora_error ($ this-> Query_ID );
$ This-> halt ("
Ora_parse () failed:
$ Query_String
Snap & paste this to sqlplus!");
} Elseif (! @ Ora_exec ($ this-> Query_ID )){
$ This-> Errno = ora_errorcode ($ this-> Query_ID );
$ This-> Error = ora_error ($ this-> Query_ID );
$ This-> halt ("
N $ Query_Stringn
Snap & paste this to sqlplus!");
}

$ This-> Row = 0;

If (! $ This-> Query_ID ){
$ This-> halt ("Invalid SQL:". $ Query_String );
}

Return $ this-> Query_ID;
}

Function next_record (){
If (! $ This-> ora_no_next_fetch &&
0 = ora_fetch ($ this-> Query_ID )){
If ($ this-> Debug ){
Printf ("
Next_record (): ID: % d Row: % d
N ",
$ This-> Query_ID, $ this-> Row + 1 );
// More info for $ this-> Row + 1 is $ this-> num_rows (),
// But dosn't work in all cases (complicated selects)
// And it is very slow here
}
$ This-> Row + = 1;

$ Errno = ora_errorcode ($ this-> Query_ID );
If (1403 = $ errno) {#1043 means no more records found
$ This-> Errno = 0;
$ This-> Error = "";
$ This-> disconnect ();
$ Stat = 0;
} Else {
$ This-> Error = ora_error ($ this-> Query_ID );
$ This-> Errno = $ errno;
If ($ this-> Debug ){
Printf ("
% D Error: % s ",
$ This-> Errno,
$ This-> Error );
}
$ Stat = 0;
}
} Else {
$ This-> ora_no_next_fetch = false;
For ($ ix = 0; $ ix Query_ID); $ ix ++ ){
$ Col = strtolower (ora_columnname ($ this-> Query_ID, $ ix ));
$ Value = ora_getcolumn ($ this-> Query_ID, $ ix );
$ This-> Record ["$ col"] = $ value;
$ This-> Record [$ ix] = $ value;
# DBG echo" [$ Col]: $ Value
N ";
}
$ Stat = 1;
}

Return $ stat;
}

# Seek () works only for $ pos-1 and $ pos
# Perhaps I make a own implementation, but my
# Opinion is, that this shoshould be done by PHP3
Function seek ($ pos ){
If ($ this-> Row-1 = $ pos ){
$ This-> ora_no_next_fetch = true;
} Elseif ($ this-> Row = $ pos ){
# Do nothing
} Else {
$ This-> halt ("Invalid seek (): Position is cannot be handled by API.
".
"Only a seek to the last element is allowed in this version
".
"Difference too big. Wanted: $ pos Current pos: $ this-> Row ");
}
If ($ this-> Debug) echo"
Debug: seek = $ pos
";
$ This-> Row = $ pos;
}

Function lock ($ table, $ mode = "write "){
If ($ mode = "write "){
$ Result = ora_do ($ this-> Link_ID, "lock table $ table in row exclusive mode ");
} Else {
$ Result = 1;
}
Return $ result;
}

Function unlock (){
Return ora_do ($ this-> Link_ID, "commit ");
}

// Important note: This function dosn't work with Oracle-Database-Links!
// You are free to get a better method .:)
Function metadata ($ table, $ full = false ){
$ Count = 0;
$ Id = 0;
$ Res = array ();

/*
* Due to compatibility problems with Table we changed the behavior
* Of metadata ();
* Depending on $ full, metadata returns the following values:
*
*-Full is false (default ):
* $ Result []:
* [0] ["table"] table name
* [0] ["name"] field name
* [0] ["type"] field type
* [0] ["len"] field length
* [0] ["flags"] field flags ("not null", "INDEX ")
* [0] ["format"] precision and scale of number (eg. "10, 2") or empty
* [0] ["index"] name of index (if has one)
* [0] ["chars"] number of chars (if any char-type)
*
*-Full is true
* $ Result []:
* ["Num_fields"] number of metadata records
* [0] ["table"] table name
* [0] ["name"] field name
* [0] ["type"] field type
* [0] ["len"] field length
* [0] ["flags"] field flags ("not null", "INDEX ")
* [0] ["format"] precision and scale of number (eg. "10, 2") or empty
* [0] ["index"] name of index (if has one)
* [0] ["chars"] number of chars (if any char-type)
* [0] ["php_type"] the correspondig PHP-type
* [0] ["php_subtype"] the subtype of PHP-type
* ["Meta"] [field name] index of field named "field name"
* This cocould used, if you have the name, but no index-num-very fast
* Test: if (isset ($ result ['meta'] ['myfield']) {}...
*/

$ This-> connect ();

# This is a right outer join: "(+)", if you want to see, what
# This query results try the following:
# $ Table = new Table; $ db = new my_DB_ SQL; # you have to make
### Your own class
# $ Table-> show_results ($ db-> query (see query vvvvvv ))
##
$ This-> query ("select t. table_name, T. column_name, T. data_type ,".
"T. data_length, T. data_precision, T. data_scale, T. nullable ,".
"T. char_col_decl_length, I. index_name ".
"FROM ALL_TAB_COLUMNS T, ALL_IND_COLUMNS I ".
"Where t. column_name = I. column_name (+ )".
"And t. table_name = I. table_name (+ )".
"And t. table_name = UPPER ('$ table') order by t. column_id ");

$ I = 0;
While ($ this-> next_record ()){
$ Res [$ I] ["table"] = $ this-> Record [table_name];
$ Res [$ I] ["name"] = strtolower ($ this-> Record [column_name]);
$ Res [$ I] ["type"] = $ this-> Record [data_type];
$ Res [$ I] ["len"] = $ this-> Record [data_length];
If ($ this-> Record [index_name]) $ res [$ I] ["flags"] = "INDEX ";
$ Res [$ I] ["flags"]. = ($ this-> Record [nullable] = 'n ')? '': 'Not Null ';
$ Res [$ I] ["format"] = (int) $ this-> Record [data_precision]. ",".
(Int) $ this-> Record [data_scale];
If ("0, 0" = $ res [$ I] ["format"]) $ res [$ I] ["format"] = '';
$ Res [$ I] ["index"] = $ this-> Record [index_name];
$ Res [$ I] ["chars"] = $ this-> Record [char_col_decl_length];
If ($ full ){
$ J = $ res [$ I] ["name"];
$ Res ["meta"] [$ j] = $ I;
$ Res ["meta"] [strtoupper ($ j)] = $ I;
Switch ($ res [$ I] ["type"]) {
Case "VARCHAR2 ":
Case "VARCHAR ":
Case "CHAR ":
$ Res ["php_type"] = "string ";
$ Res ["php_subtype"] = "";
Break;
Case "DATE ":
$ Res ["php_type"] = "string ";
$ Res ["php_subtype"] = "date ";
Break;
Case "BLOB ":
Case "CLOB ":
Case "BFILE ":
Case "RAW ":
Case "LONG ":
Case "long raw ":
$ Res ["php_type"] = "string ";
$ Res ["php_subtype"] = "blob ";
Break;
Case "NUMBER ":
If ($ res [$ I] ["format"]) {
$ Res ["php_type"] = "double ";
$ Res ["php_subtype"] = "";
} Else {
$ Res ["php_type"] = "int ";
$ Res ["php_subtype"] = "";
}
Break;
Default:
$ This-> halt ("metadata (): Type is not a valid value: '$ res [$ I] [type]'");
Break;
}
}
If ($ full) $ res ["meta"] [$ res [$ I] ["name"] = $ I;
$ I ++;
}
If ($ full) $ res ["num_fields"] = $ I;
# $ This-> disconnect ();
Return $ res;
}

# This function is unstested!
Function affected_rows (){
If ($ this-> Debug) echo"
Debug: affected_rows = ". ora_numrows ($ this-> Query_ID )."
";
Return ora_numrows ($ this-> Query_ID );
}

# Known bugs: It will not work for select distinct and any
# Other constructs which are depending on the resulting rows.
# So you * really need * to check every query you make, if it
# Will work with it!
##
# Also, for a qualified replacement you need to parse
# Selection, cause this will fail: "SELECT id, from ...").
# "From" is-as far as I know a keyword in Oracle, so it can
# Only be used in this way. But you have been warned.
Function num_rows (){
$ Curs = ora_open ($ this-> Link_ID );

# This is the important part and it is also the HACK!
If (eregi ("^ [: space:] * SELECT [[: space:]", $ this-> lastQuery ))
{

# This works for all ?? Cases, including select distinct case.
# We just make select count (*) from original SQL expression
# And remove order by (if any) for speed
# I like regular expressions too ;-)))
$ Q = sprintf ("select count (*) FROM (% s )",
@ Eregi_Replace ("ORDER [[: space:] + BY [^)] * () *)", "\ 1 ",
$ This-> lastQuery)
);

# Works also for subselects:
# If (eregi ("[[: space:] + FROM ([[: space:] +. * [[: space:] + FROM) ", $ this-> lastQuery, $ r ))
# $ Areplace = $ r [1];
# $ Q = eregi_Replace ("^ [[: space:] * SELECT [[: space:] + ".
# ". * [[: Space:] + FROM ",
# "Select count (*) FROM $ areplace ",
# $ This-> lastQuery );

If ($ this-> Debug) echo"
Debug: num_rows: $ q
";

ORA_parse ($ curs, $ q );
ORA_exec ($ curs );
ORA_fetch ($ curs );
$ Result = ORA_getcolumn ($ curs, 0 );
ORA_close ($ curs );
If ($ this-> Debug)
{
Echo"
Debug: ID ". $ this-> QueryID.
"Num_rows =". $ result ."
";
}
Return $ result;
}
Else
{
$ This-> halt ("Last Query was not a SELECT: $ this-> lastQuery ");
}
}

Function num_fields (){
If ($ this-> Debug) echo"
Debug: num_fields = ". ora_numcols ($ this-> Query_ID )."
";
Return ora_numcols ($ this-> Query_ID );
}

Function nf (){
Return $ this-> num_rows ();
}

Function np (){
Print $ this-> num_rows ();
}

Function f ($ Name ){
Return $ this-> Record [$ Name];
}

Function p ($ Name ){
Print $ this-> Record [$ Name];
}

/* Public: sequence number */
Function nextid ($ seq_name)
{
$ This-> connect ();

/* Independent Query_ID */
$ Query_ID = ora_open ($ this-> Link_ID );

If (! @ Ora_parse ($ Query_ID, "SELECT $ seq_name.NEXTVAL from dual "))
{
// There is no such sequence yet, then create it
If (! @ Ora_parse ($ Query_ID, "create sequence $ seq_name ")
|
! @ Ora_exec ($ Query_ID)
)
{
$ This-> halt ("
Nextid () function-unable to create sequence ");
Return 0;
}
@ Ora_parse ($ Query_ID, "SELECT $ seq_name.NEXTVAL from dual ");
}
If (! @ Ora_exec ($ Query_ID )){
$ This-> halt ("
Ora_exec () failed:
NextID function ");
}
If (@ ora_fetch ($ Query_ID )){
$ Next_id = ora_getcolumn ($ Query_ID, 0 );
}
Else {
$ Next_id = 0;
}
If ($ Query_ID> 0 ){
Ora_close ($ Query_ID );
}

Return $ next_id;
}

Function disconnect (){
If ($ this-> Debug ){
Echo "Debug: Disconnecting $ this-> Query_ID...
N ";
}
If ($ this-> Query_ID <1 ){
Echo" Warning: Disconnect (): Cannot free ID $ this-> Query_IDn ";
# Return ();
}
Ora_close ($ this-> Query_ID );
$ This-> Query_ID = 0;
}

/* Private: error handling */
Function halt ($ msg ){
If ($ this-> Halt_On_Error = "no ")
Return;

$ This-> haltmsg ($ msg );

If ($ this-> Halt_On_Error! = "Report ")
Die ("Session halted .");
}

Function haltmsg ($ msg ){
Printf ("
Database error:% S
N ", $ msg );
Printf (" Oracle Error: % S (% s)
N ",
$ This-> Errno,
$ This-> Error );
}

Function table_names (){
$ This-> connect ();
$ This-> query ("
SELECT table_name, tablespace_name
FROM user_tables ");
$ I = 0;
While ($ this-> next_record ())
{
$ Info [$ I] ["table_name"] = $ this-> Record ["table_name"];
$ Info [$ I] ["tablespace_name"] = $ this-> Record ["tablespace_name"];
$ I ++;
}
Return $ info;
}


// Some transaction support
// Methods are used in ct_oracle.inc
Function begin_transaction ()
{
$ This-> connect ();
// Now, disable autocommit
Ora_CommitOff ($ this-> Link_ID );
If ($ this-> Debug)
{
Print "BEGIN TRANSACTION
";
}
}
Function end_transaction ()
{
If ($ this-> Debug)
{
Print "BEGIN TRANSACTION
";
}

$ Res = 1;
If (! @ Ora_Commit ($ this-> Link_ID ))
{
Ora_CommitOn ($ this-> Link_ID );
$ This-> halt ("Unable to finish transaction ");
$ Res = 0;
}
// Enable autocommit again
Ora_CommitOn ($ this-> Link_ID );

If ($ this-> Debug)
{
Print "end transaction: $ res
";
}
Return $ res;
}


}
?>

The above introduces the php oracle database function library, including the content, and hope to be helpful to friends who are interested in PHP tutorials.