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 is the Java implementation of ODBC. It requires an ODBC driver, and can be done in 4 ways
Typical code is
db = new Database(url, user, password); statement = connection.createStatement(); sql = "SELECT counter FROM t_counter"; result_set = statement.executeQuery(sql);
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
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
A transaction must satisfy the ACID properties
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
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.