ORDS as a JotForm webhook: An actual customer use case

by

Recently, we (one of our Support engineers and I) worked with a customer who had integrated various ORDS POST APIs with a web-based form application called JotForm. JotForm offers integrations for numerous workflows, some official and others you can create independently. That’s where we come in.

In this example, a user would enter information into an online JotForm form, and when the user hits Submit, that would trigger a Webhook to an ORDS endpoint. But in this customer’s scenario, some of the data he was expecting wasn’t arriving in his database (he was on a 21c, but I tested these in a 23ai Autonomous database—the same still applies to both). For our customer, some of the fields in his table showed NULL.

A Webhook example

We wanted to understand better what was happening, so I created an account in Jotform and set up a Webhook similar to his configuration. This Webhook essentially forwards the JotForm form data to a target endpoint (an ORDS POST handler in our case).

NOTE: This isn't a JotForm tutorial. But to get the gist, below you'll see how I set up one of my Webhooks.

Webhooks are weird

Here is what I don’t like about Webhooks (perhaps this is just my ignorance on full display):

  1. What if the request doesn’t work or fails? Are reattempts automatic, or is that user information lost forever?
  2. In this case, we have no real idea what is being sent to ORDS (at least not without some creative interrogation).
  3. Without spending too much time with Webhooks, would an APEX front end not be more straightforward (i.e., same ecosystem and it is a direct POST as opposed to a Webhook that forwards a POST request to an POST endpoint…say that five times fast! More on this later…).

Where our problem starts

Here, we have an online form in JotForm. On the front end, I’ve set up three fields (these are all customizable in their web application):

  1. Full Name, which consists of First Name and Last Name
  2. Contact number
  3. And a “Radio” button selection 1

The Workflow

A user enters their information and clicks Submit. However, after inspecting the Console and Network tabs (in your Developer tools), you’ll notice two things:

  1. No POST requests
  2. No available request body (What does JotForm send to us?)

The absence of information is unsurprising since I’m working with a third-party tool (it’s a business, man!). There are enumerable reasons why you’d obscure information. Regardless of the cause, that obfuscation complicates matters when you want to ingest the form data into your database. You can follow the debugging advice to view the PHP output sent internally to JotForm. But I don’t know PHP, and I wouldn’t even know how to map PHP to JSON or SQL datatypes, for that matter.

ORDS Implicit Parameters to the Rescue

Here’s where you have to get creative. To better understand what was in that POST request, I relied on ORDS’ Implicit Bind parameters to understand what data was sent to us by the Webhook. Figuring this out required some trial and error on my part. But you can start with the :content_type bind parameter to see what kind of POST it is and go from there. In my example, I kept it simple and relied on the :content_type parameter and whatever was included in the POST body (up to this point, I wasn’t sure what to expect). I named that second value incoming_payload (which maps to the “PAYLOAD” column in the JOTFORM_DATA table)

INFO: I used the :body_json bind parameter, for the sole fact that neither the :body nor the :body_text parameters seemed to work for me.

Resource Handler code

A look at the ORDS Resource Handler code: 

-- The :content_type implicit bind parameter would "map" to the CONTENT_TYPE column in the 
-- JOTFORM_DATA table, while incoming_payload would map to the PAYLOAD column. 

DECLARE
    incoming_payload CLOB;
BEGIN
    incoming_payload := :body_json;
    INSERT INTO JOTFORM_DATA VALUES ( :content_type,
                                      incoming_payload );

END;
REMINDER: Remember, this is the endpoint I use in the "Webhook" integration in JotForm (i.e. the first two images at the top of this page).

Table DDL

You’ll notice a table in the handler code called JOTFORM_DATA. Well, I ended up creating a table of two columns, as seen here:

  1. Content_type
  2. Payload 
CREATE TABLE JOTFORM_DATA 
    ( 
     CONTENT_TYPE VARCHAR2 (4000) , 
     PAYLOAD      VARCHAR2 (4000) 
    ) 
;
Why VARCHAR2(4000)?  Two reasons. First, its just that it seems like VARCHAR2 is catch-all/the most flexible data type for testing like this. Secondly, since the PAYLOAD might be JSON or possibly malformed JSON, if I decide, I can always use an "IS JSON" check constraint. But that can come later.2

Moving through the workflow

With the Resource Handler code in place and the table defined, we can test this Webhook and see what it sends across HTTP.

Next, a look at what my Autonomous database received:

Okay! So now we have something to work with. And we know at least two things:

  1. The POST request is a multipart/form-data Content-Type, and3
  2. The payload we received appears to be JSON

Next, we can copy the contents from the PAYLOAD column. And throw it into any number of online JSON Formatting tools, to see what we are working with:

Final thoughts

Well, I can already see why somebody might struggle with API integrations (in general, not just here). There is a lot to learn and A LOT of unknowns. Take this actual use case, for instance. Of course, you’d assume this payload to be multipart/form-data. You’d also expect the payload contents to be the JotForm form’s data. However, in this case, the form is pre-processed before it even reaches ORDS or the Autonomous database. So it is a good thing we did some investigation because there is way more “stuff” than we originally anticipated!

Next, we’ll have to parse through the payload within the payload. For that, we can also use the ORDS Implicit Bind parameters, Automatic Binding, and JSON_VALUE functions. But we’ll save that for the next post. Stay tuned!

Complete code

Click for code

You can find the most up-to-date code here. The code used in this post:

-- Table DDL

CREATE TABLE JOTFORM_DATA 
    ( 
     CONTENT_TYPE VARCHAR2 (4000) , 
     PAYLOAD      VARCHAR2 (4000) 
    ) 
;

-- ORDS Resource Module PL/SQL

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => '[Your username/schema]',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => '[same as p_schema in all lowercase]',
      p_auto_rest_auth      => FALSE);
    
  ORDS.DEFINE_MODULE(
      p_module_name    => 'ords_and_jotform',
      p_base_path      => '/oj/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'ords_and_jotform',
      p_pattern        => 'jotform_data',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'ords_and_jotform',
      p_pattern        => 'jotform_data',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page => 25,
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'DECLARE
    incoming_payload CLOB;
BEGIN
    incoming_payload := :body_json;
    INSERT INTO JOTFORM_DATA VALUES ( :content_type,
                                      incoming_payload );

END;');

        
COMMIT;

END;

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!

  1. What is a Radio button? ↩︎
  2. Directly from the JSON Developer’s Guide Release 23, “You can store JSON data in Oracle Database using columns whose data types are JSON, VARCHAR2, CLOB, or BLOB.” Although, the guide then continues to state that, “Oracle recommends that you use JSON data type, which stores JSON data in a native binary format.” This is an easy change to make early on though. Should I choose to, I can adjust future table columns to accommodate the JSON data type. ↩︎
  3. About the multipart/form-data Content-Type. This section discusses the multipart/form-data Content-Type, but also ExampleBoundaryString why it is used/included. You’ll see that in some of the screenshots throughout. ↩︎

Leave a Comment