「Web出版サイト」ベータ公開

Q&A集[?]

当サイトでのご質問の受付は終了しました

すべてのコンテンツを読み込み専用としたため、回答欄からも投稿できません

Apache OpenOffice/LibreOfficeのご質問はそれぞれのフォーラムへご投稿ください

質問コーナー

サイト内検索

分類メニュー

関連サイト


本日:2
昨日:0
総数:2949
現在:3


Baseのクエリでユーザー定義関数を使うには?

ページOpenOffice.org FAQの登録ページ
投稿者imaicom23
分類
edit/refer
優先順位
edit/refer
状態
edit/refer
カテゴリー
edit/refer
投稿日2010-11-06 22:32:18 (土)
OSWindowsXP
依存するページ
バージョン
edit/refer

メッセージ

回答ページでは行末に「~」を付加する必要はありません

Baseのクエリで、フィールドの一つを引数として指定したユーザー定義関数を使うにはどうしたらよいのでしょうか? 教えてください。


ユーザー定義関数について

M.Kamataki (2010-11-08 12:21:59 (月))

Baseでお使いのデータベースエンジンによるのですが、標準のHSQLDBをお使いだと想定して回答します。どのデータベースエンジンを使っているかは、Baseの画面下部のステータスバーを見るとわかります。

そのHSQLDBのドキュメントがあります。以下は「HyperSQL User Guide」の第10章です。

http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html

「Overview」の直後に「HyperSQL supports a wide range of built-in functions and allows user-defined functions written in SQL and Java languages. 」と書かれています。ユーザー定義関数(user-defined functions)は、SQL、Java言語で書くことを許可する」とあり、その次に以下の第8章を参考するよう書かれています。

http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html

このドキュメントの最終項目「User Defined Aggregate Functions」を読んでみてください。ちょっと調べた範囲では、なかなか日本語の情報は出てきませんでした。

なお、HSQLDB以外のデータベースエンジンをお使いの場合は、やはり同じようにそのデータベースエンジンのユーザー定義関数を使えると思います。

p.s. 最終的にやりたいことを書いていただくと、ユーザー定義関数ではなく、他の方法で実現できるかもしれません。

Javaを使った例

M.Kamataki (2010-11-08 17:25:28 (月))

当Q&Aサイトで、過去にfaq/4/644「Javaのメソッドの呼び出しが出来ない」という質問があったのを思い出しました。

わたし自身はあまりJavaに詳しくないのですが、「SELECT "java.lang.Math.sqrt"("フィールド名") AS "平方根" FROM "table1"」などのクエリーで、数値型フィールドの平方根値を表示できたのは、確認しています。

最終的にやりたいこと

imaicom23 (2010-11-10 18:43:08 (水))

M.Kamatakiさん ご投稿に感謝します。
ご指摘のとおり、以下に最終的にやりたいことを示します。

以下のユーザー定義関数は、MSAccessで使っていたもののサンプルです。
この関数を、クエリから引数「intApNo」を指定して呼び出すと、テーブル「t1-2RoomLi」の中から、「intApNo」に該当する「Ap_ID」のレコードのフィールド「RoomNo」を、「[RoomNo(1)] & " " & [RoomNo(2)] & " " & …[RoomNo(n)]」という様に文字列結合させることが出来ます。

Function MyTestFu(intApNo As Integer) As String
   Dim strSQL As String
   Dim objRS As Object
   Dim strRoomNo As String
   strSQL = "SELECT [t1-2RoomLs].Ap_ID, [t1-2RoomLs].RoomNo, FROM [t1-2RoomLs] "
   strSQL = strSQL & "WHERE [t1-2RoomLs].Ap_ID = " & intApNo & " ORDER BY [t1-2RoomLs].RoomNo"
   strRoomNo = ""
   Set objRS = CurrentDb.OpenRecordset(strSQL)
   
   If Not (objRS.EOF = True And objRS.BOF = True) Then
       
       Do While objRS.EOF = False
           If IsNull(objRS("RoomNo")) Then
           Else
               strRoomNo = strRoomNo & objRS("RoomNo") & " "
           End If
           objRS.MoveNext
       Loop    
   End If
       
   MyTestFu = strRoomNo
   objRS.Close
   Set objRS = Nothing
End Function

これと同じユーザー定義関数を、OpenOffice.org Basicで定義し、Baseのクエリから呼び出したいのです。

どうしたらよいのでしょうか?
データベースエンジンはHSQLDBです。

Basicで実現するには

M.Kamataki (2010-11-10 23:20:55 (水))

>ユーザー定義関数を、OpenOffice.org Basicで定義し、Baseのクエリから呼び出したいのです。

HSQLDBがユーザー定義関数として想定するプログラム言語はJavaです。OpenOffice.org Basicはその中に含まれていないので無理ですね。

もし、Basicで実現することになると、Baseのクエリー自体をBasicで書き出す方法が考えられます。

当Q&Aに投稿されたサンプルで、クエリーを書き換えるマクロ例がありました。とりあえず、faq/5/74「Querie.Command 後に更新できない!」に添付されているサンプルをご覧になり、感じをつかんでみてください。マクロはWriterファイルから利用するようになっており、BaseファイルのTestDataBase.odbをデータソースに登録しておく必要があります。ちょっと複雑なサンプルですが。

クエリーは無理かも

M.Kamataki (2010-11-11 11:41:02 (木))

クエリーは最終的に、SELECT文になっていないといけないので、無理かもしれないです。

Baseで実現するなら、結果を新規テーブルに書き出す、(結果が1行なら)フォームのテキストボックスに書き出すのが良いかもしれません。あるいは、Calcからデータベースに接続して、表として書き出すなども考えられます。

サンプルの「Set objRS = CurrentDb.OpenRecordset(strSQL)」は、「ResultSet= Statement.executeQuery(strSQL)」と書き換えるなど、OpenOffice.org Basicでもそんなに違和感なくプログラミングできると思います。

Calcへの書き出しは、faq/4/164「インポートしたデータベース範囲を自動更新したい」(ちょっと古い例なので次のものがお薦め)faq/6/20「カルク帳票への差込印刷」にサンプルがあります。

使いまわしがしたいので

imaicom23 (2010-11-12 10:53:03 (金))

「抽出レコードのフィールドを文字結合させる」というのを、いろいろな形で使い回しがしたいので、ユーザー定義関数という形が良いかと思いましたが、Basicではなく、Javaでないとユーザー定義関数を利用できないということがわかっただけでも、収穫です。「差込印刷」という方法も参考にしてみます。
Javaのユーザー定義関数も挑戦してみます。
ちなみに、Basicからユーザー定義関数を利用できるように、Baseのバージョンアップということはありえないでしょうか?

Java関数(メソッド?)を使うには?

imaicom23 (2010-11-15 12:49:05 (月))

重ねて質問ですが、

過去にfaq/4/644「Javaのメソッドの呼び出しが出来ない」を見てみました。
「SELECT "java.lang.Math.sqrt"("f1") AS "平方根" FROM "t1"」で試してみましたが、以下のエラーがでました。
「SQL ステータス: HY000
エラーコード: 1000

Syntax error in SQL expression」

「SQL ステータス: HY000
エラーコード: 1000

SELECT java.lang.Math.sqrt( "f1" ) AS "平方根" FROM "t1"」

「SQL ステータス: HY000
エラーコード: 1000

syntax error, unexpected SQRT」
そのまま、クエリのSQL表示で編集で、入力しただけなのですが、javaの関数を使うには、なにか設定があるのでしょうか?
教えてください。

「SQLコマンドを直接実行」ボタンをオン

M.Kamataki (2010-11-15 13:54:27 (月))

クエリーをSQL表示にしたら、ツールバーにある「SQLコマンドを直接実行」ボタンをオンにしてください。このボタンがオフだと、ご報告のエラーになりますね。

「SQLコマンドを直接実行」ボタンをオン!

imaicom23 (2010-11-15 22:06:45 (月))

ありがとうございます。オンになっていませんでした。
オンにして、実行しましたら、今度は、
「データ内容が読み込めませんでした。」
SQL ステータス: 22019
エラーコード: -226

Three part identifiers prohibited in statement [SELECT java.lang.Math.sqrt( "f1" ) AS "平方根" FROM "t1"]

となりました。これは何のエラーなんでしょうか?
ファイルも添付いたしますので、添削お願いします。

添削しました

M.Kamataki (2010-11-15 22:23:14 (月))

上記でSQLを「SELECT "java.lang.Math.sqrt"("f1") AS "平方根" FROM "t1"」とされていますが、実際は「SELECT java.lang.Math.sqrt("f1") AS "平方根" FROM "t1"」でした。「java.lang.Math.sqrt」を「"」(ダブルクォート)で囲めばエラーは出ません。

勉強になりました

imaicom23 (2010-11-15 22:36:24 (月))

ありがとうございます。
あとはJava勉強します。

いったん完了にします

M.Kamataki (2010-11-16 10:32:30 (火))

当初のご質問の回答としては十分だと思いますので、完了にします。時間のあるときにSQLでの実現方法も検証してみますね。

お名前:
題名:


添付ファイル: filetest.odb 535件 [詳細]