DATABASE

DATABASE

Starting in Business Rules! 4.3, the CONFIG DATABASE directive allows a BR program to connect to external SQL databases through ODBC.

This page covers BR acting as an ODBC SQL client — connecting outward to SQL Server, MySQL, SQLite, Microsoft Access, and other relational databases, executing SQL statements, and reading result rows.

BR treats a live SQL statement as an ordinary I/O channel:

1. CONFIG DATABASE names the connection.
2. OPEN binds a BR file channel to a SQL statement.
3. WRITE executes the SQL statement.
4. READ fetches rows for a SELECT.
5. CLOSE releases the statement/result set.

Important: This is different from the BR ODBC driver that exposes BR data files to Excel, Access, reporting tools, or other external consumers.

Overview

Step	Statement	Purpose
1. Configure connection	CONFIG DATABASE <db-ref> ...	Names a data source and tells BR how to connect to it.
2. Open SQL channel	OPEN #h: "DATABASE=<db-ref>", SQL sql$, OUTIN	Binds a BR channel to one SQL statement over the named connection.
3. Execute SQL	WRITE #h:	Executes the bound SQL statement.
4. Fetch rows	READ #h, USING ...	Reads result rows from a SELECT.
5. Close channel	CLOSE #h:	Releases the SQL statement/result set.

The <db-ref> is the connection name. The same name used in CONFIG DATABASE is referenced later in OPEN using "DATABASE=<db-ref>".

Example:

EXECUTE 'CONFIG DATABASE DemoConn CONNECTSTRING="DRIVER=SQL Server;SERVER=myserver;Initial Catalog=mydb;UID=myuser;PWD=mypass"'

LET C_SQL$ = "SELECT FirstName, LastName FROM dbo.Customers"

OPEN #(H := 21): "DATABASE=DemoConn", SQL C_SQL$, OUTIN
WRITE #H:

RD: READ #H, USING 'FORM POS 1,C 50,C 50': FIRST$, LAST$ EOF DONE
    PRINT TRIM$(FIRST$); " "; TRIM$(LAST$)
    GOTO RD

DONE: CLOSE #H:

CONFIG DATABASE

CONFIG DATABASE defines a named ODBC connection.

It may be placed in BRConfig.sys or issued from inside a program using EXECUTE.

Connection Methods

CONFIG DATABASE supports three connection methods:

• DSN
• CONNECTSTRING
• ODBC-MANAGER

General Syntax

CONFIG DATABASE <db-ref> { DSN=<dsn-ref>
                         | CONNECTSTRING="<odbc-connection-string>"
                         | ODBC-MANAGER }
                         [ , USER= { <department> | LOGIN_NAME | ? } ]
                         [ , { PASSWORD= { <dept-password> | BR_PASSWORD | ? }
                             | PASSWORDD=<encrypted-password> } ]

Option	Meaning
<db-ref>	Arbitrary BR database reference name. This is the name used later in OPEN.
DSN=	Uses a pre-configured Windows ODBC Data Source Name.
CONNECTSTRING=	Uses a DSN-less ODBC connection string.
ODBC-MANAGER	Displays the Windows ODBC manager so the user can select the data source.
USER=?	Prompts the operator for the user name.
PASSWORD=?	Prompts the operator for the password.
USER=LOGIN_NAME	Uses the current BR login / Active Directory user name.
PASSWORD=BR_PASSWORD	Uses the current BR login / Active Directory password.
PASSWORDD=	Supplies an encrypted password as a hexadecimal value.

DSN

Use DSN when the ODBC Data Source Name has already been configured in the Windows ODBC manager.

Syntax

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

Example

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

Encrypted passwords are expressed as hexadecimal values. BR unhexes and decrypts the value before presenting it to the SQL driver.

CONNECTSTRING / DSN-less Connections

Use CONNECTSTRING when the full ODBC connection string should be supplied directly.

This is usually the most portable option for deployed applications because it avoids requiring a pre-created DSN on each workstation or server.

Syntax

CONFIG DATABASE <db-ref> CONNECTSTRING="<odbc-connection-string>"
    [, USER=<department> | LOGIN_NAME | ?]
    [, PASSWORD=<dept-password> | BR_PASSWORD | ? | PASSWORDD=<encrypted-password>]

SQL Server with SQL Login

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

Where:

Value	Meaning
db-ref	BR database reference name.
server	SQL Server FQDN, host name, or IP address.
database	SQL Server database name.
username	SQL Server login.
password	SQL Server password.

SQL Server with Windows Authentication

CONFIG DATABASE db-ref CONNECTSTRING="DRIVER=SQL Server;Initial Catalog=database;SERVER=server" USER=LOGIN_NAME PASSWORD=BR_PASSWORD

When USER= and PASSWORD= are specified outside the quoted connection string, BR augments the ODBC connection string with UID= and PWD= values.

Do not also place UID and PWD inside the connection string itself when using BR's USER= and PASSWORD= parameters.

Other Sample Connection Strings

Target	Example
SQL Server, SQL login	DRIVER=SQL Server;SERVER=myserver;Initial Catalog=mydb;UID=myuser;PWD=mypass
SQL Server, Windows authentication	DRIVER=SQL Server;SERVER=myserver;Initial Catalog=mydb with USER=LOGIN_NAME PASSWORD=BR_PASSWORD
MySQL	DSN=BRG_DEMO;SERVER=127.0.0.1;UID=dbadmin;PWD=mypass;DATABASE=brg_demo;PORT=3306
SQLite	DSN=SQLite3 Datasource;Database=C:\demo\brg_demo.sqlite;SyncPragma=NORMAL;Timeout=100000
Microsoft Access	Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\path\Contact.mdb

ODBC-MANAGER

Use ODBC-MANAGER to display the Windows ODBC manager and allow the user to select the data source at runtime.

Syntax

CONFIG DATABASE <db-ref> ODBC-MANAGER

Important Client/Server Note

ODBC-MANAGER does not work in Client/Server mode because the ODBC selection occurs on the server, not on the user's workstation.

Example

00010 PRINT NEWPAGE
00020 IF Env$("br_model") = "CLIENT_SERVER" THEN PRINT "Warning, you cannot connect to ODBC-MANAGER in Client/Server mode"
00030 EXECUTE "CONFIG DATABASE Test_DB ODBC-MANAGER" ERROR CONNECT_ERROR
00040 PRINT "Connection String is:" !:
00050 PRINT Env$("STATUS.DATABASE.[TEST_DB].CONNECTSTRING")
00060 STOP
00100 CONNECT_ERROR:
00110 PRINT "Error Connecting - Err:"; Err; " Line:"; Line; " Syserr:"; Syserr$

Viewing the Resolved Connection String

After a connection is configured, BR exposes the effective connection string through Env$.

PRINT Env$("STATUS.DATABASE.[MyConn].CONNECTSTRING")

Connection names in STATUS.DATABASE paths are generally uppercased by BR. For example:

PRINT Env$("STATUS.DATABASE.[DEMOCONN].CONNECTSTRING")

OPENing a SQL Statement

After the database connection is configured, use OPEN to bind a BR channel to a SQL statement.

Syntax

OPEN #<channel>: "DATABASE=<db-ref>", SQL <string-expression>, OUTIN

Clause	Meaning
#<channel>	BR file channel number.
"DATABASE=<db-ref>"	Refers to the connection previously configured with CONFIG DATABASE.
SQL <string-expression>	SQL text to bind to the channel.
OUTIN	Opens the channel for output and input. Used for both executing and reading.

Example:

DIM C_SQL$*4096

LET C_SQL$ = "SELECT FirstName, LastName, Email FROM dbo.Customers ORDER BY LastName"

OPEN #(DB_HANDLE := 21): "DATABASE=DemoConn", SQL C_SQL$, OUTIN

The SQL statement is bound at OPEN time. To run a different SQL statement, build a new SQL string and open a new channel.

Opening SQL from an External File

A channel may also use SQL text stored in an external file.

OPEN #H: "DATABASE=MyConn,NAME=Setup_Tables.sql/MyFolder", SQL, OUTIN

Executing SQL with WRITE

A WRITE with no data list executes the SQL statement bound during OPEN.

WRITE #DB_HANDLE:

For a SELECT, WRITE executes the query and prepares the result set. It must be called before the first READ.

For INSERT, UPDATE, and DELETE, WRITE performs the change. No READ follows unless the SQL statement returns rows.

Parameterized Statements

A WRITE with a data list binds values to parameter markers in the prepared SQL statement.

LET C_SQL$ = "INSERT INTO dbo.Customers (FirstName, LastName) VALUES (?, ?)"

OPEN #(H := 21): "DATABASE=DemoConn", SQL C_SQL$, OUTIN
WRITE #H: FIRST$, LAST$
CLOSE #H:

Use parameter binding instead of concatenating operator input into SQL strings whenever possible. It avoids quoting errors and reduces SQL injection risk.

Reading Result Rows

Each READ fetches one result row.

Syntax

READ #<channel>, USING '<form-spec>': <var> [, <var>]* EOF <line-ref>

Example:

RD: READ #DB_HANDLE, USING 'FORM POS 1,C 50,C 50': C1$, C2$ EOF DONE
    PRINT TRIM$(C1$); ","; TRIM$(C2$)
    GOTO RD

DONE: CLOSE #DB_HANDLE:

Notes:

• Columns in the SQL SELECT list map left-to-right onto the variables in the READ.
• The FORM must match the expected data layout.
• Size character fields generously, for example C 50 or C 100.
• Use numeric, date, packed decimal, and other BR FORM types as appropriate.
• EOF fires when the result set is exhausted.
• For many columns, arrays and composite forms are usually easier than long lists of individual variables.

Closing SQL Channels

Always close SQL channels when done.

CLOSE #DB_HANDLE:

This releases the channel and the associated SQL statement/result set.

This is especially important in loops that repeatedly open SQL statements.

Clearing Configured Connections

To clear one named connection:

EXECUTE "CONFIG DATABASE CLEAR MyConn"

To clear all configured database connections:

EXECUTE "CONFIG DATABASE CLEAR ALL"

Complete SELECT Example

The following example connects to SQL Server, executes a SELECT, reads the rows, and prints the result.

00010 ! ============================================================
00020 ! Sample: Connect to SQL Server, SELECT rows, print results
00030 ! ============================================================
00040 PRINT NEWPAGE
00050 DIM C_SQL$*4096
00060 DIM FIRST$*50, LAST$*50, EMAIL$*100
00070 DIM DB_HANDLE
00080 !
00090 ! --- Step 1: Create the connection ---
00100 EXECUTE 'CONFIG DATABASE DemoConn CONNECTSTRING="DRIVER=SQL Server;SERVER=myserver;Initial Catalog=mydb;UID=myuser;PWD=mypass"' ERROR CONNECT_ERR
00110 !
00120 ! --- Step 2: Define the SQL statement and open a channel ---
00130 LET C_SQL$ = "SELECT FirstName, LastName, Email FROM dbo.Customers ORDER BY LastName"
00140 OPEN #(DB_HANDLE := 21): "DATABASE=DemoConn", SQL C_SQL$, OUTIN ERROR OPEN_ERR
00150 !
00160 ! --- Step 3: Execute the query ---
00170 WRITE #DB_HANDLE: ERROR EXEC_ERR
00180 !
00190 ! --- Step 4: Loop through result rows ---
00200 RD: READ #DB_HANDLE, USING 'FORM POS 1,C 50,C 50,C 100': FIRST$, LAST$, EMAIL$ EOF DONE
00210     PRINT TRIM$(LAST$); ", "; TRIM$(FIRST$); "  <"; TRIM$(EMAIL$); ">"
00220     GOTO RD
00230 !
00240 DONE:
00250 CLOSE #DB_HANDLE:
00260 PRINT "Finished."
00270 STOP
00280 !
00290 CONNECT_ERR:
00300 OPEN_ERR:
00310 EXEC_ERR:
00320     PRINT "Error:"; ERR; " Line:"; LINE; " Syserr:"; SYSERR$
00330     STOP

Windows Authentication Example

For SQL Server integrated security, omit credentials from the quoted connection string and allow BR to supply them.

EXECUTE 'CONFIG DATABASE DemoConn CONNECTSTRING="DRIVER=SQL Server;Initial Catalog=mydb;SERVER=myserver" USER=LOGIN_NAME PASSWORD=BR_PASSWORD'

INSERT Example

LET C_SQL$ = "INSERT INTO dbo.Customers (FirstName, LastName) VALUES ('Jane', 'Doe')"

OPEN #(DB_HANDLE := 21): "DATABASE=DemoConn", SQL C_SQL$, OUTIN
WRITE #DB_HANDLE:
CLOSE #DB_HANDLE:

Inside a BR string, two double-quotes represent one literal double-quote.

Example:

LET C_SQL$ = "INSERT INTO dbo.Customers (FirstName, LastName) VALUES (""Jane"", ""Doe"")"

Error Handling

Use ERROR clauses or ON ERROR to trap connection, execution, and fetch failures.

Report:

• ERR
• LINE
• SYSERR$

Example:

OPEN #(H := 21): "DATABASE=DemoConn", SQL C_SQL$, OUTIN ERROR SQL_ERR
WRITE #H: ERROR SQL_ERR

RD: READ #H, USING 'FORM POS 1,C 50': C1$ EOF DONE ERROR SQL_ERR
    PRINT C1$
    GOTO RD

DONE:
    CLOSE #H:
    STOP

SQL_ERR:
    PRINT "Error:"; ERR; " Line:"; LINE; " Syserr:"; SYSERR$
    STOP

Common SQL / ODBC Error Codes

Code	Meaning
3006 / 4006	SQL execute-direct failed.
3007 / 4007	WRITE used with the incorrect number of data elements. Usually a parameter mismatch.
3010 / 4010	SQL fetch-row failed.
3011 / 4011	SQL bind-parameter failure.
4015	The specified database has not been opened.
4270	EOF on READ. This is normal end-of-result-set behavior when an EOF clause is not used.

First checks when a connection or query fails:

• Confirm the ODBC driver name.
• Confirm the server name or IP address.
• Confirm the database name.
• Confirm credentials.
• Confirm network/firewall access.
• Confirm the ODBC driver is installed on the machine running BR.
• If using ODBC-MANAGER, confirm BR is not running in Client/Server mode.

STATUS.DATABASE Introspection

After opening a channel against a connection, BR exposes metadata through STATUS.DATABASE environment variables.

Resolved Connection String

PRINT Env$("STATUS.DATABASE.[DEMOCONN].CONNECTSTRING")

List Tables

LET Env$("STATUS.DATABASE.[DEMOCONN].TABLES.LIST", MAT TABLES$)

List Columns

LET Env$("STATUS.DATABASE.[DEMOCONN].TABLES.[CUSTOMERS].COLUMNS.LIST", MAT COLS$)

Column Metadata

PRINT Env$("STATUS.DATABASE.[DEMOCONN].TABLES.[CUSTOMERS].COLUMNS.[EMAIL].TYPE")
PRINT Env$("STATUS.DATABASE.[DEMOCONN].TABLES.[CUSTOMERS].COLUMNS.[EMAIL].LENGTH")

Connection names in STATUS.DATABASE paths are uppercased by BR.

Production-Style Usage Pattern

The same ODBC pattern can be used in larger production routines that synchronize BR data files with SQL Server tables.

A production routine commonly performs these tasks:

• Configure named SQL connections.
• Discover SQL table structure.
• Read SQL rows through BR channels.
• Compare SQL rows against BR file records.
• Perform inserts, updates, and deletes.
• Page through large SQL tables.
• Reuse and clear named connections.

Example Setup Pattern

A program may load supporting routines and configure a default SQL connection from environment variables. These examples and environment are not part of the Business Rules language, rather suggestoins that may be usefull.

LIBRARY "SQL/Library": Fnopen_Sql_File
LIBRARY "Util/Library": Fngethandle, Fnget_Form

LET Fn_Config_Sql("SetUpConn", Env$("SQL-SYNC-SERVER"), Env$("SQL-SYNC-DATABASE"))

Common environment variables:

Variable	Purpose
SQL-SYNC-SERVER	SQL Server host name.
SQL-SYNC-DATABASE	Target SQL Server database.
SQL-SYNC-CONNECTION	Optional override connection string. May support a [LOGIN_NAME] placeholder.
SQL-SYNC-DRIVER	Optional ODBC driver name. Defaults to SQL Server.

Connection Helper Pattern

A helper routine can wrap CONFIG DATABASE and cache connections so each named reference is configured only once per session.

Typical parameters:

Parameter	Purpose
Cs_Connection$	Arbitrary database reference name, such as C_SelectCon or C_InsertCon.
Cs_Server$	SQL Server host name.
Cs_Database$	SQL Server database name.
Cs_User$	SQL login. Often defaults to LOGIN_NAME$.
Password$	SQL password. Often defaults to BR_PASSWORD for Windows authentication.
Cs_Sql_Authentication	Indicates SQL authentication mode.
Cs_Driver$	ODBC driver name.

Example pattern:

DEF Fn_Config_Sql(Cs_Connection$*256, Cs_Server$*256, Cs_Database$*256;
                  Cs_User$*256, Password$*256, Cs_Sql_Authentication, Cs_Driver$*256)

    EXECUTE 'CONFIG DATABASE ' & Cs_Connection$ &
            ' CONNECTSTRING="' & Connectionstring$ & '"' ERROR CONNECT_ERROR

FNEND

Separate Connections for Separate Workloads

Larger routines commonly use separate named connections so concurrent SQL channels do not collide. Technically you can perform everything through a single connection.

Connection Name	Typical Use
C_SelectCon	SELECT queries and schema discovery.
C_InsertCon	INSERT operations.
C_UpdateCon	UPDATE operations.
C_DeleteCon	DELETE operations.
C_RetryCon	Retry path after transient SQL errors.
SetUpConn	Folder/setup stored procedures.

Example:

LET Sconn$ = "C_SelectCon"
LET Insconn$ = "C_InsertCon"
LET Delconn$ = "C_DeleteCon"

LET Fn_Config_Sql(Sconn$, Bc_Server$, Bc_Database$, Bc_User$, Bc_Password$)
LET Fn_Config_Sql(Insconn$, Bc_Server$, Bc_Database$, Bc_User$, Bc_Password$)
LET Fn_Config_Sql(Delconn$, Bc_Server$, Bc_Database$, Bc_User$, Bc_Password$)

Discovering SQL Table Structure

A program can discover SQL columns by opening a SELECT TOP 1 * channel and reading metadata from STATUS.DATABASE.

LET Qry_Ispent_Design$ = "SELECT TOP 1 * FROM " & Gsf_Object$

OPEN #(Db_Handle_Design := Fngethandle): "DATABASE=" & Gsf_Connection$, SQL Qry_Ispent_Design$, OUTIN

LET Env$("STATUS.DATABASE.[" & Trim$(Uprc$(Gsf_Connection$)) &
       "].TABLES.[" & Trim$(Uprc$(Gsf_Object$)) & "].COLUMNS", MAT Columns$)

CLOSE #Db_Handle_Design:

The resulting metadata can be used to classify columns as character, date, or numeric and to build the appropriate BR FORM definitions.

Incremental Processing Pattern

Large SQL tables should usually be processed in batches rather than loaded all at once.

! Build the SELECT
LET Qry_Select$ = "SELECT TOP 100000 col1, col2 FROM [dbo].[MYTABLE] WITH (NOLOCK)" &
                  " WHERE Folder_Number = " & Folder_Number$ &
                  " ORDER BY Record_Number ASC"

! Save SQL for debugging
LET Setenv("ER_Last_SQL", Qry_Select$)

! Open and execute
OPEN #(Selhandle := 20): "DATABASE=C_SelectCon", SQL Qry_Select$, OUTIN
WRITE #Selhandle:

! Read rows
READ #Selhandle: MAT Sql_Object_Data$, MAT Sql_Object_Data_Dates$, MAT Sql_Object_Data EOF SQL_EOF

When the batch is exhausted, close the channel, build a new SELECT TOP ... WHERE Record_Number > <last> query, and open a fresh channel.

DML Pattern: INSERT / UPDATE / DELETE

Data-changing SQL follows the same OPEN → WRITE → CLOSE pattern.

LET Setenv("ER_Last_SQL", Insert_Sql$)

OPEN #(Inshandle := Fngethandle): "DATABASE=C_InsertCon", SQL Insert_Sql$, OUTIN
WRITE #Inshandle: ERROR SQL_ERROR
CLOSE #Inshandle:

Closing Named Connections

At the end of a SQL operation, close or clear configured connections. For simplicity, you can define a function to close the connection.

LET Fn_Close_Db_Connections(Sconn$)
LET Fn_Close_Db_Connections(Insconn$)

Or clear all configured database connections:

EXECUTE "CONFIG DATABASE CLEAR ALL"

Example Helper Entry Points

A larger BR SQL integration may include helper routines similar to the following:

Function	Purpose
Fn_Config_Sql	Establishes a named ODBC connection.
Fn_Get_Sql_Fields	Discovers SQL table or view column layout.
Fnshow_Database	Prints connection, table, and column metadata.
Fn_Bulk_Insert	Copies BR records to SQL in a full load.
Fn_Incremental_Sync	Compares and reconciles BR rows against SQL rows.
Fn_Create_Table	Creates SQL tables.
Fn_Alter_Table	Alters SQL table structure.
Fn_Truncate_Table	Clears a SQL table.
Fn_Close_Db_Connections	Releases configured SQL connections.

Example Usage from a Program

LIBRARY "SQL/Library": Fnopen_Sql_File
LIBRARY "util/Library": Fngethandle, Fnget_Form

LET Fn_Config_Sql("MyConn", "VT-SQL01", "MyDatabase")

LET Fn_Get_Sql_Fields("MyConn", "CUSTOMERS", MAT Data$, MAT Dates$, MAT Data)

! Program logic here

LET Fn_Close_Db_Connections("MyConn")

Security Notes

• Protect source files and saved connection strings. Literal credentials inside connection strings are visible to anyone who can read the program or configuration.
• Prefer PASSWORDD=, BR_PASSWORD, LOGIN_NAME, or runtime prompts over literal passwords.
• Prefer parameterized statements using WRITE #h: value1, value2 over string concatenation of user input.
• Avoid placing both UID/PWD inside the connection string and USER/PASSWORD outside the connection string.
• Do not use ODBC-MANAGER in Client/Server mode.
• When practical, log the last SQL statement to an environment variable such as ER_Last_SQL for troubleshooting.
• Always close SQL channels after use.

Troubleshooting Checklist

When a database connection fails:

1. Confirm the ODBC driver is installed on the machine running BR.
2. Confirm the driver name matches the connection string.
3. Confirm the SQL Server, MySQL, SQLite, or Access target is reachable.
4. Confirm credentials.
5. Confirm the database name.
6. Confirm firewall and network access.
7. Confirm the connection works in the Windows ODBC manager.
8. If using Windows authentication, confirm the BR process is running under the expected user context.
9. If using Client/Server mode, avoid ODBC-MANAGER.
10. Print SYSERR$ for the driver-specific error message.

When a query fails:

1. Print or log the SQL statement.
2. Confirm table and column names.
3. Confirm quoting of strings and dates.
4. Confirm the number of parameter markers matches the number of values supplied to WRITE.
5. Confirm the READ form matches the selected columns.
6. Confirm character fields are wide enough.
7. Use STATUS.DATABASE metadata to inspect table and column definitions.

See Also

• CONFIG
• EXECUTE
• OPEN
• WRITE
• READ
• CLOSE
• Encrypted passwords
• Unhex
• Decrypt