DATABASE: Difference between revisions

From BR Wiki
Jump to navigation Jump to search
Line 14: Line 14:
===CONNECTSTRING (DSN Less)===
===CONNECTSTRING (DSN Less)===
Syntax:
Syntax:
  CONFIG DATABASE <db-ref>  CONNECTSTRING="<Driver>={Microsoft Access Driver (*.mdb)} DBQ=C:\inetpub\wwwroot\BegASP\Chapter.14\Contact.mdb"
  CONFIG DATABASE <db-ref>  CONNECTSTRING="<Driver>={Microsoft Access Driver (*.mdb)} DBQ=C:\inetpub\wwwroot\BegASP\Chapter.14\Contact.mdb" [, USER= <department> | LOGIN_NAME | ? ] [, PASSWORD= <dept-password> | BR_PASSWORD | ? | PASSWORDD=<encrypted-password> ]


'''Sample Connection Strings:'''
'''Sample Connection Strings:'''
Line 22: Line 22:


"db-ref" is the database reference.
"db-ref" is the database reference.
"server" is the SQL Server [FQDN] or IP Address
 
"database" is the [SQL Server Database]  
"server" is the SQL Server [FQDN] or IP Address.
"username" is the [SQL Server User Name]
 
"password" is the [SQL Server Password]
"database" is the [SQL Server Database].
 
"username" is the [SQL Server User Name].
 
"password" is the [SQL Server Password].


Using a SQL Server /w Windows Authentication:
Using a SQL Server /w Windows Authentication:
  CONFIG database db-ref connectstring="DRIVER=SQL Server;Initial Catalog=database;Persist Security Info=True;MultipleBC_TableResultSets=True; Database=database;SERVER=server"  Login_Name=username  Password=BR_PASSWORD
  CONFIG database db-ref connectstring="DRIVER=SQL Server;Initial Catalog=database;Persist Security Info=True;MultipleBC_TableResultSets=True; Database=database;SERVER=server"  User=username  Password=BR_PASSWORD


Notice that the connection string, placed within quotes, is separate from the '''Login_Name=''' and '''Password=''' parameters. If the additional parameters are specified, BR will augment the connection string with '''UID=''' and '''PWD=''' parameters. Do not confuse connection string valid parameters with BR augmentation parameters.
Notice that the connection string, placed within quotes, is separate from the '''User=''' and '''Password=''' parameters. If the additional parameters are specified, BR will augment the connection string with '''UID=''' and '''PWD=''' parameters. Do not confuse connection string valid parameters with BR augmentation parameters.


"db-ref" is the database reference.
"db-ref" is the database reference.


"server" is the SQL Server [FQDN] or IP Address
"server" is the SQL Server [FQDN] or IP Address.


"database" is the [SQL Server Database]  
"database" is the [SQL Server Database].


"username" is the [SQL Server User Name]
"username" is the [SQL Server User Name].


BR_PASSWORD will use the users Active Directory password to connect to the SQL Server.
BR_PASSWORD will use the users Active Directory password to connect to the SQL Server.


"SQL Server" is one of several choices for SQL Server, another choice would be SQL Server Native Client 11.0
"SQL Server" is one of several choices for SQL Server, another choice would be SQL Server Native Client 11.0.


===ODBC-MANAGER===
===ODBC-MANAGER===

Revision as of 01:25, 31 July 2023

In version 4.3 and higher CONFIG DATABASE may use one of 3 methods for connecting to SQL Sources:

  • DSN
  • CONNECTSTRING
  • ODBC-MANAGER

DSN

Syntax:

CONFIG DATABASE <db-ref>  DSN=<dsn-ref>  [, USER= <department> | LOGIN_NAME | ? ] [, PASSWORD= <dept-password> | BR_PASSWORD | ? | PASSWORDD=<encrypted-password> ]

The ? indicates prompt

Example:

EXECUTE 'CONFIG DATABASE CLS_Data DSN="MyData" , PASSWORDD= <encrypted-password>'

CONNECTSTRING (DSN Less)

Syntax:

CONFIG DATABASE <db-ref>  CONNECTSTRING="<Driver>={Microsoft Access Driver (*.mdb)} DBQ=C:\inetpub\wwwroot\BegASP\Chapter.14\Contact.mdb" [, USER= <department> | LOGIN_NAME | ? ] [, PASSWORD= <dept-password> | BR_PASSWORD | ? | PASSWORDD=<encrypted-password> ]

Sample Connection Strings:

Using a SQL Server /w SQL Login:

CONFIG database db-ref connectstring="DRIVER=SQL Server;SERVER=server;Initial Catalog=database;UID=username;PWD=password"

"db-ref" is the database reference.

"server" is the SQL Server [FQDN] or IP Address.

"database" is the [SQL Server Database].

"username" is the [SQL Server User Name].

"password" is the [SQL Server Password].

Using a SQL Server /w Windows Authentication:

CONFIG database db-ref connectstring="DRIVER=SQL Server;Initial Catalog=database;Persist Security Info=True;MultipleBC_TableResultSets=True; Database=database;SERVER=server"   User=username  Password=BR_PASSWORD

Notice that the connection string, placed within quotes, is separate from the User= and Password= parameters. If the additional parameters are specified, BR will augment the connection string with UID= and PWD= parameters. Do not confuse connection string valid parameters with BR augmentation parameters.

"db-ref" is the database reference.

"server" is the SQL Server [FQDN] or IP Address.

"database" is the [SQL Server Database].

"username" is the [SQL Server User Name].

BR_PASSWORD will use the users Active Directory password to connect to the SQL Server.

"SQL Server" is one of several choices for SQL Server, another choice would be SQL Server Native Client 11.0.

ODBC-MANAGER

Syntax:

CONFIG DATABASE <db-ref> ODBC-MANAGER

Using ODBC Manager as the parameter will allow the end user to select the desired data source.

The following is a sample program that will use the ODBC-MANAGER to identify the proper connection string.

  • Note: This will not work in Client Server
00010   PRINT Newpage
00011   IF Env$("br_model")="CLIENT_SERVER" THEN PRINT "Warning, you cannot connect to ODBC-MANAGER in Client Server Mode"
00020   EXECUTE "Config Database Test_DB ODBC-MANAGER" ERROR CONNECT_ERROR
00030   PRINT "Connection String is:" !:
       PRINT Env$("STATUS.DATABASE.[TEST_DB].CONNECTSTRING")
00099   STOP 
00100 CONNECT_ERROR: ! 
00110   PRINT "Error Connecting - Err:";Err;" Line:";Line;" Syserr:";Syserr$