Archive for 3月 21st, 2012

  1. Hello, ODBC(API) World!

    Posted on 3月 21st, 2012 by cx20

    ODBC(API)

    ODBC（Open Database Connectivity）は、マイクロソフト社が提唱した DBMS 接続用の API 仕様である。
    DBMS の差異は ODBC ドライバによって吸収される為、ODBC の手順にしたがってプログラムを作成すれば、基本的な差異を意識せず、プログラムすることができる。

    ODBCドライバ ファイル
    Microsoft Access Driver (*.mdb) ODBCJT32.DLL
    Microsoft Text Driver (*.txt; *.csv) ODBCJT32.DLL
    Microsoft Excel Driver (*.xls) ODBCJT32.DLL
    Microsoft dBase Driver (*.dbf) ODBCJT32.DLL
    Microsoft ODBC for Oracle MSORCL32.DLL
    Microsoft Paradox Driver (*.db ) ODBCJT32.DLL
    SQL Server SQLSRV32.DLL
    Microsoft Access Driver (*.mdb, *.accdb) ACEODBC.DLL
    SQL Server Native Client 10.0 SQLNCLI10.DLL

    ソースコード（VC + ODBC + Jet データベース）

    hello.c
    raw download
    #include <windows.h>
#include <tchar.h>
#include <stdio.h>
#include <sqlext.h>
#include <sql.h>
 
int _tmain( int argc, TCHAR* argv[] )
{
    HENV henv;
    HDBC hdbc;
    HSTMT hstmt;
 
    _TCHAR szConnStr[1024];
    _TCHAR szConnOut[1024];
    SQLSMALLINT cchConOut; 
    _TCHAR szSQL[256];
 
    SQLTCHAR szColName[128];
    SQLSMALLINT nBufSize;
    SQLSMALLINT nSqlType;
    SQLULEN nColSize;
    SQLSMALLINT nScale;
    SQLSMALLINT nNullable;
    SQLTCHAR szMessage[256];
    SQLLEN nMessageLen;
    SQLRETURN  nReturn;
 
    _tcscpy( szConnStr, _T("Driver={Microsoft Access Driver (*.mdb)};DBQ=hello.mdb") );
    _tcscpy( szSQL, _T("SELECT 'Hello, ODBC World!' AS Message") );
 
    SQLAllocEnv( &henv );
    SQLAllocConnect( henv, &hdbc );
    SQLDriverConnect( hdbc, NULL, (SQLTCHAR*)szConnStr, _tcslen(szConnStr), 
        (SQLTCHAR*)szConnOut, _countof(szConnOut), &cchConOut, SQL_DRIVER_NOPROMPT );
    SQLAllocStmt( hdbc, &hstmt );
    SQLExecDirect( hstmt, (SQLTCHAR*)szSQL, SQL_NTS );
    SQLDescribeCol( hstmt, 1, (SQLTCHAR*)szColName, _countof(szColName),
        &nBufSize, &nSqlType, &nColSize, &nScale, &nNullable );
    SQLBindCol( hstmt, 1, SQL_C_TCHAR, szMessage, _countof(szMessage), &nMessageLen );
    while( TRUE )
    {
        nReturn = SQLFetch( hstmt );
        if( nReturn != SQL_SUCCESS
         && nReturn != SQL_SUCCESS_WITH_INFO )
        {
            break;
        }
 
        _tprintf( _T("%sn"), szColName );
        _tprintf( _T("------------------n") );
        _tprintf( _T("%sn"), szMessage );
    }
 
    SQLFreeStmt( hstmt, SQL_DROP );
    SQLDisconnect( hdbc );
    SQLFreeConnect( hdbc );
    SQLFreeEnv( henv );
 
    return 0;
}

    ソースコード（VC + ODBC + ACE データベース）

    hello.c
    raw download
    #include <windows.h>
#include <tchar.h>
#include <stdio.h>
#include <sqlext.h>
#include <sql.h>
 
int _tmain( int argc, TCHAR* argv[] )
{
    HENV henv;
    HDBC hdbc;
    HSTMT hstmt;
 
    _TCHAR szConnStr[1024];
    _TCHAR szConnOut[1024];
    SQLSMALLINT cchConOut; 
    _TCHAR szSQL[256];
 
    SQLTCHAR szColName[128];
    SQLSMALLINT nBufSize;
    SQLSMALLINT nSqlType;
    SQLULEN nColSize;
    SQLSMALLINT nScale;
    SQLSMALLINT nNullable;
    SQLTCHAR szMessage[256];
    SQLLEN nMessageLen;
    SQLRETURN  nReturn;
 
    _tcscpy( szConnStr, _T("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=.\hello.accdb") );
    _tcscpy( szSQL, _T("SELECT 'Hello, ODBC World!' AS Message") );
 
    SQLAllocEnv( &henv );
    SQLAllocConnect( henv, &hdbc );
    SQLDriverConnect( hdbc, NULL, (SQLTCHAR*)szConnStr, _tcslen(szConnStr), 
        (SQLTCHAR*)szConnOut, _countof(szConnOut), &cchConOut, SQL_DRIVER_NOPROMPT );
    SQLAllocStmt( hdbc, &hstmt );
    SQLExecDirect( hstmt, (SQLTCHAR*)szSQL, SQL_NTS );
    SQLDescribeCol( hstmt, 1, (SQLTCHAR*)szColName, _countof(szColName),
        &nBufSize, &nSqlType, &nColSize, &nScale, &nNullable );
    SQLBindCol( hstmt, 1, SQL_C_TCHAR, szMessage, _countof(szMessage), &nMessageLen );
    while( TRUE )
    {
        nReturn = SQLFetch( hstmt );
        if( nReturn != SQL_SUCCESS
         && nReturn != SQL_SUCCESS_WITH_INFO )
        {
            break;
        }
 
        _tprintf( _T("%sn"), szColName );
        _tprintf( _T("------------------n") );
        _tprintf( _T("%sn"), szMessage );
    }
 
    SQLFreeStmt( hstmt, SQL_DROP );
    SQLDisconnect( hdbc );
    SQLFreeConnect( hdbc );
    SQLFreeEnv( henv );
 
    return 0;
}

    ソースコード（VC + ODBC + SQL Server）

    hello.c
    raw download
    #include <windows.h>
#include <tchar.h>
#include <stdio.h>
#include <sqlext.h>
#include <sql.h>
 
int _tmain( int argc, TCHAR* argv[] )
{
    HENV henv;
    HDBC hdbc;
    HSTMT hstmt;
 
    _TCHAR szConnStr[1024];
    _TCHAR szConnOut[1024];
    SQLSMALLINT cchConOut; 
    _TCHAR szSQL[256];
 
    SQLTCHAR szColName[128];
    SQLSMALLINT nBufSize;
    SQLSMALLINT nSqlType;
    SQLULEN nColSize;
    SQLSMALLINT nScale;
    SQLSMALLINT nNullable;
    SQLTCHAR szMessage[256];
    SQLLEN nMessageLen;
    SQLRETURN  nReturn;
 
    _tcscpy( szConnStr, _T("Driver={SQL Server};SERVER=(local);DATABASE=master;UID=sa;PWD=P@ssW0rd") );
    _tcscpy( szSQL, _T("SELECT 'Hello, ODBC World!' AS Message") );
 
    SQLAllocEnv( &henv );
    SQLAllocConnect( henv, &hdbc );
    SQLDriverConnect( hdbc, NULL, (SQLTCHAR*)szConnStr, _tcslen(szConnStr), 
        (SQLTCHAR*)szConnOut, _countof(szConnOut), &cchConOut, SQL_DRIVER_NOPROMPT );
    SQLAllocStmt( hdbc, &hstmt );
    SQLExecDirect( hstmt, (SQLTCHAR*)szSQL, SQL_NTS );
    SQLDescribeCol( hstmt, 1, (SQLTCHAR*)szColName, _countof(szColName),
        &nBufSize, &nSqlType, &nColSize, &nScale, &nNullable );
    SQLBindCol( hstmt, 1, SQL_C_TCHAR, szMessage, _countof(szMessage), &nMessageLen );
    while( TRUE )
    {
        nReturn = SQLFetch( hstmt );
        if( nReturn != SQL_SUCCESS
         && nReturn != SQL_SUCCESS_WITH_INFO )
        {
            break;
        }
 
        _tprintf( _T("%sn"), szColName );
        _tprintf( _T("------------------n") );
        _tprintf( _T("%sn"), szMessage );
    }
 
    SQLFreeStmt( hstmt, SQL_DROP );
    SQLDisconnect( hdbc );
    SQLFreeConnect( hdbc );
    SQLFreeEnv( henv );
 
    return 0;
}

    ソースコード（VC + ODBC + Oracle）

    hello.c
    raw download
    #include <windows.h>
#include <tchar.h>
#include <stdio.h>
#include <sqlext.h>
#include <sql.h>
 
int _tmain( int argc, TCHAR* argv[] )
{
    HENV henv;
    HDBC hdbc;
    HSTMT hstmt;
 
    _TCHAR szConnStr[1024];
    _TCHAR szConnOut[1024];
    SQLSMALLINT cchConOut; 
    _TCHAR szSQL[256];
 
    SQLTCHAR szColName[128];
    SQLSMALLINT nBufSize;
    SQLSMALLINT nSqlType;
    SQLULEN nColSize;
    SQLSMALLINT nScale;
    SQLSMALLINT nNullable;
    SQLTCHAR szMessage[256];
    SQLLEN nMessageLen;
    SQLRETURN  nReturn;
 
    _tcscpy( szConnStr, _T("Driver={Microsoft ODBC for Oracle};SERVER=ORCL;UID=scott;PWD=tiger") );
    _tcscpy( szSQL, _T("SELECT 'Hello, ODBC World!' AS Message FROM DUAL") );
 
    SQLAllocEnv( &henv );
    SQLAllocConnect( henv, &hdbc );
    SQLDriverConnect( hdbc, NULL, (SQLTCHAR*)szConnStr, _tcslen(szConnStr), 
        (SQLTCHAR*)szConnOut, _countof(szConnOut), &cchConOut, SQL_DRIVER_NOPROMPT );
    SQLAllocStmt( hdbc, &hstmt );
    SQLExecDirect( hstmt, (SQLTCHAR*)szSQL, SQL_NTS );
    SQLDescribeCol( hstmt, 1, (SQLTCHAR*)szColName, _countof(szColName),
        &nBufSize, &nSqlType, &nColSize, &nScale, &nNullable );
    SQLBindCol( hstmt, 1, SQL_C_TCHAR, szMessage, _countof(szMessage), &nMessageLen );
    while( TRUE )
    {
        nReturn = SQLFetch( hstmt );
        if( nReturn != SQL_SUCCESS
         && nReturn != SQL_SUCCESS_WITH_INFO )
        {
            break;
        }
 
        _tprintf( _T("%sn"), szColName );
        _tprintf( _T("------------------n") );
        _tprintf( _T("%sn"), szMessage );
    }
 
    SQLFreeStmt( hstmt, SQL_DROP );
    SQLDisconnect( hdbc );
    SQLFreeConnect( hdbc );
    SQLFreeEnv( henv );
 
    return 0;
}

    ソースコード（VC + ODBC + MySQL）

    hello.c
    raw download
    #include <windows.h>
#include <tchar.h>
#include <stdio.h>
#include <sqlext.h>
#include <sql.h>
 
int _tmain( int argc, TCHAR* argv[] )
{
    HENV henv;
    HDBC hdbc;
    HSTMT hstmt;
 
    _TCHAR szConnStr[1024];
    _TCHAR szConnOut[1024];
    SQLSMALLINT cchConOut; 
    _TCHAR szSQL[256];
 
    SQLTCHAR szColName[128];
    SQLSMALLINT nBufSize;
    SQLSMALLINT nSqlType;
    SQLULEN nColSize;
    SQLSMALLINT nScale;
    SQLSMALLINT nNullable;
    SQLTCHAR szMessage[256];
    SQLLEN nMessageLen;
    SQLRETURN  nReturn;
 
    _tcscpy( szConnStr, _T("Driver={MySQL ODBC 5.1 Driver};Server=localhost;UID=root;PWD=P@ssW0rd") );
    _tcscpy( szSQL, _T("SELECT 'Hello, ODBC World!' AS Message") );
 
    SQLAllocEnv( &henv );
    SQLAllocConnect( henv, &hdbc );
    SQLDriverConnect( hdbc, NULL, (SQLTCHAR*)szConnStr, _tcslen(szConnStr), 
        (SQLTCHAR*)szConnOut, _countof(szConnOut), &cchConOut, SQL_DRIVER_NOPROMPT );
    SQLAllocStmt( hdbc, &hstmt );
    SQLExecDirect( hstmt, (SQLTCHAR*)szSQL, SQL_NTS );
    SQLDescribeCol( hstmt, 1, (SQLTCHAR*)szColName, _countof(szColName),
        &nBufSize, &nSqlType, &nColSize, &nScale, &nNullable );
    SQLBindCol( hstmt, 1, SQL_C_TCHAR, szMessage, _countof(szMessage), &nMessageLen );
    while( TRUE )
    {
        nReturn = SQLFetch( hstmt );
        if( nReturn != SQL_SUCCESS
         && nReturn != SQL_SUCCESS_WITH_INFO )
        {
            break;
        }
 
        _tprintf( _T("%sn"), szColName );
        _tprintf( _T("------------------n") );
        _tprintf( _T("%sn"), szMessage );
    }
 
    SQLFreeStmt( hstmt, SQL_DROP );
    SQLDisconnect( hdbc );
    SQLFreeConnect( hdbc );
    SQLFreeEnv( henv );
 
    return 0;
}

    コンパイル＆リンク方法（Visual C++）

    C:¥> cl hello.c /link odbc32.lib

    実行結果

    Message
------------------
Hello, ODBC World!