Connecting to SQL Server






3.76/5 (13 votes)
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:
- Create a table named test.
- Provide a Username and Password to connect to it.
- 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:
- Go to Control Panel/Administrative Tools/Data Sources(ODBC).
- In the User DSN tab, click Add. In the list that appears, select SQL Server and click Finish.
- In the first step of the DNS Configuration wizard that pops up, give any name you want to identify your DSN.
- Select the server on which the database exists, click Next.
- Select the radio button for SQL Server authentication using a Login ID and Password.
- In the Client configuration command button, select TCP/IP.
- Check the box for 'Connect to SQL Server to obtain default settings' for the additional configuration options.
- Provide the Username and Password your DBA has provided, click Next.
- Check the box 'Change the default database to' and enter the name of your table.
- 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:
- Open a new database project.
- Perform the same steps as you did for setting up the DSN when the wizard pops up.
- Click on your table and run the default SQL statement through the toolbar.
- You will find the string you sent in the above program in the table.
That's it
Write to me for any queries/suggestions.