ODBC API

[Valius]

Hello all, I have a problem that I can't figgure out and maybe you guys can help. I've been using the ODBC API (sql.h) in C++ to connect to a SQL database. Well, the interesting thing is that I can connect to the pubs DBC and execute a SQL statement and see how many columns are returned. Well, when I change it to a different DBC and execute the SQL statement I get error 42S02 (Base table or view not found) and I can't figgure out why. I've double and triple checked my SQL Statment...it's just a simple SELECT * FROM <table> and I KNOW the table is there. I have checked for errors for every step I take to connect to the SQL server and there are none whatsoever....except when I make the actual connection I get warning of 01000...but MSDN said that it was just a general warning so I shouldn't be too concerned about it. I'm just trying to figgure out why this would work for one DBC but not for another. Any suggestions? Thanks in advance!

Niky Williams
NTS Marketing Inc.

 

[Cagliostro]

What are you doing to set other DBC?
1. SQLDisconnect/SQLConnect(Other DBC) or
2. SELExec*(.....&quot;use otherDatabase&quot;..)? John Fill

ivfmd@mail.md

 

[Valius]

Here are the steps that I take:

ConnectionString = new char[strlen(&quot;DRIVER={SQL Server};SERVER=<SERVER>;UID=sa;PWD=;DATABASE=<DATABASE>;&quot];
strcpy (ConnectionString, &quot;DRIVER={SQL Server};SERVER=CORE11;UID=sa;PWD=;DATABASE=pubs;&quot;

SQLAllocHandle((SQLSMALLINT)SQL_HANDLE_ENV, (SQLHANDLE)SQL_NULL_HANDLE, &(SQLHANDLE)henvl);
SQLError(henvl, NULL, NULL, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

SQLSetEnvAttr((SQLHENV)henvl, (SQLINTEGER)SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, (SQLINTEGER)NULL);
SQLError(henvl, NULL, NULL, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

SQLAllocHandle((SQLSMALLINT)SQL_HANDLE_DBC, (SQLHANDLE)henvl, &(SQLHANDLE)hdbc);
SQLError(henvl, hdbc, NULL, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

SQLSetConnectAttr((SQLHDBC)hdbc, (SQLINTEGER)SQL_ATTR_ODBC_CURSORS, (SQLPOINTER)SQL_CUR_USE_DRIVER, (SQLINTEGER)0);
SQLError(henvl, hdbc, NULL, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

SQLDriverConnect((SQLHDBC)hdbc, (SQLHWND)NULL, (SQLCHAR*)ConnectionString, (SQLSMALLINT)SQL_NTS, (SQLCHAR*)NULL, (SQLSMALLINT)NULL, (SQLSMALLINT*)NULL, (SQLSMALLINT)SQL_DRIVER_NOPROMPT);
SQLError(henvl, hdbc, NULL, state, nativeerror, errormsg, errormsgmax, pcberrormsg);<-- This is where I get the warning

SQLAllocHandle((SQLSMALLINT)SQL_HANDLE_STMT, (SQLHANDLE)hdbc, &(SQLHANDLE)hstmt);
SQLError(henvl, hdbc, hstmt, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

SQLSetStmtAttr((SQLHSTMT)hstmt, (SQLINTEGER)SQL_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC, (SQLINTEGER)0);
SQLError(henvl, hdbc, hstmt, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

SQLPrepare((SQLHSTMT)hstmt, (SQLCHAR*)&quot;SELECT * FROM Summary&quot;, (SQLINTEGER)strlen(&quot;SELECT * FROM Summary&quot);
SQLError(henvl, hdbc, hstmt, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

SQLExecute((SQLHSTMT)hstmt);
SQLError(henvl, hdbc, hstmt, state, nativeerror, errormsg, errormsgmax, pcberrormsg); <-- This is where I get the error.

SQLNumResultCols((SQLHSTMT)hstmt, &(SQLSMALLINT)ColNumber);
SQLError(henvl, hdbc, hstmt, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

SQLGetData((SQLHSTMT)hstmt, (SQLUSMALLINT)1, (SQLSMALLINT)SQL_C_CHAR, (SQLPOINTER)Buffer, (SQLINTEGER)sizeof(Buffer), &(SQLINTEGER)BufLength);
SQLError(henvl, hdbc, hstmt, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

SQLCloseCursor(hstmt);
SQLError(henvl, hdbc, hstmt, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

Like I said...pubs will work fine..it's when I try a different database that it won't work. So frustrating!

Niky Williams
NTS Marketing Inc.

 

[Cagliostro]

Before connecting to other DB you must disconnect from the above connected. Before disconnecting you must close and free all statements allocated on this hdbc. John Fill

ivfmd@mail.md

 

[Valius]

I do free all the handles after I connect, I just didn't include it in here to shorten things up. I don't reconnect after this. What I did was connect to pubs, I didn't get any errors...then I went back and changed the existing code to connect to another DB. So, instead of my connection string having pubs..it had the other DB. In my program, I'm only making one connection, then deallocating all the handles...here is the rest of the code that follows what I had above:

SQLCloseCursor(hstmt);
SQLError(henvl, hdbc, hstmt, state, nativeerror, errormsg, errormsgmax, pcberrormsg);

Thank you so much for your input!

Niky Williams
NTS Marketing Inc.

SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henvl);

 

[Cagliostro]

if you have this problem woth onle one single DB, see if you should close enterprise manager or not. If it is used by Enterprise Manager, select other database. If there are some many databases then see the rights you have. John Fill

ivfmd@mail.md

 

[Valius]

Yup, I'm officially an idiot!

It was a stupid typo!

ConnectionString = new char[strlen(&quot;DRIVER={SQL Server};SERVER=CORE11;UID=sa;PWD=;DATABASE=pubs;&quot];
strcpy (ConnectionString, &quot;DRIVER={SQL Server};SERVER=CORE11;UID=sa;PWD=;DATABASE=pubs;&quot;

This worked fine...when I changed my database, I only changed the DATABASE when I allocated my ConnectionString variable. I didn't change the DATABASE when I did the strcpy(), So I had this:

ConnectionString = new char[strlen(&quot;DRIVER={SQL Server};SERVER=CORE11;UID=sa;PWD=;DATABASE=OtherDB;&quot];
strcpy (ConnectionString, &quot;DRIVER={SQL Server};SERVER=CORE11;UID=sa;PWD=;DATABASE=pubs;&quot;

....so of course I'm gonna get the error 42S02 because I was still on the pubs DB. Thanks for your help!

Niky Williams
NTS Marketing Inc.

 

[Cagliostro]

happy programming John Fill

ivfmd@mail.md