Introduction
There are occasions when it would be very useful to query the elink database using SQL from within javascript. The standard elink libraries do not provide this capability directly. However, there is a script client active X which can be adapted for this purpose. I have written a simple script which obtains the SQL from javascript and returns all the required results back to javascript for further processing and reporting.
Limitations
1. The script client and the javascript are client-side scripts, meaning they operate on the client’s computer, not on the elink server. This implies that all results have to be passed from the server to the client. This has a number of important implications:
a) If the query returns a high volume of data, the time for that information to pass from server to client will be significant. Furthermore significant resources may be required on the client computer.
b) Care must be taken to construct sql statements that will not return too many results.
c) The script includes facilities to limit the number of rows of results returned
2. The SQL query will only return results that appear in the database; any altered data in the current open edit windows will not appear. If the functionality depends on data visible in the window, the functionality should not use an SQL query to obtain the data, but should query the window elements themselves.
3. There appears to be a problem executing script client-based code when the page loads. The solution is to introduce a time delay, e.g.:
function finish_load()
{
….. execute code to load the page….
}
function load()
{
//alert(‘load’);
window.setTimeout(“finish_load();”,500);
}
If the above code is omitted, or the time delay is too short, the page may fail to open properly, or consistently. 500ms seems to be adequate for most situations, but slower client computers may require longer delays. The trouble with these sorts of timing issues is that there is no delay which is guaranteed to be long enough.
Setup
1. Script client ActiveX object
This must be added to the end of the template, just before the </body>:
<OBJECT ID=”ScriptClient”
classid=”clsid:EF219FF0-027C-44C6-B30E-E69ADE2EEF05″
codebase=”<#ROOTDIR>activex/<#LANGUAGEDIRECTORY>ScriptClientXControl1.cab#version=2006,1,0,5″
width=0 height=0 align=center hspace=0 vspace=0></OBJECT>
2. Include the SQL javascript library:
<#SCRIPT Page=”scripts/ctlGenericSQLQuery.js”> and include this script in the scripts folder
3. Include the script client script scripts/ctldosql.pas in the scripts folder.
Operation
function get_project_document_data(idproject,is_subproject)
{
// query to obtain all documents attached to this project
var sql = “select doc.iddocument, doc.reference from proj, doc, proj_doc where ”
+ ” proj.idproject = ‘”+idproject+”‘”
+ ” and proj.idproject = proj_doc.idproject”
+ ” and proj_doc.iddocument = doc.iddocument”;
//alert(sql);
var result = execute_sql(sql,’iddocument/reference’);
var n = 1;
var iddocument, ref;
// identify each document type from the results
//alert(‘results—’);
while (iddocument = result.getVariable(“iddocument”+ n))
{
//alert(‘results2—’);
ref = result.getVariable(“reference”+ n)
…..
…..
n++;
}
//alert(iddocument);
}
The pivotal statement here is:
var result = execute_sql(sql,’iddocument/reference’);
This function executes the SQL statement and obtains the results if any. There are three parameters:
1. sql – the sql statement as a string
2. fields – a string containing a list of the fields to be returned separated by /
3. maxrows – omitted = return all rows
0 = return nothing (e.g. update statement)
number = return anything up to that number of rows.
The results are returned in a variable array and can be accessed by:
ref = result.getVariable(“reference”+ n)
where n = the row number.
‘reference’ = the name of the variable to be returned.
If there is no variable of that name, or if there are fewer than that number of results, the result will be null.
