PHP/Oracle SQL statements, OciParse

[Philip D Heady]

Ok I'm a newbie to using Oracle9i w/ PHP, been a MySQL guy... What is the
proper sql statement to use insert tables, etc. as below.? I don't know much
about how OciParse works. I can connect successfuly, just my sql syntax is
pretty bad.

$conn = OCILogon($usern ame,$passwd,$db );

if ($conn) {

$sql = "


CREATE TABLE inventory
(
"item_id" numeric PRIMARY KEY auto_increment, ;
"transaction_id " numeric NULL,
"vendor_id" numeric NULL,
"user_id" numeric NULL,
"item_barco de" varchar(16) NULL,
"item_descripti on" BLOB NULL,
"item_purchase_ price" varchar(16) NULL,
"item_purchase_ date" date NULL,
"item_offer_pri ce" varchar(16) NULL,
"item_offer_dat e" date NULL,
"item_sold_pric e" varchar(16) NULL,
"item_sold_date " date NULL,
"item_locat ion" varchar(16) NULL,
"item_quant ity" varchar(16) NULL,
"item_shipping_ weight" varchar(16) NULL,
"item_statu s" varchar(16) NULL,
);

CREATE TABLE coin_singles
(
"item_id" numeric PRIMARY KEY,
"coin_grade_ser vices" varchar(32) NULL,
"coin_grade " varchar(32) NULL,
"coin_mint_date " varchar(16) NULL,
"coin_type1 " varchar(32) NULL,
"coin_type2 " varchar(32) NULL,
"coin_censu s" varchar(255) NULL,
"coin_issue_dat es" varchar(16) NULL,
"coin_title " varchar(255) NULL,
"coin_descripti on" BLOB NULL,
"coin_condition 1" varchar(32) NULL,
"coin_condition 2" varchar(32) NULL,
"coin_condition 3" varchar(32) NULL,
"coin_photo 1" varchar(32) NULL,
"coin_photo 2" varchar(32) NULL,
"coin_photo 3" varchar(32) NULL,

);

"

echo "<br>";
echo $conn;
echo "<br>";
echo $sql;
echo "<br>";

// parse SQL statement

$sql_statement = OCIParse($conn, $sql)
or die("Couldn't parse statement.");

echo $sql_statement;

// execute SQL query

OCIExecute($sql _statement)
or die("Couldn't execute statement.");


} else {

echo ("Connection failed!");

}

OCILogoff($conn );

?>

[gmuldoon]

Re: PHP/Oracle SQL statements, OciParse

In article <UYOWb.35$ao3.3 2634@news.uswes t.net>, pdheady@comcast .net
says...[color=blue]
> Ok I'm a newbie to using Oracle9i w/ PHP, been a MySQL guy... What is the
> proper sql statement to use insert tables, etc. as below.? I don't know much
> about how OciParse works. I can connect successfuly, just my sql syntax is
> pretty bad.[/color]

For starters, WHY, WHY, WHY would you want to run CREATE TABLE in a PHP
script? If I did that my Oracle Database Administrator would (rightly)
do nasty things to sensitive parts of my anatomy. You should be using a
purpose-built tool for creating database objects, then just create PHP
scripts to manipulate data in those objects.

If you're going to use Oracle and don't have access to a DBA, be
prepared to do LOTS AND LOTS of reading. Actually, even if you DO have
a DBA, that is the case.

Go to http://otn.oracle.com and register (free), then start with some of
the basic fundamentals and concept guides. Start with:
Oracle9i Application Developer's Guide - Fundamentals
[color=blue]
> $sql = "
> CREATE TABLE inventory
> (
> "item_id" numeric PRIMARY KEY auto_increment, ;
> "transaction_id " numeric NULL,
> "vendor_id" numeric NULL,
> "user_id" numeric NULL,
> "item_barco de" varchar(16) NULL,
> "item_descripti on" BLOB NULL,[/color]
<snip>

FWIW:
1. You haven't escaped the double-quotes around the column names in your
$sql definition, but in Oracle you shouldn't normally be using them
anyway.

2. Use "number" not "numeric" and set its size (and if required, its
precision), eg: item_price number(8,2).

3. Columns are by default nullable.

4. "auto_increment " doesn't exist in Oracle. Read up about sequences
(and probably about triggers - a combination of a sequence and a before-
insert trigger can simulate auto-increment).

5. Use "varchar2" instead of "varchar".

Example from the manual:

CREATE TABLE Emp_tab (
Empno NUMBER(5) PRIMARY KEY,
Ename VARCHAR2(15) NOT NULL,
Job VARCHAR2(10),
Mgr NUMBER(5),
Hiredate DATE DEFAULT (sysdate),
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(3) NOT NULL,
CONSTRAINT dept_afkey REFERENCES Dept_tab(Deptno ))

PCTFREE 10
PCTUSED 40
TABLESPACE users
STORAGE ( INITIAL 50K
NEXT 50K
MAXEXTENTS 10
PCTINCREASE 25 );

Good luck,

Geoff M

[Tom Thackrey]

Re: PHP/Oracle SQL statements, OciParse


On 12-Feb-2004, gmuldoon <gmuldoon_nospa m@scu.edu.au> wrote:
[color=blue]
> For starters, WHY, WHY, WHY would you want to run CREATE TABLE in a PHP
> script? If I did that my Oracle Database Administrator would (rightly)
> do nasty things to sensitive parts of my anatomy. You should be using a
> purpose-built tool for creating database objects, then just create PHP
> scripts to manipulate data in those objects.[/color]

There are many reasons to create tables within a script including installer
scripts, temp tables to hold results of complex, persistent, or snapshot
queries, per-user tables, tables for user-defined columns, and DBA scripts.
Wisely used dynamic table creation can be quite useful.

--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[Andy Hassall]

Re: PHP/Oracle SQL statements, OciParse

On Thu, 12 Feb 2004 12:45:20 -0500, "Philip D Heady" <pdheady@comcas t.net>
wrote:
[color=blue]
>Ok I'm a newbie to using Oracle9i w/ PHP, been a MySQL guy... What is the
>proper sql statement to use insert tables, etc. as below.? I don't know much
>about how OciParse works. I can connect successfuly, just my sql syntax is
>pretty bad.[/color]

On top of all the good advice posted by gmuldoon:
[color=blue]
>CREATE TABLE inventory
>(
> "item_id" numeric PRIMARY KEY auto_increment, ;[/color]

What's the semicolon doing there?

Also, avoid creating anonymous constraints, give them a name; e.g.
TABLENAME_PK for TABLENAME's primary key. Makes it that much easier to read
error messages and the data dictionary. So:

item_id number(9),

Then later, but befor the final close bracket:

CONSTRAINT inventory_PK
primary key (item_id)

Or do it separately with an ALTER TABLE afterwards.
[color=blue]
> "item_statu s" varchar(16) NULL,
>);[/color]

Extra comma at the end, remove it. You haven't closed your double quotes
either, but since you've got mismatched double quotes everywhere, PHP won't
even have parsed the page, let alone it getting to Oracle.
[color=blue]
> $sql_statement = OCIParse($conn, $sql)
> or die("Couldn't parse statement.");[/color]

You can get a lot more help from the database if you ask for it. Look up
OCIError. See the user contributed notes in the manual for an example of how to
get it to highlight the character where the error was found, if it's a parse
error.

You can download the docs at
<http://otn.oracle.com/documentation/oracle9i.html>, it's about 200M, or
there's a search engine at <http://tahiti.oracle.c om>.

There's also the comp.databases. oracle.server newsgroup, but it's not the
friendliest of groups; you're likely to be told in no uncertain terms to go
away and read the manual. (Which is fair enough).

--
Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>

[gmuldoon]

Re: PHP/Oracle SQL statements, OciParse

use.signature@n ospam.com says...[color=blue]
>
> On 12-Feb-2004, gmuldoon <gmuldoon_nospa m@scu.edu.au> wrote:
>[color=green]
> > For starters, WHY, WHY, WHY would you want to run CREATE TABLE in a PHP
> > script? If I did that my Oracle Database Administrator would (rightly)
> > do nasty things to sensitive parts of my anatomy. You should be using a
> > purpose-built tool for creating database objects, then just create PHP
> > scripts to manipulate data in those objects.[/color]
>
> There are many reasons to create tables within a script including installer
> scripts,[/color]

Before you can CREATE TABLE in Oracle, you have to CREATE USER, which
you have to do before you can get your OCI connect, so why not create
your schema objects when you create the schema? Can't see (with Oracle)
why you'd use PHP for installing anything at the database level.
[color=blue]
> temp tables to hold results of complex, persistent, or snapshot
> queries, per-user tables, tables for user-defined columns, and DBA scripts.[/color]

In later versions of Oracle, "materialis ed views", "global temporary
tables" and other tricks are available to achieve just about anything
you can do with "temporary" real tables.
[color=blue]
> Wisely used dynamic table creation can be quite useful.[/color]

I have yet to see a convincing case WITH RECENT ORACLE VERSIONS. Try
me.

Geoff M

[Tom Thackrey]

Re: PHP/Oracle SQL statements, OciParse


On 12-Feb-2004, gmuldoon <gmuldoon_nospa m@scu.edu.au> wrote:
[color=blue]
> use.signature@n ospam.com says...[color=green]
> >
> > On 12-Feb-2004, gmuldoon <gmuldoon_nospa m@scu.edu.au> wrote:
> >[color=darkred]
> > > For starters, WHY, WHY, WHY would you want to run CREATE TABLE in a
> > > PHP
> > > script? If I did that my Oracle Database Administrator would
> > > (rightly)
> > > do nasty things to sensitive parts of my anatomy. You should be using
> > > a
> > > purpose-built tool for creating database objects, then just create PHP
> > > scripts to manipulate data in those objects.[/color]
> >
> > There are many reasons to create tables within a script including
> > installer
> > scripts,[/color]
>
> Before you can CREATE TABLE in Oracle, you have to CREATE USER, which
> you have to do before you can get your OCI connect, so why not create
> your schema objects when you create the schema? Can't see (with Oracle)
> why you'd use PHP for installing anything at the database level.
>[color=green]
> > temp tables to hold results of complex, persistent, or snapshot
> > queries, per-user tables, tables for user-defined columns, and DBA
> > scripts.[/color]
>
> In later versions of Oracle, "materialis ed views", "global temporary
> tables" and other tricks are available to achieve just about anything
> you can do with "temporary" real tables.
>[color=green]
> > Wisely used dynamic table creation can be quite useful.[/color]
>
> I have yet to see a convincing case WITH RECENT ORACLE VERSIONS. Try
> me.[/color]

Because some of us like to run with multiple database support for
portability and flexibility.
Because not everyone likes to use 'tricks' tied to an expensive DBS.


--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[gmuldoon]

Re: PHP/Oracle SQL statements, OciParse

use.signature@n ospam.com says...
[color=blue][color=green][color=darkred]
> > > Wisely used dynamic table creation can be quite useful.[/color]
> >
> > I have yet to see a convincing case WITH RECENT ORACLE VERSIONS. Try
> > me.[/color]
>
> Because some of us like to run with multiple database support for
> portability and flexibility.
> Because not everyone likes to use 'tricks' tied to an expensive DBS.[/color]

As the OP was specifically coding using the OCI8 PHP library rather than
using ADODB or some other database abstractions layer, your point seems
moot.

Geoff M

[Tom Thackrey]

Re: PHP/Oracle SQL statements, OciParse


On 12-Feb-2004, gmuldoon <gmuldoon_nospa m@scu.edu.au> wrote:
[color=blue]
> use.signature@n ospam.com says...
>[color=green][color=darkred]
> > > > Wisely used dynamic table creation can be quite useful.
> > >
> > > I have yet to see a convincing case WITH RECENT ORACLE VERSIONS. Try
> > > me.[/color]
> >
> > Because some of us like to run with multiple database support for
> > portability and flexibility.
> > Because not everyone likes to use 'tricks' tied to an expensive DBS.[/color]
>
> As the OP was specifically coding using the OCI8 PHP library rather than
> using ADODB or some other database abstractions layer, your point seems
> moot.[/color]

The post I originally responded to did not quote that part of the original
post. My apologies for not keeping track of the entire thread.


--
Tom Thackrey

https://www.creative-light.com

tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@wil lglen.net (it's reserved for spammers)

[gmuldoon]

Re: PHP/Oracle SQL statements, OciParse

use.signature@n ospam.com says...
[color=blue][color=green][color=darkred]
> > > Because some of us like to run with multiple database support for
> > > portability and flexibility.
> > > Because not everyone likes to use 'tricks' tied to an expensive DBS.[/color]
> >
> > As the OP was specifically coding using the OCI8 PHP library rather than
> > using ADODB or some other database abstractions layer, your point seems
> > moot.[/color]
>
> The post I originally responded to did not quote that part of the original
> post. My apologies for not keeping track of the entire thread.[/color]

Apols not necessary. Cheers.

Geoff M

[Philip D Heady]

Re: PHP/Oracle SQL statements, OciParse

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_VAR S)) {
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><ti tle>etc...<body >etc...

[Andy Hassall]

Re: PHP/Oracle SQL statements, OciParse

On Fri, 13 Feb 2004 12:36:38 -0500, "Philip D Heady" <pdheady@comcas t.net>
wrote:
[color=blue]
>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_VAR S)) {
>print $key . " = " . $val . "<br>";
>}[/color]

What does that have to do with either Oracle or MySQL?
[color=blue]
>--------------------------------------------------------------------------
>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");[/color]

No, ora_do is based on the ancient OCI7 interface. Only use functions from the
OCI8 extension:

http://uk2.php.net/oci8

Avoid anything from the following page like the plague:

http://uk2.php.net/oracle

[ snip the rest since it's all using the wrong interface ].

The simplest way to fetch is using OCIFetchInto.

http://uk2.php.net/manual/en/function.ocifetchinto.php

The example in the manual misses out error checking. Don't miss that out.
[color=blue]
>--------------------------------------------------------------------------
>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><t itle>etc...<bod y>etc...[/color]

Presumably you have a bug in the code you haven't posted.

--
Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>

[Philip D Heady]

Re: PHP/Oracle SQL statements, OciParse

Ok thank you very much. Much appreciated!


"Andy Hassall" <andy@andyh.co. uk> wrote in message
news:gd5q20hlq3 mjrn6rd5a6nke09 opqv2asgm@4ax.c om...[color=blue]
> On Fri, 13 Feb 2004 12:36:38 -0500, "Philip D Heady" <pdheady@comcas t.net>
> wrote:
>[color=green]
> >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[/color][/color]
learn.[color=blue][color=green]
> >I'm starting to hate it after using MySQL!!
> >[/color]
>
>--------------------------------------------------------------------------[color=green]
> >1) Is there a similar statement using PHP/Oracle functions as below for
> >MySQL?[/color]
>
>--------------------------------------------------------------------------[color=green]
> >
> >while (list ($key, $val) = each ($HTTP_POST_VAR S)) {
> >print $key . " = " . $val . "<br>";
> >}[/color]
>
> What does that have to do with either Oracle or MySQL?
>
>
>--------------------------------------------------------------------------[color=green]
> >2) Should I be using something like this or is there easier way to pull[/color][/color]
data[color=blue][color=green]
> >into array?[/color]
>
>--------------------------------------------------------------------------[color=green]
> >
> > // Start new Oracle cursor and query
> >
> > $q = "select * from inventory";
> >
> > $ora_cur = ora_do( $ora_conn, $q ) or die("Couldn't setup Oracle
> >Cursor");[/color]
>
> No, ora_do is based on the ancient OCI7 interface. Only use functions[/color]
from the[color=blue]
> OCI8 extension:
>
> http://uk2.php.net/oci8
>
> Avoid anything from the following page like the plague:
>
> http://uk2.php.net/oracle
>
> [ snip the rest since it's all using the wrong interface ].
>
> The simplest way to fetch is using OCIFetchInto.
>
> http://uk2.php.net/manual/en/function.ocifetchinto.php
>
> The example in the manual misses out error checking. Don't miss that out.
>
>
>--------------------------------------------------------------------------[color=green]
> >3) For some reason my html below does not get displayed on the page.[/color][/color]
After[color=blue][color=green]
> >I submit the page is blank and just says database connected succesfully.
> >Why is it stopping here??[/color]
>
>--------------------------------------------------------------------------[color=green]
> >
> ><html><head><t itle>etc...<bod y>etc...[/color]
>
> Presumably you have a bug in the code you haven't posted.
>
> --
> Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
> <http://www.andyh.co.uk > / <http://www.andyhsoftwa re.co.uk/space>[/color]