Exporter (0) Imprimer
Développer tout

Procédure : se connecter à la Base de données SQL Azure à l'aide d'ODBC

Mis à jour: février 2014

Cette rubrique décrit la façon de se connecter à une Base de données SQL Microsoft Azure au moyen d'ODBC et fournit un exemple d'application console. Vous pouvez utiliser l'une des versions d'ODBC suivantes :

Lors de l'utilisation de SQL Server Native Client depuis SQL Server 2008 R2 ou SQL Server 2008, la chaîne de connexion doit inclure le nom de serveur en tant que partie du nom d'utilisateur (utilisateur@serveur).

Vous devez utiliser le protocole TCP/IP lors de la connexion à une Base de données SQL Microsoft Azure.

Vous ne pouvez pas créer de table dans la base de données master ; par conséquent, vous devez créer une base de données utilisateur pour créer une table.

Vous ne pouvez pas exécuter de commande use database pour basculer vers votre base de données utilisateur. Vous devez vous déconnecter et vous connecter directement à la base de données utilisateur.

Vous devez disposer d'une clé primaire ou d'un index cluster sur votre table pour insérer des données.

Pour plus d'informations sur l'utilisation d'ODBC, consultez :

L'exemple suivant peut être compilé en tant qu'application console. Pour compiler, vous avez besoin de SQL Server Native Client depuis SQL Server 2012 ou SQL Server 2008 R2. Lorsque vous exécutez l'application, spécifiez la chaîne de connexion. Cet exemple crée une base de données et une table. La gestion des erreurs est minimale et il n'y a aucune logique de tentative de connexion.

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

L'exemple suivant peut être compilé en tant qu'application console. Lorsque vous exécutez l'application, spécifiez les mots clés de chaîne de connexion Driver, Server, UID et PWD. Cet exemple crée une base de données et une 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++;
   }
}

Sortie de ce programme :

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

Voir aussi

Afficher:
© 2014 Microsoft