4.3 - Revision history

GomezL: /* Open SQL */

2017-01-16T19:39:50Z

Open SQL

← Older revision		Revision as of 19:39, 16 January 2017
Line 346:		Line 346:
	B$ = BR_DATE(vtring-var$ [,"format"]) ! upack DB date value		B$ = BR_DATE(vtring-var$ [,"format"]) ! upack 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"

	=== (Future) Legacy SQL Support===		=== (Future) Legacy SQL Support===

John at 20:55, 18 October 2016

2016-10-18T20:55:15Z

← Older revision		Revision as of 20:55, 18 October 2016
Line 347:		Line 347:


	~~{{Future~~		=== (Future) Legacy SQL Support===
	\|===Legacy SQL Support===

	!!! This feature is a planned addition, and is not implemented yet!!!		!!! This feature is a planned addition, and is not implemented yet!!!
Line 371:		Line 370:
	positions and formats to dictionary field names. These names are then used to		positions and formats to dictionary field names. These names are then used to
	access the database during READ and WRITE operations.		access the database during READ and WRITE operations.
	}}

	==Other Developments==		==Other Developments==

John: /* Legacy SQL Support */

2016-10-18T20:53:15Z

Legacy SQL Support

← Older revision		Revision as of 20:53, 18 October 2016
Line 346:		Line 346:
	B$ = BR_DATE(vtring-var$ [,"format"]) ! upack DB date value		B$ = BR_DATE(vtring-var$ [,"format"]) ! upack DB date value

	===Legacy SQL Support===
			{{Future
			\|===Legacy SQL Support===

	!!! This feature is a planned addition, and is not implemented yet!!!		!!! This feature is a planned addition, and is not implemented yet!!!
Line 369:		Line 371:
	positions and formats to dictionary field names. These names are then used to		positions and formats to dictionary field names. These names are then used to
	access the database during READ and WRITE operations.		access the database during READ and WRITE operations.
			}}

	==Other Developments==		==Other Developments==

Laura at 20:18, 5 October 2015

2015-10-05T20:18:57Z

← Older revision		Revision as of 20:18, 5 October 2015
Line 13:		Line 13:

	===Three New Read Types===		===Three New Read Types===
	Notes-		Notes:
	Auto Resizing applies		Auto Resizing applies

	Selection Type must be ALL		Selection Type must be ALL

Line 26:		Line 27:

	HEADERS – The original PRINT FIELDS HEADER values.		HEADERS – The original PRINT FIELDS HEADER values.
	e.g. INPUT FIELDS "row,col,LIST rows/cols, HEADERS" :		e.g. INPUT FIELDS "row,col,LIST rows/cols, HEADERS" : (MAT HEADINGS$,MAT WIDTHS, MAT FORMS$)
	(MAT HEADINGS$,MAT WIDTHS, MAT FORMS$)

	COLCNT - The number of columns established by the header arrays.		COLCNT - The number of columns established by the header arrays.
	e.g. INPUT FIELDS "row,col,LIST rows/cols, COLCNT, ALL" : numeric-variable		e.g. INPUT FIELDS "row,col,LIST rows/cols, COLCNT, ALL" : numeric-variable

	These are for generalized library routines to inspect passed controls.		These are for generalized library routines to inspect passed controls.


	SORT_ORDER - Provides a value of zero for each unsorted column and gives the ascending sequence of column sorts that have occurred. If a column has been reversed (double sorted) it's value will be negative.		SORT_ORDER - Provides a value of zero for each unsorted column and gives the ascending sequence of column sorts that have occurred. If a column has been reversed (double sorted) it's value will be negative.
Line 39:		Line 38:
	e.g. INPUT FIELDS "row,col,LIST rows/cols, SORT_ORDER, ALL" : numeric-array		e.g. INPUT FIELDS "row,col,LIST rows/cols, SORT_ORDER, ALL" : numeric-array
	Given the following history of sorting a four column GRID:		Given the following history of sorting a four column GRID:
	• column 1 (descending most ~~rescent~~)		• column 1 (descending most recent)
	• column 2 (ascending first sorted)		• column 2 (ascending first sorted)
	• column 3 (not sorted)		• column 3 (not sorted)
Line 55:		Line 54:
	The syntax for sorting an array is currently:		The syntax for sorting an array is currently:

	PRINT FIELDS "nn,nn,GRID 10/40,SORT": { column number }		PRINT FIELDS "nn,nn,GRID 10/40,SORT": { column number }

	This has been extended to allow a numeric array of instead of a scalar.		This has been extended to allow a numeric array of instead of a scalar.
Line 61:		Line 60:
	returns. The new format is:		returns. The new format is:

	PRINT FIELDS "nn,nn,GRID 10/40,SORT": { column-number \| numeric-array }		PRINT FIELDS "nn,nn,GRID 10/40,SORT": { column-number \| numeric-array }

	Where the numeric-array has one element for each column left to right. BR will resort the columns in the requested order.		Where the numeric-array has one element for each column left to right. BR will resort the columns in the requested order.

Laura: /* Grid and List Changes */

2015-10-05T19:45:45Z

Grid and List Changes

← Older revision		Revision as of 19:45, 5 October 2015
Line 8:		Line 8:
	Arrays are automatically resized when receiving data from 2D INPUT operations. This also applies to grouped arrays. Automatic resizing only applies to one dimensional arrays and does not occur when INPUTing into two dimensional arrays.		Arrays are automatically resized when receiving data from 2D INPUT operations. This also applies to grouped arrays. Automatic resizing only applies to one dimensional arrays and does not occur when INPUTing into two dimensional arrays.

	~~e.g.~~ INPUT FIELDS "row,col,LIST rows/cols, ROW, ALL" : ( MAT Array1$,		Example where all arrays are one dimensional and may have the incorrect number of elements:
	MAT Array2$, MAT Array3, MAT Array4, MAT Array5$ )		INPUT FIELDS "row,col,LIST rows/cols, ROW, ALL" : (MAT Array1$, MAT Array2$, MAT Array3, MAT Array4, MAT Array5$)
	~~Where all arrays are one dimensional and may have the incorrect number of elements.~~

GomezL: /* CONNECTSTRING (DSN Less) */

2015-07-15T15:24:35Z

CONNECTSTRING (DSN Less)

@@ Line 296: / Line 296: @@
 ===CONNECTSTRING (DSN Less)===
 CONFIG DATABASE db-ref  CONNECTSTRING="Driver={Microsoft Access Driver (*.mdb)} DBQ=C:\inetpub\wwwroot\BegASP\Chapter.14\Contact.mdb"
 ===ODBC-MANAGER===

Laura at 18:27, 19 September 2014

2014-09-19T18:27:47Z

← Older revision		Revision as of 18:27, 19 September 2014
Line 1:		Line 1:
	~~Status:~~ [[~~Beta~~]]		The prior version was [[4.2]]

	~~The prior version was [[4~~.~~2]]~~		Note that the information release notes have been incorporated into the relevant wiki pages, but are left here in their original form.

Laura: 1 revision

2013-07-13T16:24:12Z

1 revision

← Older revision	Revision as of 16:24, 13 July 2013
(No difference)

GomezL: /* Legacy SQL Support */

2012-05-03T14:04:44Z

Legacy SQL Support

New page

Status: [[Beta]]

The prior version was [[4.2]]

==Grid and List Changes==

Arrays are automatically resized when receiving data from 2D INPUT operations. This also applies to grouped arrays. Automatic resizing only applies to one dimensional arrays and does not occur when INPUTing into two dimensional arrays.

e.g. INPUT FIELDS "row,col,LIST rows/cols, ROW, ALL" : ( MAT Array1$,
MAT Array2$, MAT Array3, MAT Array4, MAT Array5$ )
Where all arrays are one dimensional and may have the incorrect number of elements.

===Three New Read Types===
Notes-
Auto Resizing applies
Selection Type must be ALL

The original 2D INPUT read types are:
CNT - The number of cells specified.
SUB - Read the Cell Subscript Values.
CELL - Read each cell specified.
ROWCNT - The number of rows specified.
ROWSUB - The subscripts of the specified rows.
ROW - Read all cells in each specified row.

HEADERS – The original PRINT FIELDS HEADER values.
e.g. INPUT FIELDS "row,col,LIST rows/cols, HEADERS" :
(MAT HEADINGS$,MAT WIDTHS, MAT FORMS$)

COLCNT - The number of columns established by the header arrays.
e.g. INPUT FIELDS "row,col,LIST rows/cols, COLCNT, ALL" : numeric-variable

These are for generalized library routines to inspect passed controls.

SORT_ORDER - Provides a value of zero for each unsorted column and gives the ascending sequence of column sorts that have occurred. If a column has been reversed (double sorted) it's value will be negative.

e.g. INPUT FIELDS "row,col,LIST rows/cols, SORT_ORDER, ALL" : numeric-array
Given the following history of sorting a four column GRID:
• column 1 (descending most rescent)
• column 2 (ascending first sorted)
• column 3 (not sorted)
• column 4 (sorted ascending)

SORT_ORDER would return-
array(1) -> -1
array(2) -> 3
array(3) -> 0
array(4) -> 2

===Restoring a User Sorted 2D Control===

The syntax for sorting an array is currently:

PRINT FIELDS "nn,nn,GRID 10/40,SORT": { column number }

This has been extended to allow a numeric array of instead of a scalar.
If an array is given, it is assumed to be in the same format that SORT_ORDER
returns. The new format is:

PRINT FIELDS "nn,nn,GRID 10/40,SORT": { column-number | numeric-array }

Where the numeric-array has one element for each column left to right. BR will resort the columns in the requested order.

The numeric array values indicating the order of column sorting to be performed
do not need to exactly match the standard format. e.g Fractions are
allowed, the values can fall within any range, and there does not need to
be trailing zero elements for unsorted columns.

A new Read Qualifier is available.
DISPLAYED_ORDER - indicates that the read operation is to not restore the data into it's original order before returning the results to the program.
e.g. INPUT FIELDS "row,col,LIST rows/cols, ROWSUB, ALL, DISPLAYED_ORDER, NOWAIT": numeric-array
This reads the original row subscripts for all rows - in their present order. This qualifier works only with the ALL selection type. It may be used in conjunction with other qualifiers such as FKEY.

===Range Selection Type===

Valid current selection types are:
SEL - Read one or more selected items.
SELONE - Select only one item.
ALL - Read all items in the control (except headings).
CUR - Current cell or row number.
CHG - All cells or rows who’s values have been changed by the operator.

BR now supports the following additional selection types:
RANGE - Specifies which portion of a 2D control is to be input.
CELL_RANGE – A special type of output range.

====Range Input====

In the folowing examples BR will redimension the receiving arrays as needed:

INPUT FIELDS "row,col,LIST rows/cols, CELL, RANGE" :
start, end, MAT Data$
This reads the specified range of cells. BR redimensions MAT Data$ as needed. Note that CELL may now be used with LIST. Previously, LISTs were only addressable by row.

INPUT FIELDS "row,col,LIST rows/cols, ROW, RANGE" :
(start:=7), (end:=11), ( MAT Array1$, MAT Array2, MAT Array3$ )
This reads the cells in rows 7 through 11. The recieving arrays are
redimensioned as appropriate.

INPUT FIELDS "row,col,GRID rows/cols, ROW, RANGE" :
MAT start, MAT end, ( MAT Data1$, MAT Data2$, MAT Data3 )
This reads one or more ranges of rows.

A more detailed example of this is:
100 -- create and populate a LIST control --
........
200 MAT START(3) : MAT END(3)
210 READ MAT START, MAT END
220 DATA 7,21,33
230 DATA 11,21,38
240 INPUT FIELDS "row,col,LIST rows/cols, ROW, RANGE" : MAT START,
MAT END, ( MAT Array1$, MAT Array2, MAT Array3$ )
This reads 12 rows of data ( row 7-11, row 21 and rows 33-38 ).

====Range Output====

By default, RANGE output refers to rows. The special keyword CELL_RANGE
is used to denote the specification of cell ranges. Additionally,
the use of scalars versus arrays for start and end values determines
important characteristics of the output process.

Using Scalars For Range Specification

PRINT FIELDS "7,8,GRID 10/75, RANGE": start, end, MAT Data$

This replaces the values in rows numbered 'start' through 'end'
with the data in MAT Data$. The size of MAT Data$ must be a multiple
of the number of columns in the GRID or an error is generated.

PRINT FIELDS "7,8,LIST 10/75, RANGE": start, end, (MAT NAME$,
MAT CITY$, MAT AGE, MAT WEIGHT)

This replaces the values in ROWs numbered 'start' through 'end'
with the data from MATs NAME$, CITY$, AGE and WEIGHT. The data arrays
must all be dimensioned the same.

====Insertion and Deletion====

The number of rows being output do not need to match the number of rows
being replaced. To delete a range of rows, output one or more grouped arrays with zero elements.

Examples- Using the following statement various scenarios are described.

PRINT FIELDS "7,8,LIST 10/75, RANGE": start, end, (MAT NAME$,
MAT CITY$, MAT AGE, MAT WEIGHT)

start=7, end=11, and the arrays have been DIMed to nine elements
Result- Nine rows replace five, and the total content of the control is
expanded by 4 rows.

start=7, end=11, and the arrays are DIMed to zero elements
Result- Five rows are deleted, and the total size of the control is
reduced by 5 rows.

start=7, end=0 (anything less than 7), and the arrays are DIMed to
support three rows
Result- Three rows are inserted ahead of row seven and the total content
of the control is expanded by three rows

start=5000, end={any value}, the control only has 482 rows, and the source
arrays are DIMed to support eleven rows
Result- Eleven rows are appended to the end of the control and become
rows 483 through 493.

====Outputting Ranges of Cells====

Ranges of cells may be output in conjunction with the CELL_RANGE keyword.

PRINT FIELDS "7,8,LIST 10/75, CELL_RANGE": start, end, (MAT NAME$,
MAT CITY$, MAT AGE, MAT WEIGHT)
- or -

PRINT FIELDS "7,8,GRID 10/75, CELL_RANGE": start, end, MAT Data$

In this case, start and end specify cells instead of rows. If insertion or deletion is indicated by dimensioning the data arrays to greater or fewer elements than are being replaced, then the data must be a multiple of the number of columns. Insertion and deletion is only valid in terms
of rows, even when cell subscripts are used to specify ranges. In such cases, if the cell subscripts are not on row boundaries, an error is generated.

PRINT FIELDS "7,8,GRID 10/75, CELL_RANGE": start, start, Data$

In this example, the value in one cell is replaced with the content of
a scalar.

====Using Arrays For Range Specification====

If the start and end specifications are arrays, denoting multiple ranges, there must be a one to one correspondence between the number of rows specified and those in the data. This method implies replacement only and insertion or deletion is not allowed.

The data flow that this feature was designed to support is one where the user is presented with a LIST or GRID where multiple rows have been either selected or changed before returning control to the program and the program is responding by updating something on those rows.

The program begins by presenting a 2D control to the user and reading the the control with type ROWSUB or SUB. Type SUB only works for GRIDs where all colmns have the same data type. Of course the subscripts are read into a numeric array which BR redimensions as appropriate. Then the program reads the changed or selected data with NOWAIT. (This resets the CHG flags in the control.) The program then changes either row (ROWSUB) or cell (SUB)
data and outputs the results using the subscript array as both the start and end specification. Other scenarios are possible but this is the primary intended use.

An example of this is:
100 -- create and populate a GRID --
........
200 INPUT FIELDS "row,col,GRID rows/cols,ROWSUB,CHG": MAT Rowsubs
(Reading subscripts does not reset the CHG flags in the control.)
210 INPUT FIELDS "row,col,GRID rows/cols,ROW,CHG,NOWAIT": ( MAT Data1$,
MAT Data2, MAT Data3$ )
BR redimensions the receiving arrays as needed.
(Reading the data also resets the CHG flags in the control.)

220 -- process the changed rows now present in the data arrays --
........
300 PRINT FIELDS "row,col,GRID rows/cols,RANGE": MAT Rowsubs,
MAT Rowsubs, ( MAT Data1$, MAT Data2, MAT Data3$ )

This outputs the updated rows.

==Logging Enahncements==

See [[Logging]] for details.

==Fine Grain Field Positioning==
ROW/COL and ROWS/COLS in FIELDS operations may be expressed as
decimal fractions.

==CSV and XML Parsing Enhancements==

See [[Str2Mat]] for details.

==New DLL Structure of Business Rules!==

As of Release 4.3 Business Rules! is restructured into the following modules:

Client and Server User Interface Modules
*[[brserver.exe]] – The BR Server module accessed by Client-Server configurations
*[[brcombined.exe]] – What is now viewed as the Standard Model
*[[brclient.exe]] – The program that the user accesses in Client-Server configurations
*[[plbrclient.dll]] - The standard (non-IE) browser plugin dll
*[[iebrclient.dll]] – Internet Explorer plugin dll

Processor DLLs – Stored in the AppData\Local directory for each user and on each server.
*[[brserverdll]]-(version,platform,etc info).dll
*[[brclientdll]]-(version,platform,etc info).dll

Updates will pertain to Processor DLLs while the user interfaces will remain as installed. Client DLLs will be automatically uploaded when corresponding server DLLs are accessed in the event they are not already present on the client.

The client can be accesed from within a browser by initiating it with [[HTML]] which can specify an embedded window or a separate independent window. In all cases opening a window with [[PARENT=NONE]] creates a separate window.

Each user will have a copy of either brcombined.exe or brclient.exe, but there are no licensing concerns because clients are not licenced. Only BR Servers are licensed.

==Client Server Reconnect Configuration Statement==

CLIENT_SERVER RECONNECT_AFTER=20 ERROR_AFTER=180 EXIT_AFTER=300

The client will attempt to reconnect to the server after RECONNECT_AFTER seconds (default is 20 seconds).

ERROR_AFTER generates a BR error and awaits for reconnection (default is 120 seconds).

EXIT_AFTER generates a BR error and continues in unattended mode ( no client IO allowed - exit at next keyboard wait ). This is the normal exit from an unrecoverable disconnect (default is 240 seconds).

If both ERROR_AFTER and EXIT_AFTER are specified, EXIT_AFTER will terminate an ERROR_AFTER wait.

When a client is attempting to reconnect, it displays the session number it is using. The normal client login window will contain a check box that facilitates the entry of a reconnection session number. This allows reconnection from another workstation.

==Autoit Support==

Right now [[Autoit]] has trouble reading BR window content. We will correct that and see what other features we can provide to ease the integration of Autoit with BR, including possibly supporting Autoit DLL linkage.

==ODBC Version 4.3==

[[ODBC]] version 4.3 released.

==SQL Support==

BR will use a single SQL server module to perform all SQL database access. This ‘engine’ will communicate with multiple BR sessions via TCP/IP. Normally the engine resides in the same directory as BR, but may reside elsewhere on the network.

===New World SQL Support===
==Config Database==

CONFIG DATABASE may use one of 3 methods for connecting to SQL Sources
*DSN
*CONNECTSTRING
*ODBC-MANAGER

===DSN===
CONFIG DATABASE db-ref DSN=dsn-ref [, USER= department | LOGIN_NAME | ? ] [, {PASSWORD= dept-password | BR_PASSWORD | ?} | PASSWORDD=encrypted-passwd ]
? indicates prompt

Example: EXECUTE 'CONFIG DATABASE CLS_Data DSN="MyData"'

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

===ODBC-MANAGER===
CONFIG DATABASE db-ref ODBC-MANAGER

Using ODBC Manager as the parameter will allow the end use to select the desired datasouce.

==Open SQL==
OPEN #20: "DATABASE= db-ref" , SQL "sql-statement", OUTIN

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

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

Begin processing with a WRITE statement.
If the WRITE conatins an IO list of values then it is used to populate the
'filename' SQL before it is executed.
This 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.

New System Functions
A$ = SQL_DATE("date-value" [,"value-format"]) ! format date for storage
B$ = BR_DATE(vtring-var$ [,"format"]) ! upack DB date value

===Legacy SQL Support===

!!! This feature is a planned addition, and is not implemented yet!!!

If a file that is opened INTERNAL is really a DISPLAY file which has as the
first four characters "#SQL" then the file will be regarded as an SQL Reference
file which must be in the following format:

#SQL
DATABASE= -DB ref-
CONTEXT= dictionary-path

The file may then be processed as an INDEXED or RELATIVE BR file.
If Indexing is specified, the Index file is used to identify the key elements
to be used by BR for record selection. Split keys are broken into multiple
values by field length.

When a READ or WRITE is issued, BR transfers data between variables in memory
and data in the DB using its own internally generated SQL control statements.
It uses the ODBC dictionary (CONTEXT) to translate BR FORM statement field
positions and formats to dictionary field names. These names are then used to
access the database during READ and WRITE operations.

==Other Developments==

[[Gary Hoff]] may get into the development cycle for some BR [[4.4]] features that will move BR into the world of mobile computing.

[[Category:Needs Help]]

[[Category:Release 4.3]]
[[Category:Release Notes]]