System Commands or SYSCMD

There is a default configuration present in the library which requires no configuration but to further configure the library to act in certian manner there are library configuration commands called SYSCMD or System Commands, which can be used to either get current configuration or set new configuration for next statements. SYSCMD Commands are case insensitive

These commands are used to get columns of table to index information etc.

Culture Info To view currently installed available cultures on machine you can use View_Available_Cultureinfo command, it uses .net System.Globalization namespace to query the machine.

SYSCMD View_Available_Cultureinfo;

Current Culture Info To view culture currently in use thread_cultureinfo can be used, by default "en-US" is used.

SYSCMD Thread_CultureInfo;
                

SYSCMD Thread_CultureInfo = 'en-GB';

SYSCMD Thread_CultureInfo = 'fr-FR';
                

Databases To view all the databases attached to particular connection use database_list SYSCMD statement. It returns three (3) columns with information about each database, Sequence Number starts with zero (0), for in-memory database file column will be empty.

SYSCMD Database_List;
                

Collating Sequence it uses a collating sequence or collating function to understand which string is greater or whether the two strings are equal. Since it is written in .Net it also uses .Net built-in String Comparision rules. By default SQLDatabase.Net is case insensitive and use NOCASE as collating sequence The following are supported.

SYSCMD Collation_List;
                

To obtain the current collating sequence of the database connection use this command, a single column is returned. To change the active collation pass the new collation name, e.g. Ordinal which is case sensitive, all where clause and other comparision will be case sensitive, you can also define collation during table creation for each column or at query level using COLLATE keyword. Note: It only applies to binary comparison operators (=, <, >, <=, >=, !=, IS, and IS NOT).

SYSCMD Collating_Sequence;

SYSCMD Collating_Sequence = 'Ordinal';

WHERE COLA COLLATE BINARY = 'A' ;
                

Table Information Table information can be obtained using table_info command, it returns 6 columns with information about the structure or columns in that table, it requires table name. The following statement returns information about sys_objects table.

SYSCMD table_info('sys_objects');
                

Index Information Information about particular index can be obtained using index_info command, it returns 3 columns with information about the index and requires index name as parameter.

SYSCMD index_info('Index Name>');
                

SYSCMD index_list('Table Name');
                

Foreign Keys Information about foreign keys can be obtained using SYSCMD, table name as parameter is required. Total eight (8) columns are returned.

SYSCMD Foreign_Key_List('Table Name') ;
                

Database Encoding Current Encoding can be obtained through encoding system command, as of now only UTF-8 is supported.

SYSCMD encoding;
                

Database Encryption Security is implemented through encryption as data is stored in plain text format inside database file, the entire file can be encrypted using AES 256. The secret password or Key is provided through SYSCMD statement and must be provided after opening the connection or in connection string. They key is used to generate hash and encrypt all data, to maintain performance data is only encrypted or descrypted during read write operation. Key can also be changed using ReKey command.

SYSCMD Key = 'Secret Password';
SYSCMD ReKey = 'New Secret Password';