Simplicty and flexibility!


New Sample - Data Export in DQL, ExecDQL and with DataExport() explained!

Started by DataEase
You will need to Sign In to be able to comment on the Blog!

New Sample - Data Export in DQL, ExecDQL and with DataExport() explained!

Download Sample

It is no secret that data export which used to be so easy and natural in DFD has been a nightmare in DFW. Especially the DQL export.

If you do a manual export from a form, DFW is fantastic, but if you want to do it with programming i.e. DQL it has been hopeless.

It has been awkward and slow. 

In DE8 this has been rectified in many different ways and the best way to showcase it is via a Sample.


Firstly the Console. We use this for debuggin and messaging. In this sample we also manipulate labels directly in the form, but the console is a good way of doing it. If you can Show the console and move it outside of the DE8 window so you can monitor it as you progress.



In this sample we showcase two techniques:

1. ExecDQL
2. DataExport()

We have also included a "Traditional DFW DQL export example so you can compare.

ExecDQL

ExecDQL is DQL as a function and it will generate no output other than a export file if a file name is given as a parameter (number 5).

The export is ~ seperated DFW standard, but it is without column name headers, but we will show how to get around this in the sample.

We create the DQL in our ExecDQLStore form.

define "retval" text .
retval := ConsoleCopy(concat("Export started: ", current time),1) .
retval := SetLabelText("Status1",concat("Export started: ", current time)) .
for Addressbook ;
list records
Company ;
Name ;
Address ;
Town ;
Postcode ;
Country ;
Phone ;
Email ;
Web ;
Contact .
end
retval := ConsoleCopy(concat("Export end: ", current time),4) .
retval := SetLabelText("Status11",concat("Export ended: ", current time)) .
.
As we see, it is just a normal DQL that list all the data in our Addressbook table.

What we want to do now is to add the header to the export file. The function to that looks like this:

retval := WriteToFile("Company~Name~Address~Town~Postcode~Country~Phone~Email~Web~Contact",data-entry field1,5) .
It would be tempting to add it to the end of the previous DQL and we would be all done, but that won't work. DataEase keep the file open and locked exclusive till it has finished with it and that is when the DQL is completely finished, so if we want to manipulate it we have to do that after the first DQL is finished.

So we simply make another DQL (Number 5) in our ExecDQL Store.

define "retval" text .
retval := WriteToFile("Company~Name~Address~Town~Postcode~Country~Phone~Email~Web~Contact",data-entry field1,5) .
This nifty little file manipulation function enables us to insert the header at the top of the file ;-) It has switches for inserting, overwriting, deleting, appending with and without line feed...

Now we have solved both issues we simply want to join the. In traditional DQL that would be Run Procedure, but that is still a DFW Trad functions so we need to call these ExecDQLs from another ExecDQL like this:

define "retval" text .
retval := MemoExecDQL(Any ExecDQLStore with DQL Number = 3 DQL,"","","","",data-entry field1) .
retval := MemoExecDQL(Any ExecDQLStore with DQL Number = 5 DQL,data-entry field1,"","","","") .

This also saves us specifying the file name both as a data-entry parameter and the filename, as we simply move it around inside the new DQL.

Now we only have to call it from the Form and voila...

The calling can be done many ways, but this time we decided to use OML (Future Event DQL) just for the fun of it.

We put the DQL on the Clicked event of the button.

If the Action don't work, simply go in and recompile it. I had some trouble that we will look into, but it should work fine.

Now I have to go and finish my breads that are in the oven, but I will come back and finish the write up later.

Enjoy!


Written by DataEase 25/07/13 at 18:24:45 Dataease [{8}]FIVE