Skip to content
Connect > Data APIs > Use Cases

Insert rows and call a procedure

Goal: the write path over REST: an external system (an OMS, an algo, an ETL job) inserts rows into a Center table and then calls a server-side procedure to act on them, reading back what the procedure returned.

Prerequisites

1
2
3
4
5
6
curl -sk -u username:password -G \
  --data-urlencode 'cmd=CREATE PUBLIC TABLE ORDERS (ORDER_ID String, SYM String, side String, qty Long, price Double, status String) USE Broadcast="true" RefreshPeriodMs="100"' \
  "https://localhost:33333/3forge_rest/query?display=jsonMaps"
curl -sk -u username:password -G \
  --data-urlencode 'cmd=CREATE INDEX ORDERS_PK ON ORDERS(ORDER_ID HASH) USE Constraint="PRIMARY"' \
  "https://localhost:33333/3forge_rest/query?display=jsonMaps"

1. Insert a row

Any statement the Center accepts can go in cmd, including INSERT:

1
2
3
curl -sk -u username:password -G \
  --data-urlencode "cmd=INSERT INTO ORDERS (ORDER_ID, SYM, side, qty, price, status) VALUES (\"O-1001\", \"IBM\", \"BUY\", 500, 123.45, \"OPEN\")" \
  "https://localhost:33333/3forge_rest/query?display=jsonMaps"

The response reports how many rows were written:

1
{"status": "ok", "durationNanos": 480000, "rowsAffected": 1, "returnValue": null}

A plain INSERT returns no keys. To get generated keys back under autogenKeys, write the statement as INSERT AND SELECT INTO.

2. Update or upsert

The same endpoint runs UPDATE. For example, mark the order filled:

1
2
3
curl -sk -u username:password -G \
  --data-urlencode "cmd=UPDATE ORDERS SET status=\"FILLED\" WHERE ORDER_ID==\"O-1001\"" \
  "https://localhost:33333/3forge_rest/query?display=jsonMaps"

rowsAffected counts changed rows, not matched rows

rowsAffected is the number of rows actually added or changed. An INSERT of a new key reports 1; an upsert that overwrites an existing key reports 0 (even though the row changed); an UPDATE reports the rows whose values changed, so a no-op update and one that matches nothing both report 0. Do not use rowsAffected == 1 as a success check for a job that may resend a row: the resend upserts and reports 0. Check status == "ok" for success, and read the data back if you need to confirm the write landed.

3. Call a procedure and read its return value

You need a procedure on your Center for this step

The step assumes a procedure such as recalc_pnl exists. Create one over the same endpoint, then call it:

1
curl -sk -u username:password -G       --data-urlencode 'cmd=CREATE PROCEDURE recalc_pnl OFTYPE AMISCRIPT USE arguments="string _sym" script="return 4210.75;"'       "https://localhost:33333/3forge_rest/query?display=jsonMaps"

arguments is a comma-separated type name list and script is the AmiScript body, both as quoted strings. Return a result set instead of a scalar by making the script a SELECT. The envelope below is verified against a live instance.

When the work belongs on the server (recompute P&L, run a reconciliation, validate and route an order), call a stored procedure. A scalar return comes back in returnValue:

1
2
3
curl -sk -u username:password -G \
  --data-urlencode "cmd=CALL recalc_pnl(\"IBM\")" \
  "https://localhost:33333/3forge_rest/query?display=jsonMaps"

1
2
3
4
5
6
7
{
  "status": "ok",
  "durationNanos": 1747500,
  "rowsAffected": 0,
  "returnValue": 4210.75,
  "returnType": "Object"
}

returnType is the literal string Object for every scalar return, whatever the value's type, so branch on the value rather than on returnType.

A procedure whose script runs a SELECT delivers the rows under tables instead, shaped by your display choice, with returnValue left null. The key is the source table's name, not the procedure's, so a CALL against a procedure selecting from ORDERS returns tables.ORDERS.

A procedure with no explicit return statement does not leave returnValue null either: it surfaces the value of the script's last evaluated statement. A script of int x = 1; returns 1; int x = 1; x = x + 1; returns 2. Give every procedure an explicit return if you don't want its internal working values leaking into the response.

4. Delete a row

DELETE works the same way as INSERT/UPDATE: send it as cmd and read rowsAffected:

1
2
3
curl -sk -u username:password -G \
  --data-urlencode 'cmd=DELETE FROM ORDERS WHERE ORDER_ID == "O-1001"' \
  "https://localhost:33333/3forge_rest/query?display=jsonMaps"

1
{"status": "ok", "durationNanos": 812400, "rowsAffected": 1, "returnValue": null}

rowsAffected is the number of rows actually removed, the same convention as UPDATE.

5. Handle errors

Errors arrive in 4 shapes, and a robust client checks for all of them (see REST API → Four kinds of error response):

  • Statement errors: HTTP 200, JSON, status:"error", with an error object. atLine/atChar are 0-based:
1
{"status": "error", "error": {"message": "Unknown variable: quantity", "atLine": 0, "atChar": 7}}
  • Request errors: HTTP 400, plain text or HTML with no status field: cmd required, unknown center: …, display must be either: …. Calling resp.json() on these throws.
  • Server errors: HTTP 500, plain text: REQUEST TIMEOUT ERROR or UNKNOWN ERROR FROM AMI REST PLUGIN, usually followed by a stack trace. Calling resp.json() on these also throws.
  • Unknown endpoint: HTTP 404, HTML: Endpoint not found: /query when the QUERY endpoint is not enabled, followed by links to the loaded endpoints. Calling resp.json() on this throws too.

So check the HTTP status first, then status, before trusting rowsAffected or returnValue.

Notes

  • This is the write path; the WebSocket is not. The streaming API is subscribe-only: all inserts, updates, and procedure calls go over REST.
  • Escaping. String literals in Center SQL use double quotes ("IBM"); let curl --data-urlencode handle URL-encoding so spaces and quotes survive.
  • Idempotency is yours to design. REST does not deduplicate; use a primary key so a resend upserts instead of duplicating. Confirm success with status == "ok", not rowsAffected: a resend that overwrites reports rowsAffected:0 (see the warning above).
  • Pair it with a live view. A common pattern is to write orders here and watch their status stream over the filtered blotter.