1

Entendiendo el Prepared Statement

Carlos
monoald
31617

Si recién estas empezando con las bases de datos y SQL, se que la clase anterior puede llegar a ser un poco confusa (para mi lo fue). Así que me puse a jugar con las consultas para ver que estaba pasando realmente en esa consulta toda larga y extraña.

Veremos la consulta en su estado salvaje:

USE metro_cdmx;

DELIMITER //

CREATEPROCEDURE get_line_stations (
  IN line_name VARCHAR(15)
)
BEGIN-- Esta es una forma de guardar valores de una consulta -- dentro de una variableDECLARE line_id BIGINT(20);

  SELECTid 
  INTO line_id
  FROM`lines`
  WHEREname = line_name;

  -- Esta es otra forma de guardar valores de una consulta-- dentro de una variableSET @sql = CONCAT("
    SELECT
      `lines_stations`.`id` AS relation_id,
      `lines`.`name` AS line_name,
      `stations`.`name` AS station_name
    FROM `lines_stations`
    INNER JOIN `stations`
    ON `stations`.`id` = `lines_stations`.`station_id`
    INNER JOIN `lines`
    ON `lines`.`id` = `lines_stations`.`line_id`
    WHERE `lines_stations`.`line_id` =", line_id);

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATEPREPARE stmt;
END//

DELIMITER ;

Ahora vamos a intentar controlar y capturar, la consulta salvaje, des-estructurando esta en pequeñas partes. Comencemos!

Primero necesitamos saber cual es el objetivo del prepared statement, lo que queremos lograr es que al llamar a la “función” le mandemos un nombre de una linea, la que queramos, y como resultado queremos obtener todas las estaciones que pertenecen a la linea.

Crear una variable con DECLARE

Con DECLARE ”declaramos” una variable, después del statement ingresamos el nombre de la variable y luego el tipo de esta.

DECLARE line_id BIGINT(20);

podemos ver un equivalente en diferentes lenguajes de programación, como:

Python

line_id = ""

JavaScript

let line_id;

Java

String line_id = ""

Dale un valor a la variable

Lo que sigue después de crear la variable es asignarle un valor para poder usarla en siguientes pasos.

SELECTid 
INTO line_id
FROM`lines`
WHEREname = line_name;

Con la anterior consulta lo que hacemos es:

  • Seleccionar el campo “id”.

    SELECTid
    
  • de la tabla “lines”.

    FROM `lines`
    
    
  • Donde el “name” sea igual al input que pedimos, llamado “line_name.

    WHEREname = line_name;
    
    
  • Asignar el resultado de la consulta a la variable “line_id”.

    INTO line_id
    
    

Listo, nuestra variable ya tiene un valor. En el caso de enviar el nombre Linea 9 en la consulta la variable id tendrá el valor de “9” que es el valor de la Linea seleccionada.

Screenshot from 2022-11-17 19-41-04.png

Creando la lógica de la prepared statement

Ahora crearemos la lógica, eso que le da el sentido de por que necesitamos el prepared statement.

SET @sql = CONCAT("
    SELECT
      `lines_stations`.`id` AS relation_id,
      `lines`.`name` AS line_name,
      `stations`.`name` AS station_name
    FROM `lines_stations`
    INNER JOIN `stations`
    ON `stations`.`id` = `lines_stations`.`station_id`
    INNER JOIN `lines`
    ON `lines`.`id` = `lines_stations`.`line_id`
    WHERE `lines_stations`.`line_id` =", line_id);

  • Con SET estamos creando una especie de “variable” que solo puede contener un string, el nombre de estas variables deben iniciar con un “@”.

    SET @sql = "";
    
    
  • La función CONCAT concatena strings esto puede ser un equivalente a “hola” + “mundo” que daría como resultado “holamundo” en lenguajes como JavaScript y Python.

    SET @sql = CONCAT("");
    
    
  • Ahora pediremos las columnas que nos interesan y les asignaremos un nuevo “nombre” en la tabla que nos presentara mariadb.

    SET @sql = CONCAT("
        SELECT
          `lines_stations`.`id` AS relation_id,
          `lines`.`name` AS line_name,
          `stations`.`name` AS station_name");
    
    
  • Pero los campos que pedimos vienen de diferentes tablas. Como puedo traerlas todas? Esto podemos resolverlo de manera muy fácil.

  1. Haremos un llamado a la tabla “pivote” (que contiene la relación entre las otras dos tablas). A esta tabla la llamaremos la tabla “legendaria”.

    SET @sql = CONCAT("
        SELECT
          `lines_stations`.`id` AS relation_id,
          `lines`.`name` AS line_name,
          `stations`.`name` AS station_name
        FROM `lines_stations`");
    
    
    1. Con INNER JOIN pedimos que se unan nuevas tablas a la tabla que ya pedimos, pero los nuevos elementos que traigamos no son absolutamente todos, debemos crear una condicional para traer los datos que necesitamos y no toda la tabla.
      En ambos casos pedimos traer los datos si y solo si el “id” de la nueva tabla coincide con la llave foránea correspondiente en la tabla “legendaria”.
    SET @sql = CONCAT("
        SELECT
          `lines_stations`.`id` AS relation_id,
          `lines`.`name` AS line_name,
          `stations`.`name` AS station_name
        FROM `lines_stations`
        INNER JOIN `stations`
        ON `stations`.`id` = `lines_stations`.`station_id`
        INNER JOIN `lines`
        ON `lines`.`id` = `lines_stations`.`line_id`"
    );
    
    

    Muy bien, vamos muy bien, si probamos la consulta “sola” de la siguiente manera:

    SELECT
          `lines_stations`.`id`AS relation_id,
          `lines`.`name`AS line_name,
          `stations`.`name`AS station_name
        FROM`lines_stations`
        INNERJOIN`stations`
        ON`stations`.`id` = `lines_stations`.`station_id`
        INNERJOIN`lines`
        ON`lines`.`id` = `lines_stations`.`line_id`
    
    

    Veremos que tenemos una tabla con los campos que pedimos y absolutamente todos los datos que tenían su id ligado en la tabla pivote. El resultado se ve así.

    Screenshot from 2022-11-17 20-06-22.png

    y la tabla sigue y sigue. Tiene 195 elementos!! Estamos avanzando muy bien, pero no necesitamos todos estos datos. Nuestro objetivo es obtener únicamente las estaciones que pertenecen a una determinada linea.

    1. Lo que necesitamos es un WHERE, para tener esa condicional que filtre los datos que queremos.
    SET @sql = CONCAT("
        SELECT
          `lines_stations`.`id` AS relation_id,
          `lines`.`name` AS line_name,
          `stations`.`name` AS station_name
        FROM `lines_stations`
        INNER JOIN `stations`
        ON `stations`.`id` = `lines_stations`.`station_id`
        INNER JOIN `lines`
        ON `lines`.`id` = `lines_stations`.`line_id`
        WHERE `lines_stations`.`line_id` =");
    
    

    Aquí tenemos un problema. Como hacemos para usar el “id” de la estación que necesitamos? Sabemos que este “id” esta almacenado en la variable “line_id”.

    1. Bueno, por este motivo usamos la función CONCAT para poder concatenar nuestra consulta con la variable que obtuvimos, de esta forma hacemos la consulta “dinámica”.
    SET @sql = CONCAT("
        SELECT
          `lines_stations`.`id` AS relation_id,
          `lines`.`name` AS line_name,
          `stations`.`name` AS station_name
        FROM `lines_stations`
        INNER JOIN `stations`
        ON `stations`.`id` = `lines_stations`.`station_id`
        INNER JOIN `lines`
        ON `lines`.`id` = `lines_stations`.`line_id`
        WHERE `lines_stations`.`line_id` =", line_id);
    
    

    Excelente!! Podemos hacer la prueba de la consulta, por fines prácticos añadiremos un “id” manualmente. Ahora pediremos todas las estaciones que son parte de la Linea 9.

    SELECT
          `lines_stations`.`id`AS relation_id,
          `lines`.`name`AS line_name,
          `stations`.`name`AS station_name
        FROM`lines_stations`
        INNERJOIN`stations`
        ON`stations`.`id` = `lines_stations`.`station_id`
        INNERJOIN`lines`
        ON`lines`.`id` = `lines_stations`.`line_id`
        WHERE`lines_stations`.`line_id` = 9;

    Este seria el resultado:

    Screenshot from 2022-11-17 20-16-51.png

    Esta vivoo!!! Nuestra consulta funciona, pero debemos terminar el prepared statement para usarla como una función.

  • PREPARE prepara el statement para ser usado y FROM indica de donde sacaremos el statement.

    BEGIN-- Esta es una forma de guardar valores de una consulta -- dentro de una variableDECLARE line_id BIGINT(20);
    
      SELECTid 
      INTO line_id
      FROM`lines`
      WHEREname = line_name;
    
      -- Esta es otra forma de guardar valores de una consulta-- dentro de una variableSET @sql = CONCAT("
        SELECT
          `lines_stations`.`id` AS relation_id,
          `lines`.`name` AS line_name,
          `stations`.`name` AS station_name
        FROM `lines_stations`
        INNER JOIN `stations`
        ON `stations`.`id` = `lines_stations`.`station_id`
        INNER JOIN `lines`
        ON `lines`.`id` = `lines_stations`.`line_id`
        WHERE `lines_stations`.`line_id` =", line_id);
    
      PREPARE stmt FROM @sql;
    END//
    
    
  • EXECUTE ejecuta el statement,

    BEGIN-- Esta es una forma de guardar valores de una consulta -- dentro de una variableDECLARE line_id BIGINT(20);
    
      SELECTid 
      INTO line_id
      FROM`lines`
      WHEREname = line_name;
    
      -- Esta es otra forma de guardar valores de una consulta-- dentro de una variableSET @sql = CONCAT("
        SELECT
          `lines_stations`.`id` AS relation_id,
          `lines`.`name` AS line_name,
          `stations`.`name` AS station_name
        FROM `lines_stations`
        INNER JOIN `stations`
        ON `stations`.`id` = `lines_stations`.`station_id`
        INNER JOIN `lines`
        ON `lines`.`id` = `lines_stations`.`line_id`
        WHERE `lines_stations`.`line_id` =", line_id);
    
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
    END//
    
    
  • DEALLOCATE ”libera” el statement de la memoria, esto es una buena practica, pues si tenemos muchos statements en memoria podemos tener un error.

    BEGIN-- Esta es una forma de guardar valores de una consulta -- dentro de una variableDECLARE line_id BIGINT(20);
    
      SELECTid 
      INTO line_id
      FROM`lines`
      WHEREname = line_name;
    
      -- Esta es otra forma de guardar valores de una consulta-- dentro de una variableSET @sql = CONCAT("
        SELECT
          `lines_stations`.`id` AS relation_id,
          `lines`.`name` AS line_name,
          `stations`.`name` AS station_name
        FROM `lines_stations`
        INNER JOIN `stations`
        ON `stations`.`id` = `lines_stations`.`station_id`
        INNER JOIN `lines`
        ON `lines`.`id` = `lines_stations`.`line_id`
        WHERE `lines_stations`.`line_id` =", line_id);
    
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATEPREPARE stmt;
    END//
    
    

Y ya estamos listos para usar nuestro prepared statement, ahora ejecutamos el archivo de la siguiente manera:

sudo mariadb -u  -p < 

Ya podemos entrar a mariadb y ejecutar el prepared statement. No olvide entrar a la base de datos primero.

USEmetro_cdmx;
CALL get_line_stations("Linea 4");

Ya podemos ver el resultado de la “función”.

Screenshot from 2022-11-17 20-33-41.png

Felicidades, lograste crear tu primer prepared statement y capturar este statement salvaje y añadirlo en tu equipo para poder usarlo en el futuro. De la mano del gran Retax Master pudimos crear esta maravillosa “función”. Espero que este articulo te haya ayudado a entender del todo esta clase, como a mi me ayudo al crearla.
.
Soy Carlos S. Aldazosa, me puedes encontrar en mis redes sociales como @monoald. Un abrazo y nunca pares de aprender. 🙋🏾‍♂️🚀💚

Escribe tu comentario
+ 2