SQL Server 2005 ビューにアスタリスク 「 * 」で失敗

最近は本当に失敗ばかり。

気分もかなり落ち気味です。

本日もやらかしました。


使用しているアプリはAccessで開発。

レポート機能も使用しています。

使用するレポートに対して複数テーブルを参照するためあらかじめビューを作成し、

レコードソースで使用することに。

すでに使用しているレポートが突然デザインがくずれた。

Accessファイルの最適化、Windowsの再起動しても回復せず。

途方にくれていると何とか原因発覚。


使用しているビューで「*」を使用していた。

ビューを作成後に「*」を使用している対象テーブルに別機能の実装のため項目追加。

するとビューをSELECTしたときの結果が列と値がずれてしまう。

再度ALTERでビューを更新すると無事回復しました。


そもそもビューを作成しているのにも関わらず「*」を使うなということか。

というかそれが基本なのかもしれませんが。

ビュー作るときにめんどくさがった自分の怠慢が今頃かえってきたしだいです。


また一つ勉強になりました。

日々精進。

数値型のデータ変換 - SQL Server 2005

細かいですが、気づきがあったのでメモ。

DBはSQL Server 2005。

ちなみに開発環境はVS2005。

言語はVB.NET。


DBデザインを考えるときに数値型のカラムは今までNumeric型を使用していた。

特に意識して数値型を使い分けている訳ではなかったが、

とりあえず桁数、小数点をそれぞれ任意で指定できるし、桁が大きくなっても問題ない。

特に大規模なシステムなわけではないので、大は小をかねる、発想でこれを使っていた。


今回開発に携わったDBは数値型にINT型を使っている。

そこに今回落とし穴。


例えばNumeric型のカラムに’’（カラ）を挿入しようとすると型変換エラーで落ちちゃいます。

なのでアプリ側の制御として’’はNULLに変換してSQLは流さないといけない。

そこは毎回チェックするところというか、そんなことやるのは当たり前なので。


ところが今回見落としてしまいました。

しかもエラーが出る訳ではない。数値型に’’（カラ）を挿入しているにも関わらず。

最初はデフォルト値を設定しているのかと。

カラを挿入するとデフォルト値を使うようになっているのかと。

まあでもそんなわけでもなく。

確認するとINT型だから？

DecimalとNumericはエラーでこけます。

ところがINT型だと’’(カラ) は 0 として登録しまう。

しかも’’にいくら半角スペースが入っていてもTrimされるのか、問題なし。

とりあえずカラをCASTしてみると下記が0に変換しちゃいます。
bigint、int、smallint、tinyint、float、real、money


これってSQL Server 使う上で常識？

気づかないのはデータ型の本質を理解していないからのような気もするが。

まあとりあえずいまさらですが勉強になりました。

分からないことが多すぎて。

日々精進。

ストアドプロシージャ ループ処理 SQL Server 2005

今回初めてSQLServerのストアド使ってバッチを作成。

バッチであればいいかとDB側で簡潔するのでいいかとは思うが。

クライアントのプログラム側と連携するとソースが追えなくて処理の内容がわかりにくくなりそうな。

まあでも少しは使い方がわかったのでとりあえず忘れないように。

情報はたくさんありました。

内容はテーブルAの情報をテーブルBに対して一括で登録するという簡単な要件。

SELECT INSERTで書いちゃえば一発で終わるんじゃないかと思ったんですが諸事情によりストアドを利用。

INSERT部分が別のストアドで作られており、関連テーブルの更新等を行っていたので、

SELECTしたレコードからパラメーター渡して処理を実行してあげる必要があったんです。

なのでSELECT情報をループさせてレコードごとにINSERT処理を行うストアドを実行。

下記ストアド記述
--------------------------------------------------


BEGIN


DECLARE csr CURSOR FOR 
SELECT
  id
 ,A_col1
 ,A_col2
 ,A_col3
FROM
 TABLE_A


DECLARE @prm1 NUMERIC
DECLARE @prm2 VARCHAR(50)
DECLARE @prm3 VARCHAR(50)
DECLARE @prm4 VARCHAR(50)


OPEN csr


FETCH NEXT FROM csr
INTO @prm1, @prm2, @prm3, @prm4


WHILE @@FETCH_STATUS = 0
BEGIN
  
  EXEC sp_INSERT_TABLE_B @prm1, @prm2, @prm3, @prm4 ← INSERT処理を行う別ストアドを実行
  
  FETCH NEXT FROM csr
  INTO @prm1, @prm2, @prm3, @prm4
END


CLOSE csr
DEALLOCATE csr


--------------------------------------------------


まあでもストアドの書き方がわからずそれなりに四苦八苦しちゃいましたど。

カーソルの使い方もよくわからず。

いつものことですが終わってしまうと、とても簡単なことだったと気づくわけで。

わからないことが多すぎて。

日々精進。

DBに画像データをバイナリで登録 SQLServer + VB.NET

こういった処理は今までやったことはなく。

検索してもあまり理解できず。

またズレたとこでつまずいて大いに時間をかけてしまいました。


環境
SQL Server 2005
Visual Studio 2005
VB.NET


VB.NETで簡単なユーザーを管理する画面を作成中。

単純なマスタメンテ画面です。

そこに顔写真を登録したいとのことで。

最初は画像ファイルを一括して管理する所定のフォルダを作成しそこで管理しようかと。

ただそれも面倒なのでいっそのことDBへ登録してしまえということになり、調査開始。


まずはDB側の設定。

画像登録用のカラム追加。
カラム名：picture 型：varbinary(max)


画面側の処理を追加

まずは選択した画像ファイルをバイナリに変換する。
-----------------------------------------------------------------

    Private Function f_GetPhoto(ByVal filePath As String) As Byte()
            Dim stream As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)
            Dim reader As BinaryReader = New BinaryReader(stream)
            Dim photo() As Byte = reader.ReadBytes(CType(stream.Length, Integer))


            reader.Close()
            stream.Close()


            Return photo
    End Function
-----------------------------------------------------------------


次に画像から変換したバイナリデータをDBへ登録。
-----------------------------------------------------------------
Private Sub s_AddPicture(ByVal PicBinary As Byte())
'※PicBinary ←画像データを変換し取得したバイナリデータ



追加の場合
Dim strSQL As String = ""
strSQL = ""
strSQL &= vbCrLf & " INSERT INTO Staff"
strSQL &= vbCrLf & "("
strSQL &= vbCrLf & " picture"
strSQL &= vbCrLf & ")"
strSQL &= vbCrLf & " VALUES "
strSQL &= vbCrLf & "("
strSQL &= vbCrLf & " @pic"
strSQL &= vbCrLf & ")"


更新の場合
Dim strSQL As String = ""
strSQL = ""
strSQL &= vbCrLf & " UPDATE Staff SET"
strSQL &= vbCrLf & " picture = @pic"
strSQL &= vbCrLf & " WHERE "
strSQL &= vbCrLf & " staff_id = '" & Me.txtStaff_Id.Text & "'"


登録処理
Using con As New SqlClient.SqlConnection(ConnectionString)
   Dim cmd As New SqlClient.SqlCommand(strSQL, con)
   cmd.Parameters.Add("@pic", SqlDbType.Binary, PicBinary.Length).Value = PicBinary
   con.Open()
   cmd.ExecuteNonQuery()
End Using


End Sub
-----------------------------------------------------------------

登録後、データを呼び出した場合は画面のPictureBoxに画像データを表示します。
-----------------------------------------------------------------

Private Sub s_SetPicFromDB(ByVal getbyte As Byte())
※getbyte←DBから取得したバイナリデータ
    If IsNothing(getbyte) = False Then
        Dim imgconv As New ImageConverter()
        Dim img As Image = CType(imgconv.ConvertFrom(getbyte), Image)
        '画像の大きさをPictureBoxに合わせる
        Me.PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
        '画像を表示する
        Me.PictureBox1.Image = img
    Else
        Me.PictureBox1.Image = Nothing
    End If


End Sub
-----------------------------------------------------------------



自分じゃなかったらすぐ終わっちゃいそうでした。

費やした時間を戻して欲しい。

わからないことが多すぎて。

日々精進。

超図解 VB.NETルールブック (超図解シリーズ)

SQL Server 2005 トランザクションログ 復元

トランザクションログの復元って何気にやったことなかった。

フルバックのときと一緒かと思ったら違うんですね。

GUIからのやり方が未だにわからず。。。

Management Studioのエディション？バージョン？によって違うのかな？

すぐにヒット。
SQL Server 2005 Books Onlineトランザクション ログ バックアップの適用

とりあえずTransact－SQLにて実行。

ポイントは WITH NORECOVERY を指定することみたいですね。

全てのログをあて終わったら最後に下記をお忘れなく。

RESTORE DATABASE [DB名] WITH RECOVERY；


スクリプトの生成はGUIからやるのが便利。

該当するDBをオブジェクトエクスプローラーから選択し、右クリック。

↓

タスク

↓

復元

↓

開いたウィザード画面の上部「スクリプト」をクリックし、お好きな形でスクリプト保存。

あとはDB名なり必要箇所を編集して実行。

またこんな簡単なことで躓きました。

知らないことが多すぎて。

日々精進。