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

gverge393
126111118
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)
begin
   declare retval char(12);
   try
     -- 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; 
end;

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)
begin
   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;
end;

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.

link

answered 12 Jun '14, 14:52

Mark%20Wilkins's gravatar image

Mark Wilkins
7.2k226133
accept rate: 26%

edited 24 Jun '14, 08:55

Alex%20Wong's gravatar image

Alex Wong
5.3k2488

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
1

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.

http://devzone.advantagedatabase.com/dz/webhelp/advantage11/master_rowid.htm

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

There is a also the ROWNUM() function.

http://devzone.advantagedatabase.com/dz/webhelp/advantage11/index.html?master_rownum.htm

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.

link

answered 12 Jun '14, 02:37

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

Jens Mühlenhoff
1.3k51037
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
1

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: http://devzone.advantagedatabase.com/forum/questions/3311/select-record-by-rowid

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

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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

Tags:

×325
×24

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.