SalesLogix Technical Support Knowledge Base

Handy Utilities

DB Explorer

What is it?

DB Explorer (also called SQL Explorer) is an extremely useful Borland tool that is used to connect directly to and view the contents of a database using any type of BDE alias. As a result, it can be used to view and modify an InterBase, Oracle, or Microsoft SQL Server database. DB Explorer also allows you to issue SQL commands directly to the database and return result sets. DB Explorer also allows you to directly edit the values of individual fields in the database.

DB Explorer must be obtained directly from SalesLogix. The files required for it to run are the Dbexplor executable, the Dbx.dbi file, the Dbx.dll file, and the Delphimm.dll file. All of the support files should be located in the same folder as the executable and should not be put in a special location.

DB Explorer has two main windows. The first is the Database List that appears on the left side of the screen and shows the available database aliases. The second is the Details Window that appears on the right side of the screen.

The Details Window
The Details Window shows either the database alias definition tab when the alias is selected or the details of the database objects when a database with an alias has been opened.

The Database List
The Database List shows all of the available database aliases and allows you to add a new alias or edit an existing alias. Clicking the Right Mouse Button (RMB) brings up a list of the available options. These options are also available under the Object option on the main menu.

Creating a New Alias

To create a new alias, use either the main menu or the RMB menu and select the New option. This brings up the New Database Alias dialog. This lists the types of databases for which aliases can be created. After the database alias has been named, then the parameters on the Definition Tab in the Details Window to the left must be set. These parameters differ depending on the database type. For details on setting these parameters check the InterBase , MS SQL Server or Oracle paragraphs of the Borland Database Engine (BDE) Configuration Utility section.

To save a newly created alias, click on the Apply icon (the blue rolling-forward arrow) on the toolbar. This will apply the changes that have been made. All changes must be applied in order for an alias to become active. If changes have not been applied, then an orange arrow appears to the left of the alias name in the databases list. After this new alias is created it appears in the Database List.

After a valid database alias has been created, the database can be accessed by double-clicking on the alias name. This brings up the Database Login dialog. For SalesLogix Databases, the User Name is "sysdba" and the Password is usually "masterkey". These must be valid for the database to which you wish to connect.

The Detail Window

After connecting to a database by double-clicking on the database alias, a list of database object types will appear below the database alias in the Database List. You will notice that the tabs in the detail window change to the Summary Tab and the Enter SQL Tab. 

The Summary Tab: This tab is used to view the database objects of the selected type that are available for that database. These can also be displayed by selecting the plus signs that appear to the left of the database object types in the Database List.

The Enter SQL Tab: This window can be used to enter and run SQL statements against any table in the database at any time. To run a query, simply type it in the window on the Enter SQL Tab and press the yellow lightning bolt icon to execute the query. Any results will appear in a window that occupies the lower portion of the Enter SQL Tab.

How do I use it?

One particularly useful feature of DB Explorer is that it allows you to view and, to a limited degree, interactively edit the data contained within fields in the database. To do this, connect to a database. Then click the plus symbol to the left of the Tables database object type. This will expand the Tables object and show a list of the tables contained within the database. If you select a particular table, you will notice that the detail window changes to contain three tabs - the Definition Tab, the Data Tab, and the Enter SQL Tab.

The Definition Tab: This tab gives you basic system information about the table.
The Data Tab: This tab shows you the columns and rows that make up the table. To change the value of a field in the database, simply select the cell that contains it and change the value. An important thing to remember is that a change is only applied to the database after you change rows or commit the change by selecting the Commit icon. The Commit or Post Edit icon is the black check mark icon that is on the toolbar above the Detail Window.

SQL Monitor

What is it?

SQL Monitor is an extremely useful Borland tool that is used to monitor the SQL statements that are issued by the SalesLogix executables to the database. It does this by monitoring the database calls that are passed through Borland's Database Engine configuration utility (BDE). As a result, it can monitor calls regardless of whether the source database is an InterBase, MS SQL Server, or Oracle database. SQL Monitor must be obtained directly from SalesLogix.

How do I use it?

For SQL Monitor to trace the calls that are made to the database, it must always be opened before any application whose calls are to be monitored is opened. One other thing that should be done before using SQL Monitor is to choose which types of calls are to be monitored. This can be done by selecting Options > Trace Options... in the main menu. This brings up the Trace Options dialog with the Categories tab selected. It is typically best to leave only the Executed Query Statements and Vendor Errors options checked on the Categories tab. The Buffer tab is used to control how many statements are retained in the upper window of SQL Monitor. The contents of this upper window can also be saved to disk.

SQL Monitor has two display windows. The upper window and the lower window.
The Upper Window: This window displays all of the calls that have been made to the database. Each call is time stamped and given an index number. These include calls to both the SalesLogix core tables as well as any system tables.
The Lower Window: This window allows you to select a statement in the upper window and view all of the details of the statement.

To test whether a problem resides with a SQL statement or is the result of a problem within the executable, copy the SQL statement from the lower window of SQL Monitor and run it against the database using DB Explorer or Wisql32.exe. If the SQL statement runs successfully on its own, then that eliminates one possibility.

TranViewer

What is it?

TranViewer is the SalesLogix synchronization Transaction Exchange File viewer. This utility allows you to view the contents of a given Transaction Exchange File. It was created by Saleslogix and is located in the Utilities folder on the SalesLogix CD-ROM and in the SalesLogix folder on the Workgroup Administrator and Synchronization machines. TranViewer does not let you edit the contents of a transaction that you are viewing.

How do I use it?

InterBase must be running on the machine on which you wish to run TranViewer and a BDE alias named SalesLogix must exist and be pointing at the database in order for you to view transactions that are to be applied to that database. Impborl.dll must also be present on any machine on which you wish to run TranViewer.

TranViewer has 4 main sections. The upper left-hand portion of the TranViewer window contains the transaction detail information. This is specific header information about the transaction that is currently being viewed. In the upper right-hand corner is a list of the transactions that are contained in the Transaction Exchange File. Below that is the File Summary window, which lists the types of transactions that are contained in the Transaction Exchange File. Lastly, the Transaction Data window that stretches across the lower half of the viewer allows you to view what values will be inserted or what SQL statement will be executed by an individual transaction.

Transaction ID: This is the unique identifier of the transaction. It has 3 components. The "T" indicates that this is a transaction. The "XXXX" is the site code of the machine on which the transaction was generated. The "0000001" is an index number that is taken from the Security parameter of the SalesLogix.Key file.

User Name: This is the name of the user who was logged into the system when the transaction was generated.

Site Code: This is the site code of the machine on which the transaction was generated.

Date/Time: This is the local time when the transaction was generated.

Primary Field: This is the primary or key field of the table that is to be updated and that uniquely identifies each row in the table.

Primary Field Type: This is the data type of the primary or key field of the table.

Primary Field Data: This is the unique identifier that is generated and inserted into the primary or key field of the table. In the case of a Contact insert, this field will contain the Contact ID that is generated.

Related Account: This field, also referred to as the Global ID field, is used to associate transactions with specific Accounts. As a result, if populated it will contain the ID (starting with "A") of the Account with which it is related. In the case of a Contact insert, this field will contain the ID of the Account with which the Contact is associated.

Transaction Type: This field indicates such things as whether the transaction updates an individual field (tUpdate), contains an entire SQL statement to execute (tSQL), or is used to update the Whats New dialog (tWhatsNew).

Table Affected: If the transaction is an update, this is the table to which the changes contained in the transaction will be applied.

Field Affected: If the transaction is an update, this is the field that will be updated in the affected table.

Field Type: If the transaction is an update, then this field contains the data type of the field that is to be updated. Otherwise, it describes the type of Transaction Data that will be applied to the database.

Original Field Data: If the transaction is an update, then this field contains the value that existed in the field prior to the update. This value is tracked to support conflict resolution.

WISQL32.exe

What is it?

WISQL32.exe is a tool that is installed whenever you install either Local InterBase or InterBase Server. It can typically be found in c:\program files\borland\interbase\bin\WISQL32.exe. This tool allows you to issue SQL statements against an InterBase database and view the results of those queries. It also lets you gather information about the structure of the database itself.
As a direct path and not an alias is used to point to the database, this tool can only be used to issue queries to and gather information about an InterBase database.

How do I use it?

You run WISQL32.exe by double-clicking on the WISQL32 icon. This brings up the tool. After the tool has come up, you must connect to a database. To do this, select the Connect to Database... option from the File menu. This brings up the Database Connect dialog.

The Database Connect Dialog

This dialog has two main sets of options. The Locate Info options are the first set of options. These allow you to indicate the location of the InterBase database with which you wish to connect. The Database Info options are the second set of options. These provide the exact local path of the database and the user ID and password to use to connect to the database. Remember that either Local InterBase or InterBase Server must be running on the machine on which the database is located in order for you to successfully connect to the database. 

If the database that you want to connect to is located on the machine that you are currently using, leave the Local Engine option checked. If the database that you want to connect to is located on a machine other than the one that you are currently using, check the Remote Server option.
The Remote Server Option: Checking this option enables the Server and Network Protocol options. In the Server field, type in the name of the machine on which the database to which you wish to connect resides. In the Network Protocol field, select the protocol that you wish to use to connect to the database. Usually, TCP/IP is the best choice. Most of the options for the tool are relatively self-explanatory and can be found in the main menu. 

Extracting the Database Metadata

Two options that are particularly valuable are the Extract > SQL Metadata for Database and the Extract > SQL Metadata for Table options. These options let you extract the definition of particular tables within the database or for the entire database. This includes both table, index, and domain definitions. If you select either of the options, you will be asked if you wish to save the output to a file. This option is usually necessary only if you are extracting the metadata for the entire database because the number of rows returned will often exceed the number of rows in the ISQL Output window buffer. Saving to a file creates a text file containing the extracted definitions. If you decline to save the output at the time of the extract, you can do so later by pressing the Save Result button. This saves the contents of the ISQL Output window.