SQL Offline

Command Line

The command line supports Offline JavaScript and Basic (OLB, OLJ), which is also used in Formulas and Macros.

You can display the Command window in the Formula tab. You can also specify whether the Command window uses Basic or JavaScript.

The last line in the command window is active and you can enter a new command. Pressing Enter causes the command to run.

Typing into any line other than the last line of the command copies it to the last line.

RECORDING

If you enable Macro recording, Offline script is added to the command line as you work. You can enable recording in the Formula tab.

Commands are added to the command line as you work. You can use the command line to edit and replay the commands.

When you are recording, commands can produce multiple lines. For example, if you change the second (2) column name to B_renamed in the Columns dialog box three lines are added to the command line:

To replay the recording, you run the three lines in the same order. Click on an existing command and press Enter to replay it.

The three new lines are the same as the recorded lines except that column name B_renamed is B in the replay. If you wanted to change the name of a different column, you can replace 2 with a different column number.

SQL Offline does not support Office-style macro record and playback. There is no Start Recording, Stop Recording, and Playback. The reason playback is not supported in SQL Offline is that Selection and Navigation are not recorded. Only Offline document changes are recorded. Without selection and navigation, Office-like record and playback is not possible. However the benefit is that SQL Offline recordings are smaller, portable to formulas and procedures, and you can run them easily from the command line.

PROPERTIES

A Column, Table, and the Data link contain a lot of detail. You access that detail using enumerated (integer-based) Properties.

In your recording, Properties are commented when used. For example, you can see in the recording shown above that col(6) is a Label.

You can display property values from the command line:

msgbox Grid.TableProperties.String
msgbox Grid.Columns(2).String
msgbox Grid.LinkProperties.String

You can also use App.History.Debug.Prompt and COPY instead of msgbox.

When writing commands, formulas, and macros that work with properties you always begin with a recording. Opening a table from a data file creates the following recording:

It is not practical (and the details are not documented) to write the commands shown above from scratch. However if you wanted to replay with a different table or data file, it is easy to identify the file and table name.

When writing macros it is recommended that begin with a recording.

SHORTHAND

Command shorthand makes it convenient to work in the command line. You can right-click in the command line to display a context menu.

Shorthand is available in the command line to help reduce typing. For example, Insert Filename... enters a filename using a file dialog. The Insert Current Cell shorthand enters the currently selected cell.

Shorthand commands are expand when you press Enter. The command text is expanded, and string parameters are quoted. For example:

ROWS grid.cell("COMM", nrow)=0
ROWS grid.Cell("SAL", nrow)=grid.Cell("SAL", nrow)+100

Sets all commissions (COMM) to zero, and adds one hundred to all salaries (SAL).

If ? is provided it is replaced by the results of a filename dialog selection. For example: USE ? browses for a data file and opens a table.

If you have used dBase (FoxPro) you may notice influence in shorthand like USE ?. Also, the Command line is an immediate window. You can create variables and use them for the session.

Shorthand command Expanded Example

? App.StatusBar = ? "hello, world"

CLOSE App.ActiveDocument.Close CLOSE

COPY App.Utility.Clipboard = String COPY grid.linkproperties.string

COLS For nCol =1 to Grid.NumCols: String: Next COLS msgbox grid.columns.item(ncol).string

CD App.DefaultFilePath = String CD c:\temp

DOCS For Each doc In App.Documents: String: Next DOCS app.history.debug.prompt doc

NEW App.Documents.New NEW

OPEN App.Documents.Open String OPEN categories

PASTE Var = App.Utility.Clipboard PASTE app.statusbar

ROWS For nRow =1 to Grid.NumRows: String: Next ROWS s=s & grid.cell(1,nrow)

RUN App.Documents.Open String, "Basic", True, True RUN macro

SELECT App.Documents(String).Activate SELECT invoices

SAVE App.ActiveDocument.Save SAVE

SAVEAS App.ActiveDocument.SaveAs String SAVEAS temp

USE App.Documents.Use String USE ?

QUIT App.Quit QUIT

Shorthand Commands.

Shorthand command	Expanded	Example
?	App.StatusBar =	? "hello, world"
CLOSE	App.ActiveDocument.Close	CLOSE
COPY	App.Utility.Clipboard = String	COPY grid.linkproperties.string
COLS	For nCol =1 to Grid.NumCols: String: Next	COLS msgbox grid.columns.item(ncol).string
CD	App.DefaultFilePath = String	CD c:\temp
DOCS	For Each doc In App.Documents: String: Next	DOCS app.history.debug.prompt doc
NEW	App.Documents.New	NEW
OPEN	App.Documents.Open String	OPEN categories
PASTE	Var = App.Utility.Clipboard	PASTE app.statusbar
ROWS	For nRow =1 to Grid.NumRows: String: Next	ROWS s=s & grid.cell(1,nrow)
RUN	App.Documents.Open String, "Basic", True, True	RUN macro
SELECT	App.Documents(String).Activate	SELECT invoices
SAVE	App.ActiveDocument.Save	SAVE
SAVEAS	App.ActiveDocument.SaveAs String	SAVEAS temp
USE	App.Documents.Use String	USE ?
QUIT	App.Quit	QUIT

OUTPUT LOG

The output window supports three separate log windows (Results, Log, Debug).

You can write to the Log and Debug windows. For example, if you enter app.History.Log.Prompt ("hello") in the command line hello is added to the log. You can copy from the log to the clipboard.