Connecting to SQL Server

• Download source files - 2.76 Kb

Introduction

This article explains how to get connected to SQL Server database and then write string in the database table using an SQL statement.

Setting up a database

Ask your DBA - database administrator, to do the following:

1. Create a table named test.
2. Provide a Username and Password to connect to it.
3. Get the name of the server.

Setting up the DSN - Data Source Name

You need to create a Data Source Name which identifies the server and the table to which you have to connect.

Do the following steps to set up a DSN:

1. Go to Control Panel/Administrative Tools/Data Sources(ODBC).
2. In the User DSN tab, click Add. In the list that appears, select SQL Server and click Finish.
3. In the first step of the DNS Configuration wizard that pops up, give any name you want to identify your DSN.
4. Select the server on which the database exists, click Next.
5. Select the radio button for SQL Server authentication using a Login ID and Password.
6. In the Client configuration command button, select TCP/IP.
7. Check the box for 'Connect to SQL Server to obtain default settings' for the additional configuration options.
8. Provide the Username and Password your DBA has provided, click Next.
9. Check the box 'Change the default database to' and enter the name of your table.
10. Accept the defaults and perform the test connection.

Includes

• windows.h
• sqlext.h
• stdio.h
• string.h

Writing code

Open an empty Win32 console application named SQLtry, add a new CPP file Main. Your Main.cpp looks like this:

int main(void)
{
   HENV   hEnv = NULL; // Env Handle from SQLAllocEnv()
   HDBC   hDBC = NULL; // Connection handle
   HSTMT  hStmt = NULL;// Statement handle
   UCHAR  szDSN[SQL_MAX_DSN_LENGTH] = "Test";// Data Source Name buffer
   UCHAR  szUID[10] = "test";// User ID buffer
   UCHAR  szPasswd[10] = "test";// Password buffer
   UCHAR  szModel[128];// Model buffer
   SDWORD cbModel;// Model buffer bytes recieved
   char   buff[9] = "Testing";
   UCHAR  szSqlStr[128]= "INSERT into (Tablename) (ColumnName) Values ('Testing')" ;

   RETCODE retcode;

  //sprintf((char*)szSqlStr,"INSERT into (Tablename)(Columname) Values ('%s')",buff);
  // Allocate memory for ODBC Environment handle
  SQLAllocEnv (&hEnv);

  // Allocate memory for the connection handle
  SQLAllocConnect (hEnv, &hDBC);

  // Connect to the data source "test" using userid and password.
  retcode = SQLConnect (hDBC, szDSN, SQL_NTS, szUID, SQL_NTS, szPasswd, SQL_NTS);

  if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
  {
      // Allocate memory for the statement handle
      retcode = SQLAllocStmt (hDBC, &hStmt);  

      // Prepare the SQL statement by assigning it to the statement handle
      retcode = SQLPrepare (hStmt, szSqlStr, sizeof (szSqlStr)); 

      // Execute the SQL statement handle
      retcode = SQLExecute (hStmt);    

      // Project only column 1 which is the models
      SQLBindCol (hStmt, 1, SQL_C_CHAR, szModel, sizeof(szModel), &cbModel);

      // Get row of data from the result set defined above in the statement
      retcode = SQLFetch (hStmt);

      // Free the allocated statement handle
      SQLFreeStmt (hStmt, SQL_DROP);

      // Disconnect from datasource
      SQLDisconnect (hDBC);
  }    
            
  // Free the allocated connection handle
  SQLFreeConnect (hDBC);  

  // Free the allocated ODBC environment handle
  SQLFreeEnv (hEnv);

  return 0;
}

Testing Your Code

Perform the following steps:

1. Open a new database project.
2. Perform the same steps as you did for setting up the DSN when the wizard pops up.
3. Click on your table and run the default SQL statement through the toolbar.
4. You will find the string you sent in the above program in the table.

That's it

Write to me for any queries/suggestions.