免费试用 *Internet Service Required
此主题尚未评级 - 评价此主题

如何使用 ODBC 连接到 Windows Azure SQL Database

本主题中的信息说明如何使用 ODBC 连接到 Windows Azure SQL Database。本主题介绍了连接 Windows Azure SQL Database 的一些注意事项，并提供了一个您可以用来作为开始的示例控制台应用程序。

在 SQL Server Native Client 或 SUSE Linux 上的 ODBC 驱动程序（当前为 CTP）中使用 ODBC 连接到 Windows Azure SQL Database 时：

  • 当从 SQL Server 2008 R2 或 SQL Server 2008 中使用 SQL Server Native Client 时，连接字符串必须包括服务器名称作为用户名的一部分 (user@server)。

  • 连接到 Windows Azure SQL Database 时，必须使用 TCP/IP 作为协议。

  • 您不能在 master 数据库中创建表，因此您必须创建一个用户数据库来创建表。

  • 您不能执行 use database 命令来切换到您的用户数据库。您必须直接断开和连接用户数据库。

  • 您必须在表中包含主密钥或聚集索引才能插入数据。

有关在 SQL Server Native Client 中使用 ODBC 的详细信息，请参见：

Windows 上 ODBC 驱动程序的代码示例

下面的示例可以编译为一个控制台应用程序。若要编译，您将需要 SQL Server 2012 或 SQL Server 2008 R2 中的 SQL Server Native Client。当您运行应用程序时，指定连接字符串。此示例将创建一个数据库和表。只有最少量的错误处理，并且没有重试连接逻辑。

复制 
//
//  PROGRAM: SQLAzureODBCSample
//      (C) Microsoft Corporation. All rights reserved
//
//  PURPOSE: Allow user to connect to Windows 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 Windows 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, ' Windows 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++;
       }
}

Linux 上 ODBC 驱动程序的代码示例

下面的示例可以编译为一个控制台应用程序。当您运行应用程序时，指定驱动程序、服务器、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"
//
//      (C) Microsoft Corporation. All rights reserved
//
//  PURPOSE: Allow user to connect to Windows 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 Windows 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, ' Windows 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 Windows 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       Windows Azure SQL Database Rocks
All rows retrieved.

Disconnect again and connect to master database to drop the database we created.

Completed...

另请参见

本文是否对您有所帮助？
(1500 个剩余字符)

社区附加资源

添加
© 2013 Microsoft. 版权所有。
facebook page visit twitter rss feed newsletter