DATABASE: Difference between revisions
Jump to navigation
Jump to search
Gordon.dye (talk | contribs) (→DSN) |
|||
| (8 intermediate revisions by 2 users not shown) | |||
| Line 6: | Line 6: | ||
===DSN=== | ===DSN=== | ||
Syntax: | Syntax: | ||
CONFIG DATABASE <db-ref> DSN=<dsn-ref> [, USER= <department> | LOGIN_NAME | ? ] [, | CONFIG DATABASE <db-ref> DSN=<dsn-ref> [, USER= <department> | LOGIN_NAME | ? ] [, PASSWORD= <dept-password> | BR_PASSWORD | ? | PASSWORDD=<encrypted-password> ] | ||
The ? indicates prompt | The ? indicates prompt | ||
Example: | Example: | ||
EXECUTE 'CONFIG DATABASE CLS_Data DSN="MyData"' | EXECUTE 'CONFIG DATABASE CLS_Data DSN="MyData" , PASSWORDD= <encrypted-password>' | ||
''[[Encrypt$|Encrypted passwords]]'' are expressed as hexadecimal values. BR will ''[[UnHex$|Unhex]]'' the value and then ''[[Decrypt$|Decrypt]]'' it before presenting it to SQL Server. | |||
===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 24: | ||
"db-ref" is the database reference. | "db-ref" is the database reference. | ||
Using a SQL Server | "server" is the SQL Server [FQDN] or IP Address. | ||
CONFIG database db-ref connectstring="DRIVER=SQL Server;Initial Catalog=database;Persist Security Info=True;MultipleBC_TableResultSets=True; Database=database;SERVER=server | |||
"database" is the [SQL Server Database]. | |||
"username" is the [SQL Server User Name]. | |||
"password" is the [SQL Server Password]. | |||
Using a SQL Server with 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=LOGIN_NAME 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. | "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=== | ||
| Line 49: | Line 58: | ||
The following is a sample program that will use the ODBC-MANAGER to identify the proper connection string. | 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 | * Note: This will not work in Client Server because the operation is performed on the server and this process depends on the user's settings. | ||
00010 PRINT Newpage | 00010 PRINT Newpage | ||
00011 IF Env$("br_model")="CLIENT_SERVER" THEN PRINT "Warning, you cannot connect to ODBC-MANAGER in Client Server Mode" | 00011 IF Env$("br_model")="CLIENT_SERVER" THEN PRINT "Warning, you cannot connect to ODBC-MANAGER in Client Server Mode" | ||
00020 EXECUTE " | 00020 EXECUTE "Config Database Test_DB ODBC-MANAGER" ERROR CONNECT_ERROR | ||
00030 PRINT "Connection String is:" !: | 00030 PRINT "Connection String is:" !: | ||
PRINT Env$("STATUS.DATABASE.[TEST_DB].CONNECTSTRING") | PRINT Env$("STATUS.DATABASE.[TEST_DB].CONNECTSTRING") | ||
00099 STOP | 00099 STOP | ||
00100 CONNECT_ERROR: ! | 00100 CONNECT_ERROR: ! | ||
00110 PRINT "Error Connecting - Err:";Err;" Line:";Line;" Syserr:";Syserr$ | 00110 PRINT "Error Connecting - Err:";Err;" Line:";Line;" Syserr:";Syserr$ | ||
[[Category:All Parameters]] | [[Category:All Parameters]] | ||
Latest revision as of 02:29, 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>'
Encrypted passwords are expressed as hexadecimal values. BR will Unhex the value and then Decrypt it before presenting it to SQL Server.
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 with 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=LOGIN_NAME 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 because the operation is performed on the server and this process depends on the user's settings.
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$