How to work with record-variables (version 3)?

Administrator

How to work with record-variables?

http://www.mibuso.com/howtoinfo.asp?FileID=22

Discuss this How To here.

reijermolenaar

Thanks Kriki!

A real eye opener. =D> =D>

Never realised that the following code will do two select statements:

TheTable.GET(...);
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);

And the following only one!

TheTable.LOCKTABLE;
TheTable.GET(...);
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);

I will keep it in mind from now on!

Andwian

Thanks for a Good post, Kriki!

I want to ask you, what is meant by 'open a cursor in SQL'?

Thanks.

kriki

SQL Server works on sets of records and not record by record.
NAV on the other hand works record by record.
SQL must emulate the record by record approach of the native DB. How can SQL do that : by using cursors.
The problem with cursors is that they are very heavy for SQL because SQL has to maintain them while working on it. So if you can avoid it (by using FINDFIRST,FINDLAST,ISEMPTY) you should do it.
If you ask pure SQL specialists about cursors, they will say to you NEVER to use them because they are very bad for performance (and that is true), but NAV doesn't have any other possibility.

Raine

Thank you, kriki!

pdj

Great post Kriki.
I’ll start by saying I also learned something reading your entry. I didn’t know that a CLEAR(Rec) also respected InitValue properties. And that an array of temporary records only resulted in ONE temporary table. However; I have a FEW comment ;-)
I think you need to mention that your description of most of the different functions assumes that SQL is used. A lot of the explanations are not true for Native, but I guess you are only working with SQL by now :-)
You should also explain that when you say a function returns several records, it actually means that the SQL server returns them to the NAV client, which then caches them and hope you will ask for the next one without changing filters or key or locking first.

I found a few small mistakes in your list: (To the best of my knowledge - at least)
“FIND('>') / FIND('<'): This bases itself on the values in the primary key to get the next or previous record, it ALSO considering the filters. Better use NEXT or NEXT(-1).”
Should say “…values in the current key…”

“ISEMPTY: This is THE statement to use if you want to check if there is at least one record in the (filtered) table.”
Please note, that Native DB uses the current key, so please set a proper key first to be DB independent.

“WRONG: This will give you 1 record at a time. \IF TheTable.FIND('-') THEN\REPEAT\ ...\UNTIL TheTable.NEXT = 0;”
I’m not sure, but I don’t’ think so. At least it used to it returns less records than the FINDSET command, but the number of records used to be variable. I guess it depended on recordssize, but it was typically around 25 records. I’m quite sure it used to say this in the Client Monitor as well, but for some reason it doesn’t. Using the profiler it seems it is doing a TOP 1.

“CORRECT: This will give you the first N records in one go, and after that 1 by 1.\IF TheTable.FINDSET THEN\REPEAT\ ...\UNTIL TheTable.NEXT = 0;”
Again: No – I don’t think so. I tried doing a FIND(‘-‘) followed by 100 times NEXT. This resulted in a SELECT (without TOP or FAST) and four times a FETCH API_CURSOR. This is almost as I expected, but I don’t know how we get to see the actual SQL statements it executes.

“First and for all some general advice for writing to the DB\...”
You forgot the most important one: “Never ever do a COMMIT unless all data is consistent and it is acceptable that only the queued changes are committed, since an error (logical or physical) can appear half a millisecond later and thereby stopping the remaining of the code.”

“CORRECT in case most of the time you need to change the record to avoid a second SELECT in SQL..”
I guess you forgot the “IF to be changed THEN BEGIN” in the code below.

“Method 2:\// now loop the temptable. This code retrieves the record again (with EXCLUSIVE-LOCK!) and then changes it. If the record was changed between the first read and now, it will NOT generate an error on the MODIFY because you will have received the latest version.”
I really don’t like this one. You risk the conditions from the first loop isn’t fulfilled anymore and thereby making inconsistent data. You should at least repeat the “IF (Record has to be changed) THEN BEGIN” again.

“But in C/AL we can't use SQL-statements (or we must use ADO), so there is another way. I advice to always use this way to do some summing in C/AL.”
You have an confusing indention under your “IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Prod. Posting Group") THEN ;” All the lines below should be 4 chars to the left :-)

But besides these issues I think it is a very good walkthrough which I’ll show my co-workes as well. Keep up the good work. =D>

kriki

Thanks for your remarks.
I'll put them in the new version.
Under here I put my answers to your remarks.

I think you need to mention that your description of most of the different functions assumes that SQL is used. A lot of the explanations are not true for Native, but I guess you are only working with SQL by now :-)
=> CORRECT. It is specially for SQL. I wrote that in the title in the beginning but later I changed the title to make it somewhat shorter and forgot to put it in the text. I now put it in the text.

You should also explain that when you say a function returns several records, it actually means that the SQL server returns them to the NAV client, which then caches them and hope you will ask for the next one without changing filters or key or locking first.
=> CORRECT. In some places I didn't specifically write that

I found a few small mistakes in your list: (To the best of my knowledge - at least)
“FIND('>') / FIND('<'): This bases itself on the values in the primary key to get the next or previous record, it ALSO considering the filters. Better use NEXT or NEXT(-1).”
Should say “…values in the current key…”
=> I made that more clear

“ISEMPTY: This is THE statement to use if you want to check if there is at least one record in the (filtered) table.”
Please note, that Native DB uses the current key, so please set a proper key first to be DB independent.
=> CORRECT but The How To is specially for SQL.

“WRONG: This will give you 1 record at a time. \IF TheTable.FIND('-') THEN\REPEAT\ ...\UNTIL TheTable.NEXT = 0;”
I’m not sure, but I don’t’ think so. At least it used to it returns less records than the FINDSET command, but the number of records used to be variable. I guess it depended on recordssize, but it was typically around 25 records. I’m quite sure it used to say this in the Client Monitor as well, but for some reason it doesn’t. Using the profiler it seems it is doing a TOP 1.
=> I think it does, otherwise what is the reason of the existence of the FINDSET command (and why did they change the recordset from 500 to 50 in NAV2009?

“CORRECT: This will give you the first N records in one go, and after that 1 by 1.\IF TheTable.FINDSET THEN\REPEAT\ ...\UNTIL TheTable.NEXT = 0;”
Again: No – I don’t think so. I tried doing a FIND(‘-‘) followed by 100 times NEXT. This resulted in a SELECT (without TOP or FAST) and four times a FETCH API_CURSOR. This is almost as I expected, but I don’t know how we get to see the actual SQL statements it executes.
=> The actual SQL statement is "FETCH API_CURSOR". NAV opened a cursor at the beginning and now it is fetching the records 1 by 1. This means that NAV each time sends a command to fetch the next record and then SQL sends 1 record. With FINDSET, SQL sends immediately 500 (or 50) records on FINDSET command and after those record, it goes 1 by 1.

“First and for all some general advice for writing to the DB\...”
You forgot the most important one: “Never ever do a COMMIT unless all data is consistent and it is acceptable that only the queued changes are committed, since an error (logical or physical) can appear half a millisecond later and thereby stopping the remaining of the code.”
=> I found that sooooooo obvious I didn't write it..... But you are right!

“CORRECT in case most of the time you need to change the record to avoid a second SELECT in SQL..”
I guess you forgot the “IF to be changed THEN BEGIN” in the code below.
=> OOOOPPPPPPPPPPSSSSSS

“Method 2:\// now loop the temptable. This code retrieves the record again (with EXCLUSIVE-LOCK!) and then changes it. If the record was changed between the first read and now, it will NOT generate an error on the MODIFY because you will have received the latest version.”
I really don’t like this one. You risk the conditions from the first loop isn’t fulfilled anymore and thereby making inconsistent data. You should at least repeat the “IF (Record has to be changed) THEN BEGIN” again.
=> TRUE, Depending on the need it might be necessary to add the test again or not at all.

“But in C/AL we can't use SQL-statements (or we must use ADO), so there is another way. I advice to always use this way to do some summing in C/AL.”
You have an confusing indention under your “IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Prod. Posting Group") THEN ;” All the lines below should be 4 chars to the left :-)
=> Actually, the indentation is correct. The idea is that I try to use a key if it exists. If it doesn't exist, I try the second key. If that second key doesn't exist, never mind about keys. Just use the primary key.

I could also rewrite that part as follows, but I think the original is better:

tmpGLEntry.reset;

// try to get a good key for the filters
CASE TRUE OF
tmpGLEntry.SETCURRENTKEY("Gen. Bus. Posting Group"): BEGIN END;
tmpGLEntry.SETCURRENTKEY("Gen. Prod. Posting Group"): BEGIN END;
END;

// I filter on the records for which I want to group the records
tmpGLEntry.setrange("Gen. Bus. Posting Group",recGLEntry."Gen. Bus. Posting Group");

pdj

Thanks for the updates, but I still have a few comments:

>No. of records returned by FIND(‘-‘)
You might be right, but I will still claim that it used to do a FAST 25 in some older versions. :-)

kriki wrote:

=> The actual SQL statement is "FETCH API_CURSOR". NAV opened a cursor at the beginning and now it is fetching the records 1 by 1. This means that NAV each time sends a command to fetch the next record and then SQL sends 1 record. With FINDSET, SQL sends immediately 500 (or 50) records on FINDSET command and after those record, it goes 1 by 1.

Have you proved this by testing? What is wrong with my test with 100 x NEXT resulting in only four FETCH API_CURSOR?

kriki wrote:

Actually, the indentation is correct. The idea is that I try to use a key if it exists. If it doesn't exist, I try the second key. If that second key doesn't exist, never mind about keys. Just use the primary key.
…I could also rewrite that part as follows, but I think the original is better:

No, the indention it IS wrong. Otherwise the ”END” before the “UNTIL recGLEntry.NEXT = 0;” would be only 2 chars different and not 6 as now.
The code you made is fine, but the indention confused me a bit. If I only look at the indention (and ignore ; and BEGIN END) it actually only does something if none of the keys exists.

PS: My tests were done with NAV5SP1-Upd1.

Andwian

Hi Kriki,

Thank you again for your clear explanation. It really help. :thumbsup:

navuser1

Dear all,

Which will give the better performance ? Please explain if possible.

IF TheTable.FINDFIRST THEN
     BEGIN
       ....................;
       ....................; 
     END;

OR

IF TheTable.FIND('-') THEN
     BEGIN
       ....................;
       ....................; 
     END;

Thanks with regards!

Andwian

Hi navuser1,

Based on Kriki's post, I think it depends on the conditions.

FINDFIRST: Use it when you only need ONE record.
FIND('-'): Use it when you need the ENTIRE records, instead of only one. Hence, you MUST use it when using with loop.

If you want only check whether there are any records, prefer to use the best practice:

IF NOT TheTable.ISEMPTY THEN
     BEGIN
       ....................;
       ....................; 
     END;

Dear all, please CMIIW :oops:

navuser1

Andri Arie Wianto wrote:

Hi navuser1,

Based on Kriki's post, I think it depends on the conditions.

FINDFIRST: Use it when you only need ONE record.
FIND('-'): Use it when you need the ENTIRE records, instead of only one. Hence, you MUST use it when using with loop.

If you want only check whether there are any records, prefer to use the best practice:

IF NOT TheTable.ISEMPTY THEN
     BEGIN
       ....................;
       ....................; 
     END;

Dear all, please CMIIW :oops:

Dear Andri

FINDFIRST,FIND('-'),ISEMPTY are clear to me. But I have seen the code(given below) in the base database in many places. I think that the function FIND('-') is the best for that particular cases. Please CMIIW.

IF  TheTable.FINDFIRST THEN
         BEGIN
             ....................;
             ....................; 
         END;

pdj

navuser1 wrote:

FINDFIRST,FIND('-'),ISEMPTY are clear to me. But I have seen the code(given below) in the base database in many places. I think that the function FIND('-') is the best for that particular cases. Please CMIIW.

IF  TheTable.FINDFIRST THEN
         BEGIN
             ....................;
             ....................; 
         END;

Kriki clearly say you should use FINDFIRST to avoid generating a SQL cursor. What makes you say that FIND('-') is better?
Check the section called You want ONY the first/last record (if it exists), but NEVER more.

pdj

Andri Arie Wianto wrote:

FIND('-'): Use it when you need the ENTIRE records, instead of only one. Hence, you MUST use it when using with loop.

Which part of the You want ALL records in ASCENDING order section don't you agree with? And why??

DenSter

pdj wrote:

Andri Arie Wianto wrote:

FIND('-'): Use it when you need the ENTIRE records, instead of only one. Hence, you MUST use it when using with loop.

Which part of the You want ALL records in ASCENDING order section don't you agree with? And why??

Peter I think this is a translation issue, I really don't think they disagree with anything. Andri is from Indonesia and his (her?) English may not be as good as yours. The way I read it is "you must only use it when you need to loop through records", as opposed to needing only one record.

reijermolenaar

Hi Kriki,

I have a question about GET vs. FINDFIRST.

GET: This command is the best to use if you want to search the record using its primary key. You can also filter on the primary key and use FINDFIRST. The SELECT-statement sent to SQL is the same. But the GET requires less coding and is easier to read. No RESET-SETCURRENTKEYSETRANGE are needed. The GET does NOT EVEN consider them.

I have read somewhere that the the GET method only locks 1 record and the FINDFIRST method also locks the record before and the record after the fetched record.

Can you confirm this?

Best Regards,

pdj

DenSter wrote:

The way I read it is "you must only use it when you need to loop through records", as opposed to needing only one record.

That's also how I read it. My question is regarding the usage of FIND('-') instead of FINDSET.
I don't understand why anyone would use FIND('-') in these situations after having read Krikis description.

pdj

reijermolenaar wrote:

I have read somewhere that the the GET method only locks 1 record and the FINDFIRST method also locks the record before and the record after the fetched record.

I'm quite sure about the GET. But I would expect the FINDFIRST locks the found rec and the previus rec. Simple to ensure it still is the first rec in the range when comitting. I don't see why it should lock next rec as well. But you should test it and let Kriki know, so this point could be added to the recommendations.
Mark Brummel has made a few posts about locking in NAV right here.

DenSter

pdj wrote:

That's also how I read it. My question is regarding the usage of FIND('-') instead of FINDSET.

You weren't asking a question about FINDSET vs FIND('-'), you were correcting Andri. Andri was replying to a message by Navuser1, who was comparing FINDFIRST and FIND('-'), he wasn't saying not to use FINDSET. He was saying "if you are going to use FIND('-'), only use it when you need to loop". Whether to use FIND('-') or FINDSET was not being discussed.

pdj

DenSter wrote:

You weren't asking a question about FINDSET vs FIND('-')

Correct. I should have been clearer.

DenSter wrote:

Andri was replying to a message by Navuser1, who was comparing FINDFIRST and FIND('-'),

No, Navuser1 was asking if you should use FIND('-') or FINDFIRST when you need to find just one record.
Andri answered him correctly (FINDFIRST), but also mentioned that FIND('-') should be used when looping.
Navuser then quoted all of Andris answer and concluded he should use FIND('-') when needing to find just one record??

This frustraded me, and I tried to direct both of them to the sections in Krikis article describing where they each went wrong.

DenSter wrote:

Whether to use FIND('-') or FINDSET was not being discussed.

Correct. But I think it would be wrong to accept Navuser1's remark about using FIND('-') for loops.

I also agree that a lot of the confusion might be related to translation issues, mine included. We don't all speak nor write English flawlessly. :oops:

DenSter

And meanwhile, everyone means exactly the same I would LOVE to sit down with this group for beers

pdj

Oh yeah! I'm sure we could handle this in the popular beer summit style 8)

Administrator

How to work with record-variables (version 2)?

http://www.mibuso.com/howtoinfo.asp?FileID=22

Discuss this How To here.

kriki

pdj wrote:

>No. of records returned by FIND(‘-‘)
You might be right, but I will still claim that it used to do a FAST 25 in some older versions. :-)

kriki wrote:

=> The actual SQL statement is "FETCH API_CURSOR". NAV opened a cursor at the beginning and now it is fetching the records 1 by 1. This means that NAV each time sends a command to fetch the next record and then SQL sends 1 record. With FINDSET, SQL sends immediately 500 (or 50) records on FINDSET command and after those record, it goes 1 by 1.

Have you proved this by testing? What is wrong with my test with 100 x NEXT resulting in only four FETCH API_CURSOR?

The FAST is indeed old. Microsoft implemented it in the beginning, but it did not work out as expected. I don't know the details about it. But I know that if you have to loop FINDSET is better then FIND('-') except in some cases.

pdj wrote:

No, the indention it IS wrong. Otherwise the ”END” before the “UNTIL recGLEntry.NEXT = 0;” would be only 2 chars different and not 6 as now.
The code you made is fine, but the indention confused me a bit. If I only look at the indention (and ignore ; and BEGIN END) it actually only does something if none of the keys exists.

You're right. In my document it is correct (I checked my document). Now I noticed that while converting it in HTML, something went wrong.

kriki

navuser1 wrote:

Dear all,

Which will give the better performance ? Please explain if possible.

IF TheTable.FINDFIRST THEN
     BEGIN
       ....................;
       ....................; 
     END;

OR

IF TheTable.FIND('-') THEN
     BEGIN
       ....................;
       ....................; 
     END;

Thanks with regards!

To summarize all other posts (and my how-to) in 1 line for your question:
If you want 1 record (if it exists), use FINDFIRST. FINDfirst does NOT create a cursor in SQL. FIND('-') creates a cursor in SQL. Cursors ARE SLOW.

Luc_VanDyck

kriki wrote:

You're right. In my document it is correct (I checked my document). Now I noticed that while converting it in HTML, something went wrong.

I have corrected the indentation of the example code in this How To article.

kriki

pdj wrote:

I also agree that a lot of the confusion might be related to translation issues, mine included. We don't all speak nor write English flawlessly. :oops:

And even if we all did. About which English do we talk. A word can mean different things in different versions of English. Take a look at this one: http://www.mibuso.com/dlinfo.asp?FileID=1106. Specially the slides with the different meanings of words between UK and USA.

kriki

reijermolenaar wrote:

Hi Kriki,

I have a question about GET vs. FINDFIRST.

GET: This command is the best to use if you want to search the record using its primary key. You can also filter on the primary key and use FINDFIRST. The SELECT-statement sent to SQL is the same. But the GET requires less coding and is easier to read. No RESET-SETCURRENTKEYSETRANGE are needed. The GET does NOT EVEN consider them.

I have read somewhere that the the GET method only locks 1 record and the FINDFIRST method also locks the record before and the record after the fetched record.

Can you confirm this?

Best Regards,

I did some checking (after also rereading Mark's blog : http://dynamicsuser.net/blogs/mark_brummel/archive/2009/06/18/tip-7-sql-range-locks.aspx and http://dynamicsuser.net/blogs/mark_brummel/archive/2009/06/13/tip-6-find-locked-records.aspx ).

I did some tests with GET,FINDFIRST,FIND('=') and locking and I noticed ALL 3 behave the same way! There is no difference in locking between them. But remember that I used them to get exactly 1 record using the primary key.
Like Mark explains in his blogs, it is possible to lock a range of records, but it depends on the filters.

reijermolenaar

Myth busted!

They probably changed the locking meganism in the newer versions of NAV.
Good to hear.
Thanks!

Alex_Chow

Instead of saying WRONG or CORRECT, why not use EFFICIENT and INEFFICIENT?

Wrong is such a strong word for cases where the code needs to be as it is.

kriki

Alex Chow wrote:

Instead of saying WRONG or CORRECT, why not use EFFICIENT and INEFFICIENT?

Wrong is such a strong word for cases where the code needs to be as it is.

That is true, but sometimes you need something strong that remains in memory.
I have had an example some years ago: someone used always SETFILTER also when he could use SETRANGE (Native DB). The person remembered that one was more efficient than the other. But that person remembered (wrongly) that SETFILTER was more efficient then SETRANGE. And in reality it is SETRANGE that can be more efficient but never the other way around.