Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ODBC Driver problem.. Please help me...

Posted on 1998-09-09
3
Medium Priority
?
333 Views
Last Modified: 2010-04-02
Hello Experts,

I got problem with this application that are connect to ODBC drivers.I had develop a function call SearchData(ITEMCODE[]) in Visual C++ which involved accessing tables from Microsoft Access Database. And the tables each consists of 100 records. The API I used to pass parameters (ITEMCODE) to the function is develop in VB5.0. The sample code in VB is very simple as below:
   Do Until EOF()
      ITEMCODE = rc("fldItemCode")
      value = SearchData(ITEMCODE)
  Loop

The first time I run this application which loop around 100 times(100 records from same table involve in SearchData), it manage to produce the right output. But the second time, it produce junk..... When I use different computer to process this appllication, it manage to go only 2 round...and then it produce junk... When I debug thru the SearchData function, I discover that the ODBC driver were disconnected
(rc = -1). I try to assign const rc = 1(force to connect) but still the same, unless I quit application and rerun again.


The code that I used to connect ODBC driver is as below:

SQLHENV henv = NULL;
SQLHDBC hdbc = NULL;
SQLHSTMT hstmt = NULL;
SQLRETURN rc;

if (SQL_SUCCESS ==
   (rc = ::SQLAllocHandle(SQL_HANDLE_ENV,
   SQL_NULL_HANDLE, &henv)))
      {
     if (SQL_SUCCESS == (rc = ::SQLSetEnvAttr(henv,
         SQL_ATTR_ODBC_VERSION,(SQLPOINTER)
         SQL_OV_ODBC3, SQL_IS_INTEGER)))
            {
             if (SQL_SUCCESS == (rc =                     ::SQLAllocHandle(SQL_HANDLE_DBC, henv,                     &hdbc)))
            {
               rc = ::SQLConnect(hdbc, (SQLCHAR*)"Bank                         Negara Formula Database", SQL_NTS,
                  (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"",                          SQL_NTS);
               if ((SQL_SUCCESS == rc)
                  || (SQL_SUCCESS_WITH_INFO == rc))
               {
                  bIsConnected = 'T';
                  if (SQL_SUCCESS == (rc                        = ::SQLAllocHandle(SQL_HANDLE_STMT,
                  hdbc, &hstmt)))
                  {
                                                strcpy(statement,"SELECT fldTag FROM tblItemCode ");
      strcat(statement, "where flditemcode = ");
      strcat(statement,"'");
      strcat(statement, ITEMCODE);
      strcat(statement,"'");
      LPCSTR szSQL = statement;

      if (SQL_SUCCESS == (rc = ::SQLPrepare(hstmt,
      (unsigned char*)szSQL, SQL_NTS)))
      {
      if (SQL_SUCCESS == (rc = ::SQLExecute(hstmt)))
      {
        SDWORD cb;
          char szItmTag[LEN_TAG];
        SQLBindCol(hstmt, 1, SQL_C_CHAR, szItmTag,
            LEN_TAG, &cb);
         rc = SQLFetch(hstmt);
          ITEMCODE[LEN_ITEMCODE]='\0';
            if (rc == SQL_SUCCESS)
            {
            while (SQL_SUCCESS == rc)
            {
            strcpy(itmtag, szItmTag);
            rc = SQLFetch(hstmt);
            } // end while SQL_SUCCESS
          }

Can some expert help me in or show me the better way of connecting to ODBC driver. I really appreciate it. Thank you.
0
Comment
Question by:dekoay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Accepted Solution

by:
bobbym earned 100 total points
ID: 1172323
I think the problem with this function is that is does not free the handles it gets from the ODBC driver (this by the way also explains why the functions runs and then suddenly stops). To correct the problem you must free all the handles that you use using SQLFreeHandle passing the function the handle and the appropriate type of the handle. Please note that you must free the handles in a reverse order e.g. the last allocated gets freed first!

It is also preferable to keep a global copy of the connection and environment handles for performance reasons if you use this functions often, because the ODBC driver will not have to do the additional work of creating a connection and environment.

An easier for sure method is to use some library implementation for working with ODBC (like MFC) and leave the dirty work.

I hope this helped :).

 

0
 

Author Comment

by:dekoay
ID: 1172324
Hello Bobbyn,

Yes, i did free all handle that I created, it just i diddn't show them here becoz the program is very long. However, a new discoveries has occur after i made some modification to the stack I created. This time it loops until 5 times before it return me a junk value. Now, I suspected that it causes by memory problem. Is there a way I can destroy the stack I created after used ?

Thank You, Bob. Yur help is truly appreciated.

Regards
Dekoay
0
 

Expert Comment

by:bobbym
ID: 1172325
Dear Dekoay,
I'm not quite sure that I'm getting the idea (about the stack), but the problem is obviously connected with the handles (since you have to re-run the program to make it run again.
I see that you are using a lot ot strcat, strcpy and so on. I think it's a good idea to check wheather those functions do not mess the memory, especially if for example the statement and itmtag variables are declared before the handles. Make sure you do not overwrite the memory for the handles, cause as I can see they are declared after those two and so reside after them in the stack.
Can you move for example all the handles above in the stack and try to increase the size of the string variables. If you get  more rows without junk maybe that is the problem :) (maybe not) :)

Best regards Bob
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Often, when implementing a feature, you won't know how certain events should be handled at the point where they occur and you'd rather defer to the user of your function or class. For example, a XML parser will extract a tag from the source code, wh…
Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question