How do I write answers to a SQL database?

 

About

There are several ways to build a solution which inserts answers into a database. This article walks through a method which uses an HTTP POST data destination to a web service, which then writes to a MySQL database. This is a very simplified example which inserts two values into a table. However, this same method may be applied to much more complex forms or requirements.

This article offers an example, but ProntoForms support cannot help you troubleshoot issues with DreamFactory or your third-party database vendor.
 

Before You Begin

You should have the following:

  1. A relational database instance (MySQL, SQL Server, DB2, Oracle, etc..) and know the following parameters:
    • Host
    • Port
    • Database name
    • Username
    • Password

  2. A web service which will:
    • accept a REST API request,
    • handle the connection to the database,
    • execute an INSERT SQL statement.

    For this example, we have used DreamFactory with a MySQL Database.

  3. A form where you have documented the question labels you would like inserted into the database.

[ top ]

Sample Steps

As our example uses Dreamfactory, we have to create a Freemarker Document template to format the data in the JSON in a Dreamfactory-readable format.

  1. Create a freemarker document template. Example .ftl file contents:
    {"resource":[{"Column_name1":"${drel("%a[Question_Label1]")}","Column_name2":"${drel("%a[Question_label_2]")}"}]}
    • The content type should be "application/json"
    • The file extension should be set to ".ftl"
    • Update the ftl file to match the column name and question names for your particular form
    • It's helpful to test your syntax and connection information to the web service using the chrome plugin "Postman", substituting DREL values for hard coded values




  1. Configure DreamFactory:
    • Sign up at DreamFactory.com
    • Define a "Service" (Database connection). Set the Service type to match your database type and fill in the required database connection information.
    • On the "Apps" tab, Create an API key to be used with ProntoForms. 

  2. Define an HTTP POST Data Destination within ProntoForms with a connection to DreamFactory:
    The base connection URL will look something like:
    https://<DF instance>.dreamfactory.com/api/v2/db4free-mysql/_table/<Table_Name>?api_key=<API Key>



  3. Configure your form to submit the Freemarker document to the DreamFactory destination. The form diagram will appear similar to:



  4. Submit the form and check that the answers are inserted correctly in the DB.
    You can either manually connect to database using the appropriate tool for your specific database (e.g. MySQL Workbench for MySQL) to check the table. Alternatively, you can run a request through Postman to check the values in the table.
    Example with Postman:
    GET https://<DF_Instance>.dreamfactory.com/api/v2/db4free-mysql/_table/<Table_Name>?api_key=<API_KEY>


 

[ top ]

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments