エクスポート (0) 印刷
すべて展開

方法:ODBC を使用した Azure SQL データベースへの接続

更新日: 2014年11月

このトピックでは、ODBC を使用して Microsoft Azure SQL データベース に接続する方法を説明し、コンソール アプリケーションの例を示します。次のいずれかの ODBC バージョンを使用できます。

ODBC を使用した SQL データベース接続には、次のガイドラインが適用されます。

  • SQL Server 2008 R2 または SQL Server 2008 の SQL Server Native Client を使用する場合には、接続文字列にユーザー名の一部としてサーバー名を含める必要があります (user@server)。

  • Azure SQL データベースに接続する場合には、プロトコルは TCP/IP を使用します。

  • master データベースにはテーブルを作成できません。このため、テーブルを作成するには、ユーザー データベースを作成する必要があります。

  • ユーザー データベース コマンドを実行してユーザー データベースに切り替えることはできません。接続を切断してから直接ユーザー データベースに接続する必要があります。

  • データを挿入するには、テーブルに主キーまたはクラスター化インデックスが必要です。

次のサンプルは、C++ コンソール アプリケーションにコンパイルできます。コンパイルには、SQL Server 2012 または SQL Server 2008 R2 の SQL Server Native Client が必要です。コマンド ラインから .exe を実行するときは、データベース キーワードを使用せず、接続文字列を引用符で囲んで指定します。例:

SQLAzureODBCSample.exe "DRIVER=SQL Server Native Client 11.0;Server=mydatabase.database.windows.net;UID=myuserid;PWD=mypwd;Encrypt=yes;TrustServerCertificate=no;"

この例では、データベースとテーブルを作成します。エラー処理は最小限となっており、接続再試行ロジックはありません。

// //  PROGRAM: SQLAzureODBCSample // //  PURPOSE: Allow user to connect to Azure SQL Database.  #include <windows.h> #include <sqlext.h> #include <sql.h> #include <stdio.h> #include <stdlib.h>  void usage(void); void DumpErrors(SQLSMALLINT HandleType, HANDLE Handle);  void wmain(int argc, wchar_t * argv[]) {        HENV henv1 = SQL_NULL_HENV;        HDBC hdbc1 = SQL_NULL_HDBC;        HSTMT hstmt1 = SQL_NULL_HSTMT;        LPWSTR pwszConnStr = NULL;        LPWSTR pwszUserConnStr = NULL;        ULONG ulConnStr = 0;        SQLSMALLINT cbOutConnStr = 0;        char wszOutConnStr[4048] = "";        RETCODE rc = SQL_ERROR;                    // Check arg        if (argc != 2)               usage();         pwszConnStr = argv[1];         // Allocate environment        if (SQL_SUCCESS != (rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv1)))        {               // Couldn't allocate environment               wprintf(L"Unable to allocate environment.\r\n");               goto CLEANUP;        }         // Set the ODBC version being used        if (SQL_SUCCESS != SQLSetEnvAttr(henv1, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_INTEGER))        {               // Couldn't allocate environment               wprintf(L"Unable to set ODBC version.\r\n");               DumpErrors(SQL_HANDLE_ENV, henv1);               goto CLEANUP;        }         // Allocate connection handle        if (SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_DBC, henv1, &hdbc1))        {               // Couldn't allocate connection               wprintf(L"Unable to allocate connection.\r\n");               DumpErrors(SQL_HANDLE_ENV, henv1);               goto CLEANUP;        }         // Connect to master database which is the default if no database is specified        // Here we assume the connection string does not contain the database name already.        wprintf(L"Connecting to server master database using connection string: %s\r\n", pwszConnStr);        if (!(SQL_SUCCEEDED(SQLDriverConnect(hdbc1, NULL, (SQLWCHAR *)pwszConnStr, SQL_NTS, (SQLWCHAR *)wszOutConnStr, sizeof(wszOutConnStr), &cbOutConnStr, SQL_DRIVER_NOPROMPT))))        {               // Couldn't connect               wprintf(L"Unable to connect to server using connection string %s.\r\n", pwszConnStr);               DumpErrors(SQL_HANDLE_DBC, hdbc1);               goto CLEANUP;        }         wprintf(L"\tConnected!\r\n");         // Allocate hstmt        if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1))        {               // Couldn't allocate statement               wprintf(L"Unable to allocate statement.\r\n");               DumpErrors(SQL_HANDLE_DBC, hdbc1);               goto CLEANUP;        }         // Create a user database for our table        SQLExecDirect(hstmt1, L"drop database MySampleDB", SQL_NTS);        if (SQL_SUCCESS != SQLExecDirect(hstmt1, L"create database MySampleDB", SQL_NTS))        {               // Couldn't create table               wprintf(L"Database creation failed.\r\n");               DumpErrors(SQL_HANDLE_STMT, hstmt1);               goto CLEANUP;        }         // Add the user database to the connection string, again assuming it does not contain a database keyword already        ULONG ulMemSize = (wcslen(pwszConnStr) + wcslen(L";Database=MySampleDB"))*sizeof(WCHAR);        pwszUserConnStr = (LPWSTR)malloc(ulMemSize);         // Assume memory allocation succeeded        swprintf(pwszUserConnStr, ulMemSize, L"%s;Database=MySampleDB", pwszConnStr);         // Disconnect so we can now connect to the user database        wprintf(L"\"Use <database>\" command is not supported on Azure SQL Database. Disconnecting from master database and reconnecting to user database using connection string:\r\n\t%s\r\n", pwszUserConnStr);        SQLDisconnect(hdbc1);         // Connect to user database        if (!(SQL_SUCCEEDED(SQLDriverConnect(hdbc1, NULL, (SQLWCHAR *)pwszUserConnStr, SQL_NTS, (SQLWCHAR *)wszOutConnStr, sizeof(wszOutConnStr), &cbOutConnStr, SQL_DRIVER_NOPROMPT))))        {               // Couldn't connect               wprintf(L"Unable to connect to server using connection string %s.\r\n", pwszUserConnStr);               DumpErrors(SQL_HANDLE_DBC, hdbc1);               goto CLEANUP;        }         // Allocate hstmt again        if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1))        {               // Couldn't allocate statement               wprintf(L"Unable to allocate statement.\r\n");               DumpErrors(SQL_HANDLE_DBC, hdbc1);               goto CLEANUP;        }         // Create a table        // Write to the screen        wprintf(L"Creating a table.\r\n");        if (SQL_SUCCESS != SQLExecDirect(hstmt1, L"create table MySampleTable(col1 int primary key, col2 char(30))", SQL_NTS))        {               // Couldn't create table               wprintf(L"Table creation failed.\r\n");               DumpErrors(SQL_HANDLE_STMT, hstmt1);               goto CLEANUP;        }         // Insert a row        // Write to the screen        wprintf(L"Inserting a row.\r\n");        if (SQL_SUCCESS != SQLExecDirect(hstmt1, L"insert into MySampleTable(col1, col2) values (1, ' Azure SQL Database Rocks')", SQL_NTS))        {               // Unable to insert data               wprintf(L"Inserting into table failed.\r\n");               DumpErrors(SQL_HANDLE_STMT, hstmt1);               goto CLEANUP;        }         // Select the data        // Write to the screen        wprintf(L"Executing select statement from the table.\r\n");        if (SQL_SUCCESS != SQLExecDirect(hstmt1, L"select col1, col2 from MySampleTable", SQL_NTS))        {               // Unable to retrieve data               wprintf(L"Select statement failed.\r\n");               DumpErrors(SQL_HANDLE_STMT, hstmt1);               goto CLEANUP;        }         // Write to the screen        wprintf(L"Retrieving selected data.\r\n");         // Fetch a row        while (SQL_SUCCESS == (rc = SQLFetch(hstmt1)))        {               for (int iCol = 0; iCol < 2; iCol++)               {                      WCHAR wszBuf[100];                      SQLINTEGER IndPtr;                       // Retrieve the column                      if (SQL_SUCCESS != SQLGetData(hstmt1, iCol+1, SQL_C_WCHAR, wszBuf, sizeof(wszBuf), &IndPtr))                      {                            // Unable to retrieve data                            wprintf(L"\r\nRetrieving column data failed.\r\n");                            DumpErrors(SQL_HANDLE_STMT, hstmt1);                            goto CLEANUP;                      }                       wprintf(wszBuf);                      wprintf(L"\t");               }                     }         if (SQL_NO_DATA != rc)        {               // Unable to retrieve data               wprintf(L"Fetching row failed.\r\n");               DumpErrors(SQL_HANDLE_STMT, hstmt1);               goto CLEANUP;        }        wprintf(L"\r\nAll rows retrieved.\r\n");          // Disconnect so we can now connect to the master database again to drop the user database        // Write to the screen        wprintf(L"\r\nDisconnect again and connect to master database to drop the database we created.\r\n");        SQLDisconnect(hdbc1);        // Connect to master database which is the default if no database is specified        if (!(SQL_SUCCEEDED(SQLDriverConnect(hdbc1, NULL, (SQLWCHAR *)pwszConnStr, SQL_NTS, (SQLWCHAR *)wszOutConnStr, sizeof(wszOutConnStr), &cbOutConnStr, SQL_DRIVER_NOPROMPT))))        {               // Couldn't connect               wprintf(L"Unable to connect to server using connection string %s.\r\n", pwszConnStr);               DumpErrors(SQL_HANDLE_DBC, hdbc1);               goto CLEANUP;        }         // Allocate hstmt        if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1))        {               // Couldn't allocate statement               wprintf(L"Unable to allocate statement.\r\n");               DumpErrors(SQL_HANDLE_DBC, hdbc1);               goto CLEANUP;        }         // Drop the user database        if (SQL_SUCCESS != SQLExecDirect(hstmt1, L"drop database MySampleDB", SQL_NTS))        {               // Unable to retrieve data               wprintf(L"Drop database failed.\r\n");               DumpErrors(SQL_HANDLE_STMT, hstmt1);               goto CLEANUP;        }          wprintf(L"\r\nCompleted...\r\n");  CLEANUP:        // Clean up before exiting;        if (hstmt1 != SQL_NULL_HSTMT)               SQLFreeStmt(hstmt1, SQL_DROP);        if (hdbc1 != SQL_NULL_HDBC)        {               SQLDisconnect(hdbc1);               SQLFreeConnect(hdbc1);        }        if (henv1 != SQL_NULL_HENV)               SQLFreeEnv(henv1);     return; }  void usage(void) {        wprintf(L"Usage: SQLAzureODBCSample ConnectionString\r\n");        wprintf(L"\tMust specify an ODBC DriverConnect connection string in quotes as the only argument and it must not contain the database keyword for this sample to work.\r\n");        wprintf(L"\tExample: SQLAzureODBCSample.exe "DRIVER=SQL Server Native Client 11.0;Server=myserver.database.windows.net;UID=myuserid;PWD=mypwd;Encrypt=yes;TrustServerCertificate=no;"\r\n");        wprintf(L"\tExample 2 (SQL Server 2008 or SQL Server 2008 R2 driver): SQLAzureODBCSample "DRIVER=SQL Server Native Client 10.0;Server=myserver.database.windows.net;UID=myuserid@myserver;PWD=mypwd;Encrypt=yes;TrustServerCertificate=no;"\r\n");        exit(0); }  void DumpErrors(SQLSMALLINT HandleType, HANDLE Handle) {        SQLSMALLINT iRec = 1;        SQLWCHAR SQLState[10];        SQLINTEGER NativeError;        SQLWCHAR MessageText[512];                wprintf(L"\r\nErrors occurred:\r\n");         while (SQL_SUCCESS == SQLGetDiagRec(      HandleType,      Handle,      iRec,      SQLState,      &NativeError,      MessageText,      sizeof(MessageText),      NULL))        {                wprintf(L"\tSQLState: %s, Native Error: %d\r\n", SQLState, NativeError);               wprintf(L"\tMessage: %s\r\n", MessageText);                iRec++;        } }

次のサンプルは、コンソール アプリケーションにコンパイルできます。アプリケーションを実行するときには、ドライバー、サーバー、UID および PWD 接続文字列キーワードを指定します。この例では、データベースとテーブルを作成します。

//  PROGRAM: SQLAzureODBCSample //  // compile with: g++ SQLAzureODBCSample.cpp –o  SQLAzureODBCSample -lodbc // run with: ./ SQLAzureODBCSample "DRIVER=ODBC Driver 11 for SQL Server;Server=myserver.int.mscds.com;UID=myid;PWD=\$ql\$erver51" // // //  PURPOSE: Allow user to connect to Azure SQL Database.  #include <sql.h> #include <sqlext.h> #include <stdio.h> #include <stdlib.h> #include <string.h>  void usage(void); void DumpErrors(SQLSMALLINT HandleType, SQLHANDLE Handle);  int main(int argc, char * argv[]) {    SQLHANDLE henv1 = SQL_NULL_HENV;    SQLHANDLE hdbc1 = SQL_NULL_HDBC;    SQLHANDLE hstmt1 = SQL_NULL_HSTMT;    char* pszConnStr = NULL;    char* pszUserConnStr = NULL;    SQLSMALLINT cbOutConnStr = 0;    char szOutConnStr[4048] = "";    RETCODE rc = SQL_ERROR;               ULONG ulMemSize = 0;     // Check arg    if (argc != 2)       usage();     pszConnStr = argv[1];     // Allocate environment    if (SQL_SUCCESS != (rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv1))) {       // Couldn't allocate environment       printf("Unable to allocate environment.\r\n");       goto CLEANUP;    }     // Set the ODBC version being used    if (SQL_SUCCESS != SQLSetEnvAttr(henv1, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_INTEGER)) {       // Couldn't allocate environment       printf("Unable to set ODBC version.\r\n");       DumpErrors(SQL_HANDLE_ENV, henv1);       goto CLEANUP;    }     // Allocate connection handle    if (SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_DBC, henv1, &hdbc1)) {       // Couldn't allocate connection       printf("Unable to allocate connection.\r\n");       DumpErrors(SQL_HANDLE_ENV, henv1);       goto CLEANUP;    }     // Connect to master database which is the default if no database is specified    // Here we assume the connection string does not contain the database name already.    printf("Connecting to server master database using connection string: %s\r\n", pszConnStr);    if (!(SQL_SUCCEEDED(SQLDriverConnect(hdbc1, NULL, (SQLCHAR *)pszConnStr, SQL_NTS, (SQLCHAR *)szOutConnStr, sizeof(szOutConnStr), &cbOutConnStr, SQL_DRIVER_NOPROMPT)))) {       // Couldn't connect       printf("Unable to connect to server using connection string %s.\r\n", pszConnStr);       DumpErrors(SQL_HANDLE_DBC, hdbc1);       goto CLEANUP;    }     printf("\tConnected!\r\n");     // Allocate hstmt    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1)) {       // Couldn't allocate statement       printf("Unable to allocate statement.\r\n");       DumpErrors(SQL_HANDLE_DBC, hdbc1);       goto CLEANUP;    }     SQLExecDirect(hstmt1, (SQLCHAR *)"drop database MySampleDB", SQL_NTS);    if (SQL_SUCCESS != SQLExecDirect(hstmt1, (SQLCHAR *)"create database MySampleDB", SQL_NTS)) {       // Couldn't create table       printf("Database creation failed.\r\n");       DumpErrors(SQL_HANDLE_STMT, hstmt1);       goto CLEANUP;    }     // Add the user database to the connection string, again assuming it does not contain a database keyword already    ulMemSize = (strlen(pszConnStr) + strlen(";Database=MySampleDB"))*sizeof(char);    pszUserConnStr = (char *)malloc(ulMemSize);     // Assume memory allocation succeeded    sprintf(pszUserConnStr, "%s;Database=MySampleDB", pszConnStr);     // Disconnect so we can now connect to the user database    printf("\"Use <database>\" command is not supported on Azure SQL Database. Disconnecting from master database and reconnecting to user database using connection string:\r\n\t%s\r\n", pszUserConnStr);    SQLDisconnect(hdbc1);     // Connect to user database    if (!(SQL_SUCCEEDED(SQLDriverConnect(hdbc1, NULL, (SQLCHAR *)pszUserConnStr, SQL_NTS, (SQLCHAR *)szOutConnStr, sizeof(szOutConnStr), &cbOutConnStr, SQL_DRIVER_NOPROMPT)))) {       // Couldn't connect       printf("Unable to connect to server using connection string %s.\r\n", pszUserConnStr);       DumpErrors(SQL_HANDLE_DBC, hdbc1);       goto CLEANUP;    }     // Allocate hstmt again    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1)) {       // Couldn't allocate statement       printf("Unable to allocate statement.\r\n");       DumpErrors(SQL_HANDLE_DBC, hdbc1);       goto CLEANUP;    }     // Create a table    // Write to the screen    printf("Creating a table.\r\n");    if (SQL_SUCCESS != SQLExecDirect(hstmt1, (SQLCHAR *)"create table MySampleTable(col1 int primary key, col2 char(30))", SQL_NTS)) {       // Couldn't create table       printf("Table creation failed.\r\n");       DumpErrors(SQL_HANDLE_STMT, hstmt1);       goto CLEANUP;    }     // Insert a row    // Write to the screen    printf("Inserting a row.\r\n");    if (SQL_SUCCESS != SQLExecDirect(hstmt1, (SQLCHAR *)"insert into MySampleTable(col1, col2) values (1, ' Azure SQL Database Rocks')", SQL_NTS)) {       // Unable to insert data       printf("Inserting into table failed.\r\n");       DumpErrors(SQL_HANDLE_STMT, hstmt1);       goto CLEANUP;    }     // Select the data    // Write to the screen    printf("Executing select statement from the table.\r\n");    if (SQL_SUCCESS != SQLExecDirect(hstmt1, (SQLCHAR *)"select col1, col2 from MySampleTable", SQL_NTS)) {       // Unable to retrieve data       printf("Select statement failed.\r\n");       DumpErrors(SQL_HANDLE_STMT, hstmt1);       goto CLEANUP;    }     // Write to the screen    printf("Retrieving selected data.\r\n");     // Fetch a row    while (SQL_SUCCESS == (rc = SQLFetch(hstmt1))) {       for (int iCol = 0; iCol < 2; iCol++) {          char szBuf[100];          SQLLEN IndPtr;           // Retrieve the column          if (SQL_SUCCESS != SQLGetData(hstmt1, iCol+1, SQL_C_CHAR, szBuf, sizeof(szBuf), &IndPtr)) {             // Unable to retrieve data             printf("\r\nRetrieving column data failed.\r\n");             DumpErrors(SQL_HANDLE_STMT, hstmt1);             goto CLEANUP;          }           printf(szBuf);          printf("\t");       }                 }     if (SQL_NO_DATA != rc) {       // Unable to retrieve data       printf("Fetching row failed.\r\n");       DumpErrors(SQL_HANDLE_STMT, hstmt1);       goto CLEANUP;    }    printf("\r\nAll rows retrieved.\r\n");      // Disconnect so we can now connect to the master database again to drop the user database    // Write to the screen    printf("\r\nDisconnect again and connect to master database to drop the database we created.\r\n");    SQLDisconnect(hdbc1);    // Connect to master database which is the default if no database is specified    if (!(SQL_SUCCEEDED(SQLDriverConnect(hdbc1, NULL, (SQLCHAR *)pszConnStr, SQL_NTS, (SQLCHAR *)szOutConnStr, sizeof(szOutConnStr), &cbOutConnStr, SQL_DRIVER_NOPROMPT)))) {       // Couldn't connect       printf("Unable to connect to server using connection string %s.\r\n", pszConnStr);       DumpErrors(SQL_HANDLE_DBC, hdbc1);       goto CLEANUP;    }     // Allocate hstmt    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1)) {       // Couldn't allocate statement       printf("Unable to allocate statement.\r\n");       DumpErrors(SQL_HANDLE_DBC, hdbc1);       goto CLEANUP;    }     // Drop the user database    if (SQL_SUCCESS != SQLExecDirect(hstmt1, (SQLCHAR *)"drop database MySampleDB", SQL_NTS)) {       // Unable to retrieve data       printf("Drop database failed.\r\n");       DumpErrors(SQL_HANDLE_STMT, hstmt1);       goto CLEANUP;    }     printf("\r\nCompleted...\r\n");  CLEANUP:    // Clean up before exiting;    if (hstmt1 != SQL_NULL_HSTMT)       SQLFreeStmt(hstmt1, SQL_DROP);     if (hdbc1 != SQL_NULL_HDBC) {       SQLDisconnect(hdbc1);       SQLFreeConnect(hdbc1);    }     if (henv1 != SQL_NULL_HENV)       SQLFreeEnv(henv1);     return 0; }  void usage() {    printf("Usage: SQLAzureODBCSample ConnectionString\r\n");    printf("\tMust specify an ODBC DriverConnect connection string in quotes as the only argument and it must not contain the database keyword for this sample to work.\r\n");    printf("\tExample: SQLAzureODBCSample  \"DRIVER=ODBC Driver 11 for SQL Server;Server=myserver.database.windows.net;UID=myuserid;PWD=mypwd\"\r\n");    exit(0); }  void DumpErrors(SQLSMALLINT HandleType, SQLHANDLE Handle) {    SQLSMALLINT iRec = 1;    SQLCHAR SQLState[10];    SQLINTEGER NativeError;    SQLCHAR MessageText[512];     printf("\r\nErrors occurred:\r\n");     while (SQL_SUCCESS == SQLGetDiagRec( HandleType, Handle, iRec, SQLState, &NativeError, MessageText, sizeof(MessageText), NULL)) {       printf("\tSQLState: %s, Native Error: %d\r\n", SQLState, NativeError);       printf("\tMessage: %s\r\n", MessageText);        iRec++;    } }

このプログラムの出力:

Connecting to server master database using connection string: DRIVER=ODBC Driver 11 for SQL Server;Server=myserver.int.mscds.com;UID=myID;PWD=$ql$erver51         Connected! "Use <database>" command is not supported on Azure SQL Database. Disconnecting from master database and reconnecting to user database using connection string:         DRIVER=ODBC Driver 11 for SQL Server;Server=myserver.int.mscds.com;UID=myid;PWD=$ql$erver51;Database=MySampleDB Creating a table. Inserting a row. Executing select statement from the table. Retrieving selected data. 1       Azure SQL Database Rocks All rows retrieved.  Disconnect again and connect to master database to drop the database we created.  Completed...

関連項目

表示:
© 2015 Microsoft