社内SEの徒然なる日記

oo4oでExcelから登録してみた(ExecuteSQL)

登録・更新・削除

今回は、oo4oを使用してDML文(Insert、Update、Delete)を実行してみます。
oo4oから、これらのSQLを実行するにはExecuteSQLメソッドを使用する方法と、CreateSQLメソッドを使用する方法があります。

今回は、ExecuteSQLメソッドを使用してみます。

サンプルソース

配列myTblの内容を、TESTDBテーブルに登録(Insert)するサンプルです。

' データ登録サンプル(ExecuteSQL:バインド変数なし)
'
Sub insertTestExecuteSQLNotBind()

'--------------
' 変数宣言
'--------------

'SQL文
Dim mySql As String

'登録データ
Dim myTbl(2, 1) As String '登録用データ配列
Dim myInd As Integer 'カウンタ

'oo4o用のオブジェクト
Dim OO4OSession As Object
Dim EmpDb As Object
Dim sqlStatement As Object

'実行時情報
Dim myCount As Long

'-----------------
' 初期処理
'-----------------

'登録用のデータ作成
myTbl(0, 0) = "A": myTbl(0, 1) = 1
myTbl(1, 0) = "B": myTbl(1, 1) = 2
myTbl(2, 0) = "C": myTbl(2, 1) = 3

'-----------------
' DB接続
'-----------------

'更新用のDB接続
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
Set EmpDb = OO4OSession.OpenDatabase(ORA_DBSTRING, ORA_USERNAME & "/" & ORA_PASSWD, 0&)

'-----------------
' データ登録
'-----------------

'Insert文の実行
For myInd = LBound(myTbl) To UBound(myTbl)

'INSERT文作成
mySql = ""
mySql = mySql & " INSERT INTO "
mySql = mySql & " TESTDB (TESTSTR,TESTNUM)"
mySql = mySql & " VALUES "
mySql = mySql & " ("
mySql = mySql & "'" & CStr(myTbl(myInd, 0)) & "'"
mySql = mySql & CInt(myTbl(myInd, 1))
mySql = mySql & " )"

'Insert文を実行
myCount = myCount + EmpDb.ExecuteSQL(mySql)

Next

'-----------------
' 終了処理
'-----------------

'終了メッセージの表示
MsgBox "正常終了!" & Chr(10) & Chr(10) & "登録件数:" & myCount

'オブジェクトの開放
Set sqlStatement = Nothing
Set EmpDb = Nothing
Set OO4OSession = Nothing

End Sub


サンプルの解説

1. 変数宣言、初期処理、DB接続

DB接続までの処理は、事前準備なので省略です。
OraDatabaseオブジェクトの作成で使用しているORA_DBSTRING、ORA_USERNAME、ORA_PASSWDには、必要な接続情報が定義されているとします。

2. データ登録

変数mySqlにSQL文(Insert文)を設定して、ExecuteSQLメソッドでSQLを実行します。
これを配列の件数分実行します。

ExecuteSQLメソッドは、処理された行数を返します。
これを使用して登録件数をカウントしています。

3. 終了処理

ExecuteSQLメソッドの戻値から取得した登録件数を表示して、使用したオブジェクトを解放します。

バインド変数を使用するサンプル

これだけだとつまらないので、バインド変数を使用したサンプルも作りました。

' データ登録サンプル(ExecuteSQL:バインド変数あり)
'
Sub insertTestExecuteSQL()

'--------------
' 変数宣言
'--------------

'SQL文
Dim mySql As String

'登録データ
Dim myTbl(2, 1) As String '登録用データ配列
Dim myInd As Integer 'カウンタ

'oo4o用のオブジェクト
Dim OO4OSession As Object
Dim EmpDb As Object
Dim sqlStatement As Object

'実行時情報
Dim myCount As Long

'-----------------
' 初期処理
'-----------------

'INSERT文作成
mySql = ""
mySql = mySql & " INSERT INTO "
mySql = mySql & " TESTDB (TESTSTR,TESTNUM)"
mySql = mySql & " VALUES (:ADDSTR, :ADDNUM)"

'登録用のデータ作成
myTbl(0, 0) = "A": myTbl(0, 1) = 1
myTbl(1, 0) = "B": myTbl(1, 1) = 2
myTbl(2, 0) = "C": myTbl(2, 1) = 3

'-----------------
' DB接続
'-----------------

'更新用のDB接続
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
Set EmpDb = OO4OSession.OpenDatabase(ORA_DBSTRING, ORA_USERNAME & "/" & ORA_PASSWD, 0&)

'バインド変数の設定
EmpDb.Parameters.Add "ADDSTR", CStr(myTbl(0, 0)), 1
EmpDb.Parameters("ADDSTR").ServerType = 1 'ORATYPE_VARCHAR2

EmpDb.Parameters.Add "ADDNUM", CInt(myTbl(0, 1)), 1
EmpDb.Parameters("ADDNUM").ServerType = 2 'ORATYPE_NUMBER

'-----------------
' データ登録
'-----------------

'Insert文の実行
For myInd = LBound(myTbl) To UBound(myTbl)

'バインド変数の再設定
EmpDb.Parameters("ADDSTR").Value = CStr(myTbl(myInd, 0))
EmpDb.Parameters("ADDNUM").Value = CInt(myTbl(myInd, 1))

'Insert文を実行
myCount = myCount + EmpDb.ExecuteSQL(mySql)

Next

'-----------------
' 終了処理
'-----------------

'終了メッセージの表示
MsgBox "正常終了!" & Chr(10) & Chr(10) & "登録件数:" & myCount

'オブジェクトの開放
Set sqlStatement = Nothing
Set EmpDb = Nothing
Set OO4OSession = Nothing

End Sub


変更箇所

1. SQL文の作成

SQL文の作成を初期処理で行います。
この時に、VALUESの値にはバインド変数を定義しておきます。

2. バインド変数の初期設定
Addメソッドで、OraParametersコレクションにパラメータを追加します。
第一引数には、SQL文の作成時に設定したバインド変数を設定。
第二引数には、パラメータの初期値を設定しておきます。
第三引数には、1(入力変数)を設定します。

次に、ServerTypeプロパティにバインド変数のデータ型を設定します。
VARCHAR2の場合は 1 を、NUMBERの場合は 2 を設定します。
他の型の場合、下記のように設定する...ようです。
実は、VARCHAR2とNUMBERしか試してないんですよね。

3:SIGNED INTEGER
4:FLOAT
5:NULLで終了するSTRING
9:VARCHAR
12:DATE
68:UNSIGNED INTEGER
95:RAW
96:CHAR
97:NULLで終了するCHAR
105:MLSLABEL
108:OBJECT
110:REF
112:CLOB
113:BLOB
114:BFILE
247:VARRAY
248:NESTED TABLE
2000:RAW


3. ExecuteSQL
後は、バインド変数に設定する値を変えながらExecuteSQLメソッドを実行するだけです。
毎回SQLを作り直すより、こちらの方がシンプルですね。

補足

oo4oの場合、初期設定は自動コミットのようです。
今回のサンプルで言えば、ExecuteSQLメソッドを発行するたびにコミットが実行されています。


前回:oo4oによる抽出件数の考察
次回:oo4oでExcelから登録してみた(CreateSQL)
投稿記事の一覧:目次

--- blog end ---
スポンサードリンク

PageTop

歯ぐきの水ぶくれの治療が大事になりました No4

久しぶりに

前回投稿したのが、2012/9/18なので、一月以上たってしまいましたね。
実は、歯医者自体は毎週かよっていたのですが、それほど書く事もなかったので、治療が終わるまで待ってました。
...記事を書くのが面倒になったわけじゃないですよ。

親知らずは抜いています

左上の奥歯は親知らずなので、抜歯する事になりました。
いつものように、麻酔して、変な機械を口に入れて、グリッグリッ!って感じで作業して、あっという間に終了です。
人によっては、親知らずの治療が大変な事もあるらしいのですが、私の場合はあっけなく終了です。
それにしても、不気味なくらいは治療に痛みを感じないですね。

その次の週に、右上の奥歯の治療。
こちらは「表面だけなので麻酔無しで」との事で麻酔無しになりました。
正直、ビビってたのですが、全く痛みなく終了です。

差し歯

私は、単純に仮歯を取って、差し歯を入れるだけだと思ってたんですけど、どうやら違ったようです。
結局は、ブリッジするための歯をガッツリ削って土台を作って、型を取り直して、取り付けという手順になりました。
書くと1〜2行ですけど、この行程に数週間を要しました。

んで、いざ取り付けという段で大苦戦。
どうやら、型が微妙に合わないらしく、付けて、外して、また付けての繰り返し。
削った歯は、神経を残した状態なので、外すたびに痛みます。
まぁ、実際は痛いって言うより染みるって感じで、大騒ぎするほどのモノじゃなかったんですけどね。

結局、一週間では終わらずに仕切り直し。
んで、先日もう一回いったところでようやくピッタリはまりました。

こんな感じ

上の左側3本が差し歯です。
歯医者の結果

こうして見ると、本物と変わらない(むしろ本物より本物らしい)ですね。
あぁ、長かったけど、ようやく終わりました。

一応、差し歯の様子見ということで、もう一回通院します。
受付のお姉さんによると「歯ブラシで落ちない汚れをとる」とか言ってたけど、前にやったホワイトニングのことか?
先生には何も聞いていないのだが...
腕は悪くないと思うのだが、要所要所で無用な出費をしている気がしてならん。


スポンサードリンク

PageTop

oo4oによる抽出件数の考察

抽出件数の取得方法

oo4oに限った話ではないでしょうが、SELECT文を実行する時には件数も一緒に取得したいものです。
マニュアルによると、RecordCountプロパティを使用する方法と、SnapshotIDを使用してカウント文を発行する2通りの手法があるようです。

ただし、マニュアルによるとRecordCountプロパティは、「Oracleから結果の表全体をただちにフェッチする必要....」云々ってことで性能問題が発生するかもしれないから使わないでねって書いてます。

ふーむ、気になるんで試してみましょうか。

実験してみた

1.関数の修正

前回(oo4oでExcelから接続してみた(抽出))作った、SELECT文を実行するoracleSqlSelect関数に、RecordCountプロパティを使用した件数取得と、デバッグログの出力を追加します。
こんな感じですね。


'OraDynasetオブジェクトの作成
Debug.Print Date & " " & Time & " SQL実行 start ---"
Set myDynaset = OraDatabase.CreateDynaset(pSql, ORADYN_READONLY)
Debug.Print Date & " " & Time & " SQL実行 end ---"

'スナップショットIDを取得
Set mySnapshotID = myDynaset.Snapshot

'RecordCountプロパティの値(件数)を取得
Debug.Print Date & " " & Time & " RecordCount start ---"
Debug.Print "myDynaset.RecordCount = " & myDynaset.RecordCount
Debug.Print Date & " " & Time & " RecordCount end ---"

'検索時のスナップショットIDから、カウントを実行
Debug.Print Date & " " & Time & " SQL(カウント) start ---"
Set myOraDynCount = OraDatabase.CreateDynaset("select count(*) NUMROWS FROM (" & pSql & ")", 0&, mySnapshotID)
Debug.Print Date & " " & Time & " SQL(カウント) end ---"

'レコード件数を共通変数に格納します。
OraRecodeCount = myOraDynCount.Fields("NUMROWS").Value
Debug.Print "OraRecodeCount = " & OraRecodeCount



2.結果件数の少ない場合のテスト

実行時間が10秒程度で、件数が数万件程度になるSQLを作って実行しています。
ちなみに、取得するITEMFはVARCHAR2型で8BYTEの項目です。

SELECT ITEMF FROM ABCD WHERE TTDATE >= '20120401'


実行結果はこんな感じです。
結果が約8万件として、RecordCountプロパティの取得に3秒、カウントの実行で9秒ですね。
やはり、カウントの実行時に時間を食ってますね。
スナップショットのテスト1

3.結果件数の多い場合のテスト

先ほどのSQLからWhere句を除いて、ABCDテーブルの全件を取得します。

SELECT ITEMF FROM ABCD'


今度の実行結果は、下記の通りになりました。
抽出件数が約345万件として、RecordCountプロパティの取得に172秒、カウントの実行で9秒ですね。
やはり、件数が多くなるとRecordCountプロパティの取得には時間がかかりますね。
スナップショットのテスト2

考察

大体は当初の想定通りで「抽出件数が少なければ、RecordCountプロパティの取得速度は速い」となりました。

しかし、これってどんな状況で使えるだろう?
例えば「実行に異様に時間が掛かる。SQLチューニングの余地は無い。処理時間は短くないとダメ。件数は数万件程度で収まる」という条件で、時間短縮のためにカウントの実行時間を削りたい、とか?
...すでに、この状況自体が異様ですよね。

どうやら、素直にSnapshotIDを使用してカウントを実行させた方が良いようですね。


前回:oo4oでExcelから接続してみた(抽出)
次回:oo4oでExcelから登録してみた(ExecuteSQL)
投稿記事の一覧:目次

--- blog end ---

スポンサードリンク

PageTop

oo4oでExcelから接続してみた(抽出)

データ抽出

前回(oo4oでExcelから接続してみた(接続・切断))は、oo4oを使用してExcelからOracleへの接続まで出来るようになりました。

今回は、データを抽出してみることにします。
要するに、SELECT文の実行ですね。

共通部品としての機能

とりあえず、SELECT文を実行して、結果を取得するってだけの機能を持った関数を作ろうと思います。
この関数を実行する側では接続先を意識しないようにしたいので、取得結果をVariant型の二次元配列として返却するようにします。

前回の共通変数や関数も使用してるので、このプロシージャだけだとコンパイルエラーになるんで注意して下さい。


' 機能 : SQL(SELECT文のみ)を実行する
'
' 引数 : pSql - 実行するSQL
' : pTitle - タイトル追加フラグ(true:有り、false:無し)
' :  ※ 省略可で省略時はfalseになる。
'
' 戻値 : 実行結果を格納したVariant型配列
'
Public Function oracleSqlSelect(ByRef pSql As String, Optional ByRef pTitle As Boolean)
On Error GoTo errEnd:

'-------------
' 変数宣言
'-------------

Dim myDynaset As Object 'Dynasetオブジェクト
Dim myData As Object 'Fieldsオブジェクト
Dim myOraDynCount As Object 'カウント用
Dim mySnapshotID As Object 'スナップショット

'結果配列
Dim myWriteInd As Long 'インデックス
ReDim myWkTbl(0, 0) '結果配列

'ループカウンタ
Dim i As Long

'---------------
' エラーチェック
'---------------

'Oracle接続確認
If Not oracleOpenChk Then
MsgBox "Oracle接続エラー"
GoTo errEnd
End If

'-------------
' SQL実行
'-------------

'OraDynasetオブジェクトの作成
Set myDynaset = OraDatabase.CreateDynaset(pSql, ORADYN_READONLY)

'スナップショットIDを取得
Set mySnapshotID = myDynaset.Snapshot

'検索時のスナップショットIDから、カウントを実行
Set myOraDynCount = OraDatabase.CreateDynaset("select count(*) NUMROWS FROM (" & pSql & ")", 0&, mySnapshotID)

'レコード件数を共通変数に格納します。
OraRecodeCount = myOraDynCount.Fields("NUMROWS").Value

'対象件数が0件の場合、処理を終了します。
If OraRecodeCount = 0 Then
GoTo errEnd
End If

'Fieldsオブジェクトを取得します。
Set myData = myDynaset.Fields

'--------------
' タイトル出力
'--------------

'結果配列の出力インデックスを初期化
myWriteInd = 0

'タイトル出力モードの場合、結果配列の先頭行にタイトルを追加します。
If pTitle = True Then

'結果配列を初期化
ReDim myWkTbl(OraRecodeCount, myData.Count - 1)

'タイトルを設定
For i = 0 To myData.Count - 1
myWkTbl(myWriteInd, i) = myData(i).Name
Next

'結果配列インデックスを増加
myWriteInd = 1

'タイトル無しの場合、配列の初期化のみ行います。
Else
'結果配列を初期化
ReDim myWkTbl(OraRecodeCount - 1, myData.Count - 1)
End If

'--------------
' データ出力
'--------------

'結果セットの配列転記
Do Until myDynaset.EOF

'結果配列に結果セットを転記します。
For i = 0 To myData.Count - 1
myWkTbl(myWriteInd, i) = myDynaset.Fields(i).Value
Next

'カーソル位置を前に進めます。
myDynaset.MoveNext

'結果配列の出力行を前に進めます。
myWriteInd = myWriteInd + 1
Loop

'オブジェクトの開放
Set myDynaset = Nothing
Set myData = Nothing
Set myOraDynCount = Nothing
Set mySnapshotID = Nothing

'--------------
' 終了処理
'--------------
normalEnd:
oracleSqlSelect = myWkTbl
Exit Function
errEnd:
'エラーメッセージの表示、戻値の初期化、オブジェト解放、エラークリア
If Not Err.Number = 0 Then MsgBox (Err.Number & ", " & Err.Description)
OraRecodeCount = 0
ReDim myWkTbl(0, 0)
Set myDynaset = Nothing
Set myOraDynCount = Nothing
Set mySnapshotID = Nothing
oracleSqlSelect = myWkTbl
Err.Clear
End Function


機能説明

1.OraDynasetオブジェクトの作成

事前に、Oracleに接続していることが前提の関数です。
OraDatabase.CreateDynasetメソッドでOraDynasetオブジェクトを作成します。
第二引数のOptionsには、ORADYN_READONLY(値:&H4&)を設定します。
これによって、ダイナセットを読取り専用にしています。

2.カウント

同じSQLにカウントをかぶせて再実行して対象件数を取得します。
最初は、RecordCountプロパティってので件数を取得するつもりだったのですが、これを使うと全ての行がフェッチされるというので、OraDynasetオブジェクトのSnapshotIDを使用してカウントを実行するようにしています。

取得結果が少ない件数になることが分っている時は、RecordCountプロパティの方が良い気もしますけどね。
まぁ、このあたりはどっかで検証してみますかね。

3.戻値の作成(タイトル)

関数の第二引数にtrueが設定されていた場合、取得結果の項目名を戻値の二次元配列の先頭に追加出力します。

4.戻値の作成(データ)

今度は、取得したデータを戻値の二次元配列の出力します。

5.後処理

使用したオブジェクトを解放して、戻値の配列を返却します。

使ってみた

とりあえず、テスト用に簡単な処理を作って動かして見ました。

Sub selectTest()

'変数宣言
Dim mySql As String 'SQL文
Dim myRs '戻値
Dim i As Integer 'ループカウンタ

'DBに接続
Call db接続

'SELECT文作成
mySql = "SELECT AMLWLMAM, AMUPLMAM FROM JSTM "
mySql = mySql & "WHERE AMLWLMAM > 300000000"

'SQL実行
myRs = oracleSqlSelect(mySql, True)

'結果件数の確認
If OraRecodeCount = 0 Then
MsgBox "該当するデータが存在しません。"
Exit Sub
End If

'実行結果をデバッグログに出力する
For i = LBound(myRs) To UBound(myRs)
Debug.Print myRs(i, 0) & " / "; myRs(i, 1)
Next

End Sub


イミディエイトウィンドウの出力結果は、こんな感じになりました。
WS001048.jpg

今度は、第二引数をFalseにして実行してみます。
  myRs = oracleSqlSelect(mySql, False)
今度は、タイトルが表示されなくなりました。
WS001049.jpg

前回:oo4oでExcelから接続してみた(接続・切断)
次回:oo4oによる抽出件数の考察
投稿記事の一覧:目次

--- blog end ---


スポンサードリンク

PageTop

oo4oでExcelから接続してみた(接続・切断)

Oracleに簡単に接続したい

諸般の事情で、Oracleのデータを取得して、Excelで集計とか編集とかをしたかったので、方法を考えてみました。
最初、ADOを使うつもりだったんですけど、ODBCの設定とか面倒だったので、oo4o(Oracle Objects for OLE)を使うことにしました。
もともと、私のPCにはOralceクライアントが入っていますね。

部品を作ってみた

まず、共通で使う変数をPublic変数として定義します。

Option Explicit

'CreateDynaset Method Options
Public Const ORADYN_DEFAULT = &H0&
Public Const ORADYN_NO_AUTOBIND = &H1&
Public Const ORADYN_NO_BLANKSTRIP = &H2&
Public Const ORADYN_READONLY = &H4&
Public Const ORADYN_NOCACHE = &H8&
Public Const ORADYN_ORAMODE = &H10&
Public Const ORADYN_NO_REFETCH = &H20&
Public Const ORADYN_NO_MOVEFIRST = &H40&
Public Const ORADYN_DIRTY_WRITE = &H80&

' ORACLEインスタンス
Public ORA_USERNAME As String 'ユーザー名
Public ORA_PASSWD As String 'パスワード
Public ORA_DBSTRING As String 'データベース

'共通変数
Public OraSession As Object 'Oracleインプロセス・サーバー
Public OraDatabase As Object 'ユーザー・セッション


次に、Oracleに接続する関数を作ります。
CreateObjectでインスタンスを作成して、OraSession.OpenDatabaseでデータベースに接続します。
ユーザー名とか接続先とかは、共通変数に設定された値を使用します。

' 機能 : Oracleに接続する
'
' 返り値 : true - 接続成功 , false - 接続失敗
'
Public Function oracleOpen() As Boolean
On Error GoTo abnormalEnd:

'Oracleインプロセス・サーバーを起動
Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'ユーザー・セッションの確立
Set OraDatabase = OraSession.OpenDatabase(ORA_DBSTRING, ORA_USERNAME & "/" & ORA_PASSWD, 0&)

'--- 終了処理 --------
normalEnd:

'接続成功
oracleOpen = True
Exit Function

abnormalEnd:

'エラー情報の表示と初期化
MsgBox (Err.Number & ", " & Err.Description)
Err.Clear

'オブジェクトの開放
Set OraSession = Nothing
Set OraDatabase = Nothing

'接続失敗
oracleOpen = False

End Function


切断する関数も作っときます。
単純に、使用したオブジェクト参照を解除するだけです。
ちなみに、明示的に切断しなくても、Excelが終了すると勝手に切断されます。

' 機能 : Oracleを切断する
'
Public Sub oracleClose()

'オブジェクトの開放
Set OraSession = Nothing
Set OraDatabase = Nothing

End Sub


接続を確認する処理も作っておきます。
OraDatabaseオブジェクトのConnectionOKプロパティ(boolean型)で状態を確認できます。

' 機能 : Oracleの接続を確認する
'
' 返り値 : true - 接続中 , false - 未接続
'
Public Function oracleOpenChk() As Boolean
On Error GoTo errEnd:

'接続確認
If OraDatabase.ConnectionOK Then
oracleOpenChk = True
Else
oracleOpenChk = False
End If

normalEnd:
Exit Function
errEnd:
Err.Clear
oracleOpenChk = False
End Function


使ってみた

作った関数を使用して、Oracleに接続する処理を作ってみました。
接続状態を確認して、接続されている場合は切断、指定された条件で再度接続する処理です。

Sub db接続()

'Oracleへの接続を確認
If oracleOpenChk Then
'Oracleから切断します
Call oracleClose
End If

'ORACLEインスタンスを設定します。
ORA_USERNAME = "username" 'ユーザ名
ORA_PASSWD = "password" 'パスワード
ORA_DBSTRING = "database" '接続先

'Oracleに接続します。
If oracleOpen Then
MsgBox "Oracleに接続しました。"
End If

End Sub


接続に失敗すると、こんなメッセージが出てきます。
Oracle接続失敗

補足

ExcelからOracleに接続したい」なんて考えるのは、Excel(VBA)やOracleに精通している人だと思うので、細かいことは書きません。というか、書き出すときりがないんですよね。

え?精通してない?インスタンスって何?
...いや、そういう人はこんなの作っちゃダメよ。


次回:oo4oでExcelから接続してみた(抽出)
投稿記事の一覧:目次

--- blog end ---

スポンサードリンク

PageTop

Excelで動的に書式を変更してみた

条件付書式設定

「オプションボタンの選択状態に合わせて書式を変更したい」と聞かれました。
条件付書式設定を使えばできるかな?

条件付書式設定は、特定の条件を満たした時だけ書式(罫線、文字色、背景色、フォントなど)を設定したい時に使用します。
かなり凝ったことこったことが出来ますが、あまり多用すると何が何やら分らなくなるので注意が必要ですけどね。

とベラベラ喋ったら「使い方が分らん」というので、ちと実演してみます。

目標

目標は、こんな状態。
「飲む」を選択している場合のみ、飲酒量の文字と枠を表示するようにします。
01.オプション選択による書式制御

事前準備

とりあえず、オプションボタンを右クリックしてメニューから「コントロールの書式設定」を選択します。
02.コントロールの書式設定

コントロールタブを選択して、リンクするセルに適当なセル参照を設定します。
03.リンクするセルの設定

これで、指定したセルにはオプションボタンの選択状態によって異なる値が表示されます。
「飲む」の時は1、「飲まない」の時は2ですね。
04.リンクするセルの値

まず、飲酒量の罫線を消しておきます。
“条件を満たした時だけ罫線を引きたい”ので、通常の状態では罫線は消しておく必要があります。
05.書式を消しておきます

条件付書式設定

まず、書式を設定したセルを選択します(ここでは飲酒量の罫線を引きたいセル)。
条件付書式設定は「ホーム」タブの「スタイル」グループから「ルールの管理」を選択して下さい。
06.条件付書式設定

※ 余談
  他からも選択できるんですけど、なんか設定した通りに反応しないことがあるんですよね。
  結局、一番安定したのが上記の方法でした。
  私の勘違い(操作ミス?)かもしれないですけどね。

表示された画面の左上の「新規ルール」を選択します。
07.新規ルールの設定

ルールの設定画面が表示されるので、上部のリストの最下段「数式を使用して、書式設定するセルを決定」を選択します。
罫線を設定したい条件は、オプションボタンのリンクセルとしたセル「A4」の値が1 の時なので、その通りに条件を設定して「書式」ボタンをクリックします。
08.ルールの設定

おなじみの書式設定画面が表示されるので、条件を満たした時の書式(ここでは罫線)を設定します。
09.罫線の設定

後は「OK」で全ての画面を閉じていきます。
これで、オプションボタン「飲む」を選択した時だけ罫線が表示されるようになります。

12.文字フォントの白

フォント色を変える

枠(というか罫線)はこれでOKですが、"飲酒量"の文字が見えたままなのは気持ち悪いです。
なので、オプションボタン「飲む」が選択された時だけ、"飲酒量"の文字を表示するようにします。

文字"飲酒量"のセル「H4」を選択して、条件付き書式設定でルールを追加します。
セル文字の表示/非表示は出来ないようなので、「飲まない」を選択した時には、文字色が白(背景色)になるようにルールを設定します。
12.文字フォントの白

ちなみに、オプションボタンのリンクセル「A4」の文字色も白にして見えないようにしておきます。
こちらは、通常の書式設定を使用しました。
13.飲む選択時

結果発表

オプションボタン「飲む」を選択した状態です。
13.飲む選択時

オプションボタン「飲まない」を選択した状態です。
14.飲まない選択時

うん、思った通りに表示できましたね。
セル結合は残っちゃってますけど、これくらいは問題ないでしょう。

しかし...

このように実演して見せたんですけど、

  私「どうです、分りましたか?」、ユーザー「分らん、やってよ」。

...ぐぉおおおぉおぉぉぉぉおぉ!!!

結局、私が作業することになりました。
まぁ、いいんですけどね。

ん?セルの値も変えたい?
いや、普通にif関数とか使ってよ。
スポンサードリンク

PageTop

Excelのオブジェクトを非表示にしてみた

グループボックスを非表示にしたい

前回(http://harikofu.blog.fc2.com/blog-entry-61.html)オプションボタンのグループ化の方法を連絡したら「いや、この枠はいらない」とのお言葉を頂きました。

...まぁ、気持ちは分る。

まずは...

まず、「ページレイアウト」タグから、配置グループ内の「オブジェクトの選択と表示」をクリックします。
01.ページレイアウト(オブジェクトの表示)

すると、画面のどこかにオブジェクトの一覧が表示される枠が出てきます。
ちなみに、私の環境では右端に表示されました。
02.オブジェクトの選択と表示

使い方

このリストは、シート上のオブジェクト(図形)に対応しているので、リストを選択するとシートのオブジェクトが選択された状態になります。
03.オブジェクトの選択

図形の選択って、手こずる時があるので便利ですね。
ちなみに、リストからオブジェクトの名称も変更できます。
グループ化とかするなら、分りやすい名前を付けた方が後々便利でしょうね。

消して見た

リストの右にある気持ち悪い目玉マークが、表示、非表示の選択になります。
04.オブジェクトの非表示

シート上で非表示になるので、移動や選択も出来なくなります。
操作をしたい場合は、目玉を表示して下さい。

...しかし、目玉ってなんだろ?
これで分りやすくしたつもりなんだろうか?
分りにくい上に、気持ち悪いと思うんだけどなぁ。


前回:Excelでオプションボタンを使ってみた
投稿記事の一覧:目次

--- blog end ---

スポンサードリンク

PageTop

Excelのオプションボタンを使ってみた

オプションボタン

「オプションボタンを作ったけど、どれか一つを選択すると全部選択されないようになる、それぞれ分けたいんだけど」って問合せを受けました。
まぁ、ようするにグループ化したいってことなんですよね。

そもそも、自分でオプションボタンを作る時ってActiveXコントロールのオプションボタンを使うので、フォームコントロールのオプションボタンの使い方が分らん(--;)

という訳で、使い物になるようにいろいろ実験しました。

追加してみる

まず、開発タブからコントロールの挿入を選択します。
その中から、オプションボタンを選択します。
01.オプションボタンの追加

とりあえず、4つ追加しました。
この場合、どれか一つしか選択状態に出来ないんですよね。
02.オプションボタンの配置

グループボックスを追加してみる

どうやら、グループ化をしたい場合は、フォームコントロールのグループボックスとやらを使うようです。
03.グループボックスの追加1

グループを2つに分けたいので、とりあえず2つグループを作りました。
03.グループボックスの追加2

オプションボタンを、それぞれのグループボックス内に移動します。
03.グループボックスの追加3

しかし状況変わらずで、一つしか選択できない。
ところが悩みながら試していると、突然グループ化されるようになった。
なぜだ?

実験してみた

いろいろ実験したのですが、グループボックス内に存在するようにしてから、Excel側に何らかのアクションを起こしてやることで、グループ化の再設定がされるようです。
例えば、「新しいオプションボタンを追加する」とか「Excelを一度終了して、再度起動する」とかですね。
ちなみに私の環境だと、Excel再起動が一番安定しました。

...たぶん、もっといい方法があると思うんですけどねぇ。

んで、最終的にはこんな感じにグループ化に成功しました。
04.複数のオプション


次回:Excelでオブジェクト(図形)を非表示にしてみた
投稿記事の一覧:目次

--- blog end ---

スポンサードリンク

PageTop

NetCOBOLのエラー事例

NetCOBOLを使っていて発生したエラーの内容と、対応事例集です。

実行環境はUNIX(Solaris)で、Cシェルで実行しています。
開発はWindowsXP SP3で行ってます。

区域外書き出し

【エラーメッセージ】

cobol-rts: HALT: JMP0325I-U [PID:00001E4B TID:00000001] 'OT01FIL'ファイルに対す
る'WRITE'文の実行で,区域外書き出しが発生しました. PGM=TEST110
異常終了 (コアダンプしました)


【原因】
ディスク容量の空きが無い。

【対応】
df -hk コマンドでディスク容量をチェックして、不要ファイルを削除する。

xxxxxxファイルが割り当てられていません.

【エラーメッセージ】

cobol-rts: HALT: JMP0311I-U [PID:0000519B TID:00000001] 'OUT1FILE'ファイルが割り
当てられていません. PGM=TEST050
異常終了


【原因】
プログラム側で使用するファイルがシェル側で宣言されていない、又は間違えている。

【対応】
シェルとPGで入出力ファイルの定義の同期を取ること

シェルが実行できない.

【エラーメッセージ】

: ファイルもディレクトリもありません。


【原因】
事例では、シェルをバイナリモードでUNIXに転送した時に発生しました。
シェルはWindowsで作成しているので、アスキーモードで転送しないとファイルが壊れます。
というか、改行コードが正しく変換されません。
んで、改行コードが壊れたシェルは、正しく実行できないようです。

【対応】
アスキーモードで再度転送する

NUMVAL関数でエラーとなる.

【エラーメッセージ】

cobol-rts: ERROR: JMP0904I-E [PID:000072BC TID:00000001] 関数'NUMVAL'の引数の値に誤りがあります. PGM=TEST001
cobol-rts: ERROR: JMP0904I-E^C


【原因】
数値変換が出来ない値が設定されたため(空白もエラーとなる)。

【対応】
事前に数値チェックをする。
空白が原因なら、実行前に SPACE → "0" のような変換を行うこと。
  
COBOLの処理の順序が正しくない

想定通りの順序で処理が実行されない。

【現象】
ソースはこんな感じ。

***************************************************************
* IN01-READ-RTN
* 入力ファイル読込み
***************************************************************
IN01-READ-RTN SECTION.
*
READ IN01-FIL
AT END
MOVE CS-IN-END TO FLG-IN1-END
NOT AT END
COMPUTE IN01-COUNT = IN01-COUNT + 1
END-READ.
*
IN01-READ-RTN-EXIT.
EXIT.
*
***************************************************************
* FORMAT-SET-REC
* CSVの分解
***************************************************************
FORMAT-SET-RTN.
*


PERFORM文で「IN01-READ-RTN」を実行すると、呼出元に戻らずに「FORMAT-SET-RTN」が実行されました。

【原因】
PERFORM文の飛び先、「 **** SECTION. 」の記述から、「 SECTION 」が抜けていた。
FORMAT-SET-RTN に SECTIONの記述が無いために、IN01-READ-RTN の EXIT. が認識されずに、そのまま処理が流れてしまった。

【対応】
FORMAT-SET-RTN SECTION. に変更。
  
ORA-01413: パック10進数のバッファの値が無効です。

初期化されていない数値型の変数を設定してINSERTを実行した時に発生。
状況から見て、おそらくUPDATEでも発生する。
この件の詳細は、NetCOBOLのサブルーチン修正でORA-01413発生を参照。

【原因】
発生事例では、サブルーチンの修正のため引数のCOPY句に数値項目を追加。
起動側では、項目の初期化をせずにサブルーチンを起動。

【対応】
下記の何れかで対応可能。
1. 起動側で引数全体の初期化処理を実行する。
2. サブルーチン側で数値項目のゼロ処理を追加。

ORA-01488: 入力データ中のニブルまたはバイトが無効です。

SELECT文の実行時に発生。

【原因】
抽出条件として設定していたホスト変数に、値を設定していなかった。

【対応】
SQL文の発行前に、ホスト変数に検索条件を設定する。

***ファイルに対する WRITE 文の実行で,ファイルの最大サイズを超えました.または,ディスクの容量が不足しました.


【原因】
NetCOBOLのファイル制限は1GBなので、出力ファイルの容量がそれ以上になると異常終了します。

【対応】
シェルで、ファイル名を環境変数に設定する時にBSAMを指定します。
指定方法は、ファイル名に続き“,BSAM”です。

setenv OUTFILE1 /var/tmp/outfile.txt,BSAM


ちなみに、本来この指定はファイルの高速処理のための指定なのですが、ファイルサイズの上限を拡張する働きもしてくれるようです。

参考URL:http://software.fujitsu.com/jp/cobol/technical/tech_answer/function0007.html

セグメント例外 (コアダンプしました)

【原因】
使用しているサブルーチンのCOPY句に項目を追加、サブルーチンをリコンパイル。とした後で、メインプログラムをリコンパイルしなかったことが原因。

メインプログラム側では、追加した項目は使用していないので行けるかと思ったんですが、どうやらだめなようです。

【対応】
修正したサブルーチンとCOPY句を使用している全てのプログラムをリコンパイルしました。

前回:NetCOBOLでロードモジュールからSQLを取り出す
投稿記事の一覧 : http://harikofu.blog.fc2.com/blog-entry-22.html

--- blog end ---

スポンサードリンク

PageTop

NetCOBOLでロードモジュールからSQLを取り出す

状況

過去データを一括で削除するってプログラムがあって、元々は5年経過したデータを削除していたのですが、ハードに余裕が出来たので、期間を5年から10年に変更しました。

んで、テスト環境には4年分のデータを突っ込んで実行したところ、なぜかログに130件の削除件数が表示されました。
釈然とせず、コンパイル前のロードモジュールをテスト環境に戻して再テストしたところ、今度は削除件数は0件。

プログラムを見直しても、別段間違えてはいない。
ここから悪夢は始まりました(--;)

ソース紛失

いろいろと資料を漁って調べた結果、数年前に消してはならないデータを消してしまうバグがあったらしく、このプログラムを修正していたらしいです。
障害の報告書と、テスト環境で消えた130件の条件を付き合せたところ、ピッタリと条件に該当。
ちなみに、修正前のソースコードのタイムスタンプは、この障害より以前の日付。

どうやら、障害時に修正したソースをSubversionに登録しなかったようです。
しかも、仕様書も修正されていなかったため、当時にどのような修正をしたのかも不明。

マジ、勘弁してくれ(-_-)/~~~ピシー!ピシー!

ファイルの探索

どっかにソースがあると信じて、サーバーを検索してみました。
cdコマンドでルートディレクトリに移動して、findコマンドを実行します。


find . -name "プログラム名" -print


全てのサーバ(Windows系も含む)を調べたのですが、見つからない、又は修正されていないソースでした。

逆コンパイル

まず考えたのが、ロードモジュールからソースコードを復元できないかってことです。
つまり逆コンパイルですね。

javaとかの言語なら、結構ツールが転がっているので、COBOL用もないかと期待したのですが、見つけ出すことが出来ませんでした。
...需要はありそうだし、私が探せなかっただけで、存在しないってわけじゃないと思うんですけどね。

SQLトレース

障害の原因から考えて、修正した箇所はSQLの抽出条件と考えられました。
そこで、OracleのSQLトレースを使用してSQLを抜き出してやることを思いつきました。

まず、オラクルの設定をいじってSQLトレースを有効にします。
次に、本番環境のロードモジュールを、開発環境にコピーします。
問題のプログラムのみを実行するシェルを作成して、実行します。
トレースファイルから、実行されたSQLを抜き出して、現在のソース中のSQLと比較して差異を見つけ出して、ソースを修正します。

結果、修正後のロードにはWHERE句が1行追加されていることが分りました。
それをヒントに、こんな感じでソース修正してみます。


AND ABCD.TEST < '0000000000'


ただ、SQLトレースの結果にはホスト変数の値が出力されないようなので、完全な再現ができないのが気になります。
つまり、'0000000000' の部分が本当に正しいのかが分らない。

ロードモジュールのバイナリ比較

完全復旧を求めて知恵を絞ったのですが、結局ロードモジュールをバイナリレベルで比較してみることにしました。
まず、修正したソースをコンパイルして、ロードモジュールを作成します。
次に作成したロードモジュールと、本番環境で使用しているロードモジュールをバイナリレベルで比較します。

※ 比較には、フリーソフト「Stirling(スターリング)」を使用しました。

差異がある箇所を調べていて気付いたのですが、なんかロードモジュール内にSQLがそのまま表示されてるんですよね。
もしやと思って、ロードモジュールをテキストエディタで開いたところ、SQLを見ること出来ました。
...最初にこれを知っていれば、わざわざSQLトレースを仕込むこともなかったんだが(--;)
んで、あらためて差異を調べてソースを再修正。


AND ABCD.TEST < '9900000000'


'0000000000' 部分は '9900000000'が正しかったようです。

これを元に、再度バイナリエディタで比較して差異を確認します。
ロードモジュールにはコンパイルした日時も設定されるようで、完全には一致しませんでした。
まぁ、明らかに日付だったので、そこは無視したのですが、あと一か所だけどうしても一致しない(--;)

いろいろ試したのですが、どうやら日付以外の情報でもコンパイルするたびに書き換わる項目があるようです。
ためしにソースをいじらずにコンパイルして、ロードを比較してもその場所で差異が発生する。

なので、これで間違いないと想定して修復終了です。
はぁ、長かった...

前回:NetCOBOLのサブルーチン修正でORA-01413発生
次回:NetCOBOLのエラー事例
投稿記事の一覧 : http://harikofu.blog.fc2.com/blog-entry-22.html

--- blog end ---


スポンサードリンク

PageTop