pb when passing parameters from a form

[SebastiendOrnano]

It is not a SQLPage problem, but rather a misunderstanding of how some parameters are stored inside a query:
I am working on an address book with 4 tables:

prm1: persons

prm2: companies

prm2_sub: dept of companies

prm2_sub_employees: employees of dept of companies

When I create an employee, I create a row in prm1 and a row in prm2_sub_employee (with prm1_id and prm2_sub_id).

When I create an employee, I want to check if there is not yet any record of this person in prm1, so before inserting a record, I do a check.

SET redirect_link1 = (
    SELECT
      CASE
           WHEN EXISTS (
                SELECT 1 
                FROM prm1
                WHERE CONCAT(prm1_first_name, prm1_last_name) = CONCAT(:prm1_first_name, :prm1_last_name)
            ) 
            THEN '/d_prm2_sub_employees/prm2_sub_employee_main_create_alert1_0.sql?prm2_sub_id=' || :prm2_sub_id
           WHEN EXISTS (
                SELECT 1 
                FROM prm1
                WHERE prm1_email = :prm1_email
            ) 
            THEN '/d_prm2_sub_employees/prm2_sub_employee_main_create_alert2_0.sql?prm2_sub_id=' || :prm2_sub_id
           ELSE ''
      END
);

SELECT 'redirect' AS component,
       $redirect_link1 AS link
WHERE $redirect_link1 != '' OR $redirect1 IS NOT NULL;

My trouble is that in this query the :prm2_sub_id is taking the value of :prm1_email.
I think there is a limit on the number of parameters that can be used in the same query (if I do only one test, it works).

So how can I write this test so I can keep the right value for prm2_sub_id?

[lovasoa]

Hi! I think you forgot to paste something :)

[SebastiendOrnano]

I found a solution with two steps to define the redirect link :

SET redirect_link1 = (
    SELECT
      CASE
           WHEN EXISTS (
                SELECT 1 
                FROM prm1
                WHERE CONCAT (prm1_first_name,prm1_last_name) = CONCAT (:prm1_first_name,:prm1_last_name)
            ) 
            THEN '/d_prm2_sub_employees/prm2_sub_employee_main_create_alert1_0.sql'
            WHEN 
            EXISTS (
                SELECT 1 
                FROM prm1
                WHERE prm1_email= :prm1_email) 
            THEN '/d_prm2_sub_employees/prm2_sub_employee_main_create_alert2_0.sql'
            ELSE ''
        END
);

SET redirect_link2 =  CONCAT($redirect_link1,'?prm2_sub_id=',:prm2_sub_id) ;

SELECT 'redirect' AS component,
$redirect_link2 AS link
WHERE $redirect_link2 != '' OR $redirect2 IS NOT NULL;

[lovasoa]

Hi!
I may be misunderstanding your requirements, but it looks like you're making things more complex than they need to be. You can insert into multiple tables and handle conflicts natively in SQL with this approach. Here's an example using PostgreSQL:

WITH existing_person AS (
    SELECT prm1_id 
    FROM prm1 
    WHERE CONCAT(prm1_first_name, prm1_last_name) = CONCAT(:prm1_first_name, :prm1_last_name)
       OR prm1_email = :prm1_email
),
insert_person AS (
    INSERT INTO prm1 (prm1_first_name, prm1_last_name, prm1_email)
    SELECT :prm1_first_name, :prm1_last_name, :prm1_email
    WHERE NOT EXISTS (SELECT 1 FROM existing_person)
    RETURNING prm1_id
),
final_person AS (
    SELECT prm1_id FROM existing_person
    UNION ALL
    SELECT prm1_id FROM insert_person
)
INSERT INTO prm2_sub_employees (prm1_id, prm2_sub_id)
SELECT prm1_id, :prm2_sub_id FROM final_person
RETURNING prm1_id;

Why This is Better

Using CTEs keeps everything in one query, so you don't have to juggle multiple statements or worry about mixing up parameters like :prm2_sub_id.

Step-by-Step Clarity:

Check: First, it checks if the person exists (using name or email).

Insert: If not, it inserts the person.

Combine: Then, it gathers the correct ID from either case and uses it to insert the employee record.

This is cleaner, less error prone, more maintainable, faster, and immune to race conditions.

Hope this helps!

[SebastiendOrnano]

as usual thanks ! (I am using sqlpage to learn sql and your explanations are always enlightening. NB : you shoud consider to write a cookbook : "learning SQL with SQLPage !")

In my original code, the aim was to send alerts to avoid to create a person with an already existing name or email (and if the redirect_link is null or empty, the insert into... start) and I needed the parameter :prm2_sub_id to have a correct path to return to the form.

But your code is very clean and it gives me the right way to rewrite my file. (I am not yet familiar with the WITH and UNION but there I found a good example to understant how to use it)

the original complete code was :

-- vérif si le nom d'utilisateur n'est pas déjà utilisé 
-- vérif si l'email' existe déjà

SET redirect_link1 = (
    SELECT
      CASE
           WHEN EXISTS (
                SELECT 1 
                FROM prm1
                WHERE CONCAT (prm1_first_name,prm1_last_name) = CONCAT (:prm1_first_name,:prm1_last_name)
            ) 
            THEN '/d_prm2_sub_employees/prm2_sub_employee_main_create_alert1_0.sql'
            WHEN 
            EXISTS (
                SELECT 1 
                FROM prm1
                WHERE prm1_email= :prm1_email) 
            THEN '/d_prm2_sub_employees/prm2_sub_employee_main_create_alert2_0.sql'
            ELSE ''
        END
);

SET redirect_link2 =  CONCAT($redirect_link1,'?prm2_sub_id=',:prm2_sub_id) ;

SELECT 'redirect' AS component,
$redirect_link2 AS link
WHERE $redirect_link2 != '' OR $redirect2 IS NOT NULL;

set prm2_id = select prm2_id from prm2_sub where prm2_sub_id = $prm2_sub_id;

SET user_role = (
    SELECT user_role FROM users
    INNER JOIN sessions ON users.user_id = sessions.user_id
    WHERE sessions.session_token = sqlpage.cookie('session_token')
); 

SET redirect_link =
CASE WHEN $user_role = 'supervisor' 
THEN '/d_prm2_sub_employees/prm2_sub_employee_main_display_4.sql?prm2_sub_id='||$prm2_sub_id||'&prm2_id='||$prm2_id
ELSE '/d_prm2_sub_employees/prm2_sub_employee_main_display_3.sql?prm2_sub_id='||$prm2_sub_id||'&prm2_id='||$prm2_id
END;


INSERT INTO prm1 
(
prm1_gender, 
prm1_first_name, 
prm1_last_name, 
prm1_email, 
prm1_date_birthday, 
prm1_phone, 
prm1_category, 
prm1_function,
prm1_status,
prm1_cv_short, 
prm1_cv_long
)
    
SELECT 
:prm1_gender, 
:prm1_first_name, 
:prm1_last_name, 
:prm1_email, 
:prm1_date_birthday, 
:prm1_phone,
:prm1_category,
:prm1_function,
'active', 
:prm1_cv_short, 
:prm1_cv_long
    
WHERE :prm1_email IS NOT NULL;

SET prm1_id = SELECT prm1_id FROM prm1 WHERE prm1_email = :prm1_email;

INSERT INTO prm2_sub_employees
(
prm1_id,
prm2_id, 
prm2_sub_id, 
employee_category, 
employee_function, 
employee_title, 
employee_since, 
employee_status
)
    
SELECT 
$prm1_id,
$prm2_id, 
$prm2_sub_id, 
:employee_category, 
:employee_function, 
:employee_title, 
:employee_since, 
'active';

UPDATE prm2_sub
SET 
prm2_sub_nb_employees = prm2_sub_nb_employees+1,
modified_at=CURRENT_TIMESTAMP
WHERE prm2_sub_id=$prm2_sub_id
 
RETURNING  'redirect' AS component,
$redirect_link AS link;