Oracle Repository Function Library

?
Class Db_sql {
var $Debug = false;
var $Home = "/u01/app/oracle/product/8.0.4";
var $Remote = 1;
/* This Query is sent directly after the ' the '
Example:
var $ConnectQuery = "ALTER session SET Nls_date_language=german nls_date_format= ' DD." Mm. Rrrr ' ";
-> Set The date format 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
'll change to a zombie, if I don ' t set it 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), "a" (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 () {
# # 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 I-could 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/System/user go edit the
# # Init.ora file and increase the max_open_cursors parameter. Yours is on
# # The default value, per user.
# # We tried to change the behaviour of query () in a way, which it tries
# # to safe cursors, but on the other side is carefull with this
# # # don ' t use a.
##
# # can also make extensive use of->disconnect ()!
# # The unused queryids would 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 ("<br>next_record (): ID:%d Row:%d<br>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's 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 ("<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 () $pos-1 and $pos
# # Perhaps I make a own implementation, but my
# # opinion is, which this should being 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 to handled by Api.<br>".
"Only a" and "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 the 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 could used, if you have the name, but no index-num-very fast
* Test:if (Isset ($result [' Meta '] [' myfield '])] {}
*/

$this->connect ();

# OUTER JOIN: ' (+) ', if you want to, 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 (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," he said.
"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 won't work for SELECT DISTINCT and any
# # Other constructs which are depending on the resulting rows.
# so your *really need* to check every query for make, if it
# # would work with it!
##
# # Also, for a qualified replacement your need to parse the
# # Selection, cause this'll fail: "SELECT ID, from from ...").
# # ' from ' Is-as far as I know a keyword in Oracle, so it can
# # Only is used in this way. But you have been warned.
function Num_rows () {
$curs =ora_open ($this->link_id);

# # This are the important part and it are also the hack!
if (Eregi ("^[[:space:]]*select[[:space:]", $this->lastquery))
{

# This works to all?? Cases, including SELECT DISTINCT case.
# We just make select count (*) from original SQL expression
# and remove order by (if no) 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 is 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&gt: 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!= "a")
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;
}


}
?>