Simplicty and flexibility!


MemoExecDQL


Started by afonso santos
Search
You will need to Sign In to be able to add or comment on the forum!

MemoExecDQL

Hi all,

I've a button with a execute Function/derivation with a set of instructions as follows :

if ( cod1 not = blank and cod11 not = blank, setstate ( "b_geracao", 2) + setValue("DQLNr","432")+Wait(0.1)+MemoExecDQL(DQL,cod1 ,cod11,"","","")+ wait (0.1 ) + setValue("DQLNr","431")+Wait(0.1)+MemoExecDQL(DQL,cod1 ,cod11,"","","")+ wait (0.1 ) + setValue("DQLNr","433")+Wait(0.1)+MemoExecDQL(DQL,cod1 ,cod11,"","","")+ wait (0.1 )+ setValue("DQLNr","434")+Wait(0.1)+MemoExecDQL(DQL,cod1 ,cod11,"","","")+wait (0.1) + ExecDQL(concat("modify records in t_instrucoes_principal_header with codigo_interno_MASTER =", chr(34), cod1, chr(34) ,"seleccao :=","no","."),"","","","","") + refreshform () +setstate ( "b_geracao", 3) + setValue("DQLNr","444")+Wait(0.1)+MemoExecDQL(DQL,"" ,"","","","") ,Alert (" Definir artigos base e para duplicação !" ))

As an example please check the DQL code 432 :

define "maior" numeric string 4 .

define "act" text .

define "nome" text 75 .

delete records in t_instrucoes_principal_tmp .

act := wait (0.1 ).

delete records in t_instrucoes_principal_header_tmp .

act := wait (0.1 ).

delete records in t_instrucoes_montagem_tmp .

act := wait (0.1 ).

delete records in t_Instrucoes_a_duplicar .

act := wait (0.1 ).

maior := highest of t_Instrucoes_PRINCIPAL_header sequencia .

nome := any t_artigos named "c2" with ( codigo_interno_master = data-entry field2 ) identificacao artigo .

for t_Instrucoes_PRINCIPAL_header with codigo_interno_MASTER = data-entry field1 and seleccao = yes ;

enter a record in t_Instrucoes_PRINCIPAL_header_tmp

copy all from t_Instrucoes_PRINCIPAL_header .

enter a record in t_Instrucoes_a_duplicar

DocumentoID := t_Instrucoes_PRINCIPAL_header DocumentoID;

codigo_interno_MASTER := data-entry field1 .

end

for t_Instrucoes_PRINCIPAL_header_tmp with codigo_interno_MASTER = data-entry field1 ;

maior := maior +1 .

modify records

codigo_interno_MASTER := data-entry field2 ;

sequencia := maior ;

seleccao := no ;

sequencia_numero := maior ;

utilizador := current user name ;

data_alteracao_registo := current date ;

hora_alteracao_registo := current time .

end

the others DQL codes are quite similar without the "delete records" at the beggining.

Problem:

Somethimes these  instructions work fine mainly in standalone mode, but in an multiuser environement often I get errors and the results are not the expected ( blank fields on several records ) .

What am I doing wrong?

Thanks in advance


Written by afonso santos 22/10/19 at 01:05:41 Dataease [{8}]FIVE

Do and don't when using ExecDQL, MemoExecDQL, delete records in etc.

Hi Afonso.


Nobody can say that you haven't embraced the new ideas in DE8 ;-)

The DE8 project was very much about "releasing" DataEase from its constraints and as such it has been a learning experience for all of us.
The development - like most DataEase applications - had an evolutionary approach where we kind of amended and added to existing functionality rather than rewriting the sofware from scratch.

A lot of the new features started of as a hunch more than a finalised feature and as such have had to go through its own evolutionary process.

ExecDQL was initially created to execute "quick" no output Execdql Scripts like execdql("Delete Records in MyTable.").

To use the cumbersome DQL document to execute the simplest of scripts seemed ...just wrong. You could of course have done this in OML but that was so unstable at the time that most people would just avoid it outright.

I guess some of you still remember how the DQL used to maximize, minimize, open and close, switch on and off toolbars etc... Not very elegant.

So a lot of the ideas in DE8 was how to be able to do things in "normal" DataEase fashion in an elegant way.

This precipitated functions like the SetColor(), SetStyle(), SetValue(), SetFocus()...most of them things that would be possible - if cumbersome - in OML if it had just worked and had been trusted.

The first problem with ExecDQL("Script") was " and the limit of arguments that is 255.

This precipitated StringEscape() that was included as standard in all string functions.

You could now write a DQL more or less directly in a derivation:

execDQL("define /'myvar/' text . etc.

But it was still cumbersome to write longer DQL's and anyhow they were limited to 255.

So next step was to find a way to execute bigger than 255 and back then that was Memo so then MemoExecDQL() arrives.

As it is not practical to store the DQL in each record a virtual memo (also a new invention in 8.x) solves this problem and SetValue() help us re-use the virtual memo field to get different DQL's stored in a related table ex. called DQLStore.

As as band aid to another problem - functions being called in random order when chained together on a button etc - wait() has been developed to force the order.

And now we have finally arrived at your DQLs ;-)

All the new features can be used to your benefit or they can work against you, and I fear that in this case they work against you.

After almost 40 years with DataEase a lot of us has a become very good in "fighting" DataEase's peculiarities and inconsistencies so much that when they are finally resolved we still kind of go on fighting against them.

For instance the idea with Wait() was that the problem with chained functions/actions was that they didn't have the time to fire before the next message was sent to the windows bus, but the way that Wait() was implemented didn't really resolve this....what it did resolve though was that it forced the order of arguments so the length of the wait doesn't matter because it won't finish the first function until the wait() is over but it will stop the second to fire until the first is finished which is why we do wait(0.1).

The important thing was that it worked...

define "maior" numeric string 4 .

define "act" text .

define "nome" text 75 .

delete records in t_instrucoes_principal_tmp .

act := wait (0.1 ).

delete records in t_instrucoes_principal_header_tmp .

act := wait (0.1 ).

delete records in t_instrucoes_montagem_tmp .

act := wait (0.1 ).

delete records in t_Instrucoes_a_duplicar .

act := wait (0.1 ).

The code above should be without wait as commands inside a DQL is "old style" i.e. not windows and hence are called in sequential order. The wait doesn't do any harm here but its not required.

So the rules are:
1. In a derivation or an action on a button DataEase send messages on the windows bus and hence the order of things are not guaranteed.
2. In DQL script functions and commands are executed in order and hence you can use these features to your advantage depending on what you want.

So if you want things to be executed in a fixed order call them from a DQL.
So instead of your button Action.

if ( cod1 not = blank and cod11 not = blank, setstate ( "b_geracao", 2) + setValue("DQLNr","432")+Wait(0.1)+MemoExecDQL(DQL,cod1 ,cod11,"","","")+ wait (0.1 ) + setValue("DQLNr","431")+Wait(0.1)+MemoExecDQL(DQL,cod1 ,cod11,"","","")+ wait (0.1 ) + setValue("DQLNr","433")+Wait(0.1)+MemoExecDQL(DQL,cod1 ,cod11,"","","")+ wait (0.1 )+ setValue("DQLNr","434")+Wait(0.1)+MemoExecDQL(DQL,cod1 ,cod11,"","","")+wait (0.1) + ExecDQL(concat("modify records in t_instrucoes_principal_header with codigo_interno_MASTER =", chr(34), cod1, chr(34) ,"seleccao :=","no","."),"","","","","") + refreshform () +setstate ( "b_geracao", 3) + setValue("DQLNr","444")+Wait(0.1)+MemoExecDQL(DQL,"" ,"","","","") ,Alert (" Definir artigos base e para duplicação !" ))

You call all your MemoExecDQLs from another MemoExecDQL.

Action:
if ( cod1 not = blank and cod11 not = blank, setstate ( "b_geracao", 2) + setValue("DQLNr","499")+Wait(0.1)+MemoExecDQL(DQL,cod1 ,cod11,"","",""),Alert (" Definir artigos base e para duplicação !" ))

DQL 499:

define "retval" text .
retval := MemoExecDQL(any DQLStore with DQLID=431 DQL , data-entry field1 ,data-entry field2,"","","") .
retval := MemoExecDQL(any DQLStore with DQLID=433 DQL , data-entry field1 ,data-entry field2,"","","") .
retval := MemoExecDQL(any DQLStore with DQLID=434 DQL , data-entry field1 ,data-entry field2,"","","") .
retval := ExecDQL(concat("modify records in t_instrucoes_principal_header with codigo_interno_MASTER =", chr(34), cod1, chr(34) ,"seleccao :=","no","."),"","","","","") + refreshform () +setstate ( "b_geracao", 3) .
retval := MemoExecDQL(any DQLStore with DQLID 444 DQL) .


If for some reason the order of things should still be jumbled you can even call the next MemoExecDQL from the tail of the previous.

for t_Instrucoes_PRINCIPAL_header_tmp with codigo_interno_MASTER = data-entry field1 ;

maior := maior +1 .

modify records

codigo_interno_MASTER := data-entry field2 ;

sequencia := maior ;

seleccao := no ;

sequencia_numero := maior ;

utilizador := current user name ;

data_alteracao_registo := current date ;

hora_alteracao_registo := current time .

end

retval := MemoExecDQL(any DQLStore with DQLID=433 DQL , data-entry field1 ,data-entry field2,"","","") .


Finally there is a problem that is very much related to single user vs. multi-user and that is 

Delete records in Tablename.

This is a brilliant and quick function to delete all the data in a table but it is really a "developer" feature for testing rather than a deployment function as it will only work if the table is 100% unused.

So even if someone has opened this table and show a record on scree it will just fail.

So something like
delete record in Table1 .
for Table_1 ...

can stop this function from working but in practice it should be avoided in all code that is to be used in a production scenario.

Delete records in Table with... is OK as it will filter and delete records after a query.

However the SAFEST way to delete records (but also the slowest).

if 
For Table1 ;
delete records.

This will delete record by record.

So the advice here is to put all your delete records in on DQL and then I would split up the DQLs that enter data in a table and the ones that modify the data your just created. 

for t_Instrucoes_PRINCIPAL_header with codigo_interno_MASTER = data-entry field1 and seleccao = yes ;

enter a record in t_Instrucoes_PRINCIPAL_header_tmp

copy all from t_Instrucoes_PRINCIPAL_header .

enter a record in t_Instrucoes_a_duplicar

DocumentoID := t_Instrucoes_PRINCIPAL_header DocumentoID;

codigo_interno_MASTER := data-entry field1 .

end
------ SPLIT HERE ---- 

for t_Instrucoes_PRINCIPAL_header_tmp with codigo_interno_MASTER = data-entry field1 ;

maior := maior +1 .

modify records

codigo_interno_MASTER := data-entry field2 ;

sequencia := maior ;

seleccao := no ;

sequencia_numero := maior ;

utilizador := current user name ;

data_alteracao_registo := current date ;

hora_alteracao_registo := current time .

end


DataEase create a advanced view based on your script and you get very impressed when you look at them in memory but as with most advanced things - the more complex the more likely you get unwanted results.

So the general rule:

When do processing:

don't combine delete, enter and modify in same DQL script if not as part of the same loop.

For table with this and that ;
if a=b then
delete records. etc
end
if b=c then
modfy records  etc
end
if c=d then
enter a record etc
end

Is OK but

for Table1 ;
delete records.
end
for Table1 ;
enter records 
end
For Table1;
modify records
end

is not a good idea.


Written by DataEase 25/10/19 at 10:58:17 Dataease [{8}]FIVE

Re:Do and don't when using ExecDQL, MemoExecDQL

Thanks for the great explanation

Some videos with real examples would be helpful as in the past with the old DW6.2 CD's

Fish4Support Videos were good but unfortunately not updated


Written by afonso santos 29/10/19 at 00:34:47 Dataease [{8}]FIVE

Re:Re:Do and don't when using ExecDQL, MemoExecDQL

Hi again Alfonso.

Later this week we "release" the first official pre-release version of DE9. From then on till full release it will all be about function and functionality.

DE9 Pre-release will be all about getting existing users to start benefiting from all the work gone into the DE9 development.

The main focus from next week onward will be to fully document and publish each new feature/functionality properly and this will also include new functionality implemented first in 8.x etc. It will be a massive task as DE8 to DE9 is the biggest development undertaken in DataEase history since DFW was released in 1995.

De9 is "hyper" interactive so we can integrate examples directly from the web without the users having to install anything on their DataEase.


It is hard to explain what a shift in technology and idea DE9 is from previous DataEase for Windows versions so only time and effort will tell how this can benefit users, developers and partners.


Written by DataEase 29/10/19 at 16:05:57 Dataease [{8}]FIVE