Registersql

We can register a sql query using registersql command.

(Syntax : registersql "sqlname" "description" "sql statement" <ENTER>)

Example

>registersql "getBerlinCustomers" "return only customers from Berlin" "select "CustomerID", "CompanyName", "City", "Phone" from customers where City='Berlin'"

Permissions

To only give access to these statements use

REST2readwrite

Users with this only on a region cannot see the region in the console. cannot access REST1  but can access REST2 api in full

Sage 100 Example

We register the SQL

>registersql "getEntityData" "Get island data" "select * from {0} where 150=150 and {1}={2}"

And from the console we can test this

>test_registersql "getEntityData" "arcustomers,idfield,'02_ALLENAP'"

and then this returns the SQL and whether it is valid or not

EG

Fetched data with rows#1

SQL Statement: select * from arcustomers where 150=150 and idfield='02_ALLENAP'

---

Here is how we can register a sql statement with double quote vaules:

>registersql "sqltest38" "get 38 2" "select "IDCUST","IDBILLCYCL" from testarcus where "IDBILLCYCL" like '%{0}%' OR "IDBILLCYCL"='"abc"'"

And we can see the result as:

>test_registersql "sqltest38" "BILLMT"

Fetched data with rows#20

SQL Statement: select "IDCUST","IDBILLCYCL" from testarcus where "IDBILLCYCL" like '%BILLMT%' OR "IDBILLCYCL"='"abc"'

---

Delete a SQL statement

removesql "NAME"

REST2 API

Once we have any sql statement which we register with using registersql, then we can perform three calls as:

SQL_Rows_By_Query (GET) => To get the sql data/records based on sql statement (eg. sql select statement) with passing parameters.

Url: https://apidev.crmtogether.com/rest2/mastersqlstatements/Template:Regionid/Template:Sqlname/Template:Islandname

Template:Regionid => This region id means under which region the islandname is available.

Template:Sqlname => This is the sql statement name which we given while performing registersql command (or while calling Add_SQL_STATEMENT Api call)

Template:Islandname => This is the parameter as in sql statement we given "select * from {0}"

SQL_Row_Insert (POST) => To insert data/records with using sql statement (eg. sql insert statement) with passing parameters.

Url: https://apidev.crmtogether.com/rest2/mastersqlstatements/Template:Regionid/Template:Sqlname

SQL_Row_Update (PUT) => To update data/record(s) with using sql statement (eg. sql update statement) with passing parameters.

Url: https://apidev.crmtogether.com/rest2/mastersqlstatements/Template:Regionid/Template:Sqlname