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
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 id10 ORDER BY field1 DESC) WHERE ROWNUM11 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
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 id10 ORDER BY field1 DESC) WHERE ROWNUM11 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]
