How to Connect a MySQL Database via a JDBC Driver With OpenOffice.org

What Will I Learn?

In this you will leaRN How to Connect a MySQL Database via a JDBC Driver With OpenOffice.org
Broadcast by The OpenOffice.org Documentation Project. A short introduction to know how to use a MySQL database via a JDBC driver, under Windows.

• Tutorial object Introduction
• Creation of the database
• Installing the JDBC driver
• Connection with OOo
• Examples

Requirements

Download and install MySQL: At the following address: http://www.mysql.org/downloads you will find the database and a graphical interface for MYSQLGUI, as well as JDBC driver APIs.
A very good choice for driving MySQL is PhpMyAdmin. For this, you need to do to run a web server with PHP. It's very simple if you use PHPTriad or FOXServer in the extent that these two tools are made to install and configure, very simply, a Web server Apache with MySQL, PHP and PHPMyAdmin. You can find them at the following addresses:

http://sourceforge.net/projects/foxserv

http://sourceforge.net/projects/phptriad

Difficulty

• Intermediate

Tutorial Contents

---

1 . Introduction

---

Here I use PHPTriad because it is smaller and everything is included without the JDBC drivers.
After launching the installer, you have an Apache web server with PHP, PhpMyAdmin and MySQL as database. Since the user master is root, without a password, it's not very secure, but on a local PC, it's very convenient for learning. Later, you will be able to create users with their password, but after you will be assured that everything will work properly and that you will have gained more experience with a real database and its applications.

Start your browser and try: localhost, while Apache and PHP are started. With PhpPMyAdmin, check the MySQL database.
To check that nothing is missing, start with Start - Program - PHPTriad - Apache and MySQL.
The winmysqladmin.exe, mysqlmanager.exe and maybe mysql.exe programs (from a shell DOS) can also be useful. You will find them in the / bin directory at the following address:
c: \ apache \ mysql \ bin. With these simple programs, we can run the system, thanks to the PhpMyAdmin interface which is quite simple and powerful.

---

2. Creation of the database

---

We will now create a new Database with PhpMyAdmin. For our example it will be named mydb and the table will be named person with the fields described below.
Here are the SQL statements and their definitions. We have defined p_id as Primary Key.

CREATE TABLE person (
p_nr int (10) NOT NULL,
p_anrede char (20),
p_titel char (20),
p_vname char (30),
p_name char (30),
p_strasse char (30),
p_hnr char (10),
p_land char (3),
p_plz char (5),
p_ort char (30),
p_zusatz char (50),
p_art smallint (6),
p_tel1 char (20),
p_id int (10) NOT NULL AUTO_INCREMENT,
p_tel2 char (20),
p_telm char (20),
PRIMARY KEY (p_id)
)

As OOo has some problems connecting via JDBC, use PhpMyAdmin to create or delete tables and databases, or use native SQL statements outside of OOo. It works well too.

---

3 . Installing the JDBC driver

---

3 .1 Original method for version 1.0

---

Then you need to install JDBC driver. We copy the file mm.mysql-02.4-bin.jar, in the directory c: \ javaclasses that we created before.
OOo needs to know the way to find the driver. Edit the file:
<Openoffice-InstallDirectory> \ user \ config \ java.ini with a text editor and completed at [JAVA]
under SystemClasspath = this .jar - file, as below:

[Java]
Java = 1
JavaScript = 1
Applets = 1
Home = F: \ jdk1.3.1_01
VMType = jdk
Version 1.3.1 =
RuntimeLib = F: \ jdk1.3.1_01 \ jre \ bin \ hotspot \ jvm.dll
SystemClasspath = F: \ javaclasses \ mm.mysql-2.0.11-bin.jar; ... <the old entries are here>

3 .2 Simplified method

---

This method can be used from the X.Y.Z version of OpenOffice and does not require editing a ini. It basically consists of adding the connector in the extensions of the Java environment.

1. Download the java plugin for MySQL on the following page:
   http://www.mysql.com/products/connector-j/
2. Uncompress the "mysql-connector-java-3.0.11-stable-bin.jar" file in the \ lib \ ext directory
   its java installation, typically: "C: \ Program Files \ Java \ j2re1.4.1_01 \ lib \ ext" (names can
   vary by facility and version).
3. Restart OOo, after quitting the quick start.
4. It's close!

---

4 . Connection with OOo

---

Now we can start OOo -
Under Tools - Data Sources a window opens that will allow you to configure your data source for OpenOffice.org.
Here we choose [New data source].

On the [General] tab
Name: JDBCTest1 (= it's a free name under OOo)
Connection:
type of Bd: JDBC
Url of the data source: jdbc: mysql: // localhost / mydb
where localhost can also be the DNS name of a local network, or the DNS name of your own PC, mydb is the name of the database we created earlier with PhpMyAdmin.
Now choose [JDBC]
JDBC Driverclass: org.gjt.mm.mysql.Driver
URL: jdbc: mysql: // localhost / mydb (which should be inserted now)
Username: root (since we have not changed the username in MySQL)
Password: we leave it empty.
Character set: System (you can change later)
On the [Tables] tab you should now see the person table that we created earlier. You can create other tables here, as well as other queries, and so on. From this menu you can exit simply by pressing the F4 key.
Here you can now run and work on your MySQL databases via JDBC.

---

5 . Examples

---

Since I could not write to the database with normal formulas, I tried with Basic and I could write in the database. It seems that when you sent a direct SQL statement or via Basic, you have access rights to the database. With some OpenOffice.org's normal formulas, you can only read them, with the 641C.

Here are some examples to connect to a database:-

'Connect to an active connection through a formula:'

oConn = thiscomponent.drawpage.forms (0) .ActiveConnection
oTables = oConn.Tables ()
mTableNames = oTables.getElementNames ()

'Connecting to an existing DB:'

Function GetConnection (sDBName as String)
If Not IsNull (oConn) Then
oConn.Dispose ()
End If
oDB = CreateUnoService ("com.sun.star.sdb.DatabaseContext")
oConn = oDB.GetByName (sDBName) .GetConnection ("", "")
end function

'Load a Dialog that is in another Library:'

DialogLibraries.LoadLibrary ("Standard") 'Falls of the Dalog in anderer Lib wäre
dDB = CreateUnoDialog (DialogLibraries.Standard.DBDia1
dDB.execute ()

'For all tables in a DB:'

for n = LBound (mTableNames) to UBound (mTableNames)
print "Table" + CStr (n) + ":" + mTableNames (n)
oMetadata = oConn.getMetadata ()
if oTables.hasbyName (mTableNames (n)) then
msgbox "Tabelle:" + oTables.getbyName (mTableNames (n) + "existiert."
end if

'What the DB can:'

sSubQuery = oMetadata.supportsSubqueriesInComparisons ()
sSQL = oMetadata.supportsANSI92EntryLevelSQL ()
sKeyWords = oMetadata.getSQLKeywords ()
sMsg = "Subquery support in comparisons" + sSubquery
sMsg = sMsg + Chr (13) + "ANSI-SQL:" + sSQL
sMsg = sMsg + Chr (13) + sKeywords
msgbox sMsg
oColumns = oConn.Tables (n) .columns
mColumnNames = oColumns.getElementNames ()

'For all Columns of the current table:'

for n1 = LBound (mColumnNames) to UBound (mColumnNames)
sName = mColumnNames (n1)
oColumn = oColumns.getByName (sName)
sMsg = "Column:" + sName
sMsg = sMsg + Chr (13) + "Default Value:" + oColumn.DefaultValue
sMsg = sMsg + Chr (13) + "Description:" + oColumn.Description
sMsg = sMsg + Chr (13) + "IsAutoIncrement:" + oColumn.IsAutoIncrement
sMsg = sMsg + Chr (13) + "IsNullable:" + oColumn.IsNullable
sMsg = sMsg + Chr (13) + "IsRowVersion:" + oColumn.IsRowVersion
sMsg = sMsg + Chr (13) + "Precision:" + oColumn.Precision
sMsg = sMsg + Chr (13) + "Scale:" + oColumn.Scale
sMsg = sMsg + Chr (13) + "Type:" + oColumn.Type
msgbox sMsg
next n1
Next n

'a simple selection'

if oTables.hasbyName ("person") then
oStatement = oConn.createStatement ()
oResultset = oStatement.executeQuery ("Select p_id, p_anrede, p_titel, p_vname,"
_ +
"p_name from person where p_anrede = 'Herr'")
while oResultset.next
print oResultset.getString (1) + ":" + _
oResultset.getString (2) + oResultset.getString (3) + _
oResultset.getString (4) + oResultset.getString (5)
Wend
end if
dim iOK as integer
iOK = msgbox ("Neuen Datensatz anlegen?", 1)
while iOK = 1
Dim sText As String
sText = InputBox ("Irgendwas eingeben:", "Lieber User")
MsgBox (sText, 64, "Bitte bestätigen Sie die Eingabe!")
iOK = msgbox ("Neuen Datensatz anlegen?", 1)
Wend
'exit sub

This inserts a function only once, because p_id is a primary key,
'it's only a test to write in the DB:'

if oTables.hasbyName ("person") then
oStatement = oConn.createStatement ()
oInsert = oStatement.execute ("insert into person(p_id, p_anrede," + _
"P_titel, p_vname, p_name) values ​​(4, 'Herr', 'Prof. Nat. Rer.'," + _
"Mathias', 'irgendwer')"
end if
End Sub

Exit Dialog

sub exit_dialog
dDB.endExecute ()
end sub

---

Posted on Utopian.io - Rewarding Open Source Contributors

---