DataBases

ODBC

ODBC is an industry-standard means of connecting to databases from C code. It was devised by Microsoft. It consists of a C API, but there are many implementations, and in many languages.

A typical example is

#include "tutorial.h"

BOOL CTutorialApp::InitInstance()
{	
	m_CTutorialDatabase = new CTutorialDatabase();
	m_pMainWnd = new CTutorialWindow();	

	//rem out the two following lines to make invisible
	m_pMainWnd->ShowWindow(m_nCmdShow);	
	m_pMainWnd->UpdateWindow();	

	//open the database object
	m_CTutorialDatabase->Open("stock",FALSE,FALSE,"ODBC;DSN=stock");

        //new code to test add/delete functions
	CMyODBCFunctions *m_CMyODBCFunctions; //create instance of MyODBCFunctions
	m_CMyODBCFunctions = new CMyODBCFunctions; //allocate memory
 
	//add filter on EZ-2000. Will return record count if you want it
	m_CMyODBCFunctions->FilterOnModel("EZ-2000");
	
	//end test code

	return TRUE;
}

CTutorialWindow::CTutorialWindow()
{	
	// Create the window itself	
	Create(NULL,		
		"Tutorial",		
		WS_OVERLAPPEDWINDOW  ,		
		CRect(0,0,0,0));	
}

CTutorialSet::CTutorialSet(CDatabase *pdb) : CRecordset(pdb)
 {
	 //init the variables (don't want to include id field here
	 m_Model = _T("");
	 m_Number = 0;
	 m_Description = _T("");
	 m_ModelParam = _T("");
	 m_nFields = 3;
	 m_nParams = 1;
	 m_nDefaultType = dynaset;

 }

 CString CTutorialSet::GetDefaultConnect()
 {
	 return _T("ODBC;DSN=stock");
 }

 CString CTutorialSet::GetDefaultSQL()
 {
	 return _T("[stock]");
 }

 void CTutorialSet::DoFieldExchange(CFieldExchange* pFX)
 {
	 pFX->SetFieldType(CFieldExchange::outputColumn);
	 RFX_Text(pFX, _T("[Model]"), m_Model);
	 RFX_Long(pFX, _T("[Number]"), m_Number);
	 RFX_Text(pFX, _T("[Description]"), m_Description);

	 pFX->SetFieldType(CFieldExchange::param);
	 RFX_Text(pFX, _T("[Model]"), m_ModelParam);
 }

int CMyODBCFunctions::AddNewRecord(char *NewModel, long NewNumber, char *NewDescription)
{
	try
	{
		m_CTutorialSet.Open(); //open the database
	}
	catch(CDBException *e)
	{
		AfxMessageBox(CString("Cannot open database\n")+e->m_strError, MB_ICONEXCLAMATION);
		throw;
	}

	if(!m_CTutorialSet.CanAppend()) //make sure database can be appended to
		return -1;

	m_CTutorialSet.AddNew(); //add a new skeleton record

	//set the member varaibles to the new record values
	//the ID field is autoincriment so don't set
	m_CTutorialSet.m_Model = NewModel;
	m_CTutorialSet.m_Number = NewNumber;
	m_CTutorialSet.m_Description = NewDescription;
     
	try
	{
		m_CTutorialSet.Update(); //update the new record with this new data
	}
	catch(CDBException *e)
	{
		AfxMessageBox(CString("Cannot update record\n")+e->m_strError, MB_ICONEXCLAMATION);
		throw;
	}

	m_CTutorialSet.Requery(); //refresh data set
	m_CTutorialSet.Close(); //close the table

        return 0;
}

int CMyODBCFunctions::DeleteCurrentRecord()
{

	m_CTutorialSet2.Open();  

        try
	{
		m_CTutorialSet.Open(); //open the database
	}
	catch(CDBException *e)
	{
		AfxMessageBox(CString("Cannot open database\n")+e->m_strError, MB_ICONEXCLAMATION);
		throw;
	}

	try 
	{
		m_CTutorialSet.Delete(); //delete the current record
	}
	catch(CDBException *e)
	{
		AfxMessageBox(CString("Cannot current record\n")+e->m_strError, MB_ICONEXCLAMATION);
		throw;
	}

	m_CTutorialSet.MoveNext(); //move to the next record
    
	if(m_CTutorialSet.IsEOF()) //if the record just deleted was the last 
		m_CTutorialSet.MoveLast(); //record move to the new last record

	if(m_CTutorialSet.IsBOF()) //if the record just delted was the only 
		m_CTutorialSet.SetFieldNull(NULL); //record left, clear it

    m_CTutorialSet.Close();
	
	return 0;
}

int CMyODBCFunctions::FilterOnModel(char *ModelNumber)
{

       int iRecordCount = 0; //number of matching records
       char *cpRecordCount = new char[15]; //character string for message box
   
       m_CTutorialSet.m_ModelParam = ModelNumber; //set the parameter
       m_CTutorialSet.m_strFilter = "MODEL = ?"; //set the filter string
   
       m_CTutorialSet2.Open();  

       try
	{
		m_CTutorialSet.Open(); //open the database
	}
	catch(CDBException *e)
	{
		AfxMessageBox(CString("Cannot open database\n")+e->m_strError, MB_ICONEXCLAMATION);
		throw;
	}

	//if you wanted to do something with the filtered data do it here!

	while(!m_CTutorialSet.IsEOF()) //count the number of records
	{
	   iRecordCount++;
	   m_CTutorialSet.MoveNext(); //moves to the next record (see Navigating Records)
	}

	itoa(iRecordCount, cpRecordCount, 10); //change the integer value to a string

        AfxMessageBox(CString("Number of matching records: ") + cpRecordCount,MB_OK); //display the message box
	
	m_CTutorialSet.Close(); //close the recordset

	return iRecordCount; //return the number of records that matched filter

}

JDBC

JDBC is the Java implementation of ODBC. It requires an ODBC driver, and can be done in 4 ways

All four types exist.

Typical code is

db = new Database(url, user, password);
statement = connection.createStatement();
sql = "SELECT counter FROM t_counter";
result_set = statement.executeQuery(sql);

Transactions

If a client, a server and possibly a backend database all keep state, these must all be kept consistent. This is usually done by transactions. Parties can

At the start of a transaction, all parties should be in a consistent state. During the transaction, this may be inconsistent. But if they all commit, then a new consistent state is setup for all parties. Any party may cause a transaction to fail, in which case all parties restore the earlier state.

A typical example is a financial transaction. During this, a customer agrees to pay money to a seller. The seller needs to involve the customer's bank, to debit the customer and credit the seller. At the beginning of the transaction, no money is owed. By the time the transaction is over, the customer will believe they have less money, the seller will have more, and funds will have been transferred by the bank.

A transaction may be aborted if

ACID properties

A transaction must satisfy the ACID properties

Atomicity
A transaction is an undivisible unit of work
Consistency
After a transaction is complete, the system must be in a consistent state
Isolation
The behaviour of one transaction is not affected by other transactions
Durability
A transaction's effects are permanent, and withstand system failures

Two-phase commit

Transactions are usually handled by a two-phase commit process. In this, each participant to a transaction is asked if it is prepared to commit. If all of them are, then they are told to commit the transaction.

Any party may ask the transaction to prepare and then commit. Any party may ask the transaction to abort. If asked to prepare to commit, a transaction may call for abort instead. If a party is asked to abort, then it restores state to prior to the transaction.

The actions that a participant may call on the transaction are

The actions that a transaction may call on the participant are

Transaction managers

All participants in a transaction need to implement the two-phase commit protocol. A database typically will haev this done already. Other participants will need to do this themselves.

A transaction manager such as CICS will do this for you.


Jan Newmarch (http://jan.newmarch.name)
jan@newmarch.name

Copyright ©Jan Newmarch