Skip to main content

How-To: Integrate ProntoForms with SQL Server with the REST API

  • Updated

Looking for information about TrueContext features and functionality? Visit our Product Documentation Portal.

Note: This article refers specifically to integration with Microsoft SQL Server®. Though some details may differ, the same general approach can be applied to integration with other relational database systems like Oracle, MySQL, PostgreSQL, or IBM DB2.

Overview

What does this article provide? We’ll be showing what is needed to integrate to and from ProntoForms and SQL Server®. A couple items you will need before you begin:

    1. Basic programming knowledge.
    2. Microsoft SQL Server® knowledge (we will not be going in to how to install or maintain SQL Server®).
    3. Understanding of how REST API’s work.
    4. General ETL knowledge.
    5. You will need a SQL server up and functioning.

We’ll show two distinct integration cases; the first will be how to dispatch data from a table to ProntoForms. This means how to send data to a ProntoForms device through the dispatching API. The second is how to get ProntoForms data to an SQL table.

Dispatching Data from SQL Server® to ProntoForms

1. Choose the table from the SQL server you want to dispatch data from.
2.  You will most likely need some form of connection to your SQL server. Typically this is an ODBC connection. You can export your information from the table from SQL Server Management Studio® if needed. 
RETCODE retcode;
UCHAR *szDriver = "SQL Server";
UCHAR *szAttributes = 
   "DSN=my65dsn\0DESCRIPTION=SQLConfigDSN Sample\0" 
   "SERVER=my65server\0ADDRESS=HRServer\0NETWORK=dbmssocn\0"
   "DATABASE=pubs\0";
retcode = SQLConfigDataSource(NULL,
   ODBC_ADD_DSN,
   szDriver,
   szAttributes);

How to export from SQL Server®
3. Once you can connect to your database and obtain information from the table, you will need to format it in a way in which makes sense for your form. You will need to know the ProntoForms schema for dispatching information to the ProntoForms backend. This information can be found on their REST API documentation page. XML 
<dispatch>
 <formId>140001</formId>
  <dispatchToDraft>true</dispatchToDraft>
  <userId>160002</userId>
  <data>
     <answer label="firstName">John</answer>
     <answer label="lastName">Smith</answer>
  </data>
</dispatch>

JSON

{
 "formId": "140001",
 "username": "bjohnston",
 "data": [
   { 
       "label": "colors",
       "answer": "grey"
   },
   {
       "label": "colors",
       "answer": "blue"    
   }
 ]
}
4. Before you can make any REST API calls to ProntoForms you will need to setup an application to do so. Detailed information on that can be found on the ProntoForms page.  Set up an API Application
5. Now that you have everything in place you will need to make a REST API call with the dispatch information. Based on the type of data you selected, either XML or JSON you will need to point to the correct URL, Dispatch.xml or Dispatch.json and POST your data to the server. 
// Upload a file to an HTTP server.
pplx::task<void> UploadFileToHttpServerAsync()
{
   using concurrency::streams::file_stream;
   using concurrency::streams::basic_istream;
// To run this example, you must have a file named myfile.txt 
in the current folder. 
// Alternatively, you can use the following code to create a stream 
from a text string. 
// std::string s("abcdefg");
// auto ss = concurrency::streams::stringstream::open_istream(s);
// Open stream to file. 
  return file_stream<unsigned 
char>::open_istream(L"myfile.txt").then([](pplx::task<
basic_istream<unsigned char> previousTask)
 {
 try
 {
    auto fileStream = previousTask.get();
    // Make HTTP request with the file stream as the body.
    http_client client(L"http://www.fourthcoffee.com");
    return client.request(methods::PUT, L"myfile", fileStream).then
([fileStream](pplx::task<http_response>previousTask)
   {
    fileStream.close();
    std::wostringstream ss;
    try
    {
      auto response = previousTask.get();
      ss << L"Server returned returned status code " <<
 response.status_code() << L"." << std::endl;
    }
 catch (const http_exception& e)
 {
 ss << e.what() << std::endl;
 }
 std::wcout << ss.str();
 });
 }
 catch (const std::system_error& e)
 {
   std::wostringstream ss;
   ss << e.what() << std::endl;
   std::wcout << ss.str(); 
   // Return an empty task. 
   return pplx::task_from_result();
  }
});
/* Sample output:
 The request must be resent
 */
}
6. Detailed information on REST API calls from Windows® can be found here. REST API CALLS
7.  Once the dispatch call has taken place you will receive a response from the server on the status of the call.  This allows you to deal with how the call went or have access to the dispatched data if from ProntoForms.

XML

<dispatchResult>
  [dataId]123[/dataId>
  <referenceNumber>20110902-123</referenceNumber>
</dispatchResult>

JSON

{
  "dataId": "123",
  "referenceNumber": "20110902-123"
}

Retrieving ProntoForms Data and Integrating with SQL Server®

1.

 As with dispatching data from SQL Server®, you will need to have an application setup in ProntoForms to talk to the REST API on your account. Set up an Application

2.

 You will then need to identify which form you would like to get data for. Specifically you will need the form id which can be found here.

Where to locate Form ID in the URL

3. 

 This time you will want to make a REST API GET Call from ProntoForms in the specific format you would like receive the data in. 
// Builds an HTTP request that uses custom header values.
pplx::task<void> HTTPRequestCustomHeadersAsync()
{
    http_client client(L"http://www.fourthcoffee.com");

    // Manually build up an HTTP request with header and request URI.
    http_request request(methods::GET);
    request.headers().add(L"MyHeaderField", L"MyHeaderValue");
    request.set_request_uri(L"requestpath");
    return client.request(request).then([](http_response response)
    {
        // Print the status code.
        std::wostringstream ss;
        ss << L"Server returned returned status code " <<
 response.status_code() << L"." << std::endl;
        std::wcout << ss.str();
    });

    /* Sample output:
    Server returned returned status code 200.
    */
}

4.

 First you will need to get listing of records for this form. These items are request parameters for the API call which you will want to put in the form information.

Request Parameters

Parameter

Required

Default Value

Description

 p

 no

0

 0-based page number.

 s

 no

100

 Number of results in the page.  The maximum value is 100.

 tz

 no

UTC

 Time zone to present all dates in.

 stime

 no

  

 Lower-bound limit on the Server Receive Date of Data Records

 etime

 no

  

 Upper-bound limit on the Server Receive Date of Data Records

 fsids

 no

  

 Comma-separated set of FormSpace Identifiers the Data Records belong to

 fids

 no

  

 Comma-separated set of Form Identifiers the Data Records belong to

 gids

 no

  

 Comma-separated set of Group Identifiers the Data Records must have been submitted by

 uids

 no

  

 Comma-separated set of user Identifiers the Data Records must have been submitted by

 unames

 no

  

 Comma-separated set of Usernames the Data Records must have been submitted by

5.

 The data will be returned to you in the format specified from the call.

XML

<pagedList totalNumberOfResults="1">
   <zone>America/New_York</zone>
   <dataRecord identifier="123">
     <referenceNumber>20110911-123</referenceNumber>
     <state>Complete</state>
     <actionState>Processing</actionState>
     <dataPersisted>true</dataPersisted>
     <serverReceiveDate>2011-09-30T18:20:46-05:00</serverReceiveDate>
     <formVersionId>123</formVersionId>
     <formId>234</formId>
     <userId>345</userId>
     <username>uname</username>
   </dataRecord>
 </pagedList>

JSON

{
  "totalNumberOfResults": 1,
  "zone": "America/New_York",
  "pageData": [{
    "identifier": "123",
    "referenceNumber": "20110911-123",
    "state": "Complete",
    "actionState": "Processing",
    "dataPersisted": true,
    "serverReceiveDate": "2011-09-30T18:20:46-05:00",
    "formVersionId": "345",
    "formId": "234",
    "userId": "123",
    "username": "uname"
  }]
}
6. Now that you have all the records listed you will need to get individual record data for the form. This is another REST API call, this will be a GET call. You need to loop through the list and call this for each data record id. URL Structure 
api.prontoforms.com/api/[version]/data/[id].xml
api.prontoforms.com/api/[version]/data/[id].json 
api.prontoforms.com/api/[version]/data/[id].pdf
api.prontoforms.com/api/[version]/data/[id].doc
api.prontoforms.com/api/[version]/data/[id].html
7. As you receive each piece of data from ProntoForms you will need to modify it in a way which makes sense for the table you are importing into.  
8. Again, use a connection method to SQL Server to insert the data in to the table.  

*SQL Server® is a registered trademark of Microsoft®. ProntoForms is not affiliated with, nor has this article been authorized, endorsed or otherwise approved by Microsoft Corporation.

Related articles

Comments

0 comments

Please sign in to leave a comment.