Data Management in Bioinformatics/Integrating SQL and Programming Languages

Embedding SQL

int main ()
{
    EXEC SQL BEGIN DECLARE SECTION
        char name[30];
        char annotation[30];
        int gid;
        char SQLSTATE[6];   // will hold the SQL transaction codes
    EXEC SQL END DECLARE SECTION
    char x[30];

    ...

    cout << "Please enter some values" << endl;
    cin >> name;

    ...

    EXEC SQL
        INSERT INTO GENES VALUES(:gid, :annotation, :name);

    EXEC SQL
        SELECT name
        INTO :x
        FROM Genes
        WHERE gid = 23;
}

With Cursor

EXEC SQL DECLARE db_cursor CURSOR
    FOR [
        ...
    ]

EXEC SQL OPEN db_cursor
    EXEC SQL FETCH FROM db_cursor
        INTO ...	// put into array and process
EXEC SQL CLOSE db_cursor

Stored Procedures

Stays in the database space

CREATE FUNCTION FACTORIAL
AS {
    ...
}
LANGUAGE 'c';

SELECT FACTORIAL(num)
FROM Numbers;