SQL String

Overview

In Servoy, finding data using plain SQL strings offers direct and flexible access to database queries. This method allows developers to write SQL queries to load data into a foundset or retrieve results in a dataset. However, it is considered the least preferred approach due to several disadvantages, including potential database vendor dependency, performance issues, and security risks like SQL injection. While it provides control over the query, developers must handle these challenges carefully and prefer the Query Builder for more complex and secure operations.

Using plain SQL to find data in Servoy is a flexible but less preferred approach compared to using the Query Builder (QB). While it allows for direct SQL queries, there are several disadvantages and important considerations to keep in mind:

• Database Vendor Dependence: The syntax of SQL queries ca vary between different database vendors (e.g., MySQL, PostgreSQL, Oracle). This makes it harder to switch database vendors later since the SQL strings might need significant modifications.

• Performance Considerations: Using plain SQL to load data into a foundset might result in subselects, which can be slower compared to using the Query Builder, especially for large datasets.

• Lack of Table Filters: Table filters are not always enforced when using plain SQL to get a dataset. This can lead to unexpected results if the filters are bypassed.

• Limited Extensibility: Unlike the Query Builder, plain SQL does not have a programming API to further extend or modify the query dynamically within your code.

Security Warning: SQL Injection Risk

Concatenating user input directly into SQL strings is highly insecure and makes your application vulnerable to SQL injection attacks. Always use parameterized queries (using ? placeholders) to safely include user input in your SQL queries.

Example:

// Unsecure: Vulnerable to SQL injection
foundset.loadRecords('select productid from products where productname like ' + searchText);

// Secure: Using parameterized query to prevent SQL injection
foundset.loadRecords('select productid from products where productname like ? ', [searchText]);

Why Use Plain SQL in Servoy?

Despite its disadvantages, using plain SQL in Servoy can be beneficial in certain scenarios:

• Direct SQL Control: When you need precise control over the SQL query, such as complex joins, specific aggregate functions, or database-specific SQL features, plain SQL allows you to write the exact query you need.

• Legacy Code Integration: If you are working with an existing system where SQL queries are already defined, using plain SQL can be the quickest way to integrate that logic into Servoy without re-engineering the queries.

• Complex Queries: In cases where the Query Builder might not support the required SQL syntax or constructs, writing raw SQL ensures you can implement the exact logic needed, especially for advanced database operations like subqueries, unions, or custom functions.

• Performance Optimization: Experienced developers might use plain SQL for performance tuning in certain situations, where hand-optimizing the SQL query gives better results than the automatically generated SQL from the Query Builder.

• Learning and Debugging: For developers who are comfortable with SQL, writing raw SQL can sometimes make the logic of the data retrieval more transparent and easier to debug, especially in complex scenarios where understanding the exact SQL being executed is critical.

While using plain SQL offers flexibility, it requires careful consideration due to the potential downsides, such as increased maintenance costs and security risks. It is essential to weigh these factors against the benefits when deciding whether to use this approach in your Servoy applications.

Examples of Using Plain SQL

Complex SQL Search

This example demonstrates filtering products based on name and price range. It highlights how SQL allows for precise control in narrowing down results using multiple conditions. It's useful in scenarios where specific product attributes must be targeted, such as e-commerce platforms filtering items by name and price.

// Perform a complex search for products with multiple conditions
var searchText = '%Example%';
var minPrice = 10;
var maxPrice = 100;

foundset.loadRecords('SELECT productid FROM products WHERE productname LIKE ? AND price BETWEEN ? AND ?', [searchText, minPrice, maxPrice]);

This query searches for products with names matching a specified pattern and prices within a given range. The use of parameterized queries (?) ensures security against SQL injection.

Search with Dates

This example shows how to retrieve records within a specific date range, crucial for generating time-based reports or audits. It's commonly used in applications like sales reporting to filter orders or transactions over a defined period.

// Retrieve orders placed within a specific date range
var startDate = new Date(2023, 0, 1); // January 1, 2023
var endDate = new Date(2023, 11, 31); // December 31, 2023

foundset.loadRecords('SELECT orderid FROM orders WHERE orderdate BETWEEN ? AND ?', [startDate, endDate]);

This query fetches all orders placed within a specified date range. It uses the BETWEEN operator to include records where the orderdate falls between startDate and endDate.

Join Operation

This example illustrates the power of SQL joins by combining data from related tables to meet a business need, such as linking orders to customer details. It's essential in relational databases for retrieving related data from multiple tables in a single query.

// Retrieve orders along with customer information where the customer is from Berlin
foundset.loadRecords(`
    SELECT o.orderid 
    FROM orders o 
    INNER JOIN customers c ON o.customerid = c.customerid 
    WHERE c.city = ?
`, ['Berlin']);

This query performs a join between the orders and customers tables to find orders placed by customers in Berlin. The INNER JOIN ensures that only matching records are returned.

Complex Search with OR Conditions

This example showcases the use of the OR operator to search for records that meet any of multiple criteria. It's especially useful in scenarios where a broader search is required, such as targeting customers in various cities for marketing campaigns.

// Find customers with postal code 111 in Berlin or 222 in London
foundset.loadRecords(`
    SELECT customerid 
    FROM customers 
    WHERE (postalcode = ? AND city = ?) 
    OR (postalcode = ? AND city = ?)
`, ['111', 'Berlin', '222', 'London']);

This query uses multiple conditions with the OR operator to find customers in Berlin with postal code 111 or in London with postal code 222. It demonstrates how to combine conditions for complex searches.

Aggregated Query with HAVING Clause

This example demonstrates performing calculations on grouped data and filtering based on those calculations. It's crucial for generating summaries or reports that require aggregation, such as identifying high-value orders in sales data.

// Get order IDs and the total value of orders where the total exceeds $500
var dataset = databaseManager.getDataSetByQuery(`
    SELECT orderid, SUM(quantity * unitprice) AS total 
    FROM order_details 
    GROUP BY orderid 
    HAVING SUM(quantity * unitprice) > ?
`, [500]);

// Process the dataset
for (var i = 1; i <= dataset.getMaxRowIndex(); i++) {
    application.output('Order ID: ' + dataset.getValue(i, 1) + ', Total Value: ' + dataset.getValue(i, 2));
}

This query calculates the total value of each order by summing the product of quantity and unitprice. It then filters results to include only those orders where the total exceeds $500, using the HAVING clause.