Oracle Database function library

<?
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 ("<br> connect () ing to $ this-> database... <br> N ");
}
If ($ this-> remote ){
If ($ this-> Debug ){
Printf ("<br> connect () $ this-> User/****** @ $ this-> database <br> 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 ("<br> connect () $ this-> User, $ this-> password <br> 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 ("<br> connect () link_id: $ this-> link_id <br> N ");
}
If (! $ This-> link_id ){
$ This-> halt ("Connect () Link-id = false ".
"($ This-> link_id), ora_plogon failed ");
} Else {
// Echo "commit on <p> ";
Ora_commiton ($ this-> link_id );
}
If ($ this-> Debug ){
Printf ("<br> connect () obtained the link_id: $ this-> link_id <br> 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 <br> N", $ QUERY_STRING );
Printf ("<br> Debug: query_id: % d <br> 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 ("<br> ora_parse () failed: <br> $ QUERY_STRING <br> <small> snap & Paste this to sqlplus! </Small> ");
} Elseif (! @ Ora_exec ($ this-> query_id )){
$ This-> errno = ora_errorcode ($ this-> query_id );
$ This-> error = ora_error ($ this-> query_id );
$ This-> halt ("<br> N $ query_stringn <br> <small> snap & Paste this to sqlplus! </Small> ");
}

$ 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
}< br> $ 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 ("<br> % d error: % s ",
$ This-> errno,
$ This-> error );
}
$ Stat = 0;
}
} Else {
$ This-> ora_no_next_fetch = false;
For ($ IX = 0; $ IX <ora_numcols ($ this-> 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 "<B> [$ Col] </B>: $ value <br> 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. <br> ".
"Only a seek to the last element is allowed in this version <br> ".
"Difference too big. Wanted: $ POS current pos: $ this-> row ");
}
If ($ this-> Debug) echo "<br> Debug: Seek = $ POS <br> ";
$ 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 "<br> Debug: affected_rows =". ora_numrows ($ this-> query_id). "<br> ";
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 "<br> Debug: num_rows: $ q <br> ";

Ora_parse ($ curs, $ q );
Ora_exec ($ curs );
Ora_fetch ($ curs );
$ Result = ora_getcolumn ($ curs, 0 );
Ora_close ($ curs );
If ($ this-> Debug)
{
Echo "<br> Debug: ID". $ this-> queryid.
"Num_rows =". $ result. "<br> ";
}
Return $ result;
}
Else
{
$ This-> halt ("Last query was not a select: $ this-> lastquery ");
}
}

Function num_fields (){
If ($ this-> Debug) echo "<br> Debug: num_fields =". ora_numcols ($ this-> query_id). "<br> ";
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 ("<br> 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 ("<br> ora_exec () failed: <br> 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... <br> N ";
}
If ($ this-> query_id <1 ){
Echo "<B> warning </B>: 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 ("</TD> </tr> </table> <br> <B> database error: </B> % S <br> N", $ MSG );
Printf ("<B> Oracle error </B>: % s (% s) <br> 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 <br> ";
}
}
Function end_transaction ()
{
If ($ this-> Debug)
{
Print "begin transaction <br> ";
}

$ 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 <br> ";
}
Return $ res;
}

}
?>