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 the API calls as:
(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}"
As per the Sage 100 Example
the test
>test_registersql "getEntityData" "arcustomers,idfield,'02_ALLENAP'"
done via REST is
---
WIP
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
