This describes how I set up JDBC to interact with a Sql Server Express 2005 database. Sql Server Express 2005 was installed on a Windows XP machine. The database was being accessed using JDBC on a remote Trusted Solaris machine running Java 1.2. The following instructions should apply to more configurations than just that though.
- Installing Sql Server Express 2005 -
1. Install Sql Server Express.
Download it from http://www.microsoft.com/sql/downloads/trial-software.mspx#EXC. During install, selecting all the default options is fine. The only thing you would want to change is using Mixed Mode authentication instead of Windows Authentication. If you didn't select that option, I'll tell you how to do it later, so don't worry about it.
2. Allow TCP/IP Connectivity
Configure TCP/IP connectivity for use with IBM Director Server. By default, SQL Server Express Edition does not have TCP/IP connectivity enabled after installation. This means you won't be able to remotely access the database. In addition, the TCP port must be set for use with IBM Director Server. Complete the following steps:
1. Click Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager.
2. In the left pane of the SQL Server Configuration Manager window, expand SQL Server 2005 Network Configuration and then click Protocols for SQLEXPRESS.
3. In the right pane of the SQL Server Configuration Manager window, right-click TCP/IP and click Enable. Click OK to acknowledge the warning message.
4. Right-click TCP/IP again and click Properties.
5. In the TCP/IP Properties window, click the IP Addresses tab, and then expand IPALL.
6. Under IPALL, clear the TCP Dynamic Ports field and then type a port number in the TCP Port field. Usually, the port number should be set to 1433.
7. In the TCP/IP Properties window, click OK, and then click OK to acknowledge the warning message.
8. In the left pane of the SQL Server Configuration Manager window, click SQL Server 2005 Services.
9. In the right pane of the SQL Server Configuration Manager window, right-click SQL Server (SQLEXPRESS) and click Restart.
3. Install SQL Server Management Studio
Download SQL Server Management Studio Express from https://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en. If you didn't allow Mixed mode Authentication for the Sql Server Express earlier, complete the following steps:
1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
2. On the Security page, under Server authentication, select "SQL Server and Windows Authentication mode."
3. In the SQL Server Management Studio dialog box, click OK, to acknowledge the need to restart SQL Server.
4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
5. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
6. On the General page, you may have to create and confirm a password for the sa login.
7. On the Status page, in the Login section, click Enabled, and then click OK.
4. Create Your Database
- Create the database using SQL Server Management Studio. Add a user to the database by going into [database_name]/security/users.
- Give the user permissions for remote access.
a) Right Click [database_name], select properties.
b) Go into properties.
c) Select the user and grant all permissions.
- JDBC Access -
Microsoft provides a JDBC Driver for SQL Server 2005, which can be downloaded from http://msdn2.microsoft.com/en-us/data/aa937724.aspx. There's a "sqljdbc.jar" in the pack that you'll need to put on your classpath. Then you can use the following Driver and Url:
Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Url: jdbc:sqlserver://[host]:[port, default 1433];"databaseName=[database_name]"
However, I needed to use Java 1.2 for my application and Microsoft's JDBC driver does not support it. It doesn't support 1.3 either. I found another great free driver at http://sourceforge.net/project/shownotes.php?release_id=369359&group_id=33291 which fit my needs: free, supports java 1.2, and easy to use. There's a jtds.jar in the pack that you'll need to put on your classpath. Then you can use the following Driver and Url.
Driver: net.sourceforge.jtds.jdbc.Driver
Url: jdbc:jtds:sqlserver://[host]:[port, default 1433]/[database_name]
I'm assuming you know how to use JDBC already, so this guide ends here.
Sunday, October 21, 2007
MS Access and JDBC
This describes how I set up JDBC to interact with a Microsoft Access .mdb database. The MS Access Database was installed on a Windows XP machine. The database was being accessed using JDBC on a remote Trusted Solaris machine running Java 1.2. The following instructions should apply to more configurations than just that though.
- Create the Microsoft Access Database -
1. Go into Control Panel -> Administrative Tools -> Data Sources (ODBC)
2. Click on the System DSN tab
3. Click the Add Button, choose Microsoft Access (*.mdb)
4. Choose the database name. Click the "create" button and decide on the location.
5. Click the Advanced button to give the database a username and password.
6. You can edit these options using the Configure button.
- JDBC Access for Microsoft Access -
You can use JDBC to access the MS Access database using Sun's built in ODBC driver. The Driver and the Url are as follows:
Driver: sun.jdbc.odbc.JdbcOdbcDriver
Url: jdbc:obbc:[database_name]
However, I needed to access the database from a remote host. I couldn't find any direct way to do this, due to the nature of the MS Access database. So to use access the database remotely, I used RmiJdbc. You can download it at http://rmijdbc.objectweb.org/.
RmiJdbc is a bridge to allow remote access to JDBC drivers. You'll find the RmiJdbc jar in the package, which you'll need to include on your classpath. Then you can use the following Driver and Url (to connect to the mdb Access database):
Driver: org.objectweb.rmijdbc.Driver
Url: jdbc:rmi://[host]/jdbc:obbc:[database_name]
In order for this to work, RmiJdbc needs to be listening for requests. Start up the server by executing "org.objectweb.rmijdbc.RJJdbcServer" on the machine the MS Access database is located on. Remember that to put the RmiJdbc jar on your classpath.
I'm assuming that you know how to use JDBC, so I'm going to end my instructions here.
[Reference]
http://rmijdbc.objectweb.org/
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=2691&lngWId=2
- Create the Microsoft Access Database -
1. Go into Control Panel -> Administrative Tools -> Data Sources (ODBC)
2. Click on the System DSN tab
3. Click the Add Button, choose Microsoft Access (*.mdb)
4. Choose the database name. Click the "create" button and decide on the location.
5. Click the Advanced button to give the database a username and password.
6. You can edit these options using the Configure button.
- JDBC Access for Microsoft Access -
You can use JDBC to access the MS Access database using Sun's built in ODBC driver. The Driver and the Url are as follows:
Driver: sun.jdbc.odbc.JdbcOdbcDriver
Url: jdbc:obbc:[database_name]
However, I needed to access the database from a remote host. I couldn't find any direct way to do this, due to the nature of the MS Access database. So to use access the database remotely, I used RmiJdbc. You can download it at http://rmijdbc.objectweb.org/.
RmiJdbc is a bridge to allow remote access to JDBC drivers. You'll find the RmiJdbc jar in the package, which you'll need to include on your classpath. Then you can use the following Driver and Url (to connect to the mdb Access database):
Driver: org.objectweb.rmijdbc.Driver
Url: jdbc:rmi://[host]/jdbc:obbc:[database_name]
In order for this to work, RmiJdbc needs to be listening for requests. Start up the server by executing "org.objectweb.rmijdbc.RJJdbcServer" on the machine the MS Access database is located on. Remember that to put the RmiJdbc jar on your classpath.
I'm assuming that you know how to use JDBC, so I'm going to end my instructions here.
[Reference]
http://rmijdbc.objectweb.org/
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=2691&lngWId=2
MySQL and JDBC
This describes how I set up a MySql database to talk with JDBC. The MySql database and server were installed on a Windows XP machine. The database was being accessed using JDBC on a remote Trusted Solaris machine running Java 1.2. The following instructions should apply to more configurations than just that though.
- MySql Database and Server -
1. Download MySql Server from http://www.mysql.org. For my installation, I used MySql Server 4.1, which I downloaded from http://www.mysql.org/downloads/mysql/4.1.html.
2. Install MySql Server using all the default options. Afterwards, you can add the MySql bin path to your PATH enviroment variable for convenience if you want.
3. It's time to create your Database. I used the mysql tool, which can be found in the bin folder of the MySql installation. It's a command line tool. Here is an example of session for setting your database up.
1) C://> mysql -u root -p
2) > create database test;
3) > select host,user,password from mysql.user;
4) > GRANT ALL PRIVILEGES ON *.* to 'user'@'%' identified by 'pass';
5) > select host,user,password from mysql.user;
The first line logs you in. The second line creates a database called test. The fourth line grants priviliges to the database. '*.*' means these all privilges will be granted on everything, 'user' is the username, 'pass' is the password, '%' is a wildcard for the host; Altogether, line 4 allows user to access any mysql database from any host using the password pass. You can limit the host to just localhost or a specific IP and you can limit privileges to a specific database. Lines 3 and 5 will allow you to see if your user was set up correctly: you'll see new entries in the table.
- JDBC Access for MySql -
1. Download the MySql JDBC Driver (called Connector/J) from http://www.mysql.org. It was under Downloads, Connectors, Connector/J. Because I needed to use Java 1.2, I needed to download the older Connector/J 3.0. If you're using a newer JVM, you can use a higher version.
2. Extract the archive and add the jar found at the top level to java's classpath. The mysql jar shouldn't have '-g' in its name anywhere.
3. Now you can write your java code. Use the following Driver and Url:
Driver: com.mysql.jdbc.Driver
Url: jdbc:mysql://[host, default localhost]:[port, default 3306]/[database_name]
4. This isn't a JDBC guide, so I'll end here. Happy coding. And good luck.
TroubleShooting
1. Error 'Can not connect to host': You don't have permission to connect to the database from the machine you're using. This probably means you didn't grant permission to your database correctly.
2. "ClassDefNotFound: java/lang/reflect/InvocationHandler": I go this error when I was trying to use the 5.0 Connector/J with Java 1.2. The 3.0 Connector/J is the only version that supports such an old JVM.
- MySql Database and Server -
1. Download MySql Server from http://www.mysql.org. For my installation, I used MySql Server 4.1, which I downloaded from http://www.mysql.org/downloads/mysql/4.1.html.
2. Install MySql Server using all the default options. Afterwards, you can add the MySql bin path to your PATH enviroment variable for convenience if you want.
3. It's time to create your Database. I used the mysql tool, which can be found in the bin folder of the MySql installation. It's a command line tool. Here is an example of session for setting your database up.
1) C://> mysql -u root -p
2) > create database test;
3) > select host,user,password from mysql.user;
4) > GRANT ALL PRIVILEGES ON *.* to 'user'@'%' identified by 'pass';
5) > select host,user,password from mysql.user;
The first line logs you in. The second line creates a database called test. The fourth line grants priviliges to the database. '*.*' means these all privilges will be granted on everything, 'user' is the username, 'pass' is the password, '%' is a wildcard for the host; Altogether, line 4 allows user to access any mysql database from any host using the password pass. You can limit the host to just localhost or a specific IP and you can limit privileges to a specific database. Lines 3 and 5 will allow you to see if your user was set up correctly: you'll see new entries in the table.
- JDBC Access for MySql -
1. Download the MySql JDBC Driver (called Connector/J) from http://www.mysql.org. It was under Downloads, Connectors, Connector/J. Because I needed to use Java 1.2, I needed to download the older Connector/J 3.0. If you're using a newer JVM, you can use a higher version.
2. Extract the archive and add the jar found at the top level to java's classpath. The mysql jar shouldn't have '-g' in its name anywhere.
3. Now you can write your java code. Use the following Driver and Url:
Driver: com.mysql.jdbc.Driver
Url: jdbc:mysql://[host, default localhost]:[port, default 3306]/[database_name]
4. This isn't a JDBC guide, so I'll end here. Happy coding. And good luck.
TroubleShooting
1. Error 'Can not connect to host': You don't have permission to connect to the database from the machine you're using. This probably means you didn't grant permission to your database correctly.
2. "ClassDefNotFound: java/lang/reflect/InvocationHandler": I go this error when I was trying to use the 5.0 Connector/J with Java 1.2. The 3.0 Connector/J is the only version that supports such an old JVM.
Subscribe to:
Posts (Atom)