• (2 Pages)
  • +
  • 1
  • 2

CPP with ODBC (Open Database Connectivity) CPP connection with a database Rate Topic: ***-- 2 Votes

#1 eXceed69  Icon User is offline

  • "Super Sentai Knight Of DawN"
  • member icon

Reputation: 7
  • View blog
  • Posts: 688
  • Joined: 12-November 06

Posted 25 November 2006 - 05:42 AM

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

This are actual guides

Note:

Quote

Before I start just to introduce some SQL commands and stuff. For starter you had some back grounds on it….

SQL (Structured Query Language) is a fourth-generation language (4GL) that is used to define, manipulate, and control an RDBMS (relational database management system).

DML sublanguage is concerned with commands that can perform operations on data in tables.It provides four commands for data manipulation:

SELECT command retrieves data for queries.

INSERT command adds new rows into a database table.

UPDATE command changes the values of data items in a database table.

DELETE command removes rows from a database table.


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

//Header files:

#include <sql.h>
#include<sqltypes.h>
#include<sqlext.h>
...
....

//Declaration:

SQLHandle hdlEnv, hdlConn, hdlStmt, hdlDbc
char* stmt = "SELECT * from NutHead"; //SQL statement NutHead is the Table name 

//for example
char *dsnName = “COLLECTOR”  name of your program or what ever….. 
char* userID = "eXceed";
char* passwd = "hole";
char* retVal[256];
unsigned int cbData;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hdlEnv); 
SQLSetEnvAttr(hdlEnv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC30);
SQLAllocHandle(SQL_HANDLE_DBC, hdlEnv, &hdlConn); 
SQLConnect(hdlConn, (SQLCHAR*)dsnName,SQL_NTS,(SQLCHAR*)userID,SQL_NTS, (SQLCHAR*)passwd, SQL_NTS);
SQLAllocHandle(SQL_HANDLE_STMT, hdlDbc, &hdlStmt);
SQLExecDirect(hdlStmt, (SQLCHAR*)stmt, SQL_NTS); 


//Initialize the database connection

while(SQLFetch(hdlStmt) == SQL_SUCCEEDED)
{
  SQLGetData(hdlStmt,0,DT_STRING,retVal,256,&cbData);
  std::cout << retVal << std::endl;
}
SQLFreeHandle(SQL_HANDLE_STMT, hdlStmt);
SQLFreeHandle(SQL_HANDLE_DBC, hdlConn); 
SQLFreeHandle(SQL_HANDLE_ENV, hdlEnv);  //End the connection



Here some helpful links:
SQLAPI connectivity

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


Is This A Good Question/Topic? 0
  • +

Replies To: CPP with ODBC (Open Database Connectivity)

#2 Dark_Nex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 22-December 06

Posted 22 December 2006 - 01:11 AM

You had a Interesting tutorials, nice work for a beatiful lady :D
Was This Post Helpful? 0
  • +
  • -

#3 fbaroud  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 01-July 07

Posted 01 July 2007 - 01:21 AM

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
Was This Post Helpful? 0
  • +
  • -

#4 NewP  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 07-August 07

Posted 07 August 2007 - 03:04 PM

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
Was This Post Helpful? 0
  • +
  • -

#5 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1865
  • View blog
  • Posts: 20,278
  • Joined: 17-March 01

Posted 07 August 2007 - 03:07 PM

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

Good luck with your problem.
Was This Post Helpful? 0
  • +
  • -

#6 NewP  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 07-August 07

Posted 09 August 2007 - 08:07 AM

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




View Postskyhawk133, on 7 Aug, 2007 - 03:07 PM, said:

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

Good luck with your problem.

Was This Post Helpful? 0
  • +
  • -

#7 ankit_k7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 29-October 07

Posted 29 October 2007 - 05:00 AM

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


from where i get these files?
Was This Post Helpful? 0
  • +
  • -

#8 victorsan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 23-January 08

Posted 23 January 2008 - 08:14 PM

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........
Was This Post Helpful? 0
  • +
  • -

#9 Pam  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 15-May 08

Posted 15 May 2008 - 02:41 AM

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
Was This Post Helpful? 0
  • +
  • -

#10 MainMa  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 5
  • Joined: 26-July 08

Posted 26 July 2008 - 04:46 AM

Quote

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
...


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


Quote

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?

http://msdn.microsof...810(VS.85).aspx might be helpful.

This post has been edited by MainMa: 26 July 2008 - 04:47 AM

Was This Post Helpful? 0
  • +
  • -

#11 MainMa  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 5
  • Joined: 26-July 08

Posted 26 July 2008 - 05:08 AM

View Postankit_k7, on 29 Oct, 2007 - 05:00 AM, said:

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

from where i get these files?

For Windows platform, these files are included in Microsoft SDK.
Was This Post Helpful? 0
  • +
  • -

#12 MainMa  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 5
  • Joined: 26-July 08

Posted 26 July 2008 - 05:24 AM

View Postfbaroud, on 1 Jul, 2007 - 01:21 AM, said:

hi how are you?

10xs for your tutorial, it s very helpful.

...

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?

  • The author do not precise that we must include windows.h at the beginning to make the code work.
  • 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).
  • 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 ?...
  • 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.
  • What about using SQLDisconnect() at the end of the script?
  • In final code, the author uses iostream library. But iostream.h is never included before.
  • The code is not well-formatted: #include <sql.h> with space, #include<sqltypes.h> without space; sometimes we have char* variable, sometimes char *variable...
  • In final code, char *dsnName = “COLLECTOR” will throw "undeclared identifier" error.

Was This Post Helpful? 1
  • +
  • -

#13 vikas0356  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 17-February 09

Posted 17 February 2009 - 02:44 AM

that was very helpful. but i am using the language C and trying to connect mysql database. can you please help me with this..
Was This Post Helpful? 0
  • +
  • -

#14 darmawan efendi  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 28
  • Joined: 03-November 08

Posted 27 July 2009 - 06:11 AM

HI I'm Newbie...
In your tutorial.. Just connect to SQL...
How about Access??
thank you :D
Was This Post Helpful? 0
  • +
  • -

#15 Ancient Dragon  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 81
  • View blog
  • Posts: 679
  • Joined: 19-July 09

Posted 22 September 2009 - 10:00 PM

Sorry for being soooo late with this. But I have to agree with MaiMa in that this is not a very good tutorial for the reasons (s)he posted.

As for darmawan's question: You can use any ODBC-compliant database with this, including MS-Access. You just have to set up the odbc data source in the Windows control panel --> ODBC. For Vista its control-panel --> Administrative Tools --> ODBC

This post has been edited by Ancient Dragon: 22 September 2009 - 10:01 PM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2