Meta Data? SQL View Handling?

Classic Servoy
1

What kind of database and Servoy form meta data is available within servoy?

Can you get lists of active form field names, database properties such a field contraints, etc? from within the Servoy environment?

How does Servoy deal with SQL Views where some columns are updatable and others are not?

Thanks,
Lee Snover

2

Hi Lee,

  1. You can get the schema of any connection using the DatabaseManager node of the Methods Editor; including tables, columns, data tyes and lengths.

  2. Constraints are more tricky - but Servoy can read the primary/foreign key constraints and use them in relations. You can also used stored procedures in your backend to get other constraints.

  3. You cannot use views as the basis for forms in Servoy - only tables.

Hope this helps,

Bob Cusick

3

bcusick:
Hi Lee,

  1. You can get the schema of any connection using the DatabaseManager node of the Methods Editor; including tables, columns, data tyes and lengths.

  2. Constraints are more tricky - but Servoy can read the primary/foreign key constraints and use them in relations. You can also used stored procedures in your backend to get other constraints.

  3. You cannot use views as the basis for forms in Servoy - only tables.

Hope this helps,

Bob Cusick

Bob:

I’m aware of the Field lists in the Method Editor. My question is regarding a script step, something similar to the Fieldnames function in Filemaker.

When you say Servory can’t use views, do you mean only views that would have calculated fields? Otherwise, how does Servoy know the difference between a View and a Table?

Is it possible to use Views with calculations in a Read Only manner? This would be extrement beneficial in reporting and presenting other summary data in other forms.

Can Global values be based on data coming from Views?

Sorry to have so many questions, but I appreciate the help.

Thanks,
Lee

4

Hi Lee,

  1. I think we’re talking about two different things. If you open the Method Editor, create a new method, scroll down to the “Database Manager” node, then right-click on “getTable” and choose “Move Sample”. You’ll see this code:
//get a specific table and get column info
var jstable = databaseManager.getTable('user_data','orders');
var tableSQLName = jstable.getSQLName();
var columnNamesArray = jstable.getColumnNames();
var firstColumnName = columnNamesArray[0];
var jscolumn = jstable.getColumn(firstColumnName);
var columnLength = jscolumn.getLength();
var columnType = jscolumn.getType();
var columnSQLName = jscolumn.getSQLName();

  1. Regarding views - you can issue your own SQL statements against views, you can execute stored procedures against views - you just can’t BASE A FORM on a view. Try this: go to File → New Form. You’ll see a list of server connections (top popup menu). When you choose one (choose “example_data”) - you’ll see a list of all the TABLES in that database (NOT views).

  2. Regarding globals - these are simply in-memory variables. You can assign them ANY value - coming from a database, view, table, or constant value. To execute your own query, open the Method Editor, create a new method, scroll down to the “Database Manager” node, then right-click on “getDataSetByQuery” and choose “Move Sample”. You’ll see this code:

//Get a dataset based on query
var maxReturedRows = 10;//useful to limit number of rows
var query = 'select c1,c2,c3 from test_table';//do not use '.' or special chars in names or aliases if you want to access data by name
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);

//place in label: elements.myLabel.text = '<html>'+dataset.getAsHTML()+'</html>';

//example to calc a strange total
global_total = 0;
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
		dataset.rowIndex = i;
		global_total = global_total + dataset.c1 + dataset[3];
}
//example to assign to dataprovider
//employee_salary = dataset.getValue(row,column)

Hope this helps,

Bob Cusick

5

Bob:

Yes, this helps. Sometimes it’s difficult to convey nuances of these issues.

Thanks again.

Lee Snover