BR Wiki - User contributions [en]

DATABASE

2026-07-09T13:47:35Z

GomezL: /* Closing Named Connections */

= DATABASE =

Starting in '''Business Rules! 4.3''', the <code>CONFIG DATABASE</code> 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:

<ol>
<li><code>CONFIG DATABASE</code> names the connection.</li>
<li><code>OPEN</code> binds a BR file channel to a SQL statement.</li>
<li><code>WRITE</code> executes the SQL statement.</li>
<li><code>READ</code> fetches rows for a <code>SELECT</code>.</li>
<li><code>CLOSE</code> releases the statement/result set.</li>
</ol>

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

== Overview ==

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

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

Example:

<pre>
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:
</pre>

== CONFIG DATABASE ==

<code>CONFIG DATABASE</code> defines a named ODBC connection.

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

== Connection Methods ==

<code>CONFIG DATABASE</code> supports three connection methods:

<ul>
<li><code>DSN</code></li>
<li><code>CONNECTSTRING</code></li>
<li><code>ODBC-MANAGER</code></li>
</ul>

=== General Syntax ===

<pre>
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> } ]
</pre>

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

== DSN ==

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

=== Syntax ===

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

=== Example ===

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

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 <code>CONNECTSTRING</code> 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 ===

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

=== SQL Server with SQL Login ===

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

Where:

<table class="wikitable">
<tr>
<th>Value</th>
<th>Meaning</th>
</tr>
<tr>
<td><code>db-ref</code></td>
<td>BR database reference name.</td>
</tr>
<tr>
<td><code>server</code></td>
<td>SQL Server FQDN, host name, or IP address.</td>
</tr>
<tr>
<td><code>database</code></td>
<td>SQL Server database name.</td>
</tr>
<tr>
<td><code>username</code></td>
<td>SQL Server login.</td>
</tr>
<tr>
<td><code>password</code></td>
<td>SQL Server password.</td>
</tr>
</table>

=== SQL Server with Windows Authentication ===

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

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

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

=== Other Sample Connection Strings ===

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

== ODBC-MANAGER ==

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

=== Syntax ===

<pre>
CONFIG DATABASE <db-ref> ODBC-MANAGER
</pre>

=== Important Client/Server Note ===

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

=== Example ===

<pre>
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$
</pre>

== Viewing the Resolved Connection String ==

After a connection is configured, BR exposes the effective connection string through <code>Env$</code>.

<pre>
PRINT Env$("STATUS.DATABASE.[MyConn].CONNECTSTRING")
</pre>

Connection names in <code>STATUS.DATABASE</code> paths are generally uppercased by BR. For example:

<pre>
PRINT Env$("STATUS.DATABASE.[DEMOCONN].CONNECTSTRING")
</pre>

== OPENing a SQL Statement ==

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

=== Syntax ===

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

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

Example:

<pre>
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
</pre>

The SQL statement is bound at <code>OPEN</code> 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.

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

== Executing SQL with WRITE ==

A <code>WRITE</code> with no data list executes the SQL statement bound during <code>OPEN</code>.

<pre>
WRITE #DB_HANDLE:
</pre>

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

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

== Parameterized Statements ==

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

<pre>
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:
</pre>

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 <code>READ</code> fetches one result row.

=== Syntax ===

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

Example:

<pre>
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:
</pre>

Notes:

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

== Closing SQL Channels ==

Always close SQL channels when done.

<pre>
CLOSE #DB_HANDLE:
</pre>

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:

<pre>
EXECUTE "CONFIG DATABASE CLEAR MyConn"
</pre>

To clear all configured database connections:

<pre>
EXECUTE "CONFIG DATABASE CLEAR ALL"
</pre>

== Complete SELECT Example ==

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

<pre>
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
</pre>

== Windows Authentication Example ==

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

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

== INSERT Example ==

<pre>
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:
</pre>

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

Example:

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

== Error Handling ==

Use <code>ERROR</code> clauses or <code>ON ERROR</code> to trap connection, execution, and fetch failures.

Report:

<ul>
<li><code>ERR</code></li>
<li><code>LINE</code></li>
<li><code>SYSERR$</code></li>
</ul>

Example:

<pre>
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
</pre>

== Common SQL / ODBC Error Codes ==

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

First checks when a connection or query fails:

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

== STATUS.DATABASE Introspection ==

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

=== Resolved Connection String ===

<pre>
PRINT Env$("STATUS.DATABASE.[DEMOCONN].CONNECTSTRING")
</pre>

=== List Tables ===

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

=== List Columns ===

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

=== Column Metadata ===

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

Connection names in <code>STATUS.DATABASE</code> 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:

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

== 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.

<pre>
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"))
</pre>

Common environment variables:

<table class="wikitable">
<tr>
<th>Variable</th>
<th>Purpose</th>
</tr>
<tr>
<td><code>SQL-SYNC-SERVER</code></td>
<td>SQL Server host name.</td>
</tr>
<tr>
<td><code>SQL-SYNC-DATABASE</code></td>
<td>Target SQL Server database.</td>
</tr>
<tr>
<td><code>SQL-SYNC-CONNECTION</code></td>
<td>Optional override connection string. May support a <code>[LOGIN_NAME]</code> placeholder.</td>
</tr>
<tr>
<td><code>SQL-SYNC-DRIVER</code></td>
<td>Optional ODBC driver name. Defaults to <code>SQL Server</code>.</td>
</tr>
</table>

== Connection Helper Pattern ==

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

Typical parameters:

<table class="wikitable">
<tr>
<th>Parameter</th>
<th>Purpose</th>
</tr>
<tr>
<td><code>Cs_Connection$</code></td>
<td>Arbitrary database reference name, such as <code>C_SelectCon</code> or <code>C_InsertCon</code>.</td>
</tr>
<tr>
<td><code>Cs_Server$</code></td>
<td>SQL Server host name.</td>
</tr>
<tr>
<td><code>Cs_Database$</code></td>
<td>SQL Server database name.</td>
</tr>
<tr>
<td><code>Cs_User$</code></td>
<td>SQL login. Often defaults to <code>LOGIN_NAME$</code>.</td>
</tr>
<tr>
<td><code>Password$</code></td>
<td>SQL password. Often defaults to <code>BR_PASSWORD</code> for Windows authentication.</td>
</tr>
<tr>
<td><code>Cs_Sql_Authentication</code></td>
<td>Indicates SQL authentication mode.</td>
</tr>
<tr>
<td><code>Cs_Driver$</code></td>
<td>ODBC driver name.</td>
</tr>
</table>

Example pattern:

<pre>
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
</pre>

== 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.

<table class="wikitable">
<tr>
<th>Connection Name</th>
<th>Typical Use</th>
</tr>
<tr>
<td><code>C_SelectCon</code></td>
<td>SELECT queries and schema discovery.</td>
</tr>
<tr>
<td><code>C_InsertCon</code></td>
<td>INSERT operations.</td>
</tr>
<tr>
<td><code>C_UpdateCon</code></td>
<td>UPDATE operations.</td>
</tr>
<tr>
<td><code>C_DeleteCon</code></td>
<td>DELETE operations.</td>
</tr>
<tr>
<td><code>C_RetryCon</code></td>
<td>Retry path after transient SQL errors.</td>
</tr>
<tr>
<td><code>SetUpConn</code></td>
<td>Folder/setup stored procedures.</td>
</tr>
</table>

Example:

<pre>
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$)
</pre>

== Discovering SQL Table Structure ==

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

<pre>
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:
</pre>

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

== Incremental Processing Pattern ==

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

<pre>
! 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
</pre>

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

== DML Pattern: INSERT / UPDATE / DELETE ==

Data-changing SQL follows the same <code>OPEN</code> → <code>WRITE</code> → <code>CLOSE</code> pattern.

<pre>
LET Setenv("ER_Last_SQL", Insert_Sql$)

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

== 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.

<pre>
LET Fn_Close_Db_Connections(Sconn$)
LET Fn_Close_Db_Connections(Insconn$)
</pre>

Or clear all configured database connections:

<pre>
EXECUTE "CONFIG DATABASE CLEAR ALL"
</pre>

== Example Helper Entry Points ==

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

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

== Example Usage from a Program ==

<pre>
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")
</pre>

== Security Notes ==

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

== Troubleshooting Checklist ==

When a database connection fails:

<ol>
<li>Confirm the ODBC driver is installed on the machine running BR.</li>
<li>Confirm the driver name matches the connection string.</li>
<li>Confirm the SQL Server, MySQL, SQLite, or Access target is reachable.</li>
<li>Confirm credentials.</li>
<li>Confirm the database name.</li>
<li>Confirm firewall and network access.</li>
<li>Confirm the connection works in the Windows ODBC manager.</li>
<li>If using Windows authentication, confirm the BR process is running under the expected user context.</li>
<li>If using Client/Server mode, avoid <code>ODBC-MANAGER</code>.</li>
<li>Print <code>SYSERR$</code> for the driver-specific error message.</li>
</ol>

When a query fails:

<ol>
<li>Print or log the SQL statement.</li>
<li>Confirm table and column names.</li>
<li>Confirm quoting of strings and dates.</li>
<li>Confirm the number of parameter markers matches the number of values supplied to <code>WRITE</code>.</li>
<li>Confirm the <code>READ</code> form matches the selected columns.</li>
<li>Confirm character fields are wide enough.</li>
<li>Use <code>STATUS.DATABASE</code> metadata to inspect table and column definitions.</li>
</ol>

== See Also ==

<ul>
<li>[[CONFIG]]</li>
<li>[[EXECUTE]]</li>
<li>[[OPEN]]</li>
<li>[[WRITE]]</li>
<li>[[READ]]</li>
<li>[[CLOSE]]</li>
<li>[[Encrypted passwords]]</li>
<li>[[Unhex]]</li>
<li>[[Decrypt]]</li>
</ul>

[[Category:All Parameters]]

DATABASE

2026-07-09T13:45:28Z

GomezL: /* Separate Connections for Separate Workloads */

= DATABASE =

Starting in '''Business Rules! 4.3''', the <code>CONFIG DATABASE</code> 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:

<ol>
<li><code>CONFIG DATABASE</code> names the connection.</li>
<li><code>OPEN</code> binds a BR file channel to a SQL statement.</li>
<li><code>WRITE</code> executes the SQL statement.</li>
<li><code>READ</code> fetches rows for a <code>SELECT</code>.</li>
<li><code>CLOSE</code> releases the statement/result set.</li>
</ol>

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

== Overview ==

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

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

Example:

<pre>
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:
</pre>

== CONFIG DATABASE ==

<code>CONFIG DATABASE</code> defines a named ODBC connection.

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

== Connection Methods ==

<code>CONFIG DATABASE</code> supports three connection methods:

<ul>
<li><code>DSN</code></li>
<li><code>CONNECTSTRING</code></li>
<li><code>ODBC-MANAGER</code></li>
</ul>

=== General Syntax ===

<pre>
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> } ]
</pre>

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

== DSN ==

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

=== Syntax ===

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

=== Example ===

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

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 <code>CONNECTSTRING</code> 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 ===

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

=== SQL Server with SQL Login ===

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

Where:

<table class="wikitable">
<tr>
<th>Value</th>
<th>Meaning</th>
</tr>
<tr>
<td><code>db-ref</code></td>
<td>BR database reference name.</td>
</tr>
<tr>
<td><code>server</code></td>
<td>SQL Server FQDN, host name, or IP address.</td>
</tr>
<tr>
<td><code>database</code></td>
<td>SQL Server database name.</td>
</tr>
<tr>
<td><code>username</code></td>
<td>SQL Server login.</td>
</tr>
<tr>
<td><code>password</code></td>
<td>SQL Server password.</td>
</tr>
</table>

=== SQL Server with Windows Authentication ===

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

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

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

=== Other Sample Connection Strings ===

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

== ODBC-MANAGER ==

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

=== Syntax ===

<pre>
CONFIG DATABASE <db-ref> ODBC-MANAGER
</pre>

=== Important Client/Server Note ===

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

=== Example ===

<pre>
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$
</pre>

== Viewing the Resolved Connection String ==

After a connection is configured, BR exposes the effective connection string through <code>Env$</code>.

<pre>
PRINT Env$("STATUS.DATABASE.[MyConn].CONNECTSTRING")
</pre>

Connection names in <code>STATUS.DATABASE</code> paths are generally uppercased by BR. For example:

<pre>
PRINT Env$("STATUS.DATABASE.[DEMOCONN].CONNECTSTRING")
</pre>

== OPENing a SQL Statement ==

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

=== Syntax ===

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

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

Example:

<pre>
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
</pre>

The SQL statement is bound at <code>OPEN</code> 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.

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

== Executing SQL with WRITE ==

A <code>WRITE</code> with no data list executes the SQL statement bound during <code>OPEN</code>.

<pre>
WRITE #DB_HANDLE:
</pre>

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

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

== Parameterized Statements ==

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

<pre>
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:
</pre>

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 <code>READ</code> fetches one result row.

=== Syntax ===

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

Example:

<pre>
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:
</pre>

Notes:

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

== Closing SQL Channels ==

Always close SQL channels when done.

<pre>
CLOSE #DB_HANDLE:
</pre>

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:

<pre>
EXECUTE "CONFIG DATABASE CLEAR MyConn"
</pre>

To clear all configured database connections:

<pre>
EXECUTE "CONFIG DATABASE CLEAR ALL"
</pre>

== Complete SELECT Example ==

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

<pre>
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
</pre>

== Windows Authentication Example ==

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

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

== INSERT Example ==

<pre>
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:
</pre>

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

Example:

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

== Error Handling ==

Use <code>ERROR</code> clauses or <code>ON ERROR</code> to trap connection, execution, and fetch failures.

Report:

<ul>
<li><code>ERR</code></li>
<li><code>LINE</code></li>
<li><code>SYSERR$</code></li>
</ul>

Example:

<pre>
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
</pre>

== Common SQL / ODBC Error Codes ==

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

First checks when a connection or query fails:

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

== STATUS.DATABASE Introspection ==

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

=== Resolved Connection String ===

<pre>
PRINT Env$("STATUS.DATABASE.[DEMOCONN].CONNECTSTRING")
</pre>

=== List Tables ===

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

=== List Columns ===

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

=== Column Metadata ===

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

Connection names in <code>STATUS.DATABASE</code> 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:

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

== 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.

<pre>
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"))
</pre>

Common environment variables:

<table class="wikitable">
<tr>
<th>Variable</th>
<th>Purpose</th>
</tr>
<tr>
<td><code>SQL-SYNC-SERVER</code></td>
<td>SQL Server host name.</td>
</tr>
<tr>
<td><code>SQL-SYNC-DATABASE</code></td>
<td>Target SQL Server database.</td>
</tr>
<tr>
<td><code>SQL-SYNC-CONNECTION</code></td>
<td>Optional override connection string. May support a <code>[LOGIN_NAME]</code> placeholder.</td>
</tr>
<tr>
<td><code>SQL-SYNC-DRIVER</code></td>
<td>Optional ODBC driver name. Defaults to <code>SQL Server</code>.</td>
</tr>
</table>

== Connection Helper Pattern ==

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

Typical parameters:

<table class="wikitable">
<tr>
<th>Parameter</th>
<th>Purpose</th>
</tr>
<tr>
<td><code>Cs_Connection$</code></td>
<td>Arbitrary database reference name, such as <code>C_SelectCon</code> or <code>C_InsertCon</code>.</td>
</tr>
<tr>
<td><code>Cs_Server$</code></td>
<td>SQL Server host name.</td>
</tr>
<tr>
<td><code>Cs_Database$</code></td>
<td>SQL Server database name.</td>
</tr>
<tr>
<td><code>Cs_User$</code></td>
<td>SQL login. Often defaults to <code>LOGIN_NAME$</code>.</td>
</tr>
<tr>
<td><code>Password$</code></td>
<td>SQL password. Often defaults to <code>BR_PASSWORD</code> for Windows authentication.</td>
</tr>
<tr>
<td><code>Cs_Sql_Authentication</code></td>
<td>Indicates SQL authentication mode.</td>
</tr>
<tr>
<td><code>Cs_Driver$</code></td>
<td>ODBC driver name.</td>
</tr>
</table>

Example pattern:

<pre>
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
</pre>

== 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.

<table class="wikitable">
<tr>
<th>Connection Name</th>
<th>Typical Use</th>
</tr>
<tr>
<td><code>C_SelectCon</code></td>
<td>SELECT queries and schema discovery.</td>
</tr>
<tr>
<td><code>C_InsertCon</code></td>
<td>INSERT operations.</td>
</tr>
<tr>
<td><code>C_UpdateCon</code></td>
<td>UPDATE operations.</td>
</tr>
<tr>
<td><code>C_DeleteCon</code></td>
<td>DELETE operations.</td>
</tr>
<tr>
<td><code>C_RetryCon</code></td>
<td>Retry path after transient SQL errors.</td>
</tr>
<tr>
<td><code>SetUpConn</code></td>
<td>Folder/setup stored procedures.</td>
</tr>
</table>

Example:

<pre>
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$)
</pre>

== Discovering SQL Table Structure ==

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

<pre>
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:
</pre>

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

== Incremental Processing Pattern ==

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

<pre>
! 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
</pre>

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

== DML Pattern: INSERT / UPDATE / DELETE ==

Data-changing SQL follows the same <code>OPEN</code> → <code>WRITE</code> → <code>CLOSE</code> pattern.

<pre>
LET Setenv("ER_Last_SQL", Insert_Sql$)

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

== Closing Named Connections ==

At the end of a SQL operation, close or clear configured connections.

<pre>
LET Fn_Close_Db_Connections(Sconn$)
LET Fn_Close_Db_Connections(Insconn$)
</pre>

Or clear all configured database connections:

<pre>
EXECUTE "CONFIG DATABASE CLEAR ALL"
</pre>

== Example Helper Entry Points ==

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

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

== Example Usage from a Program ==

<pre>
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")
</pre>

== Security Notes ==

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

== Troubleshooting Checklist ==

When a database connection fails:

<ol>
<li>Confirm the ODBC driver is installed on the machine running BR.</li>
<li>Confirm the driver name matches the connection string.</li>
<li>Confirm the SQL Server, MySQL, SQLite, or Access target is reachable.</li>
<li>Confirm credentials.</li>
<li>Confirm the database name.</li>
<li>Confirm firewall and network access.</li>
<li>Confirm the connection works in the Windows ODBC manager.</li>
<li>If using Windows authentication, confirm the BR process is running under the expected user context.</li>
<li>If using Client/Server mode, avoid <code>ODBC-MANAGER</code>.</li>
<li>Print <code>SYSERR$</code> for the driver-specific error message.</li>
</ol>

When a query fails:

<ol>
<li>Print or log the SQL statement.</li>
<li>Confirm table and column names.</li>
<li>Confirm quoting of strings and dates.</li>
<li>Confirm the number of parameter markers matches the number of values supplied to <code>WRITE</code>.</li>
<li>Confirm the <code>READ</code> form matches the selected columns.</li>
<li>Confirm character fields are wide enough.</li>
<li>Use <code>STATUS.DATABASE</code> metadata to inspect table and column definitions.</li>
</ol>

== See Also ==

<ul>
<li>[[CONFIG]]</li>
<li>[[EXECUTE]]</li>
<li>[[OPEN]]</li>
<li>[[WRITE]]</li>
<li>[[READ]]</li>
<li>[[CLOSE]]</li>
<li>[[Encrypted passwords]]</li>
<li>[[Unhex]]</li>
<li>[[Decrypt]]</li>
</ul>

[[Category:All Parameters]]

DATABASE

2026-07-09T13:44:36Z

GomezL: /* Example Setup Pattern */

= DATABASE =

Starting in '''Business Rules! 4.3''', the <code>CONFIG DATABASE</code> 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:

<ol>
<li><code>CONFIG DATABASE</code> names the connection.</li>
<li><code>OPEN</code> binds a BR file channel to a SQL statement.</li>
<li><code>WRITE</code> executes the SQL statement.</li>
<li><code>READ</code> fetches rows for a <code>SELECT</code>.</li>
<li><code>CLOSE</code> releases the statement/result set.</li>
</ol>

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

== Overview ==

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

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

Example:

<pre>
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:
</pre>

== CONFIG DATABASE ==

<code>CONFIG DATABASE</code> defines a named ODBC connection.

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

== Connection Methods ==

<code>CONFIG DATABASE</code> supports three connection methods:

<ul>
<li><code>DSN</code></li>
<li><code>CONNECTSTRING</code></li>
<li><code>ODBC-MANAGER</code></li>
</ul>

=== General Syntax ===

<pre>
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> } ]
</pre>

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

== DSN ==

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

=== Syntax ===

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

=== Example ===

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

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 <code>CONNECTSTRING</code> 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 ===

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

=== SQL Server with SQL Login ===

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

Where:

<table class="wikitable">
<tr>
<th>Value</th>
<th>Meaning</th>
</tr>
<tr>
<td><code>db-ref</code></td>
<td>BR database reference name.</td>
</tr>
<tr>
<td><code>server</code></td>
<td>SQL Server FQDN, host name, or IP address.</td>
</tr>
<tr>
<td><code>database</code></td>
<td>SQL Server database name.</td>
</tr>
<tr>
<td><code>username</code></td>
<td>SQL Server login.</td>
</tr>
<tr>
<td><code>password</code></td>
<td>SQL Server password.</td>
</tr>
</table>

=== SQL Server with Windows Authentication ===

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

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

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

=== Other Sample Connection Strings ===

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

== ODBC-MANAGER ==

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

=== Syntax ===

<pre>
CONFIG DATABASE <db-ref> ODBC-MANAGER
</pre>

=== Important Client/Server Note ===

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

=== Example ===

<pre>
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$
</pre>

== Viewing the Resolved Connection String ==

After a connection is configured, BR exposes the effective connection string through <code>Env$</code>.

<pre>
PRINT Env$("STATUS.DATABASE.[MyConn].CONNECTSTRING")
</pre>

Connection names in <code>STATUS.DATABASE</code> paths are generally uppercased by BR. For example:

<pre>
PRINT Env$("STATUS.DATABASE.[DEMOCONN].CONNECTSTRING")
</pre>

== OPENing a SQL Statement ==

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

=== Syntax ===

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

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

Example:

<pre>
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
</pre>

The SQL statement is bound at <code>OPEN</code> 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.

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

== Executing SQL with WRITE ==

A <code>WRITE</code> with no data list executes the SQL statement bound during <code>OPEN</code>.

<pre>
WRITE #DB_HANDLE:
</pre>

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

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

== Parameterized Statements ==

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

<pre>
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:
</pre>

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 <code>READ</code> fetches one result row.

=== Syntax ===

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

Example:

<pre>
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:
</pre>

Notes:

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

== Closing SQL Channels ==

Always close SQL channels when done.

<pre>
CLOSE #DB_HANDLE:
</pre>

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:

<pre>
EXECUTE "CONFIG DATABASE CLEAR MyConn"
</pre>

To clear all configured database connections:

<pre>
EXECUTE "CONFIG DATABASE CLEAR ALL"
</pre>

== Complete SELECT Example ==

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

<pre>
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
</pre>

== Windows Authentication Example ==

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

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

== INSERT Example ==

<pre>
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:
</pre>

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

Example:

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

== Error Handling ==

Use <code>ERROR</code> clauses or <code>ON ERROR</code> to trap connection, execution, and fetch failures.

Report:

<ul>
<li><code>ERR</code></li>
<li><code>LINE</code></li>
<li><code>SYSERR$</code></li>
</ul>

Example:

<pre>
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
</pre>

== Common SQL / ODBC Error Codes ==

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

First checks when a connection or query fails:

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

== STATUS.DATABASE Introspection ==

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

=== Resolved Connection String ===

<pre>
PRINT Env$("STATUS.DATABASE.[DEMOCONN].CONNECTSTRING")
</pre>

=== List Tables ===

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

=== List Columns ===

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

=== Column Metadata ===

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

Connection names in <code>STATUS.DATABASE</code> 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:

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

== 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.

<pre>
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"))
</pre>

Common environment variables:

<table class="wikitable">
<tr>
<th>Variable</th>
<th>Purpose</th>
</tr>
<tr>
<td><code>SQL-SYNC-SERVER</code></td>
<td>SQL Server host name.</td>
</tr>
<tr>
<td><code>SQL-SYNC-DATABASE</code></td>
<td>Target SQL Server database.</td>
</tr>
<tr>
<td><code>SQL-SYNC-CONNECTION</code></td>
<td>Optional override connection string. May support a <code>[LOGIN_NAME]</code> placeholder.</td>
</tr>
<tr>
<td><code>SQL-SYNC-DRIVER</code></td>
<td>Optional ODBC driver name. Defaults to <code>SQL Server</code>.</td>
</tr>
</table>

== Connection Helper Pattern ==

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

Typical parameters:

<table class="wikitable">
<tr>
<th>Parameter</th>
<th>Purpose</th>
</tr>
<tr>
<td><code>Cs_Connection$</code></td>
<td>Arbitrary database reference name, such as <code>C_SelectCon</code> or <code>C_InsertCon</code>.</td>
</tr>
<tr>
<td><code>Cs_Server$</code></td>
<td>SQL Server host name.</td>
</tr>
<tr>
<td><code>Cs_Database$</code></td>
<td>SQL Server database name.</td>
</tr>
<tr>
<td><code>Cs_User$</code></td>
<td>SQL login. Often defaults to <code>LOGIN_NAME$</code>.</td>
</tr>
<tr>
<td><code>Password$</code></td>
<td>SQL password. Often defaults to <code>BR_PASSWORD</code> for Windows authentication.</td>
</tr>
<tr>
<td><code>Cs_Sql_Authentication</code></td>
<td>Indicates SQL authentication mode.</td>
</tr>
<tr>
<td><code>Cs_Driver$</code></td>
<td>ODBC driver name.</td>
</tr>
</table>

Example pattern:

<pre>
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
</pre>

== Separate Connections for Separate Workloads ==

Larger routines commonly use separate named connections so concurrent SQL channels do not collide.

<table class="wikitable">
<tr>
<th>Connection Name</th>
<th>Typical Use</th>
</tr>
<tr>
<td><code>C_SelectCon</code></td>
<td>SELECT queries and schema discovery.</td>
</tr>
<tr>
<td><code>C_InsertCon</code></td>
<td>INSERT operations.</td>
</tr>
<tr>
<td><code>C_UpdateCon</code></td>
<td>UPDATE operations.</td>
</tr>
<tr>
<td><code>C_DeleteCon</code></td>
<td>DELETE operations.</td>
</tr>
<tr>
<td><code>C_RetryCon</code></td>
<td>Retry path after transient SQL errors.</td>
</tr>
<tr>
<td><code>SetUpConn</code></td>
<td>Folder/setup stored procedures.</td>
</tr>
</table>

Example:

<pre>
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$)
</pre>

== Discovering SQL Table Structure ==

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

<pre>
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:
</pre>

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

== Incremental Processing Pattern ==

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

<pre>
! 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
</pre>

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

== DML Pattern: INSERT / UPDATE / DELETE ==

Data-changing SQL follows the same <code>OPEN</code> → <code>WRITE</code> → <code>CLOSE</code> pattern.

<pre>
LET Setenv("ER_Last_SQL", Insert_Sql$)

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

== Closing Named Connections ==

At the end of a SQL operation, close or clear configured connections.

<pre>
LET Fn_Close_Db_Connections(Sconn$)
LET Fn_Close_Db_Connections(Insconn$)
</pre>

Or clear all configured database connections:

<pre>
EXECUTE "CONFIG DATABASE CLEAR ALL"
</pre>

== Example Helper Entry Points ==

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

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

== Example Usage from a Program ==

<pre>
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")
</pre>

== Security Notes ==

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

== Troubleshooting Checklist ==

When a database connection fails:

<ol>
<li>Confirm the ODBC driver is installed on the machine running BR.</li>
<li>Confirm the driver name matches the connection string.</li>
<li>Confirm the SQL Server, MySQL, SQLite, or Access target is reachable.</li>
<li>Confirm credentials.</li>
<li>Confirm the database name.</li>
<li>Confirm firewall and network access.</li>
<li>Confirm the connection works in the Windows ODBC manager.</li>
<li>If using Windows authentication, confirm the BR process is running under the expected user context.</li>
<li>If using Client/Server mode, avoid <code>ODBC-MANAGER</code>.</li>
<li>Print <code>SYSERR$</code> for the driver-specific error message.</li>
</ol>

When a query fails:

<ol>
<li>Print or log the SQL statement.</li>
<li>Confirm table and column names.</li>
<li>Confirm quoting of strings and dates.</li>
<li>Confirm the number of parameter markers matches the number of values supplied to <code>WRITE</code>.</li>
<li>Confirm the <code>READ</code> form matches the selected columns.</li>
<li>Confirm character fields are wide enough.</li>
<li>Use <code>STATUS.DATABASE</code> metadata to inspect table and column definitions.</li>
</ol>

== See Also ==

<ul>
<li>[[CONFIG]]</li>
<li>[[EXECUTE]]</li>
<li>[[OPEN]]</li>
<li>[[WRITE]]</li>
<li>[[READ]]</li>
<li>[[CLOSE]]</li>
<li>[[Encrypted passwords]]</li>
<li>[[Unhex]]</li>
<li>[[Decrypt]]</li>
</ul>

[[Category:All Parameters]]

DATABASE

2026-07-06T16:53:01Z

GomezL: Replacement Documentation for Database.

= DATABASE =

Starting in '''Business Rules! 4.3''', the <code>CONFIG DATABASE</code> 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:

<ol>
<li><code>CONFIG DATABASE</code> names the connection.</li>
<li><code>OPEN</code> binds a BR file channel to a SQL statement.</li>
<li><code>WRITE</code> executes the SQL statement.</li>
<li><code>READ</code> fetches rows for a <code>SELECT</code>.</li>
<li><code>CLOSE</code> releases the statement/result set.</li>
</ol>

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

== Overview ==

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

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

Example:

<pre>
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:
</pre>

== CONFIG DATABASE ==

<code>CONFIG DATABASE</code> defines a named ODBC connection.

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

== Connection Methods ==

<code>CONFIG DATABASE</code> supports three connection methods:

<ul>
<li><code>DSN</code></li>
<li><code>CONNECTSTRING</code></li>
<li><code>ODBC-MANAGER</code></li>
</ul>

=== General Syntax ===

<pre>
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> } ]
</pre>

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

== DSN ==

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

=== Syntax ===

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

=== Example ===

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

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 <code>CONNECTSTRING</code> 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 ===

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

=== SQL Server with SQL Login ===

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

Where:

<table class="wikitable">
<tr>
<th>Value</th>
<th>Meaning</th>
</tr>
<tr>
<td><code>db-ref</code></td>
<td>BR database reference name.</td>
</tr>
<tr>
<td><code>server</code></td>
<td>SQL Server FQDN, host name, or IP address.</td>
</tr>
<tr>
<td><code>database</code></td>
<td>SQL Server database name.</td>
</tr>
<tr>
<td><code>username</code></td>
<td>SQL Server login.</td>
</tr>
<tr>
<td><code>password</code></td>
<td>SQL Server password.</td>
</tr>
</table>

=== SQL Server with Windows Authentication ===

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

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

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

=== Other Sample Connection Strings ===

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

== ODBC-MANAGER ==

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

=== Syntax ===

<pre>
CONFIG DATABASE <db-ref> ODBC-MANAGER
</pre>

=== Important Client/Server Note ===

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

=== Example ===

<pre>
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$
</pre>

== Viewing the Resolved Connection String ==

After a connection is configured, BR exposes the effective connection string through <code>Env$</code>.

<pre>
PRINT Env$("STATUS.DATABASE.[MyConn].CONNECTSTRING")
</pre>

Connection names in <code>STATUS.DATABASE</code> paths are generally uppercased by BR. For example:

<pre>
PRINT Env$("STATUS.DATABASE.[DEMOCONN].CONNECTSTRING")
</pre>

== OPENing a SQL Statement ==

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

=== Syntax ===

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

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

Example:

<pre>
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
</pre>

The SQL statement is bound at <code>OPEN</code> 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.

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

== Executing SQL with WRITE ==

A <code>WRITE</code> with no data list executes the SQL statement bound during <code>OPEN</code>.

<pre>
WRITE #DB_HANDLE:
</pre>

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

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

== Parameterized Statements ==

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

<pre>
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:
</pre>

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 <code>READ</code> fetches one result row.

=== Syntax ===

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

Example:

<pre>
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:
</pre>

Notes:

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

== Closing SQL Channels ==

Always close SQL channels when done.

<pre>
CLOSE #DB_HANDLE:
</pre>

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:

<pre>
EXECUTE "CONFIG DATABASE CLEAR MyConn"
</pre>

To clear all configured database connections:

<pre>
EXECUTE "CONFIG DATABASE CLEAR ALL"
</pre>

== Complete SELECT Example ==

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

<pre>
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
</pre>

== Windows Authentication Example ==

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

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

== INSERT Example ==

<pre>
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:
</pre>

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

Example:

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

== Error Handling ==

Use <code>ERROR</code> clauses or <code>ON ERROR</code> to trap connection, execution, and fetch failures.

Report:

<ul>
<li><code>ERR</code></li>
<li><code>LINE</code></li>
<li><code>SYSERR$</code></li>
</ul>

Example:

<pre>
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
</pre>

== Common SQL / ODBC Error Codes ==

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

First checks when a connection or query fails:

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

== STATUS.DATABASE Introspection ==

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

=== Resolved Connection String ===

<pre>
PRINT Env$("STATUS.DATABASE.[DEMOCONN].CONNECTSTRING")
</pre>

=== List Tables ===

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

=== List Columns ===

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

=== Column Metadata ===

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

Connection names in <code>STATUS.DATABASE</code> 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:

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

== Example Setup Pattern ==

A program may load supporting routines and configure a default SQL connection from environment variables.

<pre>
LIBRARY "SQL/Library": Fnopen_Sql_File
LIBRARY "Clsutil/Library": Fngethandle, Fnget_Form

LET Fn_Config_Sql("SetUpConn", Env$("SQL-SYNC-SERVER"), Env$("SQL-SYNC-DATABASE"))
</pre>

Common environment variables:

<table class="wikitable">
<tr>
<th>Variable</th>
<th>Purpose</th>
</tr>
<tr>
<td><code>SQL-SYNC-SERVER</code></td>
<td>SQL Server host name.</td>
</tr>
<tr>
<td><code>SQL-SYNC-DATABASE</code></td>
<td>Target SQL Server database.</td>
</tr>
<tr>
<td><code>SQL-SYNC-CONNECTION</code></td>
<td>Optional override connection string. May support a <code>[LOGIN_NAME]</code> placeholder.</td>
</tr>
<tr>
<td><code>SQL-SYNC-DRIVER</code></td>
<td>Optional ODBC driver name. Defaults to <code>SQL Server</code>.</td>
</tr>
</table>

== Connection Helper Pattern ==

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

Typical parameters:

<table class="wikitable">
<tr>
<th>Parameter</th>
<th>Purpose</th>
</tr>
<tr>
<td><code>Cs_Connection$</code></td>
<td>Arbitrary database reference name, such as <code>C_SelectCon</code> or <code>C_InsertCon</code>.</td>
</tr>
<tr>
<td><code>Cs_Server$</code></td>
<td>SQL Server host name.</td>
</tr>
<tr>
<td><code>Cs_Database$</code></td>
<td>SQL Server database name.</td>
</tr>
<tr>
<td><code>Cs_User$</code></td>
<td>SQL login. Often defaults to <code>LOGIN_NAME$</code>.</td>
</tr>
<tr>
<td><code>Password$</code></td>
<td>SQL password. Often defaults to <code>BR_PASSWORD</code> for Windows authentication.</td>
</tr>
<tr>
<td><code>Cs_Sql_Authentication</code></td>
<td>Indicates SQL authentication mode.</td>
</tr>
<tr>
<td><code>Cs_Driver$</code></td>
<td>ODBC driver name.</td>
</tr>
</table>

Example pattern:

<pre>
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
</pre>

== Separate Connections for Separate Workloads ==

Larger routines commonly use separate named connections so concurrent SQL channels do not collide.

<table class="wikitable">
<tr>
<th>Connection Name</th>
<th>Typical Use</th>
</tr>
<tr>
<td><code>C_SelectCon</code></td>
<td>SELECT queries and schema discovery.</td>
</tr>
<tr>
<td><code>C_InsertCon</code></td>
<td>INSERT operations.</td>
</tr>
<tr>
<td><code>C_UpdateCon</code></td>
<td>UPDATE operations.</td>
</tr>
<tr>
<td><code>C_DeleteCon</code></td>
<td>DELETE operations.</td>
</tr>
<tr>
<td><code>C_RetryCon</code></td>
<td>Retry path after transient SQL errors.</td>
</tr>
<tr>
<td><code>SetUpConn</code></td>
<td>Folder/setup stored procedures.</td>
</tr>
</table>

Example:

<pre>
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$)
</pre>

== Discovering SQL Table Structure ==

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

<pre>
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:
</pre>

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

== Incremental Processing Pattern ==

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

<pre>
! 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
</pre>

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

== DML Pattern: INSERT / UPDATE / DELETE ==

Data-changing SQL follows the same <code>OPEN</code> → <code>WRITE</code> → <code>CLOSE</code> pattern.

<pre>
LET Setenv("ER_Last_SQL", Insert_Sql$)

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

== Closing Named Connections ==

At the end of a SQL operation, close or clear configured connections.

<pre>
LET Fn_Close_Db_Connections(Sconn$)
LET Fn_Close_Db_Connections(Insconn$)
</pre>

Or clear all configured database connections:

<pre>
EXECUTE "CONFIG DATABASE CLEAR ALL"
</pre>

== Example Helper Entry Points ==

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

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

== Example Usage from a Program ==

<pre>
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")
</pre>

== Security Notes ==

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

== Troubleshooting Checklist ==

When a database connection fails:

<ol>
<li>Confirm the ODBC driver is installed on the machine running BR.</li>
<li>Confirm the driver name matches the connection string.</li>
<li>Confirm the SQL Server, MySQL, SQLite, or Access target is reachable.</li>
<li>Confirm credentials.</li>
<li>Confirm the database name.</li>
<li>Confirm firewall and network access.</li>
<li>Confirm the connection works in the Windows ODBC manager.</li>
<li>If using Windows authentication, confirm the BR process is running under the expected user context.</li>
<li>If using Client/Server mode, avoid <code>ODBC-MANAGER</code>.</li>
<li>Print <code>SYSERR$</code> for the driver-specific error message.</li>
</ol>

When a query fails:

<ol>
<li>Print or log the SQL statement.</li>
<li>Confirm table and column names.</li>
<li>Confirm quoting of strings and dates.</li>
<li>Confirm the number of parameter markers matches the number of values supplied to <code>WRITE</code>.</li>
<li>Confirm the <code>READ</code> form matches the selected columns.</li>
<li>Confirm character fields are wide enough.</li>
<li>Use <code>STATUS.DATABASE</code> metadata to inspect table and column definitions.</li>
</ol>

== See Also ==

<ul>
<li>[[CONFIG]]</li>
<li>[[EXECUTE]]</li>
<li>[[OPEN]]</li>
<li>[[WRITE]]</li>
<li>[[READ]]</li>
<li>[[CLOSE]]</li>
<li>[[Encrypted passwords]]</li>
<li>[[Unhex]]</li>
<li>[[Decrypt]]</li>
</ul>

[[Category:All Parameters]]

Index

2026-06-13T21:46:49Z

GomezL:

The '''Index''' [[command]] creates or recreates an index file. See the [[Index Facility]] for additional information. You may also find it useful to familiarize yourself with the concept of a [https://en.wikipedia.org/wiki/Index_(database) Database Index]

For a simple tutorial, see [[Index_and_Sort_facilities_Tutorial|Index Tutorial]].

==Syntax==
INDEX <data file> <[[index file]]> <key start position>[/...] <key length>[/...] [-<work path>] [{[[DUPKEYS]]|NATIVE|[[REORG]]|REPLACE|[[LISTDUPKEYS]][>[>]<output file>]}][...] [-N]

[[Image:Index.png|1000px]]

== Errors & Limits ==

=== Error 4123: Corrupted B-tree Index ===

This error may occur when a very large table, typically 500,000 records or more, is combined with a long or complex index.

In this situation, the index can become too large or too complex for the system to process reliably, resulting in:

<pre>
Error 4123 corrupted Btree Index
</pre>

'''Common cause:'''
* A large number of records
* A long index key
* A complex index expression
* A combination of the above

'''Typical threshold:'''
* Approximately 500,000 records or more, depending on the index design

'''Recommendation:'''
Review the index definition and simplify it where possible. Consider reducing the number of fields in the index, shortening indexed values, or using a less complex index expression.

==Comments and Examples==
Index works both in [[procedure files]] and as a separate [[:Category:Commands|command]]. It performs a [[CLEAR]] operation, except when a program is active, and creates or recreates the index file as specified.

The following command replaces INDEX.FIL, if it already exists, with a new index file for the master file MASTER.FIL. The key is a field that starts at position 10 and consists of 5 characters:

INDEX MASTER.FIL INDEX.FIL 10 5 REPLACE

The next command creates a new index file HIST.KEY for the master file HISTORY. The first key is a field that starts at position 1 and consists of 4 characters. The second key starts at position 10 and has 2 characters, and the third field starts at position 20 and has 2 characters:

INDEX HISTORY HIST.KEY 1/10/20 4/2/2

In the following example, INDEX reorganizes the existing CUST.KEY index file. However, if CUST.KEY does not exist, a new index file by the same name is built. The REORG parameter greatly speeds up the INDEX command because it takes advantage of the part of the key file that is already in sorted order. The first key field starts at position 1 and consists of 6 characters. The second key field starts at position 4 and consists of 3 characters. '''Note''' that the key fields overlap.

INDEX CUSTOMER CUST.KEY 1/4 6/3 REORG

The next example builds a new index file called PAY.KEY for the PAYROLL master file. The key field starts at position 1 and consists of 6 characters. This command allows duplicate keys, and it sends a listing of duplicate keys to the file DUP. No warning error will occur if duplicate keys are encountered. '''Note''' that the parameters in this Index command are separated by commas rather than by spaces:

INDEX PAYROLL,PAY.KEY,1,6,DUPKEYS,LISTDUPKEYS >DUP

Also note that the creation of indexes can be interrupted with Ctrl-A, and can then be resumed with GO.

==Defaults==
# Use space on the current default drive and directory as temporary work space.
# Display a record of duplicate keys on the screen.
# Send an error if duplicate keys are found; use the collating sequence specified in the BRConfig.sys file (or by the most recent CONFIG command); completely build (or rebuild) the specified index; send an error message and abort the index procedure if the specified index file already exists.

==Parameters==
The '''data file name''' parameter specifies the name of the data file for the index you wish to create.

'''Index file name''' specifies the name of the index file for the desired index.

'''1st key start position''' and '''next key start position''' specify the starting record positions of each key field. Up to '''six''' starting positions may be specified.

'''1st key length''' and '''next key length''' parameters indicate the number of characters contained in each key field. Each '''key length''' specification must correlate to a '''key starting position''' specification. The specifications match up according to ordinal position. The first key starting pos correlates to the first key length specification.

The indexing process requires availability of a temporary work space. The optional '''work path''' parameter specifies a location somewhere other than the default drive and directory for this work space.

If you decide to allow duplicate keys in the index, you can use '''DUPKEYS''' to prevent the corresponding error message 7603 from appearing on the screen.

As the index is being created, '''LISTDUPKEYS''' lists all duplicate keys on the screen.

'''>Redirected output File''' saves LISTDUPKEYS information that would normally be displayed on the screen to a text file.
'''>>Redirected output File''' appends LISTDUPKEYS information to an existing text file.
Programs can use input from this file to report on or delete the duplicate keys.

'''NATIVE''' causes the Index command to use native collating sequence. This parameter should be used when the following two conditions are true: an index is being performed on a packed decimal field, and COLLATE ALTERNATE has been specified for the rest of the program.

'''REORG''' can be used to reorganize index files directly, without reading the master file. This can greatly speed up the re-indexing process, especially on network systems, because the already sorted portion of the index file will not be sorted again. If the index file has already been fully sorted, BR will read only the header record and then quit the re-indexing process immediately.

It is important to note that the REORG parameter will generally not cause new records to be added to the index file. It is the responsibility of the programmer to be sure that index file's overflow area is updated with added records before REORG performs its operation. This can be done by always opening the index file when writing to the master file.

There are three cases in which INDEX with the REORG parameter will completely build an index file:
# When the index file does not exist.
# When the specified file is not an index file.
# When any of the key starting pos or key length parameters stored in the header record are different from those specified in the INDEX REORG command.

'''REPLACE''' specifies that the current index should replace an index of the same name if it exists on the disk. BR expects to create a new file when this parameter is not specified.

The '''-N''' option suppresses all information that would normally be output (except LISTDUPKEYS information).

==BADKEYS==
{{:Badkeys}}

==Special Considerations==

The Index command may be executed in a program as part of the [[EXECUTE]] statement. If a program is active, this method of running the INDEX command does not CLEAR memory.

''When the starting key position and key length parameters are both specified as 0, INDEX will now use the key parameters as they exist in the key file.'' The REPLACE parameter is assumed.

The INDEX command may now include a SHR parameter. This allows another workstation to access the master file while indexing. For example:

INDEX master.fil index.fil 1 4 REPLACE SHR

'''Note''' that SHR should be used only for making temporary indexes because master files normally should not be modified when rebuilding indexes. When INDEX is used with the SHR parameter, all records in the master file can be accessed by the INDEX command, even if a record is locked by another user.

For ''case insensitive key file access'' place a U after the key length or key starting position parameters. This causes the program to keep that key field as uppercase. Whenever a record is written, the key field is converted to uppercase before it is written to the key file (master record is unchanged) and whenever the file is read with KEY=, the parts of the key that need to be converted to uppercase are converted before the key look up.

This is useful for looking up names, where the capitalization of the data on file and the key entered by the operator may be inconsistent. This also permits sequential processing of a keyed file in alpha order without regard to the case of the data. '''Note''' that even though BR stores the key as uppercase, the resulting processing occurs as if the case is ignored. In other words, 'A' and 'a' are considered equal.

The following code writes data as is to the master file, but writes all uppercase characters to the key file.

00010 OPEN #1:"name=XXX,kfname=XXX.key,replace,recl=63,kps=1,kln=4u",INTERNAL,OUTIN,KEYED
00020 WRITE #1,USING 30: "aaAA"
00030 FORM C 4
00040 READ #1,USING 30,KEY="AAaa": X$
00050 PRINT X$

INDEX MASTER.FIL KEY.FIL 1/3 2/2U REPLACE

If a key field is a two digit year it should be flagged as such so BR will use [[BaseYear]] when comparing values for sorting purposes. Place the letter Y after either the field position or its length.

INDEX MASTER.FIL KEY.FIL 1/3 2/2Y REPLACE

If a two digit year is stored in binary or packed decimal form, the INDEX command needs to indicate that by following the Y with either P or D as in:

INDEX MASTER.FIL KEY.FIL 1/3 2/2YB REPLACE

The INDEX command defaults to B-tree indexing unless OPTION 5 is used in the BRConfig.sys file to set the universal default to ISAM or the ISAM parameter is used within the syntax of the INDEX command to override the B-tree default on an individual basis. If OPTION 5 is specified in BRConfig.sys, the INDEX command's BTREE parameter can be used to override the ISAM default on an individual basis.

;INDEX will completely rebuild the [[B-tree]] index when any of the following three conditions is true:
:1) When the REPLACE parameter is specified in the INDEX command,
:2) When INDEX REORG is specified and the key starting positions and/or lengths in the existing index file are different than those specified on the INDEX command, or
:3) When INDEX REORG is specified and the key file does not exist.

The syntax of the INDEX command allows the BTREE or ISAM parameters, as follows:

INDEX file.dat,file.idx,1,2,REPLACE,ISAM
INDEX file.dat,file.idx,1,2,REPLACE,BTREE

<noinclude>
[[Category:Commands]]
[[Category:Facility Commands]]
</noinclude>

Encryption

2026-04-03T03:20:11Z

GomezL: Document Status Encrypt

(As of 4.30)

'''Encryption''' encompasses a number of different operations. These operations can be used independently or in combination to meet different needs. We use industry standard encryption available through OpenSSL. Three technologies are widely used for encrypting data. BR supports the first two listed below through its [[ENCRYPT]] and [[DECRYPT]] [[internal functions]].

==Overview of Two Encryption Methods==

1. '''Symmetric key ciphers''' – where the same key is used to encrypt and decrypt data. You can specify a key to encrypt some data and later use the same key to decrypt the data.

2. '''Hashing routines''' – one way routines that take data and convert it to a hash value. Sometimes these are thought of as checksums such as MD5 sum. Hash values are always the same length regardless of how big the hashed data is. A 10 gb file will have a hash result that is the same length as a 200 byte file. Hashing routines have a number of specific uses, including:
*Verify that data has not changed.
*Verifying that two files are the same.
*Validating passwords – this is based on the concept that if two values have the same hash value the values are equal. Using this technique improves security because it allows a server to store passwords in an unrecoverable format. Even the server software is unable to regenerate the original password. It is only capable of checking if the hash of a password matches the stored password hash.

==Symmetric Key Ciphers==
There are two encryption functions in the BR language, [[ENCRYPT$]] and [[DECRYPT$]].

ENCRYPT$(Data$ [,Key$ [,Encryption-type$ [,Initialization-vector$]]])
DECRYPT$(Data$ [,Key$ [,Encryption-type$ [,Initialization-vector$]]])

Data$ - The data to be encrypted

Key$ - The secret key to be used for encryption. If not specified this value will come from an OPTION 66 statement.

Encryption-type$ - The type of encryption to be done. If not specified, a common high strength encryption type will be employed. This is described in more detail below, but is generally only useful for interfacing with other typically non-BR programs.

Initialization-vector$ - This is an arcane part of encryption standards. It exists to prevent attackers from being able to tell whether the unencrypted data has changed. This is described in more detail below, but is general only needed when interfacing with other non-BR programs.

== Interfacing With Other Programs (encryption type and initialization vector)==
There are a number of different types of encryption that BR supports through OpenSSL: AES, BLOWFISH, DES, triple DES, RC4 and RC2. Most symmetric key ciphers are block ciphers meaning that they encrypt one block at a time. This means if you have a bit message, it is broken up into multiple blocks and each block is encrypted. The block size can be set as (128, 192, 256) bits. Some encryption types don't support all of these values so STATUS ENCRYPTION should be checked to see what encryption types are available in BR. Besides block size, there are also various schemes for blocking data. One might expect that using 256 bit blocking would simply take every 32 bytes and call it a block. This is not done though because there is a possibility that this would cause patterns in the encrypted data. To prevent this, there are various schemes known as codebooks which change the way data is blocked. Wikipedia explains this in more detail. If the encryption type is not specified AES:256:CBC:128 will be used. To be compatible with other programs the entire encryption type must be specified (cipher: key length: codebook: initialization vector length).

Initialization-vector – this is used to cause the same data encrypted with the same key to have a different encrypted result. This is significant because otherwise an attacker looking at data seeing the same encrypted result twice would know that the key and the unencrypted data have not changed. Regardless of whether or not you are concerned about this potential security issue, the standard encryption methods require this value so interfacing with other programs may require you to use it. It is a common practice to use a random number for this value and store the value at the beginning of (ahead of) the encrypted result. This is what BR does if this parameter is omitted.

As an example:
ENCRYPT$(“test”,“key”)
Produces a string containing “random number initialization vector”&”encrypted result”.

If the initialization vector is explicitly specified as in:
ENCRYPT$(“test”,“key”,“AES:256:CBC:128”,“RANDOM”)
the result would be simply “encrypted result”.

DECRYPT$ has the same arguments as ENCRYPT$ with the exception of the first parameter which is the encrypted data. DECRYPT$ expects to be used with the same key$, encryption-type$, and initialization-vector$ as was used to encrypt the data. As with ENCRYPT$, if key$ is not specified, the value from the OPTION statement will be used. If encryption-type$ is not specified, “AES:256:CBC:128” will be used. If the initialization vector is not specified, it will be assumed that the encrypted data starts with an initialization vector.

==Hashing Routines==
Three common forms of hashing are allowed in BR. They are MD5, SHA, and SHA-1. These are also provided through the ENCRYPT$ function specifying a null key$ value:

ENCRYPT$(data$, “”, “MD5”) ENCRYPT$(data$, “”, “SHA”) ENCRYPT$(data$, “”, “SHA-1”)

Hashing is also referred to as Message Digests or digests. This is what the MD in MD5 means. There is no way to restore data that has been hashed. Hashing is a one way function so DECRYPT$ will yield an error.

==Asymmetric Encryption==
Asymmetric key encryption is also known as public/private key encryption.

Public/private keys are created as a pair by a key generator. They are a pair, and it is not possible to have two public keys for the same private key or vice versa. With regard to public/private key pairs, what one key encrypts the other key can decrypt, and neither key can decrypt what it has encrypted. When a private key is used to encrypt data, the result is called a signature because everyone who has the public key can decrypt it.

This technique is used for:

Signing (using certificates) – A private key can be used to sign data. The result of such signing can be tested/validated with the corresponding public key.

Data encryption – A public key can be used to encrypt data. This data can then only be decrypted by the corresponding private key.

Hashes and signing are different but used together. Rather than signing a large block of data which would create a large signature, only the hash is signed to create much smaller fixed length signature data. When verifying a large block of signed data, the data is used to create a hash value and the hash value is compared to a decrypted signature.

Asymmetric encryption is not accessible through the BR ENCRYPT$, DECRYPT$ functions. However, it is used by our SSL client server connections and HTTPS. Certificates are most commonly used by SSL and HTTPS and are less useful for other application processes.

In the Client Server model the client knows the server’s public key and the server uses its private key to encrypt and decrypt. BRclient.exe connects to BRListener.exe by opening an SSL socket on the server using DHE_RSA-AES256-SHA Encryption. Handshaking is performed using a private key stored on the server within BRListener. Once authenticated, the socket is then passed to BRServer.exe for actual data processing.

Encryption is invoked by Business Rules HTTP support as follows:

CONFIG HTTPS port-number [ LOG file-pathname ] [CERT= cert-file-basename]
CONFIG OPTION 66 private-key-file-encryption-password
OPEN #400: “HTTP=SERVER”, DISPLAY, OUTIN

The BRSERVER executable directory must contain two files:

https-private.pem
https-cert.pem

These files are made by the following commands under Linux, MAC and cygwin for Windows: openssl req -new -x509 -out httpserver.pem -days 10000

(this will prompt for the OPTION 66 password)

mv privkey.pem https-private.pem
mv httpserver.pem https-cert.pem

This port specific service can then be accessed with browsers. When the specified port is accessed through a browser, BR establishes an HTTPS connection rather than an HTTP connection.

==Signing and Certificate Processing Industry Standards==
The purpose of certificates is to verify the authenticity of unencrypted data.

Certain companies are authorized by the government to act as a Certificate Authority (CA). These companies (e.g. Verisign) issue electronic certificates which can be used to issue second level certificates. Certificates can have expiration dates and the line of authority extends from a CA to any number of levels (but a chain is only as strong as its weakest link). Certificates contain a list of signatures (described below) that trace back to a CA as follows:

When a company needs to obtain a certificate from a CA, it prepares its own certificate and sends it to the CA for signature. Creating a certificate requires the pre-production of a private and public key pair. The certificate text properly identifies the signing authority, the owner of the certificate, and the owner’s public key. The signing authority externally verifies the identity of the owner before signing it. The CA provides the signature and the CA’s own public key for validating it.

Browsers know the CA identities and their public keys. A browser can verify a CA signed cert by hashing it, decrypting the signature with the known public key and matching the decrypted signature against the hash total.

Any company that is issued a (self-prepared authority signed) certificate by a CA can sign second level certificates issued to third parties. A second level cert contains the parent (CA issued) certificate, and includes the public keys of the issuer and the recipient. The signature is essentially the encrypted hash total of ‘itself plus all of its ancestors’. Additional levels each contain the chain of certificates leading from a CA issued cert to itself. By including public keys along with the identities of the owners, certificates become tools for validating the signatures of their owners. When signed data (with an encrypted hash total) is sent to clients the signatures insure that the data has not been altered along the way.

Ostensibly, a certificate cannot be counterfeited because it requires the signature of its parent which can only be produced with its parent’s private key. By providing both public keys ( signer and recipient ) in all certs along with signatures, a non-forgeable or alterable chain is established.

====Example:====
CA public key – known to browser
certificate 1 (signed by CA – contains owner’s public key)
certificate 2 (signed by second level - includes certificate 1 - contains owner’s public key)
final certificate (signed by third level - includes certificate 2 - contains owner’s public key)

A browser would find the CA information in the certificate and check to see if it is in the browser’s internal list. If not, it fails. Then it verifies that certificate 1 (which ends up being part of the final certificate) has been signed by the CA. After that it checks certificate 2 and verifies that it has been signed by the owner of certificate 1. Finally it checks the final certificate and verifies that it has been signed by the owner of certificate 2.

To assure the line authority of any certificate, the public keys are associated with signers, not with documents.

=='''Encryption Support by Version'''==

BR encryption support has evolved over time. The sections below distinguish between currently supported legacy algorithms, deprecated (removed) algorithms, and newer additions.

==Old List (Still Supported)==

The following algorithms are supported for backward compatibility and remain available in current versions:

===Symmetric Key Ciphers===
<pre>
DES (all modes)
DES-EDE / DES-EDE3 (Triple DES)
RC4 (40, 128)
RC2 (40, 64, 128)
BF (Blowfish)
CAST5
AES:128 / 192 / 256 (ECB, CBC, CFB, OFB)
IDEA
CAMELLIA:128 / 192 / 256
SEED
</pre>

===Message Digest Algorithms===
<pre>
MD5
SHA-1
MDC-2
RIPEMD-160
</pre>

==Deprecated (Removed / No Longer Available)==

The following algorithms were available in earlier versions but are no longer exposed in current builds:

<pre>
SHA (original / alias)
DSS
DSS-1
</pre>

These were removed due to obsolescence or lack of modern security relevance.

==In Development (Newer Additions)==

===Added in Version 4.31hdg===
<pre>
SHA-256
</pre>

==STATUS ENCRYPTION==

The command:

<pre>
STATUS ENCRYPTION
</pre>

displays the encryption and digest algorithms supported by the currently running BR executable. This is the authoritative source for determining which algorithms are available.

==Notes==

* Default encryption:
<code>AES:256:CBC:128</code>
* If no initialization vector is provided, BR prepends a random IV to the encrypted output.
* DECRYPT$ must use the same parameters as ENCRYPT$.

==Wishlist: Future Encryption Types==

The following modern algorithms are supported by OpenSSL but not currently exposed in BR:

* AES:128:GCM:96
* AES:192:GCM:96
* AES:256:GCM:96
* HMAC-SHA-256
* HMAC-SHA-512
* SHA-512
* ChaCha20-Poly1305
* PBKDF2 (password hashing)

<noinclude>
[[Category:Definitions]]
</noinclude>

MAX SORT MEMORY

2026-03-22T19:28:03Z

GomezL: /* Notes */

This [[CONFIG]] option sets the maximum memory allowed for SORT and INDEX operations ''(in megabytes)''. This is a [[BRconfig.sys]] statement.

=== Syntax ===

<pre>
MAX_SORT_MEMORY <integer> MB
</pre>

[[File:msm.png|400px]]

== Description ==

Specifies the maximum number of megabytes BR should use for sorting and indexing.

* Default: 8 MB
* Allowed range: 2–512 MB

== Example ==

<pre>
CONFIG MAX_SORT_MEMORY 512 MB
</pre>

== Notes ==

* The ``MB`` suffix is required after the integer value.
* This setting is a balance between CPU / Hard Disk Speed and available memory.
* Do not assume that bigger is better.
* 64 MB may be the sweet spot for your system.

<noinclude>
[[Category:Config]]
</noinclude>

MAX SORT MEMORY

2026-03-22T17:10:53Z

GomezL:

MAX SORT MEMORY

2026-03-22T17:09:26Z

GomezL: Document required MB and allowed values

This [[CONFIG]] option sets the maximum memory allowed for SORT and INDEX operations ''(in megabytes)''. This is a [[BRconfig.sys]] statement.

== Syntax ==

<pre>
MAX_SORT_MEMORY <integer> MB
</pre>

[[File:msm.png|400px]]

== Description ==

Specifies the maximum number of megabytes BR should use for sorting and indexing.

* Default: 8 MB
* Allowed range: 2–512 MB

== Example ==

<pre>
CONFIG MAX_SORT_MEMORY 512 MB
</pre>

== Notes ==

* The ``MB`` suffix is required after the integer value.

<noinclude>
[[Category:Config]]
</noinclude>

^KeyStroke and ^DataChg

2026-02-05T19:46:32Z

GomezL: Show Sample for ^DataCHG

As of [[4.30]], two new [[INPUT FIELDS]] field attributes are now supported which provide detailed control of user input:
*^KEYSTROKE - return control to the program when a keystroke is entered
*^DATACHG - return control to the program when data is changed
(a subset of ^KEYSTROKE)

When control is returned to a program as a result of these attributes, one of three new [[Fkey]] values is applied:

*140 - a character was appended to the data
*141 - the data changed other than appending a character
*142 - ( applies only to ^KEYSTROKE ) the data is unchanged but a key was pressed or a mouse action occurred that did not trigger a field exit

When a key is pressed with ^KEYSTROKE or ^DATACHG that sets a 140, 141 or 142 FKEY, it will also set the [[KStat$]] value. This allows the program to see what key was pressed. This KSTAT$ value will be cleared on the next [[RINPUT Fields]] operation.

If a navigation key operates within the field and ^KEYSTROKE is specified, it triggers fkey 142. If the navigation key causes focus to leave the field, fkeys 140-142 are not generated and KSTAT is not set.

===Sample===
00001 PRINT Newpage
00010 INPUT FIELDS "10,10,C 10,[D]S^DataCHGC"&Str$(New_Pos),ATTR '[A]': Dummy$
00020 PRINT FIELDS "1,70,n 10": Fkey
00025 LET New_Field$=Kstat$
00026 LET New_Pos=Curpos
00030 PRINT FIELDS "3,70,N 10": New_Pos
00040 PRINT FIELDS "4,70,C 10": Unhex$(New_Field$)&" "&New_Field$
00050 PRINT FIELDS "5,70,C 10": Dummy$
00090 LET Curfld(Curfld,Fkey) !:
GOTO 10

The program triggers ^DATACHG and displays the FKEY along with the value that was pushed to KSTAT$. Each keystrokeis processed. The input allows granular control.

;Note
While shift, control and alt do not change either the functionality or fkey value of navigation keys, KSTAT$ produces unique values for each of these settings. This complicates things a bit but provides more options for programmed responses to custom key combinations.

<noinclude>
[[Category:All Parameters]]
</noinclude>

DEBUG PROFILE

2025-09-12T17:23:21Z

GomezL: /* Additional resources */

= BR Profiler Main Page =

Use '''PROFILER.EXE''' to translate the BR Profiler output file into readable text.

== Generating profiler output ==

<pre>
DEBUG PROFILE SAMPLED filename
DEBUG PROFILE TIMED filename
DEBUG PROFILE STOP
</pre>

== Viewing profiler output ==
Use '''profiler.exe''' to view the contents of a log file:

<pre>
profiler.exe filename
profiler.exe filename raw
</pre>

== Additional resources ==

* See the FTP site: ''Dll_Distr/Profiler'' for samples and documentation.
* [[Profiler File Layout]] — Detailed description of the profiler log file format.

<noinclude>
[[Category:Debug]]
[[Category:Profiler]]
[[Category:Tools]]
</noinclude>

DEBUG PROFILE

2025-09-12T17:22:55Z

GomezL:

= BR Profiler Main Page =

Use '''PROFILER.EXE''' to translate the BR Profiler output file into readable text.

== Generating profiler output ==

<pre>
DEBUG PROFILE SAMPLED filename
DEBUG PROFILE TIMED filename
DEBUG PROFILE STOP
</pre>

== Viewing profiler output ==
Use '''profiler.exe''' to view the contents of a log file:

<pre>
profiler.exe filename
profiler.exe filename raw
</pre>

== Additional resources ==

* See the FTP site: ''Dll\_Distr/Profiler'' for samples and documentation.
* [[Profiler File Layout]] — Detailed description of the profiler log file format.

<noinclude>
[[Category:Debug]]
[[Category:Profiler]]
[[Category:Tools]]
</noinclude>

Profiler File Layout

2025-09-12T17:14:40Z

GomezL:

= Profiler Output File Format =

The profiler output file will be a sequence of variable-length records of various types. The first byte of each record provides the record type, while the length and record information are record-type specific. All numbers are in '''network byte order'''.

== CREATE MODULE MAPPING ==

* '''Byte 1:''' 1
* '''Byte 2–3:''' 16-bit module number
* '''Byte 4–5:''' 16-bit File Name Length
* '''Byte 6–end:''' File Name

== CURRENT LINE ==
Current line information begins with this record and ends with an '''END CURRENT LINE''' record.

* '''Byte 1:''' 3
* '''Byte 2–3:''' module number
* '''Byte 4–7:''' line number
* '''Byte 8:''' clause number

== TIME SPENT IN LINE ==
These records exist only for timed sampling. They always occur between '''CURRENT LINE''' and '''END CURRENT LINE'''.

* '''Byte 1:''' 4
* '''Byte 2–9:''' 8-byte time spent in line (nanoseconds)

== BACKTRACE INFORMATION ==
These records may or may not exist depending on creation options. They always occur between '''CURRENT LINE''' and '''END CURRENT LINE'''.

Note: Apart from the record type identifier, these records have the same format as '''CURRENT LINE'''.

* '''Byte 1:''' 5
* '''Byte 2–3:''' module number
* '''Byte 4–7:''' line number
* '''Byte 8:''' clause number

== FUNCTION NAME ==
These records may or may not exist depending on creation options. They follow immediately after either '''CURRENT LINE''' or '''BACKTRACE INFORMATION'''.

* '''Byte 1:''' 7
* '''Byte 2:''' 8-bit name length
* '''Byte 3–on:''' function name

== GOSUB ==
These records may or may not exist depending on creation options. They follow immediately after either '''CURRENT LINE''' or '''BACKTRACE INFORMATION'''.

* '''Byte 1:''' 8

== MAIN ROUTINE ==
These records may or may not exist depending on creation options. They follow immediately after either '''CURRENT LINE''' or '''BACKTRACE INFORMATION'''.

This indicates that the given line is neither in a GOSUB nor in a function body.

* '''Byte 1:''' 9

== END CURRENT LINE ==

* '''Byte 1:''' 6

= Potential Syntax =

* <code>DEBUG PROFILE SAMPLED filename</code>
* <code>DEBUG PROFILE TIMED filename</code>
* <code>DEBUG PROFILE STOP</code>

== Profiler.exe ==
To use the profiler.exe available on the ftp site in /Dll_Distr/profiler to get a nice listing of what is in the log file use.
* profiler.exe filename
or
* profiler.exe filename raw

Profiler File Layout

2025-09-12T17:10:42Z

GomezL:

Profiler File Layout

2025-09-12T17:01:59Z

GomezL: Created page with "The profiler output file will be a sequence of variable length records of various types. The first byte of each record will provide the record type, the length and record information will be record type specific. All numbers are network byte order. CREATE MODULE MAPPING Byte 1: 1 Byte 2-3: 16 bit module number Byte 4-5: 16 bit File Name Length Byte 6-end: File Name CURRENT LINE Current line information is started with this record and will be ended with a END CURRENT..."

The profiler output file will be a sequence of variable length records of various types. The first byte of each record will provide the record type, the length and record information will be record type specific. All numbers are network byte order.

CREATE MODULE MAPPING
Byte 1: 1
Byte 2-3: 16 bit module number
Byte 4-5: 16 bit File Name Length
Byte 6-end: File Name

CURRENT LINE
Current line information is started with this record and will be ended with a END CURRENT LINE
record
Byte 1: 3
Byte 2-3: module number
Byte 4-7: line number
Byte 8: clause number

TIME SPENT IN LINE
these records will only exist for timed sampling
they will always occur between CURRENT LINE and END CURRENT LINE
Byte 1: 4
Byte 2 – 9: 8 bit time spent in line in nano-seconds.

BACKTRACE INFORMATION
these records may or may not exist depending on creation options
they will always occur between CURRENT LINE and END CURRENT LINE
Note: besides the record type identifier, these records have the same format as CURRENT LINE
Byte 1: 5
Byte 2-3: module number
Byte 4-7: line number
Byte 8: clause number

FUNCTION NAME:
These records may or may not exist depending on creation options.
These records will follow immediately after either CURRENT LINE or BACKTRACE INFORMATION
Byte 1:7
Byte 2: 8-bit name length
Byte 3- on: function name

GOSUB:
These records may or may not exist depending on creation options.
These records will follow immediately after either CURRENT LINE or BACKTRACE INFORMATION
Byte 1:8

MAIN ROUTINE:
These records may or may not exist depending on creation options.
These records will follow immediately after either CURRENT LINE or BACKTRACE INFORMATION
This indicates that the given line is neither in a GOSUB or in a function body.
Byte 1:9

END CURRENT LINE
Byte 1: 6

Potential syntax

DEBUG PROFILE SAMPLED filename
DEBUG PROFILE TIMED filename
DEBUG PROFILE STOP

DEBUG PROFILE

2025-09-12T17:01:43Z

GomezL:

Use PROFILER.EXE to translate the BR Profiler output file into readable text.

To generate profiler output:
DEBUG PROFILE SAMPLED filename
or
DEBUG PROFILE TIMED filename
and
DEBUG PROFILE STOP to end the profile

Also, to use the profiler.exe to get a nice listing of what is in the log file.
profiler.exe filename
or
profiler.exe filename raw

See ftp site Dll_Distr/Profiler for samples and documentation.

[[Profiler File Layout]] -- Details about the profiler log

<noinclude>
[[Category:Debug]]
</noinclude>

DEBUG PROFILE

2025-09-12T17:00:26Z

GomezL:

DEBUG PROFILE

2025-09-12T16:57:29Z

GomezL: Created page with " Use PROFILER.EXE to translate the BR Profiler output file into readable text. To generate profiler output: DEBUG PROFILE SAMPLED filename or DEBUG PROFILE TIMED filename and DEBUG PROFILE STOP to end the profile Also, to use the profiler.exe to get a nice listing of what is in the log file. profiler.exe filename or profiler.exe filename raw See ftp site Dll_Distr/Profiler for samples and documentation."

Debug

2025-09-12T16:56:13Z

GomezL:

see also: [[BR32.exe Debug]]

There are many [[commands]], [[statements]], [[debuggers]], etc that will help you to '''debug''' a [[Business Rules!]] [[program]].

Some [[editors]] such as [[MyEdit (BR Edition)]] provide an vast array of their own tools for use in debugging.

Debug versions of BR32.exe are used almost exclusively during the [[beta]] process of upcoming versions of Business Rules!.

Other than functions in the [[:Category:Debug|debug category]], the [[Go]] [[command]] can be very useful for debugging programs

The '''DEBUG CONSOLE OFF''' command is supported as of BR [[4.18a]].

[[DEBUG PROFILE]] -- Used to create Itemized Profile log file for troubleshooting performance.

<noinclude>
[[Category:Debug]]
[[Category:Needs Help]]
</noinclude>

-202,020,202.02

2025-06-05T12:57:41Z

GomezL:

[[PD]] is a Packed Decimal.

PD 6.2 can store +/ 999,999,999.99

'''-202,020,202.02''' is the value read from an empty, never written, [[PD]] '''6.2''' field.

[[Category:Number]]

SQL

2025-05-23T15:48:50Z

GomezL:

SQL manages data via a relational data management system. As of 4.3, BR includes several special ways of working together with SQL.

====CONFIG DATABASE db-ref ODBC-MANAGER====
CONFIG [[DATABASE]] db-ref ODBC-MANAGER will invoke the ODBC Manager to define or identify a file DSN. Once this is done you can issue the command [[STATUS]] DATABASE –P to see the connect string that the ODBC Manager used to make the connection. Thereafter you can use that connection string to establish the connection to the database as follows:

CONFIG DATABASE db-ref CONNECTSTRING="Driver={Microsoft Access Driver (*.mdb)}DBQ=C:\inetpub\wwwroot\BegASP\Chapter.14\Contact.mdb"
- or -
CONFIG DATABASE db-ref DSN=’dsn-ref ‘

;Additional Optional Parameters:

[, USER= department | LOGIN_NAME | ? ]
[, {PASSWORD= dept-password | PASSWORDD=encrypted-passwd | BR_PASSWORD | ? ]

Where '''?''' indicates prompt and '''BR_PASSWORD''' indicates the password used during client login. If running the standard model (not client-server) then this is equivalent to "?". '''encrypted-passwd''' is the DB password encrypted with the key stated in OPTION 66.

'''CONFIG DATABASE MAX_COLUMN_WIDTH nnnn'''
Some database column types are variable length. SQL requires advanced buffer allocation. This would unnecessarily tax the system if not restricted. This statement sets a maximum width for all columns. Memory is allocated to the minimum of (this amount or the column width). "nnnn" is the maximum column width. The default is 2000 characters.

''''CONFIG DATABASE CLEAR { db-ref | ALL }'''
This will close the specified database or all open databases.

'''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;Login Name=username;Password=BR_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]
Note that BR_PASSWORD will use the users Active Directory password to connect to the SQL Server.
Note that "SQL Server" is one of several choices for SQL Server, another choice would be SQL Server Native Client 11.0

====OPEN statements====

OPEN #20: "DATABASE= db-ref", SQL "sql-statement", OUTIN
- or -
OPEN #20: "DATABASE= db-ref, Name= filename" , SQL, OUTIN
Where filename refers to a DISPLAY file containing a SQL statement that gets executed when a WRITE statement is processed.

Example:
OPEN #20: "DATABASE=MyData",SQL "SELECT FILENO,BALANCE from MASTER WHERE FILENO=?",OUTIN

====Sequence of Operations====
#Begin processing with a WRITE statement.
#If the WRITE contains an IO list of values then it is used to populate the SQL before it is executed. In this process IO list values replace question marks positionally from left to right. These question marks only work with SQL arguments that refer to stored data. Question marks cannot be specified where SQL keywords or table column names appear.
#Resulting SQL may or may not produce a result set. If it does, the result set may be processed like a BR file opened RELATIVE. Some operations that use file positioning may be slow since the whole result set may not be in memory immediately. Simple sequential access should be fairly quick.
#Once SQL has been populated by an IOlist, it may be reused with the same values by issuing a WRITE with no IOlist.
#READ IOlist variable associations are positional with each READ accessing one row of values.

===SQL read/write example===

00001 execute "CONFIG database Inventory connectstring=""DRIVER=SQL Server;SERVER=COMPUTER;Initial Catalog=Inventory;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True"""
00002 let sqlconnection = 1
00003 ! READ test
00004 open #sqlconnection: "DATABASE=Inventory",SQL "SELECT [Name],[Address],[City],[Country],[DateOfBirth],[Age] FROM [Inventory].[dbo].[Customer]",OUTIN
00005 write #sqlconnection:
00006 do
00007 read #sqlconnection, using 'form POS 1,C 50,C 50,C 50,C 50,C 50,N 3': Name$,Address$,City$,Country$,DateOfBirth$,Age eof ignore
00008 ! if EOF was encountered, then we are finished reading the result set
00009 if file(sqlconnection)<>0 then exit do
00010 let dob$ = ""
00011 ! only convert SQL date to BR date if it is non-empty, otherwise BR_DATE$ bombs
00012 if DateOfBirth$<>"" then
00013 let dob$ = BR_DATE$(DateOfBirth$)
00014 end if
00015 ! show user the next result row
00016 print Name$,Address$,City$,Country$,dob$,Age
00017 loop
00018 close #sqlconnection:
00019 !
00020 ! WRITE test
00021 open #sqlconnection: "DATABASE=Inventory",SQL "insert into [Inventory].[dbo].[Customer] ([Id],[Name],[Address],[City],[Country],[DateOfBirth],[Age]) values (6,'Tom, Jerryngton', '123 Big Walkway', 'Dallas', 'USA', '1982-01-01', 35);",OUTIN
00022 write #sqlconnection:
00023 close #sqlconnection:

===SQL Date Format Functions===

A$=SQL_DATE$(BR-date-string,"format-mask") ! format date for storage
B$=BR_DATE$(SQL-date-string,"format-mask") ! unpack DB date value

* Note: SQL DATETIME fields are "Packed for Storage", but "DATE" fields are returned as a CCYY-MM-DD string in a SQL Query. DATE returns "BLANK" for "Empty or Null Date"

===SQL Table Interrogation===

'''ENV$(STATUS)''' has been extended to interrogate database connections and ODBC data sources. A program called ENVDB.BRS (listed below) has been written to demonstrate how this extension works and to show how to setup linkage to a database or ODBC data source. Run the program as is to bring up the Microsoft ODBC manager. Then select a data source and look at the output from the program. This will also show you how to get and use connect strings. In this example lines 1900 and 2000 accomplish the same open as line 1800 in my environment.

Try it on your Windows workstation. As long as you have one or more ODBC drivers supported you can use it without having to install a database. You can even use it to interrogate Excel files because Microsoft provides an ODBC driver for that.

A sample program to demonstrate database interrogation entry is:
01000 ! Replace Envdb
01100 dim DATABASES$(1)*100
01200 dim DATABASE$*100
01300 dim TABLES$(1)*100
01400 dim TABLE$*100
01500 dim COLUMNS$(1)*100
01600 dim COLUMN$*100
01700 dim C$*100,FLD1$*40,FLD2$*40,FLD3$*40,FLD4$*40
01800 Execute "CONFIG database testdb odbc-manager"
01900 ! Execute "CONFIG database testdb DSN='Accounts Payable'"
02000 ! Execute "CONFIG database testdb connectstring=""DSN=Excel Files;DBQ=L:\orders\Beneficial PORCEL.xls;DefaultDir=L:\orders;DriverId=1046;MaxBufferSize=2048"""
02100 open #1: "name=envdb.txt,replace",display,output
02200 Dump_Table: ! ***** Dump Table Layout
02300 let OUTFD = 1
02400 let ENV$("status.database.LIST", MAT DATABASES$) !List of db's
02500 for DATABASE=1 to UDIM(DATABASES$) !For each connected database
02600 let DATABASE$=DATABASES$(DATABASE)
02700 let FNSHOW_DATABASE(DATABASE$, "status.database."&DATABASE$)
02800 next DATABASE
02900 end
03000 !
03100 def FNSHOW_DATABASE(DATABASE$*100, ST_PREFIX$*100)
03200 print #OUTFD: DATABASE$
03300 let OUT_PREFIX$=CHR$(9)
03400 print #OUTFD: OUT_PREFIX$&"DSN="&ENV$(ST_PREFIX$&".DSN")
03500 print #OUTFD: OUT_PREFIX$&"CONNECTSTRING="&ENV$(ST_PREFIX$&".CONNECTSTRING")
03600 print #OUTFD: OUT_PREFIX$&"Tables:"
03700 let ST_PREFIX$=ST_PREFIX$&".TABLES"
03800 let ENV$(ST_PREFIX$&".LIST", MAT TABLES$)
03900 for TABLE = 1 to UDIM(MAT TABLES$)
04000 let TABLE$=TABLES$(TABLE)
04100 let FNSHOW_TABLE(TABLE$,ST_PREFIX$&"."&TABLE$,OUT_PREFIX$&CHR$(9))
04200 next TABLE
04300 fnend
04400 !
04500 def FNSHOW_TABLE(TABLE$*100, ST_PREFIX$*100, OUT_PREFIX$)
04600 print #OUTFD: OUT_PREFIX$&TABLE$
04700 let OUT_PREFIX$=OUT_PREFIX$&CHR$(9)
04800 print #OUTFD: OUT_PREFIX$&"Table remarks="&ENV$(ST_PREFIX$&".REMARKS")
04900 print #OUTFD: OUT_PREFIX$&"Table type="&ENV$(ST_PREFIX$&".TYPE")
05000 print #OUTFD: OUT_PREFIX$&"Columns:"
05100 let ST_PREFIX$=ST_PREFIX$&".COLUMNS"
05200 let ENV$(ST_PREFIX$&".LIST", MAT COLUMNS$)
05300 for COLUMN = 1 to UDIM(MAT COLUMNS$)
05400 let COLUMN$=COLUMNS$(COLUMN)
05500 let FNSHOW_COLUMN(COLUMN$, ST_PREFIX$&"."&COLUMN$,OUT_PREFIX$&CHR$(9))
05600 next COLUMN
05700 fnend
05800 !
05900 def FNSHOW_COLUMN(COLUMN$*100, ST_PREFIX$*100, OUT_PREFIX$)
06000 print #OUTFD: OUT_PREFIX$&COLUMN$
06100 let OUT_PREFIX$=OUT_PREFIX$&CHR$(9)
06200 print #OUTFD: OUT_PREFIX$&"Column type="&ENV$(ST_PREFIX$&".TYPE")
06300 print #OUTFD: OUT_PREFIX$&"Column length="&ENV$(ST_PREFIX$&".LENGTH")
06400 print #OUTFD: OUT_PREFIX$&"Column decimals="&ENV$(ST_PREFIX$&".DECIMALS")
06500 fnend

===Other===
For more information about '''SQL''' or '''Structured Query Language''' see [[Wikipedia:SQL]].

===CLIENT_SERVER RECONNECT===
You can modify WBCONFIG.SYS or execute in code.

Sample:
EXECUTE "CONFIG CLIENT_SERVER RECONNECT_AFTER=120 RECONNECT_TIME=120"

The default 120 is two minutes; set the values to 99999 to wait 27 hours (infinity).

Please remember that the BRServer.exe components will remain "Active" in the server's task manager until the timeout period has passed.

When executing a SQL command, Client Server "appears to be hung" waiting for a response. if you reach the limit (IE 120 seconds), BRClient will believe it has timed out, and "Crash".

===SQL LOCK_TIMEOUT===
In SQL, [LOCK_TIMEOUT] instructs the SQL Server session to wait a certain number of seconds before returning an error.

* The default value is -1
* 120 is a reasonable value, and it works in tandem with CLIENT_SERVER RECONNECT.

Sample Code to set the [LOCK_TIMEOUT] settings:

Call the function providing the SQL Connection Name and the desired timeout.

If there is a blocking transaction, that transaction will trigger an error 3006 with SQL State of "42000"

Sample Function:
64230 DEF FNSET_LOCK_TIMEOUT(SLT_Connection$,SLT_Timeout)
64232 DIM SLT_SQL$*9999
64234 SLT_SQL$="SET LOCK_TIMEOUT "&str$(SLT_Timeout)
64236 OPEN #(Updhandle=Fngethandle): "DATABASE="&SLT_Connection$,SQL SLT_SQL$,OUTIN
64238 WRITE #Updhandle:
64240 CLOSE #Updhandle:
64242 FNEND

<noinclude>
[[Category:Terminology]]
[[Category:SQL]]
</noinclude>

Editor

2025-04-20T15:29:22Z

GomezL: /* Known Third Party Editors */

Many editors are supported for BR! [[Source code]].

The purpose of the EDITOR statement is to identify the program used by the [[EDIT]] command.

Editor "editor-program" [ REMOVE ] [NOWAIT]

While the '''Editor''' specification is normally placed within a BRCONFIG.SYS file, it can also be specified with the [[config|Config]] command or within a program line using the [[Execute]] statement to issue the Config command.

====Examples====
An Editor Statement could be typed in the command console like:

Config Editor "C:\Program Files\NotePad++\NotePad++.exe"

Or added to [[BRConfig.sys]] like:

Editor "C:\Program Files\NotePad++\NotePad++.exe"

Or perhaps added to BRConfig.sys, but only applicable to an individual user (Herbert) like:

@"Herbert" Editor "C:\Program Files\Mills Enterprise\MyEditBR\MyEditBR.exe"

<br>

REMOVE and NOWAIT affect the way the EDIT command uses the editor program. REMOVE causes BR to remove the source file when editing is complete.

====See also====

*[[Edit]]
*[[wikipedia:comparison_of_text_editors]].

<br>

====Known Third Party Editors====

These editors can process .wbs/.brs [[Business Rules! Editable Source]] code.

*NotePad
*[[PFE32]]
*[http://www.textpad.com/ TextPad]
*[[My Edit BR]]
*[http://www.mills-enterprise.ca MyEditBR website]
*[http://www.brixoft.net/prodinfo.asp?id=1 Source Edit]
*[[EditPad Pro]]
*[[Notepad++]] (open source)
*[http://www.ultraedit.com/ UltraEdit]
*[https://sourceforge.net/projects/jedit/ JEdit] (open source)
*[http://www.crimsoneditor.com Crimson Editor] (open source)
*[http://www.lugaru.com/ Epsilon]
*[http://www.context.cx/ ConTEXT]
*[http://www.andre-simon.de/ WinHighlight]
*[http://editra.org/index.php Editra]
*[https://www.cursor.com/ Cursor]

<noinclude>
[[Category:Utilities Third Party]]
[[Category:Config]]
[[Category:Basics]]
[[Category:Needs Help]]
</noinclude>

0317

2025-03-03T15:17:22Z

GomezL:

{{Error
|0317
|attempt to [[redimension]] an [[array]] that is already on the [[RPN stack]]
|attempt to redimension an array that is already on the RPN stack
This error was added in BR! version [[4.2]].

The following program demonstrates the error. Remember that X & Y are the same array in this example, and you cannot assign the results of FN_Y to the same array.

00010 dim x(10)
00020 let '''x(1)<nowiki>=</nowiki>'''fn_y(mat x)
00030 end
00040 def fn_y(mat y)
00060 mat y(1)
00070 let y(1)<nowiki>=</nowiki>123
00080 let fn_y<nowiki>=</nowiki>y(1)
00090 fnend

|Type [[GO]] and hit enter to continue.

You can solve the above example like this becomes 1:
00010 dim x(10),
00020 let temp<nowiki>=</nowiki>fn_y(mat x)
00025 let x(1)<nowiki>=</nowiki>temp
00030 end
00040 def fn_y(mat y)
00060 mat y(1)
00070 let y(1)<nowiki>=</nowiki>123
00080 let fn_y<nowiki>=</nowiki>y(1)
00090 fnend

Another alternative Becomes 123:

00010 dim x(10),
00020 let fn_y(mat x)
00030 end
00040 def fn_y(mat y)
00060 mat y(1)
00070 let y(1)<nowiki>=</nowiki>123
00080 let fn_y<nowiki>=</nowiki>y(1)
00090 fnend

}}
[[Category:Error Codes]]

0317

2025-03-03T15:14:38Z

GomezL:

{{Error
|0317
|attempt to [[redimension]] an [[array]] that is already on the [[RPN stack]]
|attempt to redimension an array that is already on the RPN stack
This error was added in BR! version [[4.2]].

The following program demonstrates the error. Remember that X & Y are the same array in this example, and you cannot assign the results of FN_Y to the same array.

00010 dim x(10)
00020 let '''x(1)<nowiki>=</nowiki>'''fn_y(mat x)
00030 end
00040 def fn_y(mat y)
00060 mat y(1)
00070 let y(1)<nowiki>=</nowiki>123
00080 let fn_y<nowiki>=</nowiki>y(1)
00090 fnend

|Type [[GO]] and hit enter to continue.

You can solve the above example like this: (X(1)=1)

00010 dim x(10),
00020 let temp<nowiki>=</nowiki>fn_y(mat x)
00025 let x(1)<nowiki>=</nowiki>temp
00030 end
00040 def fn_y(mat y)
00060 mat y(1)
00070 let y(1)<nowiki>=</nowiki>123
00080 let fn_y<nowiki>=</nowiki>y(1)
00090 fnend

Another alternative: (X(1)=123)

00010 dim x(10),
00020 let fn_y(mat x)
00030 end
00040 def fn_y(mat y)
00060 mat y(1)
00070 let y(1)<nowiki>=</nowiki>123
00080 let fn_y<nowiki>=</nowiki>y(1)
00090 fnend

}}
[[Category:Error Codes]]

BRSerial.dat

2024-03-04T15:52:46Z

GomezL:

The '''BRSerial.dat''' file contains your [[Business Rules!]] license information. You can easily check the license information contained in this file by using [[CheckSerial.exe]].

You may Rename BRSerial.dat to BrSerial.[BR Version] such as BRSerial.42 or BRSerial.43.

[[UserID$]]
<noinclude>
[[Category:Files]]
</noinclude>

BRSerial.dat

2024-03-04T15:52:01Z

GomezL:

The '''BRSerial.dat''' file contains your [[Business Rules!]] license information. You can easily check the license information contained in this file by using [[CheckSerial.exe]].

You may Rename BRSerial.dat to BrSerial.[BR Version] such as BRSerial.42 or BRSerial.43.

<noinclude>
[[Category:Files]] [[UserID$]]
</noinclude>

Sort Facility

2023-06-09T19:58:33Z

GomezL: Sort expanding on A vs B for "Address Out"

Business Rules sort facility allows you to produce a sorted output file from a randomly ordered file of records, and to rearrange the records in a variety of ways.

A simple [[Index and Sort facilities Tutorial#Basic Overview of SORT|Sort Tutorial]] is now available.

==Sort Command==
The SORT command is the instruction that tells BR to begin the sorting procedure. Its only parameter is an optional control file name that contains all the specifications about how a particular file, or set of files, is to be sorted.

BR will execute the SORT command in any of the following 3 ways:

#From READY mode. When followed by the name of the sort control file, SORT can be executed from READY mode.
#From a procedure file. When followed by either a sort control file name or by the specifications that make up a single sort group, SORT can be executed from a procedure file.
#With the EXECUTE statement. When the name of the sort control file is included, SORT may be specified by the EXECUTE statement.

BR SORT facility allows you to produce a sorted output file from a randomly ordered file of records, and to rearrange the records in a variety of ways.

===Comments and Examples===
The following example causes the sort specifications in ALPHABET.FIL to be executed (when ALPHABET.FIL is a sort control file in the form of an internal file):
SORT ALPHABET.FIL

Alternatively, and more easily edited sort control files can be created as [[PROC]] files. For example:
PROC SAMPLESORT

This command will run the procedure file SAMPLESORT, which begins with the SORT command, and contains all the sort control information. The SAMPLESORT procedure file might look like this:

Sort
! Creating a sort file, don't you worry!
FILE orders.int,,,samplesort2,,,,,R,,REPLACE,SHR
ALTS RO 1,"VERYQUICKFOX"
RECORD I,106,2,C,"TX","TX",OR
RECORD I,106,2,C,"LA","LA",OR
RECORD I,106,2,C,"tx","tx",OR
RECORD I,106,2,C,"la","la",OR
SUM
MASK 31,30,C,A,1,30,C,A

SORT tells BR to run a sort as the first step of the procedure. The comment will show up on the operator's screen. Only FILE and MASK are required. Each parameter will be described in detail below, but this particular SORT will return a file named samplesort2 of the data in the ORDERS.INT file, containing only the customer information from Texas and Louisiana arranged alphabetically according to "VERYQUICKFOX". Commas are necessary when skipping optional parameters within each line.

===Syntax===
SORT [<[[Sort control file|control file]]>]
[[Image:Sort.png]]

===Defaults===
1.) Look to the next lines in the procedure file for the sort specifications.

===Parameters===
The "control file" parameter specifies the name (and path, if required) of the sort control file to be executed. The sort control file contains up to six different types of specifications:
# COMMENT (use ! as the first character of a comment)
# FILE
# ALTS
# RECORD
# SUM
# MASK

FILE and MASK are required, others are optional.

The use of SORT without a control file-ref can only be done from within a procedure. All required sort specifications must then immediately follow the SORT command. (See [[Sort Facility]] for more information).

Sort control files can be created in a text editor as a procedure file, or as an internal file using WRITE. The simplest way is using a procedure file, since you can easily view and edit the file.

===Technical Considerations===
The '''Sort''' command performs a [[Clear]] operation (unless a program is active) and begins execution of the SORT control file. The number of RECORD specifications that may be used in sort control files is 20.

A user-defined collating sequence may be accessed through the FILE specification's collating sequence parameter. See [[COLLATE ALTERNATE]] in [[BRConfig.sys]] for more information.

==Types of Sort Specifications==

Six different types of sort specifications are allowed in each sort group. FILE and MASK are required. The following table summarizes some information about the sort specifications:

[[Image:SortSpecs.png]]

Below are six sections describing each SORT specification in detail.

===Comment===

The optional Comment (!) specification displays a message to the operator on the screen. Usually it's a description of the current sort. Comments appear exactly as you enter them.

====Comments and Examples====
BR displays up to twenty lines worth of Comments per sort group. Comments or Comment lines exceeding this amount are ignored. If a Comment is longer than 43 characters, it will wrap to the next line on the screen. The following is an example of SORT's Comment:

! Now sorting customer file

====Syntax====

[[Image:SortComment.png]]

====Parameters====
After the required '''!''' symbol, you mat include an optional message.

'''Message''' is the information to be displayed on the screen exactly as it is entered.

====Technical Considerations====

1) Comment specifications may be placed anywhere before the MASK specification in the sort group.

===SUM===

SUM is an optional specification that causes BR to display a summary of record counts after a file has been sorted. The way the titles are displayed is currently under development.

====Comments and Examples====

SUM should be used only when an operator will be in attendance during the sort, as execution will not continue between sort groups until <ENTER> has been pressed.

SUM displays three record counts:
# the total number of records in the input file not including deleted records.
# the number of records that were included in the sort.
# the number of records that were written to the output file.

The following example replaces the first and last messages printed by the SUM specification. The "Records read" and "Records selected" titles are left unchanged, but the single space in the fourth position causes the title for "Records output" to be omitted.

SUM Sort of HIST.FIL completed,,, ,Press <CR> to continue

====Syntax====

[[Image:SortSum.png]]<br>

====Defaults====
#Display "Sort successfully completed."
#Display "Records read"
#Display "Records selected"
#Display "Records output"
#Display "Press any key to continue."

====Parameters====
{|
|-valign="top"
|width="90"|'''Title1'''||is a replacement for the message "Sort successfully completed", which will automatically be displayed unless you specify otherwise.
|-valign="top"
|width="90"|'''Title2'''||is a replacement for the title "Records read". Unless you specify otherwise, BR displays this title next to a count of the total number of records read from the input file.
|-valign="top"
|width="90"|'''Title3'''||is a replacement for the title "Records selected". Unless you specify otherwise, BR displays this title next to the total number of records, which were included in the sort.
|-valign="top"
|width="90"|'''Title4'''||is a replacement for the title "Records output", which is displayed next to the total number of records that were written to the output file.
|-valign="top"
|width="90"|'''Title5'''||is a replacement for the message "Press Enter to continue", which is automatically displayed unless you specify otherwise.
|-valign="top"
|}

====Technical Considerations====

#To entirely suppress a message, enter a space for the title. There is no way to suppress record counts when SUM is specified.
#BR truncates any title parameter, which is longer than 70 characters.
#The number of records selected displayed on the third line and the number of records output displayed on the fourth line should always be the same.

===FILE===

The FILE specification is a required specification that identifies the following:
#Names of input and output files.
#Directory for workspace.
#Type of output file.
#Desired collating sequence.
#Optional replacement of previous output files with the same name.
#File sharing rules for the input file.

====Comments and Examples====
The following specification indicates that MASTER is the file to be sorted. It can be found in the VOL subdirectory on drive C. SORTOUT is the name of the output file. This can also be found in the VOL subdirectory on drive C. The WORK subdirectory, on drive D, is the designated work space. An address-out sort (PD3 format) is to be performed, and the native collating sequence is to be used (unless the BRConfig.sys file or CONFIG command has specified ALTERNATE):

FILE MASTER,VOL,C,SORTOUT,VOL,C,WORK,D,A,N

In the following example, SORT.IN is the file to be sorted. It is located in the current directory on the current drive. SORT[WSID].OUT is the output file. The current drive and directory will be used for the work space. In the name SORT[WSID].OUT, the [WSID] will be replaced with the current workstation ID. A record-out sort using the alternate collating sequence is called for. If SORT[WSID].OUT already exists, BR will replace it with the newly sorted file.

FILE SORT.IN,,,SORT[WSID].OUT,,,,,R,A,REPLACE

====Syntax====

[[Image:SortFile.png]]

====Defaults====
#Use the current directory.
#Use the current drive.
#Record-out sort (FILE spec)
#Default according to the COLLATE specification in the BRConfig.sys file.
#Do not replace existing file by same name.
#Use SHRI.

====Parameters====
{|
|-valign="top"
|width="150"|'''Input file name'''||is the name of the internal file containing the records to be sorted. "Path" is the sequence of directories, which leads to this file. "Drive" is the drive on which the subdirectory path and file can be found.
|-valign="top"
|width="150"|'''Output file name'''||is the name of the file that is to contain the sorted records (or just the record numbers of the records in sorted order). "Path" is the sequence of directories, which leads to this file, and "drive" is the drive on which the subdirectory path and file can be found.
|-valign="top"
|width="150"|'''Workpath'''||is a sequence of subdirectories, which leads to the disk area where BR should create a temporary Workspace. "Workrive" is the drive on which this subdirectory path can be found. There is no need to specify a name for the work file, as BR handles this internally. See the Technical Considerations section below for formulas to help you estimate work file space.
|-valign="top"
|width="150"|'''FILE spec'''||allows you to choose from three different sort storage methods. "A" calls for an address-out sort, which is to be stored in PD3 format. "B" calls for an address-out sort, which is to be stored in B4 format. (''Note: Not BH4'') In both cases, the "address" which is being stored is the relative record number. "R" calls for a record-out sort, which means that each entire record is written to the output file. See the Technical Considerations section below for formulas to help you estimate address-out and record-out space. Address-out sorts execute much more quickly than record-out sorts, and the B storage method is faster than the A storage method. When using the A Method, PD 3 has a limit of 99,999, so any records >10,000 will have a 0 in the PD 3 value. When possible using "B" to avoid problems with large data sets.
|-valign="top"
|}

{|
|-valign="top"
|width="150"|'''Collating sequence'''||allows you to specify a collating sequence for the sort. "A" calls for the alternate collating sequence to be used, and "N" calls for the native sequence to be used. In most instances, the FILE specification collating sequence will override the BRConfig.sys file's COLLATE specification (for the sort only). There is one exception, however: if the FILES specification is N, and the BRConfig.sys specification is COLLATE ALTERNATE, the alternate collating sequence will be used. The following table should help clarify when each sequence is used. NOTE that the column labeled BRConfig.sys refers to the specification currently in use, whether it was actually specified by the BRConfig.sys file or by the CONFIG command:
[[Image:SortFileTable.png]]
'''*Note''' the exception with this option
|-valign="top"
|}

{|
|-valign="top"
|width="150"|'''REPLACE'''|| replaces an old sort output file (if it exists under the specified name) with the new output file. This eliminates the need to free or drop the file before sorting. REPLACE should only be used with caution, as the existence of the output file may indicate that a previous error has occurred. You may wish to restrict the use of this parameter to WSID temporary files and to address-out files.
|-valign="top"
|}

{|
|-valign="top"
|width="150"|'''share spec'''|| allows you to specify file sharing rules for the input file. Any one of the NOSHR, SHRI, SHR or SHRU keywords is accepted. Keep in mind that the sort output file will not be in sorted order if SHR is specified and a record's sort field is modified by another workstation during the sort. (See "[[share specs]]" for more information about share parameters)
|-valign="top"
|}

'''All commas must be included in the FILE specification, even if some parameters are omitted'''.

====Technical Considerations====
1) To estimate the amount of space BR will need for its temporary work space, use the following formula (applies to both address-out and record-out sorts):

: Number of records selected x 2
: x Sort key length + 4
: = N [round up to a multiple of 1024]

2) To estimate the byte size of a PD3-formatted output file for an address-out sort (specified with the "A" parameter), use the following formula:

: Number of records selected
: x 4
: + 15
: = N [round up to a multiple of 512]

3) The record limit for a PD3-formatted address-out sort ("A"), is 99,999 records.

4) To estimate the byte size of a B4-formatted output file for an address-out sort (specified with the "B" parameter), use the following formula:

: Number of records selected
: x 5
: + 16
: = N [round up to a multiple of 512]

5) The record limit for a B4-formatted address-out sort ("R"), is 2.147 billion (2**31-1) records.

6) To estimate the byte size of the output file for a record-out sort (specified with the "R" parameter), use the following formula:

: Number of records selected
: x Record length + 1
: = N [round up to a multiple of 512]

7) BR automatically clears the controls (including collating sequence) set by one sort group before it begins executing the next sort group.

===ALTS===

The optional ALTS specification allows you to either reorder part of the collating sequence or to set certain characters equal to a new collating value.

====Comments and Examples====

ALTS is frequently used to set the values of uppercase letters to the same values as their lowercase counterparts.

ALTS specifications take effect only during character comparisons when C is specified as the format type.

The following example reorders the collating values for the uppercase letters A through Z to match the collating values for their lowercase counterparts:

ALTS RO,97,"ABCDEFGHIJKLMNOPQRSTUVWXYZ"

The following example reorders lowercase letters so that vowels are sorted before consonants:

ALTS RO,97,"aeioubcdfghjklmnpqrstvwXYZ"

The following nine specifications set the collating values for the characters 1 through 9 to a collating value of 48. These values come into effect only when the sort is performed on C field types. It does not effect the sorting of the digits 1 through 9 when they appear in N fields:

ALTS EQ,48,"123456789"

====Syntax====

[[Image:SortAlts.png]]

====Parameters====

In the top route of the syntax diagram, "RO" indicates that a set of characters is to be reordered.

"New starting value" is the first value to be assigned in the reordering process. It must be a number from 0 to 255, and it must not be allowed to increment beyond 255. Thus if you enter a value of 253, only three characters may be reordered.

"Character sequence" is a list of up to 28 characters which are to be reordered. It must be enclosed in quotation marks. The value which is specified as the "new starting value" will be assigned to the first character specified in the "character sequence". The next sequential value will be assigned to the second character in the "character sequence", and so on. If you wish to reorder more than 28 characters, you must use an additional ALTS specification.

In the lower route of the syntax diagram, "EQ" indicates that a new, single collating value is to be assigned to the specified characters (the value and the characters are set equal).

"New value" is a value from 0 to 255 which is to be assigned to the character. "Character sequence" is the character or set of characters which is to be given a new value; it must be enclosed within quotation marks.

====Technical Considerations====

1) If the ALTERNATE collating sequence is used for the sort, the following implied ALTS specification is automatically registered before any other ALTS specification is executed. Subsequent and overlapping ALTS specifications will take precedence over this specification:

ALTS RO,176,"0123456789"

2) There is no limit to the number of ALTS specifications in a single sort group.

3) BR automatically clears all controls (including collating sequence and ALTS reordering) set by one sort group before it begins executing the next sort group.

===Record===

The optional RECORD specification allows you to specifically include or eliminate particular records from the sorting procedure.

====Comments and Examples====
Each RECORD specification tells BR to examine a specific field, called the select field, and determines whether or not the record should be included in the sort. If the value of the select field falls inside the specified limits and I (include) has been specified, BR includes that record in the sort.

If the value of the select field falls outside the specified limits and O (omit) has been specified, BR includes that record in the sort.

When two or more RECORD specifications are connected by the keyword "AND" (also the default), a single record must meet the qualifications of both before it will be included in the sort.

The RECORD specification in the following example tells BR to include only the records that fall inside the specified limits. The select field starts in position one of the record and is four characters in length. It is in packed decimal (PD) format. If the select field is equal to or greater than 100, and not more than 999, it will be included in the sort.

RECORD I,1,4,PD,"100","999"

The following six RECORD specifications demonstrate the use of AND and OR. If a record passes the test in the first specification, it is automatically included in the sort. If it does not pass this test, it is evaluated according to the next three record specifications. If it does not receive a true evaluation for each, it may still be able to pass the requirements of the last two specifications. If it does not pass any of the three tests, it is not included in the sort:

RECORD I,40,5,C,"55024","55036",OR
RECORD I,49,15,C,"DAKOTA","DAKOTA",AND
RECORD I,20,15,C,"OUST RD","OUST RD",AND
RECORD I,15,5,C,"47315","47722",OR
RECORD I,49,15,C,"DAKOTA","DAKOTA",AND
RECORD I,20,15,C,"RIDGE RD","RIDGE RD"

The following record specification uses comparison fields in each individual record as the select field's lower and upper limits. An example of how it would be used is to identify all inventory items which are either understocked or overstocked. The select field (which represents the current inventory amount), is described as the six-character field that starts at position 28. If the value of this field is less than the value of the field that starts at position 52 (the minimum inventory amount) or greater than the value of the field that starts at position 59 (the maximum inventory amount), it will be included in the sort.

RECORD O,28,6,N,52,59

====Syntax====

[[Image:SortRecord.png]]

====Defaults====

1) Use AND.

====Parameters====

RECORD's first parameter must either be "I" (include) or "O" (omit). If you choose I, BR will include all records with select fields that fall inside the values you specify. If you choose O, BR will exclude all records with select fields that fall outside the values you specify.

"Start pos" is the starting position of the select field, and "field length" is its character length. "Form spec" indicates the format of the field (C, N, PD, etc.; see the discussion of [[Category:File_Operations#Format_Specifications|format specifications]] in the [[File Operations]] Section for more information).

BR compares the data in the specified field to lower and upper limits before determining whether or not its record should be included in the sort.

"Lower limit start pos" identifies the starting position of another field in the record. BR will use the value of this comparison field as the lower limit of the select field. The format type of the comparison field must be identical to that of the select field, and the field length must be the same.

"Upper limit start pos" identifies the starting position of the field which contains the upper limit value for the select field. The format type of this comparison field must be identical to that of the select field, and the field length must be the same.

"Lower limit" and "upper limit" are constants enclosed within quotation marks. BR will consider these to be the low and high limit values for the select field in determining whether or not the record should be included. BR will not accept strings greater than 40 characters in length for this parameter. It is important to distinguish between uppercase and lowercase letters when using these options, as BR makes a letter-for-letter comparison on character fields.

"OR" and "AND" are the RECORD specifications' last two parameters. If all the RECORD specifications in a sort group are joined by "AND", a record must pass the requirements of each before it will be included in the sort. The following two specifications, for instance, require that the value of a certain four-character field in the record fall outside 2000 and 8000 and that a certain two-character field is equal to MN. If a record doesn't pass both these tests, it is not included in the sort.

RECORD O,3,4,C,"2000","8000",AND
RECORD I,28,2,"MN","MN"

The "OR" parameter gives a second try to records that don't pass the first set of requirements. When it is included at the end of a RECORD specification, "OR" tells BR that the next RECORD specification begins a new set of requirements. If the current record passes the first set of requirements, BR does not check to see if it will pass any others. If the record does not pass the first set, however, BR sequentially checks to see if it will pass any other sets. As soon as it passes the requirements for one set of RECORD specifications, BR includes it in the sort.

When "passing"lower and higher limits in the "record" section be sure and enclose the incoming variables with the appropriate quote sequence. This is just and example:

LET Data_Record$='RECORD I,'&Str$(Recpos)&',8,C,"'&V_File$&'","'&V_File$&'"'

In the above example we are passing the Record Position and the lower and upper file numbers we want included in the "filter"

====Technical Considerations====

1) BR allows up to 20 RECORD specifications per sort group. (On releases prior to 3.21b, the limit was ten)
2) When the OR parameter is used, processing will be faster if the first conditions are the ones most likely to result in a true evaluation.
3) The two different types of lower and upper limit parameters may be specified in the same RECORD specification.
4) When I is specified, the RECORD specification's parameters are inclusive. This means that a record will be included in the sort if the select field's value is equal to the upper or lower limit value or if it is between the upper and lower limit values.
5) When O is specified, RECORD's upper and lower limits are exclusive; the select field value may not be equal to either the upper or lower limit if it is to be included in the sort.

===MASK===

MASK is a required specification that identifies up to ten sort fields and the manner (ascending or descending) in which they should be sorted.

====Comments and Examples====
A sort field contains the specific information to be sorted. When you wish to organize records in descending order by zip code, for example, the sort field is the zip code. The following MASK specification defines two sort fields. The first starts in position 1 and is four characters long; it is in packed decimal (PD) format, and it is to be sorted in ascending order. The second field starts in position 10 and is three characters long. It is in character (C) format, and is also to be sorted in ascending order.

MASK 1,4,PD,A,10,3,C,A

====Syntax====

[[File:SortMask.png]]

====Parameters====

"Start position" is the starting record position of the field to be sorted, and "field length" is its character length.

"Form spec" indicates the format, or data type, of the field. The format specifications which can be used in the MASK specification include the following: B (Binary); BL (Binary low); BH (Binary high); C (Character); D (Double-precision); L (Long); N (Numeric); PD (Packed decimal); S (Single-precision) and ZD (Zoned decimal). See [[:Category:File_Operations#Format_Specifications|Format Specifications]] in the [[File Operations]] section for more information about each of these format types.

The last parameter must either be "A" to sort the specified record in ascending order, or "D" to sort it in descending order.

====Technical Considerations====
#The following field lengths are required for the D, S & L format specs: D is 8; S is 4; & L is 9.
#The total sort key length may not exceed 32,767 bytes.
#Up to ten fields may be specified with the MASK specification.
#MASK must always be the last specification in a sort group.
#The existence of MASK flags the end of the current sort group. BR will automatically treat any specifications that follow as part of the next sort group.

==Sort Specification==

The ! (for a comment), FILE, ALTS, RECORD, SUM and MASK specifications each control a different aspect of the sorting procedure. A set of these specifications makes up a sort group.

===Sort Group===

A sort group contains all the necessary specifications for controlling the sorting of a single file. If only one file is to be sorted, a single sort group may simply follow a SORT command when it is specified in a procedure file. When several files are to be sorted, the sort group for each file may be placed together in a single sort control file.

==Sort Control File==
{{:Sort control file}}

==Sort Baseyear Processing==

If a Y is appended to the A/D (ascending/descending) indicator of sort MASK statements or the I/O (include/exclude) field of RECORD statements, the field is subject to BASEYEAR processing.

If such a field is in display format, then the first two characters are assumed to be a BASEYEAR dependent value. If the field is packed (BH or PD) then the storage format is assumed to be YYMMDD, YYMM or YY format depending on the length of the field, similar to INDEX (see above table).

The Y2K sorting and indexing features interpret year zero as zero (instead of 2000) if the month and day are zero. Leading blanks in baseyear sensitive fields are replaced with zeroes for sorting purposes, provided the remainder of the field contains only numeric data.

Prior to release 3.83e, BR ignored the digits to the left of the rightmost six digits of a date value unless century was specified in the mask.

;Example:

... DAYS(1001021,"YMD") produced the same result as DAYS(001021,"YMD")

Now if BR is unsuccessful applying the "YMD" mask it now tries "CYMD". This change has inconvenienced some dealers. Therefore OPTION 18 is provided to ignore the presence of digits to the left of the rightmost six digits.

==Improving Sort Speed==

All of the following factors can affect the speed of a sorting operation:

# Number of records to be sorted. The fewer the records, the faster the sort.
# Type of sort. Address-out sorts takes less time to run than a record-out sorts because they require fewer disk reads and writes. Also the B storage method for address-out sorts is faster than the A storage method.
# RECORD specification(s). When RECORD specifications cause many records to be omitted from the sort, the sort runs faster.
# Length of sort field. The shorter the sort field, the faster the sort.
# Order of records in the input file. Files with records that are already close to the desired order sort more quickly than more randomly ordered files.
# Record size. The smaller the input records the faster the sort.
# Storage size of the computer. In general, the greater the storage, the faster the sort. If the computer does not have enough storage, it must use work files: the time required to read from and write to these files adds to the amount of time the sort takes.

<noinclude>
[[Category:Sorting and Indexing]]
[[Category:Facility Commands]]
[[Category:Needs Help]]
</noinclude>

Category:Format Specifications

2023-04-11T16:27:00Z

GomezL:

See the format specifications below for more details on each.

See also [[Format specifications]] for a chart comparing their uses.

Format specifications tell Business Rules the form that a particular data field will be in. A format specification is required whenever data is input into or output from a program. It is also required with the full screen processing statements. Format specs are not required in the following situations: with unformatted internal files, with all display files, and when unformatted PRINT statements are used.

Not all format specifications are alike in their uses. Some can be used with internal and external files, but not with display files. Some can be used only with full screen formatting. And some format specifications behave differently depending on whether they are used for input or output.

== '''Common SQL Fields and BR Equivilants''' ==

<TABLE BORDER="1" CELLSPACING="1" CELLPADDING="3" WIDTH="100%" ALIGN="Center">

<TR>
<TH>SQL Field</TH>
<TH>BR Equivilant</TH>
<TH>Example</TH>
</TR>

<TR>
<TD>COUNTER</TD>
<TD>N</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>INTEGER</TD>
<TD>N</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>LONG INTEGER</TD>
<TD>N</TD>
<TD>N 12</TD>
</TR>

<TR>
<TD>SINGLE</TD>
<TD>S</TD>
<TD>S</TD>
</TR>

<TR>
<TD>DOUBLE</TD>
<TD>D</TD>
<TD>D</TD>
</TR>

<TR>
<TD>BINGINT</TD>
<TD>N/A</TD>
<TD>N 10</TD>
</TR>

<TR>
<TD>SMALLINT</TD>
<TD>BH</TD>
<TD>BH 2</TD>
</TR>

<TR>
<TD>FLOAT</TD>
<TD>L</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>DECIMAL</TD>
<TD>N or PD</TD>
<TD>N 12.2 or PD 6.2</TD>
</TR>

<TR>
<TD>DATETIME</TD>
<TD>N/A</TD>
<TD>Typically Stored as separate fields</TD>
</TR>

<TR>
<TD>DATE</TD>
<TD>C or D</TD>
<TD>C 8 or D</TD>
</TR>

<TR>
<TD>CHAR</TD>
<TD>C</TD>
<TD>C 30</TD>
</TR>

</TABLE>

<noinclude>
[[Category:Field Specifications]]
[[Category:All Parameters]]
</noinclude>

Category:Format Specifications

2023-04-11T16:26:21Z

GomezL:

See the format specifications below for more details on each.

See also [[Format specifications]] for a chart comparing their uses.

Format specifications tell Business Rules the form that a particular data field will be in. A format specification is required whenever data is input into or output from a program. It is also required with the full screen processing statements. Format specs are not required in the following situations: with unformatted internal files, with all display files, and when unformatted PRINT statements are used.

Not all format specifications are alike in their uses. Some can be used with internal and external files, but not with display files. Some can be used only with full screen formatting. And some format specifications behave differently depending on whether they are used for input or output.

Common SQL Fields and BR Equivilants

<TABLE BORDER="1" CELLSPACING="1" CELLPADDING="3" WIDTH="100%" ALIGN="Center">

<TR>
<TH>SQL Field</TH>
<TH>BR Equivilant</TH>
<TH>Example</TH>
</TR>

<TR>
<TD>COUNTER</TD>
<TD>N</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>INTEGER</TD>
<TD>N</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>LONG INTEGER</TD>
<TD>N</TD>
<TD>N 12</TD>
</TR>

<TR>
<TD>SINGLE</TD>
<TD>S</TD>
<TD>S</TD>
</TR>

<TR>
<TD>DOUBLE</TD>
<TD>D</TD>
<TD>D</TD>
</TR>

<TR>
<TD>BINGINT</TD>
<TD>N/A</TD>
<TD>N 10</TD>
</TR>

<TR>
<TD>SMALLINT</TD>
<TD>BH</TD>
<TD>BH 2</TD>
</TR>

<TR>
<TD>FLOAT</TD>
<TD>L</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>DECIMAL</TD>
<TD>N or PD</TD>
<TD>N 12.2 or PD 6.2</TD>
</TR>

<TR>
<TD>DATETIME</TD>
<TD>N/A</TD>
<TD>Typically Stored as separate fields</TD>
</TR>

<TR>
<TD>DATE</TD>
<TD>C or D</TD>
<TD>C 8 or D</TD>
</TR>

<TR>
<TD>CHAR</TD>
<TD>C</TD>
<TD>C 30</TD>
</TR>

</TABLE>

<noinclude>
[[Category:Field Specifications]]
[[Category:All Parameters]]
</noinclude>

Category:Format Specifications

2023-04-11T14:23:26Z

GomezL:

See the format specifications below for more details on each.

See also [[Format specifications]] for a chart comparing their uses.

Format specifications tell Business Rules the form that a particular data field will be in. A format specification is required whenever data is input into or output from a program. It is also required with the full screen processing statements. Format specs are not required in the following situations: with unformatted internal files, with all display files, and when unformatted PRINT statements are used.

Not all format specifications are alike in their uses. Some can be used with internal and external files, but not with display files. Some can be used only with full screen formatting. And some format specifications behave differently depending on whether they are used for input or output.

Common SQL Fields and BR Equivilants

<TABLE BORDER="0" CELLSPACING="1" CELLPADDING="3" WIDTH="100%" ALIGN="Center">

<TR>
<TH>SQL Field</TH>
<TH>BR Equivilant</TH>
<TH>Example</TH>
</TR>

<TR>
<TD>COUNTER</TD>
<TD>N</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>INTEGER</TD>
<TD>N</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>LONG INTEGER</TD>
<TD>N</TD>
<TD>N 12</TD>
</TR>

<TR>
<TD>SINGLE</TD>
<TD>S</TD>
<TD>S</TD>
</TR>

<TR>
<TD>DOUBLE</TD>
<TD>D</TD>
<TD>D</TD>
</TR>

<TR>
<TD>BINGINT</TD>
<TD>N/A</TD>
<TD>N 10</TD>
</TR>

<TR>
<TD>SMALLINT</TD>
<TD>BH</TD>
<TD>BH 2</TD>
</TR>

<TR>
<TD>FLOAT</TD>
<TD>L</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>DECIMAL</TD>
<TD>N or PD</TD>
<TD>N 12.2 or PD 6.2</TD>
</TR>

<TR>
<TD>DATETIME</TD>
<TD>N/A</TD>
<TD>Typically Stored as separate fields</TD>
</TR>

<TR>
<TD>DATE</TD>
<TD>C or D</TD>
<TD>C 8 or D</TD>
</TR>

<TR>
<TD>CHAR</TD>
<TD>C</TD>
<TD>C 30</TD>
</TR>

</TABLE>

<noinclude>
[[Category:Field Specifications]]
[[Category:All Parameters]]
</noinclude>

Category:Format Specifications

2023-04-11T14:22:16Z

GomezL:

See the format specifications below for more details on each.

See also [[Format specifications]] for a chart comparing their uses.

Format specifications tell Business Rules the form that a particular data field will be in. A format specification is required whenever data is input into or output from a program. It is also required with the full screen processing statements. Format specs are not required in the following situations: with unformatted internal files, with all display files, and when unformatted PRINT statements are used.

Not all format specifications are alike in their uses. Some can be used with internal and external files, but not with display files. Some can be used only with full screen formatting. And some format specifications behave differently depending on whether they are used for input or output.

Common SQL Fields and BR Equivilants

<TABLE BORDER="0" CELLSPACING="1" CELLPADDING="3" WIDTH="100%" ALIGN="Center">

<TR>
<TH>SQL Field</TH>
<TH>BR Equivilant</TH>
<TH>Example</TH>
</TR>

<TR>
<TD>COUNTER</TD>
<TD>N</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>INTEGER</TD>
<TD>N</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>LONG INTEGER</TD>
<TD>N</TD>
<TD>N 12</TD>
</TR>

<TR>
<TD>SINGLE</TD>
<TD>S</TD>
<TD>S</TD>
</TR>

<TR>
<TD>DOUBLE</TD>
<TD>D</TD>
<TD>D</TD>
</TR>

<TR>
<TD>BINGINT</TD>
<TD>N/A</TD>
<TD>N 10</TD>
</TR>

<TR>
<TD>SMALLINT</TD>
<TD>BH</TD>
<TD>VH 2</TD>
</TR>

<TR>
<TD>FLOAT</TD>
<TD>L</TD>
<TD>N 6</TD>
</TR>

<TR>
<TD>DECIMAL</TD>
<TD>N</TD>
<TD>N 12.2</TD>
</TR>

<TR>
<TD>DATETIME</TD>
<TD>N/A</TD>
<TD>Typically Stored as separate fields</TD>
</TR>

<TR>
<TD>DATE</TD>
<TD>C or D</TD>
<TD>C 8 or D</TD>
</TR>

</TABLE>

<noinclude>
[[Category:Field Specifications]]
[[Category:All Parameters]]
</noinclude>

BR Forum

2019-05-21T13:16:20Z

GomezL: Link to Register on Forum was broken.

The '''BR Forum''' ([http://brforum.brulescorp.com brforum.brulescorp.com]) is the place to post your questions, answers and other discussions about the Business Rules! programming language.

To keep up to date with what is going on on the BR Forum, it is suggested that you subscribe to the [http://brforum.brulescorp.com/rss.php BR Forum's RSS feed].

===New Users===
*[http://brforum.brulescorp.com/ucp.php?mode=register Register]
*[http://brforum.brulescorp.com/viewforum.php?f=20&sid=60fc6da8b23fc4ddab1355116d9e3906 Help using the BR forum]

When you get there, please check out the "[http://brforum.brulescorp.com/viewforum.php?f=20&sid=60fc6da8b23fc4ddab1355116d9e3906 Using this forum]" section for a few pointers to help you get started.

You must be registered in the new forum in order to post messages. To do so, go to [http://brforum.brulescorp.com/ brforum.brulescorp.com] and [http://brforum.brulescorp.com/profile.php?mode=register&sid=ee5bc51d99c90d7865769931e18acb22 Register]. New registrations must be approved by a moderator, so there could possibly be a slight delay before that takes place. Once you are registered, log in using the [http://brforum.brulescorp.com/login.php Log in] link in the upper right corner and you're ready to go.

==Older Forum: the Maillist==
The older "BR_forum" maillist is still in wide spread use as a portal to the online forum, just remember that every email you send or receive here will be published.

*[http://brulescorp.com/mailman/listinfo/br_forum_ads.net maillist]

Floating Point Numbers

2019-04-29T17:42:33Z

GomezL:

Floating point numbers are numbers which allow the decimal point to "float" to any position.

The Business Rules syntax is nEm, where n is a numeric constant indicating the sign and significant digits in the number, E represents base 10, and m is a signed integer representing the power to which 10 is raised.

The largest and smallest numbers available to the system vary according to the hardware and operating system being used. The system function [[INF]] can be used to print the largest possible number on any given system; 1/INF generates the smallest number. Some caution should be used in working with numbers near these minimum and maximum extremes (e.g., using exponential notation in SORT). Also, for any given combination of hardware and Business Rules, the system infinity function INF and some testing should be combined to determine whether or not intermediate values used in calculations will be truncated.

All numbers in memory are floating point numbers. The form of the number when written to disk or printed depends on the format or mask used in writing the number. Number variables are designated by NOT ending with a dollar sign in Business Rules.

The fact that floating-point numbers cannot precisely represent all real numbers, and that floating-point operations cannot precisely represent true arithmetic operations, leads to many surprising situations. This is related to the finite precision with which computers generally represent numbers. in BUsiness Rules, floating point numbers can only provide 16 digits of precision, this is before or after the decimal place.

Examples:

Values that fit in Floating Point:
print using 'form n 20.2':1234567890123.45 --> 1234567890123.45
print using 'form n 20.8':1234567.89012345 --> 1234567.89012345

Values that do not fit in Floating Point:
print using 'form n 20.2':123456789012345.67 --> 123456789012346.00
print using 'form n 20.8':1234567890.1234567 --> 1234567890.12346000

<noinclude>
[[Category:Definitions]]
</noinclude>

System (command)

2019-03-26T06:54:53Z

GomezL: /* Using SYSTEM to exit BR */

The '''System (SY)''' [[:Category:commands|command]] can do two things:
#Exit BR and return control to the [[operating system]], or
#Perform a '''shell call''' (an operating system command) while BR is active.

System can be executed from [[READY mode]], from a [[procedure file]], or with the [[Execute]] [[statement]].

==Using SYSTEM to exit BR==

Entering SYSTEM or it's short form, SY into the command line while in ready mode will exit BR. The same can be done using an [[Execute]] Statement or a procedure file. Optionally, the SYSTEM LOGOFF may be used when in client server to clear the login credentials. The next time client server is started, the user will be requested to enter their credentials.

[[image:Sy.jpg]]
[[image:System2.jpg]]

==Using SYSTEM to send commands to the operating system==
===Comments and Examples===
One use of the SYSTEM command is to run a program. In the following example, the SYSTEM command runs a program called BACKUP. By default, the first program waits until the other program has finished running, and then continues where it left off. The syntax includes the SYSTEM keyword and then the OS command, which in this case is the program name and location of the data to handle.

SYSTEM BACKUP C:\\*.DAT A: /S

When the same command is used with an asterisks preceding the program name, BACKUP, the BR program restores its own screen upon re-entry:

SYSTEM *BACKUP C:\\*.DAT A: /S

The statement below tests to see what type of operating system the program is running on, then executes the appropriate SYSTEM command for the operating system:

00100 IF FILE$(0)(1:3)="CON:" THEN EXECUTE "SYSTEM DIR" :!ELSE EXECUTE "SYSTEM ls -al /usr/BR"

===Using Batch Files with System Exits===

One use of the System command is to pass a return code to the host operating system as it permanently exits from BR. In the following example, a DOS [[batch file]] tests the return code ([[ERRORLEVEL]]) and performs a specific action according to its value.

BR PROC START
IF ERRORLEVEL 101 GOTO BADERROR
IF ERRORLEVEL 100 GOTO BACKUP
IF ERRORLEVEL 99 GOTO FORMAT
GOTO DONE
:BACKUP
BACKUPBR
:FORMAT
FORMAT A:
BRSTART
:BADERROR
ECHO BAD ERROR RETURN CODE >100
:DONE

If the command SYSTEM 100 were used to exit from BR while the above batch file was active, DOS would start a back-up procedure as soon as the BR exit was complete.

==Syntax==
SYSTEM [< code for OS>|[*]<OS command>]
[[Image:System.png]]

==Defaults==
:1) Return control to the operating system with a return code of zero.
:2) Do not restore screen.

==Parameters==
'''Code returned to OS''' parameter sets the value of the operating system return code which may be accessed using ERRORLEVEL in DOS batch files, and through the $? substitution in a Linux shell.

'''OS command''' parameter is an operating system command which BR passes to the operating system for execution. If the result of the command requires no operator action, BR is immediately reentered after the command finishes execution. Since BR is active during the entire process, all variable values and memory remain the same. When the '''OS command''' parameter is preceded by an asterisk, the BR screen is restored upon re-entry. The '''OS command''' parameter must be a '''string variable''' or a string enclosed in quotes.

Additional Flags that can alter the behavior of the shell call as follows:

{|
|-valign="top"
|width="60"|'''-E or -e'''||"Errors" indicates that any Operating System errors returned from the shell should be reported (as error 4300). Otherwise shell call operating errors are ignored. Occasionally Windows returns error values for fairly nominal exceptions. So the -e is provided as an error return sensitivity level flag. Further information can be obtained by querying SYSERR or SYSERR$
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-M or -m'''|| Run the program "minimized".
|-valign="top"
|width="60"| ||If a lower case m is specified, the process or program runs in a DOS command shell without displaying the DOS window, but with a corresponding icon on the task bar (effectively running the shelled process/program "minimized") This is only applicable in Windows. It is ignored on all other systems. If -m is omitted, the shelled program will open in a separate application window.
|-valign="top"
|}

{|
|-valign="top"
|width="60"| ||If an upper case M is specified, the same thing happens except the task does not appear on the taskbar. It runs invisibly. Furthermore [[SPOOLCMD]] calls use lowercase m.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-s'''||Server Shell Call (this is the default for Unix). If -s is omitted on Windows, then this shell call is performed on the client.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-@'''||If using Client Server, then it runs the program on the client. Client Shell Call is the default for Windows. If -@ is omitted, on Unix the shell call is performed on the server.
|-valign="top"
|width="60"| ||(search path not implemented on client)
|-valign="top"
|}

{|
|-valign="top"
|width="60"| ||Note- Ctrl-] ALWAYS performs a DOS shell call on the client.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-c'''||The main program "continues" to run while running the called one. Windows launches the task and resumes BR operation where it left off. For Unix, the command is framed with NOHUP and &. If -c is omitted, then the shell call waits til the process is finished (up to the # seconds specified in SHELL LIMITS) before returning to BR.

For [[OSX]] 10.6 you need

sys -c | (pipe) Shell commands with a pipe make BR Continue after executing the sys -c (continue), if you do not insert the | pipe, then the script does not run under Br [[4.18]] and up.
This was found on a Macintosh client that just updated to OSX 10.6.x

|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-r'''||"Restore" means that Windows always performs shell in a separate window. (Unix ignores shell standard out data). If -r is omitted. then Unix forwards standard out and stdin data to and from the client window.
|-valign="top"
|width="60"| ||Restores screen after running command on all systems. In windows, the screen is always restored, so this flag is essentially ignored. This is the same as sys *command. The newer syntax is more readable and preferred.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-w'''||("without shell") When a Windows application is called, use a -w flag to tell BR not to call COMMAND.COM, but to call the application directly. It must be a program (i.e. "exe") and not a script. The Search Path is used ON SERVER only (if no slashes). This option does NOT open a DOS window. It does not use Bourne shell. It enables unfiltered return values. A Timeout or Ctrl-A kills the process. If -w is omitted, then a timeout or Ctrl-A does NOT kill the process, which can leave orphans.
|-valign="top"
|}

{|
|-valign="top"
|width="60"| ||Note- Currently -w is ignored in Unix standard models.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''blank'''||Application standard output is forwarded to the client. However the Bourne shell is utilized to initiate the process. The shelled program CAN be an executable script.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-p'''||("Page Standard Output") - This option applies to Unix server only. If -p is omitted, then output goes to screen without pausing
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-t9999'''||Wait up to the specified number of seconds (for client server only). If -t is omitted, wait the number of seconds specified in the [[SHELL LIMIT]] statement (see [[BRConfig.sys]] chapter for more information on [[SHELL LIMIT]])
|-valign="top"
|}
<br>

==Technical Considerations==
:1) Simultaneous pressing of the Ctrl key and the right- bracket (]) key has the same effect as the SYSTEM command's shell call, except that no command string is automatically executed upon access to the operating system. Pressing of these keys causes BR immediately access the operating system and display the system prompt. Between each operating system operation, a message about what action is required to return to BR will be displayed.
:2) Too frequent or careless use of the SYSTEM command can make it more difficult to move your program to a new operating system. ADS strongly recommends the use of the FILE$ function in programs and procedures which access the resident operating system. This function allows programs to test the type of operating system before proceeding with a system-dependent sequence of commands. While the FILE$ function can always be programmed in at a later date, you may be able to save a great deal of time and recoding by placing it in programs as they are written.
:3) When the system call (either Ctrl-] or the [[SYSTEM]] command) starts and then exits a secondary software program before returning to [[BR]], the [[CODE]] function will reflect any return code that the secondary program has passed to the operating system. As soon as BR is reactivated, the value of [[CODE]] can be tested to Verify whether or not the secondary program was successful. This is currently valid for Linux versions of [[BR]] only.
:4) [[BR]] passes all quotation marks within or surrounding the "string-expr" parameter back to the operating system.
:5) The screen may be cleared on some Linux terminals when the [[SYSTEM]] call is completed. If this is undesirable, see [[Terminal Considerations]] for a possible remedy.
:6) BR now searches PATHs during Windows shell call.
:7) Shell calls to Windows applications are no longer maximized.
:8) System calls -M -W -C -R now operate like their corresponding lower case flags. This case sensitivity had been suppressed to allow passing the flags to DOS, but that made the rules too obscure.
:9) Concerning Unix shell calls, the following flag combinations are prevented-
:-W and -C<br>
:-R and -C<br>
:-W and -R<br>
:In other words Without Shell, Restore Screen, and Continue are mutually exclusive options. If more than one is specified at a time, error [[2222]] is generated.

For disambiguation purposes, also see [[System Parameter]].

<noinclude>
[[Category:Commands]]
[[Category:Executive Commands]]
</noinclude>

BRlistener.exe

2019-01-18T17:23:38Z

GomezL:

The '''BRListener.exe''' file is a [[32 bit]] C++ application which serves as the [[connection agent]] for the server side of [[Business Rules!]] [[Client Server]] installations.

GENERAL: You must be logged in as [[Administrator]] in order to run Setup (BRListener.exe). (Or [[superuser]] for [[SCO]])

In order to run BRListener.exe, [[BRListener.conf]] must be properly configured.

Copy BRListener.exe to the proper [[System32]] folder.

If you have a BR Service running:

# Stop the BR Service on the machine where you intend to install. If your previous installation was done using InstallShield, use "stop BR service" from the Programs/BR Service icon list.
# You can also stop the BR Service by going to start, run, and typing "install /release".
# If your previous installation was done using InstallShield, you can remove it by using the Add/Remove Programs feature in the Control Panel. In this case, your [[BRConfig.sys]] and [[license file]] will not be removed. This is to make them available for subsequent use.
# When you run install, installation will modify your BRListener.conf file if your target directory is different from the one previously specified.

*BRListener ([[4.13]]) was changed to allow multiple label statements. It works in conjunction with a new build of the Client ([[4.12c]]).

The listeners recognize a qualifying prefix on [[brlistener.conf|CONF]] file statements. This supports having different listeners active at the same time. This permits testing in production client-server environments.

@release = 1.2 PORT=8555
@release = 2.0 PORT=8557
@release < 2.0 EXECUTABLE = /ads/sys/br.d/brserver
@release >= 2.0 EXECUTABLE = /ads/sys/br.d/brserver.new

The full syntax for the Windows brlistenerinstaller is:

brlistenerinstaller [/RELEASE] [/ALTERNATE alternate] [brlistener-path]

[/RELEASE] removes this service.
[/ALTERNATE alternate] uses an alternate service name. This will result in a service named BR_Listener-(alternate).

The second brlistener must be installed using the ALTERNATE keyword followed by a number. Under Linux this is handled by naming the listeners differently.

Persistent Login – Brlistener Release 2.0 in MULTISESSION Mode

A Client session ID is stored in the registry on the client side and associated with the user login name. This is maintained in memory by the BRlistener along with the user's login-name and password. The listener first checks the user's client session ID before prompting for login info.

[BRclient] communicates with [BRlistener] over TCP encrypted by TLS.

<noinclude>
[[Category:Files]]
[[Category:Client Server]]
</noinclude>

System (command)

2018-11-01T15:50:30Z

GomezL:

The '''System (SY)''' [[:Category:commands|command]] can do two things:
#Exit BR and return control to the [[operating system]], or
#Perform a '''shell call''' (an operating system command) while BR is active.

System can be executed from [[READY mode]], from a [[procedure file]], or with the [[Execute]] [[statement]].

==Using SYSTEM to exit BR==

Entering SYSTEM or it's short form, SY into the command line while in ready mode will exit BR. The same can be done using an [[Execute]] Statement or a procedure file.

[[image:Sy.jpg]]
[[image:System2.jpg]]

==Using SYSTEM to send commands to the operating system==
===Comments and Examples===
One use of the SYSTEM command is to run a program. In the following example, the SYSTEM command runs a program called BACKUP. By default, the first program waits until the other program has finished running, and then continues where it left off. The syntax includes the SYSTEM keyword and then the OS command, which in this case is the program name and location of the data to handle.

SYSTEM BACKUP C:\\*.DAT A: /S

When the same command is used with an asterisks preceding the program name, BACKUP, the BR program restores its own screen upon re-entry:

SYSTEM *BACKUP C:\\*.DAT A: /S

The statement below tests to see what type of operating system the program is running on, then executes the appropriate SYSTEM command for the operating system:

00100 IF FILE$(0)(1:3)="CON:" THEN EXECUTE "SYSTEM DIR" :!ELSE EXECUTE "SYSTEM ls -al /usr/BR"

===Using Batch Files with System Exits===

One use of the System command is to pass a return code to the host operating system as it permanently exits from BR. In the following example, a DOS [[batch file]] tests the return code ([[ERRORLEVEL]]) and performs a specific action according to its value.

BR PROC START
IF ERRORLEVEL 101 GOTO BADERROR
IF ERRORLEVEL 100 GOTO BACKUP
IF ERRORLEVEL 99 GOTO FORMAT
GOTO DONE
:BACKUP
BACKUPBR
:FORMAT
FORMAT A:
BRSTART
:BADERROR
ECHO BAD ERROR RETURN CODE >100
:DONE

If the command SYSTEM 100 were used to exit from BR while the above batch file was active, DOS would start a back-up procedure as soon as the BR exit was complete.

==Syntax==
SYSTEM [< code for OS>|[*]<OS command>]
[[Image:System.png]]

==Defaults==
:1) Return control to the operating system with a return code of zero.
:2) Do not restore screen.

==Parameters==
'''Code returned to OS''' parameter sets the value of the operating system return code which may be accessed using ERRORLEVEL in DOS batch files, and through the $? substitution in a Linux shell.

'''OS command''' parameter is an operating system command which BR passes to the operating system for execution. If the result of the command requires no operator action, BR is immediately reentered after the command finishes execution. Since BR is active during the entire process, all variable values and memory remain the same. When the '''OS command''' parameter is preceded by an asterisk, the BR screen is restored upon re-entry. The '''OS command''' parameter must be a '''string variable''' or a string enclosed in quotes.

Additional Flags that can alter the behavior of the shell call as follows:

{|
|-valign="top"
|width="60"|'''-E or -e'''||"Errors" indicates that any Operating System errors returned from the shell should be reported (as error 4300). Otherwise shell call operating errors are ignored. Occasionally Windows returns error values for fairly nominal exceptions. So the -e is provided as an error return sensitivity level flag. Further information can be obtained by querying SYSERR or SYSERR$
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-M or -m'''|| Run the program "minimized".
|-valign="top"
|width="60"| ||If a lower case m is specified, the process or program runs in a DOS command shell without displaying the DOS window, but with a corresponding icon on the task bar (effectively running the shelled process/program "minimized") This is only applicable in Windows. It is ignored on all other systems. If -m is omitted, the shelled program will open in a separate application window.
|-valign="top"
|}

{|
|-valign="top"
|width="60"| ||If an upper case M is specified, the same thing happens except the task does not appear on the taskbar. It runs invisibly. Furthermore [[SPOOLCMD]] calls use lowercase m.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-s'''||Server Shell Call (this is the default for Unix). If -s is omitted on Windows, then this shell call is performed on the client.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-@'''||If using Client Server, then it runs the program on the client. Client Shell Call is the default for Windows. If -@ is omitted, on Unix the shell call is performed on the server.
|-valign="top"
|width="60"| ||(search path not implemented on client)
|-valign="top"
|}

{|
|-valign="top"
|width="60"| ||Note- Ctrl-] ALWAYS performs a DOS shell call on the client.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-c'''||The main program "continues" to run while running the called one. Windows launches the task and resumes BR operation where it left off. For Unix, the command is framed with NOHUP and &. If -c is omitted, then the shell call waits til the process is finished (up to the # seconds specified in SHELL LIMITS) before returning to BR.

For [[OSX]] 10.6 you need

sys -c | (pipe) Shell commands with a pipe make BR Continue after executing the sys -c (continue), if you do not insert the | pipe, then the script does not run under Br [[4.18]] and up.
This was found on a Macintosh client that just updated to OSX 10.6.x

|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-r'''||"Restore" means that Windows always performs shell in a separate window. (Unix ignores shell standard out data). If -r is omitted. then Unix forwards standard out and stdin data to and from the client window.
|-valign="top"
|width="60"| ||Restores screen after running command on all systems. In windows, the screen is always restored, so this flag is essentially ignored. This is the same as sys *command. The newer syntax is more readable and preferred.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-w'''||("without shell") When a Windows application is called, use a -w flag to tell BR not to call COMMAND.COM, but to call the application directly. It must be a program (i.e. "exe") and not a script. The Search Path is used ON SERVER only (if no slashes). This option does NOT open a DOS window. It does not use Bourne shell. It enables unfiltered return values. A Timeout or Ctrl-A kills the process. If -w is omitted, then a timeout or Ctrl-A does NOT kill the process, which can leave orphans.
|-valign="top"
|}

{|
|-valign="top"
|width="60"| ||Note- Currently -w is ignored in Unix standard models.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''blank'''||Application standard output is forwarded to the client. However the Bourne shell is utilized to initiate the process. The shelled program CAN be an executable script.
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-p'''||("Page Standard Output") - This option applies to Unix server only. If -p is omitted, then output goes to screen without pausing
|-valign="top"
|}

{|
|-valign="top"
|width="60"|'''-t9999'''||Wait up to the specified number of seconds (for client server only). If -t is omitted, wait the number of seconds specified in the [[SHELL LIMIT]] statement (see [[BRConfig.sys]] chapter for more information on [[SHELL LIMIT]])
|-valign="top"
|}
<br>

==Technical Considerations==
:1) Simultaneous pressing of the Ctrl key and the right- bracket (]) key has the same effect as the SYSTEM command's shell call, except that no command string is automatically executed upon access to the operating system. Pressing of these keys causes BR immediately access the operating system and display the system prompt. Between each operating system operation, a message about what action is required to return to BR will be displayed.
:2) Too frequent or careless use of the SYSTEM command can make it more difficult to move your program to a new operating system. ADS strongly recommends the use of the FILE$ function in programs and procedures which access the resident operating system. This function allows programs to test the type of operating system before proceeding with a system-dependent sequence of commands. While the FILE$ function can always be programmed in at a later date, you may be able to save a great deal of time and recoding by placing it in programs as they are written.
:3) When the system call (either Ctrl-] or the [[SYSTEM]] command) starts and then exits a secondary software program before returning to [[BR]], the [[CODE]] function will reflect any return code that the secondary program has passed to the operating system. As soon as BR is reactivated, the value of [[CODE]] can be tested to Verify whether or not the secondary program was successful. This is currently valid for Linux versions of [[BR]] only.
:4) [[BR]] passes all quotation marks within or surrounding the "string-expr" parameter back to the operating system.
:5) The screen may be cleared on some Linux terminals when the [[SYSTEM]] call is completed. If this is undesirable, see [[Terminal Considerations]] for a possible remedy.
:6) BR now searches PATHs during Windows shell call.
:7) Shell calls to Windows applications are no longer maximized.
:8) System calls -M -W -C -R now operate like their corresponding lower case flags. This case sensitivity had been suppressed to allow passing the flags to DOS, but that made the rules too obscure.
:9) Concerning Unix shell calls, the following flag combinations are prevented-
:-W and -C<br>
:-R and -C<br>
:-W and -R<br>
:In other words Without Shell, Restore Screen, and Continue are mutually exclusive options. If more than one is specified at a time, error [[2222]] is generated.

For disambiguation purposes, also see [[System Parameter]].

<noinclude>
[[Category:Commands]]
[[Category:Executive Commands]]
</noinclude>

Multi-Line Textbox

2018-10-20T18:30:43Z

GomezL:

'''Multi-Line TextBoxes''' are accomplished in BR! by using an [[Input Fields]] [[statement]]. Here is a sample:

00010 DIM Buff$*2048
00012 LET Buff$='start with something'
00020 RINPUT FIELDS '#0,5,2,text 14/58/20,[D]s': Buff$

Syntax: Text [Rows]/[Columns]/[Max Char]

<noinclude>
[[Category:Widget]]
</noinclude>

Multi-Line Textbox

2018-10-20T18:30:28Z

GomezL:

Text

2018-07-09T17:06:39Z

GomezL: /* TEXT FIELD FORMAT */

== TEXT FIELD FORMAT==
As of 4.3

10 (R)INPUT FIELDS “row, col, TEXT rows/cols[/capacity], leading-attr, …”:

This format is similar to the C/V/G format with the ^ENTER_LF attribute except:
No trailing space padding or trimming occurs.

Rows and columns are specified.
Field capacity or newlines can cause a vertical scrollbar to appear.

Supported leading attributes are ^ENTER_LF (the default), ^ENTER_CRLF and ^NOWRAP.
The TEXT keyword will imply ^ENTER_LF unless ^ENTER_CRLF is specified.

^ENTER_LF (the default) causes the ENTER key to add LF characters to the data and to go to a new line left justified. The cursor will retrace the data when backspace or left-arrow is keyed, meaning the on-screen LF characters are represented by moving the cursor, instead of allowing it to rest on invisible LF characters.

^ENTER_CRLF may be specified in lieu of ^ENTER_LF. When that is the case, carriage returns entered are represented in the data as carriage-return-linefeed (CRLF) character pairs. ^NOWRAP may be specified to suppress word wrapping. When ^NOWRAP is active, data is entered on the current line until ENTER is keyed to go to the next line. Both horizontal and vertical scroll bars appear as needed.

Tabs are entered into the text. Shift-tab is ignored.

Home, End and the arrow keys all operate relative to the current line as opposed to operating on the text box as a whole.

Depressing the Control key causes the following keys to operate in the normal string entry mode:

*Enter – Returns control to the BR program
*Tab – Goes to the next control
*Shift-Tab – Goes to the prior control
*Home – Goes to the beginning of data or the first field
*End – Goes to the end of data or the last field
*Left-Arrow/Up-Arrow – Goes to the prior control
*Right-Arrow/Down-Arrow – Goes to the next control
* [[CurPos]] introduced.

A sample program to demonstrate TEXT entry is:
01000 ! Rep Text
01020 ! Test New Text Field Type
01040 dim TEXT$*300, TEXT$(1)*80
01060 print NEWPAGE
01080 rinput fields "3,10,text 4/40/300,uh": TEXT$
01100 print fields "10,10,c": TEXT$
01120 print TEXT$
01140 let STR2MAT(TEXT$, MAT TEXT$, CHR$(10))
01160 print MAT TEXT$

<noinclude>
[[Category:Format Specifications]]
[[Category:All Parameters]]
</noinclude>

Text

2018-07-09T17:05:07Z

GomezL: /* TEXT FIELD FORMAT */

Text

2018-07-09T17:04:02Z

GomezL: /* TEXT FIELD FORMAT */

== TEXT FIELD FORMAT==
As of 4.3

10 (R)INPUT FIELDS “row, col, TEXT rows/cols[/capacity], leading-attr, …”:

This format is similar to the C/V/G format with the ^ENTER_LF attribute except:
No trailing space padding or trimming occurs.

Rows and columns are specified.
Field capacity or newlines can cause a vertical scrollbar to appear.

Supported leading attributes are ^ENTER_LF (the default), ^ENTER_CRLF and ^NOWRAP.
The TEXT keyword will imply ^ENTER_LF unless ^ENTER_CRLF is specified.

^ENTER_LF (the default) causes the ENTER key to add LF characters to the data and to go to a new line left justified. The cursor will retrace the data when backspace or left-arrow is keyed, meaning the on-screen LF characters are represented by moving the cursor, instead of allowing it to rest on invisible LF characters.

^ENTER_CRLF may be specified in lieu of ^ENTER_LF. When that is the case, carriage returns entered are represented in the data as carriage-return-linefeed (CRLF) character pairs. ^NOWRAP may be specified to suppress word wrapping. When ^NOWRAP is active, data is entered on the current line until ENTER is keyed to go to the next line. Both horizontal and vertical scroll bars appear as needed.

Tabs are entered into the text. Shift-tab is ignored.

Home, End and the arrow keys all operate relative to the current line as opposed to operating on the text box as a whole.

Depressing the Control key causes the following keys to operate in the normal string entry mode:

*Enter – Returns control to the BR program
*Tab – Goes to the next control
*Shift-Tab – Goes to the prior control
*Home – Goes to the beginning of data or the first field
*End – Goes to the end of data or the last field
*Left-Arrow/Up-Arrow – Goes to the prior control
*Right-Arrow/Down-Arrow – Goes to the next control
* [[CurPos]] introduced.

A sample program to demonstrate TEXT entry is:
01000 ! Rep Text
01020 ! Test New Text Field Type
01040 dim TEXT$*300, TEXT$(1)*80
01060 print NEWPAGE
01080 rinput fields "3,10,text 4/40/300,uh": TEXT$
01100 print fields "10,10,c": TEXT$
01120 print TEXT$
01140 let STR2MAT(TEXT$, MAT TEXT$, CHR$(10))
01160 print MAT TEXT$

<noinclude>
[[Category:Field Specifications]]
[[Category:All Parameters]]
</noinclude>

Copy

2017-09-25T14:35:37Z

GomezL: /* Technical Considerations */

The '''Copy (COP)''' [[command]] copies one or more files in numerous ways:
# From one directory to another on the same or different disks.
# From one place on a directory to another place on the same directory. In this case you must give the resultant copy a different name.
# From a file to a printer.
# The -S option has been added to allow copying of a file which is currently open. The file to be copied must be opened for sharing ([[Shr]] or [[ShrI]]). If it is opened [[NoShr]], a file sharing violation error [[4148]] will occur when Copy with -S is attempted.

==Comments and Examples==

When using the copy command, you may specify a drive letter or number, and a path. You may also use [[wildcard characters]] '''?''' and '''*''' in place of alphanumeric characters in file names. [[Redirection symbols]] '''>''' and '''>>''' redirect screen output to a printer, a file, or another device.

The following command copies the file SAMPLE from the default directory of a disk in drive A to the default directory of a disk in drive B. The file name remains the same:

COPY A:SAMPLE B:

The next command copies the file SAMPLE from the subdirectory \TEST1 on a disk in drive A to the subdirectory \TEST2 on the same disk. If TEST2 does not exist as a subdirectory, Business Rules names the file TEST2 and places it in the default directory on the default drive:

COPY A:\TEST1\SAMPLE A:\TEST2

The following command copies SAMPLE from one place in the subdirectory \TEST1 on a disk in drive A to another place in the same subdirectory. The copied file is renamed SAMPLE1:

COPY A:\TEST1\SAMPLE A:\TEST1\SAMPLE1

The next command copies the file SAMPLE from the default directory of a disk in drive B to a printer attached to the first parallel port:

COPY B:SAMPLE PRN:

The same file can be copied to the printer at the first serial port with the following command:

COPY B:SAMPLE AUX:

The next command copies all files with names starting with SAMPLE, a period and any extension from a disk in drive A to a disk in drive B. The '''D''' in the command causes deleted records to be removed from any of the SAMPLE files which are internal files. The '''C''' in the command causes Business Rules! to prompt for a diskette change if the disk in drive B fills up before the copying process is completed.

COPY A:SAMPLE.* B: -DC

The following example copies the internal file SAMPLE from a disk in drive A to a disk in drive B, setting the record length of the new file at 128:

COPY A:SAMPLE B: -128

As of 4.2, COPY supports copying a print file unaltered to a printer:
COPY PRT\EDITLIST.255 DIRECT:/HP4

==Syntax==
COPY <from file> {[AUX:]|[PRN:]|[<to file>]} {[>[>]<output file>]|[PRINT]} [<[[copy option|-option]]>][-...]

[[Image:Copy.png]]

==Defaults==
# Copy the file, using the current file name, into the current directory on the current drive.
# Display a log of all performed actions in the [[command console]].

==Parameters==

Copy requires the '''from file''' parameter, which provides the information necessary for identifying the file or files you wish to copy.

After the '''from file''', you may specify the optional parameter '''to file''', which is the name (and path, if required) of the file you are creating.

'''PRN:''' allows you to copy the file indicated in the '''from file''' to the printer attached to the first parallel port on DOS systems. This string must be translated with the [[SUBSTITUTE]] specification in [[BRConfig.sys]] to specify the appropriate output device on [[Linux]] systems.

'''AUX:''' copies the '''from file''' file to the printer attached to the first [[serial port]]. This string must be translated with the SUBSTITUTE specification for Business Rules! Linux versions.

In place of the '''PRN:''' or '''AUX:''' parameters, you may also specify COM1:, COM2:, LPT1:, LPT2:, etc. to indicate the port to which your printer is attached (see the drive discussion in the Definitions chapter for more information). These device references will operate properly only on Business Rules DOS versions. They may be mapped to new references with the BRConfig.sys file's SUBSTITUTE specification.

The redirection symbol in the '''>file-ref''' parameter saves messages that normally appear on the screen to the specified file. The '''PRINT''' parameter sends these same messages to the printer rather than to the screen or a file.

'''Option''' may be one or more of several optional parameters. Such option(s) can follow the copy command when separated from the rest of the command with a dash:

{|
|-valign="top"
|width="10%"|'''-A'''||tells the system to copy only files that have the archive bit on. The system turns the archive bits in the original files off when the copying process is complete. This option applies to Business Rules! [[DOS]] versions only.
|-valign="top"
|width="10%"|'''-C'''||prompts for a diskette change if the disk is full.
|-valign="top"
|width="10%"|'''-D'''||omits deleted records from the copy, but responds only when used with internal files. It is important to rebuild all related index files after using this option.
|-valign="top"
|width="10%"|'''-N'''||prevents the screen from showing a log of performed actions.
|-valign="top"
|width="10%"|'''-P'''||causes scrolling to pause after a full screen of information has been displayed. (Press <CR> for next screen.)
|-valign="top"
|width="10%"|'''-S'''||option has been added to allow copying of a file which is currently open. The file to be copied must be opened for sharing (SHR or SHRI) if it is opened NOSHR, error [[4148]] (file sharing violation) will occur when COPY with the -S is attempted.
|-valign="top"
|width="10%"|'''-V'''||requires the operator to Verify each action before Business Rules! performs it.
|-valign="top"
|width="10%"|'''-#'''||represents output file record length. Internal file record lengths are shortened or expanded (with blanks) to match the number you indicate (version [[2.04]]+). When specified with other options, the record length must always be last.
|-valign="top"
|}

==Technical Considerations==
Prior to version [[3.0]] the -S switch meant preserve space. That syntax is no longer supported. -s now refers to file sharing as noted above.

COPY to a printer :DEVICE was being rejected. COPY to a PRN: or WIN: device is still illegal. (Use TYPE with redirection for PRN: and WIN:)

While the [Source] Directory is very flexible supporting *, *.* & *.*.* to include "All Files", the [Destination] Directory is less flexibe.

Examples:

Copy Source\* Destination
* This sample will copy all files to Destination - This is the preferred syntax for copy

Copy Source\* Destination\*
* This sample will copy all files to Destination but will Remove any Extension so "Sample.wb" will become "Sample"

Copy Source\* Destination\*.*
* This sample will copy all files to Destination but complex files like "Sample.wb.brs" will become "Sample.wb"

Copy Source\* Destination\*.*.*
* This sample will copy all files to Destination complex files like "Sample.wb.brs" will become "Sample.wb.brs" - Files with more "." would also be truncated.

==Client Server==

COPY filename @:filename (@ indicates Client system)

Copies filename TO client current directory

(Or specify @:pathname relative to client current directory.)

COPY @:filename filename

Copies filename FROM client current directory.

If you are trying to use [[absolute paths]] on the [[client]], specify @::

This is really most useful with [[OPEN:]] and [[SAVE:]] file selection browsers.

Use caution when using absolute paths on the client because [[permissions]] and [[file systems]] can be different from one computer to another.

Normally BR will use the [[startup directory]] to copy things and you can change this in the shortcut if needed.

<noinclude>
[[Category:Commands]]
[[Category:File Management Commands]]
</noinclude>

Copy

2017-09-25T14:34:44Z

GomezL: /* Technical Considerations */

TRANSLATE

2017-06-25T18:33:15Z

GomezL: Sample program to create a "Translate" file.

TRANSLATE=<file ref>
The "TRANSLATE=file-ref" parameter indicates that character translation is required for all input or output of C, V, G, N, ZD or PIC strings. The file-ref must identify either a 256- or 512-byte file. The first 256 bytes are used as an input translation table.

This optional parameter allows the translation of data to or from another collating sequence. Internally, all computers represent letters of the alphabet, punctuation marks and even digits according to an arbitrary numbering scheme. Today, the common coding schemes is ASCII (American Standard Code for Information Interchange). In the past, EBCDIC (Extended Binary Coded Decimal Interchange Code) was used, especially for main frames. Probably the most common use of the TRANSLATE= capability is translating files between ASCII and EBCDIC.

An example of the difference between ASCII and EBCDIC is that the uppercase letters A, B and C are represented by 65, 66 and 67 respectively in ASCII; but in EBCDIC, they are 193, 194 and 195. Also, in ASCII, the blank or space character is represented by the number 32 while in EBCDIC it is number 64. Although all systems that currently run Business Rules the ASCII coding scheme, some older computer systems use EBCDIC.

Used in [[Open]] statements: [[Open Display|Display]], [[Open Internal|Internal]], [[Open external|external]], and [[Open communications|communications]].

The translate table is made up of 256 characters starting with the replacement value for CHR$(0), and ending with CHR$(255).

See also the [[XLATE$]] internal function.

Sample Programs to create "TRANSLATE" tables:
* This program created a TRANSLATE table that will only allow "Common Characters", and filter out unwanted symbols. Tihs is commonly used when importing data from Electronic Data files.

00100 DIM Tbl$*512,Edi_Xlate$*512
00110 LET Edi_Xlate$ = Rpt$(" ", 32)&" !"&Chr$(34)&"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~*"&Rpt$(" ", 128)
00120 LET Edi_Xlate$(11:11)=Chr$(10) : LET Edi_Xlate$(14:14)=Chr$(13)
00130 LET Tbl$=Edi_Xlate$&Edi_Xlate$
00140 OPEN #1: "NAME=EDI_XLATE,RECL=512,replace",EXTERNAL,OUTPUT
00150 WRITE #1,USING 160: Tbl$
00160 FORM Pos 1,C 512
00170 CLOSE #1:
00180 END

<noinclude>
[[Category:All Parameters]]
</noinclude>

TimeOut

2017-01-26T14:54:59Z

GomezL:

The TIMEOUT error trap can be used with the WAIT parameter The WAIT= parameter on INPUT/RINPUT statements will trigger an error based on the [WAIT] settings.

<noinclude>
[[Category:Terminology]]
[[Category:Error Conditions]]
</noinclude>

4.31

2017-01-26T14:49:15Z

GomezL: /* Version 431ga */

Release Notes 4.31h

The prior versions were [[4.1]] and [[4.2]] and [[4.30]].

As Of 06/30/2015

===Version 430+zmf===
Added final keepalive send before turning off the copy_sort_index_sys_cmd flag.<br>
Revised SSL shutdown wait loop.<br>
Set SSL shutdown start time (this was causing a reconnect timeout because it wasn't set).<br>

Changed close listener wait from 5 seconds down to 2 seconds.

===Version 431===
Made connection status global.<br>
Prevented sending KB char to server when reconnecting.<br>
Level 14 logging of client-server messages (debug version only).<br>

===Version 431a===
Error [[413]] message (window not on top) clarified.<br>
Suppressed logging in checkSSL until CS version match established.<br>
Polished up source terms and spelling errors - no object affect.<br>
Revised order of Menu Display responses to brserver.<br>
Allowed BR MSG() keyboard function to work when Command Console is the active window.<br>

===Version 431b===
Corrected exit pre-processing for both normal and unattended modes.<br>
Changed "blank" references to "blanksPtr".<br>
Completely revised UNATTENDED_INPUT_CALL processing.<br>
Changed HTTP "Client-Inquiry" to "PAGE-URL".<br>
Added HTTP support for "PAGE-HEADER".<br>

Revised error messages that pertain to system errors to indicate as much.<br>
Added some DLL errors to the BRERR$ definitions file. <br>

Added support for getting cookies via http. <br>
Cleaned up some errors causing dumps during termination.<br>
Switched log information to message boxes for errors that occur while uploading the client DLL.<br>

Revised SYSTEM LOGOFF to not fail to zero session IDs when issued to BRSERVER.
This is to fix the problem where Windows reports active sessions where there is none, causing WSID's to be assigned incorrectly.

===br4.31ce===
Fixed the problem where NOWAIT was being ignored if OPTION 38 was on.

===Version 431d===
Removed DUPLICATE FLOWSTACK code.<br>
Revised ENDRUN processing of libraries .. set currentFileChain and currentScreenChain values during library PCB detachment. This fixed a problem detaching a NOFILES library.<br>
Lots of changes to run.cpp for flowstack processing and readability.

Changes from prior editions:<br>
*SQL and SYSERR fixes.
*Suppress error 867 on a radio button.
*Don't show LABELS, and BUTTONS in STATUS command when GUI is off.
*Removed an error trying to close an already closed window.

===Version 431d===
Removed DUPLICATE FLOWSTACK code.<br>
Revised ENDRUN processing of libraries .. set currentFileChain and CurrentScreenChain values during library PCB detachment. This fixed a problem detaching a NOFILES library.<br>
Lots of changes to run.cpp for flowstack processing and readability.

Changes from prior editions:
*SQL and SYSERR fixes.
*Suppress error 867 on a radio button.
*Don't show LABELS, and BUTTONS in STATUS command when GUI is off.
*Removed an error trying to close an already closed window.

Mismatched packets are ignored when:
*Ignore failure or keyboard return or we're terminating.
*Ignore failure is now on for cleanupNormalExits and closeWindowZero.
*When a flow stack error occurs, BR now returns error 9003.
*Current program name is stored on the client in a global when the status line is updated.

Corrected processing mismatched packets for CALL_FUNCTION_NULL_ACTION.<br>
Added transfer of current line number to client.

===Version 431e===
Revised remote.cpp ignoring of unmatched packets.<br>
Polished up the transfer of program name and line number to the client.<br>
Renamed clientOnly to ignoreFailureClientOnly<br>

Added error [[0738]] - SELECT should be a trailing attribute.<br>
This error occurs when SELECT is used as a leading attribute without the ^.

===Version 431f===
Added path to log message referencing brCrashDumpUploader.<br>
Added support for DEBUG INTERNAL COREDUMP_SERVER.<br>
Now we pass currentUserLineNumber to client in all models.<br>

===Version 431fb===
If an invalid trailing attribute is allowed via OPTION 38 BR was occasionally dumping.<br>
Keepalive timeouts are prevented when sitting in a message box.<br>

===Version 431fc===
Created and applied MINOR_IO_ERROR (valued at 3). This relates to log levels.

===Version 431fe===
Ignore mismatched packets on menu display.<br>
Suppress timeout during MSGBOX call.<br>
Fix 2D control header font colors.<br>
Removed V42 references.<br>
Fixed a problem re loading library modules RELEASE.<br>
Corrected logging before DLL loaded.<br>

===Version 431ff===
Revised PIC processing.

===Version 431fg===
Revised PIC, and DATE processing to test formatted field capacity when inserting characters. (FMT runs only in overtype mode so it isn't affected.)<br>
Statusline ROWCOL now defaults to off.<br>

===Version 4.31g===
Upgrade to VS 2010.<br>
Fixed problem with license verification only to first decimal point of version number.<br>
Change made to prevent looping during shutdown.<br>
Client changed to let the server determine whether or not to terminate.<br>
Graceful client shutdown. See build notes for details.<br>

===Version 431ga===
Removed UPGRADE code flag from source.<br>
Fixed date problem that was created by recent FMT and PIC revisions.<br>
KREC is updated during printing only when the PUSH level is zero.<br>
For HTTP client, the log data showing CURL information was extended.<br>
The HTTP client now does a GET if no printing occurs prior to a LINPUT.<br>
A display parameter was added to the fldedit function. This function is used
by FMT, PIC DATE and other field types to see if typing is done in excess of field capacity.<br>

===Version 4.31gc===
More detail is provided when a database connection attempt is unsuccessful.<br>
Corrected failures pertaining to deleting characters in non-currency PIC masks, especially with backspace. <br>
ENV$ and STATUS ENV search strings are now enhanced. See the wiki for details.
Internal documentation updated.<br>

===Version 4.31h===
Removed references to abort. Used CANCEL instead.<br>
Error 370 was dropping the program file during a REPLACE where OPTION 60 was on and the program contained 4.2+ statements. This is fixed.<br>
The XLATE system function was extended to support translation to and from UTF-8. <br>
Added error 438 "Input String not valid UTF-8 format."<br>
Removed system functions UPSI, LINEBUFFER, XPOS, CFIELDS$, and CHYRON$.<br>
Days(date("H:M:S"),"H:M:S") was producing a date with the first day of the current month. This was changed to the current day.<br>
RINPUT was positioning the cursor according to the previous Input. This was fixed.<br>
The Web Server support now does not tamper with binary data. It was converting to UTF-8.<br>
An internal pointer was adjusted to prevent crashes when the stack overflows.<br>
Default stack sizes were revised.
work_size = 500000, /* work stack size */
flow_size = 100, /* flow stack size */
rpn_size = 1000, /* rpn stack size */
for_size = 100; /* for-next stack */

[[Category:Release 4.3]]
[[Category:Release Notes]]

4340

2017-01-26T14:48:30Z

GomezL:

{{Error
|4340
|([[4.2]]+) An HTTP error has occurred / ([[4.16]]-) Novell error 140
|In BR! version [[4.20]], This error code was re-introduced to be used in conjunction with the [[SysErr]] and [[SysErr$]]. It indicates that an error occurred in the underlying operating system. Reference SysErr$ for a description of the actual error.

As of BR! version 4.16 This Novell error no longer exists.

|Use the SysErr & Syserr$ to troubleshoot the errors. The HTTP interface in Business Rules uses the CURL library, and these errors should be documented in the Curl Site.
}}
[[Category:Error Codes]]

DATABASE

2017-01-23T20:20:23Z

GomezL: /* ODBC-MANAGER */

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 | ? } | PASSWORD=<encrypted-password> ]
The ? indicates prompt

Example:
EXECUTE 'CONFIG DATABASE CLS_Data DSN="MyData"'

===CONNECTSTRING (DSN Less)===
Syntax:
CONFIG DATABASE <db-ref> CONNECTSTRING="<Driver>={Microsoft Access Driver (*.mdb)} DBQ=C:\inetpub\wwwroot\BegASP\Chapter.14\Contact.mdb"

'''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;Login Name=username;Password=BR_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]

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$

[[Category:All Parameters]]

DATABASE

2017-01-23T20:19:25Z

GomezL: /* ODBC-MANAGER */

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 | ? } | PASSWORD=<encrypted-password> ]
The ? indicates prompt

Example:
EXECUTE 'CONFIG DATABASE CLS_Data DSN="MyData"'

===CONNECTSTRING (DSN Less)===
Syntax:
CONFIG DATABASE <db-ref> CONNECTSTRING="<Driver>={Microsoft Access Driver (*.mdb)} DBQ=C:\inetpub\wwwroot\BegASP\Chapter.14\Contact.mdb"

'''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;Login Name=username;Password=BR_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]

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$

[[Category:All Parameters]]

Encryption

2017-01-19T01:33:42Z

GomezL: /* Asymmetric Encryption */

(As of 4.30)

'''Encryption''' encompasses a number of different operations. These operations can be used independently or in combination to meet different needs. We use industry standard encryption available through OpenSSL. Three technologies are widely used for encrypting data. BR supports the first two listed below through its [[ENCRYPT]] and [[DECRYPT]] [[internal functions]].

==Overview of Two Encryption Methods==

1. '''Symmetric key ciphers''' – where the same key is used to encrypt and decrypt data. You can specify a key to encrypt some data and later use the same key to decrypt the data.

2. '''Hashing routines''' – one way routines that take data and convert it to a hash value. Sometimes these are thought of as checksums such as MD5 sum. Hash values are always the same length regardless of how big the hashed data is. A 10 gb file will have a hash result that is the same length as a 200 byte file. Hashing routines have a number of specific uses, including:
*Verify that data has not changed.
*Verifying that two files are the same.
*Validating passwords – this is based on the concept that if two values have the same hash value the values are equal. Using this technique improves security because it allows a server to store passwords in an unrecoverable format. Even the server software is unable to regenerate the original password. It is only capable of checking if the hash of a password matches the stored password hash.

==Symmetric Key Ciphers==
There are two encryption functions in the BR language, [[ENCRYPT$]] and [[DECRYPT$]].

ENCRYPT$(Data$ [,Key$ [,Encryption-type$ [,Initialization-vector$]]])
DECRYPT$(Data$ [,Key$ [,Encryption-type$ [,Initialization-vector$]]])

Data$ - The data to be encrypted

Key$ - The secret key to be used for encryption. If not specified this value will come from an OPTION 66 statement.

Encryption-type$ - The type of encryption to be done. If not specified, a common high strength encryption type will be employed. This is described in more detail below, but is generally only useful for interfacing with other typically non-BR programs.

Initialization-vector$ - This is an arcane part of encryption standards. It exists to prevent attackers from being able to tell whether the unencrypted data has changed. This is described in more detail below, but is general only needed when interfacing with other non-BR programs.

== Interfacing With Other Programs (encryption type and initialization vector)==
There are a number of different types of encryption that BR supports through OpenSSL: AES, BLOWFISH, DES, triple DES, RC4 and RC2. Most symmetric key ciphers are block ciphers meaning that they encrypt one block at a time. This means if you have a bit message, it is broken up into multiple blocks and each block is encrypted. The block size can be set as (128, 192, 256) bits. Some encryption types don't support all of these values so STATUS ENCRYPTION should be checked to see what encryption types are available in BR. Besides block size, there are also various schemes for blocking data. One might expect that using 256 bit blocking would simply take every 32 bytes and call it a block. This is not done though because there is a possibility that this would cause patterns in the encrypted data. To prevent this, there are various schemes known as codebooks which change the way data is blocked. Wikipedia explains this in more detail. If the encryption type is not specified AES:256:CBC:128 will be used. To be compatible with other programs the entire encryption type must be specified (cipher: key length: codebook: initialization vector length).

Initialization-vector – this is used to cause the same data encrypted with the same key to have a different encrypted result. This is significant because otherwise an attacker looking at data seeing the same encrypted result twice would know that the key and the unencrypted data have not changed. Regardless of whether or not you are concerned about this potential security issue, the standard encryption methods require this value so interfacing with other programs may require you to use it. It is a common practice to use a random number for this value and store the value at the beginning of (ahead of) the encrypted result. This is what BR does if this parameter is omitted.

As an example:
ENCRYPT$(“test”,“key”)
Produces a string containing “random number initialization vector”&”encrypted result”.

If the initialization vector is explicitly specified as in:
ENCRYPT$(“test”,“key”,“AES:256:CBC:128”,“RANDOM”)
the result would be simply “encrypted result”.

DECRYPT$ has the same arguments as ENCRYPT$ with the exception of the first parameter which is the encrypted data. DECRYPT$ expects to be used with the same key$, encryption-type$, and initialization-vector$ as was used to encrypt the data. As with ENCRYPT$, if key$ is not specified, the value from the OPTION statement will be used. If encryption-type$ is not specified, “AES:256:CBC:128” will be used. If the initialization vector is not specified, it will be assumed that the encrypted data starts with an initialization vector.

==Hashing Routines==
Three common forms of hashing are allowed in BR. They are MD5, SHA, and SHA-1. These are also provided through the ENCRYPT$ function specifying a null key$ value:

ENCRYPT$(data$, “”, “MD5”) ENCRYPT$(data$, “”, “SHA”) ENCRYPT$(data$, “”, “SHA-1”)

Hashing is also referred to as Message Digests or digests. This is what the MD in MD5 means. There is no way to restore data that has been hashed. Hashing is a one way function so DECRYPT$ will yield an error.

==Asymmetric Encryption==
Asymmetric key encryption is also known as public/private key encryption.

Public/private keys are created as a pair by a key generator. They are a pair, and it is not possible to have two public keys for the same private key or vice versa. With regard to public/private key pairs, what one key encrypts the other key can decrypt, and neither key can decrypt what it has encrypted. When a private key is used to encrypt data, the result is called a signature because everyone who has the public key can decrypt it.

This technique is used for:

Signing (using certificates) – A private key can be used to sign data. The result of such signing can be tested/validated with the corresponding public key.

Data encryption – A public key can be used to encrypt data. This data can then only be decrypted by the corresponding private key.

Hashes and signing are different but used together. Rather than signing a large block of data which would create a large signature, only the hash is signed to create much smaller fixed length signature data. When verifying a large block of signed data, the data is used to create a hash value and the hash value is compared to a decrypted signature.

Asymmetric encryption is not accessible through the BR ENCRYPT$, DECRYPT$ functions. However, it is used by our SSL client server connections and HTTPS. Certificates are most commonly used by SSL and HTTPS and are less useful for other application processes.

In the Client Server model the client knows the server’s public key and the server uses its private key to encrypt and decrypt. BRclient.exe connects to BRListener.exe by opening an SSL socket on the server using DHE_RSA-AES256-SHA Encryption. Handshaking is performed using a private key stored on the server within BRListener. Once authenticated, the socket is then passed to BRServer.exe for actual data processing.

Encryption is invoked by Business Rules HTTP support as follows:

CONFIG HTTPS PORT port-number [ LOG file-pathname ]
CONFIG OPTION 66 private-key-file-encryption-password
OPEN #400: “HTTP=SERVER”, DISPLAY, OUTIN

The BRSERVER executable directory must contain two files:

https-private.pem
https-cert.pem

These files are made by the following commands under Linux, MAC and cygwin for Windows: openssl req -new -x509 -out httpserver.pem -days 10000

(this will prompt for the OPTION 66 password)

mv privkey.pem https-private.pem
mv httpserver.pem https-cert.pem

This port specific service can then be accessed with browsers. When the specified port is accessed through a browser, BR establishes an HTTPS connection rather than an HTTP connection.

==Signing and Certificate Processing Industry Standards==
The purpose of certificates is to verify the authenticity of unencrypted data.

Certain companies are authorized by the government to act as a Certificate Authority (CA). These companies (e.g. Verisign) issue electronic certificates which can be used to issue second level certificates. Certificates can have expiration dates and the line of authority extends from a CA to any number of levels (but a chain is only as strong as its weakest link). Certificates contain a list of signatures (described below) that trace back to a CA as follows:

When a company needs to obtain a certificate from a CA, it prepares its own certificate and sends it to the CA for signature. Creating a certificate requires the pre-production of a private and public key pair. The certificate text properly identifies the signing authority, the owner of the certificate, and the owner’s public key. The signing authority externally verifies the identity of the owner before signing it. The CA provides the signature and the CA’s own public key for validating it.

Browsers know the CA identities and their public keys. A browser can verify a CA signed cert by hashing it, decrypting the signature with the known public key and matching the decrypted signature against the hash total.

Any company that is issued a (self-prepared authority signed) certificate by a CA can sign second level certificates issued to third parties. A second level cert contains the parent (CA issued) certificate, and includes the public keys of the issuer and the recipient. The signature is essentially the encrypted hash total of ‘itself plus all of its ancestors’. Additional levels each contain the chain of certificates leading from a CA issued cert to itself. By including public keys along with the identities of the owners, certificates become tools for validating the signatures of their owners. When signed data (with an encrypted hash total) is sent to clients the signatures insure that the data has not been altered along the way.

Ostensibly, a certificate cannot be counterfeited because it requires the signature of its parent which can only be produced with its parent’s private key. By providing both public keys ( signer and recipient ) in all certs along with signatures, a non-forgeable or alterable chain is established.

====Example:====
CA public key – known to browser
certificate 1 (signed by CA – contains owner’s public key)
certificate 2 (signed by second level - includes certificate 1 - contains owner’s public key)
final certificate (signed by third level - includes certificate 2 - contains owner’s public key)

A browser would find the CA information in the certificate and check to see if it is in the browser’s internal list. If not, it fails. Then it verifies that certificate 1 (which ends up being part of the final certificate) has been signed by the CA. After that it checks certificate 2 and verifies that it has been signed by the owner of certificate 1. Finally it checks the final certificate and verifies that it has been signed by the owner of certificate 2.

To assure the line authority of any certificate, the public keys are associated with signers, not with documents.

<noinclude>
[[Category:Definitions]]
</noinclude>