There is no DLL Hell in this SQL! The new DLL Structure, SQL access to Excel, BR in a Browser

1. There is no DLL Hell in this SQL!The new DLL Structure, SQL access to Excel, BR in a Browser Business Rules Group Conference 2012 San Antonio Tx Steve Koger

2. Getting the latest stuff • In order to make sure we work with the very latest stuff when we go through this I am pointing you to a website to download all the latest br versions to be used in this demo. • http://m-a-c-s.net/brconf2012.zip • Download all files you find there to the desktop of your computer. We will then go through the process of Unzipping and copying them to the correct location on your computer.

3. NEW DLL STRUCTURE OF BUSINESS RULES! • As of Release 4.3 Business Rules! has been restructured into the following modules: • brserver.exe – The BR Server module accessed by Client-Server configurations. Brserver.exe also operates as what is now viewed as the Standard Model. If it is invoked by brlistener, then it act as brserver. If it is simply executed, it acts as brcombined. However, when operating as the standard model, it needs to have brclient.dll in the same directory as brserver. • brclient.exe – The program that the user accesses in Client-Server configurations • brclient.dll – The client processing program that correlates with the brserver edition. • Client installation is done by placing brclient.exe and brclient.dll on the client system and referencing brclient.exe in an icon. Server installation is done by placing brserver.exe and brclient.dll on the server and referencing brserver.exe in the brlistener.conf file. Exe files may be renamed as desired. The name of the released brclient.dll modules will be lengthy and must not be changed because BR relies on the DLL names for version identification. • You will need one of the following possible configurations: • Workstation Standard Model • Server executable • Workstation Client dll • Linux Terminal Support • Server executable • Linux Client dll ( .so ) • Client Server Model • On Client Machine- • Client executable • Workstation Client dll • On Server • Server executable • Workstation Client dll • BR Listener installed • [ Linux Client dll for Linux terminal access ]

4. The 32 and 64 bit versions of servers and clients can be intermixed. However dlls must be the in same bit class as the modules that call them. Put your BR bmp files ( drawsunk.bmp, startup.bmp etc. ) in the BR server executable directory. • 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. • Adjunctive Files • The BR executable is now considered to be where the BR server actually resides, irrespective of Drive statements and current working directories.  • The following files are located in the BR executable directory by default: • BR server executable • BRconfig.sys • BRserial.dat • BRserver.dat • WBcmd.wbh - BR help files • Server Dlls • Client Dlls for uploading as needed • System Image files – linedraw, etc. - typically BMPs

5. Exceptions • If WBcmd.wbh doesn't exist in the BR executable directory then BR looks for it in the initial directory specified by the first drive statement. ( deprecated – will be eliminated at some point) ONQPATH currently defaults to this initial path as well. ( this will remain ) • If the BRconfig file is not present in the BR executable directory then BR looks for it in the current working directory at the time of BR invokation.  • Licensing Restrictions • As of 4.20H, brserial files must be specific to the first decimal position of the release of BR that is being used. ( e.g. 4.2x versus 4.3x ) • To accommodate more than one brserial level, BR first looks for its own suffix ( 42 or 43 ) before looking for a dat file. From now on it is most useful to name your brserial files either BRSERIAL.42 or BRSERIAL.43, etc.

6. The SPOOLPATH :OS-fullpath configuration statement specifies where print spool files are temporarily stored during printing. SPOOLPATH defaults to a spool directory that runs off of the BR root of the first drive statement. If no such spool directory exists and SPOOLPATH is not specified then BR creates one. • e.g. DRIVE J:, C:\BR, x, \MYAPP would result in spool files being placed in: • C:\BR\SPOOL\ • SPOOLPATH @::client-OS-fullpath specifies where on the client spool files are to be placed. • e.g. SPOOLPATH @::C:\BR\SPOOL -or whatever other full path is desired- • The @: tells BR that this path is on the client. The second colon says the path is independent of drive specifications. • WORKPATH defaults to the BR root of the first drive statement: • e.g. C:\BR\ • BRconfig.sys INCLUDE statements are relative to the location of the file containing the INCLUDE statement ( the parent ). CONFIG command INCLUDE statements are relative to the current directory at the time the command is issued.

7. If you don’t have the matching DLL in the same directory as the brserver.exe then you will get the following message displaying the name of the correct dll to be placed in that directory. The name of the dll must match this exactly. When running client/server if the listener is not available you will get a box like this. You may also see this box briefly if your connection to the server is slow. This box also pops up if you lose connection to the server. It will attempt to reconnect.

8. Windows 7 & 2008 Server The box on the left shows the location of the client dll after it has been downloaded. Windows XP The Box on the right shows the location This is where the client app looks for the latest dll. As updates are applied on the server The new DLL’s will be downloaded to this folder automatically from the server. You must have a copy of the dll in the same folder as the client to start with. That is its starting point. It will be copied to the above folders for actual operation. When its updating, depending upon the speed of your connection and the size of the dll, you may get a Box pop up that indicates the dll is being updated.

9. BR IN A BROWSER • We have made it easy to setup BR in a Browser once you have Client Server working. • Point your browser at ads.net/plugin for a demonstration. Located in that directory is a file called index.html which is also on the FTP site in the BETA release browser-plugin directory. Modify this file to setup your own web page. • This HTML file demonstrates activation of BR client server in a browser. It first checks to see if the plugin is installed and if not it installs it. If needed, it updates the plugin from the ads.net site. It then initiates a connection to the server just like the BR client does. See the HTML file for further instructions, including how to confine BR to a window on the launch page if desired. • A new OPTION 70 is provided which will default to being ON in the Browser version. OPTION 70 will do the same thing as 54 (exit if BR enters command console mode), but it will be configurable to: • OPTION 70 OFF ! This can be set programmatically • OPTION 70 ON ! Same as OPTION 54 – the default for browsers • OPTION 70 RELAXED ! Mild security – enables some support • RELAXED mode is intended to allow some debugging but still provide some security. This means disabling commands such as COPY, DIR, etc., CONFIG commands, and preventing changes from being made to BR programs. However, the security can be defeated because all of these activities can be performed by programs or procedures. Use ON (the default) for high security.

10. Open up firefox or Chrome on your computer and in the address bar enter ads.net/plugin. It should load this page and then shortly after start downloading the dll to your machine. You will only get this box coming up when your computer does not have the most up to date DLL already downloaded. It does not download the dll unless it is not there. As of the making of this document, I am unable to make Internet Explorer work. If I have that resolved by conference time I will show you what needs to be done.

11. If everything goes according to plan you will get a box like what is on the left come up shortly after the download of the client completes. If there is a problem you will get a box like what is showing on the right. Read carefully what the error says to diagnose the issue.

12. If we are successful then the ADS application should come up in a new window on your screen. Basically you have just used a browser to launch the client application.

13. I’ve pulled up the source of plugin.htm here. Note I have changed the line ‘var host =‘ and ‘varconf_label=‘ to localhost and PC43 from the values that ADS was using to access their test site. I have done this so we can connect to our own local machine during our session. I will supply you an ip address during our session so you can connect to me during our class.

14. The ADS application opened in its own separate window. For our simple test I want the window to stay within the browser. So we need to edit another line in the plugin. Locate the line that says “document.write(<“PARAM NAME=\”hidden\” VALUE=\”true\”>”); Put two ‘//’ in front of it to remark it out and save the file. You can adjust the ‘width’ and ‘height’ values to adjust the size of the window within which your application will appear on the page.

15. If we then load our modified plugin.htm in firefox you should get a login box like the following. Enter ‘test’ for the user and ‘test’ for the password.

16. You should get this coming up on your screen with a button that you can press remaining within the window we defined . If you open a ‘parent=none’ window within your application it will open in an independent window. Congrats you have just successfully run Business rules within a Browser!

17. SQL Access to Excel Documents in Business Rules Here is the example spreadsheet so graciously supplied to me by Luis. We are going to read in and display the Name, ADDR, CSZ fields from the file in our example program. No ODBC license is necessary for this. Also nothing other than Excel and Business Rules had to be installed on my computer.

18. SQL ACCESS to Excel Documents • This is example code that I have supplied that will read the excel spreadsheet Attyfile.xlsx. • 00010 DIM SQL$*1024,NAME$*256,ADDR$*256,CSZ$*256 • 00030 GOSUB CONFIG_EXCEL • 00050 LET SQL$="SELECT [Key],[Name],[Addr],[CSZ] FROM [ATTYFILE$] Where [CSZ]>'A' " ! For Excel • 00070 OPEN #(DB_HANDLE:=20): "DATABASE=mydb",SQL SQL$,OUTIN • 00090 WRITE #DB_HANDLE: • 00110 RD:READ #DB_HANDLE: KEY,NAME$,ADDR$,CSZ$ EOF FINIS ERROR ERROR_READ • 00130 PRINT TRIM$(NAME$)&","&TRIM$(ADDR$)&","&TRIM$(CSZ$) • 00150 Goto RD • 00170 FINIS: CLOSE #DB_HANDLE: • 00190 END • 00370 CONFIG_EXCEL: ! • 00390 DIM DATABASE$*256,DBQ$*256,DEFAULTDIR$*256,CONNECTION$*256 • 00410 LET DEFAULTDIR$=OS_FILENAME$(" ") • 00430 LET CONNECTION$="DSN=Excel Files;" • 00450 LET DATABASE$="mydb" • 00470 LET DBQ$=OS_FILENAME$("\Attyfile.xlsx") • 00490 DIM C$*100 • 00510 EXECUTE 'CONFIG database '&DATABASE$&' connectstring="'&CONNECTION$&';DBQ='&DBQ$&';ReadOnly=0;DefaultDir='&DEFAULTDIR$&';DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"' • 00530 RETURN • 00550 ERROR_READ: Print "Error #"&STR$(Err)&" in Line "&STR$(LINE): Pause

19. Here I have run my example ‘read_atty’ program. As you can see it has read in and displayed each record I requested from the table in the Excel document.

20. Lets diagnose my code: LET DEFAULTDIR$=OS_FILENAME$(" ") Setting the default directory to the current directory LET CONNECTION$="DSN=Excel Files;" Setting the connection string DSN to use the Excel Files ODBC driver supplied by Microsoft LET DATABASE$="mydb“ Giving our Database a name as a point of reference LET DBQ$=OS_FILENAME$("\Attyfile.xlsx") Setting the Filename that we want to access Our Execute Statement that opens the connection to the Excel Spreadsheet EXECUTE 'CONFIG database '&DATABASE$&' connectstring="'&CONNECTION$&'; DBQ='&DBQ$&'; ReadOnly=0; ! Important to avoid 9205 Errors DefaultDir='&DEFAULTDIR$&'; DriverId=1046; MaxBufferSize=2048; PageTimeout=5;"'

21. LET SQL$="SELECT [Key],[Name],[Addr],[CSZ] FROM [ATTYFILE$] Where [CSZ]>'A' " ! For Excel Our SQL code that selects the fields we want from the Sheet ATTYFILE$ OPEN #(DB_HANDLE:=20): "DATABASE=mydb",SQL SQL$,OUTIN Opening the Database WRITE #DB_HANDLE: Writing our SQL statement to populate IOLIST RD:READ #DB_HANDLE: KEY,NAME$,ADDR$,CSZ$ EOF FINIS ERROR ERROR_READ Reading IOLIST Data into our BR fields PRINT TRIM$(NAME$)&","&TRIM$(ADDR$)&","&TRIM$(CSZ$) Printing out the Data to the screen Goto RD Get next record from IOLIST FINIS: CLOSE #DB_HANDLE: Close the Database connection when we are done.

22. That’s just a quick and dirty on how to read some data from a simple excel spreadsheet. I must thank Luis for his assistance on getting this working. I believe he may go more in depth in his session. If you have any questions on any thing I have covered here after you get home and play with it a bit. Please email me at: steve.koger@greenbush.org