Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 8 Next »

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 connection string to the connectionStrings collection.  In the example below, in addition to the default formsserver connection string, I have added another one 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.

      <connectionStrings>
<add name="formsserver" providerName="System.Data.SqlClient" connectionString="Data Source=sql-dev;Initial Catalog=Forms_inMotion;User ID=myuser;password=pass;"/>
<add name="queryserver" providerName="System.Data.SqlClient" connectionString="Data Source=sql-dev;Initial Catalog=ERPSystem;User ID=myuser;Password=pass;"/>
</connectionStrings>

    2. Add a queryDefinition section to the formsinMotionConfiguration section of the web.config file.  A full example of this section is shown below

      <formsinMotionConfiguration xmlns="urn:FormsBuilderServer" >
	<authenticationmethod type="application" domain=""/>
	<debug debugfile="c:\temp\formsserverlog\formsserverdebuglog22.txt" level="0"/>
	<license activationcode="AAAAAAAAA-AAAAAAAAA-AAAAAAAAA-AAAAAAAAA-AAAAAAAAA-AAAAAAAAA-AAAAAAAAA-AAAAAAAAA" customername="Joe Customer" licensecode="AAAAAA-AAAAAA-AAAAAA-AAAAAA-AAAAAA"/>
	<email smtpserver="mail.mymailserver.com" smtpport="25" smtpuser="" smtppass="" smtpsenderaddress="FORMS-INMOTION-DO-NOT-REPLY@myserver.com"/>
	<queryDefinitions>
		<queryDefinition name="userIDFromEmpID" querytext="select ADUserName,UID from dbo.ADUser where UID=@employeenum" connectionstringname="queryserver" >
          <parameters>
			<parameter name="@employeenum" datatype="String" />
          </parameters>
        </queryDefinition>
	</queryDefinitions>
</formsinMotionConfiguration>

      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 form itself to make the query call.  All you need to do is call the query function and specify a callback.  An example that would talk to the setup in 1b above would be:

    var empID = $("#employeenum").val();
var parameters = [];
parameters[0] = { name:"@employeenum",value:empID };
Forms_inMotion("query","userNameLookupCallback","userIDFromEmpID", parameters);

    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 callback function that we will be using

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

    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.

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

    function userNameLookupCallback ( data ) {
	var parser = new DOMParser();
	var xmlDoc = parser.parseFromString(data, "application/xml");
	var recordNodesList = xmlDoc.getElementsByTagName("record");
	var j;
	var recordCount = recordNodesList.length;
	if (recordCount>0) {
		for (j=0; j<recordNodesList[0].attributes.length; j++) {
			switch (recordNodesList[0].attributes[j].nodeName.toUpperCase()) {
				case "ADUSERNAME" :
					$("#employeeid").val(recordNodesList[0].attributes[j].nodeValue);
					return;
			}
		}
	}
}

    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 parser, then uses standard DOM manipulation to loop through results looking for tags.  

 

 

  • No labels