テーブルやビューからデータを取得する方法として、ForguncyではODataおよびODataのリソースパスをサポートしています。またODataの一部のクエリオプション、演算子、関数もサポートしています。
ODataとは:
「Webアプリケーションにおけるデータアクセス方法を標準化したプロトコル」です。Microsoft、IBM、SAP、Citrix社など中心となり仕様が策定され、2014年3月に国際標準化団体OASIS(Organization for the Advancement of Structured Information Standards, 構造化情報標準促進協会)によりOData 4.0が標準化されています。
ODataを使ったデータの取得方法には、以下の4種類の方法が用意されています。
サーバーサイドAPI
Web API
URL
関数
[重要]OData使用における注意点:
ODataを関数として使用する場合などでは、セル単位で自由にデータベースからデータを取得できたり、さらには取得したデータをページ上で計算処理を行うことができたりと、データアクセスを伴うロジック実装において、ODataは容易にかつ自由度の高い処理を実現できるといったメリットがあります。
しかし一方で、ODataを使った実装は、アプリ開発において統制を効かせにくかったり、実装した処理のメンテナンス性が低くかったりデメリットになりえる要素も複数存在します。
OData利用における開発上の主なデメリット
関数でセルに使用した場合、業務処理がセルの値として埋もれてしまい処理実装の見通しが悪い
テーブルやフィールドの使用個所を検索する「すべての参照を検索」での検索対象に含まれない
ODATA関数をページ上に複数記載した場合、ODATA関数の数だけリクエストがサーバーに送信されるためパフォーマンスへの考慮が必要
現行バージョンのForguncyでは、[変数の設定]コマンドを使うことでデータベースから取得した値を変数に格納することが可能です。ODataを使用しなくても多くの処理は、変数を使うことで実装が可能になります。OData関数を使用する場合は上記のデメリットがあることを理解した上で適切にご利用ください。
GetTableData メソッド (String)オーバーロードを使用することで、ODataのリソースパスを使ってテーブルデータを取得できます。
getTableDataByODataメソッドを使用することで、ODataのリソースパスを使ってテーブルデータを取得できます。詳細はForguncy JavaScript APIリファレンスを参照してください。
以下のようなURLのルールで、ODataを使用したテーブルデータの取得が行えます。
http://<サーバー名、またはIPアドレス>/<アプリケーションパス>/OData/GetData/<ODataのリソースパス>
たとえば、サーバー名が「srv01」、アプリケーション名が「app01」である場合に、「Categories」テーブルの「CategoryID」フィールドの値が「2」のレコードを取得するURLは以下のようになります。
http://srv01/app01/OData/GetData/Categories?$filter=CategoryID eq 2
なお、Forguncyアプリケーションにログインしていない場合には、URLを使用した方法は使用できません。以下にC#コードを使用してForguncyアプリケーションの認証を取得し、URLによるODataを使用したテーブルデータの取得例を示します。
フォーム認証
C# |
コードのコピー |
---|---|
public string GetOData(string baseUrl, string odataParam, CookieContainer loginCookie) { HttpWebRequest rq = HttpWebRequest.Create(baseUrl + "OData/GetData/" + odataParam) as HttpWebRequest; rq.CookieContainer = loginCookie; rq.Method = WebRequestMethods.Http.Get; var response = rq.GetResponse(); return new StreamReader(response.GetResponseStream()).ReadToEnd(); } CookieContainer GetLoginCookie(string baseUrl, string userName, string passWord) { var login = baseUrl + "Account/Login"; HttpWebRequest rq = HttpWebRequest.Create(login) as HttpWebRequest; rq.CookieContainer = new CookieContainer(); rq.Method = WebRequestMethods.Http.Post; rq.Accept = "application/json"; rq.ContentType = "application/json"; var loginStr = "{userName:\"" + userName + "\", password:\"" + passWord + "\",rememberMe:true }"; var data = Encoding.UTF8.GetBytes(loginStr); using (Stream stream = rq.GetRequestStream()) { stream.Write(data, 0, data.Length); } var response = rq.GetResponse(); return rq.CookieContainer; } //テストコード var baseUrl = "http://localhost:42515/Forguncy/"; var loginCookie = GetLoginCookie(baseUrl, "Administrator", "123456"); Console.WriteLine(GetOData(baseUrl, "table", loginCookie)); Console.WriteLine(GetOData(baseUrl, "table(2)", loginCookie)); |
Windows認証
C# |
コードのコピー |
---|---|
public static string GetODataWindows(string baseUrl, string odataParam, string userName, string password, string domain) { HttpWebRequest rq = HttpWebRequest.Create(baseUrl + "OData/GetData/" + odataParam) as HttpWebRequest; rq.Credentials = new NetworkCredential(userName,password, domain); rq.Method = WebRequestMethods.Http.Get; var response = rq.GetResponse(); return new StreamReader(response.GetResponseStream()).ReadToEnd(); } // テストコード Console.WriteLine(GetOData(baseUrl, "table", "budoutaro", "123456","abccompany")); |
Excelには存在しないForguncy固有の関数としてODATA関数を提供しています。ODATA関数を使用することで、ODataで指定した条件でテーブルデータを取得できます。
ODATA関数をセルに設定する場合、OData生成ツールを使用することで、GUIによるODataパスの構築が可能です。
注意:
セルの値に設定したODATA関数は、非同期で実行されます。そのため「ページロード時のコマンド」で、そのODATA関数を使用したセルの値を参照した場合、データベースやネットワークの処理速度によってはODATA関数で値を取得するよりも前にセル参照が行われてしまう可能性があります。このような問題を防ぐにはセルにODATA関数を設定するのではなく、「ページロード時のコマンド」のコマンド内でODATA関数を使用するようにします。
ODATA関数は、通常のセルで使用する以外に、リストビュー上のセルでも使用できます。しかし、リストビュー上のセルにODATA関数を設定した場合、リストビューにデータ連結しているデータの個数分、データの取得のためのサーバーへの通信が発生します。そのため、リストビュー上のセルで使用する場合には、リストビューに表示するデータ数を少なくするなどし、パフォーマンス問題が発生しないような状態での使用を推奨します。
「Categories」テーブルの「CategoryID」フィールドの値が「2」のレコードを取得する場合の数式は以下のようになります。
=ODATA("Categories?$filter=CategoryIDeq 2")
上記の場合で、値「2」の部分をA1セルの値で置き換えたい場合の数式は以下のようになります。
=ODATA("Categories?$filter=CategoryIDeq " & A1)
ODATA関数を使って複数レコードのデータを表示したい場合、配列数式と組み合わせることで実現可能です。詳しくは配列数式の使用を参照してください。
ODATA関数の取得結果は集計関数で評価することができます。例えば、以下の書き方でデータの個数を取得できます。このとき、集計関数の対象はレコードではなくデータ全体、つまり複数フィールドを取得した場合すべてのフィールドが集計の対象になる点に注意してください。
=COUNT(ODATA("Categories?$filter=CategoryIDeq 2"))
このようなODataのURLをForguncyがサポートする4種類の方法で表現した場合、以下のようになります。
サーバーサイドAPI(C#)
this.DataAccess.GetTableData("Categories(1)/Products?$top=2&$orderby=Name");
Web API(JavaScript)
Forguncy.getTableDataByOData("Categories(1)/Products?$top=2&$orderby=Name", function (data) { }, function (e) { });
URL(サーバー名が「srv01」、アプリケーション名が「app01」である場合)
http://srv01/app01/OData/GetData/Categories(1)/Products?$top=2&$orderby=Name
関数
=ODATA("Categories(1)/Products?$top=2&$orderby=Name")
以下に代表的な具体例を示します。記載する具体例は「リソースパス」以降の部分のみです。
基本的なクエリ
括弧を使用して評価の優先順位を制御した場合を除き、各クエリオプションの評価順序はSQLにおけるselect文の評価順序に従います。クエリオプションの並び順は関係しませんので、注意してください。
テーブルの全データを取得
Categories
「Categories」テーブルの全データを取得します。
主キーの値を指定してテーブルからレコードを取得
Categories(4)
主キーであるフィールドが4のレコードを「Categories」テーブルから取得します。
複数の主キーを持つテーブルから主キーを指定してレコードを取得
OrderDetails(OrderID=10000,ProductID=17)
主キーである「OrederID」フィールドが10000、「ProductID」が17のレコードを「Categories」テーブルから取得します。
特定のフィールドの値を取得
Categories(4)/CategoryName
「Categories」テーブルで主キーのフィールドが4であるレコードの「CategoryName」フィールドの値を取得します。
$valueオプションで特定のフィールドの値を取得
Categories(4)/CategoryName$value
「Categories」テーブルで主キーのフィールドが4であるレコードの「CategoryName」フィールドの値を取得します。
特定のフィールドでデータをフィルタしてレコードを取得
Categories?$filter=CategoryName eq 'Produce'
「Categories」テーブルで「CategoryName」フィールドの値が'Produce'であるレコードを取得します。
複数のフィールドでデータをフィルタしてレコードを取得
Categories?$filter=CategoryName eq 'Produce' and CategoryID le 2
「Categories」テーブルで「CategoryName」フィールドの値が'Produce'、かつ「CategoryID」が2以下であるレコードを取得します。
関数を使用して特定のフィールドでデータをフィルタしてレコードを取得
Categories?$filter=contains(CategoryName,'Pro')
「Categories」テーブルで「CategoryName」フィールドの値に'Pro'という文字列が含まれるレコードを取得します。
グループ化されたフィルターの条件を使用し、データをフィルタしてレコードを取得
Categories?$filter=CategoryName eq 'Beverages' or (CategoryID lt 4 and CategoryID gt 1) or startswith(CategoryName,'Con')
「Categories」テーブルで「CategoryName」フィールドの値が'Beverages'のレコード、もしくは「CategoryID」フィールドの値が4よりも小さくかつ1よりも大きい、もしくは 「CategoryName」フィールドの値に'Con'という文字列が含まれるレコードを取得します。
日付型のフィールドでデータをフィルタしてレコードを取得
(例1)Orders?$filter=OrderDate ge 1994-03-30"
「Orders」テーブルで「OrderDate」フィールドの値が1994/3/30と同じか、またはそれよりも新しい日付であるレコードを取得します。ODataの構文として記載する日付は「yyyy-MM-dd」の形式で記述してください。
(例2)Orders?$filter=OrderDate le "&IF(ISBLANK($C$7),"null",TEXT($C$7,"yyyy-MM-dd"))
「Orders」テーブルで「OrderDate」フィールドの値が例1のような固定でなく日付型のデータを条件とする場合、上記のようなTEXT関数を使うなどして「yyyy-MM-dd」の形式に変換が必要です。
(例3)Orders?$filter=year(OrderDate) gt 1994 and month(OrderDate) ge 1 and month(OrderDate) le 3
「Orders」テーブルで「OrderDate」フィールドの値が1994以降の年の第一四半期(1月から3月)のレコードを取得します。
特定のフィールドのレコードのみを取得
Categories?$select=CategoryName,CategoryID
「Categories」テーブルで「CategoryName」フィールドと「CategoryID」フィールドの全レコードを取得します。
特定行の特定フィールドのレコードのみを取得
Categories(1)?$select=CategoryName,CategoryID
「Categories」テーブルで主キーである「CategoryID」が1であるレコードの「CategoryName」フィールドと「CategoryID」フィールドの値のみを取得します。
すべてのフィールドを取得
Categories?$select=*
Categoriesテーブルにあるすべてのフィールドの全レコードを取得します。
フィルターを伴う特定フィールドのレコードの取得
Categories?$select=CategoryName,CategoryID&$filter=not startswith(CategoryName, 'Con')
「Categories」テーブルで「CategoryName」フィールドの値が'Con'という文字列から始まらないレコードの「CategoryName」フィールドと「CategoryID」フィールドの値のみを取得します。
先頭行からの数を指定したレコードの取得
Categories?$top=3
「Categories」テーブルで先頭行から3レコードのデータを取得します。
フィルターを伴う先頭行からの数を指定したレコードの取得
Categories?$top=3&$filter=CategoryID gt 3
「Categories」テーブルで「CategoryID」が3より大きいレコードに対して、先頭行から3レコードを取得します。
並べ替えたレコードの取得
Employees?$orderby=FirstName
「Employees」テーブルで「FirstName」フィールドをキーとして昇順で並べ替えを行った状態の全レコードを取得します。
複数のフィールドをキーとして並べ替えたレコードの取得
Employees?$orderby=HireDate asc,LastName desc,FirstName desc
「Employees」テーブルで「HireDate」フィールドをキーとして昇順で並べ替え、その後それを「LastName」フィールドをキーとして降順で並べ替え、最後にそれを「FirstName」フィールドをキーとして降順で並べ替えを行った状態の全レコードを取得します。
フィルターを伴う並べ替えたレコードの取得
Categories?$orderby=CategoryID desc&$filter=CategoryID lt 5
「Categories」テーブルで「CategoryID」フィールドの値が5よりも小さいレコードのみを抽出し、さらに「CategoryID」フィールドをキーとして降順で並べ替えたレコードを取得します。
先頭行の取得オプションを伴う並べ替えたレコードの取得
Employees?$orderby=HireDate asc&$top=5
「Employees」テーブルで「HireDate」フィールドをキーとして降順で並べ替え、その先頭から5レコードを取得します。
先頭行の取得オプションとフィルターを伴う並べ替えたレコードの取得
Categories?$orderby=CategoryID desc&$filter=CategoryID le 2&$top=1
「Categories」テーブルで「CategoryID」フィールドの値が2以下のレコードのみを抽出し、「CategoryID」フィールドをキーとして降順で並べ替え、その中の先頭1件のレコードを取得します
レコード数の取得
Categories$count
「Categories」テーブルのレコード数を取得します。
フィルターを伴うレコード数の取得
Categories$count?$filter=CategoryID lt 10
「Categories」テーブルにて「CategoryID」フィールドの値が10より少ないレコードの数を取得します。
関連レコードを含めたレコードの取得
Products?$expand=Categories
「Products」テーブルの全レコードを関連する「Categories」テーブルのレコードを含めた形で取得します。
複数テーブルの関連レコードを含めたレコードの取得
Orders?$expand=Customers,Employees
「Orders」テーブルの全レコードを関連する「Customers」テーブルのレコードと「Employees」テーブルのレコードを含めた形で取得します。
関連レコードの特定フィールドのみを含めたレコードの取得
Products?$expand=Categories($select=Description)
「Products」テーブルの全レコードを関連する「Categories」テーブルの「Description」フィールドのみを含めた形で取得します。
Orders?$expand=Customers($select=CompanyName,ContactName),Employees($select=Title)
「Orders」テーブルの全レコードを関連する「Customers」テーブルの「CompanyName」フィールドと「ContactName」フィールド、「Employees」テーブルの「Title」フィールドを含めた形で取得します。
OrderDetails?$expand=Products($select=ProductName,Categories)
「OrderDetails」テーブルの全レコードを関連する「Products」テーブルの「ProductName」フィールド、「Categories」テーブルのレコードを含めた形で取得します。
特定フィールドのみとその関連レコードを含めたレコードの取得
Products?$select=ProductName,Categories
Products?$expand=Categories&$select=ProductName,Categories
Products?$expand=Categories&$select=ProductName
「Products」テーブルの「ProductName」フィールドのみの全レコードとそれに関連する「Categories」テーブルのレコードを含めた形で取得します。上記の3つは、すべて同じ結果を返します。
Orders?$select=OrderID,Customers,Employees
「Orders」テーブルの「OrderID」フィールドのみの全レコードとそれに関連する「Customers」テーブルと「Employees」テーブルのレコードを含めた形で取得します。
特定フィールドのみとその関連レコードの特定フィールドのみを含めたレコードの取得
Products?$expand=Categories($select=CategoryName)&$select=ProductName
「Products」テーブルの「ProductName」フィールドのみの全レコードとそれに関連する「Categories」テーブルの「CategoryName」フィールドのみのレコードを含めた形で取得します。
フィルターを伴う関連レコードの特定フィールドのみを含めたレコードの取得
OrderDetails?$expand=Products($select=ProductName,Categories)&$filter=Products/Categories/CategoryName eq 'Produce'
「OrderDetails」テーブルと関連する「Product」の「ProductName」フィールドのみのレコード、さらにそれに関連する「Categories」テーブルのレコードで、「CategoryName」フィールドの値が'Produce'であるレコードのみを取得します。
フィルターと並べ替えを伴う関連レコードの特定フィールドのみを含めたレコードの取得
OrderDetails?$expand=Products($select=ProductName,Categories)&$filter=Products/Categories/CategoryName eq 'Produce'&$orderby=Products/ProductName desc,OrderID asc
「OrderDetails」テーブルで関連する「Products」テーブルの「ProductName」フィールドのみのレコード、さらにそれに関連する「Categories」テーブルのレコードで、「CategoryName」フィールドの値が'Produce'であるレコードのみを「Products」テーブルの「ProductName」フィールドをキーとして降順で並べ替え、その後「OrderID 」フィールドをキーとして並べ替えたレコードを取得します。
テーブルの関連付けを行ったフィールド名称と関連付け先のテーブル名が同じ場合
OrderDetails?$expand=Product_Product
「OrderDetails」テーブルの「Product」フィールドが「Product」テーブルに対して関連付けが行われている場合のODataパスです。
特定レコードの関連テーブルのデータを取得
Products(1)/Categories
「Products」テーブルの「ProductID」フィールドの値が1であるレコードに関連する「Categories」テーブルのレコードを取得します。
複数条件による特定レコードの関連テーブルのデータを取得
OrderDetails(OrderID = 10000, FK_ProductID = 17)/Products/Categories/CategoryName
「OrderDetails」テーブルの「OrderID」フィールドの値が10000であり、かつ「ProductID」フィールドの値が17であるレコードに関連する「Products」テーブルのレコードで、さらにそのレコードに関連する「Categories」テーブルの「CategoryName」フィールドの値のみを含むレコードを取得します。
valueオプションを使用した特定レコードの関連テーブルの特定フィールドの値の取得
Products(1)/Categories/CategoryName$value
Products」テーブルの「ProductID」フィールドの値が1であるレコードに関連する「Categories」テーブルの「CategoryName」フィールドの値を取得します。
関連テーブルのフィールドでフィルターを使用してレコードを取得
Products?$filter=Categories/CategoryName eq 'Produce'
「Products」テーブルで関連する「Categories」テーブルの「CategoryName」フィールドの値が'Produce'であるレコードを取得します。
OrderDetails?$filter=Products/Categories/CategoryName eq 'Produce'
「OrderDetails」テーブルで関連する「Products」テーブルから、さらに関連する「Categories」テーブルの「CategoryName」フィールドの値が'Produce'であるレコードを取得します。
Orders?$filter=Customers/CompanyName eq 'Franchi S.p.A.' and Employees/EmployeeID lt 10
「Orders」テーブルで関連する「Customers」テーブルの「CompanyName 」フィールドの値が'Franchi S.p.A.'で、かつ関連する「Employees」テーブルの「EmployeeID」フィールドの値が10より少ないレコードを取得します。
ODataでは、特殊文字を含むレコードをODataパスで指定する際に文字のエンコードが必要です。たとえば、「+」を含んでいるレコードを取得する場合、ODataパスには以下のように「%2b」にエンコードして指定します。
エンコード前の「+」を含んだODataパス
Products?$filter=Categories/CategoryName eq 'Produce+'
エンコード後のODataパス
Products?$filter=Categories/CategoryName eq 'Produce%2b'
エンコードの必要な特殊文字
特殊文字 |
エンコード値 |
+ |
%2b |
# |
%23 |
& |
%26 |
ForguncyのODataでは、以下の演算子や関数がサポートされます。
演算子 |
説明 |
例 |
eq |
等号 |
|
ne |
不等号 |
|
gt |
より大きい |
|
ge |
以上 |
|
lt |
より小さい |
|
le |
以下 |
|
and |
論理積 |
|
or |
論理和 |
|
not |
論理否定 |
|
演算子 |
説明 |
例 |
() |
式での評価の優先順位を制御します。 |
Products?$filter=(Price sub 5) gt 10 |
関数 |
説明 |
contains/not contains |
指定した文字列を含むかどうかを返します。 |
startswith/not startswith |
指定した文字列で始まるかどうかを返します。 |
endswith/not endswith |
指定した文字列で終わるかどうかを返します。 |
year |
指定した日付の年を返します。 |
month |
指定した日付の月を返します。 |
day |
指定した日付の日を返します。 |
データ型 |
リテラル形式 |
例 |
Null |
null |
null |
Boolean |
true|false |
例1:true 例2:false |
DateTime |
yyyy-mm-dd | yyyy-mm-ddThh:mm[:ss[.fffffff]]Z |
例1:2000-01-01 例2:2000-01-01T12:00:00Z |
Decimal |
[0-9]+.[0-9]+M|m |
2.345M |
Double |
[0-9]+ ((.[0-9]+) | [E[+ | -][0-9]+])d |
例1:1E+10d 例2:2.029d 例3:2.0d |
Single |
[0-9]+.[0-9]+f |
2.0f |
Guid |
[A-Fa-f0-9] |
12345678-aaaa-bbbb-cccc-ddddeeeeffff |
Int |
[-][0-9]+ |
例1:16 例2:-16 |
String |
'<任意のUTF-8文字>' |
'ハローOData' |
ページ上にOData関数を記載した場合、そのページの「ページロード時のコマンド…」で「JavaScriptコードの実行」コマンドを呼び出し、以下を記載してください。この記載がないと、同時に異なる端末でデータを更新していたとしても、ブラウザにOData関数で取得した値のキャッシュが残り表示が更新されなくなります。
Forguncy.Page.recalc();