473,320 Members | 2,117 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,320 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 4199
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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.