Forum Discussion

tristaanogre's avatar
tristaanogre
Esteemed Contributor
7 years ago

Corrected SQLUtilities Script Extension

Thanks to some side work with Novari-QA, we discovered a problem in my SQLUtilities extension which would make it impossible to actually work.  I've updated the TCX file with the bug fix and the new version is available to download at

https://bitbucket.org/privateteamogre/scriptextensions/downloads/SQLUtilities.tcx

 

How to use it can be found at

https://bitbucket.org/privateteamogre/scriptextensions/wiki/SQLUtilities

3 Replies

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor

    There's still some problem with passing in the parameters object.  I'm working on figuring that out so there's an aspect of it that's still not working.

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor

      For those of you who are curious... and perhaps can help... the problem is here:

      function queryObject(query, parameters) {
          this.queryString = query;
          if (parameters == undefined) {
              parameters = [];
          }
          this.parameterArray = [];
          for (parameter in parameters) {
              this.parameterArray.push({name: parameter, value: parameters[parameter]});
          }
      }
      
      
      function getSQLFromFile(filePath) {
          return aqFile.ReadWholeTextFile(filePath, aqFile.ctANSI);
      }
      
      function open() {
      //Opens the connection on the query object and sets it as the Active Connection on the command object property
          this.localConnection.Open();
          this.localCommand.CommandObject.ActiveConnection = this.localConnection;    
      }
      
      function closeAndNull() {
      //Closes the connection on the object and sets the connection and command objects to null to free up memory space
          if (this.localConnection.State != 0) {
              this.localConnection.Close();
          }
          this.localConnection = null;
          this.localCommand = null;
      }
      
      function execute() {
      //assigns the query to the command text, parses it for parameters, and then executes the query as a command, returning any recordset
          this.localCommand.CommandText = this.queryObject.queryString;
          this.localCommand.Parameters.ParseSQL(this.localCommand.CommandText, true);        
          for (var i = 0; i < this.queryObject.parameterArray.length; i++) {
      
              this.localCommand.Parameters.ParamByName(this.queryObject.parameterArray[i].name).Value = this.queryObject.parameterArray[i].value;
          }
          return this.localCommand.Execute();
      }
      
      function sqlQuery(sqlString, parameters, stringIsFile) {
          try {
              if (stringIsFile == undefined) {
                  stringIsFile = false;
              }
              var localObject = {};
              var queryString;
              stringIsFile ? queryString = getSQLFromFile(sqlString) : queryString = sqlString;
              localObject.queryObject = new queryObject(sqlString, parameters);
              localObject.localConnection = ADO.CreateConnection();
              localObject.localConnection.ConnectionString = getConnectionString();
              localObject.localCommand = ADO.CreateADOCommand();
              localObject.open = open;
              localObject.execute = execute;
              localObject.closeAndNull = closeAndNull;
              return localObject;
          }
          catch (exception) {    
              Log.Error('Could not execute sqlQuery: ' + exception.message, exception.stack);
          }
      }

      Specifically, the bolded loop is never getting hit.  Without this, if you want to run a query similar to what I have in the readme in my repository, we get an error that the parameters object of localCommand is not set up properly and the whole thing errors out.  As far as I can tell, for (<property> in <object>) should still work just fine in JScript, but for some reason, the enumeration of the properties is not happening.

       

      So... if y'all have any ideas, I'm open.  I'll still be working on this but the extension should work as is if you handle the parameters of the sql query externally of the objects and just pass in the string and a blank params object.

       

       

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor

    I think I've finally solved my parameters issue with my SQLUtilities extension.  To read about the extension, how to use it, and to find a link to download it, please visit

    https://bitbucket.org/privateteamogre/scriptextensions/wiki/SQLUtilities

     

    Please note the disclaimer on the main WIKI:

     

    The work in this repository is licensed under the Creative Commons Attribution-ShareAlike 4.0 International License. To view a copy of this license, visit the official license site. All the extensions and code available are presented "as-is". Use of the code and extensions applies no liability to the developer or creator of said code.

     

    If you download this and give it a try and have issues with it, please let me know via PM