TSimpleDataSet creation during runtime

I am trying to create a TSimpleDataSet object during runtime to edit some database information. But at the line pSDS->ApplyUpdates(), the program throws an exception TDBXError stating oracle error ORA-00942 : table or view does not exist. Same results with TClientDataSet. What am I doing wrong here?  
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include <tchar.h>
//---------------------------------------------------------------------------

#include <DBTables.hpp>
#include <SqlExpr.hpp>
#include <Provider.hpp>
#include <SimpleDS.hpp>

#pragma argsused
int _tmain(int argc, _TCHAR* argv[])
{

	//set-up connection
	TSQLConnection *pConn = new TSQLConnection(NULL);
	pConn->ConnectionName = "MEDLCONNECTION";
	pConn->DriverName = "Oracle";
	pConn->GetDriverFunc = "getSQLDriverORACLE";
	pConn->LibraryName = "dbxora.dll";
	pConn->KeepConnection = true;
	pConn->LoginPrompt = false;
	//safe:: set-up every parameter
	pConn->Params->Add("drivername=ORACLE");
	pConn->Params->Add("database=XE");
	pConn->Params->Add("user_name=medl");
	pConn->Params->Add("password=medl");
	pConn->Params->Add("blobsize=-1");
	pConn->Params->Add("localecode=0000");
	pConn->Params->Add("isolationlevel=ReadCommitted");
	pConn->Params->Add("rowsetsize=20");
	pConn->Params->Add("os authentication=False");
	pConn->Params->Add("multiple transaction=False");
	pConn->Params->Add("trim char=False");
	pConn->Params->Add("decimal separator=.");

	//set-up SQLDataset
	TSQLDataSet *pDSet = new TSQLDataSet(NULL);
	pDSet->SQLConnection = pConn;
	pDSet->DbxCommandType = "Dbx.SQL";
	pDSet->MaxBlobSize = -1;

	//set-up data set provider
	TDataSetProvider *pDSPro = new TDataSetProvider(NULL);
	pDSPro->DataSet = pDSet;

	//using TSimpleDataSet
	TSimpleDataSet *pSDS = new TSimpleDataSet(NULL);
	pSDS->Name = "mySDS";
	pSDS->Connection = pConn;
	pSDS->SetProvider(pDSPro);
	pSDS->ProviderName = pDSPro->Name;
	pSDS->DataSet->CommandType = ctQuery;
	pSDS->DataSet->CommandText = "SELECT FINGER_IDX, USER_ID, PRINT FROM USER_PRINTS	WHERE office_id = 903 AND user_id ='MEDLDEV4' AND finger_IDX = 7 ";

	//get record
	pConn->Open();
//	pSDS->Close();
	pSDS->Open(); 
	pSDS->Edit();

	//probing dataset
	TField* pField1 = pSDS->FieldByName("FINGER_IDX");
	String s1 = pField1->AsString;          //legit value
	TField* pField2 = pSDS->FieldByName("USER_ID");
	String s2 = pField2->AsString;     //working OK legit value

	//set new value for field2
	pField2->AsString = "CODBDEV4";

	//probing client data set
	bool b2 = pSDS->Delta.IsNull(); //false
	bool b3 = pSDS->Delta.IsEmpty();     //false

	int errcnt = pSDS->ApplyUpdates(-1); //throws exception

	// clean-up
	delete pConn;
	delete pDSet;
	delete pDSPro;
	delete pSDS;

	return 0;
}
//---------------------------------------------------------------------------

Open in new window

LVL 1
ol muserTechnology GeneralistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marco GasiFreelancerCommented:
I work with Delphi and I don't know if it's the same thing, but in Delphi when I create a TClientDataSet at runtime I have to give  value to its property FileName to avoid that error.
Hope this can help you.

Cheers
0
jimyXCommented:
What is the point of using query and open the table for Edit?
If you want to update the table use SQL statements "update".
Another thing you do not need to call "Edit" or "ApplyUpdates" you did not alter the database, you seem opened for read.

Try this way:
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include <tchar.h>
//---------------------------------------------------------------------------

#include <DBTables.hpp>
#include <SqlExpr.hpp>
#include <Provider.hpp>
#include <SimpleDS.hpp>

#pragma argsused
int _tmain(int argc, _TCHAR* argv[])
{

	//set-up connection
	TSQLConnection *pConn = new TSQLConnection(NULL);
	pConn->ConnectionName = "MEDLCONNECTION";
	pConn->DriverName = "Oracle";
	pConn->GetDriverFunc = "getSQLDriverORACLE";
	pConn->LibraryName = "dbxora.dll";
	pConn->KeepConnection = true;
	pConn->LoginPrompt = false;
	//safe:: set-up every parameter
	pConn->Params->Add("drivername=ORACLE");
	pConn->Params->Add("database=XE");
	pConn->Params->Add("user_name=medl");
	pConn->Params->Add("password=medl");
	pConn->Params->Add("blobsize=-1");
	pConn->Params->Add("localecode=0000");
	pConn->Params->Add("isolationlevel=ReadCommitted");
	pConn->Params->Add("rowsetsize=20");
	pConn->Params->Add("os authentication=False");
	pConn->Params->Add("multiple transaction=False");
	pConn->Params->Add("trim char=False");
	pConn->Params->Add("decimal separator=.");

	//set-up SQLDataset
	TSQLDataSet *pDSet = new TSQLDataSet(NULL);
	pDSet->SQLConnection = pConn;
	pDSet->DbxCommandType = "Dbx.SQL";
	pDSet->MaxBlobSize = -1;

	//set-up data set provider
	TDataSetProvider *pDSPro = new TDataSetProvider(NULL);
	pDSPro->DataSet = pDSet;

	//using TSimpleDataSet
	TSimpleDataSet *pSDS = new TSimpleDataSet(NULL);
	pSDS->Name = "mySDS";
	pSDS->Connection = pConn;
	pSDS->SetProvider(pDSPro);
	pSDS->ProviderName = pDSPro->Name;
	pSDS->DataSet->CommandType = ctQuery;
	pSDS->DataSet->CommandText = "SELECT FINGER_IDX, USER_ID, PRINT FROM USER_PRINTS	WHERE office_id = 903 AND user_id ='MEDLDEV4' AND finger_IDX = 7 ";

	//get record
	pConn->Open();
//	pSDS->Close();
//	pSDS->Open(); 
//	pSDS->Edit();

	//probing dataset
	TField* pField1 = pSDS->FieldByName("FINGER_IDX");
	String s1 = pField1->AsString;          //legit value
	TField* pField2 = pSDS->FieldByName("USER_ID");
	String s2 = pField2->AsString;     //working OK legit value

	//set new value for field2
	pField2->AsString = "CODBDEV4";

	//probing client data set
	bool b2 = pSDS->Delta.IsNull(); //false
	bool b3 = pSDS->Delta.IsEmpty();     //false

//	int errcnt = pSDS->ApplyUpdates(-1); //throws exception

	// clean-up
	delete pConn;
	delete pDSet;
	delete pDSPro;
	delete pSDS;

	return 0;
}
//---------------------------------------------------------------------------

Open in new window

I am from Delphi zone.
0
ol muserTechnology GeneralistAuthor Commented:
Just any filename would do? I have no intention to store my data into a file, my operations are purely with the database.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jimyXCommented:
Update line 62 in the code snippet in my last post:
      pSDS->Open();
0
ol muserTechnology GeneralistAuthor Commented:
Still get the same exception if I set the filename property to some arbitrary file on the harddisk.
0
ol muserTechnology GeneralistAuthor Commented:
@jimyX, I am just using this as a trial to update the database table using a bidirectional dataset. I intend to update DB fields this way. My SELECT statement just fetches the data I need to update.
0
jimyXCommented:
What happens when you try SQL statement? Could you try this please:
	pSDS->DataSet->CommandType = ctQuery;
	pSDS->DataSet->CommandText = "update USER_PRINTS set USER_ID = 'CODBDEV4' WHERE office_id = 903 AND user_id ='MEDLDEV4' AND finger_IDX = 7";

	pConn->Open();
	pSDS->ExecSQL(); // I am not sure, it could be Execute or maybe like in Delphi ExecSQL

Open in new window

0
ol muserTechnology GeneralistAuthor Commented:
Actually, if I make the changes below the code works, the database is updated properly, but the console application fails to terminate properly. The application hangs!! Any thoughts?


      pSDS->DataSet->CommandType = ctTable;
      pSDS->DataSet->CommandText = "USER_PRINTS";
0
jimyXCommented:
Can you show your final code please?
0
ol muserTechnology GeneralistAuthor Commented:
here it is
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include <tchar.h>
//---------------------------------------------------------------------------

#include <DBTables.hpp>
#include <SqlExpr.hpp>
#include <Provider.hpp>
#include <SimpleDS.hpp>

#pragma argsused
int _tmain(int argc, _TCHAR* argv[])
{

	//set-up connection
	TSQLConnection *pConn = new TSQLConnection(NULL);
	pConn->ConnectionName = "MEDLCONNECTION";
	pConn->DriverName = "Oracle";
	pConn->GetDriverFunc = "getSQLDriverORACLE";
	pConn->LibraryName = "dbxora.dll";
	pConn->KeepConnection = true;
	pConn->LoginPrompt = false;
	//safe:: set-up every parameter
	pConn->Params->Add("drivername=ORACLE");
	pConn->Params->Add("database=XE");
	pConn->Params->Add("user_name=medl");
	pConn->Params->Add("password=medl");
	pConn->Params->Add("blobsize=-1");
	pConn->Params->Add("localecode=0000");
	pConn->Params->Add("isolationlevel=ReadCommitted");
	pConn->Params->Add("rowsetsize=20");
	pConn->Params->Add("os authentication=False");
	pConn->Params->Add("multiple transaction=False");
	pConn->Params->Add("trim char=False");
	pConn->Params->Add("decimal separator=.");

	//set-up SQLDataset
	TSQLDataSet *pDSet = new TSQLDataSet(NULL);
	pDSet->SQLConnection = pConn;
	pDSet->DbxCommandType = "Dbx.SQL";
	pDSet->MaxBlobSize = -1;

	//set-up data set provider
	TDataSetProvider *pDSPro = new TDataSetProvider(NULL);
	pDSPro->DataSet = pDSet;

	//using TSimpleDataSet
	TSimpleDataSet *pSDS = new TSimpleDataSet(NULL);
	pSDS->Name = "mySDS";
	pSDS->Connection = pConn;
	pSDS->SetProvider(pDSPro);
	pSDS->ProviderName = pDSPro->Name;
	pSDS->DataSet->CommandType = ctTable;
	pSDS->DataSet->CommandText = "USER_PRINTS";

	//get record
	pConn->Open();
	pSDS->Close();
	pSDS->Open();
	pSDS->Edit();

	//probing dataset
	TField* pField1 = pSDS->FieldByName("FINGER_IDX");
	String s1 = pField1->AsString;          //legit value
	TField* pField2 = pSDS->FieldByName("PRINT");
	String s2 = pField2->AsString;     //working OK legit value

	//set new value for field2
	pField2->AsString = "CODasdEV7";

	//probing client data set
	bool b2 = pSDS->Delta.IsNull(); //false
	bool b3 = pSDS->Delta.IsEmpty();     //false

	int errcnt = pSDS->ApplyUpdates(-1);

	pSDS->Close();
	pConn->CloseDataSets();
	pConn->Close();

	// clean-up
	delete pConn;
	delete pDSet;
	delete pDSPro;
	delete pSDS;

	return 0;
}
//---------------------------------------------------------------------------

Open in new window

0
jimyXCommented:
Try use ApplyUpdates(0) rather than -1.
0
jimyXCommented:
I think the problem is that you forgot to close the provider before deleting it:
	pSDS->Close();
	pConn->CloseDataSets();
	pConn->Close();
	[b]pDSPro[/b]->Close();

	// clean-up
	delete pConn;
	delete pDSet;
	delete pDSPro;
	delete pSDS;

Open in new window

0
jimyXCommented:
Oops, Bold does not work in code snippet:
      pSDS->Close();
      pConn->CloseDataSets();
      pConn->Close();
      pDSPro->Close();

      // clean-up
      delete pConn;
      delete pDSet;
      delete pDSPro;
      delete pSDS;
0
ol muserTechnology GeneralistAuthor Commented:
Service Provider has got no Close() method...
0
jimyXCommented:
Sorry it was a shot in the dark :-D

I am not so much familiar with C++ environment, but I will suggest the things that should be common as in Delphi:
1. If you open the table for edit you have to select the record that you need to update then call Edit and after finishing call Post.
2. Normally you need to loop through the records and match if to find the records you are after to amend. In your code you are amending the active record.
3. When you are done with all the updates then you call ApplyUpdates.

So it looks like this:
  pSDS->Open();
  pSDS->First;
  while (not   pSDS->EOF) do
  {
    if   (pSDS->FieldByName("FINGER_IDX")->AsString == "7") &
         (pSDS->FieldByName("office_id")->AsString == "903") &
         (pSDS->FieldByName("user_id")->AsString == "MEDLDEV4") {
              pSDS->Edit();
              pSDS->FieldByName("user_id")->AsString = "CODBDEV4";
              pSDS->Post();
             }
  }
  pSDS->ApplyUpdates(0);

Open in new window

0
Ephraim WangoyaCommented:
You don't need to set up a connection or a dataset. Use the Internal stuff
//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include <tchar.h>
//---------------------------------------------------------------------------

#include <DBTables.hpp>
#include <SqlExpr.hpp>
#include <Provider.hpp>
#include <SimpleDS.hpp>

#pragma argsused
int _tmain(int argc, _TCHAR* argv[])
{

	//using TSimpleDataSet
	TSimpleDataSet *pSDS = new TSimpleDataSet(NULL);
	pSDS->Name = "mySDS";
	pSDS->Connection->ConnectionName = "MEDLCONNECTION";
	pSDS->Connection->DriverName = "Oracle";
	pSDS->Connection->GetDriverFunc = "getSQLDriverORACLE";
	pSDS->Connection->LibraryName = "dbxora.dll";
	pConn->Connection->KeepConnection = true;
	pSDS->Connection->LoginPrompt = false;
	//safe:: set-up every parameter
	pSDS->Connection->Params->Add("drivername=ORACLE");
	pSDS->Connection->Params->Add("database=XE");
	pSDS->Connection->Params->Add("user_name=medl");
	pSDS->Connection->Params->Add("password=medl");
	pSDS->Connection->Params->Add("blobsize=-1");
	pSDS->Connection->Params->Add("localecode=0000");
	pSDS->Connection->Params->Add("isolationlevel=ReadCommitted");
	pSDS->Connection->Params->Add("rowsetsize=20");
	pSDS->Connection->Params->Add("os authentication=False");
	pSDS->Connection->Params->Add("multiple transaction=False");
	pSDS->Connection->Params->Add("trim char=False");
	pSDS->Connection->Params->Add("decimal separator=.");

	pSDS->DataSet->CommandType = ctQuery;
	pSDS->DataSet->CommandText = "SELECT FINGER_IDX, USER_ID, PRINT FROM USER_PRINTS WHERE office_id = 903 AND user_id ='MEDLDEV4' AND finger_IDX = 7 ";

	//get record
	pSDS->Open();
	pSDS->Edit();

	//probing dataset
	TField* pField1 = pSDS->FieldByName("FINGER_IDX");
	String s1 = pField1->AsString;          //legit value
	TField* pField2 = pSDS->FieldByName("USER_ID");
	String s2 = pField2->AsString;     //working OK legit value

	//set new value for field2
	pField2->AsString = "CODBDEV4";

	//probing client data set
	bool b2 = pSDS->Delta.IsNull(); //false
	bool b3 = pSDS->Delta.IsEmpty();     //false

	int errcnt = pSDS->ApplyUpdates(0); //throws exception

	// clean-up
	pSDS->Close();
	delete pSDS;

	return 0;
}
//---------------------------------------------------------------------------

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ol muserTechnology GeneralistAuthor Commented:
@ewangoya, the above code throws an exception Unknown driver : ORACLE at the line pSDS->Open();
0
Ephraim WangoyaCommented:
Try using Params.Values
Dont forget to include SqlConst

See if this helps

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include <tchar.h>
//---------------------------------------------------------------------------

#include <DBTables.hpp>
#include <SqlExpr.hpp>
#include <Provider.hpp>
#include <SimpleDS.hpp>
#include <SqlConst.hpp>

#pragma argsused
int _tmain(int argc, _TCHAR* argv[])
{

	//using TSimpleDataSet
	TSimpleDataSet *pSDS = new TSimpleDataSet(NULL);
	pSDS->Name = "mySDS";
	pSDS->Connection->ConnectionName = "MEDLCONNECTION";
	pSDS->Connection->DriverName = "Oracle";
	pSDS->Connection->GetDriverFunc = "getSQLDriverORACLE";
	pSDS->Connection->LibraryName = "dbxora.dll";
	pConn->Connection->KeepConnection = true;
	pSDS->Connection->LoginPrompt = false;
	//safe:: set-up every parameter
	pSDS->Connection->Params->Values[DRIVERNAME_KEY]="ORACLE";
	pSDS->Connection->Params->Values[DATABASENAME_KEY]="XE";
	pSDS->Connection->Params->Values[szUSERNAME] = "medl";
	pSDS->Connection->Params->Values[szPASSWORD]="medl";
	pSDS->Connection->Params->Values[MAXBLOBSIZE_KEY]="-1";
	pSDS->Connection->Params->Values[SQLLOCALE_CODE_KEY] ="0000";
	pSDS->Connection->Params->Values["isolationlevel"]="ReadCommitted";
	pSDS->Connection->Params->Values[ROWSETSIZE_KEY]="20";
	pSDS->Connection->Params->Values[OSAUTHENTICATION]="False";
	pSDS->Connection->Params->Values[MULTITRANSENABLED]="False";
	pSDS->Connection->Params->Values[TRIMCHAR]="False";
	pSDS->Connection->Params->Values["decimal separator"]=".";

	pSDS->DataSet->CommandType = ctQuery;
	pSDS->DataSet->CommandText = "SELECT FINGER_IDX, USER_ID, PRINT FROM USER_PRINTS WHERE office_id = 903 AND user_id ='MEDLDEV4' AND finger_IDX = 7 ";

	//get record
	pSDS->Open();
	pSDS->Edit();

	//probing dataset
	TField* pField1 = pSDS->FieldByName("FINGER_IDX");
	String s1 = pField1->AsString;          //legit value
	TField* pField2 = pSDS->FieldByName("USER_ID");
	String s2 = pField2->AsString;     //working OK legit value

	//set new value for field2
	pField2->AsString = "CODBDEV4";

	//probing client data set
	bool b2 = pSDS->Delta.IsNull(); //false
	bool b3 = pSDS->Delta.IsEmpty();     //false

	int errcnt = pSDS->ApplyUpdates(0); //throws exception

	// clean-up
	pSDS->Close();
	delete pSDS;

	return 0;
}
//---------------------------------------------------------------------------

Open in new window

0
ol muserTechnology GeneralistAuthor Commented:
none of the solutions addressed the question directly, but gave some pointers otherwise. Actual solution is self found.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C++

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.