Problem:

There is a need to determine what database to connect to at runtime,  to override the default set up through the ODBC DSN.

Resolution:

1. Using a EXEC SQL USE DATABASE END-EXEC statement to change database, but this cannot be a host variable but is a hardcoded name.

2. Using a connection string (with a named connection is necessary) as specified in Format 6 of Connect syntax

Format 6

>>----EXEC SQL---CONNECT USING input_connection---------->

>-----.-------------.---.---------------------.---------->

        --AS db_name-     --WITH-.----.-PROMPT-

                                  -NO-

>-----.------------------------------.------END-EXEC----><

        --RETURNING output_connection-

To create a connection string like the following and then to modify the string depending on the database to get in use:

Using either Driver={SQL Server} to Driver={SQL Native Client} depending on what driver is required.

Refer to Knowledge Base Article 15595 for details on using ODBC Connection Strings.