11 Ejecutar sentencias

Las sentencias SQL son cadenas de texto, y por lo tanto, las almacenaremos en cadenas.

Por ejemplo:

SELECT * FROM usuario;
INSERT INTO usuario VALUES("Tulana", "tulana@dominio.com");
DROP usuario;

No podemos ejecutar las sentencias SQL directamente en nuestros programas. Cada sentencia requiere un proceso, que tendrá diferentes pasos, dependiendo del tipo de sentencia y de cómo decidamos trabajar con ella.

  1. El primer paso, es compilar la sentencia. Para ello disponemos de la familia de funciones sqlite3_prepare, aunque es recomendable usar las que terminan en v2, dependiendo de si usamos codificación UTF-8 o UTF-16, sqlite3_prepare_v2 o sqlite3_prepare16_v2, respectivamente. El resto de las funciones se mantienen por compatibilidad, y no deben usarse.
    El primer argumento para estas funciones siempre es un puntero a un objeto de conexión de base de datos válido.
    El segundo es una cadena con la consulta SQL a compilar. La cadena puede contener varias sentencias, separadas con punto y coma.
    El tercer parámetro es la longitud máxima de la cadena que contiene la sentencia, o -1 si la el final de la sentencia está marcado con un carácter nulo.
    Para cada sentencia compilada se necesita mantener un objeto de tipo sqlite3_stmt. Las funciones sqlite3_prepare devuelven inicializado el puntero al objeto referenciado por el cuarto argumento.
    El quinto parámetro se usa cuando la cadena contiene más de una sentencia, y lo veremos más adelante.
  2. Si la sentencia usa plantillas, el siguiente paso consiste en asignar valores a los parámetros. Si no usa plantillas este paso se omite. Estas asignaciones se hacen usando la familia de funciones sqlite3_bind*.
  3. A continuación se ejecuta la sentencia, usando la función sqlite3_step.
    Esta función sólo necesita un parámetro, un puntero a un objeto sqlite3_stmt que contiene los datos de una sentencia compilada.
  4. Opcionalmente podemos resetear la sentencia, y volver al punto 2. Esto lo haremos si los valores de la plantilla son distintos, por ejemplo, en el caso de sentencias INSERT.
  5. O podemos mantener los parámetros y volver al punto 3, por ejemplo, para obtener la siguiente fila de una sentencia SELECT.
  6. Finalmente, borramos la estructura asociada a la sentencia compilada, usando sqlite3_finalize.
    Esta función también necesita un único parámetro, un puntero a un puntero a un objeto sqlite3_stmt. Esta función libera el objeto asociado a una sentencia compilada.

Veamos diferentes ejemplos.

Sentencias que sólo se ejecutan una vez

En sentencias que sólo se ejecutarán una vez a lo largo del programa no necesitaremos crear bucles, ni almacenar la sentencia compilada una vez ejecutada. Usaremos una versión compacta del proceso. Por ejemplo, añadamos un índice a la tabla de usuarios:

CREATE INDEX nombre ON usuario (nombre);

    sqlite3_stmt *ppStmt;
    char consulta[64];
	...

    strcpy(consulta, "CREATE INDEX nombre ON usuario (nombre);");
    rc = sqlite3_prepare_v2(db, consulta, -1, &ppStmt, NULL);
    if( rc!=SQLITE_OK ){
        cout << "Error: " << sqlite3_errmsg(db) << endl;
    } else {
        if(SQLITE_DONE != sqlite3_step(ppStmt)) {
            cout << "Error: " << sqlite3_errmsg(db) << endl;
        }
        sqlite3_finalize(ppStmt);
    }

Como la sentencia sólo necesita un paso, ejecutaremos la función sqlite3_step una vez. El valor de retorno de la función será SQLITE_DONE si se ha ejecutado correctamente.

Sentencias con varias salidas, sin parámetros

En sentencias que generan varios resultados y para las que no se necesita especificar parámetros usaremos una variante del proceso anterior:

    sqlite3_stmt *ppStmt;
    char consulta[64];
	...

    strcpy(consulta, "SELECT rowid,nombre,email FROM usuario;");
    rc = sqlite3_prepare_v2(db, consulta, -1, &ppStmt, NULL);
    if( rc!=SQLITE_OK ){
        cout << "Error: " << sqlite3_errmsg(db) << endl;
    } else {
        while(SQLITE_ROW == sqlite3_step(ppStmt)) {
            cout << "ID:     " << sqlite3_column_int(ppStmt, 0) << endl;
            cout << "Nombre: " << sqlite3_column_text(ppStmt, 1) << endl;
            cout << "email:  " << sqlite3_column_text(ppStmt, 2) << endl;
        }
        sqlite3_finalize(ppStmt);
    }

En este caso, sentencia puede necesitar cero o más pasos, ejecutaremos la función sqlite3_step mientras el valor de retorno sea SQLITE_ROW, que indica que se ha leído una fila.

Usaremos las funciones de la familia sqlite3_column_* para leer el valor de cada columna de la fila leída. El primero parámetro es siempre un puntero a un objeto sqlite3_stmt con los datos de la sentencia actual. El segundo es el índice de la columna, empezando en cero.

Hay que usar la función adecuada dependiendo del tipo de la columna que estemos leyendo.

Sentencias con parámetros

Podemos usar la misma sentencia compilada con distintos valores para los literales que contenga, de modo que nos sirva en distintas circunstancias. Dado que compilar una sentencia requiere más recursos y tiempo que modificar sus parámetros, es más eficiente compilar una única vez la sentencia.

Hay varias formas de indicar los parámetros SQL, podemos optar por la que nos interese en cada ocasión.

  • Identificados mediante enteros. Hay dos opciones:
    • Una interrogación indica la posición de cada parámetro. Para hacer referencia a cada uno, se numeran de izquierda a derecha, empezando en 1:
      SELECT * FROM usuario WHERE nombre=? AND email=?;
      En este caso, el parámetro del nombre será en número 1 y el de email el número 2.
    • Una interrogación, seguida de un valor entero indica la posición de cada parámetro. Ahora los números identificadores de cada parámetro serán los indicados:
      SELECT * FROM usuario WHERE nombre=?2 AND email=?4;
      En este caso, el parámetro del nombre será en número 2 y el de email el número 4.
  • Identificados mediante literales. Los identifiadores literales empiezan con ":", "@" o "$", seguido de un literal alfabético:
    SELECT * FROM usuario WHERE nombre=:nombre AND email=:email;
    Para conseguir el número de cada parámetro usaremos la función sqlite3_bind_parameter_index.
    sqlite3_bind_text(ppStmt, sqlite3_bind_parameter_index(ppStmt, ":nombre"), "Fulano");

Nota: cuando se usan parámetros de tipo texto no se deben añadir las comillas delimitadoras, las funciones sqlite3_bind* insertan los parámetros adecuadamente, sean de texto o numéricos.

Veamos un ejemplo:

    sqlite3_stmt *ppStmt;
    char consulta[64];
    ...

    strcpy(consulta, "SELECT numero FROM telefono WHERE idusuario=@uid;");
    rc = sqlite3_prepare_v2(db, consulta, -1, &ppStmt, NULL);
    if( rc!=SQLITE_OK ){
        cout << "Error: " << sqlite3_errmsg(db) << endl;
    } else {
        sqlite3_bind_int(ppStmt, sqlite3_bind_parameter_index(ppStmt, "@uid"), 1);
        while(SQLITE_ROW == sqlite3_step(ppStmt)) {
            cout << "telefono: " << sqlite3_column_text(ppStmt, 0) << endl;
        }
        sqlite3_finalize(ppStmt);
    }

Reutilizar sentencias compiladas

Si vamos a usar la misma sentencia, con los mismos o diferentes parámetros, bastará con compilarla una vez y guardar el objeto sqlite3_stmt asociado a ella. Cada vez que tengamos que ejecutar la sentencia desde el principio habrá que asignar los parámetros adecuados, si los hay, y resetaer la sentencia. Para resetear una sentencia compilada se usa la función sqlite3_reset.

Un ejemplo:

    sqlite3_stmt *ppStmt;
    sqlite3_stmt *ppStmt2;
    char consulta[128];
	...

    strcpy(consulta, "SELECT rowid,nombre,email FROM usuario;");
    rc = sqlite3_prepare_v2(db, consulta, -1, &ppStmt, NULL);
    if( rc!=SQLITE_OK ){
        cout << "Error: " << sqlite3_errmsg(db) << endl;
        sqlite3_close(db);
        return 0;
    }
    strcpy(consulta, "SELECT numero FROM telefono WHERE idusuario=@uid;");
    rc = sqlite3_prepare_v2(db, consulta, -1, &ppStmt2, NULL);
    if( rc!=SQLITE_OK ){
        cout << "Error: " << sqlite3_errmsg(db) << endl;
        sqlite3_finalize(ppStmt);
        sqlite3_close(db);
        return 0;
    }
    while(SQLITE_ROW == sqlite3_step(ppStmt)) {
        cout << "ID:     " << sqlite3_column_int(ppStmt, 0) << endl;
        cout << "Nombre: " << sqlite3_column_text(ppStmt, 1) << endl;
        cout << "email:  " << sqlite3_column_text(ppStmt, 2) << endl;
        sqlite3_bind_int(ppStmt2, sqlite3_bind_parameter_index(ppStmt2, "@uid"), sqlite3_column_int(ppStmt, 0));
        while(SQLITE_ROW == sqlite3_step(ppStmt2)) {
            cout << " - telefono: " << sqlite3_column_text(ppStmt2, 0) << endl;
        }
        sqlite3_reset(ppStmt2);
    }
    sqlite3_finalize(ppStmt);
    sqlite3_finalize(ppStmt2);

Ejecución compacta de sentencias

Hay una alternativa a todo lo anterior, que podremos utilizar en ciertas circunstancias. Se trata de la función sqlite3_exec que es un envoltorio para las funciones sqlite3_prepare, sqlite3_step y sqlite3_finalize.

Esta función necesita cinco parámetros:

  1. Una conexión de base de datos, es decir, un puntero válido a un objeto sqlite3.
  2. Una cadena que contenga una o más sentencias SQL sin parámetros, separadas con punto y coma.
  3. Un puntero a una función. Esta función es una retrollamada, que será invocada para cada fila resultante de evaluar la sentencia SQL.
  4. Un puntero void que será usado como primer parámetro para la retrollamada. Podemos usarlo para pasar parámetros personalizados a la retrollamada.
  5. Un puntero a una cadena que se usará para almacenar un mensaje de error.

Cuando se trate de sentencias simples, que no proporcionen salidas, la sintaxis se simplifica mucho, ya que podemos omitir los tres últimos parámetros, por ejemplo:

    sqlite3_exec(db, "BEGIN", 0, 0, 0);
...
    sqlite3_exec(db, "END", 0, 0, 0);

Estas dos llamadas se pueden usar para empezar y terminar una transacción.

También se pueden usar esta función para generar listados, o iniciar listas de selección o menús en programas GUI, etc. Para eso tendremos que hacer uso de la función de retrollamada.

Esta función tiene este prototipo:

int callback(void *a_param, int argc, char **argv, char **column);

Donde el primer parámetro es el que indicamos como cuarto en sqlite3_exec, el segundo es un contador, que indica el número de elementos en el tercero, que es un puntero a una lista de punteros char, que contendrán los valores de las columnas en la fila retornada por la iteración actual de la sentencia. El cuarto parámetro es otra lista de cadenas con los nombres de las columnas, y por lo tanto, con el mismo número de elementos que argv.

El primer parámetro podemos usarlo para lo que queramos, depende de cada caso. Si no lo necesitamos, usaremos el valor 0.

Veamos un ejemplo:

int lUsuario(void *a_param, int argc, char **argv, char **column);
int lTelefono(void *a_param, int argc, char **argv, char **column);
...
    char msg[512];
	int rc;

    rc = sqlite3_open("agenda.db", &db);
    if(SQLITE_OK != rc) {
        cout << "Error: No se puede abrir la base de datos" << endl;
        sqlite3_close(db);
        return 1;
    }

    sqlite3_exec(db, "SELECT rowid AS ID,nombre,email FROM usuario ORDER BY nombre;", lUsuario, db, (char**)&msg);
    sqlite3_close(db);
...

int lUsuario(void *a_param, int argc, char **argv, char **column) {
    char consulta[256];
    char msg[512];
    sqlite3 *db = (sqlite3*)a_param;

    for (int i=0; i < argc; i++)
        cout << column[i] << " : " << argv[i] << endl;
    sprintf(consulta, "SELECT numero FROM telefono WHERE idusuario=\"%s\";", argv[0]);
    sqlite3_exec(db, consulta, lTelefono, 0, (char**)&msg);

    return 0;
}

int lTelefono(void *a_param, int argc, char **argv, char **column) {
    for (int i=0; i < argc; i++)
        cout << column[i] << " : " << argv[i] << endl;
    return 0;
}

En la primera llamada a sqlite3_exec hemos usado el cuarto parámetro para pasar una copia del puntero a la conexión de base de datos a la función de retrollamada. De ese modo, dentro de esa función, podremos hacer otra consulta para obtener los números de teléfono.