DBPROMPT= LIBNAME Statement Option

Specifies whether a window prompts the user to enter connection information before connecting to the data source in interactive mode.

Valid in:	LIBNAME statement
Default:	NO
Supports:	All

Syntax

Details

Examples

Example 1: Prompt for Connection Information

Example 2: Defer Prompt for Connection Information

Syntax

DBPROMPT=YES | NO

Syntax Description

YES: specifies that a window interactively prompts you for the data source connection options the first time the libref is used.

NO: specifies that you are not prompted for the data source connection options. You must specify either a DSN definition or a fully specified connection string using CONNECT_STRING=. NO is the default.

Details

If you specify DBPROMPT=YES, it is not necessary to provide connection options with the LIBNAME statement. If you specify connection options with the LIBNAME statement and you specify DBPROMPT=YES, the connection option values are displayed in the window (except for the password value, which appears as a series of asterisks). All of these values can be overridden interactively.

The DBPROMPT= option interacts with the DEFER= option to determine when the prompt window appears. If DEFER=NO, the DBPROMPT window appears when the LIBNAME statement is executed. If DEFER=YES, the DBPROMPT window appears the first time a table is opened. The DEFER= option normally defaults to NO but defaults to YES if DBPROMPT=YES. You can override this default by explicitly setting DEFER=NO.

The DBPROMPT window usually opens only once for each time that the LIBNAME statement is specified. It might open multiple times if DEFER=YES and the connection fails when the engine tries to open a table. In these cases, the DBPROMPT window appears until a successful connection occurs or you click Cancel.

The maximum password length for most of the data sources is 32 characters.

Examples

Example 1: Prompt for Connection Information

In the following example, values provided in the LIBNAME statement are pulled into the DBPROMPT window. The values user1 and basedsn appear in the DBPROMPT window and can be edited and confirmed by the user. The password value appears in the DBPROMPT window as a series of asterisks; it can also be edited by the user.

libname seclib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1 
   dsn=basedsn dbprompt=yes defer=no;

Example 2: Defer Prompt for Connection Information

In the following example, the DBPROMPT window does not open when the LIBNAME statement is submitted because DEFER=YES. The DBPROMPT window appears when the PRINT procedure is processed. The values user1 and basedsn appear in the DBPROMPT window and can be edited and confirmed by the user. The password value appears in the DBPROMPT window as a series of asterisks; it can also be edited by the user. Upon confirmation, the connection is made, and the table is opened.

libname seclib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1 
   dsn=basedsn dbprompt=yes defer=yes;

proc print data=lib1.staff; run;