Eksportér (0) Udskriv
Udvid alt
EN
Dette indhold er ikke tilgængeligt på dit sprog, men her er den engelske version.

How to: Connect to Azure SQL Database Using ODBC

Updated: February 10, 2014

This topic describes how to connect to a Microsoft Azure SQL Database using ODBC, and provides an example console application. You can use one of the following ODBC versions:

When using SQL Server Native Client from SQL Server 2008 R2 or SQL Server 2008, the connection string must include the server name as part of the user name (user@server).

You must use TCP/IP as the protocol when connecting to a Microsoft Azure SQL Database.

You cannot create a table in the master database, so therefore you must create a user database to create a table.

You cannot execute a use database command to switch to your user database. You must disconnect and connect directly to the user database.

You must have a primary key or clustered index on your table to be able to insert data.

For more information about using ODBC, see:

Code Sample for the ODBC Driver on Windows

The following sample can be compiled as a console application. To compile, you will need SQL Server Native Client from either SQL Server 2012 or SQL Server 2008 R2. When you run the application, specify the connections string. This sample creates a database and table. Error handling is minimal, and there is no connection-retry logic.

//
//  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++;
       }
}

Code Sample for the ODBC Driver on Linux

The following sample can be compiled as a console application. When you run the application, specify the Driver, Server, UID and PWD connection string keywords. This sample creates a database and table.

//  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++;
   }
}

The output of this program is:

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...

See Also

Fællesskabsindhold

Tilføj
Vis:
© 2014 Microsoft