Entendiendo el Prepared Statement

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.

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í.

   

   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:

   

   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”.

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. 🙋🏾‍♂️🚀💚