In VFP, a SQL statement can have a Where clause as in "Select * from Log Where Recno() = 248". Attempting this same statement in the Advantage Data Architect SQL Utility gives a Scalar Function not found error.

asked 11 Jun '14, 15:05

gverge393's gravatar image

accept rate: 100%

As @Jens mentioned in a comment, using the primary key should be the primary mechanism for identifying a row. And as he also pointed out, the ROWID is also a good choice (you could include the ROWID in the original SELECT statement).

But as an exercise just to see if I could get it to work, I wrote a couple of functions that could be used to return a specific record based on the physical record number. It uses the ROWID concept, which is very efficient. As a result it is reasonably efficient. It does not do a full table scan. For example, it runs at the same speed on a 1 million record table as a 10 record table. It constructs the ROWID given a table name and a record number. The function that gets the rowid prefix is rather horrific. I will post it and perhaps someone can suggest a much cleaner version of it. I am having trouble coming up with a good way to use EXECUTE IMMEDIATE that has a SELECT statement.

Here is the scary function for getting the ROWID prefix (the first 12 characters).

Revised: Updated to make use of Joachim's suggestion in the comment

Revision 2: Another incremental change based on Alex's comment

create function getrowidprefix(tablename string) returns char(12)
   declare retval char(12);
     -- major kludge alert.  This defies all that is good about programming.
     -- It raises an exception in an execute immediate statement in order to
     -- "return" the value from the select statement.  I'm sure there is a much
     -- simpler way to do this.  A cleaner way would be to use a temp table as
     -- an intermediate bucket to put the value.  
     execute immediate 'raise getrowid(1, (select top 1 rowid from ['+tablename+']))' ;
   catch getrowid
      retval = substring(__errtext, 1, 12);
   end try;
   return retval; 

The function that creates the entire ROWID value given a table name and record number then is:

create function createrowid(tablename string, recordnum integer ) returns char(18)
   declare s char(6);
   declare i integer;
   declare mimeindex integer;
   i = 1;
   s = '';
   -- Convert the record number (32-bits) to a 6 byte base64 value
   while i <= 6 do
      mimeindex = recordnum % 64 + 1; -- use 6 bits of the value
      s = substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', 
                    mimeindex, 1) + s;
      i = i + 1;
      recordnum = recordnum >> 6;  // shift right to next 6 bits
   end while;
   return getrowidprefix(tablename) + s;

The query to use those might look like this:

select * from mytable where rowid = createrowid('mytable', 5);

As mentioned before, it does not do a full table scan because it is a "simple" ROWID = VALUE expression. The creation of the ROWID has a fixed cost. You could also change it around to call the scary GETROWIDPREFIX function once up front if you were going to execute several queries against the table. And then pass the prefix value to the CREATEROWID function instead of the table name.


answered 12 Jun '14, 14:52

Mark%20Wilkins's gravatar image

Mark Wilkins
accept rate: 26%

edited 24 Jun '14, 08:55

Alex%20Wong's gravatar image

Alex Wong

This SQL might be faster since it doesn' open a cursor:

@sql= 'declare @rid string; '+
      '@rid = (select top 1 rowid from ['+tablename+']);'+
      'raise getrowid(1, @rid);';

(12 Jun '14, 23:59) Joachim Duerr

Good idea. I edited it to use your suggestion. It looks a lot cleaner too.

(13 Jun '14, 07:01) Mark Wilkins

That is very sneaky way of returning result from EXECUTE IMMEDIATE. It can be shorten even more by removing the intermediate string :)

execute immediate 'raise getrowid(1, (select top 1 rowid from ['+tablename+']))' ;
(13 Jun '14, 08:16) Alex Wong

Very nice. I edited it again.

(13 Jun '14, 08:23) Mark Wilkins

Use the pseudo column ROWID.

You can not get the n-th row, but you can get a specific row this way.

There is a also the ROWNUM() function.

To make it usable for getting the n-th row you have to use a subquery like this:

SELECT l.* FROM (SELECT ROWNUM() AS "Recno", Log.* FROM Log ORDER BY Date) l WHERE l.Recno = 248

It is important that you have a specific ORDER BY clause to avoid unexpected behavior.


answered 12 Jun '14, 02:37

Jens%20M%C3%BChlenhoff's gravatar image

Jens Mühlenhoff
accept rate: 18%

edited 12 Jun '14, 02:38

Then is the value returned by ROWNUM() or the ROWID pseudo column the same as that returned by the AdsGetRecordNum(Handle, ADS_RESPECTFILTERS, @RecordNum) function in the ADS Client API - assuming the same filters and order?

I'm calling the AdsGetRecordNum() function using the API, then using the returned value in a subsequent SQL query to set a CursorAdapter to the current record.

(12 Jun '14, 06:48) gverge393

ROWID returns a unique value for each row, containing a database id (6 digits), table id (6 digits) and the physical record number (6 digits). The values are base64 encoded. ROWNUM() returns the row number in the current result set, which might be inconsistent depending on dynamic/static cursors. To extract the physical row number from the rowid column, see my post here:

(12 Jun '14, 07:23) Joachim Duerr

As Joachim says, the ROWNUM() is assigned by the server and AdsGetRecordNum gets a client side record number. I would not count on them being the same.

You should be using the primary key (or any candidate key) to get a record in a WHERE clause anyway.

Selecting by a dynamically created number (such as ROWM()) or using a physical record number (like ROWNUM) is a last resort solution in my opinion.

(12 Jun '14, 07:56) Jens Mühlenhoff

There is a typo in my last comment, it should be "physical record number (like ROWID)" of course.

(13 Jun '14, 00:23) Jens Mühlenhoff
Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 11 Jun '14, 15:05

Seen: 13,008 times

Last updated: 24 Jun '14, 08:55

Advantage Developer Zone Contact Us Privacy Policy Copyright Info

Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.