[Challenge] Week 2/28/05: Getting form and field names (answer)

by David Workman
Data Mosaic

This week's challenge (see previous post) highlights Servoy's ability to return all kinds of structure information about the SQL source you are working with. To do this, we will explore the JSTable and JSColumn nodes belonging to the Database Manager.

Task 1: All form names

To begin, let's complete the first task of the challenge. In the last update (2.1 or 2.2), Servoy slipped in a property called "allnames" under the forms node. Not a place where you expect to find something is it?! It's simple to use. To fill a value list that I use for a combobox field it is simply:

//populate 'forms' value list with all the form names in this solution
var formNamesARY = forms.allnames;
application.setValueListItems('forms', formNamesARY);

Why is this important to my "FileMaker to SQL Bridge" module? Because now my module can be added to any solution and it can tell what forms are in that solution dynamically. Since each form is tied to one specific table in a SQL database, once I know the form name, I can get the server and table names that the form uses. I will need these in the next part:

//return server and table names given a form name
var server = forms[sql_form].controller.getServerName();
var table = forms[sql_form].controller.getTableName();

Task 2: All column names

This brings us to the second part of the challenge -- returning all of the column names of the form selected.

The key to reading the code listed at the end is understanding the hierarchy in the database manager node. The functions under the JSTable node (getColumn, getColumnNames, getQuotedSQLName, getSQLName, getServerName) need to work with some kind of object. As in: objectName.getColumnNames. This object is a table object which you can retrieve with the getTable() function under the database manager node. It is important to note that the table object is the only object the JSTable functions work with.

So first get the table object and then use a function under the JSTable tree to get the column names:

//return column names from selected data table
var jstable = databaseManager.getTable( server, table );
var columnARY = jstable.getColumnNames();

To highlight the hierarchical relationship, the above two lines of the code above can be rewritten in one line:

var columnARY = databaseManager.getTable( server, table ).getColumnNames();

Extra credit task: Each column type

We have to delve one more level down the hierarchy in the database manager node to get column types. But there isn't any more levels! Actually there is, the layout of the nodes under the database manager is just misleading. The nodes JSDataSet, JSFoundSetUpdater, JSTable, and JSColumn should not be listed on the same level as they are now. JSColumn should be a child of JSTable as all the functions in JSColumn only work on a column object. Where do you get this column object? The first function under JSTable node called getColumn. What is confusing is that you have to pass the getColumn function the name of the column -- which you get using the second function under the JSTable node (getColumnNames).

Pulling this confusion together, the following lines will return a column type:

//column type
var jstable = databaseManager.getTable( server, table );
var jscolumn = jstable.getColumn(columnARY[i]);
var columnType = jscolumn.getType();

Again, to prove my point about the hierarchical relationship, the above three lines of code can be written as one line:

var columnType = databaseManager.getTable( server, table ).getColumn(columnARY[i]).getType();

Conclusion

The database manager tree is extremely powerful. With this power comes a bit of a learning curve. Hopefully I've shed some light on how to go about getting SQL table structure information. Let me know if this functionality is useful in your solutions. I'd be interested to see what other types of tasks this is put to use with.

Appendix: Code sample

//clear related rows
forms.lvl2_bd_column_list.controller.loadRecords(bridge_to_sqlcolumn);
bridge_to_sqlcolumn.deleteAllRecords();


//return column names from selected data table
var server = forms[sql_form].controller.getServerName();
var table = forms[sql_form].controller.getTableName();

var jstable = databaseManager.getTable( server, table );
var columnARY = jstable.getColumnNames();

//the preceding two lines can be written as:
//var columnARY = databaseManager.getTable( server, table ).getColumnNames();


//create related records for each column name
for ( var i = 0; i < columnARY.length; i++ )
{
    bridge_to_sqlcolumn.newRecord();
    
    //column name
    bridge_to_sqlcolumn.c_name = columnARY[i];
    
    //column type
    var jscolumn = jstable.getColumn(columnARY[i]);
    var columnType = jscolumn.getType();
    
    //the preceding two lines can be written as:
    //var columnType = jstable.getColumn(columnARY[i]).getType();
    //or even this!
    //var columnType = databaseManager.getTable( server, table ).getColumn(columnARY[i]).getType();
    
    
    switch ( columnType > 0 )
    {
        case columnType == 4:            bridge_to_sqlcolumn.c_type = "integer";                            break;
        case columnType == 12:            bridge_to_sqlcolumn.c_type = "text";                                            break;
        case columnType == 93:            bridge_to_sqlcolumn.c_type = "datetime";                        break;    
    }    
    
    //order
    bridge_to_sqlcolumn.c_order = i + 1;
}

//go to first related record
bridge_to_sqlcolumn.recordIndex = 1;