Page Comparison

...

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.

      Code Block
      <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

      Code Block

      <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>

      
      

      Info
      title 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.

      
      

      Info
      title Querying an Oracle DB
      Oracle expects the parameters to be referenced with a : instead of an @ symbol. An example Oracle query might be: Code Block language xml <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:

   Code Block
   var empID = $("#employeenum").val(); var parameters = []; parameters[0] = { name:"@employeenum",value:empID }; Forms_inMotion("query","userNameLookupCallback","userIDFromEmpID", 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 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.

   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 2b above.  An example of this would be like the one below

   Code Block
   language js linenumbers true
   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.  
   
   

...