...
This example will guide you through creating a simple REST API endpoint that retrieves and returns data from two database tables. For our example we will use an Employee and an Office table.
This example queries each table separately to demonstrate how easily you can add multiple steps. These same results can be achieved with a single step by joining the tables as shown in this example: Create API that joins two tables and returns results
In this example we will build the logic manually, however, you can also get started quicker by using the Autogenerating APIs from tables option for one of the tables first.
You can use the SQL code below to create and populate the tables used in the example or use your own tables and adjust the steps accordingly.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
CREATE TABLE employees ( EMPNUMBERempNumber INT NOT NULL , LASTNAMElastName VARCHAR(50) NOT NULL , FIRSTNAMEfirstName VARCHAR(50) NOT NULL , EXTENSIONextension VARCHAR(10) NOT NULL , EMAILemail VARCHAR(100) NOT NULL , OFFICECODEofficeCode VARCHAR(10) NOT NULL , REPORTSTOreportsTo INT DEFAULT NULL , JOBTITLEjobTitle VARCHAR(50) NOT NULL , PRIMARY KEY( EMPNUMBER empNumber) ) ; CREATE TABLE offices ( OFFICECODEofficeCode VARCHAR(10) NOT NULL , CITYcity VARCHAR(50) NOT NULL , PHONEphone VARCHAR(50) NOT NULL , ADDRESS1address1 VARCHAR(50) NOT NULL , ADDRESS2address2 VARCHAR(50) DEFAULT NULL , STATEstate VARCHAR(50) DEFAULT NULL , COUNTRYcountry VARCHAR(50) NOT NULL , POSTALCODEpostalCode VARCHAR(15) NOT NULL , TERRITORYterritory VARCHAR(10) NOT NULL , PRIMARY KEY( OFFICECODE officeCode) ) ; |
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
CREATE TABLE EMPLOYEES (
EMPNUMBER INTEGER NOT NULL ,
LASTNAME VARCHAR(50) CCSID 37 NOT NULL ,
FIRSTNAME VARCHAR(50) CCSID 37 NOT NULL ,
EXTENSION VARCHAR(10) CCSID 37 NOT NULL ,
EMAIL VARCHAR(100) CCSID 37 NOT NULL ,
OFFICECODE VARCHAR(10) CCSID 37 NOT NULL ,
REPORTSTO INTEGER DEFAULT NULL ,
JOBTITLE VARCHAR(50) CCSID 37 NOT NULL ,
CONSTRAINT Q_DEMOLIB_EMPLOYEES_EMPLO00001_00001 PRIMARY KEY( EMPNUMBER ) )
RCDFMT EMPLOYEES ;
CREATE TABLE OFFICES (
OFFICECODE VARCHAR(10) CCSID 37 NOT NULL ,
CITY VARCHAR(50) CCSID 37 NOT NULL ,
PHONE VARCHAR(50) CCSID 37 NOT NULL ,
ADDRESS1 VARCHAR(50) CCSID 37 NOT NULL ,
ADDRESS2 VARCHAR(50) CCSID 37 DEFAULT NULL ,
STATE VARCHAR(50) CCSID 37 DEFAULT NULL ,
COUNTRY VARCHAR(50) CCSID 37 NOT NULL ,
POSTALCODE VARCHAR(15) CCSID 37 NOT NULL ,
TERRITORY VARCHAR(10) CCSID 37 NOT NULL ,
CONSTRAINT Q_DEMOLIB_OFFICES_OFFICECODE_00001 PRIMARY KEY( OFFICECODE ) )
RCDFMT OFFICES ;
|
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
INSERT INTO `pjsdemo`.`employees` (`EMPNUMBER``empNumber`, `LASTNAME``lastName`, `FIRSTNAME``firstName`, `EXTENSION``extension`, `EMAIL``email`, `OFFICECODE``officeCode`, `REPORTSTO``reportsTo`, `JOBTITLE``jobTitle`) VALUES (1056,'Patterson','Mary','x4611','mpatterso@classicmodelcars.com',1,1002,'VP Sales'), (1076,'Firrelli','Jeff','x9273','jfirrelli@classicmodelcars.com',1,1002,'VP Marketing'), (1088,'Patterson','William','x4871','wpatterson@classicmodelcars.com',6,1056,'Sales Manager (APAC)'), (1102,'Bondur','Gerard','x5408','gbondur@classicmodelcars.com',4,1056,'Sale Manager (EMEA)'), (1143,'Bow','Anthony','x5428','abow@classicmodelcars.com',1,1056,'Sales Manager (NA)'), (1165,'Jennings','Leslie','x3291','ljennings@classicmodelcars.com',1,1143,'Sales Rep'), (1166,'Thompson','Leslie','x4065','lthompson@classicmodelcars.com',1,1143,'Sales Rep'), (1188,'Firrelli','Julie','x2173','jfirrelli@classicmodelcars.com',2,1143,'Sales Rep'), (1216,'Patterson','Steve','x4334','spatterson@classicmodelcars.com',2,1143,'Sales Rep'), (1286,'Tseng','Foon Yue','x2248','ftseng@classicmodelcars.com',3,1143,'Sales Rep'), (1323,'Vanauf','George','x4102','gvanauf@classicmodelcars.com',3,1143,'Sales Rep'), (1337,'Bondur','Loui','x6493','lbondur@classicmodelcars.com',4,1102,'Sales Rep'), (1370,'Hernandez','Gerard','x2028','ghernande@classicmodelcars.com',4,1102,'Sales Rep'), (1401,'Castillo','Pamela','x2759','pcastillo@classicmodelcars.com',4,1102,'Sales Rep'), (1501,'Bott','Larry','x2311','lbott@classicmodelcars.com',7,1102,'Sales Rep'), (1504,'Jones','Barry','x102','bjones@classicmodelcars.com',7,1102,'Sales Rep'), (1611,'Fixter','Andy','x101','afixter@classicmodelcars.com',6,1088,'Sales Rep'); INSERT INTO `pjsdemo`.`offices` (`OFFICECODE``empNumber`, `CITY``city`, `PHONE``phone`, `ADDRESS1``address1`, `ADDRESS2``address2`, `STATE``state`, `COUNTRY``country`, `POSTALCODE``postalCode`, `TERRITORY``territory`) VALUES (1,'San Francisco','+1 650 219 4782','100 Market Street','Suite 300','CA','USA','94080','NA'), (2,'Boston','+1 215 837 0825','1550 Court Place','Suite 102','MA','USA','02107','NA'), (3,'New York City','+1 212 555 3000','523 East 53rd Street','apt. 5A','NY','USA','10022','NA'), (4,'Paris','+33 14 723 4404','43 Rue Jouffroy Dabbans','','','France','75017','EMEA'), (5,'Tokyo','+81 33 224 5000','4-1 Kioicho','','Chiyoda-Ku','Japan','102-8578','Japan'), (6,'Sydney','+61 2 9264 2451','5-11 Wentworth Avenue','Floor #2','','Australia','NSW 2010','APAC'), (7,'London','+44 20 7877 2041','25 Old Broad Blvd','Level 7','','UK','EC2N 1HN','EMEA'), (8,'Hiddenite','+1 555 555 1212','221 Craftmaster Rd','-','NC','USA','28636','NA'); |
Open the workspace
(If you haven't created an example workspace, create a workspace called papihello. See Using Profound API Workspaces)
Open the example workspace by pointing your browser to the URL:
...
Create a new API json file by clicking the New button and select selecting API file. The file will be opened and the Canvas, Parameters, API Routes, and Properties panel will be initialized.
Set Properties
On the left hand lower panel select the General Info tab and fill on the following values
- Name: employee
- Summary: get employee information
HTTP Method: get
HTTP Path: /employee/:employeeNumber
Info Notice the colon before the employeeNumber. This indicates that employeeNumber is a required path parameter.
Specifying a parameter this way will automatically add it to the Input Parameters list.
- Category, Sub Category, Tag, and Description all help improve documentation and organization.
Input Parameters
It is always best to create parameters before building logic because the parameters will then appear as drop down options when adding logic steps.
This example is a basic GET API that requires an Employee Number as a parameter and will return some information about the employee and the office they are associated with.
You will notice that adding the :employeeNumber to the end of the HTTP path automatically added an employeeNumber parameter to the Input Parameters. These autogenerated path parameters can only be removed by changing the path. The default Type is string so we need to change it integer.
Double click on employeeNumber in the Input Parameters tab to open the Edit Parameter dialog
| Info |
|---|
If you wish to use a query parameter instead of a path parameter or want to add additional parameters click the green plus sign in the Input Parameter panel to open the API Parameter dialog.
Fill in the following properties:
Adding a valid example and description makes testing the API easier and improves the autogenerated documentation.
|
Output Parameters
Switch to the Output Parameters tab. Our API output will have a result JSON object that contains the values from the employee and office tables. The result object will allow us to query the database results from one of the tables directly into the API output.
Click the green plus sign to open the Edit Parameter dialog and complete the properties as shown.
After the result object parameter is added you can add child parameters for the data. Do this by right clicking on the result parameter in the list and selecting Add Child Parameter
Repeat this step to add the following child parameters
Save
At this point you should save your API file by clicking the Save button in the menu ribbon. The first time you use save you will be presented a save dialog with the File Name defaulting to Unnamed.api.json. You should change the Unnamed portion to an appropriate file name, like Employee.api.json in this case. Click Save.
Add Low Code Logic
Retrieve data from employee table
Click on Add Step... in the canvas.
In the What would you like to do? drop down expand the Database section and click on Retrieve One Database Record
In the Which columns section drop down the list and select the columns to include
In the Selection Criteria expression drop down the list and check the empNumber field. (You can use this to select multiple criteria fields and use the AND/OR selector to build the appropriate logic.
You can also type your criteria directly into the text area. Add a ? for each parameter and a Parameter Value selection box will be added below for each ? in the statement.
For the Parameter Value drop down the API input section and select input["employeeNumber"]
We will be using a value from this query for the next query so we will place the record into a work variable.
Select Work variable from the list and enter a variable name.
Then click OK.
Retrieve data from the offices table
Click the Add Step... button
Select the Retrieve One Database Record and then set the database, table, and columns
| Info |
|---|
In this example we are using two tables in the same database. This could be done just as easily if the two tables were in completely different database. Also, because the tables are in the same database, the data from both tables could be retrieved with a single step using a join statement. We've used two steps for sake of the example. |
For the parameter value for "officeCode = ?", this time you will drop down the Work variables section. There will be a variable for each column you selected in the previous step.
Select the employeeResult["officeCode"] variable
This time for Where do you want to place the record? you can place the record directly into the API output result object. The columns names will automatically be mapped to the matching child parameters.
From the drop down select API output
Then from the Enter API output property name select output["result"]
Completing the API Output parameters
Selecting the results from the offices query directly into the API output will populate the matching child parameters. However, we still need to get the employee tables results from the work variable into the API output.
There is a widget specifically from that purpose.
Click the Add Step... button
For What would you like to do? select Set API Output
A dialog will open showing the list of output parameters that you set up in the Output Parameters tab
Although you can not see it in the dialog, the office parameters are already set.
For the employee parameters you will need to set them from the work variable
Click the blue arrow next to result["empNumber"] to drop down the list of options. Expand the Work variables section and select employeeResult["empNumber"]
Repeat this for the remaining employee parameters
Save 
Testing the API
When you save the API it will be live and you can test it using the Test tab in the lower right panel.
Because we added an example value when creating the parameters, the example value is prefilled in the test tab. You can edit the employeeNumber value to test other results.
Click the Execute button
Scrolling down in the Test tab you will find the Response body section that will show the data that was returned
Live Test
Because this example is a simple GET API, you can test that it is live by copying the Request URL and pasting it in another browser window
The browser will display the data that was returned
