Stored Procedure y PHP

Bases de datos

No he utilizado de forma profunda demasiados motores de bases de datos. Sí he probado con mayor o menor intensidad algunos: desde DBase III, todas las versiones de Access (aunque haya quien no los considerará bases de datos), Informix, las famosas Paradox usadas en Delphi y algún otro. Sin embargo y sin dudarlo, soy un amante (y porqué no decirlo, experto) de Microsoft SQL Server (todas desde su versión 7.0), su implantación en entornos de desarrollo es fantástico y su rendimiento (aun sin compararlo con otros motores) siempre me ha parecido fabuloso. Y por supuesto sus herramientas, sobre todo el análisis de consultas, analizador de rendimiento y otros te permiten gestionar de forma eficaz tus bases de datos. Por poner una pega, a partir de la versión Microsoft SQL Server 2005, la han fastidiado con el Administrador Corporativo... esperemos lo vayan arreglando. Por último, nunca he trasteado con Oracle, siempre me ha dado pereza y salvo satisfacer la curiosidad de comparar "paradigmas" no me ofrece ninguna ventaja. Tiempo habrá.

MySql

Apenas he empezado a sobrevolar MySql. Hace tiempo trastee un poco con él, pero me dejó bastante indiferente, era pobre y raquítico. Sin embargo ahora (MySQL Server 5.0) sí me parece que haya crecido, de momento sigue siendo libre y eso ya lo hace atractivo (hace poco que lo ha comprado Sun Microsystems). Aun admitiendo que no podré ser del todo objetivo, intentaré evaluarlo sin que interfiera mi desconocimiento sobre él (pues nuestra mente siempre es perezosa al cambio) y mi gran aprecio por Microsoft SQL Server.

Así, de forma impulsiva, destacar algunos inconvenientes de MySql más o menos relevantes:

• La declaración de variables locales, parámetros de procedimientos/funciones y su uso en la sentencia SELECT es más que lioso y confuso además de limitado (fantástico en MSQL Server). Indagaremos al respecto.
• Los procedimientos parecen no poder devolver un código de retorno (su sintaxis rechaza explícitamente el uso de RETURN dentro de un procedimiento), es un fastidio, muchísimos procedimientos (por no decir que todos) deben devolver un código de retorno, aunque sólo sea para decir si la cosa ha ido bien o mal. Nos obliga entonces a devolver un recordset con el coste que esto supone.
• La escritura de las sentencias sobre un archivo de código SQL no lo veo claro, el cambio de delimitador (con DELIMITER) me parece feo y tosco. Seguiremos indagando...
• La definición de alias para las columnas y campos que obliga a usar el operador AS es prehistórico y por supuesto poco claro.
• ¿Cómo que hay problemas en manejar horas con precisión de milisegundos?, no me parece admisible. En un segundo se pueden crear muchos registros. Para una única tabla que posea un campo de autoincremento podemos ordenar en el tiempo los registros, pero con varias tablas que registren eventos en el tiempo lo vamos a tener menos claro... no parece admisible.

En fin, seguiremos analizándolo, pero en cualquier caso, tiene buena pinta.

MySql, PHP y los Stored Procedures

La primera patada en la frente. Estaba yo tan contento haciendo unas cuentas pruebas con MySql y ya tenía listos mis procedimientos almacenados (siempre, siempre, salvo extrañísimas excepciones hay que encapsular nuestra gestión de base de datos mediante procedimientos almacenados; otras alternativas "parecerán" más productivas [como usar recordset actualizables] pero en realidad no lo son) y al realizar las llamadas desde PHP me fijo que no funciona una llamada a $result->close() que libere los recursos de la consulta para poder ejecutar otra posterior.

Concretamente, no funciona:

// Inicialización conexión con base de datos:
$this->cnx = new mysqli(
	MSPW_SITE_DB_Host,
	MSPW_SITE_DB_User,
	MSPW_SITE_DB_Pass,
	MSPW_SITE_DB_Data
);

// Preparamos una consulta:
$q1 = $this->cnx->query( 'CALL cmp_node_list( \'pedro\', NULL )' );
// Recuperamos datos:
$r1 = $q1->fetch_assoc();
// Liberamos recursos:
$q1->close();

// Hasta aquí todo correcto.

// Preparamos otra consulta:
$q2 = $this->cnx->query( 'CALL cmp_node_list( \'pedro\', NULL )' );
// ¡Se produce un error!
printf( "ERROR { %s }\n", $this->cnx->error );
/*
	ERROR { Commands out of sync; you can't run this command now }
*/

¿Cual es el problema?

Aparentemente se han hecho las cosas tal y como pone en el manual de PHP, buscamos y rebuscamos y no encontramos nada. ¿Que ocurre?, ¿porqué no se liberan los recursos de la sentencia anterior?.

No hay ningún problema sin embargo, si en lugar de procedimientos almacenados usamos consultas directamente (como un SELECT).

Conjunto de resultados de una llamada a un procedimiento almacenado en MySql

Harto de mirar en PHP, nos vamos a la documentación de MySql y leemos con sorpresa que el problema efectivamente está en MySql (aunque la API de PHP mysqli ya que lo sabe y que es específica de MySql podría haberlo dejado resuelto...).

En el punto 21.2.6. CALL Statement Syntax de la documentación de MySql leemos:

If you write C programs that use the CALL SQL statement to execute stored procedures that produce result sets, you must set the CLIENT_MULTI_RESULTS flag, either explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS when you call mysql_real_connect(). This is because each such stored procedure produces multiple results: the result sets returned by statements executed within the procedure, as well as a result to indicate the call status. To process the result of a CALL statement, use a loop that calls mysql_next_result() to determine whether there are more results. For an example, see Section 26.2.9, "C API Handling of Multiple Statement Execution".

Es decir, aunque no queramos leer los resultados, aunque no los pidamos (si, sin hacer fetch_assoc también falla) estamos obligados a recorrer una colección de resultados. Sí, no es mucho, pero estamos obligados a realizar un par de llamadas más.

Resolviendo el problema

Bien, ahora que lo sabemos, llamaremos directamente a la función multi_query y haremos siempre tantas llamadas a store_result como nos indique next_result.

Facilitando la llamada a Stored Procedures

En determinadas situaciones es preciso optimizar las llamadas a la base de datos, bien porque el conjunto de resultados es muy grande (y queremos procesarlo lo más rápida y directamente posible) o bien porque el número de llamadas es muy elevada. En el primer caso utilizaremos directamente el canal de datos que venga del motor de bases de datos y en el segundo precompilaremos las sentencias (sí, aun cuando la sentencia sea un procedimiento almacenado) parametrizándola (en mysqli mediante statements usando stmt_init y otros).

Sin embargo, en la mayoría de los casos, nuestras llamadas a procedimientos almacenados no son frecuentes (como para precompilar las sentencias cada vez) y no devuelven un conjunto grande de resultados (muchas veces ninguno o sólo un registro). Por tanto, parece que lo sensato es trivializar la programación y utilizar los métodos anteriores sólo cuando sea preciso.

Una sencilla función en PHP nos permitirá obtener todos los conjuntos de resultados de forma cómoda. Tiene el inconveniente de hacer un copiado intermedio de los datos (¡y duplicar para cada registro los nombres de los campos!), pero en la mayoría de los casos el coste adicional será despreciable consiguiendo una productividad mucho mayor, tanto en el desarrollo como en el mantenimiento del código.

/**************************************************************************************************************
*	Devuelve un array con array's asociativos conteniendo todos los datos.
*		ResultSet {
*			DataSet1 {
*				Row1 {
*					Columna1 => Dato1,
*					Columna2 => Dato2,
*						...
*				}
*				Row2 {
*					Columna1 => Dato1,
*					Columna2 => Dato2,
*						...
*				}
*				...
*			}
*			DataSet2 {
*				...
*			}
*			...
*		}
*/
private function rs_query( $query ) {
	// nuestro ResultSet
	$RS = array();
	// ¿Se ejecutó correctamente?
	if( $this->cnx->multi_query( $query ) ) {
		do {
			// ¿Hay datos?
			if( $result = $this->cnx->store_result() ) {
				// Un DataSet
				$DS = array();
				// Copiamos datos:
				while( $row = $result->fetch_assoc() ) {
					$DS[] = $row;
				}
				// Añadimos el DataSet al ResultSet
				$RS[] = $DS;
				// Liberamos resultado intermedio:
				$result->close();
			}
		// ¿Quedan datos?
		} while( $this->cnx->next_result() );
	}
	// Devolvemos el ResultSet con los DataSet
	return $RS;
}

---

Opinado el 14/09/09 18:03, valoración
    

---

Opinado el 15/04/10 21:13, valoración
    er

---

Opinado el 01/10/10 03:35, valoración
    gracias por tu apoto me ayudo

---

Opinado el 22/11/11 20:53, valoración
    2 años después este artículo aún es vigente. Graci

---

Opinado el 20/01/12 23:26, valoración
    tengo el mismo problema pero con mysql sin "i"

---

Opinado el 21/01/12 16:16, valoración
    Hola amigos, excelente artículo. A continuación adjunto un post que explica cómo implementar una tienda virtual MySQL con stored procedures. Ánimo y saludos a todos. http://programarivm.com/2012/01/comercio-electronico-de-la-uoc-base-de-datos-de-una-tienda-virtual-con-stored-procedures-y-stored-functions-de-mysql/

---

Opinado el 22/02/13 00:00, valoración
    Excelente y gracias por tu aportación

---

Opinado el 04/06/13 16:46, valoración
    j

---

Opinado el 11/06/13 18:13, valoración
    

---

Opinado el 14/02/14 20:35, valoración
    

---

Opinado el 12/05/14 23:08, valoración
    bien

---

Opinado el 03/01/16 20:58, valoración
    excelente!

---

Opinado el 18/10/21 21:53, valoración
    Valiste. Pégate un balazo y pider perdón a la vida