Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

MSSQL ODBC And Transactions

  
   Database Forums (Home) -> ODBC RSS
Next:  com.microsoft.sqlserver.jdbc.SQLServerException: ..  
Author Message
Milind

External


Since: Feb 20, 2008
Posts: 3



(Msg. 1) Posted: Wed Feb 20, 2008 2:21 pm
Post subject: MSSQL ODBC And Transactions
Archived from groups: microsoft>public>sqlserver>odbc (more info?)
Hi There,



I have developed the ODBC app which can work with Oracle as well as
MSSQL server databases. I need to use transactions at some level. By
setting AUTOCOMMIT OFF and using SET TRANSACTION and BEGIN WORK,
COMMIT WORK everything works fine with Oracle. But with MSSQL server
does not. Do I need to code something special for MSSQL? Does anyone
has example on how to implement transactions with MSSQL server using
ODBC?



I tried reading lots of documents on Microsoft site but nothing
specifically says how to do this. One way the doc says is using
SQLSetConnectAttr with SQL_ATTR_TXN_ISOLATION but does not specify how
to.



Any help with appreciated. Thanks in advance.



Milind

 >> Stay informed about: MSSQL ODBC And Transactions 
Back to top
Login to vote
Arnie

External


Since: Jan 04, 2007
Posts: 2



(Msg. 2) Posted: Thu Feb 21, 2008 10:31 am
Post subject: Re: MSSQL ODBC And Transactions [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
"Milind" wrote in message

> Hi There,
>
>
>
> I have developed the ODBC app which can work with Oracle as well as
> MSSQL server databases. I need to use transactions at some level. By
> setting AUTOCOMMIT OFF and using SET TRANSACTION and BEGIN WORK,
> COMMIT WORK everything works fine with Oracle. But with MSSQL server
> does not. Do I need to code something special for MSSQL? Does anyone
> has example on how to implement transactions with MSSQL server using
> ODBC?
>
> Any help with appreciated. Thanks in advance.
>
>
>
> Milind
The following C++ code works with Oracle, SQL Server 2000 and DB2.

- Arnie

/////////////////////////////////////////////////////////////////////////////

//

// Auto commit is ON by default. We turn it OFF for an explicit transaction.

//

void VRMSDatabase::StartTransaction( void )

{

SQLRETURN rc = SQLSetConnectAttr( m_hDbc, SQL_ATTR_AUTOCOMMIT,

SQL_AUTOCOMMIT_OFF, 0 );

CHECK_DBC( m_hDbc, rc );

m_InTransaction = true;

}



/////////////////////////////////////////////////////////////////////////////

//

// Commit the transaction and turn auto commit back on

//

void VRMSDatabase::Commit( void )

{

if ( m_InTransaction )

{

SQLRETURN rc = SQLEndTran( SQL_HANDLE_DBC, m_hDbc, SQL_COMMIT );

CHECK_DBC( m_hDbc, rc );

m_InTransaction = false;

rc = SQLSetConnectAttr( m_hDbc, SQL_ATTR_AUTOCOMMIT,

(SQLPOINTER) SQL_AUTOCOMMIT_ON, 0 );

}

}



/////////////////////////////////////////////////////////////////////////////

//

// Rollback the transaction and turn auto commit back on

//

void VRMSDatabase::Rollback( void )

{

if ( m_InTransaction )

{

SQLRETURN rc = SQLEndTran( SQL_HANDLE_DBC, m_hDbc, SQL_ROLLBACK );

CHECK_DBC( m_hDbc, rc );

m_InTransaction = false;

rc = SQLSetConnectAttr( m_hDbc, SQL_ATTR_AUTOCOMMIT,

(SQLPOINTER) SQL_AUTOCOMMIT_ON, 0 );

}

}

 >> Stay informed about: MSSQL ODBC And Transactions 
Back to top
Login to vote
Milind

External


Since: Feb 20, 2008
Posts: 3



(Msg. 3) Posted: Thu Feb 21, 2008 10:47 am
Post subject: Re: MSSQL ODBC And Transactions [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
On Feb 21, 10:31 am, "Arnie" wrote:
> "Milind" wrote in message
>
>
>
>
>
> > Hi There,
>
> > I have developed the ODBC app which can work with Oracle as well as
> > MSSQL server databases. I need to use transactions at some level. By
> > setting AUTOCOMMIT OFF and using SET TRANSACTION and BEGIN WORK,
> > COMMIT WORK everything works fine with Oracle. But with MSSQL server
> > does not. Do I need to code something special for MSSQL? Does anyone
> > has example on how to implement transactions with MSSQL server using
> > ODBC?
>
> > Any help with appreciated. Thanks in advance.
>
> > Milind
>
> The following C++ code works with Oracle, SQL Server 2000 and DB2.
>
> - Arnie
>
> ///////////////////////////////////////////////////////////////////////////­//
>
> //
>
> // Auto commit is ON by default. We turn it OFF for an explicit transaction.
>
> //
>
> void VRMSDatabase::StartTransaction( void )
>
> {
>
> SQLRETURN rc = SQLSetConnectAttr( m_hDbc, SQL_ATTR_AUTOCOMMIT,
>
> SQL_AUTOCOMMIT_OFF, 0 );
>
> CHECK_DBC( m_hDbc, rc );
>
> m_InTransaction = true;
>
> }
>
> ///////////////////////////////////////////////////////////////////////////­//
>
> //
>
> // Commit the transaction and turn auto commit back on
>
> //
>
> void VRMSDatabase::Commit( void )
>
> {
>
> if ( m_InTransaction )
>
> {
>
> SQLRETURN rc = SQLEndTran( SQL_HANDLE_DBC, m_hDbc, SQL_COMMIT );
>
> CHECK_DBC( m_hDbc, rc );
>
> m_InTransaction = false;
>
> rc = SQLSetConnectAttr( m_hDbc, SQL_ATTR_AUTOCOMMIT,
>
> (SQLPOINTER) SQL_AUTOCOMMIT_ON, 0 );
>
> }
> }
>
> ///////////////////////////////////////////////////////////////////////////­//
>
> //
>
> // Rollback the transaction and turn auto commit back on
>
> //
>
> void VRMSDatabase::Rollback( void )
>
> {
>
> if ( m_InTransaction )
>
> {
>
> SQLRETURN rc = SQLEndTran( SQL_HANDLE_DBC, m_hDbc, SQL_ROLLBACK );
>
> CHECK_DBC( m_hDbc, rc );
>
> m_InTransaction = false;
>
> rc = SQLSetConnectAttr( m_hDbc, SQL_ATTR_AUTOCOMMIT,
>
> (SQLPOINTER) SQL_AUTOCOMMIT_ON, 0 );
>
>
>
> }
> }- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Hi Arnie,

Thanks for help.

I guess your suggestion will help me where I have to use 'BEGIN WORK'
and 'COMMIT WORK'. I do set AUTO_COMMIT OFF as soon as I make the
connection. So I also need see how my existing code will behave when I
do not set the AUTO_COMMIT OFF.

Also I have a problem with 'SET TRANSACTION READ ONLY' where I want
to read the same table data multiple times in one transaction and when
I read second or third time it should not do the dirty reads (the same
data should be read all the SELECTs till the TRANSACTION does not
end). Do you know how can I achive this?

Thanks,
Milind
 >> Stay informed about: MSSQL ODBC And Transactions 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
MSSQL ODBC problem - Hello everyone, I have big problem with the speed of the ODBC connection. Speed of the connectino depends of user which starts application. For examle: Main user (which usualy works on this computer) of computer open application - connection is very..

Can you guess my MSSQL database password? - Till to date large number of attacks are still carried out by guessing users password. A structured organization have well defined and imposed Password Policy but policy control enforcement measures do not guarantee secure MSSQL databases. Tools for..

How much more disk-space requires to upgrade the MSSQL 200.. - Hi All, I had MSSQL 2000 and I would like to upgrade this server to MSSQL 2005 and I were wordering how much more disk space requires for the upgrade process. Is there any document that I need to read before upgrade process to upgrade the server from..

Connexion ODBC échoue quand 'Déterminer le port de manière.. - Bonjour Voici ma configuration : serveur SQL Server 2008 express, clients Access2000 en VBA Je configure les liens ODBC de mes postes clients en utilisant la routine Build_System DSN. Jusque là tout va bien, les liens ODBC sont créés correctement L...

ODBC using SSH - Hello! I am trying to use the ODBC services via an SSH connection. What is the easiest way of doing this? Are there any ready-made software out there than I can use to connect? Thank you! -Bahman
   Database Forums (Home) -> ODBC All times are: Pacific Time (US & Canada)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]