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

This post has been edited by eXceed69: 26 Nov, 2006 - 08:22 PM

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