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

    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

      Hi , 

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

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor

        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;
        }