Registersql - Revision history

WikiSysopdi at 15:02, 26 November 2024

2024-11-26T15:02:42Z

← Older revision		Revision as of 15:02, 26 November 2024
Line 72:		Line 72:
	'''As per the Sage 100 Example'''		'''As per the Sage 100 Example'''

	~~https://apidev.crmtogether.com/rest2/mastersqlstatements/5ccb3342-3047-4065-afaf-1effc6e7d870/getEntityData/arcustomers/idfield/'02_ALLENAP'~~		the test

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

			done via REST is

			[https://apidev.crmtogether.com/rest2/mastersqlstatements/5ccb3342-3047-4065-afaf-1effc6e7d870/getEntityData/arcustomers/idfield/'02_ALLENAP' https://apius.crmtogether.com/rest2/mastersqlstatements/5ccb3123-3047-4065-afaf-1effc6e7d870/getEntityData/arcustomers/idfield/'02_ALLENAP']






			---

	WIP		WIP

2024-11-26T10:14:33Z

REST2 API

← Older revision		Revision as of 10:14, 26 November 2024
Line 58:		Line 58:

	=== REST2 API ===		=== REST2 API ===
	Once we have any sql statement which we register with using registersql, then we can perform ~~three~~ calls as:		Once we have any sql statement which we register with using registersql, then we can perform the API calls as:

	~~SQL_Rows_By_Query~~ (GET) => To get the sql data/records based on sql statement (eg. sql select statement) with passing parameters.		(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/{{regionid}}/{{sqlname}}/{{islandname}}		Url: https://apidev.crmtogether.com/rest2/mastersqlstatements/{{regionid}}/{{sqlname}}/{{islandname}}
Line 69:		Line 69:

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

			'''As per the Sage 100 Example'''

			https://apidev.crmtogether.com/rest2/mastersqlstatements/5ccb3342-3047-4065-afaf-1effc6e7d870/getEntityData/arcustomers/idfield/'02_ALLENAP'







			WIP

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

2024-11-26T09:53:36Z

← Older revision		Revision as of 09:53, 26 November 2024
Line 1:		Line 1:
	We can register a sql query using registersql command.		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>)		(Syntax : registersql "sqlname" "description" "sql statement" <ENTER>)

2024-11-26T09:52:23Z

← Older revision		Revision as of 09:52, 26 November 2024
Line 30:		Line 30:

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

	~~Then from here you can call thsis via a REST API~~


	---		---

2024-11-26T09:51:48Z

← Older revision		Revision as of 09:51, 26 November 2024
Line 5:		Line 5:
	Example		Example

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

	=== Permissions ===		=== Permissions ===

2024-11-26T09:50:19Z

← Older revision		Revision as of 09:50, 26 November 2024
Line 1:		Line 1:
	We can register a sql ~~with~~ using registersql command.		We can register a sql query using registersql command.

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

2024-11-25T18:04:02Z

@@ Line 6: / Line 6: @@
 >registersql "getBerlinCustomers" "return only customers from Berlin" "select \"CustomerID\", \"CompanyName\", \"City\", \"Phone\" from customers where City='Berlin'"
 === Sage 100 Example ===
@@ Line 46: / Line 53: @@
 removesql "NAME"

2024-11-25T17:58:57Z

← Older revision		Revision as of 17:58, 25 November 2024
Line 40:		Line 40:

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

			---

			'''Delete a SQL statement'''

			removesql "NAME"

2024-11-25T17:57:29Z

← Older revision		Revision as of 17:57, 25 November 2024
Line 25:		Line 25:

	Then from here you can call thsis via a REST API		Then from here you can call thsis via a REST API


			---

			'''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"'''<nowiki/>'"

			'''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"'

2024-11-25T17:56:54Z

← Older revision		Revision as of 17:56, 25 November 2024
Line 6:		Line 6:

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

			=== 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'

			Then from here you can call thsis via a REST API