mibuso.com

How to work with record-variables?

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

Discuss this How To here.

Thanks Kriki!

A real eye opener.

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

And the following only one!

I will keep it in mind from now on!

Thanks for a Good post, Kriki!

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

Thanks.

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.

Thank you, kriki!

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.

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");

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.


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


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.

Hi Kriki,

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

Dear all,

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




OR





Thanks with regards!

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:


Dear all, please CMIIW

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.

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.

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.