OpenOffice BASIC Macro資料庫操作－2

在上集中，我們完成了現在要使用的資料庫，
接著我們就可以透過OpenOffice BASIC來操作剛剛產生的資料庫。

在OOo BASIC中要操作資料庫，整個過程需要6個動作：

1. 連結DataSource
2. 取得查詢
3. 連結SQL伺服器
4. 從查詢中取得SQL Code，並查詢SQL伺服器
5. 查詢完成，取出查詢結果
6. 關閉SQL伺服器連線

整個程式碼如下：
Sub Main

REM 連結 DataSource
Dim DatabaseContext As Object
Dim DataSource As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("OOoTEST")

REM 取得查詢
Dim QueryDefinitions As Object
QueryDefinitions = DataSource.getQueryDefinitions()
QueryDefinition = QueryDefinitions("ProductQuery")

REM 連結SQL伺服器
Dim InteractionHandler As Object
Dim Connection As Object
InteractionHandler = createUnoService("com.sun.star.task.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)

REM 從查詢中取得SQL Code，並查詢SQL伺服器
Dim Statement As Object
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery(QueryDefinition.Command)

REM 查詢完成，取出查詢結果
Dim DATA_ID As String
Dim DATA_NAME As String
Dim DATA_PRICE As String
If Not IsNull(ResultSet) Then
While ResultSet.next
DATA_ID = ResultSet.getString(1)
DATA_NAME = ResultSet.getString(2)
DATA_PRICE = ResultSet.getString(3)
MsgBox "ID:"+ DATA_ID + " 名稱:" + DATA_NAME + " 價格:" + DATA_PRICE
Wend
End If

REM 關閉SQL伺服器連線
Connection.Close()
End Sub

接著我們分段來解說每個部份：

1. 連結 DataSource

在OpenOffice中，所有要被Calc使用的資料庫都需要先被「註冊」，
在建立資料庫時，預設都會自動幫你把它註冊到OpenOffice當中，
回憶一下，如下圖：

當我們將資料庫註冊到OpenOffice後，
我們就可以將它當成DataSource進行連結，
這裡要注意到，註冊的名稱可以和DataBase名稱不同，
要以「註冊名稱」為「DataSource名稱」，
不過預設，兩者名稱相同。

在這裡，我們指定DataSource為剛剛建立好的資料庫OOoTEST，
分段程式碼如下：
Dim DatabaseContext As Object
Dim DataSource As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("OOoTEST")
我們先定義DatabaseContext和DataSource這兩個變數，
接著建立DatabaseContext服務，
透過DatabaseContext服務來取得OOoTEST這個DataSource。

延伸：
這裡有個延伸的程式碼可以參考，如下：
Dim DatabaseContext As Object
Dim DataSource As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
Names = DatabaseContext.getElementNames()
For I = 0 To UBound(Names())
MsgBox Names(I)
Next I
我們可以透過這段程式碼，將所有註冊的資料庫全部列出。

2. 取得查詢

取得DataSource後，還記得剛剛我們在OOo Base裡面，
有建立好一個叫做ProductQuery的查詢項目。
接著，我們取得這個查詢，分段程式碼如下：
Dim QueryDefinitions As Object
QueryDefinitions = DataSource.getQueryDefinitions()
QueryDefinition = QueryDefinitions("ProductQuery")
定義QueryDefinitions這個變數，
透過DataSource的getQueryDefinitions() Method，
我們取得全部的Query，
並從裡面取出我們要使用的Query - ProductQuery。

延伸：
如果我們不清楚有哪些Query，希望透過Macro動態查詢，如下：
Dim QueryDefinitions As Object
QueryDefinitions = DataSource.getQueryDefinitions()
REM 顯示所有查詢
For I = 0 To QueryDefinitions.Count() - 1
QueryDefinition = QueryDefinitions(I)
MsgBox QueryDefinition.Name
Next I
我們可以用這段程式碼，將所有已經儲存的Query全部列出。

如果我們想要用Macro建立新的Query，我們可以使用下面的程式碼：
Dim QueryDefinitions As Object
QueryDefinitions = DataSource.getQueryDefinitions()
QueryDefinition = createUnoService("com.sun.star.sdb.QueryDefinition")
QueryDefinition.Command = "SELECT Name FROM ProductTable"
QueryDefinitions.insertByName("OurNewQuery", QueryDefinition)
透過這段程式碼，我們可以建立一個新的Query叫做OurNewQuery，
這個Query的SQL Code為「SELECT Name FROM ProductTable」。

3. 連結SQL伺服器

再來我們要正式連結SQL伺服器，分段程式碼如下：
Dim InteractionHandler As Object
Dim Connection As Object
InteractionHandler = createUnoService("com.sun.star.task.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
這裡我們要建立InteractionHandler服務，
接著使用DataSource的ConnectWithCompletion()來連結SQL伺服器。

4. 從查詢中取得SQL Code，並查詢SQL伺服器

SQL伺服器連結完成後，我們要查詢SQL伺服器，如下：
Dim Statement As Object
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery(QueryDefinition.Command)
我們先定義Statement變數，
接著透過Statement的executeQuery()查詢SQL伺服器。

這邊注意到，事實上executeQuery()使用的是SQL Code，
因此我們要透過QueryDefinition.Command，
將剛剛我們取得的Query - ProductQuery內的SQL Code取出。

延伸：

因為executeQuery()使用的是SQL Code，
因此我們可以不透過Query，直接用SQL Code進行資料庫的查詢，
程式碼如下：
Dim Statement As Object
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("SELECT Name FROM ProductTable")

5. 查詢完成，取出查詢結果

如果資料庫是INSERT或者UPDATE的操作，不需要取得資料，
那麼這個步驟是完全不需要的，這個步驟只有在查詢資料，
需要從資料庫中取得資料才需要。

分段程式碼如下：
Dim DATA_ID As String
Dim DATA_NAME As String
Dim DATA_PRICE As String
If Not IsNull(ResultSet) Then
While ResultSet.next
DATA_ID = ResultSet.getString(1)
DATA_NAME = ResultSet.getString(2)
DATA_PRICE = ResultSet.getString(3)
MsgBox "ID:"+ DATA_ID + " 名稱:" + DATA_NAME + " 價格:" + DATA_PRICE
Wend
End If
這裡很簡單，在前面步驟4的最後，
我們透過executeQuery()查詢資料庫後，
結果會儲存在變數ResultSet當中，
這裡我們先檢查ResultSet，確定它是有東西的，
接著使用ResultSet.next取出每條結果，
再來我們使用ResultSet.getString(1)取出第1個欄位資料，
使用ResultSet.getString(2)取出第2個欄位資料，
再利用MsgBox將資料用訊息視窗顯示出來。

6. 關閉SQL伺服器連線

最後，我們要有好習慣，將SQL伺服器斷線，分段程式碼如下：
Connection.Close()
到這裡，整個資料庫的查詢全部完成。

這裡延續前面的慣例，可以出個作業，
這裡我們完整的取得了資料庫的資料，
那要如何填入OpenOffice的Calc呢？