SQL to GlideRecord Parser

[ericpan]

Hi,

I am new and studying the ServiceNow. While dealing with GlideRecord, I found that it's not very straight forward for me to convert SQL into program codes. I am not saying GlideRecord is not good, it provides abstraction on database which is a big benefit. However, I think it's not necessary not to use SQL in programming. To maintain the benefits and backend intergration of GlideRecord and at the same time allow programmer to use SQL, I think it's good to have something directly parse SQL statement into GlideRecord and JavaScript program. That's why I am doing a SQL to GlideRecorder Parser.

The Parser is still under development. It can only support SELECT ... Fields ... Aggregates ... FROM ... JOIN ... GROUP BY ... HAVING ... ORDER BY without subquery right now. The ultimate target is to parse general SQL statement into GlideRecord and JavaScript program. It's very difficult since SQL cannot be converted to GlideRecord functions directly but require helping codes like looping.

Anyway, it's now being developed and tested. Let me know if you like the Parser.

Example (Note - May Not Correct, Just for Example):

SELECT COUNT(*), field1, MAX(field3) FROM table WHERE table.batman = 1 GROUP BY field1, field2 HAVING COUNT(*) > 1 and MAX(field3) > 6 ORDER BY field1 DESC, MAX(field3)

The Program Segment Generated by the Parser (Again - May Not Correct, Still Being Developed and Tested):

var glideTable = new GlideAggregate("TABLE");

var glideQC["TABLE"] = glideTable.addQuery("1","=","1");

glideQC["TABLE"].addQuery("BATMAN", "=","1");

glideTable.groupBy("FIELD1");

glideTable.groupBy("FIELD2");

glideTable.addAggregate("COUNT","*");

glideTable.addHaving("COUNT", ">", "1");

glideTable.addHaving("MAX", ">", "6");

glideTable.orderByDesc("FIELD1");

glideTable.orderByAggregate("MAX","FIELD3");

Regards,

Eric Poon

[tony_barratt]

Hi Eric,

Sounds very interesting to me. Thanks for sharing.

I was wondering how you are building the parser plus how and where it is invoked?

Best Regards

Tony

[ericpan]

Hi,

Thanks for your interesting.

It will be a JavaScript includes. It will return Glide Record Set instead of JavaScript codes. Currently, my difficulty is that I am not an expert on Glide and I need a lot of time to do research to convert SQL statements into Glide to build the logics in the Parser. If anyone can help me to convert some sample SQL statements into Glide, it would help very much.

Here is the result so far.

Testing Script in Scripts - Background:

var sqlStr = 'SELECT table1.col1, table1.col2 FROM table1 WHERE table1.col3 = \'test\'';

var sqlToGlide = new SQL2Glide();

gs.print(sqlToGlide.parse(sqlStr));

The Result Returned:

*** Script: var glideTable = new GlideRecord("table1");var glideQC["table1"] = glideTable.addQuery("1","=","1");glideQC["table1"].addQuery("col3", "=", "'test'");

However, for complex query, the script still has bugs.

Thanks,

Eric Poon

[ericpan]

Okay, the version 1.0 is now working. Testing on more complicated SQL queries.

Two modes implemented, 1 - return Glide Table Object, 2 - return Glide JavaScript Code. A demo below.

Let me know if you are interested. This version can be shared with you for free use after I finished the testing.

Mode 1

var GlideTable = (new SQL2Glide()).parse("SELECT sys_user.* FROM sys_user WHERE sys_user.first_name = 'Tit' AND sys_user.last_name = 'Poon'", 1);

GlideTable.query();

while (GlideTable.next()) {
gs.print(GlideTable.first_name + ' ' + GlideTable.last_name);
}

Result:

*** Script: Tit Poon

Mode 2

var GlideTable = (new SQL2Glide()).parse("SELECT sys_user.* FROM sys_user WHERE sys_user.first_name = 'Tit' AND sys_user.last_name = 'Poon'", 2);

gs.print(GlideTable);

Result:

var glideTable = {};

glideTable["sys_user"] = new GlideRecord("sys_user");

glideTable["sys_user"].addQuery("first_name", "=", 'Tit');

glideTable["sys_user"].addQuery("last_name", "=", 'Poon');

[tony_barratt]

Hi Eric,

Good work!

There is the possibility to share, and benefit from others sharing on

ServiceNow Share

Best Regards

Tony