Execute query System API
Execute database query using global object 'g'.
| await g.sys.system.executeQuery({
instance: 'INSTANCE_NAME',
database: 'DATABASE_NAME',
collection: 'COLLECTION_NAME',
query: "select * from DATABASE_NAME.COLLECTION_NAME"
});
|
- If the database is Mongodb API Maker supports all the commands listed in below.
- https://www.mongodb.com/docs/drivers/node/current/usage-examples/command/
- https://www.mongodb.com/docs/v6.0/reference/command/
| await g.sys.system.executeQuery({
instance: 'INSTANCE_NAME',
database: 'DATABASE_NAME',
query: {
dbStats: 1,
}
});
|
How we use Native Query in API Maker.
Native query using secret management
Various types of Database & It`s Native Query In API Maker
Please follow these rules when writing a query or statement:
- Use backticks (`) to enclose your query or statement.
- Do not add a semicolon (;) at the end of your query.
| await g.sys.system.executeQuery({
instance: "oracle",
query: `SELECT * FROM "INVENTORY"."employees"`
});
|
DDL Operation in API Maker
-
CREATE TABLE
| await g.sys.system.executeQuery({
instance: "mysql",
query: "CREATE TABLE inventory.employees(id int AUTO_INCREMENT PRIMARY KEY);"
})
|
-
ALTER TABLE
| await g.sys.system.executeQuery({
instance: "mysql",
query: "ALTER TABLE inventory.employees ADD COLUMN emp_name VARCHAR(30) NOT NULL;"
})
|
-
RENAME TABLE
| await g.sys.system.executeQuery({
instance: "mysql",
query: "ALTER TABLE inventory.employees RENAME COLUMN id TO emp_id;"
})
|
-
TRUNCATE TABLE
| await g.sys.system.executeQuery({
instance: "mysql",
query: "TRUNCATE TABLE inventory.employees;"
})
|
-
DROP TABLE
| await g.sys.system.executeQuery({
instance: "mysql",
query: "DROP TABLE IF EXISTS inventory.employees;"
})
|
DML Operation in API Maker
API Maker allows us to perform various DML operations, such as the following examples.
-
INSERT
| await g.sys.system.executeQuery({
instance: "mysql",
query: "INSERT into inventory.employees Values(101, 'Bob', 'M');"
})
|
-
DELETE
| await g.sys.system.executeQuery({
instance: "mysql",
query: "DELETE FROM inventory.employees WHERE emp_Id = '101';"
})
|
-
UPDATE
| await g.sys.system.executeQuery({
instance: "mysql",
query: "UPDATE inventory.employees SET emp_Name = 'Alice' WHERE emp_Id = '101';"
})
|
DQL Operation in API Maker
API Maker allows us to perform various DQL operations, such as the following examples.
-
SELECT
| await g.sys.system.executeQuery({
instance: "mysql",
query: "SELECT * FROM inventory.employees;"
})
|
-
DISTINCT
| await g.sys.system.executeQuery({
instance: "mysql",
query: "SELECT DISTINCT emp_Name FROM inventory.employees;"
})
|
-
ORDER BY
| await g.sys.system.executeQuery({
instance: "mysql",
query: "SELECT * FROM inventory.employees order by emp_Gen,emp_Name;"
})
|
Aggregated Function in API Maker
API Maker allows us to perform various Aggregated Function, such as the following examples.
-
SUM
| await g.sys.system.executeQuery({
instance: "mysql",
query: "SELECT SUM(emp_Sal) as Total_Salary FROM inventory.employees;"
})
|
-
AVG
| await g.sys.system.executeQuery({
instance: "mysql",
query: "SELECT AVG(emp_Sal) as Average_Salary FROM inventory.employees;"
})
|
Joins in API Maker
API Maker allows us to perform Joins, such as the following examples.
-
INNER JOIN
| await g.sys.system.executeQuery({
instance: "mysql",
query: "SELECT * FROM inventory.employees INNER JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;"
})
|
-
LEFT JOIN
| await g.sys.system.executeQuery({
instance: "mysql",
query: "SELECT * FROM inventory.employees LEFT JOIN inventory.attandance ON inventory.employees.emp_Id = inventory.attandance.emp_id;"
})
|
How to call SQL Server Store Procedure
- Just replace your procedure name with below "procedureName".
| await g.sys.system.executeQuery({
instance: "sql_server",
query: "USE inventory; exec dbo.procedureName;"
});
|