Version 25.3.9411

• Marking Up Table Columns
• Getting Data
• Posting Data
• Putting Data
• Deleting Data
• Schema Example
• Keyword Reference

When you define APIs on the API Server API page, your tables and stored procedures are held in text-based schemas. Schemas are written in APIScript, a XML-based scripting configuration language that allows you to define table columns and stored procedure operations. It includes built-in operations that enable you to read and write to databases, files, and backend services.

In addition to these data processing capabilities, APIScript is a full-featured language with constructs for conditionals, looping, and so on. However, as you can see in the schema example, you don’t need to use these features for most table definitions.

The following sections describe a fully functional schema that enables bidirectional access to tables in an SQLite database. Use the tabs on the API page to generate similar schemas for your database tables and stored procedures. You can make some modifications to an existing schema by clicking its link, making the necessary changes, and clicking Save Changes. However, many changes must be made by editing the schema files directly. Schemas for Tables are written to .rsd files; schemas for Stored Procedures are written to .rsb files. Find these files in the api subfolder of the application root. Place your custom schemas in the same folder.

These sections also detail all of the components you need to write your own schemas to read and write to other data sources.

Marking Up Table Columns

When API Server connects to databases, the columns of a table have these basic attributes:

• Column name
• Primary key indicator
• Data type

Inside the api:info schema block, you can mark up the table columns with these attributes (and others).

<api:info title="NorthwindOData" desc="Access the Cars database through REST APIs." connection="SQLiteCars">
  <attr name="ID"           key="true" xs:type="int"      />
  <attr name="Make"         xs:type="string"   />
  <attr name="Model"        xs:type="string"   />
  <attr name="Cost"         xs:type="double"   />
  <attr name="CreatedDate"  xs:type="datetime" />
  <attr name="InStock"      xs:type="boolean"  />
</api:info>

Getting Data

When an HTTP GET request is received, API Server executes the schema GET method. In this method you can call API Server’s built-in operations to process data retrieval requests. Here is an example search request using HTTP GET:

GET api.rsc/Cars?$filter=Make eq 'Honda'

This request maps to the following SQL query:

SELECT * FROM Cars WHERE Make = 'Honda'

In the corresponding GET method, the results from the database query are pushed to API Server’s HTTP response with the api:push keyword. You can use an apiSelect operation to execute search, sort, summary, and other data retrieval queries to your database.

<api:script method="GET" >
  <api:push op="apiSelect"/>
</api:script>

Posting Data

When a POST request is received, API Server executes the schema POST method, where you can call data manipulation operations such as insert.

For example, consider this HTTP POST request:

POST api.rsc/Cars
{
  "Model": "Civic",
  "Make": "Honda"
}

This request maps to the following SQL query:

INSERT INTO (Model, Make) VALUES ('Civic', 'Honda')

In the corresponding POST method, you can use apiInsert to execute inserts to databases.

<api:script method="POST">
  <api:push op="apiInsert"/>
</api:script>

Some databases return data from an INSERT statement, such as the generated Id of the new record. To access values returned from an insert, use the api:push keyword in the same way as GET.

Putting Data

When a PUT request is received, API Server executes the schema PUT method, where you can call data manipulation operations such as update.

For example, consider this PUT request:

PUT http://localhost:8065/api.rsc/Cars('1000')
{
  "Model": "Civic"
}

This request maps to the following SQL statement:

UPDATE Cars SET Model = 'Civic' WHERE Id = '1000'

In the corresponding PUT method, the required input—the primary key—is validated before the operation is called. You can check that an input was provided (and alert the user if not) with the api:validate keyword.

Note: You can specify multiple HTTP methods to be handled by the script.

Since updates do not typically return data, the api:call keyword is used to invoke the operation. You can call the apiUpdate operation to execute updates to your database.

<api:script method="PUT,MERGE,PATCH">
  <api:validate attr="Id" desc="An Id is required to update." />
  <api:call op="apiUpdate"/>
</api:script>

Deleting Data

When a DELETE request is received, API Server executes the schema DELETE method, where you can call delete operations. For example, consider this HTTP DELETE request:

DELETE api.rsc/Cars('1000')

This request corresponds to the SQL query below:

DELETE FROM Cars WHERE Id = '1000'

In the DELETE method, you can call the apiDelete operation to execute deletes to your database.

<api:script method="DELETE">
  <api:validate attr="Id" desc="An Id is required to delete." />
  <api:call op="apiDelete"/>
</api:script>

Schema Example

The following schema enables read and write access to a Cars table in an SQLite database. It contains all the components you need to access a database through HTTP.

<api:script xmlns:api="http://www.rssbus.com/ns/rsbscript/2">
 
  <!-- Define columns and the database connection in the api:info block -->
  <api:info title="case" description="Create, Update, Query, and Delete Cars." connection="SQLiteCars">
    <attr name="Id"             key="true" xs:type="string"   />
    <attr name="Year"                      xs:type="int"      />
    <attr name="Make"                      xs:type="string"   />
    <attr name="Model"                     xs:type="string"   />
    <attr name="DatePurchased"             xs:type="datetime" />
  </api:info>
 
  <!-- The GET method is executed when an HTTP GET is received. You can configure data retrieval operations here. The results of processing are pushed to the schema's output. -->
  <api:script method="GET">
    <api:push op="apiSelect"/>
  </api:script>
 
  <!-- The POST method is executed when an HTTP POST is received. You can configure insert operations here. Use api:push to return the Id of the new record. -->
  <api:script method="POST">
  <api:validate attr="Make" desc="Make and Model are required to insert a Car."/>
  <api:validate attr="Model" desc="Make and Model are required to insert a Car."/>
    <api:push op="apiInsert"/>
  </api:script>
 
  <!-- The PUT method is executed when an HTTP PUT is received. You can configure update operations here. Within the script block, the primary key is used to update the record. Updates typically do not return data, so api:call is used to invoke the operation. -->
  <api:script method="PUT,MERGE,PATCH">
    <api:validate attr="Id" desc="Id is required to update."/>
    <api:call op="apiUpdate"/>
  </api:script>
 
  <!-- The DELETE method is executed when an HTTP DELETE is received. You can configure delete operations here. Within the script block, the primary key is used to delete the record. -->
  <api:script method="DELETE">
    <api:validate attr="Id" desc="Id is required to delete."/>
    <api:call op="apiDelete"/>
  </api:script>
 
</api:script>

Keyword Reference

See APIScript for more information on the keywords used in this topic:

• api:info
• api:set
• api:script
• api:validate
• api:call
• api:push