Q: How do I connect to my database?
A: 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:
- Open the file menu and select 'Database Connection Manager...'
- Click the Add... button, and then select 'Database connection...'
|In this field...||...do this.|
|Connection Name||Enter a unique name for the database connection.|
|Database Type||Select the database platform you want to connect to.
This list contains the database types you previously defined.
For more information, see the section called 'How do I set up
|Connect Options and JDBC URL
||Enter the connection options for the database driver. (Theses
options are based on the database type you select.)
If you are using one of the fully-supported drivers, the
connection option parameters are added into the JDBC URL
field in the order that the Java driver expects to see them (this
string is sometimes called a "db URL" in Java terminology).
In the following example:
- The default port number from the database type has been
entered automatically in the Connect Options.
Note: You would not usually change a default value unless
the database server you're connecting to has been configured
to use a different value.
- The hostname and database name have been entered
manually in the Connect Options.
|Username and Password||If necessary, enter the username and password to connect to
- Click OK. The new connection is added to the list of available connections.
Q: How do I set up database types?
A: 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:
- Go to the Database Connection Manager, which is embedded in all of our products.
- Click the 'JDBC Drivers...' button
- Click '+' below the list of database types.
- Enter the following information on the General tab:
|In this field...||...enter the following information.|
|Name||Name for the database type (for example, PostgreSQL or SQL Server).|
|Driver Class||Java class name of the driver. This is the driver class within
the JDBC driver JAR file that will be used for database
|Connection String Template
||General format of the JDBC URL for the database platform.
Important: You are not creating a connection for a specific
database - you are entering a generic connection string that
applies to the database platform. Later on, when you set up a
connection to a specific database, the Database Connection Manager will use this template
to create the URL to connect to the database.
The connection string template must conform to a specific
pattern that includes literals and variables.
Each variable you define is shown below the Connection
String Template field. This provides you with a preview
of the values you will be able to modify when creating a
Literals are entered like normal text but may not contain
angle brackets (< or >), which are reserved for defining
variables. As the name implies, literals appear in the URL
in the same position and way they appear in the template.
- Variables are used for values that change often, such
as the schema or database name you wish to connect
to. To define a variable in the template, use the format
<variable_name:default_value> (to include a default value)
or <variable_name> (if you don't want to include a default
value). If you use a default value. it is entered automatically
when you create a database connection. You can modify the
value if the database you are connecting to is configured to
use a different value.
For example, the connection string template to connect to a
Microsoft SQL Server database might look like this:
When you create a connection to a specific SQL Server
database, the Database Connection Manager will use this template to create the connection
URL. In this example, the template will create the URL
'jdbc:sqlserver://:1433', where 1433 is the default port value.
Since SQL Server databases listen to port 1433 by default, it
makes sense to include this value in the template. When you're
creating the actual database connection, you can change the
port value if the database you're connecting to is configured
- Next, you must define the location of the JDBC driver for the database type. For more information, see How do I add JDBC drivers.
- Click OK.
Q: How do I add JDBC drivers?
A: 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 web site. 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.
Q: How do I connect to a Microsoft Access database?
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.
Q: How do I connect to a spreadsheet, csv or other flat file?
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.
Q: How do I connect to SQL Server with my Windows credentials?
Follow these steps to enable connecting to a SQL Server with your Windows domain credentials.
- Copy the file named 'sqljdbc_auth.dll' which comes with the Microsoft JDBC driver to the Windows system directory.
- Create a new database type and call it something like 'SQL Server Domain Security'.
- Use the following JDBC URL template:
jdbc:sqlserver://<Hostname>:<Port:1433>;integratedSecurity=true; Notice the added 'integratedSecurity=true' part.
- Create a new database connection using the newly defined type.
Q: When I try to run server-startup.bat, I get the error 'java' is not recognized as an internal or external command,
operable program or batch file. What's wrong?
A: Make sure you have Java installed on the computer.
Q: When I try to run server-startup.sh, I get the error
command not found. What's wrong?
A: 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
Q: I'm on Linux and my application doesn't start!
A: Make sure you're using Sun JRE/JDK 6, not Gcj+classpath.