Ok, as some of you may know I'm an Oracle newbie w/ PHP. I'd rather use
MySQL but at the office here we use Oracle and boy do I have alot to learn.
I'm starting to hate it after using MySQL!!
--------------------------------------------------------------------------
1) Is there a similar statement using PHP/Oracle functions as below for
MySQL?
--------------------------------------------------------------------------
while (list ($key, $val) = each ($HTTP_POST_VARS)) {
print $key . " = " . $val . "<br>";
}
--------------------------------------------------------------------------
2) Should I be using something like this or is there easier way to pull data
into array?
--------------------------------------------------------------------------
// Start new Oracle cursor and query
$q = "select * from inventory";
$ora_cur = ora_do( $ora_conn, $q ) or die("Couldn't setup Oracle
Cursor");
if ( $ora_cur ) {
// Figure out how many columns
$numCols = ora_numcols( $ora_cur );
// Get the first fetched row and put it in to our array...
$row = array();
// Loop through columns
for( $i = 0; $i < $numCols; $i++ ){
array_push( $row, ora_getcolumn( $ora_cur, $i ) );
}
array_push( $results, $row );
// Fetch rows, one at a time, putting them in their own
// array. Each row should be appended to the array of
// results..
// Get each row
while ( ora_fetch( $ora_cur ) ){
$row = array();
// Loop through columns
for( $i = 0; $i < $numCols; $i++ ){
array_push( $row, ora_getcolumn( $ora_cur, $i ) );
}
array_push( $results, $row );
}
}
while (list ($results, $row) = each ($HTTP_GET_VARS)) {
print $results. " = " . $row . "<br>";
}
?>
--------------------------------------------------------------------------
3) For some reason my html below does not get displayed on the page. After
I submit the page is blank and just says database connected succesfully.
Why is it stopping here??
--------------------------------------------------------------------------
<html><head><title>etc...<body>etc...
9  7021  pd*****@comcast.net says... Ok, as some of you may know I'm an Oracle newbie w/ PHP. I'd rather use
MySQL but at the office here we use Oracle and boy do I have alot to learn.
I'm starting to hate it after using MySQL!!
Philip,
Suggest that you get ADODB http://php.weblogs.com/ADOdb/
This is a database abstraction layer package, which may be a softer
migration path for you than going from MySQL hard into the native OCI8
library.
If you do go that way, be sure to read up on how to query the DB using
bind variables (supported in ADODB for Oracle, MS-SQL) and make sure you
use them.
Geoff M
That link you provided does not work!
I can post to oracle no problem, but pulling data is a pain for me.
Here's an example that isn't working very well for me:
$stmt = OCIParse($conn, "select * from inventory");
OCIExecute($stmt);
OCIFetchInto ($stmt, $row, OCI_ASSOC);
while (list($key, $val) = each($row)) {
print $key . " = " . $val . "<br>";
}
"gmuldoon" <gm*************@scu.edu.au> wrote in message
news:MP************************@news.asgard.net.au ... pd*****@comcast.net says... Ok, as some of you may know I'm an Oracle newbie w/ PHP. I'd rather use
MySQL but at the office here we use Oracle and boy do I have alot to
learn. I'm starting to hate it after using MySQL!!
Philip,
Suggest that you get ADODB http://php.weblogs.com/ADOdb/
This is a database abstraction layer package, which may be a softer
migration path for you than going from MySQL hard into the native OCI8
library.
If you do go that way, be sure to read up on how to query the DB using
bind variables (supported in ADODB for Oracle, MS-SQL) and make sure you
use them.
Geoff M
On Mon, 16 Feb 2004 16:32:32 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote: "gmuldoon" <gm*************@scu.edu.au> wrote in message
news:MP************************@news.asgard.net.a u... pd*****@comcast.net says... Ok, as some of you may know I'm an Oracle newbie w/ PHP. I'd rather use
MySQL but at the office here we use Oracle and boy do I have alot to learn. I'm starting to hate it after using MySQL!!
Philip,
Suggest that you get ADODB http://php.weblogs.com/ADOdb/
That link you provided does not work!
Works from here. Check your ISP.
I can post to oracle no problem, but pulling data is a pain for me.
Here's an example that isn't working very well for me:
$stmt = OCIParse($conn, "select * from inventory");
OCIExecute($stmt);
OCIFetchInto ($stmt, $row, OCI_ASSOC);
while (list($key, $val) = each($row)) {
print $key . " = " . $val . "<br>";
}
Other than the lack of error checking and assumption that it returns a single
row, what about that isn't 'working very well' for you? Presumably you want to
put the OCIFetchInto in a while loop. Like in the manual.
<http://uk2.php.net/manual/en/function.ocifetchinto.php>
But since you haven't said what's wrong, it's hard to suggest how to fix it.
--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space> pd*****@comcast.net says... That link you provided does not work!
An example why not to top-post. Put you responses in-line.
Capitalisation problem, try:
http://php.weblogs.com/ADODB/
I can post to oracle no problem, but pulling data is a pain for me.
Here's an example that isn't working very well for me:
$stmt = OCIParse($conn, "select * from inventory");
OCIExecute($stmt);
OCIFetchInto ($stmt, $row, OCI_ASSOC);
while (list($key, $val) = each($row)) {
print $key . " = " . $val . "<br>";
}
Some very rough sample snippets:
OCI8 example of the way I code:
$sql1="select item_id, item_desc from inventory";
$stmt1=OCIParse($conn, $sql1);
OCIDefineByName($stmt1, "item_id", &$id);
OCIDefineByName($stmt1, "item_desc", &$desc);
OCIExecute($stmt1);
while(OCIFetch($stmt1)){
echo $id.' - '.$desc.'<br>';
}
OCILogoff($conn);
ADODB example of the way I code:
$type='some_type';
$sql="select item_id, item_desc from inventory where item_type=:type";
$stmt=$conn->Prepare($sql);
$conn->Parameter($stmt, $type, 'type'); // USE BIND VARIABLES
$rs=$oraconn->Execute($stmt);
if ($rs===false) die('Sorry, unable to retrieve items);
while (!$rs->EOF ) {
echo $rs->fields(item_id).' - '$rs->fields(item_desc).'<br>';
$rs->moveNext();
}
Cheers,
Geoff M
On Mon, 16 Feb 2004 22:45:08 GMT, gmuldoon <gm*************@scu.edu.au> wrote: Suggest that you get ADODB http://php.weblogs.com/ADOdb/
That link you provided does not work!
Capitalisation problem, try:
http://php.weblogs.com/ADODB/
They're running Windows so your original URL was (also) fine.
<http://uptime.netcraft.com/up/graph/?host=php.weblogs.com>
--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>
Thansk for the examples GMuldoon, and folks...!
---------------------------------------------------
1) How would I query an item_id and return all the key/values to populate a
form...
---------------------------------------------------
"select * from inventory where item_id = '$item_id'";
Then what?? Must I list every variable in OCIDefineByName?? I would hope
not...I should be able to pull key/val like I do in mySQL which right now
looks much easier then oracle's pain in the ass way.
$sql1="select * from inventory where item_id = '$item_id'";
$stmt1=OCIParse($conn, $sql1);
OCIDefineByName($stmt1, "item_id", &$id);
OCIDefineByName($stmt1, "item_desc", &$desc);
OCIExecute($stmt1);
while(OCIFetch($stmt1)){
echo $id.' - '.$desc.'<br>';
}
OCILogoff($conn);
---------------------------------------------------
2) What are the benefits to using ADODB??
--------------------------------------------------- That link you provided does not work!
An example why not to top-post. Put you responses in-line.
Capitalisation problem, try:
http://php.weblogs.com/ADODB/
I can post to oracle no problem, but pulling data is a pain for me.
Here's an example that isn't working very well for me:
$stmt = OCIParse($conn, "select * from inventory");
OCIExecute($stmt);
OCIFetchInto ($stmt, $row, OCI_ASSOC);
while (list($key, $val) = each($row)) {
print $key . " = " . $val . "<br>";
}
Some very rough sample snippets:
OCI8 example of the way I code:
$sql1="select item_id, item_desc from inventory";
$stmt1=OCIParse($conn, $sql1);
OCIDefineByName($stmt1, "item_id", &$id);
OCIDefineByName($stmt1, "item_desc", &$desc);
OCIExecute($stmt1);
while(OCIFetch($stmt1)){
echo $id.' - '.$desc.'<br>';
}
OCILogoff($conn);
ADODB example of the way I code:
$type='some_type';
$sql="select item_id, item_desc from inventory where item_type=:type";
$stmt=$conn->Prepare($sql);
$conn->Parameter($stmt, $type, 'type'); // USE BIND VARIABLES
$rs=$oraconn->Execute($stmt);
if ($rs===false) die('Sorry, unable to retrieve items);
while (!$rs->EOF ) {
echo $rs->fields(item_id).' - '$rs->fields(item_desc).'<br>';
$rs->moveNext();
}
Cheers,
Geoff M
On Tue, 17 Feb 2004 17:52:31 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote: Thansk for the examples GMuldoon, and folks...!
---------------------------------------------------
1) How would I query an item_id and return all the key/values to populate a
form...
---------------------------------------------------
"select * from inventory where item_id = '$item_id'";
No, completely wrong.
Then what?? Must I list every variable in OCIDefineByName?? I would hope
not...I should be able to pull key/val like I do in mySQL which right now
looks much easier then oracle's pain in the ass way.
OCIFetchInto is acceptable, and acts similarly to mysql_fetch_*. Have you read
the documentation?
OCIDefineByName more closely matches the way you're _supposed_ to use the OCI
interface natively from C, and so is likely to be more efficient (untested).
$sql1="select * from inventory where item_id = '$item_id'";
*bash over head - this is some thing you must never do*
MySQL has the flaw that you have to stuff values into SQL statements, mixing
DATA with SQL. This is Bad. Sensible databases use BIND VARIABLES,
alternatively named PLACEHOLDERS.
A properly rewritten version of above would look like:
select * from inventory where item_id = :item_id
Or:
select * from inventory where item_id = ?
Depending on what interface and database you're using.
(Actually you'd probably also want to lose the '*' and replace it with a
proper column list to insulate you from schema changes).
Stuffing data into literal SQL causes several problems:
(1) SQL injection attacks. You forget to escape quotes, and suddenly data
becomes executed SQL. This is a severe security problem.
(2) Oracle, and other databases, cache the execution plans of SQL. The first
time it comes across a new statement, it works out the best way to execute it.
Since it has a decent optimiser, this is a relatively expensive operation, as
it does a fair bit of work to find out what's really the best way to do it from
the many access paths it has available. But it caches this in memory, so when
you execute it again later (you rarely only run something *once*) it doesn't
have to go through the parsing and optimisation steps, it already knows HOW to
execute it, it just has to plug in the values.
But if you put literal values in an SQL statement, the text of the statement
is different every time you execute it. So it can't use the cached plan, and
has to re-parse it, and do all the optimisation again. This causes excessive
"hard-parsing" and cripples your database.
If you have bind variables/placeholders, then the execution plan is the same
(since the SQL is the same) but you plug in the _data_ later.
$sql = 'select * from inventory where item_id = :item_id';
$stmt1 = OCIParse($conn, $sql1);
if (!$stmt1) {
// handle the error, with reference to OCIError()
}
OCIBindByName($stmt, ':item_id', &$item_id, -1);
OCIDefineByName($stmt1, "item_id", &$id);
OCIDefineByName($stmt1, "item_desc", &$desc);
OCIExecute($stmt1);
while(OCIFetch($stmt1)){
echo $id.' - '.$desc.'<br>';
}
You have a choice between using defines, or using OCIFetchInto which wraps
this up for you and gives you PHP arrays. It's up to you. Read the
documentation, make your own decision.
OCILogoff($conn);
---------------------------------------------------
2) What are the benefits to using ADODB??
---------------------------------------------------
ADODB has a manual. The very first chapter of the manual gives reasons why you
might want to use ADODB.
Geoff already gave you a reason why; it hides you from some of the OCI8
interface, which is quite low-level, and gives you a higher-level uniform
interface that works for many different types of database. That link you provided does not work!
An example why not to top-post. Put you responses in-line.
You (Philip) didn't write this. But because you're still top-posting (look it
up on Google) and further you're not quoting correctly, Geoff's reply is
attributed to you in your reply.
--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>
"You have a choice between using defines, or using OCIFetchInto which wraps
this up for you and gives you PHP arrays. It's up to you. Read the
documentation, make your own decision."
From your poor explanation, I would conclude that I three choices not two.
So which is it?
OCIDefineByName, OCIBindByName, or using OCIFetchInto
Perhaps you could be a little clearer.
Phil
"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:q8********************************@4ax.com... On Tue, 17 Feb 2004 17:52:31 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote:
Thansk for the examples GMuldoon, and folks...!
---------------------------------------------------
1) How would I query an item_id and return all the key/values to populate
aform...
---------------------------------------------------
"select * from inventory where item_id = '$item_id'";
No, completely wrong.
Then what?? Must I list every variable in OCIDefineByName?? I would hope
not...I should be able to pull key/val like I do in mySQL which right now
looks much easier then oracle's pain in the ass way.
OCIFetchInto is acceptable, and acts similarly to mysql_fetch_*. Have you
read the documentation?
OCIDefineByName more closely matches the way you're _supposed_ to use the
OCI interface natively from C, and so is likely to be more efficient
(untested).$sql1="select * from inventory where item_id = '$item_id'";
*bash over head - this is some thing you must never do*
MySQL has the flaw that you have to stuff values into SQL statements,
mixing DATA with SQL. This is Bad. Sensible databases use BIND VARIABLES,
alternatively named PLACEHOLDERS.
A properly rewritten version of above would look like:
select * from inventory where item_id = :item_id
Or:
select * from inventory where item_id = ?
Depending on what interface and database you're using.
(Actually you'd probably also want to lose the '*' and replace it with a
proper column list to insulate you from schema changes).
Stuffing data into literal SQL causes several problems:
(1) SQL injection attacks. You forget to escape quotes, and suddenly data
becomes executed SQL. This is a severe security problem.
(2) Oracle, and other databases, cache the execution plans of SQL. The
first time it comes across a new statement, it works out the best way to execute
it. Since it has a decent optimiser, this is a relatively expensive operation,
as it does a fair bit of work to find out what's really the best way to do it
from the many access paths it has available. But it caches this in memory, so
when you execute it again later (you rarely only run something *once*) it
doesn't have to go through the parsing and optimisation steps, it already knows
HOW to execute it, it just has to plug in the values.
But if you put literal values in an SQL statement, the text of the
statement is different every time you execute it. So it can't use the cached plan,
and has to re-parse it, and do all the optimisation again. This causes
excessive "hard-parsing" and cripples your database.
If you have bind variables/placeholders, then the execution plan is the
same (since the SQL is the same) but you plug in the _data_ later.
$sql = 'select * from inventory where item_id = :item_id';
$stmt1 = OCIParse($conn, $sql1);
if (!$stmt1) {
// handle the error, with reference to OCIError()
}
OCIBindByName($stmt, ':item_id', &$item_id, -1);
OCIDefineByName($stmt1, "item_id", &$id);
OCIDefineByName($stmt1, "item_desc", &$desc);
OCIExecute($stmt1);
while(OCIFetch($stmt1)){
echo $id.' - '.$desc.'<br>';
}
You have a choice between using defines, or using OCIFetchInto which
wraps this up for you and gives you PHP arrays. It's up to you. Read the
documentation, make your own decision.
OCILogoff($conn);
---------------------------------------------------
2) What are the benefits to using ADODB??
---------------------------------------------------
ADODB has a manual. The very first chapter of the manual gives reasons
why you might want to use ADODB.
Geoff already gave you a reason why; it hides you from some of the OCI8
interface, which is quite low-level, and gives you a higher-level uniform
interface that works for many different types of database.
That link you provided does not work!
An example why not to top-post. Put you responses in-line.
You (Philip) didn't write this. But because you're still top-posting
(look it up on Google) and further you're not quoting correctly, Geoff's reply is
attributed to you in your reply.
--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>
On Wed, 18 Feb 2004 12:50:34 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote: "You have a choice between using defines, or using OCIFetchInto which wraps
this up for you and gives you PHP arrays. It's up to you. Read the
documentation, make your own decision."
From your poor explanation, I would conclude that I three choices not two.
So which is it?
OCIDefineByName, OCIBindByName, or using OCIFetchInto
Perhaps you could be a little clearer.
You have (at least) four choices for fetching data, but OCIBindByName is not
one.
OCIDefineByName is for binding PHP variables to columns to get data out of a
result set; each time you OCIFetch a row, the variables get set to the
corresponding columns of the result set.
Or, OCIResult fetches the value of one column in the 'current' row.
Or, OCIFetchInto fetches a single row as an array.
Or, OCIFetchStatement, which fetches an array (one entry per row) of arrays
(one entry per column of that row) - i.e. the entire result set in one call.
OCIBindByName is for binding data IN to placeholders, not for fetching data.
For a given SQL statement, you follow this cycle:
Prepare: OCIParse - you do this once.
Loop: (for each different set of values you want to query with):
Bind: OCIBindByName - for each bind variable/placeholder in the query.
[ Define: Optionally bind PHP variables to result set columns. ]
Execute: OCIExecute - begin execution of the statement.
Fetch: Either:
Loop: (for each row in the result set)
Then either:
OCIFetch (values go into previously OCIDefineByName'd variables
or use OCIResult)
or OCIFetchInto (PHP fetches the row into a PHP array)
End loop (when no more rows)
Or: OCIFetchStatement: Fetch all rows in one go
End loop (when you're finished with the cursor)
So for 'select a, b, c from inventory where d = :d' you would have:
* One call to OCIBindByName to bind a value to the :d placeholder.
* [Optionally] Three calls to OCIDefineByName to bind PHP variables to receive
values for a, b and c when fetching.
Any clearer?
--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space> This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Philip D Heady |
last post by:
How do I pull data into an array for use on my web pages? What's the best
way w/ Oracle 9i? Must I bind varibables?? I'm lost...
Example:
$stmt = OCIParse($conn, "select * from inventory");
...
|
by: Andrew Arace |
last post by:
I scoured the groups for some hands on code to perform the menial task
of exporting table data from an Access 2000 database to Oracle
database (in this case, it was oracle 8i but i'm assuming this...
|
by: bdtmike |
last post by:
I'm using Access to view data from an Oracle 9i database. When I try
to link an Oracle database, the PC appears to Hang before finally
presenting the list of tables to choose from. It's a large...
|
by: Chris Fink |
last post by:
I have setup an Oracle table which contains a blob field. How do I insert
data into this field using C# and ADO.net?
|
by: jander22 |
last post by:
Hi,
I just started playing with Python and I am working on a program that pulls data from a table in Oracle and creates a similar table in SQLite. Basically, I want to be able to do this:
create...
|
by: mikej |
last post by:
Hi,
I automated a process (using a recordable MACRO) in excell to open msaccess query and retrieve data (from an Oracle database) - said data then to be compared via VLOOKUP with data in a second...
|
by: egarobar |
last post by:
I am using Access 2003 (on WinXP) to read from an Oracle db, where there is a table with a CLOB which is a variable-size text field. In the 'linked table' which is created in the Tables panel of...
|
by: Vinod Sadanandan |
last post by:
All,
Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features .
Precompilers:...
|
by: anuptosh |
last post by:
Hi,
I have been trying to run the below example to get a Oracle Array as an output from a Java code. This is an example I have found on the web. But, the expected result is that the code should...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
