Data Management in Bioinformatics/Integrating SQL and Programming Languages

From Wikibooks, open books for an open world
Jump to: navigation, search

Embedding SQL[edit]

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[edit]

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[edit]

Stays in the database space

CREATE FUNCTION FACTORIAL
AS {
    ...
}
LANGUAGE 'c';
 
SELECT FACTORIAL(num)
FROM Numbers;