In this Part 2 post, we will discuss modifying the default generated CRUD (CREATE, READ, UPDATE, DELETE) procedure implementations from the RAD Server Package Wizard, to show one possible way to implement Get, GetItem, Post, PutItem and DeleteItem REST Endpoints.
In the Part 1 post, we discussed the default generated CRUD procedure implementations from the RAD Server Package Wizard.
In the Part 3 post, we will discuss implementing a FireMonkey (FMX) REST client to call and interact with the RAD Server CRUD procedures.
The Embarcadero RAD Server (also known as EMS – Enterprise Mobility Server) uses the industry standards of REST, and to transfer data to and from the RAD Server REST Service, JSON – JavaScript Object Notation, is typically used as the transfer encoding.
Due to the Embarcadero RAD Server supporting industry standard REST and JSON, we will use Delphi’s, C++ Builder and/or RAD Studio’s REST Client Library components (TRESTClient, TRESTRequest, and TRESTResponse ) on the client, and System.JSON classes (TJSONArray and TJSONObject) and FireDAC on the RAD Server to implement our CRUD procedures.
Also note, newer versions of RAD Server have introduced new frameworks and components that allows for greater control of the data retrieved by desktop, multi-device, web and other service-based applications that connect to your RAD Server application. For example the EMSDataSetResource Component is discussed here and other RAD Server Improvements introduced in RAD Studio 10.3.2, like the RAD Server Database Mapping Wizard, and Swagger API Documentation Improvements, are discussed here.
OK, now let’s dig into the details!
First, for our application we want to access and perform CRUD procedures on our Employee Table from our InterBase Employee Database. For the Employee Table, I’m using the sample InterBase Employee database, located at: localhost:C:UsersPublicDocumentsEmbarcaderoStudio21.0Samplesdataemployee.gdb
Recall from the part 1 post, using the RAD Server Package Wizard, , we created a package with resource. This created for us a Data Module.
A Data Module is like a special form that contains nonvisual components. We can now add FireDAC data access controls onto our Data Module to access our Employee table.
On our uResourceModule unit we can add our FireDAC controls to connect to the InterBase Employee database and Query the Employee Table:
The easiest way to do this is to use the IDE’s Data Explorer Tab and create a new FireDAC connection to the InterBase Employee database:
Then, select the Employee Table from the Tables node.
And then drag and drop the Employee Table onto the Data Module form:
This will add two FireDAC components on your data module. EmployeeConnection is the FireDAC connection (FDConnection) to your InterBase Employee database, and the EmployeeTable is your FireDAC Query component (FDQuery) to perform the SQL SELECT * FROM EMPLOYEE, to return all rows from the Employee table.
To test the FDQuery EmployeeTable, double-click the FDQuery EmployeeTable component. This will open the FireDAC Query Editor, and show us the SQL Command for this FDQuery EmployeeTable, like this. The SQL is SELECT * FROM EMPLOYEE
Click the Execute button, to verify you get all the rows return from the Employee table:
Get (READ) Procedure
Recall from the part 1 post, we saw the RAD Server Package Wizard, generate a default GET (READ) procedure implementation like this:
Here is how we modified the default GET procedure to use FireDAC and System.JSON Classes (TJSONArray and TJSONObject) on the RAD Server EMS Resource Module to implement our GET (READ) CRUD procedure:
[crayon-67429a8f8a4c6683203080/]
Basically, in the above code, we are doing a SQL query SELECT * FROM EMPLOYEE and copying the returned Record Set fields (columns) into a JSON Array that will get sent back to our REST Client.
Here are the details on how we implemented the above GET Procedure:
1. First, we open our FDQuery EmployeeTable (EmployeeTable.Active := true😉 that is connected to the Employee table.
2. Next, we check that the query is Active (if EmployeeTable.Active then begin).
3. Next, we check if more than zero (0) records get returned from the FDQuery EmployeeTable (if EmployeeTable.RecordCount > 0 then begin)
4. Next, we create a JSON Array (a := TJSONArray.Create;). Our JSON Array will contain the results that we send back to our REST Client.
5. Next, we position ourselves to the first record from our FDQuery EmployeeTable (EmployeeTable.First;).
6. Next, and while not end of file (while (not EmployeeTable.Eof) do begin), we will loop through all the records from the FDQuery EmployeeTable query.
7. And we will create a new JSON Object (o := TJSONObject.Create;) for each new record.
8. And then for each field in that record, we add to the JSON Object (
o.AddPair(‘EmployeeNumber’,TJSONNumber.Create (EmployeeTable.FieldByName(‘EMP_NO’).AsInteger)); with both it’s column name and value.
9. And we do the same to the other nine columns (FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE, DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, and SALARY) returned from the EmployeeTable query, with both its column name and value.
10. Then we add that JSON Object to the JSON Array that we created (a.AddElement(o)😉
11. And then we move to the next record in the query (EmployeeTable.Next;) and repeat the same steps 7 – 10, until we reach the queries End of File.
12. After we have looped through all of the records from the query, in our finally, we set the Response class, that is our Response parameter from the GET procedure (const AResponse: TEndpointResponse) to the Body being set to the SetValue of our JSONArray ( AResponse.Body.SetValue(a, TRUE); )
13. And we set the Boolean to TRUE, saying that the JSONArray is owned by the Response.
Next, let’s test if our new modified GET (READ) procedure functions correctly.
Recall, from the part 1 post, we created a Resource Name called ‘employees‘. In RAD Server the terminology of a Resource is a Collection. So for example, if we have a RAD Server resource defined as http://localhost:8080/employees this will call the GET procedure to return a Collection of Employee records from the Employees table. The table employee in the Employees database is a Collection of employee records. So in our RAD Server we exposed this as a Resource called ’employees’. And this is a Collection.
Testing Get (READ) Procedure
We can test this by:
1. Run the RADServerCRUD.BPL project.
2. This starts our RAD Development Server:
3. Click the ‘Open Browser’ button on the RAD Development Server.
4. In the Web Browser, enter the URL http://localhost:8080/employees
5. This calls our modified GET (READ) procedure, and returns the JSON Array of all the Employee table records, like this:
So far, all is working correctly!
GetItem procedure
Next, let’s look at our modified GetItem procedure.
Recall from the part 1 post, the GetItem procedure has the same prototype as the Get procedure, with the same parameters, except we get to pass a parameter on the URL, like: http://localhost:8080/employees/5
Meaning that instead of returning a JSON Array of all Employee table records, we only need to return a JSON Object WHERE EMP_NO = 5.
So with this information we can modify the default GetItem procedure with code like this:
[crayon-67429a8f8a4cd137615854/]
Here are the details on how we implemented the above GetItem Procedure:
1. This GetItem Procedure is basically doing the same implementation as the Get procedure.
2. But for the GETItem procedure, we do not need a JSON Array, because we are only asking for a single record, so we use a TJSONObject for our single record.
3. We create the one JSON object ( o := TJSONObject.Create; ).
4. And what we return from the SQL request is the same JSON object ( AResponse.Body.SetValue(o,TRUE); )
5. But for this GetItem procedure, because we are specifying one single item, we get the Item from the URL that we are passing in the Request, and that Item is a numeric (Integer) value for the EMP_NO column in our Employee Table. This Item gets passed in as a String, so we need to perform a StrToInt conversion. And we store that value as the EmployeeNumber: integer:
EmployeeNumber := StrToInt(ARequest.Params.Values[‘item’]);
5. Next, we modify the SQL that we will execute. So instead of the SQL Select * from EMPLOYEE that we do for the Get procedure, for the GetItem procedure, the SQL is SELECT * FROM EMPLOYEE WHERE EMP_NO = ‘Item’ :
EmployeeTable.SQL.Text := ‘SELECT * FROM EMPLOYEE WHERE EMP_NO = :EMP_NO’;
We added the WHERE clause to the SQL, and we parameterized the EMP_NO.
And then that parameter is being set to the EMP_NO:
EmployeeTable.Params.ParamByName(‘EMP_NO’).AsInteger := EmployeeNumber;
6. So, when we activate the query ( EmployeeTable.Active := true ), we should only get one record returned. If zero records are returned, then that EMP_NO does not exist in the Employee table, and the GetItem procedure returns without any data in the Body.
Testing GetItem procedure
Similar to how we tested the Get procedure, we can test our GetItem procedure the same way.
1. Run the RADServerCRUD.BPL project.
2. This starts our RAD Development Server.
3. Click the ‘Open Browser’ button on the RAD Development Server.
4. In the Web Browser, enter the URL http://localhost:8080/employees/5
5. This should return one JSON Object (not a JSON Array) of the EMP_NO = 5 record, like this:
We see our EMP_NO=5 record returned, encoded as JSON data!
Post (CREATE) Procedure
Next, let’s discuss our modified Post procedure.
From HTTP to CRUD, the Post is the SQL CREATE (INSERT) statement.
Recall, the the RAD Server Package Wizard, generated for us this default Post procedure:
Since we want our Post procedure to do a CREATE (INSERT) of a new record into our Employee table, we we can modify the default Post procedure with code like this:
[crayon-67429a8f8a4ce397627824/]
Here are the details on how we implemented the above Post Procedure:
1. With the Post Procedure, we can create (insert) a new Employee into the Employee table.
2. First, we get the Body of the Request:
var RequestObject: TJSONObject;
RequestObject := ARequest.Body.GetObject;
The body of the request is going to be a JSON Object, which represents the record that we want to create.
3. Next, we build a SQL statement to INSERT into employee, all of the fields that are needed to be inserted:
// Insert New Record
EmployeeTable.SQL.Text := ‘insert into employee ( ‘+
‘ FIRST_NAME, ‘+
And each field is parameterized:
‘ SALARY ) values( ‘ +
‘ :FIRST_NAME, ‘+
‘ :LAST_NAME, ‘+
4. Next, we set as parameters, each one of the fields, as values that we get from the Request Object:
// Set query parameters.
EmployeeTable.Params.ParamByName(‘FIRST_NAME’).AsString := RequestObject.GetValue(‘FirstName’).Value;
5. So, from the data from the Request Object, we can select the values we need, and publish them into our parameters:
// Set query parameters.
EmployeeTable.Params.ParamByName(‘FIRST_NAME’).AsString := RequestObject.GetValue(‘FirstName’).Value;
6. Next, we execute the SQL ( EmployeeTable.ExecSQL; )
7. And since this InterBase Employee table has a Trigger to automatically generate a new EMP_NO before an INSERT:
CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.emp_no = gen_id(emp_no_gen, 1);
END
;
8. In the Post procedure, we get a new EMP_NO record ID from the InterBase Generator:
// Get new record ID from Generator.
EmployeeTable.SQL.Text := ‘select GEN_ID(EMP_NO_GEN,0) as ID from RDB$DATABASE;’;
EmployeeTable.Active := True;
EmployeeTable.First;
NewRecordID := EmployeeTable.Fields.FieldByName(‘ID’).AsInteger;
EmployeeTable.Active := False;
This code will generate for us a unique EMP_NO id for the new record we are going to CREATE (INSERT).
So, we select the Generator from the InterBase database Employee table, to create a new unique EMP_NO for our new record.
9. Next, we build a new JSON Object ( o := TJSONObject.Create; ) containing that new EMP_NO id: o.AddPair(‘EmployeeNumber’,TJSONNumber.Create(NewRecordID));
10.And lastly, we send that JSON Object as the Response to send back to the client: AResponse.Body.SetValue(o,TRUE);
Testing Post Procedure
For our Post Procedure we will need to use the POST method. We can use the included free Embarcadero REST Debugger to test our POST, PUT and DELETE procedures.
1. Run the RADServerCRUD.BPL project.
2. This starts the RAD Development Server (REST Server).
3. Run the REST Debugger (from the IDE top menu, Tools | REST Debugger):
The REST Debugger is a free tool from Embarcadero for making ad hoc REST requests. With this tool, you can use different authentication methods to connect to services. You can send a request to the specified service and view the response.
Details on the REST Debugger are here.
4. To test our POST procedure, first on the Request tab of the REST Debugger, we set the Method to POST:
5. Next, we need to set a Content-Type to application/json for our Post procedure, because we will be sending a JSON body to our RAD Server:
6. On the Request tab of the REST Debugger, we enter http://localhost:8080 for the URL. This is the location and port of our running RAD Server:
7. The JSON body we can use to test our Post procedure can be this:
{
“FirstName”:”Al”,
“LastName”:”Mannarino”,
“PhoneExt”:”9225″,
“HireDate”:”02/01/2001″,
“DepartmentNumber”:130,
“JobCode”:”Eng”,
“JobGrade”:”2″,
“JobCountry”:”USA”,
“Salary”:25500
}
Notice the body does not include the primary EMP_NO field, since this will be auto-generated for us from the InterBase trigger discussed above.
8. Enter the above JSON body for the Custom Body in the REST Debugger:
9. Lastly, on the REST Debugger, on the Parameters tab, for the Resource, we enter our Resource Name employees from our EMS Resource Module on the RAD Server, that is employees:
10. Now, we can click the Send Request button the REST Debugger:
11. What you show see on the REST Debugger on the Response section, in the Body tab, is something like this:
12. We see from the Response that EmployeeNumber 146 gets returned. So this should have created a new Employee record for Al Mannarino, with Emp_No = 146.
13. To test that this new Employee record has been created and inserted into the Employee table, we can use the same REST Debugger application, and on the Request tab, set Method to GET, and on the Parameters tab, for the Resource, enter employees/146. This should call the GETItem procedure on the RAD Server and only return the record for this newly inserted EMP_NO = 146.
14. Click the Send Request button the REST Debugger, and you should see this for the body of the Response, showing that new EMP_NO = 146 has been created and inserted in your your Employee table! Wonderful!
PUT (Update) Procedure
Next, let’s look at modifying the default RAD Server Package Wizard generated PutItem procedure.
From HTTP to CRUD, the Put is the SQL Update statement.
Recall, the RAD Server Package Wizard, generated for us this default Put procedure:
Since we want our PutItem procedure to do an UPDATE of an existing record in our Employee table, we we can modify the default PutItem procedure with code like this:
[crayon-67429a8f8a4d2908534268/]
As you can see, this PutItem (Update) procedure is implemented similar to the Post (CREATE) and GetItem procedures. For the Post procedure we used SQL INSERT into EMPLOYEE. But for our PutItem (Update) procedure, we use SQL ‘UPDATE employee SET ‘.
Here are the details on how we implemented the above PutItem (UPDATE) Procedure:
1. With the PutItem Procedure, we can UPDATE an existing Employee from the Employee table.
2. Similar to the GetItem procedure, because we are specifying one single item, for the record we want to Update; Update employee WHERE EMP_NO = :EMP_NO, we get the Item from the URL that we are passing in the Request, and that Item is a numeric (Integer) value for the EMP_NO column in our Employee Table. This Item gets passed in as a String, so we need to perform a StrToInt conversion. And we store that value as the EmployeeNumber: integer:
EmployeeNumber := StrToInt(ARequest.Params.Values[‘item’]);
2. Next, we get the Body of the Request:
var RequestObject: TJSONObject;
RequestObject := ARequest.Body.GetObject;
The body of the request is going to be a JSON Object, which represents the record that we want to UPDATE.
3. Next, we build a SQL statement to ‘UPDATE employee SET‘, all of the fields that need to be Updated.
4. Next, we set as parameters, each one of the fields to be Updated, as values that we get from the Request Object:
EmployeeTable.Params.ParamByName(‘FIRST_NAME’).AsString := RequestObject.GetValue(‘FirstName’).Value;
5. So, from the data from the Request Object, we can select the values we need, and publish them into our parameters:
EmployeeTable.Params.ParamByName(‘LAST_NAME’).AsString := RequestObject.GetValue(‘LastName’).Value;
6. Next, we execute the SQL ( EmployeeTable.ExecSQL; )
7. Next, we build a new JSON Object ( o := TJSONObject.Create; ) containg the Employee EMP_NO record to Update: o.AddPair(‘EmployeeNumber’,TJSONNumber.Create(NewRecordID));
10.And lastly, we send that JSON Object as the Response to send back to the client: AResponse.Body.SetValue(o,TRUE);
Testing PutItem Procedure
For our PutItem Procedure we will need to use the PUT method. We can use the included free Embarcadero REST Debugger to test our PutItem procedure.
1. Run the RADServerCRUD.BPL project.
2. This starts the RAD Development Server (REST Server).
3. Run the REST Debugger (from the IDE top menu, Tools | REST Debugger).
4. On the REST Debugger Request tab, set the Method to PUT. Where PUT translates to CRUD Update.
5. The URL remains the same: http://localhost:8080 which is the IP address and port of our running RAD Server.
6. For the Custom Body, we can use this same JSON body we used for the POST procedure, but we will change PhoneExt”:”9225″ to “PhoneExt”:”9999″. This will be the field that gets updated.
{
“FirstName”:”Al”,
“LastName”:”Mannarino”,
“PhoneExt”:”9999″,
“HireDate”:”02/01/2001″,
“DepartmentNumber”:600,
“JobCode”:”VP”,
“JobGrade”:”2″,
“JobCountry”:”USA”,
“Salary”:105900
}
7. Enter the above JSON data into the Custom Body field on the REST Debugger:
8. For our Update, we can set all of the fields in the JSON body.
9. But for this Update, let’s only change “PhoneExt”:”9225″ to “PhoneExt”:”9999“.
10. Recall from the Post (INSERT) this record is EMP_NO= 146. So, on the REST Debugger, for Request, Parameters tab, for the Resource, enter: employees/146. This tells the PutItem procedure that we only want to Update the record WHERE EMP_NO = 146.
11. Also, set the Request Content-Type to application/json.
12. Click the Send Request button on the REST Debugger.
13. The Response Body in the REST Debugger should once again return:
{
“EmployeeNumber”: 146
}
which is a confirmation, telling us that this record has been Updated.
14. To verify the record got Updated, use the REST Debugger and execute a GET on employee/146, and you should see your EMP_NO= 146 returned with the “PhoneExt”:”9999″.
DELETE (Delete) Procedure
Lastly, let’s look at modifying the default RAD Server Package Wizard generated DeleteItem procedure.
From HTTP to CRUD, the Delete is the SQL Delete statement.
Recall, the RAD Server Package Wizard, generated for us this default DeleteItem procedure:
Since we want our DeleteItem procedure to do a SQL Delete of an existing record from our Employee table, we we can modify the default DeleteItem procedure with code like this:
[crayon-67429a8f8a4d4330776312/]
Here are the details on how we implemented the above DeleteItem Procedure:
1. The goal is to not Delete all the records in the Employee table. We only want to Delete a single item from the Employee table.
2. So, similar to how we implemented the GetItem procedure, we will pass the item id on the URL. For this DeleteItem procedure, because we are specifying one single item, we get the Item from the URL that we are passing in the Request, and that Item is a numeric (Integer) value for the EMP_NO column in our Employee Table. This Item gets passed in as a String, so we need to perform a StrToInt conversion. And we store that value as the EmployeeNumber: integer:
// Get employee number parameter
EmployeeNumber := StrToInt(ARequest.Params.Values[‘item’]);
3. Next, we modify the SQL that we will execute for the DeleteItem procedure:
// Delete Existing Record
EmployeeTable.SQL.Text := ‘DELETE FROM employee WHERE EMP_NO = :EMP_NO; ‘;
We added the WHERE clause to the SQL, and we parameterized the EMP_NO.
And then that parameter is being set to the EMP_NO:
// Set query parameters.
EmployeeTable.Params.ParamByName(‘EMP_NO’).AsInteger := EmployeeNumber;
So, EmployeeNumber is the EMP_NO record we want to delete.
4. Next, we execute the SQL:
// Execute SQL
EmployeeTable.ExecSQL;
5. Lastly, we build the JSON response.
For the DeleteItem procedure, we do not need a JSON Array, because we are only asking for a single record to delete, so we use a TJSONObject for our single record.
6. We create the one JSON object ( o := TJSONObject.Create; ).
7. Next, we build a new JSON Object ( o := TJSONObject.Create; ) containing the Employee EMP_NO record to Delete:
o.AddPair(‘EmployeeNumber’,TJSONNumber.Create(EmployeeNumber));
8. And what we return from the SQL Delete request is the same JSON object:
( AResponse.Body.SetValue(o,TRUE); )
And this will send back to the client, as a confirmation, the EMP_NO that got deleted.
Testing Delete (DELETE) Procedure
Similar to how we tested the Post and PutItem procedures, we will use the REST Debugger (Tools | REST Debugger).
On the REST Debugger,
1.Set the Request Method to DELETE.
2.Set the URL to http://localhost:8080
3. Set the Content-Type to application/json
4. On the Request Parameters tab, set the Resource to employees/146
This request says we want to execute the DeleteItem procedure and delete EMP_NO = 146 from the Employee table. This will be the ‘Al Mannarino’ record we inserted.
5. Click the Send Request button on the REST Debugger.
6. This returns the Body Response:
Confirming EMP_NO=146 has been deleted from the Employee table.
This post showed one way to implement the four CRUD procedues (CREATE, READ, UPDATE, and DELETE) as HTTP procedures; POST, GET, PUT and Delete on the RAD Server.
In the next RAD Server CRUD Procedures – Part 3 post we will show how to implement a FireMonkey (FMX) Client to call the CRUD procedures and interact with the Employee Table.
The project source code for this RAD Server CRUD project can be downloaded here: