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($username,$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_barcode" varchar(16) NULL,
"item_description" BLOB NULL,
"item_purchase_price" varchar(16) NULL,
"item_purchase_date" date NULL,
"item_offer_price" varchar(16) NULL,
"item_offer_date" date NULL,
"item_sold_price" varchar(16) NULL,
"item_sold_date" date NULL,
"item_location" varchar(16) NULL,
"item_quantity" varchar(16) NULL,
"item_shipping_weight" varchar(16) NULL,
"item_status" varchar(16) NULL,
);

CREATE TABLE coin_singles
(
"item_id" numeric PRIMARY KEY,
"coin_grade_services" 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_census" varchar(255) NULL,
"coin_issue_dates" varchar(16) NULL,
"coin_title" varchar(255) NULL,
"coin_description" BLOB NULL,
"coin_condition1" varchar(32) NULL,
"coin_condition2" varchar(32) NULL,
"coin_condition3" varchar(32) NULL,
"coin_photo1" varchar(32) NULL,
"coin_photo2" varchar(32) NULL,
"coin_photo3" 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]

In article <UY****************@news.uswest.net>, pd*****@comcast.net
says...

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.
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
$sql = "
CREATE TABLE inventory
(
"item_id" numeric PRIMARY KEY auto_increment,;
"transaction_id" numeric NULL,
"vendor_id" numeric NULL,
"user_id" numeric NULL,
"item_barcode" varchar(16) NULL,
"item_description" BLOB NULL,

<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]

On 12-Feb-2004, gmuldoon <gm*************@scu.edu.au> wrote:

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.

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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)

[Andy Hassall]

On Thu, 12 Feb 2004 12:45:20 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote:

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.
On top of all the good advice posted by gmuldoon:
CREATE TABLE inventory
(
"item_id" numeric PRIMARY KEY auto_increment,;
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.
"item_status" varchar(16) NULL,
);
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.
$sql_statement = OCIParse($conn,$sql)
or die("Couldn't parse statement.");

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.com>.

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 <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

[gmuldoon]

us***********@nospam.com says...

On 12-Feb-2004, gmuldoon <gm*************@scu.edu.au> wrote:

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.
There are many reasons to create tables within a script including installer
scripts,

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.
temp tables to hold results of complex, persistent, or snapshot
queries, per-user tables, tables for user-defined columns, and DBA scripts.
In later versions of Oracle, "materialised views", "global temporary
tables" and other tricks are available to achieve just about anything
you can do with "temporary" real tables.
Wisely used dynamic table creation can be quite useful.

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

Geoff M

[Tom Thackrey]

On 12-Feb-2004, gmuldoon <gm*************@scu.edu.au> wrote:

us***********@nospam.com says...

On 12-Feb-2004, gmuldoon <gm*************@scu.edu.au> wrote:

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.

There are many reasons to create tables within a script including
installer
scripts,

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.

temp tables to hold results of complex, persistent, or snapshot
queries, per-user tables, tables for user-defined columns, and DBA
scripts.

In later versions of Oracle, "materialised views", "global temporary
tables" and other tricks are available to achieve just about anything
you can do with "temporary" real tables.

Wisely used dynamic table creation can be quite useful.

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

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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)

[gmuldoon]

us***********@nospam.com says...

Wisely used dynamic table creation can be quite useful.

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

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.

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]

On 12-Feb-2004, gmuldoon <gm*************@scu.edu.au> wrote:

us***********@nospam.com says...

> Wisely used dynamic table creation can be quite useful.

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

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.

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.

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
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to ja*********@willglen.net (it's reserved for spammers)

[gmuldoon]

us***********@nospam.com says...

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.

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.

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.

Apols not necessary. Cheers.

Geoff M

[Philip D Heady]

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...

[Andy Hassall]

On Fri, 13 Feb 2004 12:36:38 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote:

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>";
}
What does that have to do with either Oracle or MySQL?
--------------------------------------------------------------------------
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");
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.
--------------------------------------------------------------------------
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...

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

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

[Philip D Heady]

Ok thank you very much. Much appreciated!
"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:gd********************************@4ax.com...

On Fri, 13 Feb 2004 12:36:38 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote:

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>";
}

What does that have to do with either Oracle or MySQL?
--------------------------------------------------------------------------

2) Should I be using something like this or is there easier way to pull datainto 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");

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.
--------------------------------------------------------------------------

3) For some reason my html below does not get displayed on the page. AfterI submit the page is blank and just says database connected succesfully.
Why is it stopping here??

--------------------------------------------------------------------------

<html><head><title>etc...<body>etc...

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

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>