OData

ODataとは「Webアプリケーションにおけるデータアクセス方法を標準化したプロトコル」です。Microsoft、IBM、SAP、Citrix社など中心となり仕様が策定され、2014年3月に国際標準化団体OASIS（Organization for the Advancement of Structured Information Standards, 構造化情報標準促進協会）によりOData 4.0が標準化されています。

ForguncyはOData、およびODataのリソースパスを使用したテーブルデータの取得のみをサポートしています。また、ODataの一部のクエリオプション、演算子、関数をサポートしています。ODataを使ったデータの取得方法には、以下の4種類の方法があります。

サーバーサイドAPI
Web API
URL
関数

サーバーサイドAPI

GetTableData メソッド (String)オーバーロードを使用することで、ODataのリソースパスを使ってテーブルデータを取得できます。

Web API

getTableDataByODataメソッドを使用することで、ODataのリソースパスを使ってテーブルデータを取得できます。詳細はレコードの取得を参照してください。

URL

以下のような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を使用したテーブルデータの取得例を示します。

フォーム認証

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認証

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"));

ODATA関数

Excelには存在しないForguncy固有の関数としてODATA関数を提供しています。ODATA関数を使用することで、ODataで指定した条件でテーブルデータを取得できます。なお、ODATA関数をセルに設定する場合、　OData生成ツール　を使用することで、GUIによるODataパスの構築が可能です。

ODATA関数をセルの値として設定した場合、その処理は非同期に実行されます。そのため、たとえば「ページロード時のコマンド」において、ODATA関数を使用しているセルの値を参照するような設定を行った場合、データベースやネットワークのパフォーマンスによってはODATA関数による値の取得完了よりも前にセル参照が行われてしまう可能性があります。このような場合、セルにODATA関数を設定するのではなく、「ページロード時のコマンド」に設定したコマンド内でODATA関数を使用するようにします。

ODATA関数は、通常のセルで使用する以外に、リストビュー上のセルでも使用できます。しかし、リストビュー上のセルにODATA関数を設定した場合、リストビューにデータ連結しているデータの個数分、データの取得のためのサーバーへの通信が発生します。そのため、リストビュー上のセルで使用する場合には、リストビューに表示するデータ数を少なくするなどし、パフォーマンス問題が発生しないような状態での使用を推奨します。

「Categories」テーブルの「CategoryID」フィールドの値が「2」のレコードを取得する場合の数式は以下のようになります。

=ODATA("Categories?$filter=CategoryID eq 2")

上記の場合で、値「2」の部分をA1セルの値で置き換えたい場合の数式は以下のようになります。

=ODATA("Categories?$filter=CategoryID eq " & A1)

ODATA関数を使って複数レコードのデータを表示したい場合、配列数式と組み合わせることで実現可能です。詳しくは　配列数式を使用する　を参照してください。

ODataの具体例

このような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」フィールドの値を取得します。

●filterオプション

特定のフィールドでデータをフィルタしてレコードを取得

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'という文字列が含まれるレコードを取得します。

日付型のフィールドでデータをフィルタしてレコードを取得

（例１）Orders?$filter=OrderDate ge 1994-03-30"

「Orders」テーブルで「OrderDate」フィールドの値が1994/3/30と同じか、またはそれよりも新しい日付であるレコードを取得します。ODataの構文として記載する日付は「yyyy-MM-dd」の形式で記述してください。

（例２）Orders?$filter=OrderDate le "&IF(ISBLANK($C$7),"null",TEXT($C$7,"yyyy-MM-dd"))

「Orders」テーブルで「OrderDate」フィールドの値が例１のような固定でなく日付型のデータを条件とする場合、上記のようなTEXT関数を使うなどして「yyyy-MM-dd」の形式に変換が必要です。

（例３）Orders?$filter=year(OrderDate) gt 1994 and month(OrderDate) ge 1 and month(OrderDate) le 3

「Orders」テーブルで「OrderDate」フィールドの値が1994以降の年の第一四半期（1月から3月）のレコードを取得します。

●selectオプション

特定のフィールドのレコードのみを取得

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」フィールドの値のみを取得します。

●topオプション

先頭行からの数を指定したレコードの取得

Categories?$top=3

「Categories」テーブルで先頭行から3レコードのデータを取得します。

フィルターを伴う先頭行からの数を指定したレコードの取得

Categories?$top=3&$filter=CategoryID gt 3

「Categories」テーブルで「CategoryID」が3より大きいレコードに対して、先頭行から3レコードを取得します。

●orderbyオプション

並べ替えたレコードの取得

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件のレコードを取得します

●Countオプション

レコード数の取得

Categories/$count

「Categories」テーブルのレコード数を取得します。

フィルターを伴うレコード数の取得

Categories/$count?$filter=CategoryID lt 10

「Categories」テーブルにて「CategoryID」フィールドの値が10より少ないレコードの数を取得します。

●expand

関連レコードを含めたレコードの取得

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	等号	`Accounts?$filter=City eq '仙台'`
ne	不等号	`Accounts?$filter=City ne null`
gt	より大きい	`Accounts?$filter=SupportIncidents/Value gt 1000`
ge	以上	`Accounts?&$filter=SupportIncidents/Value ge 1000`
lt	より小さい	`Accounts?$filter=SupportIncidents/Value lt 1000`
le	以下	`Accounts?$filter=SupportIncidents/Value le 1000`
and	論理積	`Accounts?$filter=SupportIncidents/Value ge 1000 andPrefecture eq '宮城'`
or	論理和	`Accounts?$filter=AccountCategoryCode/Value eq 2 or AccountRank/Value eq 1`
not	論理否定	`Accounts?$filter=(AccountCategoryCode/Value ne null) and not (AccountCategoryCode/Value eq 1)`

●グループ化演算子

演算子	説明	例
()	式での評価の優先順位を制御します。	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パスを簡単に生成できるOData生成ツールが提供されています。
詳細については、OData生成ツール　を参照してください。

ページ上にOData関数を記載した場合、そのページの「ページロード時のコマンド…」で「JavaScriptコードの実行」コマンドを呼び出し、以下を記載してください。この記載がないと、同時に異なる端末でデータを更新していたとしても、ブラウザにOData関数で取得した値のキャッシュが残り表示が更新されなくなります。
Forguncy.Page.recalc();