Operations (sample payloads)
Main operations
Count rows
Counts the number of rows that meet a given set of conditions. Sample Input
{
"authentication": {},
"table_or_view": "Customers",
"conditions": [
\{
"condition_type": "AND",
"field": "Country",
"operator": "equal to",
"value": "USA"
\},
\{
"condition_type": "AND",
"field": "Age",
"operator": "greater than",
"value": 30
\}
],
"request_timeout": 20000,
"connect_timeout": 18000
}
Sample Output
{
"count": 157
}
Delete rows
Deletes rows in your database based on criteria of your choosing. Sample Input
{
"authentication": {},
"table": "Customers",
"conditions": [
\{
"condition_type": "AND",
"field": "LastPurchaseDate",
"operator": "less than",
"value": "2020-01-01"
\},
\{
"condition_type": "AND",
"field": "TotalPurchases",
"operator": "less than",
"value": 100
\}
]
}
Sample Output
{
"rows_deleted": 15
}
Find rows
Find rows in your database based on criteria of your choosing. Sample Input
{
"authentication": {},
"table_or_view": "Customers",
"fields": [
"CustomerID",
"CompanyName",
"ContactName",
"Country"
],
"conditions": [
\{
"condition_type": "AND",
"field": "Country",
"operator": "equal to",
"value": "Germany"
\},
\{
"condition_type": "AND",
"field": "CustomerID",
"operator": "less than",
"value": "ALFKI"
\}
],
"order_by": "CompanyName",
"offset": 0,
"fetch": 5
}
Sample Output
{
"rows": [
\{
"CustomerID": "ALFKI",
"CompanyName": "Alfreds Futterkiste",
"ContactName": "Maria Anders",
"Country": "Germany"
\},
\{
"CustomerID": "ANATR",
"CompanyName": "Ana Trujillo Emparedados y helados",
"ContactName": "Ana Trujillo",
"Country": "Germany"
\},
\{
"CustomerID": "ANTON",
"CompanyName": "Antonio Moreno Taquería",
"ContactName": "Antonio Moreno",
"Country": "Germany"
\}
]
}
Insert rows
Insert one or more rows into your Microsoft SQL database. Sample Input
{
"authentication": {},
"table": "Customers",
"row_data": [
[
\{
"field": "CustomerID",
"value": "CUST001"
\},
\{
"field": "CompanyName",
"value": "Acme Corporation"
\},
\{
"field": "ContactName",
"value": "John Doe"
\},
\{
"field": "ContactTitle",
"value": "Sales Manager"
\},
\{
"field": "City",
"value": "New York"
\},
\{
"field": "Country",
"value": "USA"
\}
],
[
\{
"field": "CustomerID",
"value": "CUST002"
\},
\{
"field": "CompanyName",
"value": "Global Tech"
\},
\{
"field": "ContactName",
"value": "Jane Smith"
\},
\{
"field": "ContactTitle",
"value": "CEO"
\},
\{
"field": "City",
"value": "London"
\},
\{
"field": "Country",
"value": "UK"
\}
]
],
"return_inserted_rows": true
}
Sample Output
{
"row_count": 2,
"inserted_rows": [
\{
"CustomerID": "CUST001",
"CompanyName": "Acme Corporation",
"ContactName": "John Doe",
"ContactTitle": "Sales Manager",
"City": "New York",
"Country": "USA"
\},
\{
"CustomerID": "CUST002",
"CompanyName": "Global Tech",
"ContactName": "Jane Smith",
"ContactTitle": "CEO",
"City": "London",
"Country": "UK"
\}
]
}
Run SQL query
Run a raw SQL query. Sample Input
{
"authentication": {},
"sql_query": "SELECT TOP 5 * FROM Customers WHERE Country = @1",
"sql_parameters": [
\{
"name": "1",
"type": "VarChar",
"value": "USA"
\}
],
"request_timeout": 20000,
"connect_timeout": 18000
}
Sample Output
{
"success": true,
"row_count": 5,
"rows": [
\{
"CustomerID": "ALFKI",
"CompanyName": "Alfreds Futterkiste",
"ContactName": "Maria Anders",
"ContactTitle": "Sales Representative",
"Address": "Obere Str. 57",
"City": "Berlin",
"Region": null,
"PostalCode": "12209",
"Country": "USA",
"Phone": "030-0074321",
"Fax": "030-0076545"
\},
\{
"CustomerID": "ANATR",
"CompanyName": "Ana Trujillo Emparedados y helados",
"ContactName": "Ana Trujillo",
"ContactTitle": "Owner",
"Address": "Avda. de la Constitución 2222",
"City": "México D.F.",
"Region": null,
"PostalCode": "05021",
"Country": "USA",
"Phone": "(5) 555-4729",
"Fax": "(5) 555-3745"
\},
\{
"CustomerID": "ANTON",
"CompanyName": "Antonio Moreno Taquería",
"ContactName": "Antonio Moreno",
"ContactTitle": "Owner",
"Address": "Mataderos 2312",
"City": "México D.F.",
"Region": null,
"PostalCode": "05023",
"Country": "USA",
"Phone": "(5) 555-3932",
"Fax": null
\},
\{
"CustomerID": "AROUT",
"CompanyName": "Around the Horn",
"ContactName": "Thomas Hardy",
"ContactTitle": "Sales Representative",
"Address": "120 Hanover Sq.",
"City": "London",
"Region": null,
"PostalCode": "WA1 1DP",
"Country": "USA",
"Phone": "(171) 555-7788",
"Fax": "(171) 555-6750"
\},
\{
"CustomerID": "BERGS",
"CompanyName": "Berglunds snabbköp",
"ContactName": "Christina Berglund",
"ContactTitle": "Order Administrator",
"Address": "Berguvsvägen 8",
"City": "Luleå",
"Region": null,
"PostalCode": "S-958 22",
"Country": "USA",
"Phone": "0921-12 34 65",
"Fax": "0921-12 34 67"
\}
]
}
Update rows
Updates rows in your database based on criteria of your choosing. Sample Input
{
"authentication": {},
"table": "Customers",
"row_data": [
\{
"field": "ContactName",
"value": "John Smith"
\},
\{
"field": "Phone",
"value": "(555) 123-4567"
\}
],
"conditions": [
\{
"condition_type": "AND",
"field": "CustomerID",
"operator": "equal to",
"value": "ALFKI"
\}
],
"is_newer_server": true
}
Sample Output
{
"row_count": 1,
"updated_rows": [
\{
"CustomerID": "ALFKI",
"CompanyName": "Alfreds Futterkiste",
"ContactName": "John Smith",
"ContactTitle": "Sales Representative",
"Address": "Obere Str. 57",
"City": "Berlin",
"Region": null,
"PostalCode": "12209",
"Country": "Germany",
"Phone": "(555) 123-4567",
"Fax": "030-0076545"
\}
]
}
DDL operations
List table fields (DDL)
Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping
Sample Input
{
"authentication": {},
"request_timeout": 20000,
"connect_timeout": 18000
}
Sample Output
[
\{
"table_name": "Customers",
"column_name": "CustomerID",
"data_type": "INT",
"is_nullable": "NO",
"column_default": null,
"character_maximum_length": null,
"numeric_precision": 10,
"numeric_scale": 0
\},
\{
"table_name": "Customers",
"column_name": "FirstName",
"data_type": "VARCHAR",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": 50,
"numeric_precision": null,
"numeric_scale": null
\},
\{
"table_name": "Customers",
"column_name": "LastName",
"data_type": "VARCHAR",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": 50,
"numeric_precision": null,
"numeric_scale": null
\},
\{
"table_name": "Customers",
"column_name": "Email",
"data_type": "VARCHAR",
"is_nullable": "YES",
"column_default": null,
"character_maximum_length": 100,
"numeric_precision": null,
"numeric_scale": null
\},
\{
"table_name": "Customers",
"column_name": "CreatedDate",
"data_type": "DATETIME",
"is_nullable": "NO",
"column_default": "GETDATE()",
"character_maximum_length": null,
"numeric_precision": null,
"numeric_scale": null
\}
]
List tables and views (DDL)
Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping
Sample Input
{
"authentication": {},
"request_timeout": 20000,
"connect_timeout": 18000
}
Sample Output
[
\{
"schema": "dbo",
"name": "Customers",
"type": "TABLE",
"ddl": "CREATE TABLE [dbo].[Customers] (\n [CustomerID] INT PRIMARY KEY,\n [FirstName] NVARCHAR(50),\n [LastName] NVARCHAR(50),\n [Email] NVARCHAR(100),\n [Phone] NVARCHAR(20)\n)"
\},
\{
"schema": "dbo",
"name": "Orders",
"type": "TABLE",
"ddl": "CREATE TABLE [dbo].[Orders] (\n [OrderID] INT PRIMARY KEY,\n [CustomerID] INT FOREIGN KEY REFERENCES Customers(CustomerID),\n [OrderDate] DATETIME,\n [TotalAmount] DECIMAL(10, 2)\n)"
\},
\{
"schema": "dbo",
"name": "ActiveCustomers",
"type": "VIEW",
"ddl": "CREATE VIEW [dbo].[ActiveCustomers] AS\nSELECT c.CustomerID, c.FirstName, c.LastName, c.Email\nFROM Customers c\nINNER JOIN Orders o ON c.CustomerID = o.CustomerID\nWHERE o.OrderDate >= DATEADD(MONTH, -6, GETDATE())"
\}
]
List tables (DDL)
Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping
Sample Input
{
"authentication": {},
"request_timeout": 20000,
"connect_timeout": 18000
}
Sample Output
[
\{
"table_name": "Customers",
"ddl": "CREATE TABLE Customers (\n CustomerID int PRIMARY KEY,\n FirstName varchar(50),\n LastName varchar(50),\n Email varchar(100),\n Phone varchar(20),\n Address varchar(200),\n City varchar(50),\n State varchar(2),\n ZipCode varchar(10),\n CreatedDate datetime DEFAULT GETDATE()\n);"
\},
\{
"table_name": "Orders",
"ddl": "CREATE TABLE Orders (\n OrderID int PRIMARY KEY,\n CustomerID int FOREIGN KEY REFERENCES Customers(CustomerID),\n OrderDate datetime DEFAULT GETDATE(),\n TotalAmount decimal(10,2),\n Status varchar(20)\n);"
\},
\{
"table_name": "Products",
"ddl": "CREATE TABLE Products (\n ProductID int PRIMARY KEY,\n ProductName varchar(100),\n Description text,\n Price decimal(10,2),\n StockQuantity int,\n CategoryID int\n);"
\}
]
List views (DDL)
Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping
Sample Input
{
"authentication": {},
"request_timeout": 20000,
"connect_timeout": 18000
}
Sample Output
[
\{
"schema_name": "dbo",
"view_name": "CustomerOrders",
"ddl": "CREATE VIEW dbo.CustomerOrders AS SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate, o.TotalAmount FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID"
\},
\{
"schema_name": "sales",
"view_name": "TopProducts",
"ddl": "CREATE VIEW sales.TopProducts AS SELECT TOP 10 p.ProductID, p.ProductName, SUM(od.Quantity) AS TotalQuantitySold FROM Products p JOIN OrderDetails od ON p.ProductID = od.ProductID GROUP BY p.ProductID, p.ProductName ORDER BY TotalQuantitySold DESC"
\},
\{
"schema_name": "hr",
"view_name": "EmployeePerformance",
"ddl": "CREATE VIEW hr.EmployeePerformance AS SELECT e.EmployeeID, e.FirstName, e.LastName, COUNT(o.OrderID) AS TotalOrders, SUM(o.TotalAmount) AS TotalSales FROM Employees e LEFT JOIN Orders o ON e.EmployeeID = o.EmployeeID GROUP BY e.EmployeeID, e.FirstName, e.LastName"
\}
]