This tutorial show coding how to connect with a database in C++….

This are actual guides

Note:



And let’s start….

ODBC (Open Database Connectivity)

• A standard interface for connecting from C++ to relational databases
• It allows individual providers to implement and extend the standard with their own ODBC drivers

Here are procedures or rather steps used in the industry in C++ coding for connecting to a database

Steps of the ODBC

• Include Header Files
• Open a Connection to a Database
• Choose an ODBC Driver
• Query the Database
• Creating an ODBC Statement Object
• Executing a Query and Returning an ODBCResultSet Object
• Extracting Data from an ODBCResultSet
• Closing the ODBCResultSet and ODBCStatement
• Importance of closing the connection



Okey, this are actual steps how I connect to a database…definitely I used Oracle database, but I don’t want to further say what version it is. LOL


1. Include the Header Files
# include statements at the beginning of your programs:
#include <sql.h>
#include<sqltypes.h>
#include<sqlext.h>

2. Open a Connection to a Database
Set the environment handle:
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv);

Set ODBC Driver version:
SQLSetEnvAttr(hdlEnv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC30);

Set the connection handle:
SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlConn);

Connect to the database:
SQLConnect(hdlConn, (SQLCHAR*)dsnName,SQL_NTS,(SQLCHAR*)userID,SQL_NTS, (SQLCHAR*)passwd, SQL_NTS);

3. Choose Driver
•DSN – Data Source Name
•Open the GUI ODBC Administrator (ODBCConfig)
•Choose the appropriate ODBC driver
•Provide a meaningful name to the DSN
•Specify the server and the host string (host string is required if the server is running on a different machine)

4. Query the Database
Querying the database involves the following steps:

–Creating a Statement
SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);
It allocates the memory for the statement handle. The database handle obtained during connection phase is passed as the second argument.

– Executing a Query
SQLExecDirect(hdlStmt, stmt, SQL_NTS);
It executes the query, which is passed in as SQLCHAR* in the second argument.

5. Extract the Data out of the Executed Query

SQLGetData(hStmt,colNum,type,retVal,buffLength,&cbData);
It extracts data from table as void* data and places it in retVal

colNum refers to the column number provided in the SELECT statement in SQLExecDirect()

Type is one of the standard ODBC data types
example: DT_STRING à for a string data type
DT_DOUBLE à for a double data type

buffLength is the estimated size of the expected data

cbData is the actual size of the data

6. Traverse Through the Results
SQLFetch(hStmt);
Fetches the next record

hStmt is the statement handle obtained using SQLAllocHandle
If a record is available, It returns SQL_SUCCEEDED

7. Close the Statement and the Connection
SQLFreeHandle(SQL_HANDLE_STMT, hdlStmt);
It closes and de-allocates the memory reserved for the statement handle

SQLFreeHandle(SQL_HANDLE_DBC, hdlConn);
It disconnects and de-allocates the memory reserved for the connection handle

SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv);
It de-allocates the memory occupied by the environment handle


For further escalate the whole steps here a sample program




Here some helpful links:
SQLAPI connectivity

You had a Interesting tutorials, nice work for a beatiful lady

hi how are you?

10xs for your tutorial, it s very helpful.

i tried it and i have a problem in the SQL librairies:

ULONG does not name a type
USHORT does not name a type
PVOID does not name a type
....

.....


SQLPOINTER has not been declared
SALCHAR has not been declared
SQLUSMALLINT has not been declared
.
.
.

do you have any idea how to solve these errors. i think i have Microsoft SQL Server Management Studio Express on my computer.

10xs for ur help

Regards,

Is there any way to do these sme things in C#? I want ot use SQLTables() API but I don't know what to pass for SQLHSTMT ..Is there a way to add <sql.h> etc to C# class?

Thanks
NewP

Not sure if this is what you want, but there is a C# tutorial on connecting to SQL here: http://www.dreamincode.net/forums/showtopic31314.htm

Good luck with your problem.

I wanted to use these ODBC APIs in C#, but found out that we can't. Because there is no way to include header files that are required to use APIs. There is still a way to find out Database schema information using odbcconnection.getschema() method ....

This solved my problem

include<sqltypes.h>
include<sqlext.h>


from where i get these files?

If I want to connect to the ms sql server that is located on the server is running on a different machine, How? Please provide me a sample code.

Thanks.

Victor........

Hi,

I tried also the code sample to connect to my database with odbc but I had the same error as one mentioned above. The problem is in the SQL librairies:

ULONG does not name a type
USHORT does not name a type
PVOID does not name a type
....

.....


SQLPOINTER has not been declared
SALCHAR has not been declared
SQLUSMALLINT has not been declared
.
.
.

How can I solve it? Please, help me.

And I have another question also: what should I put in char *dsnName? The name of the data source file, the name of the hostname or what?

I hope someone can help me with this as soon as possible. I have to finish a project soon.

Thanks

Pam

If you develop under Windows, put #include <windows.h> before other includes (sql.h, sqlext.h...).



http://msdn.microsoft.com/en-us/library/ms711810(VS.85).aspx might be helpful.

For Windows platform, these files are included in Microsoft SDK.

Well, I disagree with this you. In my humble opinion, this tutorial needs a deep and complete rewriting to be used for learning purposes. Why?

1. The author do not precise that we must include windows.h at the beginning to make the code work.
2. The author do not precise that his code is not unicode-compatible. As is, it will never work with a default C++ solution created in Visual Studio 2005/2008 with default settings (unicode is default).
3. Before the final source code, when the author uses variables/pointers, we do not know neither their types, nor their initial values, allocations and so on. For example, in SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv), what is hdlEnv ?...
4. There is no error check (!!!). What about checking a return value of SQLConnect before SQLGetData...? Even worse, SQLFreeHandle() is used without checking if handles were created correctly.
5. What about using SQLDisconnect() at the end of the script?
6. In final code, the author uses iostream library. But iostream.h is never included before.
7. The code is not well-formatted: #include <sql.h> with space, #include<sqltypes.h> without space; sometimes we have char* variable, sometimes char *variable...
8. In final code, char *dsnName = “COLLECTOR” will throw "undeclared identifier" error.