exactly what does the statement handle(hstmt) do? in ODBC

Question

I`m new to write c code for ODBC but here I have question... after searching for the tutorial for odbc function tutorials for C, I still caanot get exactly how they work collaborately...

what does the statement handle hstmt exactly do for database data? I knew it controls the SQL query statements that I give as a parameter. but,, then every SQL statements that i give as query should have each statement handle(hstmt) for each of them? or one statement handle for several SQL query statements?

for example,

lstrcpy((LPTSTR)update, L"insert into employee values  ('Dshong','summer','LosAngeles');");
SQLExecDirect(hstmt1, update, SQL_NTS);

lstrcpy((LPTSTR)update, L"insert into works values ('Dshong','Small Bank',    2500);");
SQLExecDirect(hstmt3, update1, SQL_NTS);

lstrcpy((LPTSTR)select, L"select * from works;");
if (SQLExecDirect(hstmt, select, SQL_NTS) != SQL_SUCCESS)
    return printf("can’t exec direct");


lstrcpy((LPTSTR)select1, L"select * from employee;");
if (SQLExecDirect(hstmt2, select1, SQL_NTS) != SQL_SUCCESS)
    return printf("can’t exec direct");

should I give like this for two insert statements and two select statements with four hstmt?

and if I give like this,

lstrcpy((LPTSTR)insert, L"insert into employee values ('Dshong','summer','LosAngeles');");
SQLExecDirect(hstmt1, insert, SQL_NTS);

the SQLExecDirect has hstmt1 as parameter but I don`t know how SQLExecDirect works with it..

And as I think, the result should be one line inserted, isnt it?

but when I run the code, it inserts multi-same rows for ('Dshong','summer','LosAngeles')... when I printed out the result, ('Dshong','summer','LosAngeles') was inserted multiple times even though the insert line was not in the for loop or so...

and how they work together with the functions below?

• SQLBindCol(hstmt, 1, SQL_C_CHAR, id, (SDWORD)sizeof(id), &idlen);
• SQLFetch(hstmt)

I know what they simply doing but.. here I want to know exactly in which way hstmt ( statement handle) work with them..

thank you.. :)

Answer 1

A handle in C is just a pointer to a chunk of memory that's allocated by ODBC. Typically a struct that keeps track of the state of a SQL query. You don't have much use for the handle in an INSERT statement, you use it just once. Unless it fails, then you need to pass the exact same handle to SQLError() to find out what went wrong. Which ODBC then uses to access that internal struct and retrieve the error code. The advantage of a handle over a struct pointer is that it hides the internal implementation.

You certainly need to use it repeatedly for a SELECT query since you want to retrieve the query result. You must pass the exact same handle to SQLBindCol() to map a column in the query result to a memory location. And to SQLFetch() to retrieve a row.

If you know C++ then it can be helpful to think of a handle as the this object pointer. And SQLAllocStmt() as the constructor. And SQLFetch() as an instance method of the class. And finally SQLFreeHandle() as the destructor. Which is exactly what a C++ ODBC wrapper class does, like MFC's CRecordSet, CDatabase to wrap a SQLHDBC handle.

In most cases you only use a single SQLHSTMT handle. Unless you run multiple SQL statements at the same time. Say to generate UPDATE statements from a SELECT query, that requires two handles.