[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Using transactions in SQL Server 7.0

Posted on 1999-12-14
10
Medium Priority
?
252 Views
Last Modified: 2010-04-01
I have a series of records to add to different tables in a SQL Server 7.0 database.

The first thing I did was create a new workspace and opened the database.  Then I used the WorkSpace.BeginTrans to start recording the transactions. The first set records okay but it hangs on the second set of transaction.  The transactions are using DAO recordset and the AddNew method.  Does anyone have any advice ??



0
Comment
Question by:AnnaWang
10 Comments
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2282759
Do you perform any selects after you've updated tables? Don't.
0
 
LVL 4

Expert Comment

by:inpras
ID: 2283214
Hi
How about some samle code? may be some mistake cannot guess right?

Regards
0
 

Author Comment

by:AnnaWang
ID: 2283737
Even if I took out the select in the second insert, I still get an dao error.

Please help ....


Here is the sample code:



#include <afxdao.h>

//Global vars
CDaoDatabase    *db;
CDaoWorkspace      *wsSQLWork;

//main
extern __declspec(dllexport) long __stdcall ssrtf_updatedb( ...some arguments){


.......
.........

AfxDaoInit();

//open database
if (!opendb())
{
fprintf(fplog, "\nError opening the database. Aborting !");
ret_stat = 1;            
return(ret_stat);
}


wsSQLWork->BeginTrans();

//first insert
if (insertfirstOrdVault() == 0)
{
                              err = true;
}

//***THIS IS WHERE IT HANGS****!
if (insertsecondOrdVault() == 0)
{
                                    err = true;
}

//rollback transaction
if (err == true)
{
      wsSQLWork->Rollback();
}
//commit transaction
else
{
      wsSQLWork->CommitTrans();
}
      AfxDaoTerm();
        return 0;

}




//function to open database

bool openDMSdb()
{

 // Only call for regular MFC DLL's
   #ifndef _AFXDLL
   AFX_MANAGE_STATE (AfxGetStaticModuleState());
      #endif

wsSQLWork = new CDaoWorkspace;
db = new CDaoDatabase;

try
{
// open default  work space
wsSQLWork->Open(NULL);

// open the database
//Sql database
db->Open(...database path etc...);
}            
catch () // catch Dao database exceptions
{
..............
...............
}
}


//function to insert first set of records
//**** THIS PART WORKS FINE
int insertfirstDocVault()
{
CDaoRecordset      rstDocVault(db);


try
{

                  
      rstDocVault.Open(dbOpenDynaset, "SELECT * FROM sometable", dbSeeChanges);

                  rstOrdVault.AddNew();
         //set the fields
          ...................
          ...................
        rstOrdVault.Update();
}
catch()
{           ..............
}
}

//function to insert first set of records
//**** THIS IS WHERE IT HANGS insertfirstDocVault()
{
CDaoRecordset      rstDocVault(db);


try
{

                  
      rstDocVault.Open(dbOpenDynaset, "SELECT * FROM sometable", dbSeeChanges);

                  rstOrdVault.AddNew();
         //set the fields
          ...................
          ...................
        rstOrdVault.Update();
}
catch()
{           ..............
}
}



0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:AnnaWang
ID: 2283895
Even if I took out the select in the second insert, I still get an dao error.

Please help ....


Here is the sample code:



#include <afxdao.h>

//Global vars
CDaoDatabase    *db;
CDaoWorkspace      *wsSQLWork;

//main
extern __declspec(dllexport) long __stdcall ssrtf_updatedb( ...some arguments){


.......
.........

AfxDaoInit();

//open database
if (!opendb())
{
fprintf(fplog, "\nError opening the database. Aborting !");
ret_stat = 1;            
return(ret_stat);
}


wsSQLWork->BeginTrans();

//first insert
if (insertfirstOrdVault() == 0)
{
                              err = true;
}

//***THIS IS WHERE IT HANGS****!
if (insertsecondOrdVault() == 0)
{
                                    err = true;
}

//rollback transaction
if (err == true)
{
      wsSQLWork->Rollback();
}
//commit transaction
else
{
      wsSQLWork->CommitTrans();
}
      AfxDaoTerm();
        return 0;

}




//function to open database

bool openDMSdb()
{

 // Only call for regular MFC DLL's
   #ifndef _AFXDLL
   AFX_MANAGE_STATE (AfxGetStaticModuleState());
      #endif

wsSQLWork = new CDaoWorkspace;
db = new CDaoDatabase;

try
{
// open default  work space
wsSQLWork->Open(NULL);

// open the database
//Sql database
db->Open(...database path etc...);
}            
catch () // catch Dao database exceptions
{
..............
...............
}
}


//function to insert first set of records
//**** THIS PART WORKS FINE
int insertfirstDocVault()
{
CDaoRecordset      rstDocVault(db);


try
{

                  
      rstDocVault.Open(dbOpenDynaset, "SELECT * FROM sometable", dbSeeChanges);

                  rstOrdVault.AddNew();
         //set the fields
          ...................
          ...................
        rstOrdVault.Update();
}
catch()
{           ..............
}
}

//function to insert first set of records
//**** THIS IS WHERE IT HANGS insertfirstDocVault()
{
CDaoRecordset      rstDocVault(db);


try
{

                  
      rstDocVault.Open(dbOpenDynaset, "SELECT * FROM sometable", dbSeeChanges);

                  rstOrdVault.AddNew();
         //set the fields
          ...................
          ...................
        rstOrdVault.Update();
}
catch()
{           ..............
}
}



0
 
LVL 3

Accepted Solution

by:
NullTerminator earned 200 total points
ID: 2285512
Have you tried passing the WorkSpace pointer to the db constructor after opening the wksp.  Also I notice you don't close your recordsets.

You might try an ptrDB->Execute(strSQL) where strSQL = "Insert into myTable field1, field2 values (10, 20)"

'\0'
0
 
LVL 4

Expert Comment

by:inpras
ID: 2285606
How about making rstDocVault global
I think the problem is U R opening it twice under the same workspace and since UR using Begin TRans and Commit trans the records U update till U commit or roll back will be locked try using the global var of CDaoRecordSet

Hope this helps

Regards


0
 

Author Comment

by:AnnaWang
ID: 2296408
How do you make the recordset global ?
0
 
LVL 4

Expert Comment

by:inpras
ID: 2296918
Hi AnnaWang
declaration of function and variables
bool openDMSdb() ;
int insertsecondOrdVault(CTryDaoSet&);
int insertfirstDocVault(CTryDaoSet&) ;
CDaoDatabase    *db;
CDaoWorkspace *wsSQLWork;
Not: Here CTryDaoSet is my recordset  class derived from CDaoRecordset
In my function where I call to update
      CTryDaoSet rstDocVault(db);
      rstDocVault.Open(dbOpenDynaset, "SELECT * FROM NUm", dbSeeChanges);
      wsSQLWork = new CDaoWorkspace;
      db = new CDaoDatabase;
      wsSQLWork->Open(NULL);
      wsSQLWork->BeginTrans();
      bool err = false;
      if (insertfirstDocVault(rstDocVault) == 0)
      {
            err = true;
      }

      //***THIS IS WHERE IT HANGS****!
      if (insertsecondOrdVault(rstDocVault) == 0)
      {
            err = true;
      }
      if (err == true)
      {
            wsSQLWork->Rollback();
      }
      else
      {
      wsSQLWork->CommitTrans();
      }

the other functions will remain same except opening the recordset
int insertfirstDocVault(CTryDaoSet& rs)
{
            rs.AddNew();
        rs.m_Number = 112;
            rs.m_Name = "PrasannaIV";
        rs.Update();
            return 1;
}

int insertsecondOrdVault(CTryDaoSet& rs)
{
            rs.AddNew();
        rs.m_Number = 111;
            rs.m_Name = "PrasannaI";
        rs.Update();
            return 1;
}

Hope this helps
Regards
0
 

Author Comment

by:AnnaWang
ID: 2301626
I solved it.  I had to use db->Execute(strSQL).  

Much thanks to all that responded.
0
 

Author Comment

by:AnnaWang
ID: 2301631
Thanks a lot for all your response.
0

Featured Post

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.

Question has a verified solution.

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

When writing generic code, using template meta-programming techniques, it is sometimes useful to know if a type is convertible to another type. A good example of when this might be is if you are writing diagnostic instrumentation for code to generat…
This article shows you how to optimize memory allocations in C++ using placement new. Applicable especially to usecases dealing with creation of large number of objects. A brief on problem: Lets take example problem for simplicity: - I have a G…
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 be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

591 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