Embedded SQL in C

Introduction

Embedded SQL means that you can use SQL statements through a host programming language, for example, C/C++. C/C++ is used as an example in this article.

You include SQL statements as part of the source code for an application. The source code is compiled and linked with the appropriate language-specific facilities. The SQL statements are executed in the context of the application.

Mimer SQL supports embedded SQL for C/C++ according to the ISO standard.

Function

The ESQL preprocessor processes SQL statements embedded in the host language. SQL statements are identified by the leading delimiter “exec sql” and terminated by a semicolon (;), e.g. “exec sql DELETE FROM HOTEL;”.

You must declare host variables used in SQL statements within the SQL DECLARE SECTION, delimited by the statements BEGIN DECLARE SECTION and END DECLARE SECTION.

You can find detailed information on Embedded SQL in the Programmer’s Manual of the Mimer SQL Documentation set.

Example

The following is an example that connects to the database defined as the default. A table named “Straße” is created to show the multilingual support. To be able to create a table, a databank must be available for the purpose. Therefore, to be on the safe side, the databank ‘testdb’ is created. Finally, all base tables with the name starting on the letter ‘s’ (or ‘S’) are selected using a cursor. Before exiting, both the databank file and the table created are deleted.

The example is stored in a file called multilang.ec:

#include <stdio.h>;
#include <stdlib.h>;
#include <string.h>;
#include <locale.h>;

#define BUFLEN 129
#define lengthof(x) (sizeof(x)/sizeof(x[0]))

exec sql BEGIN DECLARE SECTION;
    static char SQLSTATE[6];
exec sql END DECLARE SECTION;

void print_sqlerror(void)
/*
**  print_sqlerror prints an error message for the latest error.
**  Programmed according to the X/Open CAE specification.
*/
{
    exec sql BEGIN DECLARE SECTION;
        int i;
        int exceptions;
        varchar message[255];
    exec sql END DECLARE SECTION;

    exec sql GET DIAGNOSTICS :exceptions = NUMBER; /* How many exceptions? */
    for (i=1; i&lt;=exceptions; i++) {
        exec sql GET DIAGNOSTICS EXCEPTION :i :message = MESSAGE_TEXT;
        printf("%s\n", message);
    }
}

int main()
{
    exec sql BEGIN DECLARE SECTION;
        nvarchar schema[129], table[129];
        varchar type[21];
    exec sql END DECLARE SECTION;

    char print_buffer[BUFLEN*6]; /* Multibyte character buffer */

    setlocale(LC_ALL, "");

    exec sql WHENEVER SQLEXCEPTION goto error_exit;

    /* Declare a cursor for all base tables staring with "S" */

    exec sql DECLARE MYCURSOR CURSOR FOR
             SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
             FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_TYPE = 'BASE TABLE'
             AND TABLE_NAME LIKE 's%';

    /* Connect to default database */

    exec sql CONNECT TO '' USER 'SYSADM' USING 'SYSADM';
    exec sql CREATE DATABANK testdb;
    exec sql CREATE TABLE Straße (col1 integer);
    exec sql OPEN MYCURSOR;

    printf("%-30s\t%-20s\n",
        "SCHEMA                          TABLE                    ", "TYPE");
    printf("%-30s\t%-20s\n",
        "======                          =====                    ", "====");
    while (1) {
        exec sql FETCH MYCURSOR INTO :schema, :table, :type;
        if (strcmp(SQLSTATE, "02000") == 0) break; /* No more rows */

        wcstombs(print_buffer, schema, lengthof(print_buffer));
        printf("%-25s\t", print_buffer);
        wcstombs(print_buffer, table, lengthof(print_buffer));
        printf("%-30s\t%-20s\n", print_buffer, type);
    }

    exec sql CLOSE MYCURSOR;
    exec sql COMMIT;
    exec sql DROP TABLE Straße CASCADE;
    exec sql DROP DATABANK testdb CASCADE;
    exit(EXIT_SUCCESS);

error_exit:
    print_sqlerror();

    exec sql WHENEVER SQLEXCEPTION CONTINUE;
    exec sql ROLLBACK;
    exec sql DISCONNECT ALL;
    exit(EXIT_FAILURE);
}

The program is preprocessed as follows:

esql −c multilang.ec

The resulting file, “multilang.c”, is compiled using a C-compiler and then linked to the Mimer SQL shared library or DLL. Usually, as an aid to this step, an example makefile or a build procedure is available in the software distribution package.

When executing the resulting “multilang” program, a list of the items selected will be displayed.

There are some other example programs available, like simple.ec and example.ec.

Benefits

Embedded SQL is a straightforward way of writing applications that access databases.

Links

Programming example files: simple.ec, example.ec

See the section about Embedded SQL in the Programmer’s Manual of the Mimer SQL Documentation set.