Previous Topic

Next Topic

Book Contents

Book Index

Scripting - Parms Function

The Parms function specifies substitutional parameters to be used in the subsequent SQL statement.

This function is used internally by AQT to load LOB values. Within the Update/Insert Row window, if you specify Load from File for a LOB, you can click on Preview SQL to see how this is used.

If you wish to run a stored procedure in batch mode, you should do this by coding a Parms statement for each procedure parameter. An example of this is given below.

The Parms function takes the following parameters:

Multiple parameter / value pairs can be specified on a Parms statement.

Examples:

Basic

--aqt parms,parmno=1,value=000001

Select * from Customer_Details WHERE cust_code=?

Two parameters

--aqt parms,parmno=1,value=000001,parmno=2,value=ELSPETH

Select * from Customer_Details WHERE cust_code=? AND name=?

Inserting a Blob

--aqt parms,parmno=1,value=<aqtfile>C:\test.jpg

Insert into BLOBTAB values('000200',’Smith’,?)

Running a Stored Procedure

--aqt parms,parmno=1,value=23.2,dtype="decimal(5,3)"

--aqt parms,parmno=2,ptype=out,dtype=character(30)

{ call DBA.GET_CUSTOMER_NAME (?,?) }

You can generate the SQL (and control statements) for running a stored procedure by clicking on the Copy to SQL Window button from the Run Procedure window.

You can also use queryparm and setparm to specify parameters which are used through the entire script. These functions can do much of what the Parms function does, however you must use Parms if you are doing a BLOB Insert or running a stored procedure.

Dtype

This is the data type of the parameter. This is discussed in more detail in Running a Stored Procedure. It is recommended that you do not code this directly yourself, but generate this from the Copy to SQL Window button of the Run Procedure window.

Setting parms to values returned by queries and stored procedures

You can set a parameter to a value returned by a query, or to an OUT parameter from a stored procedure. This is discussed in more detail in the section on setparm.