reading the results of gs.sql query into a GlideRecord or variable in a script?

[adelnspi]

Can anyone shed some light on the proper syntax to read the results of the gs.sql command into a GlideRecord or variable?

It seems "gs.sql" is a secret feature, Googling it turned up only a few pages, with no examples of capturing the results inside a variable:

   
1. GLIDESYSTEM SQL
   
2. Fun With Queries
   
3. Run SQL statement
   
4. Record not found....
   
5. Executing Direct SQL in Service-Now
   
6. Correcting Invalid sys_class_name Entries in your CMDB

The last site (robpickering.com) gives this warning:

A Note About gs.sql():   I cannot stress this enough. This is undocumented for a reason. There are VERY few security checks when you use gs.sql() and the damage you do with this GlideSystem call can be absolute and unrepairable. You should not use it, you should forget you found it here. Incorrect use, a mistake, a typo, can easily render your Instance completely and totally unusable.

However I am looking to use gs.sql for read-only access for a developer tool, not for updating the database or in regular production.

Here is the test code I am running in background script editor:

       var sValue = "";
       
       gs.print("");
       gs.print("-------------------------------------------------------------------------------");
       gs.print("Test #1, call gs.sql directly:");
       gs.print("\t" + "gs.sql(\"select count(*) as iRowCount from sys_user\");" + "");
       gs.sql("select count(*) as iRowCount from sys_user");
       
       gs.print("");
       gs.print("-------------------------------------------------------------------------------");
       gs.print("Test #2, capture gs.sql in a variable:");
       
       try
       {
               // try using gs.sql as a row source
               var sqlResult = gs.sql("select count(*) as iRowCount from sys_user");
               
               // populate GlideRecord with row source
               var gr = new GlideRecord(sqlResult);
               gr.query();
               
               // retrieve value(s)
               if (gr.next())
               {
                       sValue = "" + 
                               "gr.getRowCount=" + gr.getRowCount().toString() + 
                               ", " + 
                               "value=" + gr.getValue("iRowCount").toString();
               }
               else
               {
                       sValue = "(No records.)";
               }
       }
       catch (e)
       {
               sValue = "ERROR: " + e.message;
       }
       
       // output results
       gs.print("Results: " + sValue + "");
       
       gs.print("-------------------------------------------------------------------------------");
       gs.print("Finished gs.sql test");

Here is the output:

       *** Script: 
       *** Script: -------------------------------------------------------------------------------
       *** Script: Test #1, call gs.sql directly:
       *** Script:         gs.sql("select count(*) as iRowCount from sys_user");
       iRowCount
       25189
       *** Script: 
       *** Script: -------------------------------------------------------------------------------
       *** Script: Test #2, capture gs.sql in a variable:
       iRowCount
       25189
       *** Script: Results: ERROR: GlideRecord.query() - invalid table name: null (; line 20)
       *** Script: -------------------------------------------------------------------------------
       *** Script: Finished gs.sql test

Just calling the gs.sql directly seems to work, but the glide system sees it as a null table name when using it as a row source.

Is it not possible to read into a GlideRecord or variable or am I just using the wrong syntax?

Any help would be most appreciated... Thanks

[Michael Kaufman · Accepted solution]

I am the author of the Glide System SQL — ServiceNow ELITE.com article.   I also have "scripting tool" that might be similiar to what you are building.   Scripting Tool — ServiceNow ELITE.com

In that scripting tool, I used glide aggregate for row count.

var count1 = new GlideAggregate(script_tool_table_name);

count1.addQuery(script_tool_query);

count1.addAggregate('COUNT');

count1.query();

if (count1.next()) {

gs.addInfoMessage('Table Row Count: '+script_tool_table_name+ ' contains '+ count1.getAggregate('COUNT') + ' rows.');

I would use the gs.sql sparingly. I hesitated even writing the gs.sql article because people might start using it to replace standard GlideRecord queries, which is not great.

Mike

[adelnspi]

Thanks for your reply.  

I will give the glide aggregate method a try (it sounds promising)and let you know how it went

Thanks also for sharing the link to that scripting tool, I will check it out.

[adelnspi]

The aggregate method you shared is dramatically faster than doing a straight GlideRecord.getRowCount - thank you for your help!