• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

My check table exist function does not work,... any ideas..??

hi guys,

I hv written a check table function that will written a integer code to show if it exists or not using ODBC APIs...
it does not work... always return 0...!
What is wrong?

-----------------------------------

long COdbc::CheckTable (char *szTableName)
{
    SQLCHAR            scName[129];
      SQLHANDLE      handle;

      // Allocate a handle, v3.5 compliant

    SQLRETURN rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &handle);

    if (rc == SQL_SUCCESS)
      {
            rc = SQLTables(handle, NULL, 0, NULL, 0,
                 (SQLCHAR *) szTableName, SQL_NTS, NULL, 0);

            if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
                  return 0; // table do exist
            else
                  return -1; // table do not exist

            // Free the handle
         if (handle != NULL)
            SQLFreeHandle(SQL_HANDLE_STMT, handle);
      }


}

--------------------------------

Thanks
0
Haho
Asked:
Haho
1 Solution
 
V_BapatCommented:
I think it is working!!!

This is a part of your code.

if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
return 0; // table do exist

This function returns 0 when SqlTables returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. What is the problem?

Vicky
0
 
V_BapatCommented:
What you may do is that when SQLTables returns SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle.

For more help, refer SQLTables in MSDN help.

Vicky
0
 
HahoAuthor Commented:
hi guys (and bapat)
 
the problem is that even if the table DOES not exist, it will still return 0!!
If that is the case, I cannot know if the table exists or doesn't exists!! (which is the purpose of the function)

Please reply ASAP.. Thanks :)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
V_BapatCommented:
Return value 0 means that the call to SQLTables() is successful.
What is the value of szTableName when you call SQLTables()?
0
 
captainkirkCommented:
You might try a query against the system objects table which will contain the names of existing tables in your database. For example, in SQL Server, you would do something like:

select name from sysobjects where name="yourtablename";


Programmatically you would code that to be carried out by whatever method you are using to execute queries.
0
 
carldeanCommented:
Haho,

Providing you've not messed up the parameters to SQLTables it will always return a success state.  What you need to do after your call to SQLTables is perform a SQLFetch to determine whether any results were returned.

rc == SQLTables(handle,
                NULL,
                0,
                NULL,
                0,
                (SQLCHAR*) szTableName,
                SQL_NTS,
                NULL,
                0);

if (rc != SQL_SUCCESS &&
    rc != SQL_SUCCESS_WITH_INFO) {
   // An error has occurred - still
   // don't know if tables exists though
}
else {
   // No try to fetch results
   //
   rc = SQLFetch(handle);
   if (rc == SQL_NO_DATA_FOUND) {
      // It definately doesn't exist
   }
   else {
      // Yippee I've found it!!!!
   }
}

Hope this helps
0
 
HahoAuthor Commented:
carldean, exactly what I was looking for.... will use it and see if it works.. :)
0
 
HahoAuthor Commented:
tq
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now