Re: PHP/Oracle - Pulling data into array

From: Andy Hassall (andy_at_andyh.co.uk)
Date: 02/18/04 

Date: Tue, 17 Feb 2004 23:20:27 +0000

On Tue, 17 Feb 2004 17:52:31 -0500, "Philip D Heady" <pdheady@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 <andy@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

Relevant Pages

  • Re: Business objects, subset of collection
    ... SQL only works when the statements are ... all items (invoices) are subscribing for events. ... all items and all items has to execute the criteria evaluation, ... The features of a OO database is basically the same as of a network ...
    (comp.object)
  • Re: Multiple Database Security - How to handle
    ... There is no 'execute as' in SQL Server but you can simplify security ... Assuming the DM database contains tables that are accessed only by ...
    (microsoft.public.sqlserver.security)
  • Re: New to C# - DB question
    ... Firstly, you are interested in the System.Data namespace, also known as ... In there you will find a few sub namespaces for specific database ... is your sql statement. ... The former allows you to just execute some SQL, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Problem with program hanging - SQLServer issue?
    ... > queries the program makes to an SQL Server database. ... > Public WithEvents SQLServerCN As ADODB.Connection ... > I execute some stored procedures like so: ... > And a couple of SQL statements like so: ...
    (microsoft.public.vb.database.ado)
  • Re: declarative DBI programming
    ... I'm taking a look through some of the database modules you and Terrence ... the real substance of the SQL statements appears to be the ... As is, how locking across multiple SQL statements, sorting, ... >> but how about returning multiple rows of data? ...
    (perl.dbi.users)