Operations (sample payloads)

Main operations
Copy

Count rows
Copy

Counts the number of rows that meet a given set of conditions.

Sample Input

1{
2    "authentication": {},
3    "table_or_view": "Customers",
4    "conditions": [
5        {
6            "condition_type": "AND",
7            "field": "Country",
8            "operator": "equal to",
9            "value": "USA"
10        },
11        {
12            "condition_type": "AND",
13            "field": "Age",
14            "operator": "greater than",
15            "value": 30
16        }
17    ],
18    "request_timeout": 20000,
19    "connect_timeout": 18000
20}

Sample Output

1{
2    "count": 157
3}

Delete rows
Copy

Deletes rows in your database based on criteria of your choosing.

Sample Input

1{
2    "authentication": {},
3    "table": "Customers",
4    "conditions": [
5        {
6            "condition_type": "AND",
7            "field": "LastPurchaseDate",
8            "operator": "less than",
9            "value": "2020-01-01"
10        },
11        {
12            "condition_type": "AND",
13            "field": "TotalPurchases",
14            "operator": "less than",
15            "value": 100
16        }
17    ]
18}

Sample Output

1{
2    "rows_deleted": 15
3}

Find rows
Copy

Find rows in your database based on criteria of your choosing.

Sample Input

1{
2    "authentication": {},
3    "table_or_view": "Customers",
4    "fields": [
5        "CustomerID",
6        "CompanyName",
7        "ContactName",
8        "Country"
9    ],
10    "conditions": [
11        {
12            "condition_type": "AND",
13            "field": "Country",
14            "operator": "equal to",
15            "value": "Germany"
16        },
17        {
18            "condition_type": "AND",
19            "field": "CustomerID",
20            "operator": "less than",
21            "value": "ALFKI"
22        }
23    ],
24    "order_by": "CompanyName",
25    "offset": 0,
26    "fetch": 5
27}

Sample Output

1{
2    "rows": [
3        {
4            "CustomerID": "ALFKI",
5            "CompanyName": "Alfreds Futterkiste",
6            "ContactName": "Maria Anders",
7            "Country": "Germany"
8        },
9        {
10            "CustomerID": "ANATR",
11            "CompanyName": "Ana Trujillo Emparedados y helados",
12            "ContactName": "Ana Trujillo",
13            "Country": "Germany"
14        },
15        {
16            "CustomerID": "ANTON",
17            "CompanyName": "Antonio Moreno Taquería",
18            "ContactName": "Antonio Moreno",
19            "Country": "Germany"
20        }
21    ]
22}

Insert rows
Copy

Insert one or more rows into your Microsoft SQL database.

Sample Input

1{
2    "authentication": {},
3    "table": "Customers",
4    "row_data": [
5        [
6            {
7                "field": "CustomerID",
8                "value": "CUST001"
9            },
10            {
11                "field": "CompanyName",
12                "value": "Acme Corporation"
13            },
14            {
15                "field": "ContactName",
16                "value": "John Doe"
17            },
18            {
19                "field": "ContactTitle",
20                "value": "Sales Manager"
21            },
22            {
23                "field": "City",
24                "value": "New York"
25            },
26            {
27                "field": "Country",
28                "value": "USA"
29            }
30        ],
31        [
32            {
33                "field": "CustomerID",
34                "value": "CUST002"
35            },
36            {
37                "field": "CompanyName",
38                "value": "Global Tech"
39            },
40            {
41                "field": "ContactName",
42                "value": "Jane Smith"
43            },
44            {
45                "field": "ContactTitle",
46                "value": "CEO"
47            },
48            {
49                "field": "City",
50                "value": "London"
51            },
52            {
53                "field": "Country",
54                "value": "UK"
55            }
56        ]
57    ],
58    "return_inserted_rows": true
59}

Sample Output

1{
2    "row_count": 2,
3    "inserted_rows": [
4        {
5            "CustomerID": "CUST001",
6            "CompanyName": "Acme Corporation",
7            "ContactName": "John Doe",
8            "ContactTitle": "Sales Manager",
9            "City": "New York",
10            "Country": "USA"
11        },
12        {
13            "CustomerID": "CUST002",
14            "CompanyName": "Global Tech",
15            "ContactName": "Jane Smith",
16            "ContactTitle": "CEO",
17            "City": "London",
18            "Country": "UK"
19        }
20    ]
21}

Run SQL query
Copy

Run a raw SQL query.

Sample Input

1{
2    "authentication": {},
3    "sql_query": "SELECT TOP 5 * FROM Customers WHERE Country = @1",
4    "sql_parameters": [
5        {
6            "name": "1",
7            "type": "VarChar",
8            "value": "USA"
9        }
10    ],
11    "request_timeout": 20000,
12    "connect_timeout": 18000
13}

Sample Output

1{
2    "success": true,
3    "row_count": 5,
4    "rows": [
5        {
6            "CustomerID": "ALFKI",
7            "CompanyName": "Alfreds Futterkiste",
8            "ContactName": "Maria Anders",
9            "ContactTitle": "Sales Representative",
10            "Address": "Obere Str. 57",
11            "City": "Berlin",
12            "Region": null,
13            "PostalCode": "12209",
14            "Country": "USA",
15            "Phone": "030-0074321",
16            "Fax": "030-0076545"
17        },
18        {
19            "CustomerID": "ANATR",
20            "CompanyName": "Ana Trujillo Emparedados y helados",
21            "ContactName": "Ana Trujillo",
22            "ContactTitle": "Owner",
23            "Address": "Avda. de la Constitución 2222",
24            "City": "México D.F.",
25            "Region": null,
26            "PostalCode": "05021",
27            "Country": "USA",
28            "Phone": "(5) 555-4729",
29            "Fax": "(5) 555-3745"
30        },
31        {
32            "CustomerID": "ANTON",
33            "CompanyName": "Antonio Moreno Taquería",
34            "ContactName": "Antonio Moreno",
35            "ContactTitle": "Owner",
36            "Address": "Mataderos 2312",
37            "City": "México D.F.",
38            "Region": null,
39            "PostalCode": "05023",
40            "Country": "USA",
41            "Phone": "(5) 555-3932",
42            "Fax": null
43        },
44        {
45            "CustomerID": "AROUT",
46            "CompanyName": "Around the Horn",
47            "ContactName": "Thomas Hardy",
48            "ContactTitle": "Sales Representative",
49            "Address": "120 Hanover Sq.",
50            "City": "London",
51            "Region": null,
52            "PostalCode": "WA1 1DP",
53            "Country": "USA",
54            "Phone": "(171) 555-7788",
55            "Fax": "(171) 555-6750"
56        },
57        {
58            "CustomerID": "BERGS",
59            "CompanyName": "Berglunds snabbköp",
60            "ContactName": "Christina Berglund",
61            "ContactTitle": "Order Administrator",
62            "Address": "Berguvsvägen 8",
63            "City": "Luleå",
64            "Region": null,
65            "PostalCode": "S-958 22",
66            "Country": "USA",
67            "Phone": "0921-12 34 65",
68            "Fax": "0921-12 34 67"
69        }
70    ]
71}

Update rows
Copy

Updates rows in your database based on criteria of your choosing.

Sample Input

1{
2    "authentication": {},
3    "table": "Customers",
4    "row_data": [
5        {
6            "field": "ContactName",
7            "value": "John Smith"
8        },
9        {
10            "field": "Phone",
11            "value": "(555) 123-4567"
12        }
13    ],
14    "conditions": [
15        {
16            "condition_type": "AND",
17            "field": "CustomerID",
18            "operator": "equal to",
19            "value": "ALFKI"
20        }
21    ],
22    "is_newer_server": true
23}

Sample Output

1{
2    "row_count": 1,
3    "updated_rows": [
4        {
5            "CustomerID": "ALFKI",
6            "CompanyName": "Alfreds Futterkiste",
7            "ContactName": "John Smith",
8            "ContactTitle": "Sales Representative",
9            "Address": "Obere Str. 57",
10            "City": "Berlin",
11            "Region": null,
12            "PostalCode": "12209",
13            "Country": "Germany",
14            "Phone": "(555) 123-4567",
15            "Fax": "030-0076545"
16        }
17    ]
18}

DDL operations
Copy

List table fields (DDL)
Copy

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

1{
2    "authentication": {},
3    "request_timeout": 20000,
4    "connect_timeout": 18000
5}

Sample Output

1[
2    {
3        "table_name": "Customers",
4        "column_name": "CustomerID",
5        "data_type": "INT",
6        "is_nullable": "NO",
7        "column_default": null,
8        "character_maximum_length": null,
9        "numeric_precision": 10,
10        "numeric_scale": 0
11    },
12    {
13        "table_name": "Customers",
14        "column_name": "FirstName",
15        "data_type": "VARCHAR",
16        "is_nullable": "YES",
17        "column_default": null,
18        "character_maximum_length": 50,
19        "numeric_precision": null,
20        "numeric_scale": null
21    },
22    {
23        "table_name": "Customers",
24        "column_name": "LastName",
25        "data_type": "VARCHAR",
26        "is_nullable": "YES",
27        "column_default": null,
28        "character_maximum_length": 50,
29        "numeric_precision": null,
30        "numeric_scale": null
31    },
32    {
33        "table_name": "Customers",
34        "column_name": "Email",
35        "data_type": "VARCHAR",
36        "is_nullable": "YES",
37        "column_default": null,
38        "character_maximum_length": 100,
39        "numeric_precision": null,
40        "numeric_scale": null
41    },
42    {
43        "table_name": "Customers",
44        "column_name": "CreatedDate",
45        "data_type": "DATETIME",
46        "is_nullable": "NO",
47        "column_default": "GETDATE()",
48        "character_maximum_length": null,
49        "numeric_precision": null,
50        "numeric_scale": null
51    }
52]

List tables and views (DDL)
Copy

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

1{
2    "authentication": {},
3    "request_timeout": 20000,
4    "connect_timeout": 18000
5}

Sample Output

1[
2    {
3        "schema": "dbo",
4        "name": "Customers",
5        "type": "TABLE",
6        "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)"
7    },
8    {
9        "schema": "dbo",
10        "name": "Orders",
11        "type": "TABLE",
12        "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)"
13    },
14    {
15        "schema": "dbo",
16        "name": "ActiveCustomers",
17        "type": "VIEW",
18        "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())"
19    }
20]

List tables (DDL)
Copy

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

1{
2    "authentication": {},
3    "request_timeout": 20000,
4    "connect_timeout": 18000
5}

Sample Output

1[
2    {
3        "table_name": "Customers",
4        "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);"
5    },
6    {
7        "table_name": "Orders",
8        "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);"
9    },
10    {
11        "table_name": "Products",
12        "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);"
13    }
14]

List views (DDL)
Copy

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

1{
2    "authentication": {},
3    "request_timeout": 20000,
4    "connect_timeout": 18000
5}

Sample Output

1[
2    {
3        "schema_name": "dbo",
4        "view_name": "CustomerOrders",
5        "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"
6    },
7    {
8        "schema_name": "sales",
9        "view_name": "TopProducts",
10        "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"
11    },
12    {
13        "schema_name": "hr",
14        "view_name": "EmployeePerformance",
15        "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"
16    }
17]

Operations (sample payloads)

Main operationsCopy

Count rowsCopy

Delete rowsCopy

Find rowsCopy

Insert rowsCopy

Run SQL queryCopy

Update rowsCopy

DDL operationsCopy

List table fields (DDL)Copy

List tables and views (DDL)Copy

List tables (DDL)Copy

List views (DDL)Copy

Notes on using Microsoft SQL database

Operations

Main operations
Copy

Count rows
Copy

Delete rows
Copy

Find rows
Copy

Insert rows
Copy

Run SQL query
Copy

Update rows
Copy

DDL operations
Copy

List table fields (DDL)
Copy

List tables and views (DDL)
Copy

List tables (DDL)
Copy

List views (DDL)
Copy