SQLite

SQLite is a simple database for rapid development without any serious needs (no security, no large size etc).

• SQLite good article in Wikipedia

Basic

Tool for managing the database SQLiteStudio

SQLite tutorial for C++

In order to compile a program in Eclipse you should download the source of sqlite3 and in MINGW execute the command :gcc -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1

That will create an obj file. In Eclipse then Project->Properties->C/C++ Build->Settings->MINGW C++Linker->Miscellaneous add the obj in the other objects window.

Simple C++ Source Code

<cpp> /*

* testDatabase.cpp
*
*  Created on: Jul 6, 2013
*      Author: Kostas
*/

1. include <iostream>
2. include "../sqlite/sqlite3.h"
3. define DBNAME "testdb_sql"

using namespace std;

static int callback(void *data, int argc, char **argv, char **azColName){

  int i;
  cout<< " "<< (const char*)data;
  for(i=0; i<argc; i++){
     cout<< "=  "<< azColName[i]<< " "<<argv[i] ? argv[i] : "NULL";
  }
  cout<<endl;
  return 0;

}

int main() { int dbMessage;

sqlite3 *dbHandler; char * sql; char *zErrMsg = 0; int rc; const char* data = "Callback function called";

dbMessage=sqlite3_open(DBNAME, &dbHandler);

if (dbMessage==0) { cout<< "Database Opened Successfully"<<endl; cout<< "MAS ESPASE TA FRYDIA"<<endl; } else { cout<< "PROBLEM: Database not opened"<<endl; cout<<"EDO na deis poso mas espases ta frydia"<<endl; } /* sql = "CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,"\ "AGE INT NOT NULL," \ "ADDRESS CHAR(50)," \ "SALARY REAL );";

• /

sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \ "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \ "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \ "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); " \ "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \ "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \ "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \ "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );"; /* sql = "SELECT * from COMPANY";

• /

dbMessage= sqlite3_exec(dbHandler, sql, callback,(void*)data, &zErrMsg); if( rc != SQLITE_OK ){

cout<< "PROBLEMO"<<zErrMsg<<endl; sqlite3_free(zErrMsg); }else{ cout<< "ALL GOOd"<<endl; }

sqlite3_close(dbHandler);

return 0; }

</cpp>

Create trigger using SQLStudio

Create to tables <sql> CREATE TABLE COMPANY (

   ID      INT         PRIMARY KEY
                       NOT NULL,
   NAME    TEXT        NOT NULL,
   AGE     INT         NOT NULL,
   ADDRESS CHAR( 50 ),
   SALARY  REAL 

);

CREATE TABLE CURRENTEMPLOY (

   ID          INT  PRIMARY KEY,
   CURRENTNAME TEXT 

);

</sql>

• put one entry in the currentemploy table
• Choose trigger -> new trigger.
• Choose trigger name
• Choose table that will create the trigger and what action will create the trigger and write code that will be executed with the trigger

e.g

• Trigger name=set_current_name
• Action:INSERT
• ON TABLE :COMPANY
• CODE TO BE EXECUTED: UPDATE CURRENTEMPLOY SET CURRENTNAME = new . name WHERE ID = 1
• Every time that you insert an entry in company name the currentemply will be updated with the lates employee name.