SQL Mod

The SQL Module is a BR_VB module that adds SQL to BR 4.03 and higher.

Installation Notes (from Readme.rtf)

Congratulations on your recent purchase of the Business Rules SQL mod. We would like to wish your BR programs long and prosperous times in their newfound friendship with SQL. Installing this new mod, and giving your BR programs these new abilities, is a fairly simple straightforward process. However, if you have any trouble, a representative of Sage will be happy to help you in any way we can, to get this new product working for you.

Installation steps

1. Unzip the contents of SQL.ZIP into the folder where BR is located.
2. Double Click on sql.exe to add it to the windows registry.
3. Double Click on dcomkey.exe to add it to the windows registry.

That’s it. If you do those three things, your BR programs will be able to communicate with SQL using standard OPEN / PRINT FIELDS / RINPUT FIELDS syntax from here on out.

The BR SQL mod comes with a test program called sqlut.wb, which you may use to test the proper installation of the mod. Start BR and load sqlut. Review line 2010 for an example of a connection string. By default this program is configured to connect to Sage’s SQL Test Server, which you can reach from any computer with an internet connection.

Troubleshooting

If you have trouble communicating with SQL and you are getting error 311, failed creating com instance, you need to make sure steps 2 and 3 have been completed successfully above. The regclean.exe program is included to help with this troubleshooting. Regclean.exe is a command line program that checks to see if BR is able to find these executables.

Type regclean with no parameters to see the instructions. Try typing “regclean –q –v all”. Regclean should respond with the correct path to sql.exe. Make sure dcomkey.exe is registered, because it is harder to check this one using regclean.

If you are upgrading from a previous version of the SQL mod, use regclean from a command line to unregister the old version by typing regclean –d sql, then register the new one by double clicking on it. You should then check your registry by typing regclean –q –v all and verifying that the path regclean reports points to the proper (new) copy of sql.exe.

You may also run the included batch file fix.cmd batch file to unregister and reregister the product if you hare having trouble.

Finally, the source folder contains the VB source code for this project. Feel free to use it however you want. You may also distribute as many copies as you like of this software to anyone who uses your BR programs. However, you do not have the right to distribute this software to any other BR vendor.

Contact

If you still are unable to resolve your difficulty, don’t hesitate to contact Sage. We will be glad to help. Your contact for the SQL modification is:

Gabriel Bakker
gabriel.bakker@gmail.com 
469 223 5476

Documentation (From SQL.doc)

The Database Query language SQL has been adding powerful database functionality to programming languages for many years. People use it to perform complex database operations with simple SQL commands, and to share data between different programs, even programs written in separate languages. Many web servers have the ability to read SQL data and allow users to access important information from the road wherever an Internet connection can be found. Many software companies use a SQL database to store their data specifically for this purpose.

Now, for the first time, the BR programmer has access to this powerful tool, and the interface is done using the familiar syntax of PRINT/INPUT fields. With the addition of a simple DLL “mod”, BR gains this useful ability previously reserved only for programmers of other languages.

Open

You create an SQL connection using the standard BR open statement. Once the interface is installed, you simply give a special open statement from within your BR program to connect to any SQL database. This OPEN statement includes the SQL connection information that the linkage will use to establish a connection to your database.

02000 OPEN #10: “Project=SQL, Form=Database:OurData;Server:69.13.58.130;UserID:usr;Password:pwd”, display, outin

In this example, we are opening a SQL connection to the database called OurData, located at server 69.13.58.130. We will be using the UserID usr, and pwd is the password required to connect to OurData. The ‘“Project=SQL, Form=’ part is required. BR uses this to know that your data should go to this SQL mod. The rest of the syntax, the part that comes after the keyword ‘Form=’, is nearly identical to the standard SQL connection string. The only difference between this and the standard SQL connection string is that the ‘=’ signs in SQL have been replaced with ‘:’’s for our purposes here.

Currently, our SQL module allows for the specification of six parameters, database, systemDB, server, driver, userID, and password. If you would like the ability to specify additional parameters, just let us know.

The optional Driver parameter is used to indicate to the SQL mod which type of Database you would like to connect to. Currently, we support four databases: Microsoft SQL Server, Firebird, MySQL, and Microsoft Access. Microsoft SQL Server is the default. To connect to a Firebird database, you would simply add the string “Driver:FireBird” to the connection string shown above. For Microsoft SQL Server, you would add “Driver:MsSqlSrv”. (However, as this is the default, you don’t really need to specify the driver parameter to connect to Microsoft SQL Server 2005. MySQL can be accessed by simply adding the string Driver:MySQL to your Open Statement.

To connect to an Access Database you would simply specify Driver:Access. The parameters you need to specify for Access are Driver, SystemDB, Database, UserID, and Password. Database and SystemDB point to the necessary database and security files.

If you need to connect to a different database, please let us know as we can probably add support for your database to the project without an additional charge. Sage plans to continue to add support for the custom databases our customers ask us for.

• Note: Use “!” instead of “:” when specifying a hard coded path in a parameter!

If you are connecting to a database where you specify a hard coded path to your data file, such as FireBird or Access, then you will need to take note of one small convention that must be used. If your database was stored at location “C:\DATA\DATABASE.FDB”, the colon in “C:\…” would confuse the parsing engine. So to get around this problem, simply replace your “:” with a “!”. Your connection string would then become:

02000 OPEN #10: "Project=SQL, Form=Driver:FireBird;Database:c!\DATA\DATABASE.FDB;Server:;UserID:usr;Password:pwd", display, outin

This would instruct the SQL Module to connect to the FireBird database on the local server stored at C:\DATA\DATABASE.FDB using the UserID “usr” and the Password “pwd”.

• Important Note: Due to limitations in BR403k, if you are using the SQL module with this version of BR the OPEN statement is unable to return any error codes, such as Invalid Username/Password. Because of this limitation, we have included a function library called sqllib.wb that contains a function to test your new connection after you open it. If you are using BR403k, it is very important that you call this function after each open statement. The function is called FNTESTOPEN(filenumber,errdescription$*255). The function will test the connection to see if the open was successful. It will return 0 if successful, and it will return the error code for the problem if there is a problem. If the open was unsuccessful, the file number will automatically be closed and made ready to try again. Simply read the results of the function into a variable and take the appropriate action based upon the return value of the function. See the example program sqlUT.wb for a demonstration of the syntax and recommended programming practices.

Close

From this point, until our close statement, file channel #10 will be associated with the database OurData.

06000 CLOSE #10:

The close operation will tell the SQL project to let go of the connection and free up the memory associated with it. It will also free file channel #10 for future use.

Print/Rinput

Now that we have our connection, how do we access it? All of the interaction you will do with your database from BR is done using PRINT FIELDS and RINPUT FIELDS statements.

Interaction with a SQL database requires knowledge of a few things. Our SQL project will need to know what table you want to interact with. It needs to know what you plan to do with that table (INSERT, DELETE, UPDATE, SELECT). It needs to know which rows in the table to do it on (WHERE). You give this information to SQL via PRINT FIELDS.

03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$

PRINT FIELDS requires the specification of some fields (MAT SPEC$) and some data (MAT DATA$). Using the SQL project, you will specify Column names instead of fields. Consider the following table:

Names Table:

FirstName	LastName

If you want to interact with the Names table, your field specs will be “FirstName” and “LastName”. Your field data would be the values/variables to interact with the table using. However, we also need to specify some additional factors. We need to know what table you want to interact with, and what kind of interaction you would like to do with it. So to this end, we have added additional special fields that must be specified in order to communicate with SQL. These are “Command” and “Table”. The corresponding elements in MAT DATA$ should be the command you would like to use, and the table name.

So if you would like to add the name Tomas Hamilton to the Names table in OurData, we will create our MAT SPEC$ and our MAT DATA$ as follows.

Parameters:

I	MAT SPEC$	MAT DATA$
1	Command	INSERT
2	Table	Names
3	FirstName	‘Tomas’
4	LastName	‘Hamilton’

Then when you give your PRINT FIELDS command:

03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$

The SQL project will execute a command on your SQL database that will place Tomas Hamilton into the Names table.

Names Table:

FirstName	LastName
Tomas	Hamilton

An alternate syntax for this command is the following:

03000 PRINT #10, FIELDS “Command;Table;FirstName;LastName” : “insert”, ”Names”, ”’Tomas’”, ”’Hamilton’” 

We allow for both syntaxes as a tribute to BR’s continued commitment to backwards compatibility, wherever possible. The preferred, recommended syntax is to use matrices for your specs and your data.

Parameters

You have now seen a demonstration of a couple of the new field spec statements that you will need to use in order to interact with your database. Here is a complete listing of them.

• Command – What do I want to do with my database (always required)
• Table – Which table do I want to do it to (required for all except QUERY/SQL command)
• Criteria – Which rows do I want to do it to (required for DEL and SELECT and UPDATE)
• Query – What is the SQL command I want to give (required for QUERY/SQL command)
• GetError – Returns the description of the last SQL generated error. Use in INPUT only (for output errors, check GetError with an INPUT statement after performing the ill fated Output operation)

Command

The Command parameter is always required. Following is a list of commands that may be specified. If SPEC$(2)=“Command” then following is a list of valid entries for DATA$(2):

Output:

• INSERT – Adds a row to a table (similar to BR’s WRITE command).
• UPDATE – Changes existing data in a table (similar to BR’s REWRITE command).
• DEL (DELETE) – Deletes a row from a table (similar to BR’s DELETE command).
• SQL – Executes a specific SQL command.

Input:

• SELECT – Reads data from a table (similar to BR’s READ command).
• SELECT DISTINCT – Reads data from a table ignoring duplicate rows
• MOVEFIRST (FIRST) – Returns the first record of the last SELECT statement
• MOVELAST (LAST) – Returns the last record of the last SELECT statement
• MOVENEXT (NEXT) – Returns the next record of the last SELECT statement
• MOVEPREV (PREVIOUS) – Returns the previous record of the last SELECT statement
• QUERY – Executes a SQL command that returns a record set

These commands give the BR programmer access to a lot of the power of the SQL database language. If you require the ability for more complicated SQL interaction, Sage will be happy to add support for it for a small fee. Additionally, by purchasing this module, you get the source code with it, enabling you to make those changes yourself, if you prefer.

OUTPUT COMMANDS

Now, lets talk about these commands in more detail. Each one requires their own set of these special fields, along with the actual fields (SQL table column names) you would like to interact with. We’ll start with the output operations, which are specified using the PRINT FIELDS command.

INSERT

Insert is used for writing a record (adding a row) to your table. We have talked about it a bit, lets look at an example.

02900 MAT SPEC$(4) : MAT DATA$(4)
02910 SPEC$(1)=“Command”  : DATA$(1)=“INSERT”
02920 SPEC$(2)=“Table”    : DATA$(2)=“Names”
02930 SPEC$(3)=“FirstName”: DATA$(3)=“’Tomas’”
02940 SPEC$(4)=“LastName” : DATA$(4)=“’Hamilton’”
03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$

This will execute a SQL statement that adds a row to the table called Names with the FirstName of Tomas and the LastName of Hamilton.

If you are using the INSERT command, the only required/allowed SPEC values are Command and Table, and the Fields that you wish to give data for. If you give any of the extra optional parameters, an error will be returned.

• Important Note: It is standard SQL syntax to enclose all strings in single quotes (‘). In order to use SQL with BR you must follow this practice and enclose all your strings in single quotes (‘) as well. The library sqllib.wb included with this project contains a useful function FNADDQUOTES(MAT STRINGS$) to do just this. It takes every element of MAT STRINGS$ and surrounds it in single quotes.

UPDATE

Update is for changing a record or records in your SQL table. To do this, you must tell SQL which records to change. Like INSERT, you are required to give a Command and a Table, but now you must also give a Criteria.

Lets look at an UPDATE statement that would change our First Name Tomas into the properly spelled Thomas.

02900 MAT SPEC$(4) : MAT DATA$(4)
02910 SPEC$(1)=“Command”  : DATA$(1)=“UPDATE”
02920 SPEC$(2)=“Table”    : DATA$(2)=“Names”
02930 SPEC$(3)=“Criteria”: DATA$(3)=“FirstName=’Tomas’”
02940 SPEC$(4)=“Firstname” : DATA$(4)=“’Thomas’”
03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$

This will execute a SQL command that will take every row in the database where the first name was Tomas and replacing the firstname with Thomas. If you are new to SQL and you are concerned with the ability to update just a specific row I would recommend having a unique key field in your database, and using that to specify which row you would like to update. This will avoid the problems of accidentally updating multiple records when you only intended to update one.

Important Note: It is standard SQL syntax to enclose all strings in single quotes (‘). In order to use SQL with BR you must follow this practice and enclose all your strings in single quotes (‘) as well. The library sqllib.wb included with this project contains a useful function FNADDQUOTES(MAT STRINGS$) to do just this. It takes every element of MAT STRINGS$ and surrounds it in single quotes.

DEL (DELETE)

This command deletes a row from a table. You can specify DEL or DELETE, but in certain cases BR will give a problem if you use the word DELETE, so we gave the ability to use DEL as well.

For DELETE, you must specify a Command and a Table, of course, and you must also specify a Criteria. This will tell SQL which rows to delete.

However, you may not specify any column names, because you will be deleting the entire row.

02900 MAT SPEC$(3) : MAT DATA$(3)
02910 SPEC$(1)=“Command”  : DATA$(1)=“DELETE”
02920 SPEC$(2)=“Table”    : DATA$(2)=“Names”
02930 SPEC$(3)=“Criteria” : DATA$(3)=“FirstName=’Thomas’”
03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$

This will perform a SQL query that will delete any rows where the FirstName field equaled Thomas.

SQL

This command will execute a specific SQL command on your connection. You can use this to execute any SQL command you like, that doesn’t return a RecordSet (This is for output only). The only required/allowed SPEC is a special one called QUERY. This will be the actual SQL Query to execute on the connection. You may not specify a Table, a Criteria, or any specific Column Field names. Here is an example of a valid QUERY command:

02900 MAT SPEC$(2) : MAT DATA$(2)
02910 SPEC$(1)=“Command”  : DATA$(1)=“SQL”
02920 SPEC$(2)=“Query”    : DATA$(2)=“INSERT INTO Names (FirstName, LastName) SELECT 'Jimmi','Hendrix'”
03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$

This statement will execute the SQL command shown above, which will add a record to the Names table with the name Jimmi Hendrix.

• Note: You can use the SQL and QUERY commands to accomplish anything you want in SQL Server that you are unable to otherwise accomplish. You can create powerful stored procedures that you can then call using these commands. Any SQL programmer should be able create stored procedures for you, but if you need help doing it, don’t hesitate to contact Sage, as we would be glad to quote you a price for anything else you need.

Input

We now know how to modify our SQL databases from BR. Lets look at the commands we have to read data back from an SQL database. When inputting from your SQL Database, a couple of considerations must be made. We still need to specify the Table and Command and Criteria to the SQL database (with Blank Criteria meaning all rows), so we can’t use just a simple INPUT FIELDS, because it doesn’t allow us to provide any data to SQL. Yet, we can’t use PRINT FIELDS because we need to provide variables to put the return data in.

So when using any of the INPUT commands, it is necessary to use the RINPUT FIELDS syntax.

SELECT and SELECT DISTINCT

This command reads data from your SQL database, and returns it to the variables we specify. If you specify the DISTINCT keyword, the interface will perform the SQL command SELECT DISTINCT instead of simple SELECT, which ignores any duplicate records.

A SELECT command requires/allows only the specification of Command, Table, Criteria, and the fields (column names) you would like to read.

Here is an example SELECT command.

02900 MAT SPEC$(5) : MAT DATA$(5)
02910 SPEC$(1)=“Command”  : DATA$(1)=“UPDATE”
02920 SPEC$(2)=“Table”    : DATA$(2)=“Names”
02930 SPEC$(3)=“Criteria”: DATA$(3)=“FirstName=’Thomas’”
02940 SPEC$(4)=“FirstName”
02950 SPEC$(5)=“LastName”
03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$
03020 PRINT DATA$(4), DATA$(5)

This will issue the SQL commands to open a RecordSet containing all rows WHERE FirstName=“Thomas”. It will then take the field called FirstName and place it into DATA$(4) and it will take LastName and place it into DATA$(5).

A little bit about Recordsets:SQL uses a thing called RecordSets to read data. You create a RecordSet object, which is the result of a SQL query, and you then iterate through the RecordSet a row at a time and use the data you find there. In your BR program, if you want to read the values of additional rows returned in a given RecordSet, just like in SQL itself, you give Move commands. An explanation follows.

MOVEFIRST (FIRST)

Returns the first record of the last SELECT statement. You must first issue a SELECT command before trying any MOVE statements. MOVEFIRST will position the RecordSet pointer to the first record in the RecordSet and return the data to the variables you provide in BR.

With all move commands, you must specify the Command, but you may not specify anything else except the fields to read.

An example follows:

02900 MAT SPEC$(3) : MAT DATA$(3)
02910 SPEC$(1)=“Command” : DATA$(1)=“MOVEFIRST”
02940 SPEC$(2)=“FirstName”
02950 SPEC$(3)=“LastName”
03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$
03020 PRINT DATA$(2), DATA$(3)

This will return the FirstName and LastName column data from the top row that matches the criteria that we specified above with the previous SELECT statement.

MOVELAST (LAST)

Returns the last record of the last SELECT statement. You must first issue a SELECT command before trying any MOVE statements. MOVELAST will position the recordset pointer to the last record in the recordset and return the data to the variables you provide in BR.

With all move commands, you must specify the Command, but you may not specify anything else except the fields to read.

An example follows:

02900 MAT SPEC$(3) : MAT DATA$(3)
02910 SPEC$(1)=“Command”  : DATA$(1)=“MOVELAST”
02940 SPEC$(2)=“FirstName”
02950 SPEC$(3)=“LastName”
03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$
03020 PRINT DATA$(2), DATA$(3)

This will return the FirstName and LastName column data from the bottom row that matches the criteria that we specified above with the previous SELECT statement.

MOVENEXT (NEXT)

Returns the next record of the last SELECT statement. You must first issue a SELECT command before trying any MOVE statements. MOVENEXT will position the recordset pointer to the next record in the recordset and return the data to the variables you provide in BR.

With all move commands, you must specify the Command, but you may not specify anything else except the fields to read.

An example follows:

02900 MAT SPEC$(3) : MAT DATA$(3)
02910 SPEC$(1)=“Command”  : DATA$(1)=“MOVENEXT”
02940 SPEC$(2)=“FirstName”
02950 SPEC$(3)=“LastName”
03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$
03020 PRINT DATA$(2), DATA$(3)

This will return the FirstName and LastName column data from the next row in the recordset that we returned above with the previous SELECT statement. If we were already on the last row, this would move nowhere and return an EOF error (see errors below).

MOVEPREV (PREVIOUS)

Returns the previous record of the last SELECT statement. You must first issue a SELECT command before trying any MOVE statements. MOVEPREV will position the recordset pointer to the previous record in the recordset and return the data to the variables you provide in BR.

With all move commands, you must specify the Command, but you may not specify anything else except the fields to read.

An example follows:

02900 MAT SPEC$(3) : MAT DATA$(3)
02910 SPEC$(1)=“Command”  : DATA$(1)=“MOVEPREV”
02940 SPEC$(2)=“FirstName”
02950 SPEC$(3)=“LastName”
03000 PRINT #10, FIELDS MAT SPEC$ : MAT DATA$
03020 PRINT DATA$(2), DATA$(3)

This will return the FirstName and LastName column data from the prior row that matches the criteria that we specified above with the previous SELECT statement. If you were already at the top row of the recordset, this would not move the cursor anywhere, but would return a BOF error (see errors below).

QUERY

This command will execute a specific SQL command on your connection. You can use this to execute any SQL command you like that returns a RecordSet (This is for Input). The only required SPEC is a special one called QUERY. This will be the actual SQL Query to execute on the connection. You may not specify a Table or a Criteria, but you may specify specific Column Field names.

One powerful use of the QUERY command is to call a Stored Procedure. A Stored Procedure is a function saved on a SQL server that can do just about anything, and they sometimes return a record set in the process.

Here is an example of a valid QUERY command:

02900 MAT SPEC$(3) : MAT DATA$(3)
02910 SPEC$(1)=“Command”  : DATA$(1)=“QUERY”
02920 SPEC$(2)=“Query”    : DATA$(2)=“EXEC GetUnreadMessages '”&UserName$&“'”
02930 SPEC$(3)=“Message” 
03000 RINPUT #10, FIELDS MAT SPEC$ : MAT DATA$

This statement will execute the SQL command shown above, which calls a Stored Procedure called GetUnreadMessages which takes one parameter, a username, and returns a recordset containing all the unread messages by this user. Of course, you would also have to write this Stored Procedure on your SQL server in order for this to work. However, you can use the QUERY command to execute any SQL statement that returns a recordset.

• Note: You can use the SQL and QUERY commands to accomplish anything you want in SQL Server that you are unable to otherwise accomplish. You can create powerful stored procedures that you can then call using these commands. Any SQL programmer should be able create stored procedures for you, but if you need help doing it, don’t hesitate to contact Sage, as we would be glad to quote you a price for anything else you need.

Criterias (WHERE Clause)

A Criteria is the part of your SQL command known as the WHERE clause. This is a simple statement that explains to SQL which records to update. A Criteria may be “FirstName=‘Tomas’” or “LastName LIKE ‘Ham%’” or any other valid SQL WHERE clause.

Errors

Errors using the SQL mod can come from SQL, or the SQL-BR interface, or the BR SDK. If there is an error from SQL, BR will show error 343, brvbODBC error. At this point you will want to use GETERROR to check the description of the SQL error. (See Below)

BR Errors

The following errors can be returned by the SQL-BR interface, and they will show up as errors in BR.

• 343 - brvbODBCError - SQL returned an error, use GETERROR to read it
• 344 - brvbFieldsMissing - Not enough fields were sent
• 345 - brvbFieldsSent - Fields sent with DELETE or QUERY
• 346 - brvbTableSent - Table sent with QUERY
• 347 - brvbInvalidCommand - Invalid command sent
• 348 - brvbRecordSetNotOpened - A valid SELECT must be issued before a MOVE command and before any reading of data. This is commonly caused by using INPUT fields instead of RINPUT fields for a SELECT statement
• 349 - brvbRecordSetEmpty - The Select Statement returned no results
• 350 - brvbRecordSetBOF - The moveprev command could not be completed because the recordset was at the beginning
• 351 - brvbRecordSetEOF - The movenext command could not be completed because the recordset was at the end
• 354 - brvbImproperWindowIndex - attempt to reference a connection that hasn't been opened yet or attempt to open a connection that could not be resolved
• 355 - brvbWindowAlreadyOpen - Attempt to Open two connections with the same file number
• 357 - brvbServerNotFound - The SQL server specified could not be found or access denied
• 358 - brvbLoginFailed - Invalid User Name or Password
• 359 - brvbDatabaseNotFound - Database not found on server
• 362 - brvbImproperSpec - A Field specified does not exist in the table
• 364 - brvbQueryMissing - Execute SQL statement sent with no QUERY flag
• 365 - brvbTableMissing - There is no table specified with command
• 366 - brvbCommandMissing - No SQL Command sent with Print or Input Statement
• 367 - brvbCriteriaSent - Criteria sent when it shouldn't be (Insert or Delete)
• 368 - brvbCriteriaMissing - Criteria missing for SELECT or UPDATE

SQL ERRORS

If SQL returns an error, you will know because BR will give error 343. At this point you will want to check the description of the SQL error to see what error SQL has given you. You do this using the GETERROR special command with an input call. This is the only command that can be sent using just a simple INPUT FIELDS command, although RINPUT FIELDS will work as well. The Syntax is as follows:

08000 INPUT #10, FIELDS “GETERROR” : error$

This will read the last SQL generated error into the error$ variable. You should dimension this string to be at least 255 characters long. One way to use this is to have a variation of the following error checking routine.

02000 ON ERROR GOTO errcheck

10000 errcheck: ! ***** Catch errors
10010    if err=343 then !:
            input #10, fields "GETERROR" : Description$ !:
            print "SQL ERROR: "&Description$&" on line: "&str$(line) !:
         else !:
            print "BR-SQL Error: "&str$(err)&" on line: "&str$(line)
10090 continue

BR SDK ERRORS

Errors in the range of 311 – 342 are errors generated by the BR SDK which was used to create the SQL MOD. See br_vb manual.doc for an explanation of these errors.

Of these BR SDK errors, the following errors indicate that the SQL MOD may not have been installed properly.

• 311 - brDll_Dcom_Error - brvb_mgr.dll was unable to find SQL.EXE using Dcom. This indicates that the SQL.EXE is not registered properly. Use regclean.exe to debug this problem. See Below.
• 312 - brDll_Unhandled_Vb_Error - This error can sometimes occur if the SQL.EXE program was inadvertently shut down in the middle of running your program.
• 331 - brDll_Not_Found - brvb_mgr.dll could not be found. Make sure it is in the same directory as BR

REGCLEAN

If you are receiving connection errors while working with SQL Mod, and you want to make sure that SQL.EXE was properly registered, you may use regclean.exe to verify the installation of the SQL MOD. You may also use regclean to fix the problems you find, if you find any.

First, make sure the brvb_mgr.keys file can be found in the same folder where brvb_mgr.dll is found. (This should be the same folder where BR was installed.) If you view this text file, you should see a list of all the BR SDK projects you have installed on your computer, along with the associated DCOM key for each one. To use the BR SQL mod, you should see at least two entries: DCOMKEY and SQL. DCOMKEY is necessary to run any BR SDK project, and SQL is the entry associated with the SQL mod.

If you go to a command prompt, and run “regclean -Q all” you should then see a list of these two entries (and any others you may have installed on your computer) along with the path where the executable may be found. Verify that these paths are correct. If the path is present but incorrect (perhaps it points to a different copy or an old version or an old location) then you will need to remove the registry entry. If regclean is unable to locate the file, then skip the next paragraph and proceed to the following paragraph below for instructions on how to add this information back into the registry.

To remove an invalid location from your registry, you need to use regclean.exe again. The command this time is “regclean -d <project>” where <project> is the name of the entry that needs to be removed or all to clear out all of them. In this case, <project> would be “dcomkey”, “sql”, or “all”. After the entries have been removed, you may verify that they are no longer there by running “regclean -q all” again. This time you should see a message indicating that regclean was unable to locate the entries.

Now, all that remains is to add the registry location entries again. To do this, simply run the executable of the project you are trying to add. So, if you need to reregister the dcomkey project, simply type “dcomkey” from the command line. To reregister the SQL project, just type “sql” from the command line. Again, if you choose, you may run “regclean -q all” to verify that the entries have been properly added.

Once they are all present and valid, try running your project again and it should now work fine.

To get command line help for regclean.exe, you may type “regclean -?” from the command prompt.

If you want to simplify the process, you can run the batch file “fix.cmd” which contains the following instructions:

regclean -d all
dcomkey
sql
regclean -q all

This should automatically fix any BR SDK DCOM (311) errors you may encounter.

See also Troubleshooting SQL.