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:
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.
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> |
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. |
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. |
Oracle expects the parameters to be referenced with a : instead of an @ symbol. An example Oracle query might be:
|
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:
"query" is always used for a query call
the name of the javascript callback function that will receive the results of the query
the name of the query exactly as defined in our web.config file, in this case userIDFromEmpID as shown above
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.
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 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> |
Related articles appear here based on the labels you select. Click to edit the macro and add or change labels.
|