Sample JScripts scripts

Creating and saving a new database
Adding and removing records, updating calculated fields
Adding, removing and renaming tables and subfolders
Importing tables from GS-Base databases, text and Excel files
Merging records from all text and Excel files in a folder
Formatting record fields
Setting column/field widths and row heights
Browsing the folders/tables tree
Searching and sorting
Predefined searching
File password protection
Enabling sharing databases and text, Excel and other files
Opening and saving text files
Error handling

List of all properties and functions

File/database opening
Saving databases
Saving databases as new files
Saving recordsets
Importing tables databases, text and Excel files
Performing JOIN operations for tables in the same database file
Merging/adding records from other files
Switching the active table
Record counters
Searching
Adding, modifying and removing fields
Browsing the database folder and tables tree structure
Editing records
Inserting series
Changing column widths and row heights
Setting record flags
Setting database passwords for GS-Base *.gsb/*.zip files
File information and access
Updating all calculation formulas
Input/output UI functions
Window functions
Obtaining the last error details

To make scripting accessible, you need to perform one-time GS-Base scripting registration using the "Settings > Register GS-Base Scripting" command in the admin mode. (Right-click the GS-Base shortcut or file and choose "Run As Administrator", register, close GS-Base). This registers GS-Base in Windows registry as a program that can be scripted not only in GS-Base, but also using external programming tools. (The "Unregister(...)" command removes the added entries.)

You can create your scripts either as global scripts saved in the program settings and available to all databases or you can create scripts stored in a given GS-Base database and available only after you open that database.
To create these scripts use the "File > Application Scripts" and "File > Database Scripts" commands.

The "(...) Scripts" dialog box enables you to organize your scripts in subfolders, test them to locate errors, copy/import/export them etc.

Click here to see sample "Scripts" screen.

Notes:

• If a global script is executed when there is no open database, it should start with one of the database Open(...)/New(..) functions

• If a database is already loaded, an executed script, either global or local, automatically refers to that database and using the Open(...) functions with the same database path has no effect.

Creating and saving a new database

//// a new database with a the "table1" table
GSBase.NewDatabase("table1");

// insert another "table1" in the root ("") folder at the bottom;
// as "table1" already exists, the uniqueName will contain a unique
// name table1(1)...table1(n) that GS-Base will create and use instead;
var uniqueName = GSBase.InsertDatabaseItem("", 1, "table1");

var field = GSBase.CreateFieldParams();

// add one Text field to the currently selected table;
//
// adding and importing a table make it 'selected' automatically;
// deleting tables may result in a new selection;
// SetActiveTable() and GetActiveTable() set and retrieve the selection
//
field.name = "field1";
// T - text field
// N - numeric field
// M - long text / Memo
// O - objects (images, files etc.)
// C - code field (long text with specific syntax highlighting)
field.type = 'T';
GSBase.AppendField(field);

//add one Number field with range validation
//
field.Reset();
field.name = "field2";
field.type = 'N';
field.formula = "=(field2 > 1) * (field2 < 10)";
// 1 - calculation formula/calculated field
// 2 - validation formula
// 3 - conversion formula
// 4 - default value
// 5 - incremented maximum
field.formulaType = 2;
GSBase.AppendField(field);

//add one calculated Number field
//
field.Reset();
field.name = "field3";
field.type = 'N';
field.formula = "=field2 * 10";
field.formulaType = 1;
GSBase.AppendField(field);

//insert one more Text field at the beginning of the record
field.Reset();
field.name = "field4";
field.type = 'T';
GSBase.InsertField(1, field);

//add one Code field that uses the "cpp" syntax highlighting
field.Reset();
field.name = "field5";
field.type = 'C';
//subtypes same as in the "Field Setup": "cpp", "assembler", "php"...
field.subtype = "cpp";
GSBase.AppendField(field);

//choose to use the standard zip (zip32) file format for the new database;
the default value for new files is "true" ("use zip64");
for existing database files their the default value is the one that was used previously;

//GSBase.zip64 = true;
GSBase.zip64 = false;

//save a new database (with one table and no records so far);
GSBase.SaveDatabaseAs("e:\\test_dbase.gsb");
GSBase.Close();

Adding and removing records, updating calculated fields

var password = "fhE4!lko"
GSBase.OpenDatabase("e:\\test_dbase.gsb", password);

//all editing actions always refer to the currently selected table;
//once a table is selected (and the database is saved), it remains selected till you change this;
//
if (GSBase.GetActiveTable() != "table1")
	GSBase.SetActiveTable("table1");

//as the table contains the calculated "field3", for performance reasons turn off
//recalculation of each row which would otherwise occur after each of the 10,000 modifications below;
//to restore the default automatic updating use "automatic" or re-open the database;
GSBase.updateMode = "manual";

//NOTE: InsertText() and InsertNumber() do exactly what plain entering data does
//which means they set up Undo information and refreshes the screen which makes them slow. If you need
//to fill millions of fields instantly, use the Insert(...)Series() functions family instead.

//NOTE: for best performance, when inserting a large series of data, always fill the table fields
//in the "top to bottom" (and "left to right") order. The "bottom to top" order may
//be considerably slower.

//insert the following date string in the 1st record field in 100 records;
var today = "2021-01-15";
for (i = 1; i <= 100; ++i)
{
	GSBase.InsertText(i, 1, today);
}

//insert some numbers in the 3rd record field in the first 100 records
for (i = 1; i <= 100; ++i)
{
	GSBase.InsertNumber(i, 3, 2.0 + i%8);
}

//update all calculated fields in this table using 4 processor cores
GSBase.UpdateTable(4);

//get the sum of the 4th field for the entire current record set
var counter = GSBase.GetRecordSetCount();
var sum = 0;
for (i = 1; i <= counter; ++i)
	sum = sum + GSBase.GetNumber(i, 4);

//clear the 3rd field in records 11 to 21
GSBase.ClearRange(11, 3, 21, 3);

//update all "field3" calculated fields as the "manual" update mode was set earlier
GSBase.UpdateTable(4);

//remove record 1
GSBase.RemoveRecords(1, 1);
//remove records 2 to 3
GSBase.RemoveRecords(2, 3);
//remove record 11
GSBase.RemoveRecords(11, 11);

// save using the current file format and file format options
GSBase.Save();
GSBase.Close();

Adding, removing and renaming tables and folders

GSBase.OpenDatabase("e:\\test_dbase.gsb", "");

//notes:
//insert a new folder "folder1\" in the root folder at the bottom;
//if "folder1" already exists at that level, the uniqueName will contain a unique
//name folder1(1)...folder1(n) that GS-Base will create and use instead;
var uniqueName = GSBase.InsertDatabaseItem("", 1, "folder1\\");

//insert folder2 at the top
GSBase.InsertDatabaseItem("", 0, "folder2\\");

GSBase.InsertDatabaseItem("\\folder1\\", 1, "folder1\\");
GSBase.InsertDatabaseItem("folder1", 1, "folder1\\");

//rename the "text_abc(2)" table in the "\folder2" folder to "test_abc_b";
GSBase.RenameDatabaseItem("\\folder2\\test_abc(2)", "test_abc_b");
//delete the entire "folder2" folder
GSBase.DeleteDatabaseItem("\\folder2\\");
		
//delete the "\folder1\test_abc_b" table
GSBase.DeleteDatabaseItem("\\folder1\\test_abc_b");

GSBase.Save();
GSBase.Close();

Importing tables from GS-Base databases, text files and Excel workbooks

//import the "product" table from the sample.zip database and insert it in the root folder
GSBase.ImportTable("e:\\sample.zip", "products", "", "\\");

//import to the "folder1" subfolder
GSBase.ImportTable("c:\\sample2.zip", "products2", "pass4563word!", "folder1");

//import the first table from file_a.xlsx and insert it in the root folder
//first row in file_a.xlsx contains field names
GSBase.ImportExcelTable("e:\\excel_data\\file_a.xlsx", "", 1, "");
//import the first table from file_c.xlsx and insert it in the root folder
//first row in file_a.xlsx doesn't contain field names
GSBase.ImportExcelTable("e:\\excel_data\\file_c.xlsx", "", 0, "");
//import the first table from file_a.xlsx and insert it in the root folder
//first row in file_a.xlsx doesn't contain field names
GSBase.ImportExcelTable("e:\\excel_data\\file_i.xlsx", "", 0, "");

//create text file parameters
var textParams = GSBase.CreateTextParams();
//use "|" as the field separator ("," is the default one)
textParams.separator = "|";

//import a new table from the "text_abc.txt" text file and place it in the "folder2" folder
GSBase.ImportTextTable("c:\\test_abc.txt", "", textParams, "\\folder2\\");
//import it again (the name of the imported table will be modified to "text_abc(1)")
GSBase.ImportTextTable("c:\\test_abc.txt", "", textParams, "\\folder2\\");
//import it again (the name of the imported table will be modified to "text_abc(2)")
GSBase.ImportTextTable("c:\\test_abc.txt", "some_name", textParams, "\\folder2");

GSBase.Save();
GSBase.Close();

Merging records from all text and Excel files in a folder

GSBase.OpenDatabase("e:\\test_dbase.gsb", "");

var mergeParams = GSBase.CreateMergeParams();
// merge record e.g. from report2000.txt, report2001.txt, ..., report2023.txt
mergeParams.path = "e:\\gsb19_test\\report20??.txt";
// don't perform field names matching, add fields "as is"
// note: field types must match, text fields can't be copied to numeric fields
mergeParams.matchFieldNames = false;

var textParams = GSBase.CreateTextParams();
textParams.separator = "|";
textParams.encoding = "windows";

GSBase.MergeRecordsFromTextFile(mergeParams, textParams);

// if there are calculated fields, update all records using 4 processor cores
GSBase.UpdateTable(4);

// you can also only perform more complex merging using the "mergeType" property:
// mergeParams.mergeType=0 - merge records unconditionally (default value)
// mergeParams.mergeType=1 - merge records only with new values of the mergeParams.slaveIndex field from merged files
// mergeParams.mergeType=2 - update records in the main table where the specified fields in the main/master table and in the merge tables are the same, mergeParams.masterIndex = mergeParams.slaveIndex
// mergeParams.mergeType=3 - delete records from the main table where the specified fields in the main/master table and in the merge tables are the same, mergeParams.masterIndex = mergeParams.slaveIndex

// For Excel and other file format use:
// MergeRecords(mergeParams)
// MergeRecordsFromTextFile(mergeParams, textParams)
// MergeRecordsFromExcelFile(mergeParams, namesInFirstRow)	
// MergeRecordsFromXBaseFile(mergeParams, xBaseParams)	
// MergeRecordsFromMySQLFile(mergeParams) 

Record merging functions

GSBase.Save();
GSBase.Close();

Formatting fields

GSBase.OpenDatabase("e:\\test_dbase.gsb", 0);

//select the "table1" table in the root folder
GSBase.SetActiveTable("\\table1");

//create formatting settings
var format = GSBase.CreateFormatParams();

//set the currency format: variable/automatic number of decimals and the exponent value
//parameters:
//1. decimals: 0 - 14 | "auto"
//2. currency position: "$1.1" | "$ 1.1" | "1.1$" | "1.1 $"
//3. currency symbol: "$", "GBP" etc.
//4. true - use curly braces for negative values
//5. true - use red color for negative values
//
format.SetCurrencyFormat("2", "$1.1", "gbp", true, true);

// other style examples:
//
//set the scientific style: 5 decimal digits and the fixed "07" exponent
//
//----- format.SetScientificFormat("5", "07");
//
//set the scientific style: variable/automatic number of decimals and the exponent value
//
//----- format.SetScientificFormat("auto", "auto");
//
//
//set the accounting style
//parameters:
//1. decimals: 0 - 14 | "auto"
//2. currency symbol: "$", "GBP" etc.
//
//----- format.SetAccountingFormat("2", "gbp");
//
//
//set the fractional format
//parameters:
//1. if the 2nd parameter is "false", (1) is a denominator value 2, 3, ..., n;
//   if the 2nd parameter is "true", (1) is a fixed number of denominator digits 1...14
//2. ...
//
//----- format.SetFractionFormat(2, true);
//
//
//set the general number format
//parameters:
//1. decimals: 0 - 14 | "auto"
//2. leading zeroes: 0 - 14
//3. true - use curly braces for negative values
//4. true - use red color for negative values
//5. true - use the thousand separator
//
//----- format.SetGeneralNumberFormat("auto", 5, false, false, true);
//

GSBase.SetFieldFormat(3, format);

//set the date format
//parameters:
//1. a date pattern: same as in the "Format > Style" window
//2. 1 - always switch to the current Windows system day/month order ("m/d..." | "d/m...")
format.SetDateFormat("m/d/yyyy", 1);
GSBase.SetFieldFormat(1, format);

//clear the previously set information
format.Reset();

format.fontSize = 14;
format.boldFont = true;
GSBase.SetFieldFormat(3, format);

GSBase.Save();
GSBase.Close();

Setting column/field widths and row heights

GSBase.OpenDatabase("e:\\test_dbase.gsb", 0);

if (GSBase.GetActiveTable() != "table1")
	GSBase.SetActiveTable("table1");

//get the 1st column/field width in screen pixels
var width = GSBase.GetColumnWidth(1);

//set a new width
width += 50;
GSBase.SetColumnWidth(1, width);

//fit the 3rd column/field width to the data in that column/field
GSBase.FitColumnWidth(3, 3);

//get the 9th row/record height in screen pixels: typically it should be 25px
var height = GSBase.GetRowHeight(9);

//set a new height
height += 20;
GSBase.SetRowHeight(9, height);

//check the "auto-height" state of the 9th row
var autoHeight = GSBase.GetAutoRowHeight(9);

//restore on the "auto-height" state for the 9th row
GSBase.SetAutoRowHeight(9, 9, true);

//should be true now
autoHeight = GSBase.GetAutoRowHeight(9);

//it should be 25px again
height = GSBase.GetRowHeight(9);

GSBase.Save();
GSBase.Close();

Browsing the folders/tables tree

GSBase.OpenDatabase("e:\\test_dbase.gsb", "");

//1st method

//get the total number of tables and folders in the main/root folder (including nested folders)
var counter = GSBase.GetDatabaseItemCount();

//iterate through all table/folders
for (i = 1; i <= counter; ++i)
{
	var path = GSBase.GetDatabaseItem(i);
	if (path.length && path.charAt(path.length - 1) == '\\')
	{
		//folder
		var ret = GSBase.MessageBox("folder - " + path, "ok", 1, "information");
	}
	else
	{
		//table
		var ret = GSBase.MessageBox("table - " + path, "ok", 1, "information");
	}

	var ret = GSBase.MessageBox(path, "ok", 1, "information");
}

//2nd method

//get the first "child" element in the specified folder, e.g. the main/"root" folder
//var path = GSBase.GetFirstDatabaseItem("\\folder2(1)\\");
var path = GSBase.GetFirstDatabaseItem("\\");

//iterate through direct "child" elements of the specified folder (not expanding nested folders)
while (path.length)
{
	if (path.charAt(path.length - 1) == '\\')
	{
		//folder
		var ret = GSBase.MessageBox("folder - " + path, "ok", 1, "information");
	}
	else
	{
		//table
		var ret = GSBase.MessageBox("table - " + path, "ok", 1, "information");
	}
	path = GSBase.GetNextDatabaseItem(path);
}

GSBase.Close();

Searching and sorting

GSBase.OpenDatabase("e:\\test_dbase.gsb", "");

//select the "table1" table in the root folder
GSBase.SetActiveTable("\\products");
		
var field = GSBase.CreateFieldParams();

//find the "ProductName" and "UnitPrice" fields;
//filter "ProductName" and sort "UnitPrice"

//reset the previous sorting indices - resetting should be used before calling "put_sortIndex()"
GSBase.ResetSorting();

var iname = 0;
var iprice = 0;

for (i = 1; i <= GSBase.GetFieldCount() && (!iname || !iprice); ++i)
{
	GSBase.GetField(i, field);
	if (!iname && field.name == "ProductName")
	{
		//set the "\Ai" RegEx filter for "ProductName" (=search for names starting with "I");
		//searching is performed automatically if a call to the "SetField" changes the "filter" value;
		//note: in this version the filter type is always "RegEx"
		field.filter = "\\Ai";

		GSBase.SetField(iname = i, field);
	}
	if (!iprice && field.name == "UnitPrice")
	{
		//set the 1 as the sorting index for "UnitPrice";
		//sorting is performed automatically after a call to "SetField" if the "sorting" index is modified;
		//to create a compound sorting index, use subsequent numbers (2, 3...) for further fields;
		//note: using an index not in that strictly incremented manner causes an error;
				
		field.sortIndex = 1;

		// 'A' - ascending order, 'D' - descending order
		field.sortOrder = 'A';

		GSBase.SetField(iprice = i, field);
	}
}

GSBase.Save();
GSBase.Close();

Predefined searching

GSBase.OpenDatabase("e:\\test_dbase.gsb", "");

//select the "table1" table in the root folder
GSBase.SetActiveTable("\\products");

//find duplicates in the 5th field
GSBase.FindDuplicates(5, 5, 1, false, false);

//check the results
var counter1 = GSBase.GetRecordTotalCount();
var counter2 = GSBase.GetRecordSetCount();

//check the results
counter1 = GSBase.GetRecordTotalCount();
counter2 = GSBase.GetRecordSetCount();

//find records with the flag "1"
GSBase.FindFlagged(1);

// ...

//find the records not included in the current record set
GSBase.FindComplement();

// ...

//clear all filters and display all records
GSBase.FindAll();

// ...

GSBase.Close();

File password protection

//set a password
GSBase.OpenDatabase("e:\\test_dbase.gsb", "");

GSBase.SetFilePassword(true, "blowfish", "", "rocc4545");

GSBase.Save();
GSBase.Close();

//open a password-protected database
GSBase.OpenDatabase("e:\\test_dbase.gsb", "rocc4545");
//...
//GSBase.Save();
GSBase.Close();

//remove password protection
GSBase.OpenDatabase("e:\\test_dbase.gsb", "rocc4545");

GSBase.SetFilePassword(false, "blowfish", "rocc4545", "");

GSBase.Save();
GSBase.Close();

Enabling sharing databases and text, Excel and other files

var fpath = GSBase.ReleaseFile()
GSBase.MessageBox("File closed: " + fpath, "ok", 1, "information");

//You can keep on viewing/working with this file in GS-Base as usual (as it's in RAM).
//It can be edited in other programs.


//At any moment you can execute the following script, which will check for updates:

if (GSBase.AttachFile(fpath) == 1)
{
    // AttachFile returned 1, so the file was modified
    // after ReleaseFile(), you can ask whether reload
    // it now or later
    GSBase.Reload();
}
else
{
    // no changes detected, so leave the file accessible
    // for any other programs.
    GSBase.ReleaseFile();
}

Opening and saving text files

// 1. Exporting the current record set to a text file
// -----------------------------------------------

GSBase.OpenDatabase("e:\\test_dbase.gsb", "");

//select the "table1" table in the root folder
GSBase.SetActiveTable("\\products");

//create text file parameters
var textParams = GSBase.CreateTextParams();

//use ";" as the field separator; the default value is ","
textParams.separator = ";";

//use of separators can be turned off; the default value is true
//textParams.useSeparator = false;

//use "'" as the quoting symbol; the default value is """
textParams.quotingSymbol = "'";

//use of quoting symbols can be turned off; the default value is true
//textParams.useQuoting = false;

//save field names in the first row; the default value is true
textParams.fieldNames = true;

//change the text encoding: "utf8" | "windows" | "dos"; the default value is "utf8"
textParams.encoding = "utf8";

//export the current table to a text file;
//"dbase" remains the originally opened database and can edited further as usual
GSBase.SaveRecordSetAsText("e:\\test_b.txt", textParams);

	
// 2. Opening and saving a text file
// ---------------------------------

//re-use the above "txt" settings and add new ones

//if a column contains textual representations of numbers, try converting them to a number field
textParams.parseNumbers = true;

//if a column contains textual representations of dates in various formats, convert these strings
//to the generic "DT" W3 text representation of dates in GS-Base (please see the "data types" help topic for details).
textParams.parseDates = true;

GSBase.OpenTextFile("e:\\test_b.txt", textParams);

var fcounter = GSBase.GetFieldCount();

var field = GSBase.CreateFieldParams();

GSBase.GetField(1, field);
//
// ...perform any editing, field changes etc.
//

//save the edited text file
GSBase.Save();
GSBase.Close();

// 3. Opening a text file and saving it as a database
// --------------------------------------------------

//re-use the above "txt"
textParams.parseNumbers = false;

GSBase.OpenTextFile("e:\\test_b.txt", txt);

//SaveDatabaseAs changes "textFile" to a database with the path given below; the text file is closed
GSBase.SaveDatabaseAs("e:\\sample_b.gsb");

//
// ...perform any editing, field actions etc. with "e:\\sample_b.gsb"
//

GSBase.Close();

Error handling

GSBase.OpenDatabase("e:\\test_dbase.gsb", "");

//
// ...
//

// if a COM funcion returns an error other than E_OUTOFMEMORY,
// additional error information can obtained via the "lastError" property;
var code = GSBase.lastError;

// 21	// Out of memory while creating/editing worksheet data
// 22	// A database must contain at least one table
// 37	// Invalid password.
// 53	// Not allowed field type change - e.g. conversion form "Files/Images" to "Number"
// 61	// Can't open the specified file
// 62	// Can't open or create the specified file
// 63	// Error while closing the specified file
// 64	// Error while repositioning a file pointer
// 65	// Error while reading from a file
// 66	// Error while writing to a file
// 67	// Error while deleting a file
// 68	// Error while checking the file size/info
// 69	// Error while allocating a file read/write buffer
// 70	// Can't open the specified file. File in use.
// 71	// Error while decrypting a file.
// 72	// Error while encrypting a file.
// 73	// Error while reading binary fields."
// 79	// Can't find the manifest file or its content is incorrect
// 80	// The file format requires a newer GS-Base version.
// 119	// Too many zip streams in a standard zip (zip32) file
// 120	// Inconsistent zip stream state
// 121	// Corrupted zip stream data
// 122	// Out of memory while processing a zip stream
// 123	// Unexpected end of zip stream
// 125	// Unknown zlib error
// 131	// Some data in the file can't be converted to numeric field values.

GSBase.Close();

File/database opening functions

params = GSBase.CreateTextParams() - settings corresponding to the "Open Text File" dialog box options

params = GSBase.CreateXBaseParams() - settings corresponding to the "Open xBase File" dialog box options

Saving recordsets

Note: Unlike "SaveDatabaseAs" methods, recordset saving methods do not change the current database path or state.

Saving databases

Saving databases as new files

Note: The above methods change the current database path, saving a new copy of a given database.

Importing tables databases, text and Excel files

Performing JOIN operations for tables in the same database file

Merging/adding records from other files

params = GSBase.CreateMergeParams() - settings corresponding to the record merging options

Switching the active table

Record counters

Searching

Note: regular field filter are set using the SetField() function.

Adding, modifying and removing fields

params = GSBase.CreateFieldParams() - settings corresponding to the record field definition

Browsing the database folder and tables tree structure

Editing records

NOTE: InsertText() and InsertNumber() do exactly what plain entering data does which means they set up Undo information and refreshes the screen which makes them slow. If you need to fill millions of fields instantly, use the Insert(...)Series() functions family instead.

Note: all record numbers are relative to the current, filtered and sorted (or not) record set. They are equal to physical cardinal record numbers in a table only if they are not active searches or sort keys.

Inserting series

Note: all record numbers are relative to the current, filtered and sorted (or not) record set. They are equal to physical cardinal record numbers in a table only if they are not active searches or sort keys.

Changing column widths and row heights

Note: all record numbers are relative to the current, filtered and sorted (or not) record set. They are equal to physical cardinal record numbers in a table only if they are not active searches or sort keys.

Formatting fields

params = GSBase.CreateFormatParams() - format/style settings

Setting record flags

Setting database passwords for GS-Base *.gsb/*.zip files

Note: The industry-level encryption include the entire database contents and all fields (textual, numeric, LongText, Code, Images/Objects). Information included in JScript and VBscript scripts stored either as GS-Base global settings or scripts added to files using the "File > Database Scripts" command is not encrypted.

File information and access

Updating all calculation formulas

Input/output methods

Window methods

Obtaining the last error details

Related Topics

Interfaces