All of our products include the SQL Power Database Connection Manager. This practical tool allows you to create, modify and delete connections that are shared between all of our products. Our products are compatible with any JDBC or ODBC-accessible database.

To create a new database connection:

1. Open the file menu and select 'Database Connection Manager...'
2. Click the Add... button, and then select 'Database connection...'

3. Click OK. The new connection is added to the list of available connections.

You must define general settings for the database platforms you plan to work with (such as SQL Server, MySQL, Oracle, DB2, etc.). These settings will be used by our products when you set up a connection to a specific database server. The most common database types are predefined by default. If you plan to work with one of these database platforms, all you need to do is define the location of the JDBC driver. For more information, see How do I add JDBC drivers.

Keep in mind that at this point you are configuring general settings only and are not connecting to a specific database. For more information on connecting to a database, see How do I connect to my database.

To add a new database type:

1. Go to the Database Connection Manager, which is embedded in all of our products.
2. Click the 'JDBC Drivers...' button
3. Click '+' below the list of database types.
4. Enter the following information on the General tab:

1. Next, you must define the location of the JDBC driver for the database type. For more information, see How do I add JDBC drivers.
2. Click OK.

Whether you are adding a new database platform to one of our products or want to use one of the pre-configured platforms, the last step in setting up a database type is to locate the JAR file (or files) that contain the JDBC drivers for the database platform. Remember, at this point you are just telling our products where the drivers are. You must set up a database connection in order to connect to a specific database server (for more information, see How do I connect to my database).

Unlike most applications, which need a distinct driver program to communicate with each type of database, our products use Java-based drivers. These drivers normally come from the database vendor in the form of JAR (Java Archive) files. JAR files are an extension to the file format used by PKZip/WinZip archives.

Most database platforms provide drivers that are fully backward compatible. This means that it is best to use the newest driver available, regardless of the software version on the specific database server you intend to connect to. One exception to this is the Oracle database. It is important to match the major version number of your JDBC driver with the major version number of the Oracle database server you connect to. For example, if you are connecting to an Oracle 10g database, use the latest Oracle 10g driver. If you are connecting to an Oracle 9i database, use the Oracle 9i driver.

Note for Oracle 11 users:
Although Oracle 11 is not officially supported yet, you can still connect to Oracle 11 databases using the Oracle 10 driver.

To define the JDBC driver for a database type:

• If you do not have the JDBC driver for a specific database platform, you can usually obtain one from the database vendor. (For example, you can find Oracle JDBC Drivers on the Oracle site.) If that fails, you can find a directory of databases drivers on Sun's website. There is also a permanent thread in the SQL Power Architect user support forum, where you can share information with other Best Of BI tool users about finding and configuring drivers for a particular database platform.
• Decide on a permanent location to store your JDBC drivers. A good strategy is to create a JDBC folder under your Documents folder and collect all of you JDBC driver files there.
• Save the JDBC driver (it will usually be one or more JAR files) in the location you've chosen.
• Go to the Database Connection Manager, which is embeded in all of our products.
• Click on the 'JDBC Drivers...' button.
• On the 'JDBC Drivers' window, select a database type.
• Click 'Add JAR'.
• Locate the JAR file and click 'Open'. If there is a valid driver class in the JAR file, a file tree will appear showing the JDBC driver classes within the JAR file.
• Select the driver you want to use by selecting it from the list.
• Click OK.

Since SQL Power Software uses JDBC and MS Access uses ODBC, you will need to use a JDBC-ODBC bridge - such as the one included in your JDK.

For more information, refer to this article.

SQL Power Software doesn't support flat file access out of the box. However, since we use JDBC, you can access any file/database that you have a working JDBC driver for. So if you have the corresponding JDBC driver for your file/database type, all you need to do is install the JDBC driver.

Follow these steps to enable connecting to a SQL Server with your Windows domain credentials.

1. Copy the file named 'sqljdbc_auth.dll' which comes with the Microsoft JDBC driver to the Windows system directory.
2. Create a new database type and call it something like 'SQL Server Domain Security'.
3. Use the following JDBC URL template:
   • jdbc:sqlserver://<Hostname>:<Port:1433>;integratedSecurity=true;
   • Notice the added 'integratedSecurity=true' part.
4. Create a new database connection using the newly defined type.

Make sure you have Java installed on the computer.

This is related to a known bug. A simple fix is to change the server-startup.sh file permissions manually from the terminal:

chmod a+x server-startup.sh

Make sure you're using Sun JRE/JDK 6, not Gcj+classpath.

Best Of BI Developers communicate with the developer community using Google Groups. You can contact us at the following Google Groups for each project:

SQL Power Architect Developers:
http://groups.google.com/group/architect-developers

SQL Power DQguru Developers:
http://groups.google.com/group/matchmaker-developers

SQL Power Wabit Developers:
http://groups.google.com/group/wabit-developers

You can use Java's built in proxy support. To do this, you have to set a few system properties:

-Dhttp.proxyHost=myproxyserver.com -Dhttp.proxyPort=80

How you accomplish this depends on which operating system you're using (Windows, OS X, or some form of Unix).

Please note: for clarity, the following examples refer to SQL Power Wabit, but the instructions will work for all of our apps. If you are not using SQL Power Wabit, just substitute the name of the app you're using.

Windows

On Windows, you can provide additional command-line parameters in a file called appname.l4j.ini. To do so, follow these steps:

1. In Windows Explorer, locate the application's .exe file (for example, C:Program FilesWabitwabit.exe)
2. In the application's folder, create a new text file called wabit.l4j.ini. An easy way to accomplish this is to right-click on the folder window's background, the select New > Text Document from the popup menu.
   Note: if you are using SQL Power Architect, call the file architect.l4j.ini; for DQguru, call the file dqguru.l4j.ini
3. Open the newly-created file in Notepad
4. Paste the following contents into the file, and edit the settings as necessary for your environment:
   # SQL Power Wabit runtime configuration
   -Dhttp.proxyHost=myproxyserver.com
   -Dhttp.proxyPort=80
5. Save the file, then relaunch Wabit.

Mac OS X

On Mac, you will need to edit the application's info.plist file. To do so, follow these steps:

1. In the Finder, locate the application's icon
   (for example, /Applications/SQL Power Wabit.app.)
2. While holding down the Ctrl key, click the application's icon.
3. Select "Show Package Contents" from the popup menu. A new finder window will appear.
4. In the new finder window, open the Contents folder.
5. Open the Info.plist file in TextEdit. (Once TextEdit is running, you can do this by dragging the Info.plist file onto the TextEdit icon in the dock.)
6. Locate the following lines near the bottom of the file:
   <key>VMOptions</key>
   <string>-Xmx600m</string>
7. Change them to:
   <key>VMOptions</key>
   <array>
   <string>Xmx600m</string>
   <string>-Dhttp.proxyHost=myproxyserver.com</string>
   <string>-Dhttp.proxyPort=80</string>
   </array>
8. Save the file and re-launch the application.

Linux and other Unix-like Platforms

If you are using the Unix distribution, simply include the appropriate command-line parameters when launching the application. For example:

prompt% java -Xmx600m -Dhttp.proxyHost=myproxyserver.com -Dhttp.proxyPort=80 -jar wabit.jar

Note that as of SQL Power Wabit version 1.3.4, Parameters are still experimental and only recommended for advanced users.

There are two places where you can use parameters: in a report, and in a where clause on a query.

1. Report Parameters
   To create a report parameter:
   • Click on the + button in the lower right parameters panel.
   • You can create a date picker, a text field or a drop down list.
2. Query Parameters
   Once you have parameters defined in a report, you can also use them in a query.
   • On a report, once a table is in the play pen, select the where field beside a column, then hold "ctrl" and press the space bar. This brings up a Parameters window which allows you to select a field out of the project as a parameter.
   • Select the variable using the "..." box in the top right. The box will display a menu of all objects in the tree. Hovering over one will display all possible parameters in that object that can be used as a variable, including titles of objects and Java preferences for Wabit.
   • Once a variable is selected, you can define a default value.
   • Note that once you press "Insert" to add the variable, you can't return to the editor for the parameter. (To change the parameter at this point, you would have to delete all of the inserts and make a new parameter.)

The source code for all of our open source projects are hosted on Google Code. You will need to use Subversion to access the source code repositories.

Instructions for how to checkout the source code are available at each of the project's Google Code page:

SQL Power Architect:
http://code.google.com/p/power-architect/source/checkout

SQL Power DQguru:
http://code.google.com/p/power-matchmaker/source/checkout

SQL Power Wabit:
http://code.google.com/p/wabit/source/checkout

But before you try to compile it, make sure you read the FAQ below.

All Best Of BI Community Edition projects include valid Eclipse project configurations and Apache Ant build.xml scripts. After checking out the code for the first time, Eclipse will complain about some missing Java source files. These sources need to be generated by the Ant build using apt, the Annotation Processing Tool. Executing the default Ant target and then refreshing the Eclipse workspace should be enough to get the whole project to compile without any errors.

Note: All our products now compile and run on Java 6 or newer. If you had been developing in any of our code bases prior to July 2010, you would have been compiling against Java 5.

Yes: please refer to http://code.google.com/p/power-architect/wiki/JavaFormattingConventions for details.

If you submit patches that don't more-or-less adhere to these conventions, we will ask you to reformat and send in a new patch.