50647

selecting record from oracle

To select last record, I was using this when my database was MySQL:

$result = mysql_query("SELECT Id FROM test ORDER BY LENGTH(Id), Id ASC"); $count = mysql_numrows($result); if($count != 0) { $lastid = mysql_result($result,$count-1,"Id"); }

...and it works fine. But now my table is in an Oracle database - I wrote:

$stid = oci_parse($conn, "SELECT Id FROM test ORDER BY LENGTH(Id), Id ASC"); oci_execute($stid); $p = oci_parse($conn, "SELECT COUNT(ID) FROM test"); oci_execute($p); $count = oci_fetch_array($p); if($count[0] != 0) { $lastid = oci_result($stid, $count[0]-1); }

It doesn't work - I am not getting the last record.

Answer1:

If you read the documentation for oci_result, you'll find you're using the second parameter incorrectly.

The second parameter is what you use to retrieve the column - it can be the column name, or the ordinal value. Ordinal value is a number, starting at one (1) based on the columns specified in the SELECT clause. Ordinals aren't a recommended practice, because if the query changes -- if you forget to update the ordinal reference your retrieval of values is screwed. Effectively, the subsequent COUNT query returns a value higher than the number of columns in the first queries SELECT clause.

$stid = oci_parse($conn, "SELECT Id FROM test ORDER BY LENGTH(Id), Id ASC"); oci_execute($stid); $lastid = oci_result($stid, 1);

oci_result lacks the third parameter that mysql_result provides.

To make things work in Oracle, use:

$stid = oci_parse($conn, "SELECT x.id FROM (SELECT Id FROM test ORDER BY LENGTH(Id) DESC, Id DESC) x WHERE ROWNUM = 1"); oci_execute($stid); $lastid = oci_result($stid, 1);

The updated query will return one row, the latest based on reversing the ORDER BY you were using previously.

Answer2:

And what are you using this for? If it is to determine the next Id, then <strong>don't do this</strong>, but use a sequence instead. If not then you can use select max(id) from testid to get the maximum value of the column.

<strong>edit</strong>

First create a sequence to store the sequential number :

create sequence testid_seq;

Then when you insert a record, use

insert into testid(id, ...) values ("D111-" || testid_seq.nextval, ... );

And just fill in the dots with the info you want to insert.

Recommend

  • Insert Data in Oracle DB using PHP
  • Handle a PL/SQL function returning a record in PHP
  • Using oci_parse and oci_execute
  • oci_bind_by_name not working in PHP
  • unable to obtain stable firefox connection in 60 seconds
  • Execute Success but num_rows return 0 [PHP-MySQL]
  • How to export MS Access table into a csv file in Python using e.g. pypyodbc
  • Displaying Data From Multiple MySQL Tables
  • Converting query results into DataFrame in python
  • Android fill_parent issue
  • FileReader+canvas image loading problem
  • Pass value from viewmodel to script in zk
  • Using $this when not in object context
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Deselecting radio buttons while keeping the View Model in synch
  • Getting last autonumber in access
  • Counter field in MS Access, how to generate?
  • Finding past revisions of files in StarTeam w/ .NET SDK / C#
  • Java applet as stand-alone Windows application?
  • Incrementing object id automatically JS constructor (static method and variable)
  • Javascript Callbacks with Object constructor
  • How to check if every primary key value is being referenced as foreign key in another table
  • Can I have the cursor start on a particular column by default in jqgrid's edit mode?
  • jquery mobile loadPage not working
  • Do create extension work in single-user mode in postgres?
  • Jquery - Jquery Wysiwyg return html as a string
  • Return words with double consecutive letters
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Comma separated Values
  • How to delete a row from a dynamic generate table using jquery?
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • json Serialization in asp
  • SQL merge duplicate rows and join values that are different
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • How to stop GridView from loading again when I press back button?
  • LevelDB C iterator
  • Can't mass-assign protected attributes when import data from csv file
  • Reading document lines to the user (python)
  • How to load view controller without button in storyboard?