UPDATING WITH DATABASE QUERY IN ORAD MAESTRO

•

In this example we will upgrade a Query (request) to a server or an Excel file. In my case I will on an Excel file, but how to do it for a DB on a server would be the same.

To start we create an Excel file. This file will have three columns, one entitled '[NOM_EQUIP]', another '[NUM_CHILD]' and the last '[COMPE]'.

For this work they asked us to create a list of football teams, and to select them according to the competition that teams played.

NOM_EQUIP = name of the football team
NUM_CHILD = is assigned a number of 'Single Child Mode' tab in the 'Object' of Orad 3Designer program. According to his position as a son of the father who take this export
COMPE = competition to play

Of scene Orad 3Designer we get exported the 'No Child' found under the button 'Single Child Mode' of the parent layer. And we do not need anything else to this example.

In Orad Maestro we begin with the tab 'Page Editor', we import Orad 3Designer scene, drag the only export that comes within the window 'SubPage 1', which may change later.

We will assign to be 'Drop down list', here we will draw the list of teams from the ‘Query Result’ list of Excel, and then we will name those that match the 'Query'.

Then we create another 'Drop down list' from the tab 'Controls'. We put, in this example, the name 'ShortName' and we wrote the competitions we want to ask, with the values 1, 2, 3, etc ....

I will not explain how to associate an Excel document or BBDD to Orad Maestro, I assume that we know this.

A 'Data Source' will call 'Programació' and 'Query Name', 'llista_escuts'.

Once the Excel associated Orad Maestro will explain the options that will create Query.

Here if you have knowledge of MySql will be of great help.

Tabs:

- Quey Parameter:

In this tab there is one key for this to work.

By default nothing comes out, it is empty. In this example we have added a parameter that need to connect the internal consultation (from Orad Maestro) to Excel.

The parameter name added must be the same as the name of the export of 'SubPage 1' window. As the consultation will come from the 'drop down list' that has the name 'ShortName', this is the name that will write.

Default is assigned 1. If the value (Value) in the tab 'Query Result' will give you the results that match this value is varied.

- Query Conditions:

It is the tab where we see if it meets one or more conditions.

The condition is a comparison between the column of Excel with the data that come to us from Orad Maestro.

In our case, we will compare the [COMPE] column with the 'ShortName' value.

If certain feature matches will be fulfilled.

- Query Order:

One of the features is the order as we show you the result of the request.

In this example, we want to teach the teams that match the condition.

The column where the names are called [NOM_EQUIP] and order to teach it be ascending [ASC].

- Query Columns:

When we connect the Excel Master, it will analyze how many pages, tables and columns exist in the document.

In the right column you can will see the columns of Excel to which you will do the consultation.

Remember that in our example are [NOM_EQUIP], [NUM_CHILD] and [COMPE].

- Query Editor:

If we have knowledge of MySql we could write anything we have done before without having to go through the previous tabs.

What I have found is that there are variables of this language that does not recognize Orad Maestro.

What remains is something like this:

SELECT TOP 100 [LLIGA$].[NOM_EQUIP], [LLIGA$].[NUM_CHILD], [LLIGA$].[COMPE]

FROM [LLIGA$]

WHERE [LLIGA$].[COMPE] = %ShortName%

ORDER BY [LLIGA$].[NOM_EQUIP] ASC

- Query Result:

In this tab we will see the result of all queries that do.

If there is anything we do wrong, we indicate here (though not help much, the message does not usually put where is the error).

Now we go with the instructions in the 'Time Line' of 'PageEditor'.

In the first line we will open a 'TAKE' - 'CUE' I like to start well, but as this is not important that each start with what you like.

In line with the 'CUE', the first icon is a 'Activate' scene, the second 'Script event' and finally 'Send export'.

In 'Send export' select all exports of scene Orad 3Designer.

The icon for the 'Script event' select the 'Script' tab that will allow us to write code.

According to the documentation Orad Maestro, to query the document Excel or a database has to be written:

Datasource.updataQuery(‘Data source’,’Query name’);

return true;

Well, in our example we must remember that 'Data source' has the 'programacio' nomenclature and that 'Query name' what we called 'llista_escuts'. The thing is us:

Datasource.updataQuery(programacio,llista_escuts);

return true;