This is a Servoy tutorial on coding efficiency and frameworks. This time I want to talk about something very basic, but extremely important. It is going to help boast your productivity like nothing else I can share with you.
I have worked in small teams, and big teams, and it always surprises me when I see someone typing the same code over and over again, or searching, copying, pasting, and modifying, a snippet that they located from somewhere in the maze known as your application. Bottom-line is this type of behavior is highly unproductive, and if I am talking about you, then you are not going to get to the next level unless you change your ways now.
This lesson is primarily about the often under-utilized feature in Eclipse (Servoy Developer) called Templates. You may have used some built-in templates that came with Servoy out of the box for loops, try-catch blocks, etc. (shame on you if you haven’t). However, this tutorial is really about creating your own custom templates, so that you can instantly generate big blocks of well designed code, ready for you to tweak for the situation at hand. It’s easy to do, ensures accuracy and consistency, and will crank your productivity through the roof, leaving you lots of time to play COD online with your friends.
To fully appreciate the lesson in this Servoy tutorial, we need to expose you to a simple framework, something I use pretty much everyday. This is going to be a simple framework that takes care of all the work, and error trapping, for running SQL queries to obtain datasets and foundsets using the databaseManager, and running raw SQL using the rawSQL plugin. The purpose is not to convince you that my code, which is now over five years old, is the way to do it. The point is more to show you how you can build out a nice simple framework, and then build some Eclipse Templates so that you can use the framework easily and efficiently whenever needed.
Okay, so let’s get started. Here is the code that I use for obtaining a dataset with a SQL query. It uses the databaseManager to execute the query, ensure “scopes.enums” defaults are used if no data is passed in, and has a separate routine that is called to handle errors. I also have jUnit tests so the function can be validated automatically. This function, and the others to follow, are all in my “scopes.utils” library, and are all built to accept the same object. There are other auxiliary functions that I am not putting here, that take care of things like building IN clauses with multiple arguments, etc., that are really outside of the scope of this Servoy tutorial, but keep in mind that you probably will start with something like this, but then extend it, as unique situations arise.
/**
* @author: Gary Dotzlaw, 2013-12-01
* @description: Will run SQL and return a JSDataset, or NULL
*
* @param {{sql:String, args:Array, [server]:String, [maxRows]:Number, [table]:String, [server]:String}} oSQL
*
* @return {JSDataSet}
*/
function sqlDataset(oSQL)
{
/*** @type {String} */
var sErrorMsg = "",
/*** @type {JSDataSet} */
dsData;
//Check for required parameters and if not, then use the defaults
if (!oSQL.sql){
application.output("Error: No oSQL.sql was passed to scopes.utils.sqlDataset(oSQL)");
return null;
}
if (!oSQL.maxRows) oSQL.maxRows = scopes.enums.DB.MAX_RECORDS;
if (!oSQL.server) oSQL.server = scopes.enums.DB.SERVER;
if (!oSQL.args) oSQL.args = [];
try{
// Run the SQL
dsData = databaseManager.getDataSetByQuery(oSQL.server, oSQL.sql, oSQL.args, oSQL.maxRows);
}catch (error){
// Trap for any error message
sErrorMsg = error['message'];
}
if (sErrorMsg){
// Error found, report it
sqlReportError(oSQL, "scopes.utils.sqlDataset(oSQL)" + "\n" + sErrorMsg);
return null;
}
// Return the dataset
return dsData;
}This next function gets a foundset using the databaseManager. It also accepts the same object, has jUnit tests to validate it, and any errors are handled by the same separate function.
/**
* @author: Gary Dotzlaw, 2013-12-01
* @description: Gets a foundset using SQL or returns NULL
*
* @param {{sql:String, args:Array, [server]:String, [maxRows]:Number, [table]:String, [server]:String}} oSQL
* @param {Boolean} [bDoNotLoadAll] - Do not force all records to be loaded into the foundset (more than 200)
*
* @return {JSFoundSet}
*/
function sqlFoundset(oSQL, bDoNotLoadAll)
{
/*** @type {String} */
var sErrorMsg = "",
/*** @type {JSFoundSet} */
fs;
//Check for required parameters and if not, then use the defaults
if (!oSQL.sql){
application.output("Error: No oSQL.sql was passed to scopes.utils.sqlFoundest(oSQL)");
return null;
}
if (!oSQL.table || oSQL.table.length == 0){
application.output("Error: No oSQL.table was passed to scopes.utils.sqlFoundest(oSQL)");
return null;
}
if (!oSQL.server) oSQL.server = scopes.enums.DB.SERVER;
if (!oSQL.args) oSQL.args = [];
// Get the foundset for the table
fs = databaseManager.getFoundSet(oSQL.server, oSQL.table);
// Attempt to load the foundset with the SQL query
try{
fs.loadRecords(oSQL.sql, oSQL.args);
// Load all the records unless asked not to
if (!bDoNotLoadAll) if (fs.getSize() > 199) fs.getRecord(databaseManager.getFoundSetCount(fs));
}catch (error){
sErrorMsg = error['message'];
}
if (sErrorMsg){
// Attempting to load the foundset failed, report the error
sqlReportError(oSQL, "scopes.utils.sqlFoundset(oSQL)" + "\n" + sErrorMsg);
// Return null; loading the foundset by SQL failed
return null;
}
// Loading the foundset succeeded
return fs
}This function handles the rawSQL plugin, also has jUnit tests for validation, and error reporting is handled by the same routine as in the other functions.
/**
* @author: Gary Dotzlaw, 2013-12-01
* @description: Will run raw SQL and returns TRUE or FALSE
*
* @param {{sql:String, args:Array, [server]:String, [maxRows]:Number, [table]:String, [server]:String}} oSQL
*
* @return {Boolean}
*/
function sqlRaw(oSQL)
{
var bResultOK = false,
sErrorMsg = "";
//Check for required parameters and if not, then use the defaults
if (!oSQL.sql){
application.output("Error: No oSQL.sql was passed to scopes.utils.sqlRaw(oSQL)");
return false;
}
if (!oSQL.table) oSQL.table = "";
if (!oSQL.server) oSQL.server = scopes.enums.DB.SERVER;
if (!oSQL.args) oSQL.args = [];
// Run the raw SQL
bResultOK = plugins.rawSQL.executeSQL(oSQL.server, oSQL.table, oSQL.sql, oSQL.args);
// Check for errors
if (!bResultOK) {
// Error found, get error message
try {
sErrorMsg = plugins.rawSQL.getException().getMessage();
}catch(error){
sErrorMsg = error['message'];
}
}
if (sErrorMsg){
// Attempting to load the foundset failed, report the error
sqlReportError(oSQL, "scopes.utils.sqlRaw(oSQL)" + "\n" + sErrorMsg);
// Return null; raw SQL failed
return null;
}
// Return true; Raw SQL ran fine
return true;
}This is the error handling function. It accepts the object from the routine that called it, along with the error message. It then reports the error to the console, or in most cases, displays it in a dialog (pulled out for this example). It also has jUnit tests to validate that it is functioning properly.
/**
* @author: Gary Dotzlaw, 2013-12-01
* @description: Reports a SQL error
*
* @param {{sql:String, args:Array, [server]:String, [maxRows]:Number, [table]:String, [server]:String}} oSQL
* @param {String} sErrorMsg - SQL error message to report
*
*/
function sqlReportError(oSQL, sErrorMsg)
{
var sMsg = "";
if (!sErrorMsg){
application.output("Error: No oSQL was passed to scopes.utils.sqlReportError(oSQL, sErrorMsg)");
return;
}
if (!sErrorMsg){
application.output("Error: No sErrorMsg was passed to scopes.utils.sqlReportError(oSQL, sErrorMsg)");
return;
}
if (scopes.enums.devMode === 1){
// Output the error to the console
sMsg += "SQL Error ------" + "\r";
sMsg += sErrorMsg + "\r";
sMsg += "SQL : " + oSQL.sql + "\r";
sMsg += "Args : " + oSQL.args + "\r";
if (oSQL.server !== scopes.enums.DB.SERVER) sMsg += "Server : " + oSQL.server + "\r";
if (oSQL.maxRows && oSQL.maxRows !== -1) sMsg += "MaxRows : " + oSQL.maxRows + "\r";
application.output(sMsg);
}else{
if (!scopes.enums.DB.SUPPRESS_ERRORS || scopes.enums.DB.SUPPRESS_ERRORS === 0){
// Display the error
sMsg = "A SQL error has been generated" + "\n";
sMsg += oSQL.sql + "\n";
sMsg += "SQL Args: ";
sMsg += oSQL.args + "\n";
sMsg += "Error: ";
sMsg += sErrorMsg;
application.setClipboardContent(sMsg);
application.output(sMsg);
}
}
return;
}Okay, so there are four functions, the beginnings of a simple framework. Just building a simple framework like this will save us a great deal of time, but we can make it even better. You should absolutely be building little frameworks like this for yourself, and your team, so that everyone is using the same functions to do the same thing, from one location, making it easy to maintain and extend, as well as ensuring consistency and reliability.
So let’s talk a minute about how we would use this. The functions have all been created to accept an object. So, if we wanted to use the “scopes.utils.sqlDataset()” function, we could do something like this:
var oSQL = {};
oSQL.sql = " \
SELECT o.ordh_id \
FROM sa_order o \
INNER JOIN sa_order_opp so ON o.ordh_id = so.ordh_id \
INNER JOIN sa_order_revision_header orh ON o.ordh_id = orh.ordh_id AND orh.ordrevh_revision = o.ordh_total_revisions \
LEFT OUTER JOIN sa_sales_person sp ON o.ordh_salesper_id = sp.salesper_id \
LEFT OUTER JOIN sa_territory t ON o.salesterr_id = t.terr_id \
WHERE o.ordh_document_type = ? AND o.ordh_estimate_status = ? \
AND DATEDIFF(DAY, GETDATE(), so.ordhopp_exp_close_date) <= ? \
"
oSQL.args = ["EST", "OPEN" , 30];
var ds = scopes.utils.sqlDataset(oSQL);So, this is already pretty easy to use, but we still have code we will be typing each time. Also, what if we forget the “oSQL.args” for example? Well, lets make an Eclipse Template to generate this code for us each time (less the SQL of course).
Go to the Eclipse Preferences, Javascript, Editor, and Templates. Click the NEW button to add a new Template.
Give the Template a name that you will remember. You will be able to start typing the name, then press CTR + SPACE to get Eclipse to suggest an available auto-completion template. I created myself three templates, one for each of the main functions, sqlDataset, sqlFoundset and sqlRaw. Notice that I use the “Insert Variable” button to place the “${cursor}” in the location I would like my cursor to be after the code is generated by the auto-completion.
Returning to Servoy, I begin to type the beginning of my code templates, “sql”, and press CTR + SPACE. Eclipse shows me the following selections.
If I type “sql_d” and press CTR + SPACE, Eclipse shows me the following:
And if I hit RETURN while it is displaying this selection, the code shown below is automatically inserted for me, and my cursor is positioned where I wanted it.
var oSQL = {
sql: "",
args: [],
maxRows: scopes.enums.DB.MAX_RECORDS,
server: scopes.enums.DB.SERVER
},
ds;
ds = scopes.utils.sqlDataset(oSQL);
if (ds && ds.getMaxRowIndex() > 0) {
// We got some data
}else {
// No data
}You can probably see how easy that was. Now I am ready to type my SQL and add my arguments. I can even see what the other options are, already set to defaults for my application from “scopes.enums”. Why would I want to type this block of code over and over again, every time I want to get a dataset using SQL?
Okay, so that was the point of this Servoy Tutorial. I wanted to make you understand how important it is for everyone in your team to use frameworks for the routine functions you will be using in your application, and how vital it is that you all start creating custom Eclipse Templates for increased productivity. Stop wasting time; life is too short! Get on it, start building out your templates today, and send your productivity through the roof.
That concludes this Servoy tutorial. I hope you enjoyed it!
