SQL

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 connect 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

1. Begin processing with a WRITE statement.
2. 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.
3. 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.
4. Once SQL has been populated by an IOlist, it may be reused with the same values by issuing a WRITE with no IOlist.
5. READ IOlist variable associations are positional with each READ accessing one row of values.

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

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.