dao.js

function Note(id, target, type, comment, date, author)
{
  this.id = id;
  this.target = target;
  this.type = type;
  this.comment = comment;
  this.date = date;
  this.author = author;
}

function insert(author, target, type, comment)
{
  var sql = "INSERT INTO notes (target, type, comment, issuedate, enabled, author) VALUES (?, ?, ?, {fn now()}, 1, ?)";

  var connection = new ActiveXObject("ADODB.Connection");
  connection.Open('dsn=cubix');

  var cmd = new ActiveXObject("ADODB.Command");
  cmd.ActiveConnection = connection;
  cmd.CommandText = sql;
  cmd.CommandType = 1; // SQL statement

  var param = cmd.CreateParameter("@p1", 8, 1, 100, target);
  cmd.Parameters.Append(param);
  param = cmd.CreateParameter("@p2", 8, 1, 100, type);
  cmd.Parameters.Append(param);
  param = cmd.CreateParameter("@p3", 8, 1, 500, comment);
  cmd.Parameters.Append(param);
  param = cmd.CreateParameter("@p4", 8, 1, 100, author);
  cmd.Parameters.Append(param);

  try
  {
    cmd.Execute();
  }
  finally
  {
    connection.Close();
  }
}

function select(target)
{
  target = '*' + target + '*';
  target = target.replace(/\*/g, '%');
  target = target.replace(/\?/g, '_');
  if (('' + target).length == 0)
  {
    target = '%';
  }

  var sql = "SELECT id, target, type, comment, issuedate, author FROM notes WHERE (lower(target) like lower(?) or lower(comment) like lower(?)) and enabled = 1 order by issuedate desc";

  var connection = new ActiveXObject("ADODB.Connection");
  connection.Open('dsn=cubix');

  var cmd = new ActiveXObject("ADODB.Command");
  cmd.ActiveConnection = connection;
  cmd.CommandText = sql;
  cmd.CommandType = 1; // SQL statement

  var param = cmd.CreateParameter("@p1", 8, 1, 500, target);
  cmd.Parameters.Append(param);
  var param = cmd.CreateParameter("@p2", 8, 1, 500, target);
  cmd.Parameters.Append(param);

  var result = new Array();

  try
  {
    var rs = cmd.Execute();

    while (!rs.EOF)
    {
      var id = rs('id').Value;
      var target = rs('target').Value;
      var type = rs('type').Value;
      var comment = rs('comment').Value;
      var date = new Date(rs('issuedate').Value);
      var author = rs('author').Value;

      var note = new Note(id, target, type, comment, date, author);

      result.push(note);

      rs.MoveNext();
    }
  }
  finally
  {
    connection.Close();
  }

  return result;
}

function del(id)
{
  var sql = "UPDATE notes SET enabled = 0 WHERE id = ?";

  var connection = new ActiveXObject("ADODB.Connection");
  connection.Open('dsn=cubix');

  var cmd = new ActiveXObject("ADODB.Command");
  cmd.ActiveConnection = connection;
  cmd.CommandText = sql;
  cmd.CommandType = 1; // SQL statement

  var param = cmd.CreateParameter("@p1", 130, 1, 4, id);
  cmd.Parameters.Append(param);

  try
  {
    cmd.Execute();
  }
  finally
  {
    connection.Close();
  }
}