Scripting - Connect function

This function is used to sign on to a database. It is mainly used when running Batch Queries – you must Connect to a database before any queries can be run. However this function can also be used when you are in “normal” (interactive) AQT – you can use this to sign onto a number of databases without going through the normal signon dialogs.

The Connect function takes the following parameters. Most of these correspond to parameters specified on the Signon window.

Parameter	Description	Notes
`Dbs`	The name of the database you wish to connect to.	This is the same as the database name you would use in the Signon window. If you have specified Conn, the Dbs parameter has a slightly different meaning. It will be used as (an arbitrary) name you give this database (you may need to refer to this database if you code a use function).
`User`	User-id to sign on with.	If not specified, you will be signed on without a user-id or password.
`Pwd`	Password to sign on with.	This password is specified in clear text. This is a security exposure - it is preferable to use the epwd parameter instead. If user is specified, and pwd is not specified, you will be signed on with a user-id only. This would be the equivalent of checking the No Password option in the Signon window.
`Epwd`	Encrypted Password.	The encrypted password. It is recommended that this is used rather than pwd. Encrypted passwords are discussed later in this topic.
`Database`	The name of the SQL Server / Sybase / Informix database to sign onto.	This is the same as option Database Name in the signon window.
`Prompt`	Specifies the level of prompting you wish for the signon	Values are: `None` (default) `Missing` (same as Prompt for Missing Info in the Signon window) `Always` (same as Prompted Signon). This parameter is ignored if you are running AQT in batch mode.
`Sheet`	Specifies the name of the Excel Worksheet to be written to	This is used for Excel only. If you code `Sheet` it is recommended that you also code `Filemode`, which specifies what to do if the Worksheet already exists. This parameter is optional – if not coded, a new Worksheet is always created (and `Filemode` is ignored).
`Filemode`		Used only for Excel, and only if `Sheet` is specified. AQT supports appending data to worksheets. Use `Filemode=append` to do this.
`ConnectAs`	Specifies whether you are signing on with sysoper or sysdba privileges.	Only used when signing onto Oracle. Requires Oracle ODBC Driver v9.2 or above.
BatchOnly		When Yes, the Connect is ignored when AQT is running in interactive mode. This is useful when you have a script which you run in both batch and interactive mode - and you don't want the Connects to run when in interactive mode.
`Conn`	Specifies a full connection string	Use the conn parameter to specify a full connection string. You may use this to connect without having to create an ODBC Datasource. They also allow you to specify a full set of connection parameters. Connection strings are different for every ODBC Driver. A reference to them is given at https://www.connectionstrings.com/ AQT has an extension to to connection strings that allow you specify passwords in an encrypted format, rather than clear text. If this parameter is specified, no other parameter is needed except Dbs.

Parameters marked * are mandatory

Examples:

--aqt connect,dbs=sample

--aqt connect,dbs=ora_sample,user=system,epwd=JkAlDkEEkGg,connectas=sysdba,batchonly=yes

--aqt connect,conn=”Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\aqtdemo.mdb”,dbs=Access

Prompting for the Userid and Password

You can can prompt the user to enter the userid and password by using the queryparm statement. An example of this is:

--aqt queryparm,parm=uid,desc=Userid,parm=pwd,desc=Password,pwd=y

--aqt connect,dbs=dbs-name,user=$uid,pwd=$pwd

`Using Encrypted Passwords`

To avoid specifying passwords in clear text, AQT allows you to use encrypted passwords.

in the --aqt connect statement, rather than specify a password with the pwd parameter, specify the encrypted password with epwd
when you use a connection string, passwords are specified with either pwd or password parameters, depending on the syntax supported by the ODBC Driver. To use an encrypted password, replace these parms with either epwd or epassword respectively. Example:
Driver={IBM DB2 ODBC DRIVER};HOSTNAME=SERV01;PORT=50000;DATABASE=SAMPLE;PROTOCOL=TCPIP;UID=scott;EPWD=^PuLvprDmYeEEzU5AwaU=KJ;
Encrypted passwords are a feature of AQT only. Connection strings containing encrypted passwords cannot be used in other product.

Encrypted passwords can be generated using the Generate Encrypted password tool.

Testing your --aqt connect statement

You can test your connect statement by running it in an SQL window. If successful, you will be connected to the database and another Database Explorer window will be opened.

Advanced Query Tool

https://www.querytool.com