Versions Compared

Old Version 6

changes.mady.by.user Al Lusher (Unlicensed)

Saved on

compared with

New Version Current

changes.mady.by.user Jessica Tomkins

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.

...

Code Block
languagesql
titleMSSQL, MySQL, MariaDB Table Definitions
collapsetrue
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
languagesql
titleDB2 Table Definitions
collapsetrue
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
languagesql
titleData Insert
collapsetrue
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:

...

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

...

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:

  • Name: EmployeeNumberemployeeNumber
  • From: query
  • Data Type: integer
  • Check the Required checkbox
  • Example: 1088
  • Description: Employee Number to request data about

Adding a valid example and description makes testing the API easier and improves the autogenerated documentation. 

...

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.

Image Added

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

Image Added

Repeat this step to add the following child parameters

Image RemovedImage Added


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

Image Removed

Image Removed

Image Removed

In the Which columns section drop down the list and select the columns to include

Image Added

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.

Image Added

For the Parameter Value drop down the API input section and select input["employeeNumber"] 

Image Added

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.

Image Added

Retrieve data from the offices table

Click the Add Step... button

Image Added

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.


Image Added

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

Image Added

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

Image Added

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

Image Added

For What would you like to do? select Set API Output

Image Added

A dialog will open showing the list of output parameters that you set up in the Output Parameters tab

Image Added

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

Image Added

Repeat this for the remaining employee parameters

Image Added

Save  Image Added

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

Image Added

Scrolling down in the Test tab you will find the Response body section that will show the data that was returned 

Image Added


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

Image Added

The browser will display the data that was returned

Image Added