Archive for 3月 20th, 2012

  1. Hello, ESQL/C World!

    Posted on 3月 20th, 2012 by cx20

    ESQL/C

    ESQL は埋め込み SQL(Embedded SQL)とも呼ばれ、手続型プログラミング言語(ホスト言語)に SQL を埋め込む方式のことを言う。埋め込み SQL 自体は、標準SQL として ANSI/ISO にて仕様が策定されている。
    ESQL/C は、ホスト言語に C言語を対象としており、SQL Server の他、いくつかの DBMS で採用している。なお、Oracle Database 用の 埋め込みSQL は Pro*C と呼ばれる。
    SQL Server 用の ESQL/C では、ESQL プリプロセッサにより「.sqc」ファイルから「*.c」ファイルを生成する。

    ソースコード(ESQL)

    #include <stdio.h>
     
    int main( int argc, char* argv[] )
    {
        EXEC SQL BEGIN DECLARE SECTION;
        char szServerDatabase[] = "(local).pubs";
        char szLoginPassword[] = "sa.P@ssW0rd";
        char szCommand[] = "SELECT 'Hello, ESQL/C World!' AS Message";
        char message[32] = { 0 };
        EXEC SQL END DECLARE SECTION;
        EXEC SQL CONNECT TO :szServerDatabase USER :szLoginPassword;
        EXEC SQL DECLARE C1 CURSOR FOR stmt;
        EXEC SQL PREPARE stmt FROM :szCommand;
        EXEC SQL OPEN C1;
        while ( SQLCODE == 0 )
        {
            EXEC SQL FETCH C1 INTO :message;
            if ( SQLCODE == 0 )
            {
                printf( "Messagen" );
                printf( "--------------------n" );
                printf( "%sn", message );
            }
        }
        EXEC SQL CLOSE C1;
        EXEC SQL DISCONNECT ALL;
        return 0;
    }

    上記コードは、ESQL プリプロセッサにより、以下の C言語のコードが生成される。

    ソースコード(C言語)

    /* ===== hello.c =====*/
    /* ===== NT doesn't need the following... */
    #ifndef WIN32
    #define WIN32
    #endif
    #define _loadds
    #define _SQLPREP_
    #include <sqlca.h>
    #include <sqlda.h>
    #include <string.h>
    #define SQLLENMAX(x)      ( ((x) > 32767) ? 32767 : (x) )
    short ESQLAPI _loadds sqlaaloc(
    	unsigned short usSqlDaId,
    	unsigned short sqld,
    	unsigned short stmt_id,
    	void far *spare);
    short ESQLAPI _loadds sqlxcall(
    	unsigned short usCallType,
    	unsigned short usSection,
    	unsigned short usSqldaInId,
    	unsigned short usSqlDaOutId,
    	unsigned short usSqlTextLen,
    	char far *lpszSQLText);
    short ESQLAPI _loadds sqlacall(
    	unsigned short usCallType,
    	unsigned short usSection,
    	unsigned short usSqldaInId,
    	unsigned short usSqlDaOutId,
    	void far *spare);
    short ESQLAPI _loadds sqladloc(
    	unsigned short usSqldaInId,
    	void far *spare);
    short ESQLAPI _loadds sqlasets(
    	unsigned short cbSqlText,
    	void far *lpvSqlText,
    	void far *spare);
    short ESQLAPI _loadds sqlasetv(
    	unsigned short usSqldaInId,
    	unsigned short sqlvar_index,
    	unsigned short sqltype,
    	unsigned short sqllen,
    	void far *sqldata,
    	void far *sqlind,
    	void far *spare);
    short ESQLAPI _loadds sqlastop(
    	void far *spare);
    short ESQLAPI _loadds sqlastrt(
    	void far *pid,
    	void far *spare,
    	void far *sqlca);
    short ESQLAPI _loadds sqlausda(
    	unsigned short sqldaId,
    	void far *lpvSqlDa,
    	void far *spare);
    extern struct tag_sqlca far sql_sqlca;
    extern struct tag_sqlca far *sqlca;
    struct sqla_program_id2 {unsigned short length;unsigned short rp_rel_num;unsigned short db_rel_num;unsigned short bf_rel_num;unsigned char  sqluser[30];unsigned char  sqlusername[30];
    unsigned char  planname[256];unsigned char  contoken[8];unsigned char  buffer[8];};static struct sqla_program_id2 program_id =		{340,2,0,0,"                              ","","hello","AAAvBYD0","        "};
    static void far* pid = &program_id;
    #line 1 "hello.sqc"
    #line 2 "hello.sqc"
    int main( int argc, char* argv[] )
    {
    #line 5
    /*
    EXEC SQL BEGIN DECLARE SECTION;
    */
    #line 5
        char szServerDatabase[] = "(local).pubs";
        char szLoginPassword[] = "sa.P@ssW0rd";
        char szCommand[] = "SELECT 'Hello, ESQL/C World!' AS Message";
        char message[32] = { 0 };
    #line 10
    /*
    EXEC SQL END DECLARE SECTION;
    */
    #line 10
    #line 11
    /*
    EXEC SQL CONNECT TO :szServerDatabase USER :szLoginPassword;
    */
    #line 11
    #line 11
    {
    #line 11
    	sqlastrt((void far *)pid, (void far *)0, (struct tag_sqlca far *)sqlca);
    #line 11
    	sqlaaloc(2, 2, 1, (void far *)0);
    #line 11
    	sqlasetv(2, 0, 462, (short) SQLLENMAX(sizeof(szServerDatabase)), (void far *)szServerDatabase, (void far *)0, (void far *)0L);
    #line 11
    	sqlasetv(2, 1, 462, (short) SQLLENMAX(sizeof(szLoginPassword)), (void far *)szLoginPassword, (void far *)0, (void far *)0L);
    #line 11
    	sqlxcall(30, 1, 2, 0, 55, (char far *)"  CONNECT TO @p1                USER @p2               ");
    #line 11
    	SQLCODE = sqlca->sqlcode;
    #line 11
    	sqlastop((void far *)0L);
    #line 11
    }
    #line 12
    #line 12
    /*
    EXEC SQL DECLARE C1 CURSOR FOR stmt;
    */
    #line 12
    #line 13
    /*
    EXEC SQL PREPARE stmt FROM :szCommand;
    */
    #line 13
    #line 13
    {
    #line 13
    	sqlastrt((void far *)pid, (void far *)0, (struct tag_sqlca far *)sqlca);
    #line 13
    	sqlasets((unsigned short)strlen(szCommand), (void far *)szCommand, (void *)0L);
    #line 13
    	sqlacall(27, 2, 0, 0, 0L);
    #line 13
    	SQLCODE = sqlca->sqlcode;
    #line 13
    	sqlastop((void far *)0L);
    #line 13
    }
    #line 14
    #line 14
    /*
    EXEC SQL OPEN C1;
    */
    #line 14
    #line 14
    {
    #line 14
    	sqlastrt((void far *)pid, (void far *)0, (struct tag_sqlca far *)sqlca);
    #line 14
    	sqlxcall(110, 2, 0, 0, 18, (char far *)"/* C1 2 nohold */ ");
    #line 14
    	SQLCODE = sqlca->sqlcode;
    #line 14
    	sqlastop((void far *)0L);
    #line 14
    }
    #line 15
    #line 14
    {
    #line 14
    	sqlastrt((void far *)pid, (void far *)0, (struct tag_sqlca far *)sqlca);
    #line 14
    	sqlacall(26, 2, 0, 0, 0L);
    #line 14
    	SQLCODE = sqlca->sqlcode;
    #line 14
    	sqlastop((void far *)0L);
    #line 14
    }
    #line 15
        while ( SQLCODE == 0 )
        {
    #line 17
    /*
    EXEC SQL FETCH C1 INTO :message;
    */
    #line 17
    #line 17
    {
    #line 17
    	sqlastrt((void far *)pid, (void far *)0, (struct tag_sqlca far *)sqlca);
    #line 17
    	sqlaaloc(1, 1, 2, (void far *)0);
    #line 17
    	sqlasetv(1, 0, 462,(short) SQLLENMAX(sizeof(message)),(void far *)&message, (void far *)0,0L);
    #line 17
    	sqlxcall(25, 2, 0, 1, 26, (char far *)"  FETCH C1 INTO :         ");
    #line 17
    	SQLCODE = sqlca->sqlcode;
    #line 17
    	sqlastop((void far *)0L);
    #line 17
    }
    #line 18
            if ( SQLCODE == 0 )
            {
                printf( "Messagen" );
                printf( "--------------------n" );
                printf( "%sn", message );
            }
        }
    #line 25
    /*
    EXEC SQL CLOSE C1;
    */
    #line 25
    #line 25
    {
    #line 25
    	sqlastrt((void far *)pid, (void far *)0, (struct tag_sqlca far *)sqlca);
    #line 25
    	sqlxcall(20, 2, 0, 0, 11, (char far *)"  CLOSE C1 ");
    #line 25
    	SQLCODE = sqlca->sqlcode;
    #line 25
    	sqlastop((void far *)0L);
    #line 25
    }
    #line 26
    #line 26
    /*
    EXEC SQL DISCONNECT ALL;
    */
    #line 26
    #line 26
    {
    #line 26
    	sqlastrt((void far *)pid, (void far *)0, (struct tag_sqlca far *)sqlca);
    #line 26
    	sqlxcall(36, 3, 0, 0, 17, (char far *)"  DISCONNECT ALL ");
    #line 26
    	SQLCODE = sqlca->sqlcode;
    #line 26
    	sqlastop((void far *)0L);
    #line 26
    }
    #line 27
        return 0;
    }
    long SQLCODE;

    コンパイル&リンク方法(Visual C++)

    C:¥> nsqlprep hello.sqc
    C:¥> cl hello.c /link sqlakw32.lib caw32.lib

    実行結果

    Message
    --------------------
    Hello, ESQL/C World!