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

[abhipsa1]

Hi Eric,

This really interesting and most required feature which we would like to explore more ..

Few question which I have

1.How can i use and test the parser ?Is there any plugin or any demo instance

2.What all SQL function as of now can be used ?

3.Is there any way to sort the records based on the Postion or string of a particular string ?

Below are   queries which i am using

select * from cmdb_ci where name like '%NGI%' order by position('NGI' in name) :-Postgres

OR

CHARINDEX('NGI',name):- My sql

Regards

Abhipsa