kabootit[Article] Client Access to Servoy's (Database Server) Properties
by Tom Parry, President
Prospect IT Consulting
Have you ever needed to get the the servoy.properties file while in the Smart Client? Have you had a need to do server side processing invoked from the client? Then read on.
I had a need to get the properties file, or more specifically the values of the db server attributes, while creating a stand-alone Jasper Reporting application that is also able to be called from Servoy as application.executeProgram (or application.executeProgramInBackground).
I had to get:
- the database server connection based on the server name
- the database URL,
- the driver,
- the user name,
- and other things.
So where is this information kept?
Look for the servoy.properties file. For Servoy 3.5.x it is in the directory: <servoy_home>/. For Servoy 4.x it is is in <servoy_home>/application_server/ where <servoy_home> is the root directory of the installation.
The servoy.properties file is a text file (it would be nicer if it was in xml but it is not!). A partial sample of the 3.5.x file is shown here.
server.0.URL=jdbc\:mysql\://localhost/ess_dev
server.0.catalog=<none>
server.0.connectionValidationType=2
server.0.driver=com.mysql.jdbc.Driver
server.0.enabled=true
server.0.maxConnectionsActive=10
server.0.maxConnectionsIdle=5
server.0.maxPreparedStatementsIdle=100
server.0.password=encrypted\:xxxxxxxxx\=
server.0.schema=<none>
server.0.serverName=ess_v2_mysql
server.0.userName=Servoy
server.0.validationQuery=Select 1
server.1.URL=jdbc\:mysql\://localhost/servoy_repository_fw_2_0_rc1
server.1.catalog=<none>
server.1.connectionValidationType=2
server.1.driver=com.mysql.jdbc.Driver
server.1.enabled=true
server.1.maxConnectionsActive=10
server.1.maxConnectionsIdle=5
server.1.maxPreparedStatementsIdle=100
server.1.password=encrypted\:xxxxxxxxx\=
server.1.schema=<none>
server.1.serverName=repository_server
server.1.userName=Servoy
server.1.validationQuery=Select 1
Since I was only interested in the server.x.yyy sections that is all I have designed. Note that the password is encrypted, so this attribute has to be either hard-coded or obtained another way. However this approach could be extended easily enough for other attributes.
Requirement
The most up to date server information must be accessed at the client.
Architecture
The client accesses a table (called
db_server_properties) to get to the db server attributes. The table is
created empty by an offline sql script. For each application server
start up the table is populated with the current servoy.properties file
contents. Additionally, each client method requiring the properties
will cause a refresh of the table in case for any reason the
servoy.properties file was updated.
####
# SQL script for MySQL – amend for your own flavour
# Table structure for table 'db_server_properties'
# Contains the fields as set in the servoy.properties file.
#
#Note: VARCHAR(200) is selected assuming this is sufficient length for each attribute value.SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `db_server_properties`;
CREATE TABLE `db_server_properties` (
`id` INT NOT NULL UNIQUE KEY PRIMARY KEY,#use Servoy sequence not dbidentity
`attrib_catalog` VARCHAR(200),
`attrib_connectionValidationType` VARCHAR(200),
`attrib_driver` VARCHAR(200),
`attrib_enabled` VARCHAR(200),
`attrib_maxConnectionsActive` VARCHAR(200),
`attrib_maxConnectionsIdle` VARCHAR(200),
`attrib_maxpreparedStatementsIdle` VARCHAR(200),
`attrib_password` VARCHAR(200),
`attrib_schema` VARCHAR(200),
`attrib_serverName` VARCHAR(200),
`attrib_serverId` VARCHAR(200),
`attrib_URL` VARCHAR(200),
`attrib_userName` VARCHAR(200),
`attrib_validationQuery` VARCHAR(200),
`last_app_type` INT DEFAULT 0,
`modification_date` DATETIME,
`modification_user` INT,#lookup login user id
`creation_date` DATETIME,
`creation_user` INT #lookup login id
) ENGINE=InnoDB;
#
###########################################
Design
A Servoy batch process (a headless client) is utilized with a solution that populates the table with the latest properties.
A solution (PITC_Properties) is created with the onOpen method assigned to a global method (PITC_openSolution) that does the population by calling a global method (PITC_populatePropertiesTable) after some house keeping checks.
The client uses the servoy_guy_robot plugin as a headless client to refresh the table with the properties file if necessary by invoking the global method PITC_populatePropertiesTable.
The PITC_populatePropertiesFile. Method has basically four steps:
- get the properties file,
- parse the properties file,
- if the properties file is not the same as the properties table
then overwrite the properties table with the file contents - if the application type is headless client
then exit
otherwise (client, web client, developer etc)
return the status (as a string)
For the client calling the headless client solution you can see that we can return a value (status as a string here) which may be used at the client end.
The actual code follows and has a few more functions and checks.
SOLUTION PITC_Properties
Global Variables:
exitApp, INTEGER, default 1
Global Methods:
Principal methods
PITC_openSolution
PITC_populatePropertiesTable
PITC_getPropertiesFile
PITC_parsePropertiesFile
PITC_comparePropertiesToTable
PITC_savePropertiesFile
Supporting methods
PITC_getServerRoot
PITC_exitApp
PITC_testHeadless
Forms:
“misc” Is a single form, no ui elements on it. Uses table “db_server_properties”.
CODE PITC_openSolution
//2008.Sep.14
/*
PITC_openSolution
this checks to see if it is called without arguments or not.
If no args then this must be the batch process so we exit the app after doing the job.
If there is an arg and it is "ESS_headlessClient" then we are being called as a headless client
and should not exit.
If this does not exit then the caller of the headless client should exit it after doing the work by calling exitApp method.
IN: arguments array
*/
application.output('PITC_openSolution: arguments[0]= ' + arguments[0]);
if (arguments[0]) {
var args = arguments[0].split(',');
application.output('PITC_openSolution: args[0]= ' + args[0]);
if (args[0].equalsIgnoreCase('ESS_headlessClient')) {
globals.exitApp = 0;
//we do not execute anything -
//the caller of the headless client should call runGlobal/Form method
}
}
else {//no args so just run the populate properties table method - must be batch
application.output('PITC_openSolution - running default method');
globals.PITC_populatePropertiesTable();
if (application.getApplicationType() != 3) {//do not exit if in developer
application.output('PITC_openSolution - exiting');
globals.PITC_exitApp();
}
}PITC_populatePropertiesTable
//2008.Sep.08
/*
PITC_populatePropertiesTable
A global method to read, parse then save the properties file to a database server table.
NOTE: this should only be called once via a headless client to set up the table each time
the application server is installed, not each time the client runs!
Assumes that a table may exist but may not have latest data or any.
NOTE: if the module is a batch process then ensure that the on open method is this one!
In: arguments[0] comma separated value string each value is one arg.
Return: if headless client then exits
otherwise: true if updated table with latest OR if table same as file;
false if something went wrong
*/
/*
Check to see if the table is populated with the latest properties file
by checking if the table contains data - that is same as the parsed properties file.
Returns true if same. False if something goes wrong.
*/
var isExitApp = true;//default if called from batch process with no arguments
application.output('PITC_Properties: arguments[0]= ' + arguments[0]);
if (arguments[0]) {
var args = arguments[0].split(',');
application.output('PITC_Properties: args[0]= ' + args[0]);
if( args[0].equals('ESS_openSolution') ) {
isExitApp = false;
}
}
var propsFile = globals.PITC_getPropertiesFile();
var dbServers = globals.PITC_parsePropertiesFile(propsFile);
var isSame = globals.PITC_comparePropertiesToTable(dbServers);
var status = isSame;
if (!isSame) {
status = globals.PITC_savePropertiesFile(dbServers);
}
//Get the application type
var appType = application.getApplicationType();
//type 1 = SERVER
//type 2 = CLIENT
//type 3 = DEVELOPER
//type 4 = HEADLESS_CLIENT
//type 5 = WEB_CLIENT
//type 6 = RUNTIME
//Close the current open solution and optionally open a new one
application.output('PITC_Properties - app type = ' + appType + ' status = ' + status);
if ( (isExitApp) && (appType == 4)) {
globals.PITC_exitApp();//this is last line executed
}
//if isExitApp or not headless client then this is executed
return status;PITC_getPropertiesFile
//2008.Aug.14
/*
PITC_getPropertiesFile
A global method to get the properties file in the server directory.
It will go to the appropriate directory for the file depending on the version of Servoy.
3.5.x it is in the root directory of Servoy install
4.x it is in the application_server sub-directory of the install.
*/
//Returns the application version
var ver = application.getVersion();
var verParts = ver.split('.');
var propDir = '.';//root servoy install dir
if (verParts[0] == 3) {
propDir += plugins.it2be_tools.server().fileSeparator + '';//remain in the root
}
else if (verParts[0] == 4){
propDir += plugins.it2be_tools.server().fileSeparator + 'application_server';
}
var propFile = propDir + "servoy.properties";
var props = plugins.it2be_tools.server().readTXTFile(propFile);//works on all application types
//application.output(props);//debug aid
return props;PITC_parsePropertiesFile
//2008.March.15
/*
PITC_parsePropertiesFile
Parse the Servoy Properties file for database server properties.
Return an array of the Database server entries or null if no
properties file exists.
2008.Aug.14 added trimming of attribs in case of trailing spaces.
*/
var serverObjects = new Array();//only container available
var textData = globals.PITC_getPropertiesFile();
if (textData == null){
plugins.dialogs.showErrorDialog( 'Error','Cannot run report - no
properties file found');
return null;
}
//for file-encoding parameter options (default OS encoding is used),
//http://java.sun.com/j2se/1.4.2/docs/guide/intl/encoding.doc.html
var lines = textData.split("\n");
var k = 0;//server object index
for(var i=0; i<lines.length;i++) {
var index = lines[i].indexOf('server.',0)
if ( index == 0) {//must be at the start
var serverLine = lines[i].split('.');//now split the line on
the dot
//[0] == 'server'
//[1] == server number
//[2] == attribute and its value
var serverNum = serverLine[1];
if (serverObjects.length <= serverNum) {
serverObjects[serverNum] = new Object();
}
var left = serverLine[0] + '.' + serverLine[1] + '.';
var right = lines[i].split(left);//now split the line using
'left' side
//[0] == empty or the left side
//[1] == right side
var attribs = right[1].split('=', 2);//split attrib name and
value only do the first
var attrib_0 = utils.stringTrim(attribs[0]);//there may be
trailing spaces
var attrib_1 = utils.stringTrim(attribs[1]);//there may be
trailing spaces
// serverObjects[serverNum][attribs[0]] = attribs[1];
serverObjects[serverNum][attrib_0] = attrib_1;
}//if line begins with 'server.'
} //for each line
return serverObjects;PITC_comparePropertiesToTable
//2008.Sep.08
/*
PITC_comparePropertiesToTable
A global method to compare the properties file to the contents of the database server table.
If table is empty (which can occur on first time) then return false.
Otherwise compare entries to see if anything updated including the number of servers.
Note: the form 'misc' is used but this could be replaced by getting the foundset of
the table 'db_server_properties' assuming one knows the server name.
Enhancements welcomed!
In: arguments[0]: the DB Server object array (see PITC_parsePropertiesFile).
Return: false if not the same or empty , true if same
*/
var isSame = false;
forms.misc.foundset.loadAllRecords();
if (utils.hasRecords(forms.misc.foundset)) {//return false if empty
var max = forms.misc.foundset.getSize();
var numServers = arguments[0].length;
if (max == numServers ){//return false if not same # of records
//now check each record against the dbServers array
//sort by the serverId so as to get them in the correct order
forms.misc.foundset.sort('attrib_serverid, asc');
for ( var index = 1; index <= max; index++ ) {
forms.misc.foundset.setSelectedIndex(index);
var dbServerId = 0 + parseInt(forms.misc.attrib_serverid);
var dbServer = arguments[0][dbServerId];
if (!forms.misc.attrib_catalog.equals(dbServer.catalog)) return false;
if (!forms.misc.attrib_connectionvalidationtype.equals(dbServer.connectionValidationType)) return false;
if (!forms.misc.attrib_driver.equals(dbServer.driver)) return false;
if (!forms.misc.attrib_enabled.equals(dbServer.enabled)) return false;
if (!forms.misc.attrib_maxconnectionsactive.equals(dbServer.maxConnectionsActive)) return false;
if (!forms.misc.attrib_maxconnectionsidle.equals(dbServer.maxConnectionsIdle)) return false;
if (!forms.misc.attrib_maxpreparedstatementsidle.equals(dbServer.maxPreparedStatementsIdle)) return false;
if (!forms.misc.attrib_password.equals(dbServer.password)) return false;
if (!forms.misc.attrib_schema.equals(dbServer.schema)) return false;
if (!forms.misc.attrib_servername.equals(dbServer.serverName)) return false;
if (!forms.misc.attrib_url.equals(dbServer.URL)) return false;
if (!forms.misc.attrib_username.equals(dbServer.userName)) return false;
if (!forms.misc.attrib_validationquery){//null
if (dbServer.validationQuery) return false;//not null so not the same!
}
else {//not null
if (!forms.misc.attrib_validationquery.equals(dbServer.validationQuery)) return false;
}
//do not compare the last_app_type!
//if get here and max index ok!
if (index == max) {
isSame = true;
}
}//while
}//size same
}//has records
return isSame;PITC_savePropertiesFile
//2008.Sep.08
/*
PITC_savePropertiesFile
A global method to save the properties file in a database server table.
In: arguments[0]: the DB Server object array (see PITC_parsePropertiesFile).
Return: true if all went well, false otherwise.
*/
var status = false;
if (arguments[0]){
//empty the table
forms.misc.foundset.deleteAllRecords();
databaseManager.saveData();
var numServers = arguments[0].length;
for (var i=0; i < numServers; i++) {
var dbServer = arguments[0][i];
forms.misc.controller.newRecord();
forms.misc.attrib_catalog = dbServer.catalog;
forms.misc.attrib_connectionvalidationtype = dbServer.connectionValidationType;
forms.misc.attrib_driver = dbServer.driver;
forms.misc.attrib_enabled = dbServer.enabled;
forms.misc.attrib_maxconnectionsactive = dbServer.maxConnectionsActive;
forms.misc.attrib_maxconnectionsidle = dbServer.maxConnectionsIdle;
forms.misc.attrib_maxpreparedstatementsidle = dbServer.maxPreparedStatementsIdle;
forms.misc.attrib_password = dbServer.password;
forms.misc.attrib_schema = dbServer.schema;
forms.misc.attrib_servername = dbServer.serverName;
forms.misc.attrib_serverid = dBServerId;
forms.misc.attrib_url = dbServer.URL;
forms.misc.attrib_username = dbServer.userName;
forms.misc.attrib_validationquery = dbServer.validationQuery;
forms.misc.last_app_type = application.getApplicationType();
}//for
databaseManager.saveData();
status = true;
}
return status;PITC_getServerRoot
//2008.Sep.17
/*
PITC_getServerRoot
Retreives the application server root directory.
Note that this only works properly if executed server side.
IN: nothing
Return: String or null if something wrong
*/
var servoyHome = new java.io.File("");
application.output("Found servoy dir " + servoyHome.getAbsolutePath());
var servoyRoot = servoyHome.getAbsolutePath();
return servoyRoot;PITC_exitApp
//2008.Sep.14
/*
PITC_exitApp
simple exits the app.
Useful if called via the robot plugin and want to exit the application.
TODO add other clean up code here if necessary.
*/
application.exit();PITC_testHeadless
//2008.Sep.14
/*
PITC_testHeadless
tests the headless client for proper global method invocation
*/
var x = "PITC_testHeadless global method start ";
return x;Client Side Example
Now here is a simple example of a client side method using the headless client to perform server side processing.
//2008.Sep.17
/*
PITC_getServerHomeDir
Retreives the Servoy server home directory
IN: nothing
Return: String or null if something goes wrong
*/
var serverHome = null;
if ( plugins.servoyguy_robot.checkAccessPassword('secret password as set in the admin pages for the plugin') ) {
//Startup a new headless client on the server, and return a JSHeadlessClient object.
//Optionally pass in ServerHost and ServerPort to get a connection to a different Application Server
var headless_client = plugins.servoyguy_robot.getNewHeadlessClient('PITC_Properties', null, null, 'ESS_headlessClient')
if(headless_client) {
serverHome = headless_client.runGlobalMethod('PITC_getServerRoot', '');
application.output('Result:' + serverHome);
headless_client.runGlobalMethod('PITC_appExit', '');
}
else {
application.output('Error connecting.')
}
}
else {
serverHome = '';
}
var serverURL = application.getServerURL();//just because I wanted to see the answer!
var ver = application.getVersion();
var verParts = ver.split('.');
var subDir = '';
if (parseInt( verParts[0] , 10) == 3) {
subDir = 'server';
}
else if (parseInt( verParts[0] , 10) == 4){
subDir = 'application_server';
}
var fileSep = plugins.it2be_tools.server().fileSeparator ;
var serverHomeDir = null;
if (serverHome) {
serverHomeDir = serverHome + fileSep + subDir;
}
return serverHomeDir;
Comments (6)