DoSqlQuery

Overview

DoSqlQuery executes any SQL query against the database for complex reporting, analytics, and multi-table operations. It provides full SQL syntax support including JOINs, aggregations, subqueries, and advanced SQL features.

When to Use

✅ Use DoSqlQuery for:

• Complex queries with JOINs across multiple tables
• Aggregations (SUM, COUNT, AVG, MIN, MAX)
• Reporting and analytics
• Core database tables (Loans, Customers, Deposits)
• Complex business logic requiring SQL

❌ Don't use for:

• Simple single-table queries (use GetFullTableQuery for BPM tables)
• Standard entity queries (use RetrieveLoanListQuery, etc. when available)
• INSERT/UPDATE/DELETE operations (use specific commands)

Syntax

doCmd() Usage

var result = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT * FROM TableName WHERE Condition = Value',
    timeout: 30000
  }
});

JSON Configuration

{
  "commandName": "DoSqlQuery",
  "parameters": {
    "Data": {
      "sql": "SELECT L.*, C.FirstName FROM Loans L INNER JOIN Customers C ON L.CustomerId = C.Id WHERE L.Status = 'ACTIVE'",
      "timeout": 30000
    }
  }
}

Parameters

Parameter	Type	Required	Description	Default
Data.sql	string	Yes	SQL query to execute	-
Data.timeout	number	No	Query timeout in milliseconds	No timeout

SQL Capabilities

Feature	Supported	Example
SELECT	✅ Yes	SELECT * FROM Loans
JOIN	✅ Yes	INNER JOIN, LEFT JOIN, RIGHT JOIN
WHERE	✅ Yes	WHERE Status = 'ACTIVE'
GROUP BY	✅ Yes	GROUP BY CustomerId
ORDER BY	✅ Yes	ORDER BY Amount DESC
HAVING	✅ Yes	HAVING COUNT(*) > 5
Subqueries	✅ Yes	WHERE Id IN (SELECT ...)
Aggregations	✅ Yes	SUM(), COUNT(), AVG(), MIN(), MAX()
CASE	✅ Yes	CASE WHEN ... THEN ... END
Date Functions	✅ Yes	DATEADD(), DATEDIFF(), GETDATE()
String Functions	✅ Yes	CONCAT(), SUBSTRING(), UPPER()
INSERT	❌ No	Use specific commands
UPDATE	❌ No	Use specific commands
DELETE	❌ No	Use specific commands

Return Value

Success Response

{
  "isSuccessful": true,
  "message": "SQL query executed successfully.",
  "data": [
    {
      "column1": "value1",
      "column2": "value2",
      "column3": "value3"
    },
    {
      "column1": "value4",
      "column2": "value5",
      "column3": "value6"
    }
  ]
}

Error Response

{
  "isSuccessful": false,
  "message": "SQL error: Invalid column name 'ColumnName'."
}

Examples

Example 1: Customer Loan Summary

var customerId = context.customerId;

var result = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT C.Id, C.FirstName, C.LastName, C.CreditScore, ' +
         'COUNT(L.Id) AS LoanCount, ' +
         'SUM(L.PrincipalAmount) AS TotalLoanAmount, ' +
         'SUM(L.Balance) AS TotalBalance ' +
         'FROM Customers C ' +
         'LEFT JOIN Loans L ON C.Id = L.CustomerId AND L.Status = \'ACTIVE\' ' +
         'WHERE C.Id = ' + customerId + ' ' +
         'GROUP BY C.Id, C.FirstName, C.LastName, C.CreditScore',
    timeout: 30000
  }
});

if (result.isSuccessful && result.data.length > 0) {
  var summary = result.data[0];
  
  context.customerLoanCount = summary.loanCount;
  context.customerTotalDebt = summary.totalBalance;
  context.customerCreditScore = summary.creditScore;
  
  return {
    success: true,
    loanCount: summary.loanCount,
    totalDebt: summary.totalBalance
  };
}

Example 2: Overdue Loans Report

var reportResult = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT L.LoanAccountId, ' +
         'C.FirstName + \' \' + C.LastName AS CustomerName, ' +
         'C.MobileNumber, ' +
         'L.NextPaymentDate, ' +
         'L.NextPaymentAmount, ' +
         'DATEDIFF(day, L.NextPaymentDate, GETDATE()) AS DaysOverdue ' +
         'FROM Loans L ' +
         'INNER JOIN Customers C ON L.CustomerId = C.Id ' +
         'WHERE L.Status = \'ACTIVE\' ' +
         'AND L.NextPaymentDate < GETDATE() ' +
         'ORDER BY DaysOverdue DESC',
    timeout: 45000
  }
});

if (reportResult.isSuccessful) {
  context.overdueLoans = reportResult.data;
  return {
    success: true,
    overdueCount: reportResult.data.length,
    loans: reportResult.data
  };
}

Example 3: Branch Performance Dashboard

var performanceResult = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT B.BranchName, ' +
         'COUNT(DISTINCT L.Id) AS TotalLoans, ' +
         'SUM(L.PrincipalAmount) AS TotalDisbursed, ' +
         'COUNT(DISTINCT L.CustomerId) AS UniqueCustomers, ' +
         'AVG(L.InterestRate) AS AvgRate ' +
         'FROM Loans L ' +
         'INNER JOIN Branches B ON L.BranchId = B.Id ' +
         'WHERE L.DisbursementDate >= DATEADD(month, -1, GETDATE()) ' +
         'GROUP BY B.BranchName ' +
         'ORDER BY TotalDisbursed DESC',
    timeout: 60000
  }
});

if (performanceResult.isSuccessful) {
  context.branchPerformance = performanceResult.data;
  return performanceResult.data;
}

Example 4: Credit Score Distribution

var distributionResult = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT ' +
         'CASE ' +
         '  WHEN CreditScore >= 800 THEN \'Excellent\' ' +
         '  WHEN CreditScore >= 700 THEN \'Good\' ' +
         '  WHEN CreditScore >= 600 THEN \'Fair\' ' +
         '  ELSE \'Poor\' ' +
         'END AS CreditCategory, ' +
         'COUNT(*) AS CustomerCount, ' +
         'AVG(CreditScore) AS AvgScore ' +
         'FROM Customers ' +
         'WHERE Status = \'ACTIVE\' ' +
         'GROUP BY CASE ' +
         '  WHEN CreditScore >= 800 THEN \'Excellent\' ' +
         '  WHEN CreditScore >= 700 THEN \'Good\' ' +
         '  WHEN CreditScore >= 600 THEN \'Fair\' ' +
         '  ELSE \'Poor\' ' +
         'END',
    timeout: 30000
  }
});

if (distributionResult.isSuccessful) {
  context.creditDistribution = distributionResult.data;
  return distributionResult.data;
}

Example 5: Transaction History with Subquery

var customerId = context.customerId;

var transactionResult = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT LT.*, L.LoanAccountId, ' +
         '(SELECT SUM(Amount) ' +
         ' FROM LoanTransactions LT2 ' +
         ' WHERE LT2.LoanId = LT.LoanId ' +
         ' AND LT2.TransactionDate <= LT.TransactionDate) AS RunningBalance ' +
         'FROM LoanTransactions LT ' +
         'INNER JOIN Loans L ON LT.LoanId = L.Id ' +
         'WHERE L.CustomerId = ' + customerId + ' ' +
         'ORDER BY LT.TransactionDate DESC',
    timeout: 30000
  }
});

if (transactionResult.isSuccessful) {
  context.transactionHistory = transactionResult.data;
  return transactionResult.data;
}

Use Cases

1. Customer Risk Assessment

var riskResult = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT C.Id, C.CreditScore, ' +
         'COUNT(L.Id) AS ActiveLoans, ' +
         'SUM(L.Balance) AS TotalDebt, ' +
         'CASE ' +
         '  WHEN C.CreditScore >= 700 AND COUNT(L.Id) < 3 THEN \'LOW\' ' +
         '  WHEN C.CreditScore >= 600 THEN \'MEDIUM\' ' +
         '  ELSE \'HIGH\' ' +
         'END AS RiskCategory ' +
         'FROM Customers C ' +
         'LEFT JOIN Loans L ON C.Id = L.CustomerId AND L.Status = \'ACTIVE\' ' +
         'WHERE C.Id = ' + context.customerId + ' ' +
         'GROUP BY C.Id, C.CreditScore',
    timeout: 30000
  }
});

2. Portfolio Analysis

var portfolioResult = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT ProductType, ' +
         'COUNT(*) AS TotalLoans, ' +
         'SUM(PrincipalAmount) AS TotalDisbursed, ' +
         'SUM(Balance) AS OutstandingBalance, ' +
         'AVG(InterestRate) AS AvgRate, ' +
         'SUM(CASE WHEN Status = \'OVERDUE\' THEN 1 ELSE 0 END) AS OverdueCount ' +
         'FROM Loans ' +
         'GROUP BY ProductType',
    timeout: 45000
  }
});

3. Payment Pattern Analysis

var patternResult = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT L.CustomerId, ' +
         'COUNT(LT.Id) AS PaymentCount, ' +
         'AVG(DATEDIFF(day, L.DueDate, LT.TransactionDate)) AS AvgDelayDays, ' +
         'SUM(CASE WHEN LT.TransactionDate > L.DueDate THEN 1 ELSE 0 END) AS LatePayments ' +
         'FROM Loans L ' +
         'INNER JOIN LoanTransactions LT ON L.Id = LT.LoanId ' +
         'WHERE LT.TransactionType = \'REPAYMENT\' ' +
         'AND LT.TransactionDate >= DATEADD(month, -6, GETDATE()) ' +
         'GROUP BY L.CustomerId ' +
         'HAVING COUNT(LT.Id) >= 3',
    timeout: 45000
  }
});

Error Handling

try {
  var result = doCmd('DoSqlQuery', {
    Data: {
      sql: 'SELECT * FROM Loans WHERE CustomerId = ' + context.customerId,
      timeout: 30000
    }
  });
  
  if (!result.isSuccessful) {
    console.error('SQL query failed:', result.message);
    
    // Check for specific errors
    if (result.message.indexOf('Timeout') !== -1) {
      throw new Error('Query timeout - consider optimizing SQL or increasing timeout');
    } else if (result.message.indexOf('Invalid column') !== -1) {
      throw new Error('SQL error - check column names');
    } else if (result.message.indexOf('Invalid object name') !== -1) {
      throw new Error('Table does not exist');
    } else {
      throw new Error('SQL execution failed: ' + result.message);
    }
  }
  
  if (result.data.length === 0) {
    console.warn('No data returned from query');
    return { hasData: false };
  }
  
  // Process results
  return { hasData: true, data: result.data };
  
} catch (error) {
  console.error('Exception in DoSqlQuery:', error.message);
  return { hasData: false, error: error.message };
}

Security Considerations

⚠️ SQL Injection Prevention

NEVER allow unsanitized user input directly in SQL:

// ❌ UNSAFE - SQL Injection Risk
var sql = 'SELECT * FROM Loans WHERE CustomerId = ' + userInput;

// ✅ SAFE - Validated input
var customerId = parseInt(context.customerId);
if (isNaN(customerId) || customerId <= 0) {
  throw new Error('Invalid customer ID');
}
var sql = 'SELECT * FROM Loans WHERE CustomerId = ' + customerId;

// ✅ BETTER - Use specific Query commands when available
var result = doCmd('RetrieveLoanListQuery', {
  Data: {
    filters: {
      customerId: context.customerId
    }
  }
});

Best Practices for Security

1. Validate all inputs before building SQL
2. Use specific commands when available (safer)
3. Limit user permissions - restrict who can use DoSqlQuery
4. Audit all usage - log all SQL executions
5. Avoid dynamic SQL when possible

Performance Tips

✅ Do's

• Add WHERE clauses to filter data early
• Use TOP or LIMIT for large result sets
• Set appropriate timeouts (30-60 seconds)
• Test queries in SQL Server Management Studio first
• Add indexes on frequently joined/filtered columns
• Specify only needed columns (avoid SELECT *)
• Use EXISTS instead of COUNT(*) > 0 for existence checks

❌ Don'ts

• Don't run queries without timeout on production
• Don't fetch entire tables without filters
• Don't use in high-frequency loops
• Don't ignore performance warnings
• Don't use SELECT * when you only need specific columns
• Don't execute without testing first

Optimization Examples

// ❌ BAD - No filter, no limit
var result = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT * FROM Loans'
  }
});

// ✅ GOOD - Filtered, limited, specific columns
var result = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT TOP 100 LoanAccountId, PrincipalAmount, Status ' +
         'FROM Loans ' +
         'WHERE Status = \'ACTIVE\' ' +
         'AND CreatedDate >= DATEADD(month, -1, GETDATE())',
    timeout: 30000
  }
});

Common Error Codes

Error Pattern	Cause	Resolution
Timeout expired	Query taking too long	Optimize SQL, add indexes, increase timeout
Invalid column name	Column doesn't exist	Verify column name in database
Invalid object name	Table doesn't exist	Check table name spelling
Syntax error	Invalid SQL	Review SQL syntax
Permission denied	Insufficient permissions	Check database user permissions

Related Commands

• GetFullTableQuery - For custom BPM tables
• RetrieveLoanListQuery - Type-safe loan queries
• RetrieveDepositListQuery - Type-safe deposit queries
• QueryTableCommand - Parameterized queries

API Endpoint

Method: POST
URL: /api/bpm/execute-command

Request Body:

{
  "commandName": "DoSqlQuery",
  "parameters": {
    "Data": {
      "sql": "SELECT * FROM Loans WHERE Status = 'ACTIVE'",
      "timeout": 30000
    }
  },
  "context": {}
}

Response:

{
  "isSuccessful": true,
  "message": "SQL query executed successfully.",
  "data": [
    {
      "id": 1,
      "loanAccountId": "LN-001",
      "principalAmount": 50000
    }
  ]
}

Best Practices

✅ Do

• Validate all inputs before building SQL
• Test queries in SQL tools first
• Use appropriate timeouts
• Add WHERE clauses to filter data
• Use TOP/LIMIT for large datasets
• Handle empty result sets
• Log query execution for auditing

❌ Don't

• Don't allow unsanitized user input in SQL
• Don't execute INSERT/UPDATE/DELETE (use specific commands)
• Don't run without timeout on production
• Don't ignore error responses
• Don't fetch entire tables
• Don't use in tight loops

Complete Example: Multi-Step Analysis

// Complete workflow: Analyze customer eligibility with complex queries

// Step 1: Get customer summary
var customerSummaryResult = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT C.*, ' +
         'COUNT(DISTINCT L.Id) AS TotalLoans, ' +
         'SUM(CASE WHEN L.Status = \'ACTIVE\' THEN 1 ELSE 0 END) AS ActiveLoans, ' +
         'SUM(L.Balance) AS TotalDebt ' +
         'FROM Customers C ' +
         'LEFT JOIN Loans L ON C.Id = L.CustomerId ' +
         'WHERE C.Id = ' + context.customerId + ' ' +
         'GROUP BY C.Id, C.FirstName, C.LastName, C.CreditScore, C.MonthlyIncome',
    timeout: 30000
  }
});

if (!customerSummaryResult.isSuccessful || customerSummaryResult.data.length === 0) {
  throw new Error('Customer not found');
}

var customer = customerSummaryResult.data[0];

// Step 2: Check payment history
var paymentHistoryResult = doCmd('DoSqlQuery', {
  Data: {
    sql: 'SELECT ' +
         'COUNT(*) AS TotalPayments, ' +
         'SUM(CASE WHEN TransactionDate > DueDate THEN 1 ELSE 0 END) AS LatePayments, ' +
         'AVG(DATEDIFF(day, DueDate, TransactionDate)) AS AvgDelayDays ' +
         'FROM LoanTransactions LT ' +
         'INNER JOIN Loans L ON LT.LoanId = L.Id ' +
         'WHERE L.CustomerId = ' + context.customerId + ' ' +
         'AND LT.TransactionType = \'REPAYMENT\' ' +
         'AND LT.TransactionDate >= DATEADD(month, -12, GETDATE())',
    timeout: 30000
  }
});

var paymentHistory = paymentHistoryResult.data[0];

// Step 3: Calculate eligibility score
var eligibilityScore = 0;

if (customer.creditScore >= 700) eligibilityScore += 30;
if (customer.activeLoans < 3) eligibilityScore += 25;
if (customer.totalDebt < customer.monthlyIncome * 3) eligibilityScore += 25;
if (paymentHistory && paymentHistory.latePayments === 0) eligibilityScore += 20;

// Step 4: Return comprehensive result
return {
  success: true,
  eligible: eligibilityScore >= 70,
  eligibilityScore: eligibilityScore,
  customer: {
    name: customer.firstName + ' ' + customer.lastName,
    creditScore: customer.creditScore,
    activeLoans: customer.activeLoans,
    totalDebt: customer.totalDebt
  },
  paymentHistory: paymentHistory
};

Version History

• v1.0: Initial release
• v1.1: Added timeout parameter
• v1.2: Improved error messages
• v1.3: Enhanced security validation