Previous Topic

Next Topic

Book Contents

Book Index

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.

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.