Skip to content
Center

Procedures

Procedures are custom pieces of AMIScript for consistently manipulating tables. They are a useful way to reuse code and make projects easier to maintain.

CREATE PROCEDURE

This command allows you to create stored procedures that can be called via amiscript to execute a sequence of statements and return a value. Arguments can be supplied when calling the stored procedure (see CALL PROCEDURE):

1
2
3
4
5
CREATE PROCEDURE [IF NOT EXISTS] procedure_name OFTYPE AMISCRIPT USE
arguments="type name, type name ... "
script="AmiScript to run when proc is called"
[logging="set the logging level when the procedure gets called"]
[onStartupScript="AmiScript to run When the procedure is created"]
  • procedure_name - name of the procedure to be created, each procedure's name must be unique within the database

Logging options:

  1. off (default): no logging.
  2. on: logs the time when the timer is called and when it completes.
  3. verbose: equivalent of using show_plan=ON in AMIDB. Logs the time that a timer starts and finishes and also each query step.

Example

In this example lets assume we have a table MyTable(id int,price double). We will make a simple procedure that inserts a row into that table and deletes any other rows with an equal or lesser price. (See CALL PROCEDURE clause for example of calling this procedure)

1
2
3
4
CREATE PROCEDURE MyProcedure OFTYPE AMISCRIPT USE
arguments="int _id,double _price"
script="DELETE FROM MyTable WHERE price<_price;
INSERT INTO MyTable(id,price) VALUES(_id,_price)"

CALL

This command allows you to execute stored procedures, typically declared using the CREATE PROCEDURE clause. When calling procedures you must supply the appropriate arguments expected for the declared procedure.

CALL proc_name ([arg1 [,arg2 ...]])

Example

In this example we will call the procedure declared in the CREATE PROCEDURE clause.

CALL MyProcedure(17,45.2);

DROP PROCEDURE

DROP PROCEDURE [IF EXISTS] proc_name[,proc_name...]

Example

This example will drop the procedure MyProcedure from the database

DROP PROCEDURE MyProcedure

String Templating

String templating can be complex with procedures, this section will go through an example of how to use string templating. First let us create a simple table and add 4 values to it:

1
2
3
CREATE PUBLIC TABLE A (ID String, Price Double);

INSERT INTO A VALUES ("I001",100),("I002",200),("I003",300),("I004",400);

Running this select query:

SELECT * FROM A;

Gives us the following table:

1
2
3
4
5
6
| ID String | Price Double |
|-----------|--------------|
| I001      | 100          |
| I002      | 200          |
| I003      | 300          |
| I004      | 400          |

Now let's create a string and assign A to it:

String T = "A";

Try running the following script:

SELECT * FROM ${T};

This produces an error as string_template=off.

Let's set string_template=on. Note we find string_template under the setlocal command:

To set this to 'on' run the following:

setlocal string_template=on;

Try running the following script again:

SELECT * FROM ${T};

This will now output table A.

Let's roll back and set string_template=off. This time we will create the following PROCEDURE:

CREATE PROCEDURE testProc OFTYPE AMISCRIPT USE arguments="String T" script="Int n = 2; Table t = SELECT * FROM ${T} LIMIT n; SELECT * FROM t;";

and then call the procedure as such:

CALL testProc(T);

List of System Procedures

This section will cover most of the procedures included in AMI by default, however the following procedures are covered in the Center Replication page:

  1. __ADD_CENTER

  2. __REMOVE_CENTER

  3. __ADD_REPLICATION

  4. __REMOVE_REPLICATION

__ADD_DATASOURCE

__ADD_DATASOURCE(String name, String adapter_type, String url, String username, String password, String options, String relayId, String permittedOverrides)
  • options: Refers to values under the Advanced section. Must be a comma delimited list e.g. DISABLE_BIGDEC=true,URL_OVERRIDE=jdbc:mysql://serverUrl:1234/databaseName
  • relayId: Refers to "Relay to Run on" under the Configuration section.
  • permittedOverrides: Refers to the checkboxes under the Security section. Must be a comma delimited list. The available values are URL, USERNAME, PASSWORD, OPTIONS, RELAY e.g. to tick the URL and USERNAME checkboxes use URL,USERNAME.

Supported Adapter Types

Name Alias (to be used in the procedure call)
AMI datasource __AMI
Sybase IQ JDBC SYBASE_IQ
Shell Command SHELL
AMIDB AMIDB
MySQL JDBC MYSQL
KDB KDB
SSH Command SSH
Redis Redis
Fred FRED
SQLServer JDBC SQLSERVER
Oracle JDBC ORACLE
Sybase JDBC SYBASE
RestAPI RESTAPI
Postgres JDBC POSTGRES
Flat File Reader FLAT_FILE
Generic JDBC GENERIC_JDBC
IMB DB2 IMBDB2
SQLITE JDBC SQLITE
OneTick ONETICK
Quandl QUANDL

__REMOVE_DATASOURCE

__REMOVE_DATASOURCE(String DSName)

This procedure removes the specified datasource. Use show datasources to see a list of available datasources.

__RESET_TIMER_STATS

__RESET_TIMER_STATS(String timerName, Boolean executedStats, Boolean errorStats)

This procedure clears NextRunTime. Optionally clears ExecutedCount, MillisSpent, and AvgMillisSpent if ExecutedStats is set to true, and optionally clears ErrorCounts if ErrorStats is set to true. Use show timers to see a list of available timers.

__RESET_TRIGGER_STATS

__RESET_TRIGGER_STATS(String triggerName)

This procedure will reset ExecutedCount, MillisSpent, AvgMillisSpent, ErrorsCount, ReturnedFalseCount for a particular trigger. Use show triggers to see information for all triggers.

__SCHEDULE_TIMER

__SCHEDULE_TIMER(String timerName, Long delayMillis)

This procedure schedules the timer to run after the specified number of milliseconds passes. For instance, call __SCHEDULE_TIMER("mytimer", 5000) means mytimer will start running after 5000 milliseconds, or 5 seconds.

__SHOW_TIMER_ERROR

__SHOW_TIMER_ERROR(String timerName)

This procedure shows you the last error the specified timer encountered in a table format.

__SHOW_TRIGGER_ERROR

__SHOW_TRIGGER_ERROR(String triggerName)

This procedure shows you the last error the specified trigger encountered in a table format.