Revision Date: March 14, 2000
Working with DSQL Statements
This tab is used for all DSQL statements that are not for fetching multiple records.
DDL, DML, EXECUTE PROCEDURE, SET GENERATOR, etc. can all be processed in this tab.
Compiling and Executing DSQL Statements
The IB_StatementBar below allows statements to be compiled and executed:
The yellow "On" light bulb is used to Prepare or compile the statement that was entered into the SQL tab.
The white "Off" light bulb is to Unprepare the statement.
The green "Run" button executes the statement.
Notice the IB_TransactionBar for convenience if you want to CommitRetain or Rollback any statements you have executed here.
NOTE: The option to compile without executing is very handy when you just want to check if the stored procedure you are writing or altering will compile and you do not want to actually execute it and risk crashing the server.
Keeping a History of Compiled Statements
The "Previous", "Next" and "New" buttons give access to past statements Prepared (compiled) and clear to enter another.
If you hit the Previous button and make any modifications to the SQL statement you are changing that statement's history. If you just want to save keystrokes and keep the original statement in the history, copy it to the clipboard and then hit the New button and paste the statement in and modify it there.
SQL, Params & Fields
This is where the statement you want to Prepare and Execute is entered. It is possible to supply input parameters on statements that allow for such. Please use the ":" (Colon) or "?" (Question mark) character to denote an input parameter. If you use a colon then make sure that the Params? checkbox is checked so that IBO will know to look for input parameters. See the example provided below.
The Params tab is used to supply parameter data prior to executing and the Fields tab is used to display the results after executing.
For example, the singleton select SQL statement:
SELECT COUNT (*) FROM CUSTOMER WHERE CUSTNO > ?CUSTNO
could be entered. Then Prepare (compile) it and go to the Params tab to supply the input value for the CUSTNO parameter. Once entered, click on the Execute button and go to the Fields tab to see the result of the statement.
Then go back to the Params tab, put in another value, and press Execute again. There will then be new output values displayed in the Fields tab.
If the statement executed has a valid "Rows Affected" number this will be reported on the status line. For example, if you execute a DELETE … WHERE … you will see how many actual rows were deleted. This also works for Update DML statements.
NOTE: You should use a "?" to denote an input parameter in a DSQL statement. But, the ":" character can also be used when in a TIB_DataSet (TIB_Cursor or TIB_Query) component. This is for compatibility with the BDE driven TQuery.
