Saving Data Using REST API

The SaveToDB add-in supports saving data via OData and DBGate REST API from the box.

Users can configure saving changes for other REST API services using the connection wizard themselves.

Developers can configure REST API in a database to allow users to connect and save data without additional configuration steps.

Users must connect to a database in this case instead of the direct connection to a data service.

Developers can use the xls.objects table or Query List views.

Here is a format for the xls.objects table:

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_OBJECT	UPDATE_OBJECT	DELETE_OBJECT
	<schema>	<name>	HTTP	<select command>	<insert command>	<update command>	<delete command>

Here is a format for the Query List view:

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_PROCEDURE	UPDATE_PROCEDURE	DELETE_PROCEDURE	PROCEDURE_TYPE
	<schema>	<name>	HTTP	<select command>	<insert command>	<update command>	<delete command>

Here is a sample for ZohoCRM:

TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_OBJECT	UPDATE_OBJECT	DELETE_OBJECT
ZohoCRM	Accounts	HTTP	https://www.zohoapis.com/crm/v2/Accounts	POST {$url} {"data":[{@json_row_values}]}	PUT {$url}/{id} {"data":[{@json_row_values}]}	DELETE {$url}/{id}
ZohoCRM	Contacts	HTTP	https://www.zohoapis.com/crm/v2/Contacts	POST {$url} {"data":[{@json_row_values}]}	PUT {$url}/{id} {"data":[{@json_row_values}]}	DELETE {$url}/{id}
ZohoCRM	Leads	HTTP	https://www.zohoapis.com/crm/v2/Leads	POST {$url} {"data":[{@json_row_values}]}	PUT {$url}/{id} {"data":[{@json_row_values}]}	DELETE {$url}/{id}

The sample contains three configured HTTP objects in the ZohoCRM schema: Accounts, Contacts, and Leads.

The TABLE_CODE field contains URLs used to load data. See more details at Getting Data from REST API.

Other fields contain formats for insert, update, and delete HTTP commands.

The formal grammar of HTTP commands:

HTTP-command = method SP absolute-URI [ SP "HTTP/1.1"] *( NEWLINE header-name ":" header-value ) [ 2*NEWLINE message-body ]

method = "POST" | "PUT" | "PATCH" | "MERGE" | "DELETE"

NEWLINE = CR | LF | CRLF

The URL can contain parameters in the curly brackets like {id} in the sample.

The add-in populates such parameters from table columns and selects URL parameters.

Also, you can use a built-in {url} parameter populated with the select URL (without parameters).

The body can contain parameters prefixed with the @ character.

You can specify parameters for all properties manually.

Also, you can use built-in parameters @json_row_values and @form_row_values that include all row values.

The add-in supports application/json and application/x-www-form-urlencoded body formats. It detects it automatically using the body definition.