読者です 読者をやめる 読者になる 読者になる

ながさわさんのアレグロモデラート

アレグロモデラート(Allegro moderato)は、速さを示す演奏記号で「穏やかに速く」

Access Web データベースから Oracle のトリガーを呼び出す(基本実装編)

Access Service 2013 SharePoint 2013 SQLServer CLRトリガー コンピューティングティップス

CLR トリガーは以下のようにつくる。

本家のチュートリアルはこちら → CLR トリガー

SharePoint Server または SQL Server が入っているコンピューターで Visual Studio を実行し、SQL Server プロジェクトをつくる。

f:id:lakeside_shinnosuke:20160429220906p:plain

プロジェクトプロパティを開けて、C#VB.NET かを決める。

f:id:lakeside_shinnosuke:20160429221201p:plain

SQL CLR C#VB.NET)というカテゴリーの、SQL CLR C# (VB.NET) Trigger という項目を追加する。

f:id:lakeside_shinnosuke:20160429222744p:plain

生成されたコードに追記したものがこちら。

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers{        
    // Enter existing table or view for the target and uncomment the attribute line

    // 以下は適切な名前に変える
    // EventFOR INSERT,UPDATE,DELETE のようにカンマでつなげられる
    [Microsoft.SqlServer.Server.SqlTrigger
    (Name="SqlTrigger1", Target="Table1", Event="INSERT,UPDATE,DELETE")]
    public static void SqlTrigger1 (){
        // Replace with your own code
        // SqlContext.Pipe.SendSQLServer Management Studio(SSMS) の
        // コンソールにメッセージが出力できる
        SqlContext.Pipe.Send("Trigger FIRED");

        SqlCommand command;
        SqlDataReader reader; 
        
        using (SqlConnection connection = new SqlConnection(@"context connection=true")){
            connection.Open();

            switch (SqlContext.TriggerContext.TriggerAction){
                // SqlContext.TriggerContext.TriggerAction で 何が起こったかわかる
                case TriggerAction.Insert:
                    command = new SqlCommand(@"SELECT * FROM INSERTED;",connection);
                    // INSERTEDINSERT された行
                    reader = command.ExecuteReader();
                    break;

                case TriggerAction.Update:
                    command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
                    // UPDATEINSERTED でとれる
                    reader = command.ExecuteReader();
                    break;

                case TriggerAction.Delete:
                    command = new SqlCommand(@"SELECT * FROM DELETED;", connection);
                    // DELETEDELETED でとる
                    reader = command.ExecuteReader();
                    break;

                default:
                    break;                
            }
        }

    }
}

どうやら、DataSet は使えないようなので、SqlDataReader でがんばること。

これをビルドすると、環境によっては、Visual Studio 上ではビルド失敗となるが、プロジェクトフォルダの obj フォルダに DLL の日付が変わっていれば、ビルドは成功している。これが CLR トリガーのライブラリになる。
(僕は SQLServer の入っているコンピュータで作らなかったので、ビルドには失敗したが、おそらく、SQLServer の入っているコンピュータでビルドすると、自動的にトリガーがデプロイされて、デバッグができるんだと思う。)

できた DLL を SSMS にデプロイするのだが、そのまえに、SSMS から、まず、CLR 統合の有効化のスクリプトを流す。

sp_configure 'clr enabled', 1
RECONFIGURE

つぎに、外部リソースへのアクセスの有効化のスクリプトを流す。

ALTER DATABASE databasename
SET TRUSTWORTHY ON

そして、DLL をデプロイする。トリガーを仕掛けたいデータベースから、プログラミング→アセンブリ→新しいアセンブリをえらぶ。

f:id:lakeside_shinnosuke:20160429225936p:plain

DLL を選択して OK を押す。

f:id:lakeside_shinnosuke:20160429230306p:plain

最後に、実テーブルに対して、トリガーをしかけるために、以下のようなスクリプトを流す。

USE databasename
GO

CREATE TRIGGER [SampleTable_Insert] ON [Access].[SampleTable]
   FOR INSERT
   AS  EXTERNAL NAME [Database1].[Triggers].[SqlTrigger1];
GO

これで CLR トリガーが呼び出せる。

ここまででハマりポイントがひとつある。トリガーを VB.NET でつくったとき、最後に CREATE TRIGGER するところで、

メッセージ 6505、レベル 16、状態 2、プロシージャ SampleTable_Insert、行 2
型 'Triggers' がアセンブリ 'Database1' に見つかりませんでした。

と出る。VB.NET で作ったときは、アセンブリのフルネームは、[Database1].[Database1.Triggers].[SqlTrigger1] となるらしい。デフォルトの名前空間をカブせて指定してください。

さあ、準備は整った。Access Services が作った SQLServer のテーブルに、Oracle のトリガーを呼び出す、CLR トリガーをコーディングしよう!道のりがまだ半分にも満たないことは、このときは知る由もなかった。。。(つづく)

Access Web データベースから Oracle のトリガーを呼び出す(導入編)

SharePoint 2013 Access Service 2013 SQLServer コンピューティングティップス

ゴールデンウィークでちょっと時間があるので、以前よりまとめようと思っていたテーマをポスト。

SharePointAccess Web データベースから、Oracle のトリガーを呼び出す必要があり、これまでは、ボタンが押されたときに、Web ブラウザーコントロールの URL を変更し、Oracle のトリガーを呼び出す SharePoint のアプリケーションページを呼び出していた。

code.msdn.microsoft.com

しかし、この方法だと、うまく動かないというケースが出てきた。要約すると、

  • GET パラメータを取得して、データベースを読み込み、PDF を作成して Octet-Stream を返す アプリケーションページを用意する
  • Access Web データベースのビュー(フォーム)に、更新ボタンと、Web ブラウザーコントロールを用意する
  • Web ブラウザーコントロールは、URL のフィールドをバインドする(Web ブラウザーコントロールには、値を直接セットできない)
  • 更新ボタンが押されたときに、レコードを保存→バインドされているレコードの ID を GET パラメータに付与したアプリケーションページの URL を作成→URL のフィールドにセット→ページのリフレッシュ
  • さらに、レコード移動時のイベントで、URL のフィールド値を消去する(そうしないと、レコードが表示されるたびに、アプリケーションページが呼ばれる)

このような処理だが、iframe に展開される Web ブラウザーコントロールの URL がうまく切り替わらないというケースが頻発し、原因究明よりも、このような信頼性に欠く方法はやめようということになった。

ちなみに、SharePoint のアプリケーションページを iframe から呼び出せるようにするには、aspx に、

<%@ Register tagprefix="WebPartPages" namespace="Microsoft.SharePoint.WebPartPages" assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

が、なければ追加して、

<WebPartPages:AllowFraming runat="server"/>

を追加する必要がある。

blogs.technet.microsoft.com

さて、この方法で、Super Visual Formade による帳票の呼び出し(PDF のダウンロード)も行っていたのだが、こちらのほうは、印刷ボタンを、ハイパーリンクコントロールにして、新しいウィンドウで呼び出すことで、確実にアプリケーションページを呼び出すということで回避した。
ちなみに、マクロからハイパーリンクの表示テキストを変更するには、以下のように「文字列#URL」という値をセットすればよい。

f:id:lakeside_shinnosuke:20160429214426p:plain

PDF のダウンロードのように、Octet-Sterm を返すのであれば、結果的に新しいウィンドウ(タブ)は開かないので、見た目の変更がなく、違和感がなかったのだが、トリガーの場合は、保存ボタンが押されるたびに、新しいタブが開くというのは、ユーザーインタフェースとして適切なものではない。

そこで、Access Services は、実データを SQLServer に書いているから、そのテーブルにトリガーに仕掛け、SQL Server のトリガーには、CLR トリガーという、.NET アセンブリを呼び出す機構があり、そこから Oracle を呼び出すという方法で進めていくことにしたのだが、これが、悪夢のはじまりだった。。。。(つづく)

Apache POI でつくった 200,000 × 255 の .xlsx ファイルを Microsoft Excel 2010 で開く

Java Apache POI Excel 2010 Excel 2013 コンピューティングティップス

Excel は 2007 から、ワークシートの最大サイズが 1,048,576 × 16384 になったので、Apache POI で大きな .xlsx ファイルを作ってみる。

Apache POI の プロジェクトサイトから、POI のバイナリーをダウンロードする。
Apache POI - Download Release Artifacts

つづけて、SXSSF の How-To にあるサンプルコードを参考に 200,000 × 255 くらいで実行してみる。
The New Halloween Document

package poitest;

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class Main {

    public static void main(String[] args) throws Throwable {
        SXSSFWorkbook wb = new SXSSFWorkbook(1); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();
        for(int rownum = 0; rownum < 200000; rownum++){
        	if ( (rownum+1) % 1000 == 0 ){
        		System.out.println( rownum + 1 );
        	}
            Row row = sh.createRow(rownum);
            for(int cellnum = 0; cellnum < 255; cellnum++){
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }

        }

        FileOutputStream out = new FileOutputStream("c:/data/sxssf.xlsx");
        wb.write(out);
        out.close();

        // dispose of temporary files backing this workbook on disk
        wb.dispose();
    }
}

20分も待てば、232M の .xlsx ファイルができあがる。
(XSSF だと、オンメモリ処理で、メモリを大量に食いつぶすので、SXSSF じゃないとだめ。)

さて、これを開けたときの結果。

1.Excel 2010 32ビット

早々に、「'sxssf.xlsx' には読み取れない内容が含まれています。このブックの内容を回復しますか? ブックの発行元が信頼出来る場合は、[はい] をクリックしてください。」

と言ってくれ、「はい」すると、回復を試みる。うまくいけば、開くときもあるが、「リソース不足のため、このタスクを完了することができません。選択するデータを少なくするか、ほかのアプリケーションを終了して再度試してください。」

と出て「まいった」となる。

2.Excel 2013 32ビット

f:id:lakeside_shinnosuke:20160429191207p:plain

がんばって、開けようとするが、しばらくすると、

f:id:lakeside_shinnosuke:20160429191240p:plain

このように「まいった」と言うのだが、2010 のときと違い、64 ビットで開けたら開くかもよ、と言ってくれる。なので、

3.Excel 2010 64ビット

で開くと、
f:id:lakeside_shinnosuke:20160429192115p:plain

10分以上、もくもくと開こうとする。

f:id:lakeside_shinnosuke:20160429192411p:plain

その間、メモリはゆるやかな右肩上がり。

そして、

f:id:lakeside_shinnosuke:20160429192021p:plain

やったー。開きました。


非常に大きい .xlsx を Apache POI でつくるとき、次のような問題にぶちあたっていく。

  • .xls の API である HSSF に対応する .xlsx の API である XSSF を使うと遅い。(zip 展開しながらだから。)
  • XSSF はオンメモリなので、ありったけのメモリが使われる
  • POI 3.8 beta3 から利用できる SXSSF という API を使えば少ないメモリで .xlsx を作成できるが、Buffered Streaming に特化したものなので、大きなテンポラリファイルが作られテンポラリに書き出し済みの行へ戻ってセルの書き込みができない
  • ただし、SXSSF のテンポラリファイルは設定によって、gzip で圧縮しながらテンポラリを作ることもできる。
  • テンポラリファイルへの書き込み行数も設定できるので、この値を大きくしておけば、前に戻ることもできるようだ。(これは未確認。)

このように紆余曲折を経て、大きい .xlsx ファイルが、小さなリソースと、短い時間で作られることになるのだが、いざ、クライアントから開けようと思うと、32 ビットではでかすぎて開けられない、ということらしい。
そのうえ、32 ビット Excel で開くことができるファイルの具体的な制限というのは、どんなにググっても、適切な記述が出てこない。

強いて言えば、以下の URL の「データ モデル ブックのメモリ ストレージとファイル サイズの最大制限」
support.office.com

32 ビット環境の仮想アドレス空間は 2 ギガバイト (GB) で、Excel やブック、また同じプロセスで実行するアドインで共有されます。データ モデルのアドレス空間のシェアは 500 ~ 700 メガバイト (MB) まで増やすことができますが、他のモデルやアドインが読み込まれている場合は少なくなることがあります。
64 ビット環境では、ファイル サイズに対し、ハード制限はありません。ブックのサイズは、利用可能メモリとシステム リソースでのみ制限されます。
注: データ モデルにテーブルを追加すると、ファイル サイズが増大します。多数のデータ ソースやデータ型をブック内で使って複雑なデータ モデル関係を作るつもりではない場合、テーブル、ピボット テーブル、データ接続をインポートまたは作成するときに、[このデータをデータ モデルに追加する] ボックスをオフにします。
詳細については、「データ モデルの仕様と制限」を参照してください。

これはようするに、.xlsx が zip 展開 されたときに、2GB を超えているときに、32ビット Excel では、オンメモリに展開できない、と言っているようだ。いずれにしても、64 ビットで開けられたので、32ビットでは 232M はでかすぎる、ということだろう。

おわり。

app ファイルが展開できない(解決編)

Access Service 2013 SharePoint 2013 SQLServer コンピューティングティップス

つづき。

Windows Update 終了後、SPConfig を走らせると、構成失敗。数回再起動しても、構成失敗。
サーバーの全体管理は起動できるので、サイトのほうを表示すると、以下のような例外。

f:id:lakeside_shinnosuke:20160227105002p:plain

これは SPConfig の構成失敗のときと同じメッセージ。

アセンブリ 'Microsoft.SharePoint.WorkflowServices, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' からの型 'Microsoft.SharePoint.WorkflowServices.WorkflowServiceApplicationProxy' にあるメソッド 'Upgrade' に実装が含まれていません。

うーん、ワークフローサービスとかインストールしてないしなあ、ワークフローの構成でもしてみるかなあ、と思いつつ、ググると以下ページがヒット。

セキュリティ更新プログラム MS14-022 for SharePoint 2013 について - Japan SharePoint Support Team Blog - Site Home - TechNet Blogs

このメッセージは既知の問題だったらしく、「追加の更新プログラム (2880963) (osfserver) 」をインストールすることで、見事、Access Services の表示までいきました。

app ファイルが展開できないときの結論(2015/2/27現在)

  • dacpac を取り出して展開してみる
  • app ファイルに環境に依存するような情報がないかどうかを確認(SQLServer のセキュリティ設定など)
  • Windows Update の重要な更新は最後までやりきる
  • Windows Update が終わったら、SPConfig を忘れない
  • Windows Update に含まれない更新プログラムがある場合は、既知の問題として公開されているので、個別に当てる(例外の内容でググるとよい)

SharePoint ってほんとうにたいへん。

ごきげんよう。

ハイパーホルダー

パーソナルインフォメーションマネジメント 差込手帳 Quovadis 革製品 ハンディピック

ながさわさんは差し込み手帳を使っています。

http://www31.atwiki.jp/shin_nosuke?cmd=upload&act=open&pageid=16&file=my_pocketbook_and_willcom.jpg

www31.atwiki.jp

もう、アドエスは使ってないし、スリップオンの BT の手帳カバーは生産中止になったようだから、栃木レザーに変えるつもりなのだが、中身は Quovadis の VisoPlan と ダイゴーの ハンディピック で変わらない。

栃木レザー
item.rakuten.co.jp

ビソプラン
shop.quovadis.co.jp

ハンディピック
item.rakuten.co.jp


ところで、ハンディピックには、ひとつの差込口を2つに増やせる、ハイパーホルダーというのがあり、これが、だいぶ痛んできたので、買い換えようと思っていたら、プラスホルダーというのしか出てこない。これも、生産中止になったのかなと思って、生駒のダイゴーに問い合わせました。

拝啓 ますますご清祥のこととお慶び申し上げます。
また日頃は弊社製品をご愛顧賜り、厚く御礼申し上げます。

この度はダイゴーオンラインショップをご利用いただきありがとうございます。

C8〇〇〇の商品番号で販売をしておりました、当社のHandy Pickシリーズは
全品リニューアルの為品番を変更して販売を行なっております。
(現在、Handy PickリフィルはC5〇〇〇の商品品番へと変更しております)

お探しいただきました「ハイパーホルダー C8017(小)/C8117(大)」は
現在では「プラスホルダー」と名称変更をし、「C5201(小)/C5301(大)」の品番で
販売をさせていただいております。

https://shop.daigo.co.jp/?s=%E3%83%97%E3%83%A9%E3%82%B9%E3%83%9B%E3%83%AB%E3%83%80%E3%83%BC

どうぞご検討下さい。

弊社では今後もお客様に喜んで頂ける商品作りに努めて参りますので、
引続き弊社製品をご愛顧賜ります様、宜しくお願い致します。

                            敬具  

プラスホルダーに名前が変わったそうです。なるほど。
もうすぐ、頼んでいる、栃木レザーが納品されるので、そのときにオプションを全部きれいにしようかと。

ごきげんよう。

app ファイルが展開できない(さらにつづき)

Access Service 2013 SharePoint 2013 SQLServer コンピューティングティップス

つづき。
前回失敗した SPConfig は、再起動後にもういちどやると成功。

f:id:lakeside_shinnosuke:20160225051428p:plain

しかし、Access Web データベースは表示できない。

  • アプリをテンプレートから作り直す
  • アプリカタログに app ファイルを展開し直す
  • オンプレミス での Access Services の セットアップの手順の見直し(https://technet.microsoft.com/ja-jp/library/jj714714.aspx)
  • Access Services アプリケーションの再作成
  • 構成ウィザードの再実行
  • もういちど SPConfig する

上記をやってみるも、現象は変わらず。

Microsoft Community の以下のポストと、この現象が同じだが、返信がついていない。
http://answers.microsoft.com/en-us/office/forum/office_2013_release-access/access-2013-app-exception-0x80004005-whenever-i/339fb3b0-3b78-4a5b-b5be-bd574ba70d55

以下の記事は、COMException は同じだが、GetTimeZoneInfo が例外をスローしているわけではない。
SharePoint COMException (0x80004005): Cannot complete this&nbsp;action.helshabini.wordpress.com

Microsoft.SharePoint.Library.SPRequestInternalClass.GetTimeZoneInfo の COMException という意味では、以下の記事が現象が似ていたが、こちらのサイトのタイムゾーンの設定は、JST で正しい。UTC にしても現象は変わらない。
Ulmaskulov SharePoint tRips: сентября 2015
(ロシア語かな?)

以下の記事では、SQL Server に対して、DBCC CHECKDB を試みよとあるが、エラーは発見できず。
[COMException (0x80004005): Cannot complete this action Microsoft.SharePoint.Library.SPRequestInternalClass.GetFileAndMetaInfo

ちなみに DBCC は以下のサイトが詳しい。
komatumoto.blog55.fc2.com

SPRequestInternalClass で HandleCOMException なんだから、COM が呼び出せないか、COM 呼び出しの失敗なのだから、インストーラから SharePoint の修復を試みようとするも、このまえの Update が上書きされることが気がかり。

基本に立ち返り、Windows Update をもういちど確認すると、

f:id:lakeside_shinnosuke:20160225051132p:plain

まだありました。。。なんてこった。パンナコッタ。

(つづく)

app ファイルが展開できない(つづき)

Access Service 2013 SharePoint 2013 SQLServer コンピューティングティップス

前エントリからのつづき。
予想通り、データベースごとの、セキュリティ設定がされていたようなので、それを外してもらった状態で app ファイルを送ってもらったら、アプリの作成は完了するようになったが、開くと、以下の画面の日本語バージョンが出るようになる。

f:id:lakeside_shinnosuke:20160223031321p:plain

ハードコピーは取らなかったのだが、日本語だと、「アプリケーションを開くことができません」、Access で開くと「バージョンを認識できません」のようなメッセージだったと思う。

以下のサイトによれば、

www.concurrency.com

Dac Framework のバージョンが古いとか、ファーム間で、バージョンが合っていないとか書いてあるので、Dac Framework を探して、インストールしてみるが、インストールはされているといわれる。しょうがないので、Windows Update を試みると、SharePoint の更新が出てきた。更新完了した図は以下のとおりで、KB2760508 とか KB2956180 とか KB2920763 あたりが入ったようだ。

f:id:lakeside_shinnosuke:20160223031936p:plain

これで、アプリを作成し直して、アクセスすると、以下のようなエラー。メモリが足らないような気がしたので、6G → 7.5G に増やして、やってみても変わらず。

f:id:lakeside_shinnosuke:20160223032253p:plain

Windows Update したから、やっぱり SPConfig しないといけないかと、製品構成ウィザードを走らせると、たくさん、アップグレードするものがあったもよう。

f:id:lakeside_shinnosuke:20160223032536p:plain

f:id:lakeside_shinnosuke:20160223032523p:plain

長い、ひたすら待つのだが。

f:id:lakeside_shinnosuke:20160223032643p:plain

うがっ。(つづく)