One major new feature of NexusDB V3 is the ability to run server side pascal scripts. These scripts have full access to the server infrastructure (like server engine, monitors, settings) as well as to all databases. The initial release will only include a simple access control to this feature on the basis of a security token which can be assigned to a user (same as the R, W and A tokens in NexusDB V2). Incremental updates will introduce finer access control to the server infra structure and databases.
Let's take a look at a small script that executes a query and dumps the data to the output.
Example
var
aSession: TnxSession;
adb: TnxDatabase;
aQuery: TnxQuery;
i: integer;
begin
aSession:=TnxSession.Create(nil);
adb:=TnxDatabase.Create(nil);
aQuery:=TnxQuery.Create(nil);
try
aSession.ServerEngine:=ServerEngine;
aSession.Active:=true;
adb.AliasName:='Test';
adb.Session:=aSession;
adb.Open;
aQuery.Database:=adb;
aQuery.SQL.Text:='select * from test';
aQuery.Open;
while not aQuery.eof do
begin
for i:=0 to aQuery.FieldCount-1 do
Output.Write(aQuery.Fields.Fields[i].AsString+',');
Output.WriteLn('');
aQuery.Next;
end;
Output.SaveToFile('c:\out.txt');
finally
aQuery.Free;
adb.free;
aSession.Free;
end;
end.
As you can see, very straight forward, the only thing different to a standard Delphi function is the use of the global variable ServerEngine (which is a direct reference to the main server engine of the server where the script is executed) and the use of the global Output class instance (preliminary class members) to dump the data.
The script processing on the server side is implemented as a special handler of the also new nxRemoteRemoteCommands plugin. This plugin has an extensible Type parameter and one such type is cmdExecScript.
How to run a script?
Now that's the question is it. Simple to answer: All you need to do is call the plugins ExecCommand method. The server side implementation of the plugin creates an Alias 'ServerRemoteCommand' which will have the the output of the script buffered in a (per command unique) table.
One of the simplest ways start a script and get return the output is the following:
uses
...
nxdb, nxtwWinsockTransport, nxreRemoteServerEngine,
nxrcRemoteCommandsPluginClient, nxsdServerEngine, nxsdTypes
...
function ExecCommand(aserver: string; aport: integer;
auser, apassword, ascript, aparams: string): string;
var
Transport: TnxWinsockTransport;
ServerEngine: TnxRemoteServerEngine;
Session: TnxSession;
rc: TnxRemoteRemoteCommandsPlugin;
error: integer;
errormessage: string;
Taskinfo: TnxAbstractTaskInfo;
Database: TnxDatabase;
ProgressTable: TnxTable;
Completed: Boolean;
Status: TnxTaskStatus;
ResultData: TStringlist;
begin
Transport:=TnxWinsockTransport.Create(nil);
ServerEngine:=TnxRemoteServerEngine.Create(nil);
Session:=TnxSession.Create(nil);
rc:=TnxRemoteRemoteCommandsPlugin.Create(nil);
ResultData:=TStringlist.Create;
try
try
Transport.ServerNameRuntime:=aserver;
Transport.Port:=aport;
ServerEngine.Transport:=Transport;
Session.ServerEngine:=ServerEngine;
Session.UserName:=auser;
Session.Password:=apassword;
rc.Session:=Session;
rc.Active:=true;
rc.ExecCommand('cmdExecScript', ascript, aparams, error, errormessage, Taskinfo);
if (error<>0) or (Taskinfo=nil) then
ResultData.Add(inttostr(error)+' - '+errormessage)
else
begin
Database:=TnxDatabase.Create(nil);
ProgressTable:=TnxTable.Create(nil);
if Assigned(TaskInfo) then
try
Database.Session:=Session;
Database.AliasName:='ServerRemoteCommand';
Database.Open;
ProgressTable.Database:=Database;
ProgressTable.TableName:='ServerRemoteCommand_'+IntToStr(Taskinfo.RemoteID);
repeat
ProgressTable.Open;
while not ProgressTable.Eof do
begin
ResultData.Add(ProgressTable.FindField('Lines').AsString);
ProgressTable.Delete;
end;
ProgressTable.Close;
TaskInfo.GetStatus(Completed, Status);
if not Completed then begin
Sleep(500);
end;
until Completed;
ProgressTable.Open;
while not ProgressTable.Eof do
begin
Write(ProgressTable.FindField('Lines').AsString);
ProgressTable.Delete;
end;
ProgressTable.Close;
finally
ProgressTable.Free;
Database.Free;
TaskInfo.Free;
end;
end;
except
on E:Exception do
begin
ResultData.Add(E.Classname + ': ' + E.Message);
end;
end;
finally
result:=ResultData.Text;
rc.Free;
Session.Free;
ServerEngine.Free;
Transport.Free;
ResultData.Free;
end;
end;
Usage examples:
// exec script direct
procedure TformRemoteCommandTest.Button1Click(Sender: TObject);
var
params:TStringlist;
begin
params:=TStringlist.Create;
try
Params.Values['/direct']:='begin Output.Write(''abc'');end.';
Memo1.Lines.Text:=ExecCommand(
edServer.Text, strtointdef(edPort.Text, 16000),
edUser.Text, edPassWord.Text,
'', params.text);
finally
params.Free;
end;
end;
// exec SQL
procedure TformRemoteCommandTest.Button2Click(Sender: TObject);
var
params:TStringlist;
begin
params:=TStringlist.Create;
try
Params.Values['/database']:='Test';
Params.Values['/sql']:='select * from t1';
Params.Values['/delimiter']:=',';
Params.Values['/qualifier']:='"';
Memo1.Lines.Text:=ExecCommand(
edServer.Text, strtointdef(edPort.Text, 16000),
edUser.Text, edPassWord.Text,
'runsql.nxscript', params.text);
finally
params.Free;
end;
end;
// exec Backup
procedure TformRemoteCommandTest.Button3Click(Sender: TObject);
var
params:TStringlist;
begin
params:=TStringlist.Create;
try
Params.Values['/Source']:='Test';
Params.Values['/Destination']:='c:\testbackup';
Memo1.Lines.Text:=ExecCommand(
edServer.Text, strtointdef(edPort.Text, 16000),
edUser.Text, edPassWord.Text,
'Backup.nxscript', params.text);
finally
params.Free;
end;
end;
Now say hello to a very small but in combination with server scripting very powerful tool:
nxCommand.exe
This nifty utility is an extended command line implementation of above code and can be used to call NexusDB server side scripts. The command line arguments are as following:
nxCommand /Type:cmdExecScript /Script:myscript.nxscript [/param1:asneededbythescript ...]
See a screen shot here.
You can also execute a script directly by replacing the last parameter with
/Direct:"begin Output.Write('abc');end."
in future (together with updated security) we will of course add support for server, username and password arguments, but for the time being this will only work on a local server.
Script Repository
We will provide at least 2 scripts at launch which will be shipped with NexusDB and at the same time will be available from our upcoming online script repository. Every registered user of our web site will be able to access and add scripts in that global repository.
The first script runSQL.nxscript will execute a SQL command and allow to format the output with delimeters, quoting etc. This can be used to very quickly export some data to a CSV (or similar) file. Please look at the preliminary source for the details. You can call it like this (for a local server with no user/password):
nxCommand /Type:cmdExecScript /Script:runsql.nxscript /database:dbname
/sql:"select * from t1" /delimiter:%20 /qualifier:%22 > export.txt
This will create nice CSV formated file of the given query.
The second script is Backup.nxscript which can be used in the pre-backup event of your favourite backup solution to create a consistent backup of your database. Call it like that:
nxCommand /Type:cmdExecScript /Script:Backup.nxscript /Source:srcalias|srcpath
/Destination:destalias|destpath
nxCommand returns, like a good command line utility, an error code<>0 if something didn't work as expected and dumps the error message to STDOUT/STDERR.
(We're also considering to ship an extra nxScript.exe command line utility that would remove the need for explicitly specifying the Type parameter.)
