Foreach Loops

[ArianYN]

I was wondering if SQLPage supported for or foreach loops to iterate through arrays and such?
If not, is there a workaround?

[lovasoa]

Hi !

SQL, including SQLPage, does not support for or foreach loops like imperative languages such as Python or JavaScript. SQL is a declarative language for querying and manipulating data in a set-based manner, differing from the procedural approach of other languages. Despite this, SQL is powerful and concise, as it is Turing complete, meaning any data manipulation possible with foreach can be done in SQL.

To translate foreach loops to SQL, you have to rely SQL's ability to perform operations on multiple rows simultaneously, utilize joins to combine related data sets, and employ subqueries or Common Table Expressions (CTEs) to simplify complex queries. Additionally, window functions can help apply calculations across sets of rows in a declarative manner.

Tell us more about what you are trying to do, and we'll help you realize it in SQL.

[ArianYN]

Okay, the problem in my case is that I am not using data from a table. I am running commands to get an either 'Active' or 'Inactive' Status of a service. I then create a datagrid for this 1 service.

I have 5 services that i need to do this with, meaning this has to be done 5 times. That becomes quite a long piece of code, so i was just wondering if it was possible to make some kind of loop, in which i can run through lets say an array containing my 5 service-names so i only have to write the entire thing once, as it runs through variables.

[lovasoa]

By running commannds, do you mean you are using sqlpage.exec ?

If so, then you can still use sqlpage.exec as a part of a larger query, using VALUES if you need to hardcode your service names:

select  'datagrid' as component;

SELECT service.column1 as title, sqlpage.exec('./check_service_status.sh', service.column1) as description
from (VALUES ('service 1'), ('service 2')) service;

[lovasoa]

Or, if you are running a a complicated script anyway, you could move all the iteration logic to it, and just use the dynamic component to render the results:

select  'datagrid' as component;
SELECT 'dynamic' as component, sqlpage.exec('./check_service_status.py') as properties;

and in check_service_status.py :

import json, sys

json.dump([
    {"title": service, "description": "Active" if check_service(service) else "Inactive"}
    for service in ["service1", "service2", "service3", "service4", "service5"]
], sys.stdout)

[lovasoa]

Related discussions:

• run_sql for each row #328
• Loops? #306
• SQL-only Programming and Templating Techniques #207
• General thoughts about the project and code readability #407

[ArianYN]

Okay thanks. I will try these

[lovasoa]

Where do you currently get your list of services from, and how do you check their status ?