HomeActive Query BuilderHow can I show the table's fields with daily-used text, ine query builder's central pane?

How can I show the table's fields with daily-used text, ine query builder's central pane?


Hello,

I'm developing a graphical query builder for marketing specialists. They would not like to read tables in its metadata fields name, so, instead of displaying "post_code", "firs_name", "earned_point" in a table's rectanglar presentation, they want to read common text labels like "post code", "first name", "earned point" in the table's rectangular presentation.

Besides, they want to internationalize these common text labels, this requirement makes it even more difficult to display only metadata column names in query builder.

Is there a way to map the metadata column names to a set of configurable text labels, and display these labels in the tables' rectangles, while still generating queries from constant metadata column?

Thanks a lot.
Rosière Fontaine | email
June 9 2009, 04:53 AM
Hi,

The option you are seeking for is called "Alternate names" in Active Query Builder.

To define alternate names, use the MetadataContainer.Edit method to call the Metadata container editor and enter alternate names for each required database object and field. To do this programmatically, you may use the AltName property of MetadataObject and MetadataField objects that are items of the MetadataContainer collection. Another way to define alternate names is to set them in the XML file that stores metadata information. You should use the "alt_name" sub-element of the "object" and "field" elements to define alternate names.

To activate usage of alternate names, you should set the UseAltNames property to True. Working with alternate names, Active Query Builder generates two different SQL texts: one for the user, that contain alternate names instead of real database objects. The end-user may edit this SQL text as usual and Active Query Builder will be able to parse it back to its visual representation correctly. The second SQL, that contains only real database names, is for database server execution. You should use the PlainTextSQLBuilder.SQL property to get the end-user SQL text with alternate names, and the QueryBuilder.SQL property to assign such SQL text after editing by the end-user. The QueryBuilder.SQLForServer property is intended to retrieve the SQL text with real database object and field names, that should be passed to the database server.

The ability to localize the component is already made. Please refer to http://www.activequerybuilder.com/localizer.html for details.
Sergey Smagin | email
35 mins since original post
Hello,

I tried the latest GeneralDemo. Im MainFrame.java,
I added a method to load a xml:
private void loadLanguageSettings(String xmlFilePath){


Helpers.localizer.loadLanguageFromFile(xmlFilePath);

System.err.println("Voici les langues disponibles: ");

for(String languageName : Helpers.localizer.languages){
System.err.println(languageName);
}

}


Then I tried to load a French language xml. I first used a relative path because I placed fr.xml under src/language_files/fr.xml, but I couldn't read it with

Helpers.localizer.loadLanguageFromFile("../language_files/fr.xml"), or "language_files/fr.xml", or "language_files/fr"

private void jMenuItemFrenchActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
loadLanguageSettings("c:\\fr.xml");
queryBuilder1.setLanguage("fr");
}


With an absolute path "c:\\fr.xml", I still could not find "fr" in localizer.languages.

Even with queryBuilder1.setLanguage("fr");
Helpers.localizer.setLanguage("fr"); I could not find French settings.
Rosière | email
1 day, 1 hour since original post
Hello,

Thank you very much for your help.

I got French settings by <language id="fr">.

I would now like to know if it is able to internationalize the alternate names, and how to implement such an i18n.

If I load alternate names from schema's xml, I would have to create as many xml files as the number of different languages. And I would need a mechanism, to create a XML containing alt_name from a database schema, and when creating such a XML, these alt_name (such as "stadt" in German for a table's field actually named "city" in English) must be available and organized in a way that is readable by a Java program.


If I add the alternate names with MetadataContainer methods I will also find a way to stock and represent the aliases such as "stadt" in German for "city" in English. What is the best way to stock and represent them so that a query builder can read them as conveniently as possible?

Rosière | email
1 week, 1 day since original post
I tried to write <alt_name> elements in my Oracle schema's XML. Then I named some alternative names in French,

For example:
<field>
<name>
<item case_sens="1" name="ID_ORDER"/>
</name>
<alt_name>Numéro de commande</alt_name>

I found that Query Builder didn't recognize the accentuated letters like "é":
It created "Numéro de commande".

So should I write my xml with some encoding options?


Besides, I downloaded the latest version (11.0.1013), ran it and could not find QueryBuilder.SQLForServer property.

Rosière | email
1 week, 1 day since original post
> If I add the alternate names with MetadataContainer
> methods I will also find a way to stock and represent
> the aliases such as "stadt" in German for "city" in English.
> What is the best way to stock and represent them so that
> a query builder can read them as conveniently as possible?

The best way is to create separate files containing <realname>-<translated_altname> pairs. Load those pairs in some quick dictionary array, than enumerate MetadataContainer objects and theirs fields and set altnames.
Eugene Starkov | email
1 week, 2 days since original post
Hello,

I returned to this old subject.

Recently I bought the lates Active Query Builder Java version. I would like to retrieve both the aliased query and the unaliased query. However I could not find "QueryBuilder.SQLForServer" property that you talked about.

Could you please tell me why?

Thanks
Rosière | email
3 months since original post
Rosière,

The specified property is from ActiveX edition of the Query Builder.
In the Java version you should always use getSQL() method.
If you previously used setUseAltNames(true) the output query text will contain aliases.
There is AlternateNames source code sample demonstrating that.
It contains two instances of PlainTextSQLBuilder bound to the same QueryBuilder. One of them returns aliased text, another one - unaliased query.
Eugene Starkov | email
3 months since original post
Thank you Eugene.

Suddenly my customer's requirements changed again.

Now, instead of creating an unaliased query, I need to create a query using aliases from a given name list. So I should integrate a select list in the column "alias", and require the user to map a field into a specified alias: for example "id" to "A", "name" to "B", and get a query like "select id AS A, name as B from ....", because the query's result is supposed to be imported into a remote table whose columns are named "A", "B", ...

Is it possible to achieve it, and how should I make it?
Rosière | email
3 months since original post
So you need expression aliases, not alternate names.
To automatically set aliases you can handle DatasourceFieldAdded event. It provides MetadataField and CriteriaItem objects those allow you to identify the new field selected by user. Use CriteriaItem's setAliasString() method to set corresponding alias.
Eugene Starkov | email
3 months since original post
Thanks,

I won't set automatically aliases. Instead, I should ask the user to select an alias from a predefined or customizable list, just as he selects the aggregate functions in the "expression" column. Besides I will should the user from writing any text into the alias column, which means the "alias list" cannot be modified by him.

In order to make this, should I manipulate the Swing object "aliasColumn" in CriteriaListControl and restrict the possible aliases to values in a list?
Rosière | email
3 months since original post
You might handle GridCellValueChanging and GridCellValueChanged events to analyze, disallow editing and suggest values, but if you want to use custom dropdown list in the Alias column, you have to modify our source code.
Eugene Starkov | email
3 months since original post
This topic is closed