Digital Workshop

//Module containing sqlite3.dll-Wrapper-Functions, based on OpusScripts CallFn (developed by Olaf Schmidt)

//If you include this file under your Master-Page as external Script, then
//its functionality should be available in all your other Page-Scripts

var pOut = 0; //Module-Var, used to fake an Out-Param for the Opus-CallFn-Mechanism
var DBHdl = 0; //Module-Var, defining an opened DB, used in most wrapper-functions
var StmtHdl = 0; //Module-Var, defining an opened SQL-Statement, used in most wrapper-functions


//---------------------------- first some kernel32-Declarations ------------------------------------
var DllKernel = LoadDLL(SYSTEM_WINSYS_DIR + "kernel32.dll");

function GetOutPtr() {return DllKernel.CallFn("GlobalAlloc", false, "ulong", "ulong", 2, "ulong", 0);}
function DeRefOutPtr() {return DllKernel.CallFn("GlobalLock", false, "ulong", "ulong", pOut);} //small hack, to dereference the out-values for API-Params
function GetTickCount() {return DllKernel.CallFn("GetTickCount", false, "ulong");}
//--------------------------------------------------------------------------------------------------


//---------------------------- and now the sqlite3-Declarations ------------------------------------
var DllSQlite = LoadDLL(SYSTEM_PUBLICATION_DIR + "OpusSqlite\\sqlite3.dll"); //assuming a SubFolder OpusSQLite for your Project

function sqliteVersion() {return DllSQlite.CallFn("sqlite3_libversion", false, "string");}
function sqliteOpen(FileName) {return DllSQlite.CallFn("sqlite3_open", false, "ulong", "string", FileName, "ulong", pOut);}
function sqliteClose() {return DllSQlite.CallFn("sqlite3_close", false, "ulong", "ulong", DBHdl);}
function sqlitePrepare(SQL) {return DllSQlite.CallFn("sqlite3_prepare_v2", false, "ulong", "ulong", DBHdl, "string", SQL, "ulong", SQL.length, "ulong", pOut, "ulong", 0);}
function sqliteFinalize() {return DllSQlite.CallFn("sqlite3_finalize", false, "ulong", "ulong", StmtHdl);}
function sqliteColumnCount() {return DllSQlite.CallFn("sqlite3_column_count", false, "ulong", "ulong", StmtHdl);}
function sqliteColumnName(ZeroBasedIdx) {return DllSQlite.CallFn("sqlite3_column_name", false, "string", "ulong", StmtHdl, "ulong", ZeroBasedIdx);}
function sqliteStep() {return DllSQlite.CallFn("sqlite3_step", false, "ulong", "ulong", StmtHdl);}
function sqliteLastError() {return DllSQlite.CallFn("sqlite3_errmsg", false, "string", "ulong", DBHdl);}
//--------------------------------------------------------------------------------------------------

//------ and finally a simple wrapper implementation, using the low-level-Declarations above -------

//try to open an existing DB, failing if the file cannot be found
function OpenDB(DBPath)
{
   if (pOut==0) pOut = GetOutPtr(); //ensure, we do this only once (in case pOut is Null yet)
   
   CloseDB(); //ensure we close an eventually unclosed DBHdl first, before we open a new one
   
   if (FileExists(DBPath) == 0) //file does not exist
   {   
      Debug.trace("DB-File not found at: " + DBPath + "\n");
      return 1;//signalize an error with a return-value !=0
   }
   var result = sqliteOpen(DBPath);
   if (result == 0) DBHdl = DeRefOutPtr(); //Zero means no error - so we set the DBHdl-variable
   return result;
}

//a pendant to OpenDB above, but able to create a new DB, if the File doesn't exist
function CreateOrOpenDB(DBPath)
{
   if (pOut==0) pOut = GetOutPtr(); //ensure, we do this only once (in case pOut is Null yet)
   
   CloseDB(); //ensure we close an eventually unclosed DBHdl first, before we open a new one

   var result = sqliteOpen(DBPath); 
   if (result == 0) DBHdl = DeRefOutPtr(); //Zero means no error - so we set the DBHdl-variable
   return result; //a result-value of Zero means SQLite_OK, if not Zero, then a failure occured
}


//you need to enclose your DB-Interaction within one of the Open-Calls above and this one, which cleans up
function CloseDB()
{
   if (DBHdl != 0) sqliteClose();
   DBHdl = 0; //reset the DBHdl-variable to 0 after this call, to signalize the closed State
}

//delivers a RowSet, based on an SQL-String
function GetRowArray(SQL)
{
   
   PrepareStatement(SQL); //Ok, lets see, if the SQL-Statement is valid
   
   var Rows = new Array();
   if (StmtHdl==0) return Rows; //return with an unfilled Rows-Array, since the Statement was not valid
   
   var ColumnCount = sqliteColumnCount();
   
   //first we get the Column-Names
   var Columns = new Array();
   for (var j = 0; j < ColumnCount; j++) Columns[j] = sqliteColumnName(j);
    Rows[0] = Columns; //fill the Column-Name-Array into Row[0]
   
   //now the loop over the real record-content using sqliteStep
   var i = 0;
    while (sqliteStep() == 100) // 100 means: step was able to position at a (next) record
   {
      Columns = new Array(); //each Row gets a new SubArray for the Columns
      
      //and here's the Sub-Loop over the current Records-Columns, retrieving the content as Text
      for (var j = 0; j < ColumnCount; j++)
      {
         //here we call directly (instead using a nicer Definition as e.g. done for sqliteColumnName
         //inside the Declares-Section for the sqlite3.dll above), because this is the most time-consuming part
         Columns[j] = DllSQlite.CallFn("sqlite3_column_text", false, "string", "ulong", StmtHdl, "ulong", j)
      }
      i++; Rows[i] = Columns;
   }
   
   sqliteFinalize(); StmtHdl = 0; //reset our Statement-Handle
   return Rows;
}

function ExecuteStatement(SQL)
{
   PrepareStatement(SQL); //Ok, lets see, if the SQL-Statement is valid
   if (StmtHdl==0) return false; //no success with the Statement, so we return false
   
   var result = sqliteStep();
   if ((result < 100) && (result > 101)) //an error in sqliteStep
   {
      sqliteFinalize(); StmtHdl = 0; //reset our Statement-Handle
      return false;
   }
   
   sqliteFinalize(); StmtHdl = 0; //reset our Statement-Handle
   return true;
}

//small helper, used to prepare and check an SQL-statement for validity
function PrepareStatement(SQL)
{ 
   if (StmtHdl != 0) sqliteFinalize(); //make sure the StmtHdl is closed, before we try to get a new one
   StmtHdl = 0;
   
   if (sqlitePrepare(SQL) != 0)
   {
      Debug.trace(sqliteLastError() + "\n"); //put out the error with the SQL-statement
      return 0;
   }
   StmtHdl = DeRefOutPtr(); //success with preparing the SQL-Statement, so we set the appropriate Handle
}