MentDB  Mentalese Database Engine
Strong AI, Weak AI, Interoperability and Software Development
Download MentDB Weak
    Home  >  MQL Examples (Weak version)
SQL execution
Execute a remote MQL command
Copy file (binary mode)
Copy file (text mode)
Insert a CSV file into a database
Get data from Excel
Copy Excelx to MySQL
Download email attachments
Download mail from POP3
Parse a directory
Parse a text file
Download a file throught FTP
Upload a file throught FTP
Download a file throught FTPs
Upload a file throught FTPs
Download a file throught sFTP
Upload a file throught sFTP
Parse JSON Object and Array
Scan directory
Parse XML file
More examples in videos ...
SQL execution #top
#Create a connection 'demo_cm_mysql' to a MySQL Server;
cm set "demo_cm_mysql" {execute "db.mysql.config.get"
	"[hostname]" "localhost"
	"[port]" "3306"
	"[database]" "test_db"
	"[user]" "bob"
	"[password]" "pwd"
;};

#Connect with the connection;
sql connect "session1" {cm get "demo_cm_mysql";};

#Mark the connection as auto commit = false;
sql auto_commit "session1" false;

#Get one value (first value from a column) from a table;
-> "[oneValue]" (sql value "session1" (concat "select name from products where id=1"));

#Get a row from a table (as JSON object);
-> "[oneRow]" (sql row "session1" (concat "select * from products where id=" (sql encode 1)));

#Get a table as JSON object;
-> "[oneTable]" (sql to json "session1" "products" (concat "select * from products"));

#Get a table as XML object;
-> "[oneTable]" (sql to xml "session1" "products" (concat "select * from products"));

#Get a table as CSV object;
-> "[oneTable]" (sql to csv "session1" "products" (concat "select * from products") "," "'");

#Parse a table and execute action on each line;
sql parse "session1" "T" (concat "select name from products") {

	log trace [T_name];

};

#Execute an insert/update/delete SQL request;
sql dml "session1" (concat "insert into products (id, name, quantity) values (4, 'other', '4567');");

#Commit the transaction;
sql commit "session1";

#Rollback the transaction;
sql rollback "session1";

#Disconnect a connection;
sql disconnect "session1";

#Disconnect all connections;
sql disconnect all;
                
Execute a remote MQL command #top
try {

	#Create a remote MentDB connection;
	cm set "demo_cm_mentdb" {execute "mentdb.remote.config.get"
		"[hostname]" "localhost"
		"[port]" "9998"
		"[key]" "pwd"
		"[user]" "admin"
		"[password]" "pwd"
		"[connectTimeout]" "10000"
		"[readTimeout]" "30000"
		"[subTunnels]" (mql {
	
		[MQL_TO_REPLACE]
	
	})
	;};

	#Build the remote MQL command;
	-> "[mql]" (mql {

		#Get the content of a file;
		file load "data/file.csv";
	
	});

	#Connect to a remote server;
	tunnel connect "session1" {cm get "demo_cm_mentdb";};

	#Execute the remote MQL command and save the result into the variable '[result]';
	-> "[result]" (tunnel execute "session1" [mql]);

	#Close the remote connection;
	tunnel disconnect "session1";

	#Return the result (always the last command);
	[result]

} {

	#Close objects;
	try {tunnel disconnect "session1";} {} "[sub_err]";

	#Generate an error;
	exception (1) ([err]);

} "[err]";
                
Copy file (binary mode) #top
#Initialization;
-> "[source]" "/Users/jimmitry/Desktop/test.jpg";
-> "[destination]" "/Users/jimmitry/Desktop/copy.jpg";

try {

	#Open a reader;
	file reader_open "r1" [source] BINARY null;
	file writer_open "w1" [destination] true BINARY null;

	#Parse the file;
	while (is not null (-> "[bytes]" (file reader_get_bytes "r1" 1024));) {

		#Write the bytes tab;
		file writer_add_bytes "w1" [bytes];

	};

	#Force to write;
	file writer_flush "w1";

	#Close the reader and the writer;
	file reader_close "r1";
	file writer_close "w1";

} {

	#Close objects;
	try {file reader_close "r1";} {} "[sub_err]";
	try {file writer_close "w1";} {} "[sub_err]";

	#Generate an error;
	exception (1) ([err]);

} "[err]";
                
Copy file (text mode) #top
#Initialization;
-> "[source]" "/Users/jimmitry/Desktop/test.txt";
-> "[destination]" "/Users/jimmitry/Desktop/copy.txt";

try {

	#Open a reader;
	file reader_open "r1" [source] TEXT "utf-8";
	file writer_open "w1" [destination] false TEXT "utf-8";

	#Parse the file;
	while (is not null (-> "[line]" (file reader_get_line "r1"));) {

		#Add the line and LF char;
		file writer_add_line "w1" (concat [line] [_n_]);

	};

	#Force to write;
	file writer_flush "w1";

	#Close the reader and the writer;
	file reader_close "r1";
	file writer_close "w1";

} {

	#Close objects;
	try {file reader_close "r1";} {} "[sub_err]";
	try {file writer_close "w1";} {} "[sub_err]";

	#Generate an error;
	exception (1) ([err]);

} "[err]";
                
Insert a CSV file into a database #top
#Connect to MySQL server;
sql connect "session1" {cm get "demo_cm_mysql";};

#Parse a CSV file and make actions on each line;
csv parse (mql "T") (mql "/Users/jimmitry/Desktop/file.csv") (mql ",") (mql "'") (mql "A,B,C") {

	log trace [T_A];

	#Insert an element into a table;
	sql dml "session1" (concat "insert into products (id, name, quantity) values (
		" (sql encode [T_A]) ", 
		" (sql encode [T_B]) ", 
		" (sql encode [T_C]) "
	);");

};

#Disconnect the SQL connection;
sql disconnect "session1";
                
Get data from Excel #top
#Get the Excel file;
-> "[localDir]" "/Users/jimmitry/Desktop";
-> "[fileName]" "test.xls";

#Initialization;
-> "[start_line]" 1;
-> "[end_line]" 10;
-> "[start_col]" 1;
-> "[end_col]" 5;

try {

	#Load the Excel file;
	excel load "excelId" (concat [localDir] "/" [fileName]);

	for (-> "[row]" [start_line]) (<= [row] [end_line]) (++ "[row]") {
	
		for (-> "[col]" [start_col]) (<= [col] [end_col]) (++ "[col]") {
		
			-> "[value]" (excel cell get "excelId" "sheet1" [row] [col]);

			#Your code here;
			log trace [value];
		
		};
	
	};

	#Close the Excel object;
	excel close "excelId";

} {

	#Close objects;
	try {excel close "excelId";} {} "[sub_err]";

	#Generate an error;
	exception (1) ([err]);

} "[err]";
                
Copy Excelx to MySQL #top
#Get the Excelx file;
-> "[localDir]" "/Users/jimmitry/Desktop";
-> "[fileName]" "test.xlsx";

#Initialization;
-> "[start_line]" 1;
-> "[end_line]" 10;
-> "[start_col]" 1;
-> "[end_col]" 5;

try {

	#Connect to MySQL server;
	sql connect "session1" {cm get "demo_cm_mysql";};

	#Load the Excelx file;
	excelx load "excelId" (concat [localDir] "/" [fileName]);

	for (-> "[row]" [start_line]) (<= [row] [end_line]) (++ "[row]") {
	
		-> "[B]" (excelx cell get "excelId" "sheet1" [row] 1);
		-> "[C]" (excelx cell get "excelId" "sheet1" [row] 2);
		-> "[D]" (excelx cell get "excelId" "sheet1" [row] 3);

		#Insert into the database;
		sql dml "session1" (concat "insert into products (id, name, quantity) values (
			" (sql encode [B]) ", 
			" (sql encode [C]) ", 
			" (sql encode [D]) "
		);");
	
	};

	#Disconnect the database;
	sql disconnect "session1";

	#Close the Excel object;
	excelx close "excelId";

} {

	#Close objects;
	try {excelx close "excelId";} {} "[sub_err]";
	try {sql disconnect "session1"} {} "[sub_err]";

	#Generate an error;
	exception (1) ([err]);

} "[err]";
                
Download a file throught FTP #top
#Create the script to download;
script create post "demo.file.ftp.download" false 1 
  (param
  	(var "[remoteDirectory]" {true} "The remote directory" is_null:false is_empty:false "/Users/jimmitry/Desktop")
  	(var "[filter]" {true} "The file filter from the remote directory" is_null:false is_empty:false "*.png")
  	(var "[localDirectory]" {true} "The local directory" is_null:false is_empty:false "/Users/jimmitry/Desktop/ftp")
  ;) 
  "Download file through FTP" 
{

	try {

		#Connect to the FTP server;
		ftp connect "session1" {cm get "demo_cm_ftp";};
		ftp set type "session1" "BINARY";

		#Move to the remote directory;
		ftp cd "session1" [remoteDirectory];

		#Get all remote files;
		json load "files" (ftp ls "session1" [filter]);

		#Get all files;
		-> "[nbFiles]" (json count "files" "/");
		for (-> "[i]" 0) (< [i] [nbFiles]) (++ "[i]") {

			-> "[filename]" (json select "files" (concat "/[" [i] "]/name"));
		
			ftp get "session1" [filename] (concat [localDirectory] "/" [filename]);
		
		};

		#Disconnect the session;
		ftp disconnect "session1";
	
	} {
	
		#Close objects;
		try {ftp disconnect "session1";} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
	
	} "[err]";
	
	
} "Return nothing";

#Execute the script;
execute "demo.file.ftp.download.post"
	"[remoteDirectory]" "/Users/jimmitry/Desktop"
	"[filter]" "*.png"
	"[localDirectory]" "/Users/jimmitry/Desktop/ftp"
;
                
Upload a file throught FTP #top
#Create a script to upload;
script create post "demo.file.ftp.upload" false 1 
  (param
  	(var "[localDirectory]" {true} "The local directory" is_null:false is_empty:false "/Users/jimmitry/Desktop/ftp")
  	(var "[filter]" {true} "The file filter from the remote directory" is_null:false is_empty:false ".*png")
  	(var "[remoteDirectory]" {true} "The remote directory" is_null:false is_empty:false "/Users/jimmitry/Desktop")
  ;) 
  "Upload file through FTP" 
{

	try {

		#Connect to the FTP server;
		ftp connect "session1" {cm get "demo_cm_ftp";};
		ftp set type "session1" "BINARY";

		#Move to the remote directory;
		ftp cd "session1" [remoteDirectory];

		#Get all local files;
		json load "files" (file dir_list_regex [localDirectory] [filter] true true);

		#Put all files;
		-> "[nbFiles]" (json count "files" "/");
		for (-> "[i]" 0) (< [i] [nbFiles]) (++ "[i]") {

			-> "[filename]" (json select "files" (concat "/[" [i] "]"));
		
			ftp put "session1" (concat [localDirectory] "/" [filename]) "RESUME";
		
		};

		#Disconnect the session;
		ftp disconnect "session1";
	
	} {
	
		#Close objects;
		try {ftp disconnect "session1";} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
	
	} "[err]";
	
	
} "Return nothing";

#Execute the script;
execute "demo.file.ftp.upload.post"
	"[localDirectory]" "/Users/jimmitry/Desktop/ftp"
	"[filter]" ".*png"
	"[remoteDirectory]" "/Users/jimmitry/Desktop"
;
                
Download a file throught FTPs #top
#Create a script to download;
script create post "demo.file.ftps.download" false 1 
  (param
  	(var "[remoteDirectory]" {true} "The remote directory" is_null:false is_empty:false "/Users/jimmitry/Desktop")
  	(var "[filter]" {true} "The file filter from the remote directory" is_null:false is_empty:false ".*png")
  	(var "[localDirectory]" {true} "The local directory" is_null:false is_empty:false "/Users/jimmitry/Desktop/ftp")
  ;) 
  "Download file through FTPS" 
{

	try {

		#Connect to the FTPS server;
		ftps connect "session1" {cm get "demo_cm_ftps";};
		ftps set type "session1" "BINARY";

		#Move to the remote directory;
		ftps cd "session1" [remoteDirectory];

		#Get all remote files;
		json load "files" (ftps ls "session1" [filter]);

		#Get all files;
		-> "[nbFiles]" (json count "files" "/");
		for (-> "[i]" 0) (< [i] [nbFiles]) (++ "[i]") {

			-> "[filename]" (json select "files" (concat "/[" [i] "]/name"));
		
			ftps get "session1" [filename] (concat [localDirectory] "/" [filename]);
		
		};

		#Disconnect the session;
		ftps disconnect "session1";
	
	} {
	
		#Close objects;
		try {ftps disconnect "session1";} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
	
	} "[err]";
	
	
} "Return nothing";

#Execute the script;
execute "demo.file.ftps.download.post"
	"[remoteDirectory]" "/Users/jimmitry/Desktop"
	"[filter]" ".*png"
	"[localDirectory]" "/Users/jimmitry/Desktop/ftp"
;
                
Upload a file throught FTPs #top
#Create a script to upload;
script create post "demo.file.ftps.upload" false 1 
  (param
  	(var "[localDirectory]" {true} "The local directory" is_null:false is_empty:false "/Users/jimmitry/Desktop/ftp")
  	(var "[filter]" {true} "The file filter from the remote directory" is_null:false is_empty:false ".*png")
  	(var "[remoteDirectory]" {true} "The remote directory" is_null:false is_empty:false "/Users/jimmitry/Desktop")
  ;) 
  "Upload file through FTPS" 
{

	try {

		#Connect to the FTPS server;
		ftps connect "session1" {cm get "demo_cm_ftps";};
		ftps set type "session1" "BINARY";

		#Move to the remote directory;
		ftps cd "session1" [remoteDirectory];

		#Get all local files;
		json load "files" (file dir_list_regex [localDirectory] [filter] true true);

		#Put all files;
		-> "[nbFiles]" (json count "files" "/");
		for (-> "[i]" 0) (< [i] [nbFiles]) (++ "[i]") {

			-> "[filename]" (json select "files" (concat "/[" [i] "]"));
			
			ftps put "session1" (concat [localDirectory] "/" [filename]) (concat [localDirectory] "/" [filename]) "RESUME";
		
		};

		ftps disconnect "session1";
	
	} {
	
		#Close objects;
		try {ftps disconnect "session1";} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
	
	} "[err]";
	
	
} "Return nothing";

#Execute the script;
execute "demo.file.ftps.upload.post"
	"[localDirectory]" "/Users/jimmitry/Desktop/ftp"
	"[filter]" ".*png"
	"[remoteDirectory]" "/Users/jimmitry/Desktop"
;
                
Download email attachments #top
#Initialization;
-> "[receive_dir]" "tmp";

#Get mails and load the JSON result;
json load "receive_state" (mail download imap [receive_dir]
	3 unread null false true
	"2018-01-01" null
	(mql {string matches [imap_from] ".*digest-noreply@quora.com.*";})
	(mql {string matches [imap_subject] ".*champ.*";})
	{cm get "demo_cm_imap"}
);

#Handle if the number of receved mails > 0;
if (> (json select "receive_state" "/NbReceived") 0) {

	#Get the JSON mails folder;
	-> "[sub_receive_dir]" (json select "receive_state" "/Directory");

	#Get the file list;
	json load "sub_receive_dir" (file dir_list [sub_receive_dir]);

	#Parse the file list;
	json parse_array "sub_receive_dir" "/" "[mail]" {

		#If the file ends with json;
		if (string ends_with [mail] ".json") {

			#Load the mail (full JSON);
			json load "current_mail" (file load (concat [sub_receive_dir] "/" [mail]));

			#Show the current mail into the log files;
			log trace (json doc "current_mail");

			#Parse attachements;
			json parse_array "current_mail" "/Parts" "part" {

				#If file type;
				if (equal (json select "part" "/type") "file") {

					#Get the filename of the attachement;
					-> "[filename]" (json select "part" "/filename");

					#Save the file;
					file b64_write (json select "part" "/content_b64") (concat "tmp/" [filename]);

				};

			};

		};
	
	};

};

#Return the JSON result that contains the mails folder and the number of downloaded mails;
json doc "receive_state";
                
Parse JSON Object and Array #top
#Load a JSON object;
json load "keyId" "{}";

#Insert a number;
json iobject "keyId" / i 0.9 NUM;

#Insert an array;
json iobject "keyId" / a "[]" ARRAY;

#Insert values into the array;
json iarray "keyId" /a 1 INT;
json iarray "keyId" /a 2 INT;
json iarray "keyId" /a 3 STR;

#Parse the object;
json parse_obj "keyId" "/" "[key]" "[val]" {

	#Here your MQL source code ...;
	log trace (concat [key] "=" [val]);

};

#Parse the array;
json parse_array "keyId" "/a" "[val_array]" {

	log trace [val_array];

};

#Show the JSON object;
json doc "keyId";
                
Parse a directory #top
#Load the directory;
-> "[localDir]" "/Users/jimmitry/Desktop";

#Get file list;
json load "files" (file dir_list [localDir]);

#Parse the file list (method 1);
json parse_array "files" "/" "[cur_filename]" {

	#Here your action;
	log trace [cur_filename];

};

#Parse the file list (method 2);
-> "[nb_files]" (json count "files" /);
for (-> "[i]" 0) (< [i] [nb_files]) (++ "[i]") {

	#Get the current filename;
	-> "[cur_filename]" (json select "files" (concat "/[" [i] "]"));

	#Here your action;
	log trace [cur_filename];
	
};
                
Parse a text file #top
#Create a script to parse a file;
script create post "demo.file.parse_text" false 1 
  (param
  	(var "[fileName]" {true} "The file name" is_null:false is_empty:false "test.txt")
  ;) 
  "Parse a text file" 
{

	#Load the connection id;
	json load "cm" (cm get "demo_cm_file");
	-> "[localDir]" (json select "cm" /localDir);

	#Open a reader;
	file reader_open "r1" (concat [localDir] "/" [fileName]) TEXT "utf-8";

	try {

		#Parse the file;
		while (is not null (-> "[line]" (file reader_get_line "r1"));) {
	
			log trace (concat "line=" [line]);
	
		};

		#Close the reader;
		file reader_close "r1";
	
	} {

		#Close the reader;
		try {file reader_close "r1";} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
	
	} "[err]";

;} "Return 1";

#Execute the script;
execute "demo.file.parse_text.post"
	"[fileName]" "test.txt"
;
                
Download mail from POP3 #top
#Initialization;
-> "[Directory]" "/Users/jimmitry/Desktop/tmp";

#Mail download;
json load "pop3" (mail download pop3 [Directory]
3 true
{cm get "demo_cm_pop3";});

#Get mail if the number of received > 0;
if (> (json select "pop3" "/NbReceived") 0) {

	#Get the file directory;
	json load "files" (file dir_list [Directory]);

	#Get mails;
	-> "[nb_files]" (json count "files" /);
	for (-> "[i]" 0) (< [i] [nb_files]) (++ "[i]") {
	
		-> "[cur_filename]" (json select "files" (concat "/[" [i] "]"));

		#Load the mail;
		json load "mail" (concat [Directory] "/" [cur_filename]);
		
		#Here your action;
		
		
	};

};
                
Download a file throught sFTP #top
#Create the script to download;
script create post "demo.file.sftp.download" false 1 
  (param
  	(var "[remoteDirectory]" {true} "The remote directory" is_null:false is_empty:false "/Users/jimmitry/Desktop")
  	(var "[filter]" {true} "The file filter from the remote directory" is_null:false is_empty:false "*png")
  	(var "[localDirectory]" {true} "The local directory" is_null:false is_empty:false "/Users/jimmitry/Desktop/ftp")
  ;) 
  "Download file through SFTP" 
{

	try {

		#Connect to the SFTP server;
		sftp connect "session1" {cm get "demo_cm_sftp";};

		#Move to the remote directory;
		sftp cd "session1" [remoteDirectory];

		#Get all remote files;
		json load "files" (sftp ls "session1" [filter]);

		#Get all files;
		-> "[nbFiles]" (json count "files" "/");
		for (-> "[i]" 0) (< [i] [nbFiles]) (++ "[i]") {

			-> "[filename]" (json select "files" (concat "/[" [i] "]/name"));
		
			sftp get "session1" [filename] (concat [localDirectory] "/" [filename]);
		
		};

		#Disconnect the session;
		sftp disconnect "session1";
	
	} {
	
		#Close objects;
		try {sftp disconnect "session1";} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
	
	} "[err]";
	
	
} "Return nothing";

#Execute the script;
execute "demo.file.sftp.download.post"
	"[remoteDirectory]" "/Users/jimmitry/Desktop"
	"[filter]" "*png"
	"[localDirectory]" "/Users/jimmitry/Desktop/ftp"
;
                
Upload a file throught sFTP #top
#Create the script to upload;
script create post "demo.file.sftp.upload" false 1 
  (param
  	(var "[localDirectory]" {true} "The local directory" is_null:false is_empty:false "/Users/jimmitry/Desktop/ftp")
  	(var "[filter]" {true} "The file filter from the remote directory" is_null:false is_empty:false ".*png")
  	(var "[remoteDirectory]" {true} "The remote directory" is_null:false is_empty:false "/Users/jimmitry/Desktop")
  ;) 
  "Upload file through SFTP" 
{

	try {

		#Connect to the SFTP server;
		sftp connect "session1" {cm get "demo_cm_sftp";};

		#Move to the remote directory;
		sftp cd "session1" [remoteDirectory];

		#Get all local files;
		json load "files" (file dir_list_regex [localDirectory] [filter] true true);

		#Put all files;
		-> "[nbFiles]" (json count "files" "/");
		for (-> "[i]" 0) (< [i] [nbFiles]) (++ "[i]") {

			-> "[filename]" (json select "files" (concat "/[" [i] "]"));
			
			sftp put "session1" (concat [localDirectory] "/" [filename]) (concat [localDirectory] "/" [filename]) "RESUME";
			
		};

		#Disconnect the session;
		sftp disconnect "session1";
	
	} {
	
		#Close objects;
		try {sftp disconnect "session1";} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
	
	} "[err]";
	
	
} "Return nothing";

#Execute the script;
execute "demo.file.sftp.upload.post"
	"[localDirectory]" "/Users/jimmitry/Desktop/ftp"
	"[filter]" ".*png"
	"[remoteDirectory]" "/Users/jimmitry/Desktop"
;
                
Scan directory #top
#Create a script that will be executed when the directory is modified;
script create exe "demo.file.watcher" false 1 
  (param
  	(var "[kind]" {true} "The kind" is_null:true is_empty:true "")
  	(var "[file]" {true} "The file" is_null:true is_empty:true "")
  ;) 
  "description ..." 
{

	#Get file change and kind;
	log trace (concat "[kind]=" [kind] ", [file]=" [file]);
	
} "Return ...";

#Start the file watcher (linked with the script name);
file_watcher start "fKey" "admin" "/Users/jimmitry/Desktop/dir" "demo.file.watcher.exe";
                
Parse XML file #top
#Initialization;
-> "[targetPath]" "/data/item";

#Load variables;
-> "[localDir]" "/Users/jimmitry/Desktop";
-> "[fileName]" "test.xml";

#Load the file;
xml load "xmlId1" (file load (concat [localDir] "/" [fileName]));

-> "[nb]" (xml count "xmlId1" [targetPath]);

#Parse the tab;
for (-> "[i]" 1) (<= [i] [nb]) (++ "[i]") {

	#Get value;
	-> "[to_use]" (xml select text "xmlId1" (concat [targetPath] "[" [i] "]"));

	#Your action here ...;
	log trace (concat "[to_use]=" [to_use]);

	

};
                
    Home  >  MQL Examples
MentDB © 2019 - Legal Notice