Using the Query API

Owned by Aaron Moller (Unlicensed)
Last updated: Nov 24, 2020 by Austin GrahamVersion comment
4 min read

The query API allows you to make a query from your HTML form through the Forms inMotion server.  At this time the 2 requirements for using it are:

  1. The query must be SQL based
  2. The database must be hosted in a location that the Forms inMotion server can access


Step-by-step guide

  1. Modify the web.config file for your Forms inMotion server to contain the query information.  This means

    1. Add a new connection string to the connectionStrings collection for your query server.  In the example below, in addition to the default formsserver connection string, I have added another one called queryserver to be used in our query.  You can choose any name you like for this that is valid XML and is not "formsserver" since that is reserved for the application.

    2. Add a new queryDefinition section to the existing queryDefinitions section in the formsinMotionConfiguration section of the web.config file.  A full example of the modifications 1a and 1b is shown here.  Note that parameters are definited with @parametername format in both the querytext and parameter definition sections.

      <formsinMotionConfiguration>
 
...
 
      <queryDefinitions>
		<queryDefinition name="departmentsForWhichYouCanProxy" querytext="select ADUserName,Department from dbo.ADDepartment where ADUserName = @username" connectionstringname="querysystem" >
          <parameters>
          </parameters>
        </queryDefinition>
		<queryDefinition name="userinfoquery" querytext="SELECT * FROM cc_SSN_vw where (SSN = @ssn or CCN = @casenum) and DOB = @dateofbirth" connectionstringname="querysystem" >
          <parameters>
			<parameter name="@ssn" datatype="String" />
			<parameter name="@casenum" datatype="String" />
			<parameter name="@dateofbirth" datatype="DateTime" />
          </parameters>
        </queryDefinition>
		<queryDefinition name="currentUserID" querytext="select ADUserName,UID from dbo.ADUser where ADUserName = @username" connectionstringname="querysystem" >
          <parameters>
          </parameters>
        </queryDefinition>
		<queryDefinition name="userIDFromEmpID" querytext="select ADUserName,UID from dbo.ADUser where UID=@employeenum" connectionstringname="querysystem" >
          <parameters>
			<parameter name="@employeenum" datatype="String" />
          </parameters>
        </queryDefinition>
      </queryDefinitions>
    </formsinMotionConfiguration>
...
 
<connectionStrings>
    <add name="formsserver" providerName="System.Data.SqlClient" connectionString="Data Source=SQLSRV; Initial Catalog=Forms_inMotion;User Id=Forms_inMotionUser;Password=formsinMotionPass"/>
    <add name="querysystem" providerName="System.Data.OracleClient" connectionString="Data Source=servername;User id=tst;Password=mypass;"/>
</connectionStrings>

      Anonymous Form Queries

      Queries in Anonymous forms are disabled by default for security purposes. However, if a queries results are safe for public viewing and the query is required in an anonymous form, then this can be allow by adding AllowAnonymous="true" to the queryDefinition.

      Reserved Word: username

      When building your query, there is one special parameter @username. If this is included in the querytext, the server will automatically replace it with the name of the logged in user prior to merging any of the other parameters supplied from the form.

      Querying an Oracle DB

      Oracle expects the parameters to be referenced with a : instead of an @ symbol. An example Oracle query might be:

      <queryDefinition name="getAllFYAccounts" querytext="SELECT dept_cd, dept_nm FROM admown.r_dept_fy WHERE fy=:fYear" connectionstringname="advantage">
   <parameter>
      <parameter name="fYear" datatype="String" />
   </parameters>
</queryDefinition>

  2. With the web.config setup.  You can turn your attention to the HTML form itself to make the query call.  The query is performed by calling a native API function inside your javascript called Forms_inMotion.  Your parameters will be passed into the function as an array.  You also must specify the callback javascript function that will receive the results of the query.  An example is below.  Here we initialize a new parameters array.  Add a single parameter (note that the @ syntax persists here as well) at the 0th position.  This parameter value is a JSON object with keys for name and value.  Then the function is called

    var dob = document.getElementById('dateofbirth').value;
var ccn = document.getElementById('casenum').value;
var parameters = [];
parameters[0] = { "name":"@dateofbirth","value":dob};
parameters[1] = { "name":"@casenum","value":ccn };
Forms_inMotion("query","userInfoLookupCallback","userinfoquery", parameters, "xml");

    Here you can see the Forms_inMotion function call is made with the following parameters:

    1. "query" is always used for a query call

    2. the name of the javascript callback function that will receive the results of the query

    3. the name of the query exactly as defined in our web.config file, in this case userIDFromEmpID as shown above

    4. and a parameters array.  Pass in an empty array if you have no parameters to use.  Also make sure that the names match exactly what you defined in the web.config file.

    5. XML or JSON can be requested as the return type depending on your application.  The example below shows processing a response with XML.  XML will be the default if none is specified.

  3. Upon return, the data will be pushed to the callback function you specified in 2 above.  An example of this would be like the one below

    function userInfoLookupCallback( data ) {
	var parser = new DOMParser();
	var xmlDoc = parser.parseFromString(data, "application/xml");
	var recordNodesList = xmlDoc.getElementsByTagName("record");
	var i,j;
		var recordCount = recordNodesList.length;
		var cdr, offensestatute, offensedescription;
		if (recordCount>0) {
			for (i=0; i<recordNodesList.length; i++) {
				for (j=0; j<recordNodesList[i].attributes.length; j++) {
					switch (recordNodesList[i].attributes[j].nodeName.toUpperCase()) {
						case "USERNAME" :
							$("#username").val(recordNodesList[i].attributes[j].nodeValue);
							break;
						case "DOB" :
							$("#dateofbirth").val(recordNodesList[i].attributes[j].nodeValue);
							break;
						case "CASENUM" :
							$("#casenum").val(recordNodesList[i].attributes[j].nodeValue);
							break;
					}
				}
			}
		}
	}
}

    Note that the data is passed back as a chunk of xml.  So the method above takes the data xml content, runs it through the browser's native parser, then uses standard DOM manipulation to loop through results.  One loop goes through each record result, one loop goes through each attribute in that record.  For reference a sample of return XML results can be seen below that illustrates the records/attribute relationship.  Note that all columns from your SQL query will be represented as an attribute (with spaces removed).

    XML Results of Employee Query
    <?xml version="1.0"?>
<xml>
  <results>
    <record authnum="3520049" fname="JOHN" lname="SMITH" AMT="723.40" sdate="11/17/2014 12:00:00 AM" apr="" empid="108594"/>
    <record authnum="3520050" fname="JOHN" lname="SMITH" AMT="723.40" sdate="11/17/2014 12:00:00 AM" apr="" empid="108594"/>
    <record authnum="3520051" fname="JANE" lname="SMITH" AMT="723.40" sdate="11/17/2014 12:00:00 AM" apr="" empid="108594"/>
    <record authnum="3520052" fname="JANE" lname="SMITH" AMT="723.40" sdate="11/17/2014 12:00:00 AM" apr="" empid="108594"/>
    <record authnum="4374468" fname="JOHN" lname="SMITH" AMT="" sdate="7/25/2015 12:00:00 AM" apr="" empid="108594"/>
  </results>
  <recordcount>5</recordcount>
</xml>