Click here to Skip to main content
15,121,540 members
Articles / Database Development / SQL Server
Article
Posted 13 May 2002

Stats

146.4K views
2.2K downloads
34 bookmarked

Connecting to SQL Server

Rate me:
Please Sign up or sign in to vote.
3.76/5 (17 votes)
13 May 2002CPOL2 min read
This article explains how to connect to an SQL Server and pass a string.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


Comments and Discussions

 
QuestionAre you sure you're testing your code? Pin
brobert2324-Oct-07 6:49
Memberbrobert2324-Oct-07 6:49 
QuestionCompletely lost on database project Pin
brobert2323-Oct-07 11:11
Memberbrobert2323-Oct-07 11:11 
Generalhelp or guidance Pin
ronin_will10-May-07 11:13
Memberronin_will10-May-07 11:13 
Generalhelp! Pin
madhu_USC20-Nov-05 14:29
Membermadhu_USC20-Nov-05 14:29 
QuestionHow can I get the result from "select"? Pin
dbyabcxyz30-Apr-04 13:29
Memberdbyabcxyz30-Apr-04 13:29 
QuestionHow to show the result Pin
sleeperfung31-Dec-03 9:40
Membersleeperfung31-Dec-03 9:40 
GeneralThat's pretty good Pin
anhtrung2-Oct-03 1:38
Memberanhtrung2-Oct-03 1:38 
Generalquestion Pin
s_man12-Aug-03 11:04
Members_man12-Aug-03 11:04 
GeneralVB beginner! Pin
22-May-02 10:05
suss22-May-02 10:05 
Generaldyn. make ODBC connection Pin
Lars Dirks20-May-02 22:34
MemberLars Dirks20-May-02 22:34 
GeneralSpeed Difference Pin
Andrew Bleakley15-May-02 0:30
MemberAndrew Bleakley15-May-02 0:30 
GeneralRe: Speed Difference Pin
eXplodus15-May-02 3:15
MembereXplodus15-May-02 3:15 
Generalquestion Pin
eXplodus14-May-02 21:03
MembereXplodus14-May-02 21:03 
i use this connection style a long time now ...

any idea how i could count the rows from a result.
(!update || !insert || !...) && select ...


General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.