Query Builder and SQL databases settings?

Classic Servoy
1

Okay.

So a Servoy application is written to interact with Postgresql. Reasonable since they are packaged together.

Also reasonable, imho, that changing database servers should be handled by Query Builder, assuming the tool is meant to normalize SQL between database vendors.

Missed the mark on that point, as it has NOT been just changing the databases, but the SQL is particular about types, groupBy, etc.

Does a setting exist where Query Builder adjusts SQL to perform with different databases, or is it tweaking by the developer to get the correct SQL to execute against the chosen vendor flavor of SQL?

What is the standard practice when supporting differing vendor databases?

Thanks!
–Joe.

2

Hi Joe,

joe26:
So a Servoy application is written to interact with Postgresql. Reasonable since they are packaged together

Not exactly, Servoy can basically interact with any database for which a class4 JDBC driver exists

When you write your code using foundsets, Servoy will generate the correct SQL to interact with the connected database (PostgreSQL, SQL server, MySQL, Oracle, etc.)

In previous versions when SQL had to be more complex, the only choice you had was to write plain SQL and use that to:
a) load the records into foundset > foundset.loadRecords(query, args);
b) get the data in a dataset > databaseManager.getDatasetByQuery(server, query, args, no of rows to be returned);

The plain SQL isn’t always compatible between different vendor databases as you noticed.
I believe somewhere in version 8 the queryBuilder was introduced and extended over the different versions after that.
The queryBuilder is a query object, which Servoy can use to (again) generate the correct SQL for the different vendor databases.

So using foundsets (including the default find/search functions) and the queryBuilder, will be the way to go in order to stay compatible.

Still you might find yourself in need of writing plain SQL, as more advanced SQL functions are not available in the queryBuilder due to compatibility issues.
(Lower versions of mySQL for instance, do not support Common Table Expressions (CTE) )

Although it’s nice to be a 100% compatible, it’s hard to make your application run nice and fast on every database.
There are differences in how SQL is handled most efficient and also the way you should index your columns can be different between different vendor databases.

My decision would be to stick to 1 database vendor, you already started of using PostgreSQL which is a great database.
It is fast, very scalable, easy to extend, there are a lot of extensions and tools out there.
Also the fact that PostgreSQL is free to use (and also some extensions and tools) is a nice add on, but depending on your target should not really be a reason.
The costs on using PG will depend on whether you will need professional help in setting up backup strategy, replication, clustering, connection pooling etc. etc.
Although this last bit might apply to other vendor databases as well.