Problems with CRecordset/CDatabase

[michaelkrauklis]

I've used CDatabase objects sucessfully before. That's not the problem. My problem is I need to get data from my SQL server using ODBC, so CRecordset was the logical next step for me. Here is the code so far(odbc is my odbc source name and table is my table name that I wish to perform the query on):

CDatabase db;
db.Open(odbc,false,true);

CRecordset *rs;
rs=new CRecordset(&db);

rs->Open(AFX_DB_USE_DEFAULT_TYPE,"SELECT * FROM "+table);

Now when I run this code I get the error message "No columns were bound prior to calling SQLFetchScroll/SQLExtendedFetch"

Any input will be eternally helpful. Thanks! MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[Zyrenthian]

I think you should try

CString SQL;

SQL.Format("Select * From %s",table);

rs->Open(AFX_DB_USE_DEFAULT_TYPE,SQL);

See if that works for you... If not, try changing the default type to CRecordset::shapshot

Matt

 

[michaelkrauklis]

I already tried both of those, and neither of them worked. It's not a problem with the SQL statement because if I set the table name to a table that does not exist I get an 'invalid object' error, meaning the table does not exist. So that's not the problem. And I tried the snapshot again just to make sure that wasn't it, and it's not. MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[Zyrenthian]

does the table name have spaces in it? You may need to bracket it

 

[michaelkrauklis]

Nope, the table name does not have spaces in it. And it's finding the table fine, because like I said above, if I give a wrong table name it gives me another error. MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[michaelkrauklis]

Oh yeah, btw, I figure this would be assumed but fyi I know the problem is in the open statement. I have print statements before and after for testing and the one after doesn't get executed. MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[Zyrenthian]

Well,
Maybe your odbc is set up incorrectly?? I am shooting in the dark now though. Look into SQLConfigDataSource. I have had problems when I thought my odbc connection was correct and it wasnt because I screwed it up somewhere with a line of code. Also, I never use any defaults as I found they can specify things I dont want.


I assume you are already doing the following but here it is for good measure.


try
{
rs->Open(AFX_DB_USE_DEFAULT_TYPE,"SELECT * FROM "+table);
}
catch(CDBException* dbEx)
{
char buffer[MAX_PATH];
dbEx->GetErrorMessage(buffer,MAX_PATH);
dbEx->Delete();
}
catch(CException* ex)
{
char buffer[MAX_PATH];
ex->GetErrorMessage(buffer,MAX_PATH);
ex->Delete();
}
catch(...)
{
AfxMessagebox("WHAT THE HECK DID YOU DO???"
}

Matt

 

[michaelkrauklis]

well I was already catching the CDBException, that's how I knew of the original error. That's pretty standard practice. I actually figured out how to do this and I think it has to do with the odbc source. You have to use CRecordset::forwardOnly. I'm not sure exactly why, and it messes stuff up if you want to go backward, but if you only need to traverse forward through the data this is fine I guess. I've run into a few problems since then, like trying to get a date from a field, but I'm only exracting two tables to text and the structure of them is static so I can just skip the four fields that are of type timestamp. I tried using a CDBVariant but that just plain didn't work. I used the microsoft example and that didn't even work. Pretty sketchy. Anyway, here's the code that worked if anyone's interested:

//****************************************************************************************************************
CString xxxx::getSQLTablesText(CString const &odbc,CString const &PATH,CString const &table){
	ar[EXP_TBL]=PAS;
	CDatabase *db;
	CRecordset *rs;
	try{
		db=new CDatabase();
		db->Open(odbc,false,true);

		rs=new CRecordset(db);

		SQL.Format("SELECT * FROM "+table);

		rs->Open(CRecordset::forwardOnly,SQL);

		short num_r=rs->GetODBCFieldCount();

		fstream out;
		rs->m_strFilter = "";
		
		out.open(PATH+table+"_f.txt",ios::out);

		int mod=-2;
		if(table.CompareNoCase("xxxx")==0){
			mod=-4;
		}

		while(!rs->IsEOF()){

			for(int z=0;z<(num_r+mod);z++){
				CString temp;

				rs->GetFieldValue(z,temp);
				out<<temp;//.m_pstring;

				if(z!=num_r+mod-1){
					out<<&quot;\t&quot;;
				}
				else{
					if(mod==-4){
						rs->GetFieldValue(num_r-2,temp);
						out<<&quot;\t&quot;<<temp<<&quot;\t&quot;;
						rs->GetFieldValue(num_r-1,temp);
						out<<temp;
					}
					out<<endl;
				}
			}
			rs->MoveNext();
		}
		rs->Close();
		out.close();
	}
	catch(CDBException *e){
		delete rs;
		char ret[1000];
		e->GetErrorMessage(ret,1000);
		ar[EXP_TBL]=ERR;
		return ret;
	}
	delete rs;
	return &quot;Finished Exporting to Text&quot;;
}

Function names and table names were changed to 'xxxx' and pretty much everything having to do with ar

[]

deals with enumerated types of another class. fun fun MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[michaelkrauklis]

oh dag, I forgot to delete the db object as well. Add a delete db; in there before both return statements. MYenigmaSELF:-9
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

"If debugging is the process of removing bugs, then programming must be the process of putting them in." --Dykstra

 

[RickCrone]

Use the Class Wizard to create a class using CRecordset as the base class. This will allow you to create a class based on your table. The Wizard will guide you thru this. Then use this new class where you are using CRecordset. You will have to #include the header for your new class you created in whatever source file you want to access this table from. The record set Open() will get all the records... to get just the one(s) you want set the member variable m_strFilter as your SQL SELECT clause (without the SELECT key word). Also m_strSort can help if you need to get the records in order.

 

[Guest_imported]

thanks, but you're a little late. and what's the difference with that? it's simply using inheratence to do the same thing I'm already doing isn't it? I got it to work my way. Thanks though.