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

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

更新日: 2014年2月

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

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

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

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

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

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

ODBC の使い方の詳細については、「」を参照してください。

次のサンプルは、コンソール アプリケーションにコンパイルできます。コンパイルには、SQL Server 2012 または SQL Server 2008 R2 の SQL Server Native Client が必要です。アプリケーションを実行するときには、接続文字列を指定します。この例では、データベースとテーブルを作成します。エラー処理は最小限となっており、接続再試行ロジックはありません。

//
//  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 nane 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  \"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 nane 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...

関連項目

表示:
© 2014 Microsoft