* Baseのクエリでユーザー定義関数を使うには? [#qa4afea7]
 |RIGHT:|LEFT:|c
 |~ページ|[[OpenOffice.org FAQの登録ページ]]|
 |~投稿者|imaicom23|
 |~分類|#listbox3(Q&A,faq03,class)|
 |~優先順位|#listbox3(普通,faq03,priority)|
 |~状態|#listbox3(完了,faq03,state)|
 |~カテゴリー|#listbox3(Base データベース,faq03,category)|
 |~投稿日|2010-11-06 22:32:18 (土)|
 |~OS|WindowsXP|
 |~依存するページ||
 |~バージョン|#listbox3(3.2.1,faq03,version)|
 
 //////////バグ対策(以降マッチさせない)
 
 ** メッセージ [#x7a272a0]
 |LEFT:|c
 |回答ページでは行末に「~」を付加する必要はありません|
 #contents
 
 Baseのクエリで、フィールドの一つを引数として指定したユーザー定義関数を使うにはどうしたらよいのでしょうか?
 教えてください。
 #clear
 
 ----
 ***ユーザー定義関数について [#pf73008f]
 >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を使った例 [#cba8587c]
 >M.Kamataki (2010-11-08 17:25:28 (月))~
 ~
 当Q&Aサイトで、過去にfaq/4/644「Javaのメソッドの呼び出しが出来ない」という質問があったのを思い出しました。~
 ~
 わたし自身はあまりJavaに詳しくないのですが、「SELECT "java.lang.Math.sqrt"("フィールド名") AS "平方根" FROM "table1"」などのクエリーで、数値型フィールドの平方根値を表示できたのは、確認しています。~
 
 //
 ***最終的にやりたいこと [#ceee4328]
 >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で実現するには [#y30aeb41]
 >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をデータソースに登録しておく必要があります。ちょっと複雑なサンプルですが。~
 
 //
 ***クエリーは無理かも [#fb3f126c]
 >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「カルク帳票への差込印刷」にサンプルがあります。~
 
 //
 ***使いまわしがしたいので [#s435cd2e]
 >imaicom23 (2010-11-12 10:53:03 (金))~
 ~
 「抽出レコードのフィールドを文字結合させる」というのを、いろいろな形で使い回しがしたいので、ユーザー定義関数という形が良いかと思いましたが、Basicではなく、Javaでないとユーザー定義関数を利用できないということがわかっただけでも、収穫です。「差込印刷」という方法も参考にしてみます。~
 Javaのユーザー定義関数も挑戦してみます。~
 ちなみに、Basicからユーザー定義関数を利用できるように、Baseのバージョンアップということはありえないでしょうか?~
 
 //
 ***Java関数(メソッド?)を使うには? [#t05951cb]
 >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コマンドを直接実行」ボタンをオン [#k9d49911]
 >M.Kamataki (2010-11-15 13:54:27 (月))~
 ~
 クエリーをSQL表示にしたら、ツールバーにある「SQLコマンドを直接実行」ボタンをオンにしてください。このボタンがオフだと、ご報告のエラーになりますね。~
 
 //
 ***「SQLコマンドを直接実行」ボタンをオン! [#k507ee97]
 >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"]~
 ~
 となりました。これは何のエラーなんでしょうか?~
 ファイルも添付いたしますので、添削お願いします。~
 
 //
 ***添削しました [#l9d780d0]
 >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」を「"」(ダブルクォート)で囲めばエラーは出ません。~
 
 //
 ***勉強になりました [#mf99ebc3]
 >imaicom23 (2010-11-15 22:36:24 (月))~
 ~
 ありがとうございます。~
 あとはJava勉強します。~
 
 //
 ***いったん完了にします [#c0eb7f75]
 >M.Kamataki (2010-11-16 10:32:30 (火))~
 ~
 当初のご質問の回答としては十分だと思いますので、完了にします。時間のあるときにSQLでの実現方法も検証してみますね。~
 
 //
 #article