SQL use overview

Classic Servoy
1

Excuse me for asking a very general question, but I’m still looking for how the use of SQL fits into Servoy. So far I’ve only seen that it can be used in a very limited way with the getDataSetByQuery method to populate a form or to provide a snapshot view for disply only. Given the limitations below, why would one want to use this method instead of a search?

//limitations/requirements for sqlstring are:
//-must start with ‘select’
//-must contain ‘from’ and ‘order by’ keywords
//-must at least select from the table used in Servoy Form
//-cannot contain ‘group by’ or ‘having’
//-all columns must be fully qualified like ‘orders.order_id’
//-the selected columns must be the (Servoy Form) table primarykey columns (alphabetically ordered like ‘select a_id, b_id,c_id …’)
//-can contain ‘?’ which are replaced with values from the array supplied to parameters function argument

I think perhaps what I’m looking for is a way for Servoy to provide views, as MySQL does not yet support them. And, I guess, I’m puzzled by the inclusion of a good SQL primer in the documentation when the use of SQL is limited in this fashion.

Thanks in advance for any comments, suggestions as we continue our evaluation of this product.

Jake L.

2

Hi Jake,

Great questions. Let me answer your first question first:

Q: Why use SQL and not a search?

A: There are times when you want to have a report display that require multiple inner or outer joins - and there’s really no way to do that via “Relations” - SQL is much faster and easier.

Also - the “limitations” you point out below are only for loading a recordset into a form via SQL.

You can do a getDataSetByQuery with ANY SQL you want to - and then you have a “handle” to the JSData object that contains the recordset. You can then iterate through rows, columns, transform the entire recordset to HTML (with one line of code), etc.

Q: I think perhaps what I’m looking for is a way for Servoy to provide views, as MySQL does not yet support them

A: Servoy doesn’t yet support forms based on a VIEW. However, you can easily create the same type of functionality on your form by creating Relations and then placing the related column(s) on your form.

I hope this answers your question.

Thanks for taking the time to evaluate Servoy.

Bob Cusick