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):

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 procedure 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.)

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:

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:

| 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 3forge 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 datasource_type, String url, String username, String password, String options, String relayId, String permittedOverrides)

Add a datasource via AMIDB shell tool with the given properties:

• name: Name of the datasource as displayed in the Data Modeler.
• datasource_type: adapter type, e.g: MYSQL, AMIDB, etc.
• url: Url of datasource.

The following arguments can be supplied optionally:

• username: Username to access datasource if required.
• password: Password to access datasource if required.
• options: A comma-delimited list of datasource-specific options, e.g. DISABLE_BIGDEC=true,URL_OVERRIDE=jdbc:mysql://serverUrl:1234/databaseName.
• relayId: The name of the Relay to run on.

• permittedOverrides: Overridable values that can be called post-launch, e.g: to supply a password within a datamodel.

  • Valid options are: "URL", "USERNAME", "PASSWORD", "OPTIONS", "RELAY"

  Note

  If you supply any optional parameters, you will need to supply null for any other optional fields you are not using.

Example

CALL __ADD_DATASOURCE("ami_datasource", "AMIDB", "some.host:1780", "demo", "demo123", null,null,null);

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 datasourceName)

Remove datasource by name.

• datasourceName: Name of datasource to remove as visible in the Data Modeler.

Example

CALL __REMOVE_DATASOURCE("ami_datasource");

Reset Timer Stats

__RESET_TIMER_STATS(String timerName, Boolean executedStats, Boolean errorStats)

Clears a given timer with the following properties:

• timerName: Name of the timer .
• executedStats: Whether to clear the stats ExecutedCount, MillisSpent, and AvgMillisSpent.
• errorStats: Whether to clear ErrorCounts.

Reset Trigger Stats

__RESET_TRIGGER_STATS(String triggerName)

Clears a given trigger with the following properties:

• triggerName: Name of the trigger.

  • This procedure will reset ExecutedCount, MillisSpent, AvgMillisSpent, ErrorsCount, ReturnedFalseCount for that trigger.

Schedule Timer

__SCHEDULE_TIMER(String timerName, Long delayMillis)

Schedules when a timer should run after a specified number of milliseconds.

• timerName: Name of the timer to schedule.
• delayMillis: Time in milliseconds to wait before running the timer.

Example

CALL __SCHEDULE_TIMER("mytimer", 5000);

Show Timer Error

__SHOW_TIMER_ERROR(String timerName)

Shows the last error the timer encountered in a table format:

• timerName: Name of the timer to show error for.

Example

CALL __SHOW_TIMER_ERROR("mytimer");

Show Trigger Error

__SHOW_TRIGGER_ERROR(String triggerName)

Shows the last error the trigger encountered in a table format:

• timerName: Name of the trigger to show error for.

Example

CALL __SHOW_TRIGGER_ERROR("mytrigger");

Get Timezone

__GET_TIMEZONE()

Gets the timezone of the 3forge session.

Example

CALL __GET_TIMEZONE();

Set Timezone

__SET_TIMEZONE(String timezone)

Sets the timezone of the 3forge session.

• timezone: Java-accepted timezone

Example

CALL __SET_TIMEZONE("GMT");