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.