Problemas para crear tabla temporal en MySQL

Sergio_Ciordia · August 29, 2020, 9:25am

Buenos días,

necesito crear una tabla temporal en mi base de datos ‘prs1’ en MySQL. He probado a crear este método llamado ‘BDTablaTemporal_Crear’:

Dim stmSQL As String 

stmSQL = "Create Temporary Table If Not Exists prs1.tmp_Inventario(ean13 VarChar(13))"

Try
  BDProductos.ExecuteSQL( stmSQL )
  Return True
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
  Return False
End Try

El método anterior se invoca desde este otro:

BDIniciarTransaccionProd

If Not BDTablaTemporal_Crear Then
MsgBox("Se ha producido un error al crear la tabla temporal: " + BDProductos.ErrorMessage)
BDCancelarTransaccionProd //Cancelamos la transacción
Return
End If

BDFinalizarTransaccionProd

MsgBox("La tabla temporal ha sido creada con éxito")

El caso es que cuando ejecuto el proyecto nada falla pero si compruebo desde la base de datos vía Valentina Studio si se ha creado la tabla, me dice que NO EXISTE.

Es curioso porque desde la consola de VStudio ejecuto la sentencia “Create Temporary Table If Not Exists prs1.tmp_Inventario(ean13 VarChar(13))” y funciona perfectamente.

Lo siguiente que he hecho ha sido crear un ‘stored procedure’ (llamado CrearTablaTemporal) directamente en la base de datos para borrar la tabla temporal si existe y sino que la cree pero, de nuevo funciona perfectamente desde VStudio y cuando lo corro desde Xojo parece que no da error pero si consulto la tabla NO EXISTE. Utilizo este método para llamar al procedimiento :

Dim stmSQL As String 

stmSQL = "CALL CrearTablaTemporal('tmp_Inventario')"

Try
BDProductos.ExecuteSQL( stmSQL )
Return True
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
Return False
End Try

¿A alguien se le ocurre qué puede estar pasando?. ¿Se pueden crear tablas temporales desde Xojo? Estoy utilizando la última release 2020r1. Sé que las tablas temporales sólo pueden verse desde el usuario que las crea pero he utilizado siempre el mismo usuario (para crear la tabla y consultarla).

Muchas gracias.
Sergio

Mauricio_Pulla · September 2, 2020, 3:49pm

Hola Sergio.

Las tablas temporales son visibles desde la conexión que las creo.

Cuando ejecutas en valentina studio es otra conexión y no la vas a ver.

Otra cosa si estas abriendo la conexión, creando la tabla y cerrando la conexión la tabla temporal se cierra.

Saludos.
Mauricio

Sergio_Ciordia · September 3, 2020, 7:04pm

Muchas gracias por tu respuesta @Mauricio_Pulla.

Por lo que había leído, las tablas temporales solo son visibles por el usuario que las crea y aunque las creo desde Xojo pensaba que si me conectaba simultáneamente a la base de datos desde Valentina Studio pero con el mismo usuario, podría ver la tabla. Ya me he dado cuenta de que no es así

De todas formas a ver si me puedes ayudar o al menos asesorarme. Mi idea era, tengo un listbox con valores, invoco el método que crea la tabla temporal, inserto los valores en la tabla, se hace una consulta y me devuelve unos valores que debo recuperar de la base de datos e importar en un listbox y por último elimino la tabla temporal.

Lo que me comentas es que… “si estas abriendo la conexión, creando la tabla y cerrando la conexión la tabla temporal se cierra.”. Vale, entonces ¿cómo hago para mantener la tabla abierta, insertar los valores y recuperar la consulta?. Me explico, mi aplicación tiene la conexión abierta de forma permanente a la base de datos todo el rato hasta que cierro la aplicación. En mi método de arriba (2ª caja de código), inicio la transacción, ejecuto la sentencia que crea la tabla temporal y finalizo la transacción. No cierro la conexión a la base datos (creo) entonces no sé cómo mantener la tabla.

¿Podrías echarme un cable?. Estoy atascado en este punto

Muchas gracias.

Un saludo,
Sergio

Sergio_Ciordia · September 8, 2020, 11:03am

Buenos días,

por si alguien está tan perdido como yo o le surge la necesidad de utilizar una tabla temporal, os cuento cómo he resuelto finalmente mi problema. El caso es que como me comentó @Mauricio_Pulla si se cierra la conexión, la tabla se pierde. Lo que he hecho ha sido crear un método como este:

// Empezamos la transacción

BBDD.SQLExecute(“START TRANSACTION”)

// Borramos la tabla temporal si existe y sino la creamos.

Debemos utilizar estas dos sentencias SQL:

‘DROP temporary table if exists @TablaTemporal’

‘CREATE temporary table if NOT exists @TablaTemporal(field1 VarChar(13))’

// OPCIONAL: comprobar si existe la tabla temporal creada (ver más abajo)

// Insertamos los valores que queramos en la tabla temporal

Dim stmSQL As String 
BBDD.SQLExecute("START TRANSACTION")

stmSQL = "INSERT INTO @TablaTemporal(field1)"
stmSQL = stmSQL + " VALUES(?)"

Dim ps As MySQLPreparedStatement = BBDD.Prepare(stmSQL)
If BBDD.Error = True Then MsgBox BBDD.ErrorMessage
ps.BindType( 0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind( 0, field1)
ps.SQLExecute

BBDD.Commit
Return Not BBDD.Error

//Recuperamos los resultados de la consulta frente a la tabla temporal

Dim stmSQL As String = “SELECT * FROM @TablaTemporal”
Return BBDD.SQLSelect( stmSQL )

//Eliminamos la tabla temporal

‘DROP temporary table if exists @TablaTemporal’

// Terminamos la transacción

BBDD.CommitTransaction

Para comprobar y verificar si existe la tabla temporal, he creado un “stored procedure” en la base de datos de MySQL que tiene este código:

CREATE PROCEDURE check_table_exists(table_name VARCHAR(100))
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02’ SET @err = 1;
SET @err = 0;
SET @table_name = table_name;
SET @sql_query = CONCAT('SELECT 1 FROM ',@table_name);
PREPARE stmt1 FROM @sql_query;
IF (@err = 1) THEN
SET @table_exists = 0;
ELSE
SET @table_exists = 1;
DEALLOCATE PREPARE stmt1;
END IF;
END

Entonces desde Xojo solo tenemos que llamar al procedimiento con este código:

Dim stmSQL As String = “CALL check_table_exists(‘@TablaTemporal’)”
BBDD.ExecuteSQL(stmSQL)

Y ejecutar esta consulta SQL:

Dim stmSQL As String = “SELECT @table_exists”
Return BBDD.SQLSelect(stmSQL)

Si el resultado es ‘1’, la tabla se ha creado y existe; y si es ‘0’, la tabla no existe.

Espero que esto pueda ayudar a alguien… en mi caso fue por puro autoaprendizaje,

Un saludo,
Sergio