The Execute command in the File menu enables you to execute SQL statements stored in a text file. This is useful for running scripts, such as backups. When this option is selected, the Execute File dialog box appears.
You use the Execute File dialog box to identify SQL batch files. The options for the dialog box are as follows:
Use this box to specify the character or characters that terminate an SQL statement. If Carriage Return is selected, the \r\n string is used. If Character is selected, the value in the edit box is used.
The following example shows the CARRTN.SQL batch file, which uses carriage returns to terminate statements:
CREATE TABLE SALES (SALESNUM INTEGER, CUSTOMER CHAR(10))
INSERT INTO SALES VALUES (10, 'Harry')
INSERT INTO SALES VALUES (20, 'June')
SELECT * FROM SALES
The next example file, SQLSRVR.SQL, shows how you can run a script for SQL Server that uses 'GO' to indicate the end of a block of statements:
CREATE TABLE Sales (SalesNum integer, Customer char(10))
GO
INSERT INTO Sales VALUES (10, 'Harry')
INSERT INTO Sales VALUES (20, 'June')
GO
SELECT * FROM Sales
GO
The third example file, ORACLE.SQL, shows the use of semicolons (;) to terminate SQL statements:
CREATE TABLE SALES
(SALESNUM NUMBER, CUSTOMER CHAR(10));
INSERT INTO SALES VALUES (10, 'Harry');
INSERT INTO SALES VALUES (20, 'June');
SELECT * FROM SALES;
Because the terminator is never actually sent to the server, you could take a script designed for SQL Server (with the 'GO' terminator) and run it against Oracle. Only the SQL statements would be sent. Provided the SQL grammar is compatible, it will work on either platform.
Use this box to specify the maximum statement length to be used. The statement length must be from 100 to 9999. This value is used to allocate global memory into which the file is read. If a statement is longer than this value, Admin Demo executes only the part of the statement that it has read. This is likely to cause a syntax error. The default of 1000 should be sufficient for nearly all batch files.
The execution method is not particularly sophisticated. Because of this, the following limitations should be kept in mind:
The only SQL statements that can be executed are those that the data source can process. That is, there is no special batch control mechanism in Admin Demo. Using SQL extensions such as WHILE and IF must be limited to data sources that can interpret them. For example, you might use various conditional statements when creating a stored procedure in SQL Server or Oracle. Admin Demo makes no attempt to distinguish between them.
Because only "Max Length" bytes are read from the file at a time, there should be no real limit on the overall size of the file being executed. However, the maximum statement size can be no longer than "Max Length," which is defined to be 100 to 9999.
The search algorithm simply fills the buffer to the specified maximum by reading sequentially from the file. A search for the terminator string starts at the beginning of the buffer. When found, it is replaced with a null terminator, and all carriage returns (assuming the terminator is not a carriage return) are removed before the statement is executed. This means that carriage returns embedded in data will be stripped.
An attempt is made to skip over data strings wherever possible (with the exception of carriage returns). For example, if your terminator is the semicolon (;) character, executing the following file:
SELECT ';', Customer FROM Sales;
is identical to executing the following:
SELECT ';', Customer FROM Sales
and
SELECT 'A line
of data' FROM Sales
is identical to:
SELECT 'A line of data' FROM Sales