SQL Object
SQL*XL
home
SQLXL    
  SQL  
    Statements

Description:

The SQL object deals with all the SQL statements in SQLXL. When assigning a new SQL statement or a new set of SQL statements to the SQL object it will parse the SQL statements and put them in the Statements collection. The SQL object will also populate the Parameters object with bind variables.

Properties:

Name Data Type Description
Statements As Statement The statements property contains a collection of SQL statements. When assigning new SQL to the SQL object (e.g. using the setText method) this collection will be populated
StatementCount As Long The number of statements in the Statements collection.

 

Methods:

Name Data Type Description
setText Text As String Initialises a new SQL statement or a set of SQL statements separated by semi colons from a text string. The Statements collection is rebuild and any parameters used in the SQL are available from the Parameters object.
setTextFromWorksheet SelectedCells As Range Gets the SQL from a range of cells on the spreadsheet. The cells are read from left to right then from top to bottom. After each cell an additional space is added to the statement. After building up the SQL statement the setText function is called.
OneStatement As Boolean Do all the cells in the SelectedCells range make one statement (true) or should each cell be regarded as a separate statement? (false)

 

Example:

Execute a mixture of SQL and SQL*Plus statements:

SQLXL.SQL.setText Text:="column empno format 000000; select * from emp;"
SQLXL.SQL.Statements(1).Execute
SQLXL.SQL.Statements(2).Execute

Execute a mixture of SQL and SQL*Plus from a SQL file:

SQLXL.SQL.setText Text:="start c:\myfiles\mysql.sql"

For l= 1 to SQLXL.SQL.StatementsCount
    SQLXL.SQL.Statement(l).Execute
Next l