Registersql

We can register a sql query using registersql command.

Why do this?

Well this allows you to dictate secure access to your data on data islands when consumed by an application.

We call this our REST2 API

(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