[PHP-DB] Oracle oci8
Hello, I'm sure this question has been asked, but i was wondering if anyone has successfully gotten oci8 working with php on windows? I've been trying for quite awhile with no success. thanks for any help, Paul _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] oracle (oci8) intro
Hi there, I'm in a situation where I need to produce a small app on top of an Oracle server really quickly. I'm quite a seasoned developer, but have only experience with MySQL so far. It's my understanding that Oracle lacks the MySQL's "LIMIT" feature. Looking at the OCI section of the PHP manual, it also looks like there's no OCIDataSeek() or some equivalent. Since the app I need to build will be a standard report builder with paging, I need this functionality. What is the common way to achieve this? Always fetch all rows, cycling through the resultset, discarding the records that preceed the one I want to start displaying with, and quit when I reach the one where the page should end? Is there a PHP + OCI tutorial somewhere? I need an intro to Oracle, and I need it now. :( TIA [EMAIL PROTECTED] - And the eyes of them both were opened and they saw that their files were world readable and writable, so they chmoded 600 their files. - Book of Installation chapt 3 sec 7 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Oracle OCI8 and rowid
Hello, does anyone know how to write te result of the following SQL statement (I have problems with rowid): select rowid, field1 from table1 Thanks in advance Benoit NOSS - ([EMAIL PROTECTED]) (Bureau 226) IAURIF, Service MSI, 15 rue Falguiere, 75740 PARIS Cedex 15 FRANCE Tel : (+33) 1 53 85 78 04 Fax : (+33) 1 53 85 76 36 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] oracle (oci8) intro
Quoting Cynic <[EMAIL PROTECTED]>: > Hi there, > > I'm in a situation where I need to produce a small app > on top of an Oracle server really quickly. I'm quite a > seasoned developer, but have only experience with MySQL > so far. It's my understanding that Oracle lacks the > MySQL's "LIMIT" feature. Looking at the OCI section of > the PHP manual, it also looks like there's no > OCIDataSeek() or some equivalent. Since the app I need > to build will be a standard report builder with paging, > I need this functionality. What is the common way to > achieve this? Always fetch all rows, cycling through the > resultset, discarding the records that preceed the one > I want to start displaying with, and quit when I reach > the one where the page should end? > > Is there a PHP + OCI tutorial somewhere? > > I need an intro to Oracle, and I need it now. :( Thies has an Oracle/PHP tutorial online at http://conf.php.net/ which may be of some assitance. The LIMIT problem is a real bitch is Oracle. There are a few ways to get around it, the most obvious people use being ROWNUM. However, ROWNUM does not listen to sorting which makes life amusing. One option is to try a query like the following: "SELECT * FROM (SELECT field1, field2 FROM table WHERE id>10 ORDER BY field1 DESC) WHERE ROWNUM<11" which gives you 10 rows, but still leaves the question of paging behind unless you use between values. I can't say I've tried paging record sets though. Cheers, Graeme -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] oracle (oci8) intro
Here's what I'm using to do paged queries in Oracle: $min = minimum of range of records $max = maximum of range of records $field_list = the fields from the table separated by commas $table = the table from where you're selecting $where_clause and $order_by should be self-explanatory SELECT linenum, $field_list FROM (SELECT rownum AS linenum, $field_list FROM (SELECT $field_list FROM $table WHERE $where_clause ORDER BY $order_by)) WHERE linenum BETWEEN $min AND $max; I hope it helps, Anthony Carlos -Original Message- From: Graeme Merrall [mailto:[EMAIL PROTECTED]] Sent: Sunday, August 19, 2001 7:34 PM To: Cynic Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] oracle (oci8) intro Quoting Cynic <[EMAIL PROTECTED]>: > Hi there, > > I'm in a situation where I need to produce a small app > on top of an Oracle server really quickly. I'm quite a > seasoned developer, but have only experience with MySQL > so far. It's my understanding that Oracle lacks the > MySQL's "LIMIT" feature. Looking at the OCI section of > the PHP manual, it also looks like there's no > OCIDataSeek() or some equivalent. Since the app I need > to build will be a standard report builder with paging, > I need this functionality. What is the common way to > achieve this? Always fetch all rows, cycling through the > resultset, discarding the records that preceed the one > I want to start displaying with, and quit when I reach > the one where the page should end? > > Is there a PHP + OCI tutorial somewhere? > > I need an intro to Oracle, and I need it now. :( Thies has an Oracle/PHP tutorial online at http://conf.php.net/ which may be of some assitance. The LIMIT problem is a real bitch is Oracle. There are a few ways to get around it, the most obvious people use being ROWNUM. However, ROWNUM does not listen to sorting which makes life amusing. One option is to try a query like the following: "SELECT * FROM (SELECT field1, field2 FROM table WHERE id>10 ORDER BY field1 DESC) WHERE ROWNUM<11" which gives you 10 rows, but still leaves the question of paging behind unless you use between values. I can't say I've tried paging record sets though. Cheers, Graeme -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] oracle (oci8) intro
Hello, Anthony Carlos wrote: > > Here's what I'm using to do paged queries in Oracle: > > $min = minimum of range of records > $max = maximum of range of records > $field_list = the fields from the table separated by commas > $table = the table from where you're selecting > $where_clause and $order_by should be self-explanatory > > SELECT linenum, $field_list > FROM (SELECT rownum AS linenum, $field_list > FROM (SELECT $field_list > FROM $table > WHERE $where_clause > ORDER BY $order_by)) > WHERE linenum BETWEEN $min AND $max; I afraid that this doesn't work well with arbitrary queries. I tried this before and I recall there are problems with computed columns (COUNT(), SUM(), etc...). If I am not mistaken there is also the problem that Oracle truncates column names that are qualified with the table names. The right way to do that is using server side cursors, but I could not figure how to return to the client side, a server side cursor that I could use skip rows and get only those that I want. Regards, Manuel Lemos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] oracle (oci8) intro
That's interesting. I haven't had to do too many queries with lots of computed columns. I'll defer to you and double check my queries. On the other hand, I have not run into any problems with truncated column data. With regards to the server side cursors, why not send an anonymous PL/SQL block? I don't suppose that it has to be a stored procedure... Perhaps you're talking about the ability to output the result set from a PL/SQL block to PHP. That's a curious puzzle. I haven't given it much thought, mainly because I'm not too good at writing dynamic SQL in PL/SQL (which is even less arbitrary, I believe, than the code I wrote below), but what would happen if you built a PL/SQL table or array and bound that to a PHP variable? Have you ever tried this? It's nice to see someone with a lot of Oracle experience-- sometimes I think that this is only for MySQL users! Thanks, Anthony Carlos -Original Message- From: Manuel Lemos [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 21, 2001 3:03 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] oracle (oci8) intro Hello, Anthony Carlos wrote: > > Here's what I'm using to do paged queries in Oracle: > > $min = minimum of range of records > $max = maximum of range of records > $field_list = the fields from the table separated by commas > $table = the table from where you're selecting > $where_clause and $order_by should be self-explanatory > > SELECT linenum, $field_list > FROM (SELECT rownum AS linenum, $field_list > FROM (SELECT $field_list > FROM $table > WHERE $where_clause > ORDER BY $order_by)) > WHERE linenum BETWEEN $min AND $max; I afraid that this doesn't work well with arbitrary queries. I tried this before and I recall there are problems with computed columns (COUNT(), SUM(), etc...). If I am not mistaken there is also the problem that Oracle truncates column names that are qualified with the table names. The right way to do that is using server side cursors, but I could not figure how to return to the client side, a server side cursor that I could use skip rows and get only those that I want. Regards, Manuel Lemos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] oracle (oci8) intro
Hello, Anthony Carlos wrote: > > That's interesting. I haven't had to do too many queries with lots of > computed columns. I'll defer to you and double check my queries. On the If you haven't had any problems is because you did not try to use your row range fetching technique with computed columns. > other hand, I have not run into any problems with truncated column data. I guess that is just a visual problem because I no longer recall an example problem. Maybe it was a problem when selecting fields with the same name in different tables. > With regards to the server side cursors, why not send an anonymous PL/SQL > block? I don't suppose that it has to be a stored procedure... I don't recall what the problem was, but I tried to declare a server side cursor but I could not figure how to execute it and have it return a result set to PHP. Maybe it was a PHP limitation when dealing with the OCI API. > Perhaps you're talking about the ability to output the result set from a > PL/SQL block to PHP. That's a curious puzzle. I haven't given it much > thought, mainly because I'm not too good at writing dynamic SQL in PL/SQL > (which is even less arbitrary, I believe, than the code I wrote below), but > what would happen if you built a PL/SQL table or array and bound that to a > PHP variable? Have you ever tried this? No. I even don't know if PHP could do that. > It's nice to see someone with a lot of Oracle experience-- sometimes I think > that this is only for MySQL users! Actually I had a hard time to deal with Oracle (as most people) because I developed a PHP database abstraction named Metabase that among many other things has the ability to let the developers to specify a range of rows that are returned by a select query. Metabase works with many different databases and it supports query result row range clipping in all of them. In those that something like the LIMIT clause is not supported like with Oracle, the feature is emulated trasparently with client side row skipping. It's not very efficient but it allows you to write portable applications. Maybe later I find a more efficient way to do it without compromising the portability Metabase developers applications. I think there is a way to do it with Oracle extensions, but I need to try it first to tell if it works. If you want to know more about Metabase, you may donwload it for free from: http://phpclasses.UpperDesign.com/browse.html/package/20 Regards, Manuel Lemos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Oracle OCI8 and rowid
On Mon, Jan 22, 2001 at 01:38:22PM -0800, signup wrote: > Hello, > does anyone know how to write te result of the following SQL statement (I have >problems with rowid): > > select rowid, field1 from table1 rowid is an opaque datatype in php. you cannot "output" it but only use it for further processing using ocibindbyname(). tc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Oracle OCI8 and rowid
Try converting the ROWID to a character string first: select rowidtochar(rowid), field1 from table1 Now you can echo it out or do whatever you would do with a character string. There's also the Oracle function CHARTOROWID to go the other way around. signup wrote: > > On Mon, Jan 22, 2001 at 01:38:22PM -0800, signup wrote: > > Hello, > > does anyone know how to write te result of the following SQL statement (I have >problems with rowid): > > > > select rowid, field1 from table1 > > rowid is an opaque datatype in php. you cannot "output" it > but only use it for further processing using ocibindbyname(). > > tc > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- -- Harry N. C. Ng Office of Information Services Air Resources Board 1001 I Street E-mail: [EMAIL PROTECTED] P.O. Box 2815 Phone: 916-322-6201 Sacramento, CA 95812 FAX:916-327-0640 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
