カテゴリー: Development

指定文字列がストアドに含まれるかどうか確認するためのSQL。

SELECT obj.type, obj.name, mdl.definition
FROM   sys.sql_modules AS mdl
INNER JOIN sys.objects AS obj
ON mdl.object_id = obj.object_id
WHERE  mdl.definition LIKE '%order%'
ORDER BY obj.type, obj.name;

SQLServerはテーブル指定のバックアップができない？

ってなことで、エクスポートとインポートをお勉強。

1) Export

BCP {DBName}.{scheme}.{Table} out c:\table1.txt -c -S localhost -U {user} -P {pwd}
BCP "SELECT * FROM {DBName}.{scheme}.{Table} WHERE 1=1" queryout c:\table2.txt -c -S localhost -U {user} -P {pwd}

1つ目はテーブルそのまま。テーブル指定後は、「out」を指定して、エクスポート先ファイルを指定してExportする。
2つ目は条件句で範囲を指定してその分だけデータをとってくる方法。こっちは「queryout」でExportする。
いずれも、その後ろは接続先とユーザ名・パスワードを設定。

2) Import

BCP {DBName}.{scheme}.{Table} IN c:\table1.txt -c -S  localhost -U {user} -P {pwd}

インポート先を指定後、「IN」でインポート元ファイルを指定。
こちらもインポート先のDB接続情報を後ろに入れる。
挿入するので、主キーが重複しないようあらかじめ削除しておくこと。

モチの論、BCP.exeがないと動きまヘン。

SQLServerはそのDB設定によって、大文字小文字や半角全角を意識しないとのこと。
つまり、’A’ = ’a’ は True になるわけ。
現在の設定を確認したい場合は、以下のようなSELECT文を実行する。
（{DBNAME}のところに対象DB名を入れる）

SELECT DATABASEPROPERTYEX('{DBNAME}', 'collation');

これはこれでいいんだが、で、意識したいときはどうすんの？って話で、

SELECT * 
FROM foo
WHERE x = 'X' COLLATE Japanese_CS_AS
AND   y = 'Y' COLLATE Japanese_BIN;

と、まぁこんな風に条件項目ごとに設定をしていくとのこと。

BINやCS、AS、KS等の違いは以下参照。
Windows 照合順序並べ替えスタイル

Struts2でjsp上に変数を定義したいなぁ・・・と思った。
普通はこんな使い方しないんだろうけど、同じ文言が何度も出てくるときに全部直すのは面倒だな、と思ったら、やはりどこかで指定してやるほうが楽だよね！と安易に考え調べた。

　<s:set var="変数名" value="初期値" />

これだ！前にもカウント変数として使ったことがある。
だけど、文字列を設定したことがないんだ。どうすればいい？

<s:set var="baseName" value="'あいうえお'" />

なるほど。ダブルクォーテーションの中にシングルクォーテーションで文字列を括ればよいのか！

ワークシート上にある「SHP1」という名前の図形をコピーして、「SUB_SHP1」という名前を付け、B2セル内に配置するロジックを考えてみた。
コピー元の図形のDuplicateメソッドをつかって、新しい図形(コピー先:以下サンプルでは変数「shp」のこと)を作り、その新しい図形のプロパティをいじって、図形の位置とか、図形の名前、図形に紐づくマクロの設定などを行う。
サンプルでは、セルB2の下部中央に配置する方法。

Dim ws  As Worksheet
Dim shp As Shape

Set ws = Thisworkbook.Worksheets("Sheet1")
Set shp = ws.Shapes("SHP1").Duplicate
With shp
  .Top = ws.Range("B2").Top - .Height -5
  .Left = ws.Range("B2").Left + (ws.Range("B2").Width - .Width) / 2
  .Name = "SUB_SHP1"
  .Visible = msoTrue
  .OnAction = "RunXXX"
End With
Set shp = Nothing
Set ws = Nothing

図形のコピーに「Duplicate」を使うというのは初めて知った。duplicateの意味が「複写する」ってことらしい。

Windows2000をVMWareに入れるときに困ったのは、
1) Windows10、VMware Workstation 12 Playerでやると入れられなかった。
2) Windows7、VMware Workstation 12 Playerでやると入ったが、
　Windows2000(SP4)だけではVMware Toolsが入らず修正プログラム「KB835732」が必要だった。

というこの２点。

1つ目は、当該の仮想マシンの設定で、プロセッサの優先モードを「Intel VT-x or AMD-V」か、「Intel VT-x/EPT or AMD-V/RVI」にする。

2つ目は、以下のアップデートファイルをインストールする。
Windows 2000 用セキュリティ問題の修正プログラム (KB835732)
http://www.catalog.update.microsoft.com/Search.aspx?q=KB835732

で、解決！

数値(整数)チェックをしようと、

Function CheckNumber(v as Varient) As Boolean
  CheckNumber = True
  If Len(v)=0 Then Exit Function
  If IsNumeric(v) Then Exit Function
  CheckNumber = False
End Function

とか記載したら、vに「a」とか入れてもTrueで返ってきてしまった。

Function CheckNumber(v as Varient) As Boolean
  CheckNumber = True
  If Len(Cstr(v))=0 Then Exit Function
  If IsNumeric(Cstr(v)) Then 
    If InStr(Cstr(v),".")=0 Then Exit Function '小数じゃなかったら(簡易チェック)
  End If
  CheckNumber = False
End Function

みたいに、文字列に変えてからIsNumericしたらうまくいった。
関数の引数を文字列型にするってのも手だけど、簡易版なのでこれでいいかなと思った。

開発テストのため、SFTPサーバが必要になったが、社内の環境にSFTPサーバはない。
外部のフリーサーバーがないか調べたが、めぼしいものはない。
freeFTPd.exeというフリーソフトを使えば簡単にできるらしい。

で、社内のWindows Serverにインストールして、サービス開始。
ポートNo.22にしてSFTPを起動し、ノートPCから接続してみた。

つながらへんがな・・・・・・

おかしいなぁ～、なんでかなぁ～、簡単やいうたがな・・・・・
とか思いつつ、ポート開放せなあかんのちゃうかなぁ・・・となんとなく感じながら、ファイアウォールの設定で、ポートNo.22を開放。

再度ノートPCから接続。
いけたがな！！

ということで、疎通テストも無事完了。
めでたし、めでたし！

Blogの外観をちょいと変えてみた。また変えるかも。

さて、Windows10でExcel2010とか2013とかを使うようになって、Windows7で作ったExcelマクロのActiveXコントロール（コンボボックス）がうまく表示されないようになった。

これは困った・・・

グループ化すれば治る・・・って、治んない。

困りましたな・・・

ってことで、もうフォームコントロールに変えることにした。
面倒だわ・・・
ActiveXコントロールだと、リストの情報はマクロ上で設定すればいいのだが、フォームコントロールはリストの情報をセルに持たせて、そのセルとリンクを貼る・・・という厄介な手順を踏まなければならない。

ということで、ActiveSheet上にあるフォームコントロール「DLIST」のリストにしたい情報が、同じシートのセル「A2～A10」にあるとすると、

With ActiveSheet.Shapes("DLIST").ControlFormat
    .ListFillRange = "A2:A10"
    .LinkedCell = "B2"
End With

参照リストセルが別のシート(仮に「Sheet2」とする)にある場合、ListFillRange の設定をちょっと変える必要がある。

    .ListFillRange = "Sheet2!A2:A10"

でも、コントロールのフォントサイズの変え方がわかんない・・・
もうヤダ。

SQLiteでテーブルの一覧を取得する場合、コマンドの「.schema」を使う方法もあるが、面倒な時もあるので、以下のようにSELECT文を使う。

SELECT * FROM sqlite_master
WHERE type = 'table' 
ORDER BY name;

WHERE句の条件を外すと、オブジェクトの一覧になって、INDEXの情報なんかも取得できる。

さて、6月も中盤。
4月から3か月間かけて行う新人研修も終盤だが、どうも、今年はイマイチ。
新人がイマイチなのか、我々がイマイチなのか、どちらもダメなのか・・・

それはともかく、中学生や高校生が読めるような漢字を読めない人がいるのがまずウンザリ。
昨今、高等教育の無料化を声高に言う人がいるが、そもそも、初等・中等教育をしっかりしてほしい。
小学生でも読めそうな漢字も読めない大人って、社会に必要なのか？
初歩的な現代国語ぐらいちゃんと勉強させてほしいものだ。

Shell関数を使って別のexeを起動。

できんかった・・・・・・

なので調べた。

どうやら、カレントディレクトリをexeのある場所に指定しないとダメな様子。
で、ChDir関数を使ってカレントディレクトリの変更！ってやってみたけど、うまくできなかったので、別の方法でカレントディレクトリを変える。

以下は、Workbookと同じフォルダにあるtest.exeを実行してみるサンプル。
起動できたら、Excelを最小化させる

    Dim p   As String
    Dim res As Integer
    
    On Error Resume Next
    p = ThisWorkbook.Path
    If Right(p, 1) <> "\" Then p = p & "\"
    With CreateObject("WScript.Shell")
        .CurrentDirectory = p    // カレントディレクトリを変える
    End With
    res = Shell("test.exe", vbNormalFocus)
    If res <> 0 Then Application.WindowState = xlMinimized

@pagesからこっちのサーバに移設してから、3か月が経った。
未だに旧サーバも立ち上げっぱなしなのだが、こちらのサーバの投稿記事のリンク（とくに画像）が正しく貼れていなかったり、リンク切れだったりするのを順に直していこうと考えている。
WordPressは投稿するにはラクチンなのだが、いろいろカスタマイズするのが面倒だなぁと感じる。
5年前ぐらいだと、ちょうどPHPの仕事もしてたし、暇もあったし、やる気もあったから、面白半分でいじってたのだが、5年経つと人間こうも変わるものかというほど、あまりやる気が起きない。

まぁ、しばらくは記事の修正を行って、終わったらカスタマイズしてみるか・・・
（いつになることやら）

Windowsのログインユーザアカウントをとってきたい！ってのを調べてロジックに組み込んだはいいが、このブログ上にメモしてなかった。

まぁWindowsにログインするときに入力するアカウントのことなんだけど、環境変数関係の情報を取得するときは、Environ関数を使う。

Environ(“USERNAME”)

と、こんな感じ。

引数にどんなものがあるか、どんな戻り値になるか、デバッグして調べてみるときは、こんな風に。

Debug.Print "OS：" & Environ("OS")
Debug.Print "デフォルトドライブ：" & Environ("HOMEDRIVE")
Debug.Print "デフォルトユーザパス(ドライブなし)：" & Environ("HOMEPATH")
Debug.Print "tempフォルダパス：" & Environ("TEMP")
Debug.Print "ログインユーザID：" & Environ("USERNAME")
Debug.Print "systemフォルダパス：" & Environ("WINDIR")

Processing2.2.1で確認。

Processingで作成したexeを引数渡しで起動させる場合、というので調べてみたが、一番よさげなのは下記のURLの説明とサンプルだと思った。

uncertain world「Processingをコマンドラインから引数つきで実行する方法」

特に良いと感じたのは引数を「xxx=yyyy」という記載で渡すところ。

というのは、Processingで開発中のときにRUNするときと、Exportしてexe起動させるときでは、引数の数が違っている。
前者だと引数の最初に、「–sketch-path=XXXXX」が入っていて、後者だと入らない。
なので、引数の何番目を見て、ウンチャラカンチャラ・・・みたいなことはできない訳。

void setup() {
  for (String a : args) {
   String[] a2 = a.split("=");
   if (a2[0].trim().equals("para1")) {
    ・・・処理・・・  
   } else if (a2[0].trim().equals("para2")) {
    ・・・処理・・・  
   }
  }
}

と、こういう感じで、setupメソッド内に、引数取得処理を入れておく。

起動させるときは、

Prog.exe para1=1 para2=ABC

ってな感じでパラメータを渡してやればよい。

基本的にScripting.Dictionaryは、

Dim dic     As Object

Set dic = CreateObject("Scripting.Dictionary")
If Not dic.Exists(a) Then
  dic.Add a,b
End If

みたいに、Existsメソッドを利用することが多いのだが、
やはり１つずつ確認して処理を行うこともなくはない。

Dim i As Long
For i = 0 To dic.Count - 1 Step 1
    debug.print dic.Keys()(i) & "-" & dic.Item(i)
Next i

もしくは、

Dim v As Variant
For Each v In dic.Keys()
    debug.print CStr(v) & "-" & dic(v)
Next

で、いずれも、Keys() と両括弧を付けるところがポイント。

色々サイトを見てみたのだが、この()が抜けていたり、ItemがItemsになっていたりして、混乱している記載が多く、ここまでたどり着くのに英語のサイトまで見に行ってしまった。

非常にやっかいだが、もともとこういう使い方するようなオブジェクトではないのだろうから、仕方ない。

ちなみに削除するときはRemoveを使う。（Deleteではない）

Dim v As Variant
For Each v In dic.Keys()
    dic.Remove v
Next

同じIDのTITLEデータをカンマ区切りでとってきたい！
とか思って漁っていたら、

MySQLでidだけをSELECTして、カンマ区切りにして出力する

という、ページが見つかった。
GROUP_CONCATという関数を使い、区切り文字をカンマに指定する方法である。

SELECT GROUP_CONCAT(title SEPARATOR ',') FROM t_sample WHERE id = 1;

PostgreSQLだと、ARRAY_TO_STRING

＜参照＞PostgreSQL覚書 配列

ファイル渡すからプログラム組んでくれ、っていわれてプログラム作成している最中、
ファイルも寄越さないうちから、「進捗どうですか？」って聞かれて、ちょいムカっとしたのは今週の始め。
んで、ムカついたからもう少し待ってねメールのついでに、送ってきた中途半端な仕様書の「重箱の隅にもならない場所」(ちゃんと書いて！ってな場所）をつつくような質問を畳みかけてやった。（フン！）

まぁ、別に大して怒っている訳ではないのだが、
　「自分がやるべきこともしないで、なんなんだ？」とか、
　「いやいや○日かかるって言ってるでしょ！何日目だよ今日！」とか・・・
色々思うところはあった。
そうは言いつつ、自分もこうならないように気を付けようと、ちょいと思ったのだった。

で、慣れないDoCmdに苦戦しつつ、ExcelファイルをReadOnlyで開いて、データを読込み、終わったら閉じる処理を書いてみた。
DisplayAlertsをFalseにしているのは、Excelで発生したエラーやら確認メッセージを出させないためだけで、値をとってくるだけなら特に必要はないと思う。

Dim xls As Object
Dim wb  As Object

Set xls = CreateObject("Excel.Application")
xls.DisplayAlerts = False
Set wb = xls.Workbooks.Open(FileName:=[Excelパス], ReadOnly:=True)

 ・・・（読込処理）・・・

wb.Close SaveChanges:=False
Set wb = Nothing 
xls.DisplayAlerts = True
xls.Quit
Set xls = Nothing

面倒だなぁ・・・Excelでいいじゃん？
だって、xlUpとかxlToLeftとかのExcelVBA特有の定数も使えないし。
（参照設定で設定すれば、使えるようにはなるけどサ）

とは思ったのだが、まぁ、後々Accessの方がいいこともあるかもって思ったので、お客さんの言うとおりAccessにした。
だけど、やはり、今回は、Accessにデータを取込むわけでもなく、別DBに出力するロジックだったので、尚更、AccessでExcelを開くというのは、かなり馬鹿馬鹿しく感じた。
もちろん、できるならImportしたいところだが、できるほど単純なデータではなかったので、わざわざロジックを組む。

　　・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・

で、今回は読み込んだデータをAccessテーブルには放り込まなかったのだが、Accessに取込むとなるとどうするのか・・・ということも考えてみた。

しっかし、AccessのTableってのは、INSERT文で出力しようが、AddNew・Updateで入れてみようが、まぁ、遅いのなんの・・・
さらに複数のSQL文を一括実行する方法なんて探してみたのだが、

Accessにはない！

ということなので、１つ思いついたのが、
　・Excelデータを取込んで、テーブルImport用のCSVを作成。
　・CSVをImportする。
という方法。
確かに、INSERTを発行するより速いのだが、かといって、すこぶる速いという訳でもなかった。件数が多ければ、明らかに速くなる気はする。

しかし、ここで問題発生。文字コードだ。

UTF-8文字が入力されたExcelデータであったため、ADO.Streamを利用して、BOM無しのUTF8でCSV出力。
かつ、インポートするときも

　DoCmd.TransferText acImportDelim, , temp_table , csv_path, True, , 65001 　　

というように、文字コード(65001がUTF-8を示す)を指定して取込む必要があった。

ということで、いろいろ試しにやったのだが、プログラム自体はすんなり作成できた。

ってか、まだファイル来ないし・・・

略すとPPAPみたいになってるけど、ExcelのVBAプロジェクトのパスワードがわからないとき（忘れたとき）は、それを外すやり方がある。

何だっけ・・・

と思って調べた。

ここでは詳細は書かないけど、検索ワード的には
　Excel, VBA, DPB=
といったところか。

DPB=のダブルクォーテーションで括られた中身が暗号化?されたPWで、それをうまいこと置換えるわけなんだけど、バイト数が合わないとVBA関連のファイルが潰れちゃうので注意しなくてはならない。
なので、バイト数のきっちり合った置換文字を作成するため、新規ExcelファイルにVBAパスを付けて保存した後バイナリファイルの中身を確認する。
（パスワードの桁数からDPBの中身の桁数が容易にわかるわけではないので、色々作って合致した！ってなパスワードが見つかってから置換えるわけ。ちょいと面倒なのだが、熱さ過ぎればチョメチョメチョメと。）

あらら、結構書いてしまったぞい。
忘れたときのためなので、悪用するべからず！

Windows上で動いている場合にしか利用できない方法だけど、メモ。

JavaでサーバのMACアドレスを簡単に取得する方法を漁ってみたのだが、結局は「ARPコマンドを実行する」ぐらいしかないようである。
なので、ちょっとガックシきてしまった。

1) target に サーバ名を設定。
2) Windowsコマンド arp に、targetサーバのIPアドレスを設定して実行。
3) コマンドプロンプトの出力からtargetサーバのIPアドレスのMACアドレスを取得

    String target = "ここにサーバ名を設定";
    String adr= "";
    try {
      // 1)
      InetAddress ip = InetAddress.getByName(target);
      // 2)
      ProcessBuilder pb = new ProcessBuilder("arp", "-a", ip.getHostAddress());
      pb.redirectErrorStream(true);
      Process proc = pb.start();
      proc.waitFor();
      // 3)
      InputStream is = proc.getInputStream();
      BufferedReader br = new BufferedReader(new InputStreamReader(is));
      try {
        for (;;) {
          String line= br.readLine();
          if (line == null) break;
          if (line.indexOf(ip.getHostAddress()) >= 0) {
            adr = buf.substring(23,45).trim();
          }
        }
      } finally {
        br.close();
      }
      if (res.length() > 0) {
        System.out.println("Server ["+ target+ "] , IP [" + ip.getHostAddress() + "] , MAC [" + adr + "]");
      }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
    }

参考URL
きしだのはてな「JavaでMACアドレスを取得する」
ひしだま’s 技術メモページ「Java > 外部プロセス起動」」

世の中には、ありがた迷惑とか、小さな親切大きなお世話とか、そういうことがわからない人が一杯いるようだ。
そういう人は、「やるな！」「やらないでくれ！」っていっても、「やる」のだなぁ。
人のやること横取りするんじゃないよ！と、叫びたかった、今日の朝。

それはそうと、SQLiteでSELECT文を使ってUPDATEしたい！というのがあって、
そうすると、まぁ、

　UPDATE table1 SET
 　col1 = (SELECT col1 FROM table2 WHERE .... )

みたいにすればできるぜ！みたいなことはいっぱい書いてあるんだけど・・・

　そうじゃないんです！
　JOINで連結させて、ホゲホゲホゲ・・・とやりたいんです！

という要求は満たされないわけ。

で、調べた。

SQLiteはWITH句が使えるらしい。
んで、WITH句をうまく活用して、UPDATEできるらしい。

他のDBのSQL文を

UPDATE table1 SET
    col1 = a.col1
  , col2 = a.col2
 FROM (SELECT .... FROM .... INNER JOIN ....) a
 WHERE table1.id = a.id

とすると、
WITH句を使ったSQL文は

WITH a AS (
  SELECT .... FROM .... INNER JOIN ....
)
UPDATE table1 SET
    col1 = (SELECT col1 FROM a WHERE table1.id = a.id)
  , col2 = (SELECT col2 FROM a WHERE table1.id = a.id)

みたいな感じ。

で、これを私が愛用する「A5:SQL Mk-2」（SQL開発ツール）で実行したのだが、うまくいかずに、「あれ？できないの？」とか思って悩んだのだが、ツール上ではうまくいかないだけで、SQLiteのコマンド実行では、ちゃんと動いた。

ホント、統一させてほしいんですけど。