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:

Parmno. The parameter number
Parmname. This is an optional parameter which you can use to identify the parameter. It is ignored by AQT.
Value. The value of a parameter. Values do not need to be enclosed in quotes, even if they are string values. However if the string value contains a comma you should enclose it in double-quotes (the double-quote is used by AQT to delimit a batch parameter value). Value must be specified for In and Inout parameters.
The value can be set to the result of a previous query, to an environment variable or to the current date/time. Full details of how to specify these are given in Setparm.
Ptype. The parameter type. This is only used when running Stored Procedures. Ptype can take values In, Out and Inout. The default is In.
Dtype. The data type of the parameter. This is only needed when running Stored Procedures, and only for some databases.

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.

if the data type contains a comma, you should enclose the value in double-quotes
the length should always be included, even for data types that do not normally have a length specified. Example: INTEGER(4).
when you use Copy to SQL Window, you can sometimes see a different datatype being specified. This happens if the data type is one that is not registered to the ODBC interface - AQT will use a character data type instead.

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.

Advanced Query Tool

https://www.querytool.com