If it can help, I did this by writing a small sqlitedb wrapper. You'll need sqlite3.exe so you can call it from "command line" (see: Command Line Shell For SQLite).
It's in an include file that references another include file ... so both are joined here in the package.
inc_sqlliteWrapper.opusscriptinstall (4.9 KB)
For reference, code for the wrapper :
// sqlLiteWrapper
// (c) 2024 Stephane
// This is an include file script for Directory Opus.
// See https://www.gpsoft.com.au/endpoints/redirect.php?page=scripts for development information.
// Called by Directory Opus to initialize the include file script
function OnInitIncludeFile(initData)
{
initData.name = "sqlLiteWrapper";
initData.version = "1.0";
initData.copyright = "(c) 2024 Stephane";
// initData.url = "https://resource.dopus.com/c/buttons-scripts/16";
initData.desc = "";
initData.min_version = "13.0";
initData.shared = true;
}
// Returns an object with 2 properties : result = the commande line result, lines = a Vector of strings
// Each entry of the vector is a string with the result (column values) of the select (each column is separated by |, so .split(/\|/); can make this an array of columns
function ExecuteSelectSQL(sqlitePath, sqlDbPath, sqlStatement) {
var fsu = DOpus.FSUtil;
var sql = fsu.Resolve(sqlitePath);
var db = fsu.Resolve(sqlDbPath);
var fso = new ActiveXObject("Scripting.FilesystemObject");
var shell = new ActiveXObject("WScript.Shell");
var tmpFileBase = "DO.Temp.";
var tmpFileExt = ".txt";
var tmpFileStdout = ExtSystem.GetTmpFileName(tmpFileBase, tmpFileExt, fso).fullname;
var tmpFileStderr = tmpFileStdout+".err"+tmpFileExt;
var cmdLine = '%comspec% /c "echo ' + sqlStatement + ' | ';
cmdLine += '"' + sql + '" "' + db + '"';
cmdLine += ' >"'+tmpFileStdout+'" 2>"'+tmpFileStderr+'""'
//dout ("Built command line = '" + cmdLine + "'");
var result = {};
result.cmd = cmdLine;
dout("Command line SQL Wrapper = '" + cmdLine + "'");
result.returncode = shell.Run(cmdLine, 0, true);
result.stdout = ExtSystem.ReadFile(tmpFileStdout, fso); fso.DeleteFile(tmpFileStdout);
result.stderr = ExtSystem.ReadFile(tmpFileStderr, fso); fso.DeleteFile(tmpFileStderr);
if (result.stderr.length > 0) dout ("Some errors occured ...", true);
//dout("Result errors : " + result.stderr);
// dout("RAW internal Result : (" + result.stdout.length + ") : '" + result.stdout + "'");
var lines = (result.stdout).split(/\r?\n/);
// dout(" #" + lines.length +" results (lines in result)");
var vResult = { "result" : result, "nbLines" : lines.length, "lines" : DOpus.Create.Vector };
for (var e = new Enumerator(lines); !e.atEnd(); e.moveNext())
{
var l = e.item();
// var detail = l.split('');
// for (var i = 0; i < detail.length; i++) {
// dout(detail[i]);
// }
if (typeof e.item() == "string") {
dout (">> type=" + typeof l + " (" + l.length + ") : " + l);
vResult.lines.push_back(e.item());
}
}
return vResult;
}
function ExecuteSelectSQLJSON(sqlitePath, sqlDbPath, sqlStatement) {
var fsu = DOpus.FSUtil;
var sql = fsu.Resolve(sqlitePath);
var db = fsu.Resolve(sqlDbPath);
var fso = new ActiveXObject("Scripting.FilesystemObject");
var shell = new ActiveXObject("WScript.Shell");
var tmpFileBase = "DO.Temp.";
var tmpFileExt = ".txt";
var tmpFileStdout = ExtSystem.GetTmpFileName(tmpFileBase, tmpFileExt, fso).fullname;
var tmpFileStderr = tmpFileStdout+".err"+tmpFileExt;
var cmdLine = '%comspec% /c "echo ' + sqlStatement + ' | ';
cmdLine += '"' + sql + '" -json "' + db + '"';
cmdLine += ' >"'+tmpFileStdout+'" 2>"'+tmpFileStderr+'""'
//dout ("Built command line = '" + cmdLine + "'");
var result = {};
result.cmd = cmdLine;
// dout("Command line SQL Wrapper = '" + cmdLine + "'");
result.returncode = shell.Run(cmdLine, 0, true);
result.stdout = ExtSystem.ReadFile(tmpFileStdout, fso); fso.DeleteFile(tmpFileStdout);
result.stderr = ExtSystem.ReadFile(tmpFileStderr, fso); fso.DeleteFile(tmpFileStderr);
if (result.stderr.length > 0) dout ("Some errors occured ...", true);
//dout("Result errors : " + result.stderr);
// dout("RAW internal Result : (" + result.stdout.length + ") : '" + result.stdout + "'");
if (result.stdout.length == 0) {
dout("No row returned.", true, true);
return { "result": result, "nbLines": 0, "json" : { } };
// return { "result": result, "nbLines": 0, "json" : { "sqlRequestReturn" : "empty" } };
}
try {
//dout("out length = " + result.stdout.length);
var jsObj = JSON.parse(result.stdout);
var vResult = { "result" : result, "nbLines" : jsObj.length, "json" : jsObj };
return vResult;
}
catch(e) {
dout("Error parsing JSON ...",true,true);
dout("RAW Out :\n" + result.stdout);
}
}
The "commons" include file :
// commonSAL
// (c) 2024 Stephane
// This is an include file script for Directory Opus.
// See https://www.gpsoft.com.au/endpoints/redirect.php?page=scripts for development information.
// Called by Directory Opus to initialize the include file script
function OnInitIncludeFile(initData)
{
initData.name = "commonSAL";
initData.version = "1.0";
initData.copyright = "(c) 2024 Stephane";
// initData.url = "https://resource.dopus.com/c/buttons-scripts/16";
initData.desc = "";
initData.min_version = "13.0";
initData.shared = true;
}
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
// Dialog Helpers
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
function MsgDialog(title, message, sourcetab) {
var dlg = DOpus.Dlg;
dlg.window = sourcetab;
dlg.title = title;
dlg.message = message;
dlg.buttons = "OK";
dlg.Show();
}
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
// Execute and get output
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////
function RunHiddenEx(exe, params, tmpFileBase, tmpFileExt, shell, fso) { //v1.2
if (!fso) fso = new ActiveXObject("Scripting.FilesystemObject");
if (!shell) shell = new ActiveXObject("WScript.Shell");
if (!tmpFileBase) tmpFileBase = "DO.Temp.";
if (!tmpFileExt) tmpFileExt = ".txt";
var tmpFileStdout = GetTmpFileName(tmpFileBase, tmpFileExt, fso).fullname;
var tmpFileStderr = tmpFileStdout+".err"+tmpFileExt;
var cmd = '%comspec% /c ""'+exe+'" '+params+' >"'+tmpFileStdout+'" 2>"'+tmpFileStderr+'""';
//dout ("CMD='" + cmd + "'");
var result = {};
result.cmd = cmd;
if (exe.match(/^([A-z]:\\.+|\\\\(.*?)\\.+)$/)) { //test path to exe if given
if (!fso.FileExists(exe)){
var msg = "E Executable not found ["+exe+"]";
DOpus.Output(msg);
throw msg;
}
//else dout (">> '" + exe + "' exists");
}
result.returncode = shell.Run( cmd, 0, true);
result.stdout = ReadFile(tmpFileStdout, fso); fso.DeleteFile(tmpFileStdout);
result.stderr = ReadFile(tmpFileStderr, fso); fso.DeleteFile(tmpFileStderr);
return result;
}
///////////////////////////////////////////////////////////////////////////////
function ReadFile ( path, fso ){
fso = fso || new ActiveXObject("Scripting.FilesystemObject");
var content = "";
if (!fso.FileExists(path)){
return content;
}
var file = fso.OpenTextFile( path, 1, -2); // Read, UseDefaultEncoding
if (!file.AtEndOfStream)
content = file.ReadAll();
file.Close();
return content;
}
///////////////////////////////////////////////////////////////////////////
function GetTmpFileName(prefix, extension, fso) {
fso = fso || new ActiveXObject("Scripting.FilesystemObject");
var tFolder = fso.GetSpecialFolder(2); //2 = temp folder
var tFile = fso.GetTempName();
if (prefix!=undefined) tFile=prefix+tFile;
if (extension!=undefined) tFile+=extension;
return {
path : tFolder.Path,
name : tFile,
fullname: tFolder.Path+'\\'+tFile
};
}
if (typeof ExtSystem === "undefined") {
var ExtSystem = (function () {
var my = {};
///////////////////////////////////////////////////////////////////////////////
my.RunHiddenEx = function RunHiddenEx(exe, params, tmpFileBase, tmpFileExt, shell, fso) { //v1.2
if (!fso) fso = new ActiveXObject("Scripting.FilesystemObject");
if (!shell) shell = new ActiveXObject("WScript.Shell");
if (!tmpFileBase) tmpFileBase = "DO.Temp.";
if (!tmpFileExt) tmpFileExt = ".txt";
var tmpFileStdout = this.GetTmpFileName(tmpFileBase, tmpFileExt, fso).fullname;
var tmpFileStderr = tmpFileStdout+".err"+tmpFileExt;
var cmd = '%comspec% /c ""'+exe+'" '+params+' >"'+tmpFileStdout+'" 2>"'+tmpFileStderr+'""';
//dout ("CMD='" + cmd + "'");
var result = {};
result.cmd = cmd;
if (exe.match(/^([A-z]:\\.+|\\\\(.*?)\\.+)$/)) { //test path to exe if given
if (!fso.FileExists(exe)){
var msg = "E Executable not found ["+exe+"]";
DOpus.Output(msg);
throw msg;
}
//else dout (">> '" + exe + "' exists");
}
result.returncode = shell.Run( cmd, 0, true);
//result.returncode = shell.Run( cmd, 1, true);
result.stdout = this.ReadFile(tmpFileStdout, fso); fso.DeleteFile(tmpFileStdout);
result.stderr = this.ReadFile(tmpFileStderr, fso); fso.DeleteFile(tmpFileStderr);
return result;
}
///////////////////////////////////////////////////////////////////////////////
my.ReadFile = function ReadFile(path, fso){
fso = fso || new ActiveXObject("Scripting.FilesystemObject");
var content = "";
if (!fso.FileExists(path)){
return content;
}
var file = fso.OpenTextFile( path, 1, -2); // Read, UseDefaultEncoding
if (!file.AtEndOfStream)
content = file.ReadAll();
file.Close();
return content;
}
///////////////////////////////////////////////////////////////////////////
my.GetTmpFileName = function GetTmpFileName(prefix, extension, fso) {
fso = fso || new ActiveXObject("Scripting.FilesystemObject");
var tFolder = fso.GetSpecialFolder(2); //2 = temp folder
var tFile = fso.GetTempName();
if (prefix!=undefined) tFile=prefix+tFile;
if (extension!=undefined) tFile+=extension;
return {
path : tFolder.Path,
name : tFile,
fullname: tFolder.Path+'\\'+tFile
};
}
return my;
}());
}
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
// Common types extension methods
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
if (!Array.prototype.contains) {
Array.prototype.contains = function(obj) {
var i = this.length;
while (i--) {
if (this[i] === obj) {
return true;
}
}
return false;
};
}
///////////////////////////////////////////////////////////////////////////
if (!Array.prototype.indexOf) {
Array.prototype.indexOf = function(item) {
for (var i=0; i<this.length; i++) {
if (this[i] == item) return i;
}
return -1;
}
}
///////////////////////////////////////////////////////////////////////////
if (!String.prototype.padStart) {
String.prototype.padStart = function padStart(targetLength,padString) {
targetLength = targetLength>>0; //truncate if number or convert non-number to 0;
padString = String((typeof padString !== 'undefined' ? padString : ' '));
if (this.length > targetLength) {
return String(this);
}
else {
targetLength = targetLength-this.length;
if (targetLength > padString.length) {
padString += padString.repeat(targetLength/padString.length); //append to original to ensure we are longer than needed
}
return padString.slice(0,targetLength) + String(this);
}
};
}
///////////////////////////////////////////////////////////////////////////
if (!String.prototype.repeat) {
String.prototype.repeat = function(count) {
'use strict';
if (this == null)
throw new TypeError('can\'t convert ' + this + ' to object');
var str = '' + this;
count = +count;
if (count != count)
count = 0;
if (count < 0)
throw new RangeError('repeat count must be non-negative');
if (count == Infinity)
throw new RangeError('repeat count must be less than infinity');
count = Math.floor(count);
if (str.length == 0 || count == 0)
return '';
// Ensuring count is a 31-bit integer allows us to heavily optimize the
// main part. But anyway, most current (August 2014) browsers can't handle
// strings 1 << 28 chars or longer, so:
if (str.length * count >= 1 << 28)
throw new RangeError('repeat count must not overflow maximum string size');
var maxCount = str.length * count;
count = Math.floor(Math.log(count) / Math.log(2));
while (count) {
str += str;
count--;
}
str += str.substring(0, maxCount - str.length);
return str;
};
}
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
// DOpus functions wrappers & helpers
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
// Helper dout function
function dout(msg, error, time) {
if (error == undefined) error = false;
if (time == undefined) time = true;
DOpus.output(msg, error, time);
}
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
// Misc helpfull methods
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////
function humanFileSize(size) {
var i = (size == 0) ? 0 : Math.floor(Math.log(size) / Math.log(1024));
return (size / Math.pow(1024, i)).toFixed(2) * 1 + ' ' + ['B', 'kB', 'MB', 'GB', 'TB'][i];
}
///////////////////////////////////////////////////////////////////////////
function FindIndex(array, item) {
for (var i=0; i<array.length; i++) {
if (array[i] == item) return i;
}
return -1;
}
///////////////////////////////////////////////////////////////////////////
function isEmptyOrSpaces(str){
return str === null || str.match(/^ *$/) !== null;
}
///////////////////////////////////////////////////////////////////////////////
// Helper function to build a string with an input string repeated n times
/**
* @param {string} mystring
* @param {number} times
*/
function repeatStringNumTimes(mystring, times)
{
var repeatedString = "";
while (times > 0) {
repeatedString += mystring;
times--;
}
return repeatedString;
}
///////////////////////////////////////////////////////////////////////////
function sanitizeFilename(input, replacement) {
// inspired from node sanitize filename : https://github.com/parshap/node-sanitize-filename/blob/master/index.js
if (typeof input !== 'string') {
throw new Error('Input must be string');
}
//var illegalRe = /[\/\?<>\\:\*\|"]/g;
var illegalRe = new RegExp('[\/\?<>\\:\*\|"]', "g");
var controlRe = /[\x00-\x1f\x80-\x9f]/g;
var reservedRe = /^\.+$/;
var windowsTrailingRe = /[\. ]+$/;
var sanitized = input
.replace(illegalRe, replacement)
.replace(controlRe, replacement)
.replace(reservedRe, replacement)
.replace(windowsTrailingRe, replacement);
return sanitized;
}
And finally a small excerp of what final call looks like :
var sqlStatement = "select distinct ksi.vendor, kcp.alias from k_content_path kcp join k_sound_info ksi on kcp.id = ksi.content_path_id where ksi.vendor IS NOT null AND kcp.path='" + (""+srcDir).replace(/^[kK]:/, "D").replace(/&/, '^&') + "'";
dbSearch = ExecuteSelectSQLJSON(Script.Config["sqlite3 Path"], Script.Config["Maschine komplete3.db path"], sqlStatement);
var res = dbSearch.json;
logger.debug("#" + res.length + " results returned");
if (res.length>1) {
logger.warn("Multiple aliases found !!!");
for (var i = 0; i < res.length; i++)
logger.warn(" >> Alias = '" + res[i].alias + "' | Vendor ='" + res[i].vendor + "'");
}
if (res.length == 1) {
logger.info("1 result found in komplete3.db => Product='" + res[0].alias + "' & Vendor='" + res[0].vendor + "'");
if (res[0].alias != uiCtxt.Product) logger.warn("Product inferred from path (" + uiCtxt.Product + ") is different from the one in DB (" + res[0].alias + ")");
if (res[0].vendor != uiCtxt.Vendor) logger.warn("Vendor inferred from path (" + uiCtxt.Vendor + ") is different from the one in DB (" + res[0].vendor + ")");
if (res[0].alias != null && res[0].alias.length > 0) uiCtxt.Product = res[0].alias;
if (res[0].vendor != null && res[0].vendor.length > 0) uiCtxt.Vendor = res[0].vendor;
}
The return from the ExecuteSelectSQLJSON has a json property which is an array of javascript object. Each item in the array will have a property coming from the way you construct your sql statement.
, new version below