Sage CRM 2020 R2: The eWareQuery Object (COM API)

The eWareQuery object allows use to issue queries against the database. These are either in the form of select statements or for data manipulation (Insert, update, delete).

Retrieval of Data

The basic pattern to use the eWareQuery object to select data is shown below:
var mySQL = "Select * from company where comp_type='customer'"; 
var myQuery = CRM.CreateQueryObj(mySQL,""); 
myQuery.SelectSQL(); 

with(myQuery) 
{ 
while (!eof) 
{ 
Response.Write(FieldValue("Field1")+"<br>"); 
Response.Write(FieldValue("Field2")+"<br>"); 
Response.Write(FieldValue("Field3")+"<br>"); 

NextRecord(); 
} 
} 



The second parameter of CreateQueryObj() method is the name of the database connection used. This allows you to run SQL against any database with which CRM has a defined connection. These connections are created under Administration > Advanced Customization > Tables and Databases.

There is a RecordCount property that allows you to check whether data has been retrieved.

One very important thing to point out about the eWareQuery objects is that it is not bound to the security model like the eWareRecord.

Editing of Data

You can use the eWareQuery object to insert, update and delete data.

The basic pattern to use the eWareQuery object to carry out manipulative tasks is shown below:

var intRecordId = CRM.GetContextInfo("company","comp_companyid"); 
var mySQL = "update company set comp_name = RTRIM(ISNULL(comp_name,''))+'Limited' where comp_companyid ="+ intRecordId; 
var myQuery = CRM.CreateQueryObj(mySQL,""); 
myQuery.ExecSQL(); 


We have a difficulty here testing whether the execution of the SQL has been successful or not. If we suspect that there may be a syntax error we can wrap the execution with a try...catch...finally statement.

But we really need to see whether the statement returns a message like "3 Rows updated". The ExecSQL method does not return anything not even a Boolean true, false. To workaround this I find myself having to run a Select statement to see whether the data has changed in the way that I expect. This tends to be quite easy for update statements but can be problematic when checking insert statements are you may have existing data that matches the criteria and you may have to check the createdate fields values to limit the selection correctly.
Anonymous
  • Warming to this theme of returning an updated result set, it occurred to me that the Query object would probably work with a SQL OUTPUT clause. And it does. But you need to call the SelectSql() method even if you're issuing an update/insert. Here's an example:

    var mySql = "UPDATE Opportunity SET Oppo_Forecast += 10. ";
    mySql += "OUTPUT inserted.oppo_ReferenceId, ";
    mySql += "deleted.Oppo_Forecast as OldValue, ";
    mySql += "inserted.Oppo_Forecast As NewValue ";
    mySql += "WHERE Oppo_Forecast > 10000.";
    
    var myQuery = CRM.CreateQueryObj(mySql,"");
    
    myQuery.SelectSql();
    
    Response.Write("Affected records = " + myQuery.RecordCount);
    Response.Write("<br/>");
    while(!myQuery.eof) {
       Response.Write(myQuery.Fieldvalue("oppo_ReferenceId") + " : " 
       + myQuery.Fieldvalue("OldValue") + " > " 
       + myQuery.Fieldvalue("NewValue"));
       Response.Write("<br/>");
       myQuery.NextRecord();
    }



  • The ExecSQL() method *does* have a return value - it returns an integer to show the affected record count :


    var mySql = "UPDATE Company SET comp_website = 'http://www.samewebsite.com' where Comp_Type = 'Supplier'";
    var myQuery = CRM.CreateQueryObj(mySql,"");

    var result = myQuery.ExecSQL();
    Response.Write(result);