Active Query Builder support area

How to implement with ElevateDB?

  • updated
  • Completed
Hi - my application uses TWO different backends depending on what customers purchase. Our "Standard" Edition uses ElevateDB while the "Server" Edition uses PostgreSQL.

I've been able to get it working fine with PostgreSQL but I'm now trying to work out what I need to do to get the component working with ElevateDB :-

For Postgres I find that I can use the PostgreSQLSyntaxProvider with a UniversalMetadataProvider (I pass a DevArt.PgSqlConnection object to the UniversalMetadataProvider) and it all works fine.

I've tried using the GenericSyntaxProvider with the UniversalMetadataProvider (by passing a EDBConnection object to it) but alas no joy.

Do I need to derive my own class from BaseSyntaxProvider (e.g. EDBSQLSyntaxProvider) in order to get this working? If so, do you have any articles or documentation to assist with this please?

Many thanks!

Hedley Muscroft
Just a further thought on this... I already have routines in my DB layer which fetch all tables/views/fields and other metadata for the schema...  so I could populate the DB schema manually myself without needing a live DBConnection object.

Unfortunately I can't see the best way to do that - please help!
Andrey Zavyalov, PM
All possible ways of adding metadata objects programmatically are listed in the Load metadata demo project that included in the installation package. Also you can read the knowledge book aboutmetadata handling 
Hedley Muscroft
OK - I've got that working and I'm now able to populate the schema manually, One problem has cropped up though...

I'm using the SqlTextEditor component (which is very nice by the way). I've noticed that the auto-completion doesn't work on field names if I add the TABLES straight in to the root of the metadata.

Instead, I have to create a DB node (which I called "DB"), then a SCHEMA node (which I called "public), then I add the tables to the schame node. Once I do that, the auto-complete of the SqlTextEditor works,

However, it now adds in the "DB.public." prefix for all table names - which I obviously don't wan't as the SQL is invalid. In other words the auto-complete changes the SQL to :-

select fld1, fld2 from DB.public.atable

The "SQLGenerationOptions" is correctly set to SkipAll but it seems to have no effect, whereas it works as expected when the metadata is populated automatically from the PostgreSQL version of my app,

Please can you advise how to either :-
[1] Get the SqlTextEditor auto-completion to work without having to add a DATABASE and SCHEMA node; or
[2] How to omit the DB.SCHEMA. prefix from table names when populating the metadata manually

Many thanks.
Hedley Muscroft
Hi - after lots of hunting around, I've been able to answer question [2] myself. In case it helps anyone else, you simply set the Database and Schema nodes ".Default" property to TRUE and then it gets omitted from the SQL.

However, I would still like to know the answer to [1] please?
The MetadataContainer should be filled with metadata according to SQL syntax you use. If the syntax supports databases and schemas, there should be database object containing the schema object that contains tables and views.  
If you want the visual representation contain no database and schema nodes you can achieve this by modifying the metadata structure - another abstraction layer between the MetadataContainer and the tree control.
The following example will show all database objects in the root level of the database schema tree:  

// Disable automatic structure generation:
queryBuilder1.MetadataStructure.AllowChildAutoItems = false;
// Set MetadataFilter of the root structure node to include all
// metadata objects excluding namespaces (database and schema nodes):
queryBuilder1.MetadataStructure.MetadataFilter.Add(); // just add new filter item (MetadataFilterItem with default properties includes all objects).
If the metadata container is filled according to syntax rules and the database and schema nodes are marked as Default, the SqlTextEditor will able to work with short object names in the auto-complete.
Hedley Muscroft
Thanks - got it now. :-)