Allow standard SQL syntax to populate a GlideRecord (incl. joins, group by, case-when, subqueries, table variables, etc.)
Developers with experience querying relational databases using SQL can tell you that pulling and joining data is far easier using SQL syntax than using a Web GUI (fine for beginners but very slow and tedious compared to typing a query for those who know SQL) or having to define the query with Glide commands in code (very limited compared to SQL, ie no joins, cross-tab, etc.)
When googling around to see if it was possible I came across the gs.sql command, which seems to be an unofficial way to query ServiceNow with SQL-like syntax, for example
gs.sql("select count(*) from incident");
However, it is undocumented and what little I found had warnings about it being "dangerous" and unsupported.
I'm not sure how ServiceNow's underlying data engine is implemented - although the data appears to be standard tables like in a regular relational DB, the core system may not be implemented using traditional SQL. Also it is understandable that giving all the users unlimited access to SQL commands can wreak havoc from a support standpoint. By abstracting away the physical database, SN avoids (and helps their customers avoid) a lot of pitfalls you can run into with traditional relational databases.
That said, it would just be VERY helpful if we could populate GlideRecords using standard SQL syntax (including joins, cross-tabs, sub-queries, and at least the basic commands you get in MS T-SQL or Oracle PL/SQL).
I'm not asking SN to re-invent their database implementation, just provide a layer that "emulates" SQL so that we programmers used to SQL have more powerful and familiar tools to work with our data.
A couple of sample use cases would be joining tables in a certain way, populating a GlideRecord with a crosstab query (pivot table), more easily transform data inside a script for whatever it needs.
Supporting SQL syntax would go hand-in-hand with the suggestion for ODBC support from external applications. That way a standard SQL query could be written and re-used from inside ServiceNow or an external application.
Note: we have been able to query our ServiceNow data from SQL Server Management Studio using a linked database and openquery, however this is very limited. In order to fully access the data from a SQL environment, it is necessary to create a view for each table, pulling each field as string data and casting it as the specific data type. We have had a lot of issues such as timeouts with large data sets, truncation of long fields, etc. Standardized ODBC would make life a lot easier.
-
Anonymous commented
servicenow provides an odbc plugin driver.