cunderw
8 years agoCommunity Hero
Utility For Getting Stored Procedure Results
After battling trying to successfully get results back from a store procedure a colleague and I came up with a nifty utility for it we would like to share.
// Object is used to provide parameters for stored procedure calls var storedProcs = { example : { procedureName: "A.StoredProc", names: ["Param1", "Param2", "Param3"], dataTypes: [adDecimal, adDecimal, ftFixedChar], size: [0, 0, 32000], dataTypeString: ["adDecimal", "adDecimal", "ftFixedChar"], direction : [adParamInput,adParamInput,adParamInput] } }; /** * @function * @param {string} server - the server to connect to and run the store procedure * @param {string} procedure - a matching procedufre from the var storeProcs * @param {string} paramsString - a comm seperated list of paramets to use in the store procued, must equal the number in the the storedProcs obj */ function getStoreProcResult(server, procedure, paramsString) { Log.Message("Params: " + paramsString); var record, infoObj = {}, infoArr = [], cmd, paramsList = [], proc, recordSet, seq = 0; proc = storedProcs[procedure]; try { cmd = ADO.CreateADODataSet(); cmd.ConnectionString = server; cmd.CommandText = proc.procedureName; cmd.CommandType = cmdStoredProc; paramsList = paramsString.split(","); // add params based on paramstString and the storeProc for(var i = 0; i < paramsList.length; i++){ cmd.Parameters.AddParameter(); // if float, change to number and set value if(proc.dataTypeString[i] == "adDecimal") { cmd.Parameters.Items(i).Value = aqConvert.StrToFloat(paramsList[i]); } // otherwise set string value else { cmd.Parameters.Items(i).Value = paramsList[i]; } // set name and datatype cmd.Parameters.Items(i).Name = proc.names[i]; cmd.Parameters.Items(i).DataType = proc.dataTypes[i]; // set size if size is not 0 if(proc.size[i] !=0 ){ cmd.Parameters.Items(i).Size = proc.size[i]; } // set direction of param cmd.Parameters.Items(i).Direction = proc.direction[i]; } // Open the dataset matching this given information cmd.Open(); // Obtain the recordSet object from it recordSet = cmd.RecordSet; // Use this to create a JSON object while(!recordSet.EOF) { infoObj = recordToObject(recordSet); logObject(infoObj,"See Additional Information For Store Proc Result Row: " + seq); infoArr.push(infoObj); seq++; recordSet.MoveNext(); } } catch(err){ Log.Error("See additional info ", + err.message + "\n" + err.stack); } finally{ // Close the DataSet cmd.Close(); return infoArr; } } /** * Takes an ADO record and converts it to a javascript object * @Author {CBU} * @function * @param {adoRecord} rec - the result of an ADO query * @returns {object} retval - the record converted to a javascript object. */ function recordToObject(rec) { var retVal = {}; try { if(!rec.EOF) { for(var i = 0; i < rec.Fields.Count; i++ ) { retVal[rec.Fields.Item(i).Name] = rec.Fields.Item(i).Value == null ? "" : aqString.Trim(rec.Fields.Item(i).Value); } } else { Log.Error("There is nothing in the record set"); } } catch(err) { Log.Error("There was an error building object. See additional information",err.message + "\n" + err.stack); } finally { return retVal; } } /** * Takes an object and logs all of its props and vals in an table * @function * @author [CBU] * @param {obj} obj -the object you want to log info for */ function logObject(obj,msg) { var attr = Log.CreateNewAttributes(), str = '<table style="width:75%;"><tr><th>Property</th><th>Value</th></tr>'; attr.ExtendedMessageAsPlainText = false; try { for(var key in obj) { str += '<tr><td>' + key + "</td><td>" + obj[key] + "</td><tr>"; } } catch(err) { Log.Message(err); } finally { str += '</table>' if(!msg) { Log.Message("See Additional Info For Object Details",str,300,attr); } else { Log.Message(msg,str,300,attr); } } }