Forum Discussion

mspatel's avatar
mspatel
Contributor
8 years ago

Generic Function to Execute different SQL operations

Hi , 

Is it possible to have a single function that would handle most of the SQL queries. for now , i have seperate SQL function for different type of operations. 

 

for example 

- function that would just execute select

- function that would just handle insert

- function that would just handle delete

- function that would just handle Update

- function that would return count of records ,e.g count(*)

 

I would like to write something generic that would handle all of the above operations

 

Your help is appreciated 

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    8 years ago

    Actually, as a matter of fact, you can. I have a script extension I've developed that will do that.    Basically, if you can write what you want as Transact-SQL, you should be able to execute it using that extension.

     

    If you don't want to use the extension, check out https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sources/databases/using-ado-and-bde-objects/index.html.  The examples that are included have hardcoded strings as the SQL query.  Replace those strings with a parameter from the function and you should be able to send any SQL query you want to to the function.

     

    • mspatel's avatar
      mspatel
      Contributor
      8 years ago

      Hi , 

      Thanks a lot.  Do you happened to have code using all these function calls ? 

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        8 years ago

        Here's an example:

         

        //The following function can be included in any project but it only ever needs to be run the first time to set up the desired settings
function SQLOptions(){
    SQLUtilities.SetSQLType = 'MSSQL'; //Can be either MSSQL or MYSQL_351
    SQLUtilities.SetSQLSecurityType = 'INTEGRATED'; //Can be either INTEGRATED or PROMPT
}

//Now, if I want to run an SQL query against my database where I log the street address for all addresses in Pennsylvania, USA
function getPAStreetAddresses() {
    var mySQLQuery;
    var myRecords;
    SQLUtilities.DatabaseName = 'MyDatabase';
    SQLUtilities.SQLServerName = 'MyServer';
    SQLUtilities.SQLUserName = 'myusername'; //This is not necessary since I'm using integrated security. Provided in example to allow for editing and adaptation
    SQLUtilities.SQLPassWord = 'mypassword'; //This is not necessary since I'm using integrated security. Provided in example to allow for editing and adaptation
    mySQLQuery = SQLUtilities.NewSQLQuery('SELECT * FROM ADDRESSES WHERE STATE = :STATE and COUNTRY = :COUNTRY', {STATE: 'PA', COUNTRY: 'USA'}, false);
    mySQLQuery.open();
    myRecords = mySQLQuery.execute();
    while (!myRecords.EOF) {
        Log.Message('Street Address: ' + myRecords.Fields.Item('STREETADDRESS').Value);
        myRecords.MoveNext();
    }
    mySQLQuery.closeAndNull;
}