473,390 Members | 1,212 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

PHP/Oracle SQL statements, OciParse

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);

?>
Jul 17 '05 #1
11 4204
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
Jul 17 '05 #2

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)
Jul 17 '05 #3
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>
Jul 17 '05 #4
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
Jul 17 '05 #5

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)
Jul 17 '05 #6
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
Jul 17 '05 #7

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)
Jul 17 '05 #8
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
Jul 17 '05 #9
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...


Jul 17 '05 #10
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>
Jul 17 '05 #11
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>

Jul 17 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Geoff Muldoon | last post by:
Hi all, Help please. Environment: Unix (Tru64) / Apache / PHP4.0.6 / Oracle8i Establish connection to Oracle: for ($I=0; $I<$max_attempts; $I++) {
0
by: Philip D Heady | last post by:
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...
9
by: Philip D Heady | last post by:
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!! ...
2
by: Kamal | last post by:
PHP 4.3.4 on Apache 2.0.48 on Linux Red Hat 8.0 Kernel 2.4.18-14smp with Oracle 9iR2. I have this code: <html> <body> <?php
11
by: Markku Uttula | last post by:
I think I'm doing something wrong. I'm able to connect to Oracle just fine, execute queries and all, but I'm having serious problems with the speed :( For example, the following PHP-script on my...
3
by: Keith E. Sauvant | last post by:
A behaviour we don't understand: +++ $user = 'xxx'; $password = 'xxx'; $database = 'xxx.xxx'; $query = 'SELECT 1 FROM DUAL'; $link = OCIlogon($user, $password, $database);
3
by: Tony Marston | last post by:
I am using PHP 4.4.4 with Oracle 10g Express Edition on Windows XP. I am trying to emulate in Oracle the SET datatype in MySQL and the ARRAY datatype in PostgreSQL I have the created a...
1
by: Steve1000 | last post by:
Hi, We're having problems connecting to an Oracle 9i db using PHP 5.2.1 and OCI8. Also, we are using IIS 5.1. PHP is working--we can see the info.php page. On the info.php page I see that...
0
by: *Davide* | last post by:
Hello, This query (PHP+Oracle) works: global $user,$pass,$sid; $db_charset = 'UTF8'; $db = OCILogon($user, $pass, $sid, $db_charset); $clob = OCINewDescriptor($db, OCI_D_LOB); $txt_clob =...
0
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,...
0
BarryA
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...
1
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...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
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,...
0
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...
0
Oralloy
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,...
0
jinu1996
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...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.