Rapid
 Interactive
  Programming
   Environment

Command Lines

 SQLite Utility can handle input in text form.

GUI user input interfaces that accept text are:

  • Utility or SQL command interface
  • DB command interface
  • File interface

The Where interface also accepts text input, but it is strictly limited and must obey the SQL rules for a WHERE clause!.

However, the other user input interfaces were designed to accept various type of input text:

  • SQL: Text that represents a SQL command and will be handled by the embedded SQLite database.
  • IIL (Internal Interpretive Language): Text that uses a simple / interpreter that follows some of the syntax rules used by VBA (Visual Basic for Applications)

It is not possible to mix SQL and IIL commands on a single command line. This may seem to be a limitation, but there is a way to allow the IIL to modify a SQL statement.


SQL

There is just no way that I can provide an explanation of SQLite and its commands.

Please, please, please: refer to other sources for such information.

But the IIL does have some commands that are related to SQL activites e.g. testing if “xyz” is the name of a table in the database, of returning the number of rows returned by the last SQL Select statement.

For information about such commands please refer to: SQL


IIL (Internal Interpretive Language)

Motivation

    In order to understand the IIL it is helpful to understand the motivation that I had to create the IIL.

Browse

    Initially the SQLite Utility was created as a SQL database browser.

    So I did not really need an internal interpretive language.

CVS - extract from Excel:

    But then my work involved me in a lot of Excel analysis. I decided that some of the analysis would be easier/quicker if I could get the data into an SQL database. But I did not have a SQL capability on my company’s laptop. So I decided to enhance my SQL browser with the ability to import a csv (comma separated variable) file.

    I would load the Excel file to Excel, and then export the required worksheet as a csv file. Then I could load the csv file to the enhanced SQL browser. Since the SQL browser needed no installation, I could run the SQL browser on my company’s laptop without administrator rights.

CVS - return to Excel:

    This really did help! But then I started loading multiple csv files to the enhanced browser and started to modify the data in the browser. At that point it stopped being a SQL browser and was renamed SQLite Utility.

    Now I was faced with a different issue. My colleagues did not have SQL either, so to share my analysis I needed to export the SQL tables as csv files, which I could then load in to Excel, and save as an Excel file(s) which I could give to my colleagues.

    See - CSV - commands to import/export data in the comma separated variable format.

HTML- Flexible display

    On some occasions I shared the analysis in a web based video conference, so I was showing the SQLite Utility GUI. At other times I only wanted to extract a screenshot of the results of a SQL Select, and say embedded it in a e-mail.

    I soon realised that the standard Windows based listview that I used for holding the results of an SQL Select was not always convenient.

    So I added the option to display the results of an SQL Select as a HTML table. I also included the ability to increase/decrease the HTML text font size. I added a few gimmics such as allowing the HTML table to be in its own window. This meant that I could have the output window on the “beamer” whilst the SQLite Utility GUI window was displayed on a second monitor/screen, so the audience could focus on the results without being distracted by the application.

    See - HTML - commands to export the results of an SQL SELECT statement to a HTML file, and to insert HTML commands / text into that HTML file.

“Go faster” tools

    I had by this point got fed-up of having to retype all of the various commands to achieve an analysis, so I had created a batch mode input i.e. I could place all of the commands in to a text file and simply tell the SQLite Utility tool to take its input from the text file.

    I also added in some commands that were effectively extensions to the SQLite SQL language. For example SQLite does not have a command to rename a column in a table. So I created commands in the IIL that, using the SQLite database, could: rename a column, remove a column, change the type of a column,.... (See the SQL section for details).

    I also realised that I needed the ability to do conditional command execution e.g. execute the following lines if a certain condition is true. So I extended the IIL to support a simple IF / ELSE /THEN construct.

    Since then, the IIL has been enhanced to support: functions, subroutines, WHILE/WEND, BEGIN/LOOP, global variables, local variables, optional arguments,...

    See - Conditionals - commands to control the forward (IF/ELSE/THEN ) and backwards (WHILE/WEND, BEGIN/LOOP) execution of commands.
    See - Macros - commands to provide functions and subroutines.

Limitations (many)

    The IIL has a syntax that is broadly similar to the syntax of Microsoft’s Visual Basic. 

    But IIL is not a direct substitute for Microsoft’s Visual Basic or for Microsoft’s Visual Basic for Applications.

Automation

    Last summer (2015) I was getting fed-up of using csv files as the interface to Excel. Ideally I wanted to import/export directly to Excel files, but I think that trying to read/write XLS, XLSM, XLSB files is too difficult (or atleast I cannot motivate myself to do it), and to be honest I do not fancy trying to handle files in Open Document Format.

    So I decided to take a different approach!

    Fundamentally, Excel allows a third party application to use OLE techniques to automate Excel.

    So, I extended the IIL to include interfaces to the automation API interfaces such as: GetIDsOfNames, Invoke. The user does not “see” these interfaces, but it enables the IIL to accept text that identifies objects and methods that are made available by automation compatible applications - such as Excel.

     

    The OLE Automation interface is not limited to Excel, it can be used with any Windows application that supports OLE Automation.

    See Automation - commands to control an 3rd party application that conforms to OLE automation e.g. Microsoft Excel / Powerpoint / Word...


    I have created a small set of commands to support the exchange of a worksheet/table between a running Excel application and SQLite Utility . These commands are wrappers around the OLE Automation API.

    See Excel - commands to move “tables” between SQLite Utility and a running Excel application.


[Home] [FAQ] [GUI Fields] [Command Lines] [Conditionals] [Excel] [HTML] [CSV] [SQL] [Macros] [Automation]