SQL Query Files

SQL Query Files

The Generic SQL API Framework executes SQL statements stored in external .sql files. This approach keeps SQL separate from application code, making queries easier to manage, reuse, and maintain.

---

Query Directory

By default, all SQL files are stored in the queries directory.

queries/
├── customers.sql
├── orders.sql
├── products.sql
└── reports/
    ├── sales_report.sql
    └── stock_report.sql

---

Creating a Query

Create a SQL file inside the queries directory.

Example:

queries/customers.sql

SELECT
    CustomerID,
    CustomerName,
    City,
    MobileNumber
FROM CustomerTable;

---

Executing a Query

To execute the SQL file, send the following JSON request.

{
    "query": "customers"
}

The framework automatically executes:

queries/customers.sql

---

Using Parameters

Parameterized queries allow dynamic values to be passed safely.

Example:

SELECT *
FROM CustomerTable
WHERE CustomerID = :CustomerID;

JSON Request:

{
    "query": "customer_by_id",
    "parameters": {
        "CustomerID": 101
    }
}

---

Organizing Queries

For large projects, group SQL files into folders.

Example:

queries/

customers/
    list.sql
    details.sql
    orders.sql

reports/
    daily_sales.sql
    monthly_sales.sql

inventory/
    stock.sql
    movements.sql

---

Naming Conventions

Use meaningful file names.

Good:

customers.sql
customer_by_id.sql
sales_report.sql
inventory_summary.sql

Avoid:

query1.sql
test.sql
new.sql
abc.sql

---

Query Best Practices

• Keep one query per file.
• Use descriptive file names.
• Format SQL consistently.
• Use parameterized queries instead of string concatenation.
• Test queries directly in SQL Server before deploying.

---

Common Errors

Query File Not Found

{
    "success": false,
    "message": "Query file not found."
}

Ensure the SQL file exists in the correct directory.

---

Missing Parameter

{
    "success": false,
    "message": "Missing required parameter."
}

Verify that all required parameters are included in the JSON request.

---

SQL Syntax Error

{
    "success": false,
    "message": "SQL syntax error."
}

Validate the SQL statement using SQL Server Management Studio before executing it through the framework.

---

Security

Always use parameterized queries.

✅ Recommended

SELECT *
FROM CustomerTable
WHERE CustomerID = :CustomerID;

❌ Avoid

SELECT *
FROM CustomerTable
WHERE CustomerID = ' " + id + " ';

Parameterized queries help protect your application against SQL injection attacks.

---

Next Steps

Now that you understand how SQL query files work, continue with:

• Validation Engine
• Query Examples
• Response Format
• Error Handling