Skip to main content

Sql queries result table column selecting javascript function

I was looking for a script which do the above. I couldn't find any. So I wrote a one. The following function takes a sql query string such as
"SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName SELECT AVG(OrderPrice) AS OrderAverage FROM Orders" and returns a string array containing LastName,FirstName,OrderNo.


function brakeColumns(query) {
  var columnList = new Array();
  var patten1 = /^[\w\$][\w]*/i;
  var patten2 = /^[\w\$][\w]*\.[\w\$][\w]*$/i;
  var numEnd = query.search(/\sfrom\s/i);
   var elems = (query.substring(6, numEnd)).split(",");
   for (var i = 0; i < elems.length; i++) {
       var elem = elems[i].toString();
       elem = trim(elem);
       var stPoint = "";
       var wanted = "";
       if (elem.search(/\sas\s/i) != -1) {
           stPoint = elem.search(/\sas\s/i);
           wanted = elem.substring(stPoint + 4);
           wanted = trim(wanted);
           if (patten1.test(wanted)) {
               columnList.push(wanted);
           }
       } else if (patten2.test(elem)) {   //check for tablename.column name pattern
           stPoint = elem.search(/\./);
           wanted = elem.substring(stPoint + 1);
           wanted = trim(wanted);
           if (patten1.test(wanted)) {
               columnList.push(wanted);
           }
       } else {   //check for straight column name like username,passsword etc
           if (patten1.test(elem)) {
               columnList.push(elem);
           }
       }
   }
   return(columnList);
}


function trim(stringToTrim) {
   return stringToTrim.replace(/^\s+|\s+$/g, "");
}

Comments

Post a Comment

Popular posts from this blog

Projecting HTML Elements on a circle using Javascript

Recently I wonted to create a tree control with a different approach. The requirement was to represent a node tree with main node on the circle center and it's child nodes projected on a circle around it. This is what i wanted. My solution has 5 files prototype.js This is a handy javascript framework. You can download the latest version from there website ( www.prototypejs.org/download ). I am using this to simply handle the onload event in the page. circle.html script.js node-main-left.gif node-sub-left.gif The circle.html, prototype.js and the script.js files are in the root folder and the two images are inside a folder named “images”. This is how the html file looks(circle.html). <html> <head> <script src="prototype.js" type="text/javascript"></script> <script src="script.js" type="text/javascript"></script> <script language="javascript" xml:space...
8 comments
Read more

Simple client side validation library

  Client side validation has never been easy.. Include jQuery and the validator.js < script src = " http://code.jquery.com/jquery-1.11.3.min.js " ></ script > < script src = " ../validate.js " ></ script > Simple validation < form id = " myForm " > < label >User Name < span class = " red-star " >*</ span > </ label > < input type = " text " name = " userName " class = " validate-required " /> < br /> < label >Password < span class = " red-star " >*</ span ></ label > < input type = " password " name = " password " class = " validate-required " /> < br /> < input type = " submit " value = " Login " /> </ form > ...
1 comment
Read more

APIM 3.0 - populate multiple apis - bash script

Created a bash script to create, tag and publish multiple APIs. This is useful to populate data for the landing page. #!/bin/bash # get the URL consumer key clientId=$(curl -k -X POST -H "Authorization: Basic YWRtaW46YWRtaW4=" -H "Content-Type: application/json" -d @payload.json https://localhost:9443/client-registration/v0.14/register | jq -r '.clientId') clientSecret=$(curl -k -X POST -H "Authorization: Basic YWRtaW46YWRtaW4=" -H "Content-Type: application/json" -d @payload.json https://localhost:9443/client-registration/v0.14/register | jq -r '.clientSecret') echo $clientId echo $clientSecret encoded=$(echo -ne $clientId:$clientSecret | base64) echo $encoded # get access token accessToken=$(curl -k -d "grant_type=password&username=admin&password=admin&scope=apim:api_view,apim:api_create" -H "Authorization: Basic $encoded" https://localhost:9443/oauth2/token | jq -r '.access_token'...
Post a Comment
Read more