{"id":802,"date":"2020-10-10T23:00:18","date_gmt":"2020-10-11T03:00:18","guid":{"rendered":"http:\/\/salzlechner.com\/dev\/?p=802"},"modified":"2020-10-11T06:26:37","modified_gmt":"2020-10-11T10:26:37","slug":"starzen-rest-library-for-dataflex-webapp-adding-a-database","status":"publish","type":"post","link":"http:\/\/salzlechner.com\/dev\/2020\/10\/10\/starzen-rest-library-for-dataflex-webapp-adding-a-database\/","title":{"rendered":"StarZen REST library for DataFlex WebApp \u2013 Adding a database"},"content":{"rendered":"

[et_pb_section fb_built=”1″ _builder_version=”3.22″][et_pb_row _builder_version=”3.26.7″ background_size=”initial” background_position=”top_left” background_repeat=”repeat”][et_pb_column type=”4_4″ _builder_version=”3.25″ custom_padding=”|||” custom_padding__hover=”|||”][et_pb_text _builder_version=”3.26.7″ z_index_tablet=”500″ text_text_shadow_horizontal_length_tablet=”0px” text_text_shadow_vertical_length_tablet=”0px” text_text_shadow_blur_strength_tablet=”1px” link_text_shadow_horizontal_length_tablet=”0px” link_text_shadow_vertical_length_tablet=”0px” link_text_shadow_blur_strength_tablet=”1px” ul_text_shadow_horizontal_length_tablet=”0px” ul_text_shadow_vertical_length_tablet=”0px” ul_text_shadow_blur_strength_tablet=”1px” ol_text_shadow_horizontal_length_tablet=”0px” ol_text_shadow_vertical_length_tablet=”0px” ol_text_shadow_blur_strength_tablet=”1px” quote_text_shadow_horizontal_length_tablet=”0px” quote_text_shadow_vertical_length_tablet=”0px” quote_text_shadow_blur_strength_tablet=”1px” header_text_shadow_horizontal_length_tablet=”0px” header_text_shadow_vertical_length_tablet=”0px” header_text_shadow_blur_strength_tablet=”1px” header_2_text_shadow_horizontal_length_tablet=”0px” header_2_text_shadow_vertical_length_tablet=”0px” header_2_text_shadow_blur_strength_tablet=”1px” header_3_text_shadow_horizontal_length_tablet=”0px” header_3_text_shadow_vertical_length_tablet=”0px” header_3_text_shadow_blur_strength_tablet=”1px” header_4_text_shadow_horizontal_length_tablet=”0px” header_4_text_shadow_vertical_length_tablet=”0px” header_4_text_shadow_blur_strength_tablet=”1px” header_5_text_shadow_horizontal_length_tablet=”0px” header_5_text_shadow_vertical_length_tablet=”0px” header_5_text_shadow_blur_strength_tablet=”1px” header_6_text_shadow_horizontal_length_tablet=”0px” header_6_text_shadow_vertical_length_tablet=”0px” header_6_text_shadow_blur_strength_tablet=”1px” box_shadow_horizontal_tablet=”0px” box_shadow_vertical_tablet=”0px” box_shadow_blur_tablet=”40px” box_shadow_spread_tablet=”0px”]Most of our APIs will deal with database content of course so lets take a look at how we can build these types of APIs.<\/p>\n

As a sample database we chose to use DAWs order entry database just because we all have and know it already.<\/p>\n

To start with an easy one we will create an API for the inventory table<\/p>\n

We will add another HTTP Handler based on the cszWebAPIController class named oInventoryAPI<\/p>\n

Object oInventoryAPI is a cszWebAPIController\n  Set psResourceName to \"Inventory Item\"\n  Set Key_Field to Field inventory.Item_ID\n  Set psPath to \"api\/v1\/inventory\"\n  Set psVerbs to \"*\"\n   ... \nEnd_Object<\/pre>\n
we also need to add our DataDictionaries<\/p>\n
Object oVendor_DD is a cVendorDataDictionary \nEnd_Object\n\nObject oInventory_DD is a cInventoryDataDictionary\n\u00a0 \u00a0 Set DDO_Server to oVendor_DD\nEnd_Object\n\nSet Main_DD to oInventory_DD<\/pre>\n
then we define the data model as follows<\/p>\n
Procedure OnDefineModels\n  Send BeginModel \"inventory\"\n  Send AddTableColumn File_Field Inventory.Item_ID\n  Send AddTableColumn File_Field Inventory.Description\n  Send AddTableColumn File_Field Inventory.Vendor_ID \n  Send AddTableColumn File_Field Inventory.Vendor_Part_ID\n  Send AddTableColumn File_Field Inventory.Unit_Price\n  Send AddTableColumn File_Field Inventory.On_Hand\n  Send EndModel\nEnd_Procedure<\/pre>\n
next we want to define a route to get a list of inventory items<\/p>\n
Send RegisterPath \"GET\" \"\" (RefProc(webAPI_Get))<\/pre>\n
this is all that is needed to create a REST API to return a list of inventory items. And again as before the OpenAPI documentation is created automatically as well<\/p>\n
now lets see what it takes to add another route to return a single inventory item by id<\/p>\n
we need to define a new route as follows<\/p>\n
Send RegisterPath \"GET\" \"\/{itemid}\" (RefProc(webAPI_GetScalar))<\/pre>\n
this new route has a variable parameter called itemid. this item id is passed as a variable to the webAPI_GetScalar method which handles finding the record and returning the json object based on the schema provided<\/p>\n
Now what about deleting an inventory item<\/p>\n
lets add the following route<\/p>\n
Send RegisterPath \"DELETE\" \"\/{itemid}\" (RefProc(webAPI_Delete))<\/pre>\n
this route again uses a variable parameter for the item id and passes it to the webAPI_Delete<\/em> method which handles deleting the inventory item.<\/p>\n
Now what about creating new inventory records. For this to work we need a POST route as follows<\/p>\n
Send RegisterPath \"POST\" \"\" (RefProc(webAPI_Post))<\/pre>\n
this route will use the schema provided as an input from the post method and create an inventory record by calling the webAPI_Post<\/em> method.<\/p>\n
Then of course we would like to update an inventory record as well. We can do this by declaring a PATCH route as follows<\/p>\n
Send RegisterPath \"PATCH\" \"\/{itemid}\" (RefProc(webAPI_Patch))<\/pre>\n
And this is all that is needed to update inventory records.<\/p>\n
And again the documentation for the API is automatically created usign the OpenAPI specifications<\/p>\n
the schema we used so far in this example is a pretty simple flat schema. What if we wanted a more complex schema adding a child object for the vendor definition and maybe even a child object for the vendors address even though it only exists in a flat structure in the table.<\/p>\n
Also we want some of the fields to only appear in the scalar functions and not in the return of the list methods<\/p>\n
lets look at the following schema declaration<\/p>\n
Procedure OnDefineModelColumnsVendorAddress \n  Send AddTableColumn File_Field Vendor.Address \n  Send AddTableColumn File_Field Vendor.City \nEnd_Procedure \n\nProcedure OnDefineModelColumnsVendor \n  Send AddTableColumn File_Field Vendor.ID \n  Send AddTableColumn File_Field Vendor.Name \n  Send AddModelParent \"Address\" (RefProc(OnDefineModelColumnsVendorAddress)) \nEnd_Procedure\n\nProcedure OnDefineModels \n  Send BeginModel \"inventory\" \n  Send AddTableColumn File_Field Inventory.Item_ID \n  Send AddTableColumn File_Field Inventory.Description  \n  Send AddModelParent \"Vendor\" (RefProc(OnDefineModelColumnsVendor)) True \n  Send AddTableColumn File_Field Inventory.Vendor_ID \n  Send AddTableColumn File_Field Vendor.Name \n  Send AddTableColumn File_Field Inventory.Vendor_Part_ID \n  Send AddTableColumn File_Field Inventory.Unit_Price \n  Send AddTableColumn File_Field Inventory.On_Hand \n  Send EndModel \nEnd_Procedure<\/pre>\n
we start by defining a schema called inventory with the fields item_id and description<\/p>\n
then we add a parent schema called vendor with the vendor fields but we define this parent as scalar only so it will not appear in the return data when getting a list of inventory items.<\/p>\n
and here is a quick screen grab of the documentation for the API<\/p>\n
\"\"<\/p>\n
This is just an example there are a number of features to describe the schemas and you can also define multiple schemas to be used for different routes.<\/p>\n
In the next part to this series we will look at the order API which will show additional features of the library[\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”3.26.7″][et_pb_column type=”4_4″ _builder_version=”3.26.7″][et_pb_text _builder_version=”3.26.7″]\n\t\t
\n\t\t\t
\n\t\t\t\t
\n\t\t\t\n\t\t\t
<\/div>\n\t\t<\/div> \n\t\t
\n\t\t\tMichael Salzlechner is the CEO of StarZen Technologies, Inc.<\/p>\n
He was part of the Windows Team at Data Access Worldwide that created the DataFlex for Windows Product before joining\u00a0StarZen Technologies. StarZen Technologies provides consulting services as well as custom Application development and third party products<\/p>\n\t\t<\/div>\n\t\t\t<\/div>\n\t\t<\/div>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
Most of our APIs will deal with database content of course so lets take a look at how we can build these types of APIs. As a sample database we chose to use DAWs order entry database just because we all have and know it already. To start with an easy one we will create […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","ngg_post_thumbnail":0,"footnotes":""},"categories":[6,27,1],"tags":[],"class_list":["post-802","post","type-post","status-publish","format-standard","hentry","category-dataflex","category-dataflex-webapp","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/posts\/802","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/comments?post=802"}],"version-history":[{"count":8,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/posts\/802\/revisions"}],"predecessor-version":[{"id":829,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/posts\/802\/revisions\/829"}],"wp:attachment":[{"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/media?parent=802"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/categories?post=802"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/salzlechner.com\/dev\/wp-json\/wp\/v2\/tags?post=802"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}