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
Meta Data? SQL View Handling?
5 posts
• Page 1 of 1
Meta Data? SQL View Handling?
by bubba » Mon Aug 23, 2004 6:09 pm
- bubba
- Posts: 227
- Joined: Thu Aug 19, 2004 5:42 pm
- Location: Bath, PA
by bcusick » Mon Aug 23, 2004 6:19 pm
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
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
- bcusick
- Posts: 1255
- Joined: Wed Apr 23, 2003 11:27 pm
- Location: Thousand Oaks, CA USA
by bubba » Mon Aug 23, 2004 6:29 pm
bcusick wrote: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
- bubba
- Posts: 227
- Joined: Thu Aug 19, 2004 5:42 pm
- Location: Bath, PA
by bcusick » Mon Aug 23, 2004 8:21 pm
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:
2) 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).
3) 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:
Hope this helps,
Bob Cusick
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:
- Code: Select all
//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();
2) 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).
3) 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:
- Code: Select all
//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
- bcusick
- Posts: 1255
- Joined: Wed Apr 23, 2003 11:27 pm
- Location: Thousand Oaks, CA USA
5 posts
• Page 1 of 1
Return to Programming with Servoy
Who is online
Users browsing this forum: No registered users and 29 guests