Make Oracle like mySQL

Anon

ok so everyone is used to being able to do:

while ($row = mysql_fetch_array($sql_result)) {
$first_name = $row["FirstName"];
$last_name = $row["LastName"];
$home_phone = $row["HomePhone"];
$email = $row["EmailName"];
}

which is my fav way to access the data since you can ref the column names as array keys.

Can someone show how to duplicate this functionality in Oracle using ora or oci functions? I've been hacking at it getting really frustrated. Thanks in advance.

Anon

//OCIFetchInto($stmt, &$row, OCI_ASSOC+OCI_RETURN_NULLS)
//returns associative array $row["COL1"], $row["COL2"], etc.

while (OCIFetchInto($stmt, &$row, OCI_ASSOC+OCI_RETURN_NULLS))
{
$first_name = $row["FIRSTNAME"];
$last_name = $row["LASTNAME"];
$home_phone = $row["HOMEPHONE"];
$email = $row["EMAILNAME"];
}

-- Michael
Darkstreak Consulting
www.darkstreak.com

Anon

Or:

Get USER data

$getuser = OCIParse($conn, "SELECT first_name, last_name, gmt_offset FROM rolodex");
OCIExecute($getuser);
$numres = OCIFetchstatement($getuser, $userres);
OCIFreestatement($getuser);

for($i=0;$i<$numres;$i++)
{
$fname[$i] = $userres["FIRST_NAME"][$i];
$lname[$i] = $userres["LAST_NAME"][$i];
$gmt_offset[$i] = $userres["GMT_OFFSET"][$i];
}

Jon

Anon

Do the fields in the array have to be uppercase? Does it matter if I use something like $userres["First_Name"] instead of $userres["FIRST_NAME"] ?

Thanks

John G.

Anon

By default, column names in Oracle are stored/handled/returned in uppercase. To get change this, you have two options, of which I highly recommend the first:

1) Use double-quotes to alias the column name in your SELECT statements.
When creating your table as you normally would do:
CREATE TABLE rolodex (first_name VARCHAR2(9), last_name VARCHAR2(9), gmt_offset NUMBER);
INSERT INTO rolodex VALUES ('Tom', 'Lehrer', -5);
INSERT INTO rolodex VALUES ('Billy', 'Gates', -8);
INSERT INTO rolodex VALUES ('Richard', 'Walker', -6);
COMMIT;

When retrieving data from it: (note the column aliases)

Get USER data

$getuser = OCIParse($conn, 'SELECT first_name "First_Name", last_name "Last_Name", gmt_offset "GMT_Offset" FROM rolodex');
OCIExecute($getuser);
$numres = OCIFetchstatement($getuser, $userres);
OCIFreestatement($getuser);

for($i=0;$i<$numres;$i++)
{
$fname[$i] = $userres["First_Name"][$i];
$lname[$i] = $userres["Last_Name"][$i];
$gmt_offset[$i] = $userres["GMT_Offset"][$i];
}

2) Use double-quotes to explicitly specify the case of the column name in the
CREATE TABLE statement, then use double-quotes everwhere the column name is
referenced.
When creating your table, explicitly specify the spelling of the column names:
CREATE TABLE rolodex ("First_Name" VARCHAR2(9), "Last_Name" VARCHAR2(9), "GMT_Offset" NUMBER);
INSERT INTO rolodex VALUES ('Tom', 'Lehrer', -5);
INSERT INTO rolodex VALUES ('Billy', 'Gates', -8);
INSERT INTO rolodex VALUES ('Richard', 'Walker', -6);
COMMIT;

When retrieving data from it: (note the double-quotes)

Get USER data

$getuser = OCIParse($conn, 'SELECT "First_Name", "Last_Name", "GMT_Offset" FROM rolodex');
OCIExecute($getuser);
$numres = OCIFetchstatement($getuser, $userres);
OCIFreestatement($getuser);

for($i=0;$i<$numres;$i++)
{
$fname[$i] = $userres["First_Name"][$i];
$lname[$i] = $userres["Last_Name"][$i];
$gmt_offset[$i] = $userres["GMT_Offset"][$i];
}

Please let me know if this answers your question.
-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

Anon

Oopss... I had the wrong message open when I posted my response to your question. Please take a look at the following and let me know if it answers your question:

http://www.phpbuilder.com/forum/read.php3?num=5&id=14769&thread=11329

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

Anon

Hey Michael,

Thanks for the response. I'm quite disappointed that I now have to go and change thousands of lines of SQL code in order to support Oracle - this kinda throws the whole idea of PHP's database transparency out the window.

My ideal solution would be to:

Have PHP support lower and uppercase result sets as equal - so $Result['ThisField'] is the same as $Result['THISFIELD'].

Be able to write a script that somehow changes the SQL script and adds in the alias columns before the script is executed to save from having to go and change all the current SQL code in my program.

Thanks for your help so far though. I'm going to scout around to see what I can find because I refuse to believe that this sort of thing is as limiting as it seems.

Strange thing is - Oracle DB through PHP is so under-documented... there are hundreds of thousands of tutorials that deal with MySQL, but this one is just not available.

-- John.

Anon

Here is a random thought from the "Innovations" side of my company:

Do you have a known (or knowable) list of column names you are working with?

If so, you could write a simple array manipulation routine which massages the keys into the mixed-case you prefer. To really be slick, you could wrapper the OCI fetch routines to call the normal OCI functions then do the array key manipulation.

That could save you a lot of time modifying code.

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

Anon

Here's what I came up with - think this will work?

$SQL = "SELECT Field1, Field2, Field3 FROM FieldList WHERE Criteria = 'Something'";

function ConvertSQL ($SQL) {
//--- Converts the SQL string to work with Oracle

if (substr ($SQL, 0, 6) == "SELECT") {

	//--- Find end position of fields
	$EndPos = strpos ($SQL, "FROM");
	//--- Retrieve fields
	$Fields = substr ($SQL, 6, $EndPos - 6);
	//--- Remove trailing whitespace
	$Fields = trim ($Fields);
	//--- Split fields into array
	$Fields = split ("[ ,;]+", $Fields);

	for ($i = 0; $i < count ($Fields); $i++) {
		$Fields[$i] = strtoupper ($Fields[$i])." AS '".$Fields[$i]."'";
	}

	$SQL = "SELECT ".implode (", ", $Fields)." ".substr ($SQL, $EndPos, strlen ($SQL) - $EndPos);

}

return $SQL;

}

echo ConvertSQL ($SQL);

Anon

That is a slightly different approach than I originally had in mind, but it should work.

I had been thinking about doing PHP array manipulations after each row was fetched from the database. The advantage to that is that next to nothing changes in the SQL and PHP you've already written. The down side is that array manipulation code overhead adds up. That is fine for small to medium record quantities, but performance would decrease as the number of records returned increased.

Your approach would be more efficient when dealing with larger quantities of records returned. Unfortunately it requires parsing the SELECT statement which could get messy fast if you use complex SELECT statements elsewhere in your code. To generalize it would require using OCIColumnName(), another OCIParse() and some regexp code to parse the SELECT.

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com