I understand that the .DBE file can be run using a CDF “Data Export” on a button action placed on the form itself.

But I want to run multiple exports using defined .DBE files from a procedure.

Is this possible?

Hi Henry.

I think we have discussed this somewhere before, but we would never like to miss an opportunity to clarify this again.

It is quite funny because in DFD you had imports but not exports, but then again you had the worlds best export generation tool (DQL) so there was no need.

As DQL in DFW was "useless" for Exporting the DBE was a life saver. However a DBE is not the direct opposite of a DBI and the reason for that is maybe for that exact reason.

DFW was created as an "interactive" tool where the foundation for everything is the MultiView.

A multiview is 100% dynamic and it is created every time you do a query in DataEase. A Multiview is a dyanmic join between data in many tables that is used to populate a form or a report.

When you use a FORM to create the multiview, you don't directly decide what columns and table to include as this is done by DataEase based on what columns/subforms/lookups/virtual fields etc is visible and dependant

   ex. if you view a record on a small screen the multiview will be different to viewing it on a big screen where more columns are visible!

So the more direct way to instruct a multiview is via DQL where you pick exactly which tables and columns to include.

Importing is simple, beacuse the only "rule" you can have is update/overwrite depending on unique keys, so what you do is simply add the new records to the table you have instructed in the DBI. 

Exporting via DBE however is quite different as you export the multiview currently open, so the location from where you fire the DBE decides what to export.

So if you create a DBE for a table and then query the table (search) before you hit the button that call the DBE you will ONLY export the data you can see in the form.

Likewise, you can use a DBE created for a different table when you export data as long as it has the same column names.

Since the DBE only export from the GUI Multiview you can't use it inside a DQL that doesn't print to screen. 

Yes, it is all very awkward.

This is why we made ExecDQL.

With ExecDQL you can export directly from a DQL to any file and format it exactly as you want. You can run many DQL's from one "control" DQL etc.

Hello Customer Care Team,

How about a sample script using ExecDQL ?

Regards

Henry

Download Sample
I know we keep on harping on this but DQL is DQL, syntax wise that is true at least.

A lot of you approach ExecDQL with somewhat of a "trepidation". 

DataEase is extreemly powerful and it has been unnecessarily "restricted" in DFW. 

The engine that execute the DQL is the same that execute all other queries in DataEase so why should it be restrained by being in a stored document.

ExecDQL is basically executing a DQL script on the fly, which means you can execute any function/command in DataEase when you need it and you can generate the format in realtime.

In Tradtional dataease there is no way you can for instance delete all the records in any table on the fly without having created the DQL in DT beforehand as the only way you could do it was via the DQL command "delete records in tablename ."

Now you can simply run it as execdql

On a button:
ExecDQL("delete records in data-entry field1 .", Myfield) -- myfield is a field in the form but could be GetVar("MyFieldValue") or anything else you are allowed in dataease programming in DT as well as RT.

in a DQL:

retval := ExecDQL(concat("delete records in ",Myvar),".") .

etc.

Everything in the future of DataEase will be oriented around execution of scripts (text: DQL,HTML,JavaScript) etc. Nothing will be compiled or stored as Binary files.

ExecDQL is just a small beginning where the next step is DataEase Server/WebForms etc.

Anyhow, back to the current problem.

There is no wizzardry to using ExecDQL for exporting.

In "basics" it is like DFD DQL the only difference is how you present it with the Script and the Format etc.

In MemoExecDQl you have to serve the Function with a Memo that contain the DQL Script and a memo that contain the output formatting.

The DQL is simply a normal DQL and the layout is like a DFD layout. It is completely FREE as long as you follow the rules of levels (grouping etc).

The DQL is not case sensitive but the Layout tags are so if you use "Customer" in list records you need to use [{Customer}] in layout too.

If you don't include a layout in the function it will use the standard DataEase export format which happens to be exactly as the one we have created. You can however create any export format or any other output you like.

You can of course also export to a PDF/Output to a Memo or to a web page.

Our DQL that fire the 3 (exactly similar but that is just out of lazyness) DQLs is the one below.

define "retval" text .

define "starttime" time .

starttime := current time.

-- this DQL could of course also have just been a ExecDQL that would have made it even sleaker but we wanted to draw the connection back to tradtional DQL which is why we fire it from here.

Message concat(data-entry directory,"\7.txt") .

retval := MemoExecDQL(any ExecDQLStore with DQL Number = 007 DQL,"","","","",concat(data-entry directory,"\7.txt"),any ExecDQLStore with DQL Number = 007 Layout) .

Message concat(data-entry directory,"\8.txt") .

retval := MemoExecDQL(any ExecDQLStore with DQL Number = 008 DQL,"","","","",concat(data-entry directory,"\8.txt"),any ExecDQLStore with DQL Number = 008 Layout) .

Message concat(data-entry directory,"\9.txt") .

retval := MemoExecDQL(any ExecDQLStore with DQL Number = 009 DQL,"","","","",concat(data-entry directory,"\9.txt"),any ExecDQLStore with DQL Number = 009 Layout) .

message concat("Exported via 3 different ExecDQL's all data in Addressbook. Total time: ",current time-starttime, "second(s).") window .

In the sample we have showcased how you can call the DQL's from a traditional DQL, LabelExecDQL and MemoExecDQL.

We also show you how you can read the files back into a Memo with MemoReadFromFile().

Thank you for your prompt reply and sample. It looks quite good and exports really fast. Well done.

However, I do not see any mention of the “FORMEXP.DBE” file anywhere in the ExecDQL. Am I missing something or are you not using the dbe file anywhere? All the data seems to be exported using the basic “export to” command the traditional dql export.

I have to export all the fields in multiple tables and I didn’t want to go into each table and call up the export command and export from each table. I had hoped to create multiple dbe files and then run 1 dql that would run each dbe file in turn. I had thought something like:

Run export FORMEXP1.DBE

Run export FORMEXP2.DBE

Run export FORMEXP3.DBE

Run export FORMEXP4.DBE

Etc..

The reason why I wanted to do it using a dbe is because one of the tables has 254 fields in it and the client wants all the fields exported. It is a lot easier using the Export dbe, which selects all the fields automatically, than “export to” “.items” and listing all the fields individually.

I guess this is not possible?

Downlaod Revised Sample!

Hi.

Sorry for the "long winded" reply. The short answer to your question is.

NO.

You can't do what you want simply because .DBE doesn't work that way. It doesn't export a table but a memory view that need to exist.

So the answer is ExecDQL and I will try to use your "problem" to get the message across.

EXECDQL is not limited like traditional DQL.

"All the data seems to be exported using the basic ?export to? command the traditional dql export."

As this was obviously not intended as an insult we will chose to not treat it as one ;-)

This not even close to what is happening.

ExecDQL is freedom!!!

For 5 minutes forget about everything you think you know about DataEase - its strengths and weaknesses - and imagine that you can do anything you like.

Everything that you have been thought is a lie. DataEase is extreemly flexible and there is no restrictions on what you can do.

Lets revert to your goal.

Which we know now is to export all the data in an application.

So since ExecDQL can be created on the fly, why don't we just create that DQL and then run it on the fly?

We have all the information we need in the Status Table for Tables . (PS! Make sure you refresh it to get it accurate).

So the DQL we need to make is this one (To Automatically Generate the DQLs that will do the export).

define "vTableName" text 52 .

define "retval" text .

retval := execdql("For AutoDQL ; Delete records .") . -- delete the existing DQL's 

For DummyMemo ; -- we have created a dummy table with a memo and one record, this is because we need a Memory Memo to build the DQL in.

for $DeStat_TableInfo$

if TableName not = vTableName then

if vTableName not = blank then

retval := memocopy(DummyMemo Memo1,"./CR ",0) . -- terminate the previous DQL and write it to the table.

enter a record in AutoDQL

TableName := vTableName ;

DQL := DummyMemo Memo1 .

end

vTableName := TableName .

retval :=MemoCopy(DummyMemo Memo1 ,concat("for ", tablename, ";/CRList Records/CR "),1) . -- Start a New DQL for a new table.

else

retval :=memocopy (DummyMemo Memo1,";/CR ",0) .

end

retval :=Memocopy (DummyMemo Memo1,concat(FldName," "),0) . -- See how we add a space on each MemoCopy, This is due to a bug where the last character is trimmed away by MemoCopy and MemoMemoCopy. It is fixed in 8.6 but ...

message FldName .

list records

TableName ;

FldName .

end

end

retval := MemoExecDQL(any ExecDQLStore with DQL Number = 013 DQL , data-entry field1 ) . -- Now we have finished generating, so time to fire the DQL below.

define "retval" text .

For AutoDQL with TableName not="Users" ; -- we run the export for all but Users.

retval := MemoExecDQL(DQL,"","","","",concat(data-entry field1,"\",TableName,".txt")) .  -- Now we run all the Scripts we generated automatically

list records

TableName in order .

PS! In the previous version of the problem we showed you how you COULD write your own Layout/format (which we could have done automatically also of course) but you don't have to have a format. ExecDQL generate a native export format if you have list records which happens to be the standard DFW export "CSV" so the only thing we need to do is to fire the DQL and point it at a automatically generated file name.

Voila.

If you say this is the same as export to again we will not be much pleased....