SQLの窓

2014年12月21日


VB.net : GetSchema メソッドでデータベース内のテーブル一覧と指定したテーブルの列名一覧を取得する

▼ これは、ODBC ドライバを列挙するスクリプトです


この仕様は元々 ADO のものです。Framework のドキュメントからはとても解りづらくなっていますが、古いドキュメントで容易に確認できます。

SchemaEnum

C# のコードはこちら

VB.net から C# へのオンライン変換ツールを久しぶりに使ってみましたが、後から手作業で変更したのは、ControlChars.CrLf を "\r\n" にしただけでした。
Imports System.Data
Imports System.Data.Odbc

Module Module1

	Sub Main()

		' 新しい OdbcConnectionStringBuilder オブジェクトを作成
		Dim builder As New OdbcConnectionStringBuilder()

		' ドライバ文字列をセット ( 波型括弧{} は必要ありません ) 
		' 文字列を正確に取得するには、レジストリ : HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI
		builder.Driver = "MySQL ODBC 5.3 Unicode Driver"

		' 接続用のパラメータを追加
		builder.Add("SERVER", "localhost")
		builder.Add("DATABASE", "lightbox")
		builder.Add("UID", "root")
		builder.Add("PWD", "password")

		' 内容を確認
		Console.WriteLine(builder.ConnectionString)

		' 新しい OdbcConnection オブジェクトを作成
		Dim myCon As New OdbcConnection()

		' 接続文字列を設定
		myCon.ConnectionString = builder.ConnectionString

		' 接続を開く
		Try
			myCon.Open()
		Catch ex As OdbcException
			Console.WriteLine("接続エラーです")
			' Console.WriteLine( ex.Message )
			Call ErrorAction( ex )
			Exit Sub
		End Try

		' データベースのテーブル一覧のメタデータを取得
		' TABLE_CATALOG
		' TABLE_SCHEMA
		' TABLE_NAME
		' TABLE_TYPE
		Dim dataTable As DataTable = myCon.GetSchema(OdbcMetaDataCollectionNames.Tables)

		' 一覧表示
		Dim row As DataRow
		For Each row In  dataTable.Rows
			Dim column As DataColumn = dataTable.Columns.item("TABLE_NAME")
			Console.WriteLine( row.item(column) )
		Next

		' データベースの列名一覧のメタデータを取得
		' TABLE_CATALOG
		' TABLE_SCHEMA
		' TABLE_NAME
		' COLUMN_NAME
		' 配列で、対象データを絞る
		Dim restrictionValues As String() = {Nothing,Nothing,"社員マスタ"}

		' 社員マスタの列のメタデータ
		dataTable = myCon.GetSchema(OdbcMetaDataCollectionNames.Columns,restrictionValues)

		' 列名の一覧を表示
		For Each row In  dataTable.Rows
			Dim column As DataColumn = dataTable.Columns.item("COLUMN_NAME")
			Console.WriteLine(row.item(column))
		Next

		' 接続を閉じる
		myCon.Close()

		' OdbcConnection オブジェクトに使用されているすべてのリソースを解放
		myCon.Dispose()

		' 処理終了
		Console.WriteLine("処理が終了しました")

		' 一時停止
		Console.Write("Enterキーを押して下さい : ")
		Console.ReadLine()

	End Sub

	' **********************
	' エラー処理
	' **********************
	Sub ErrorAction( ex As OdbcException )

		Dim CrLf As String = ControlChars.CrLf
		Dim errorMessages As String = ""
		Dim i As Integer

		For i = 0 To ex.Errors.Count - 1
			errorMessages &= _
				"Index #" & i.ToString() & CrLf _
				& "Message: " & ex.Errors(i).Message & CrLf _
				& "NativeError: " & ex.Errors(i).NativeError.ToString() & CrLf _
				& "Source: " & ex.Errors(i).Source & CrLf _
				& "SQL: " & ex.Errors(i).SQLState & CrLf
		Next i

		Console.WriteLine(errorMessages)

	End Sub

End Module

関連する記事

ODBC ドライバの列挙 / VBScript

WMIのレジストリアクセスで、レジストリエントリの一覧を取得する VBScript クラス


タグ:ODBC
posted by lightbox at 2014-12-21 20:52 | VB.NET : データベース | このブログの読者になる | 更新情報をチェックする

2011年10月30日


VB.net : ListView に DB から読み込んだデータをセットする



NAVER まとめで手順書

表形式でデータを表示する最も簡単な方法です。



以下のようなプライベートクラスを使うのがミソです
' ********************************************************
' ListView 用のプライベートクラス
' ********************************************************
Private Class RowData
	Inherits System.Windows.Forms.ListViewItem

	Public Sub New(ByVal myReader As OdbcDataReader)

		Me.Text = Form1.GetValue(myReader, "社員コード")
		Me.SubItems.Add(Form1.GetValue(myReader, "氏名"))

	End Sub
End Class

このプライベートクラスを使って以下のように呼び出します。



※ ListView 側は、デザイナから列を作成しておいて下さい。




posted by lightbox at 2011-10-30 18:03 | VB.NET : データベース | このブログの読者になる | 更新情報をチェックする

2011年10月23日


VB.net : 一時 PL/SQL で Data Pump Exportユーティリティと同じデータをエクスポートして処理結果を取得する

PL/SQL のユーティリティを使用すると、VB.net を通してパラメータを
カスタマイズ可能なエクスポートアプリケーションを作成できます。

結果を知る為のパラメータは ParameterDirection.Output で出力のみで
取得しています。正常終了すると、"COMPLETED" という文字列が返ります。
( 既にファイルがある場合は、"例外発生" が返ります。)

各プロシージャの詳細は Oracle ドキュメント : DBMS_DATAPUMP
DECLARE 
	J_HANDLE NUMBER;
/**********************************************************/
/* 処理開始 */
/**********************************************************/
BEGIN
	/***************************/
	/* OPEN */
	/***************************/
	J_HANDLE := DBMS_DATAPUMP.OPEN(
		'EXPORT'
		,'TABLE'
		,NULL
		,NULL
		,'COMPATIBLE'
		,DBMS_DATAPUMP.KU$_COMPRESS_METADATA
	);

	/***************************/
	/* 出力ファイル */
	/***************************/
	DBMS_DATAPUMP.ADD_FILE(
		J_HANDLE
		,'MY_TABLE_BACKUP.DMP'
		,'MYDIR'
		,NULL
		,DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
	);

	/***************************/
	/* SQL 式で スキーマを指定 */
	/***************************/
	DBMS_DATAPUMP.METADATA_FILTER(
		J_HANDLE
		,'SCHEMA_EXPR'
		,'IN (''LIGHTBOX'')'
	);

	/***************************/
	/* SQL 式で 表を指定 */
	/***************************/
	DBMS_DATAPUMP.METADATA_FILTER(
		J_HANDLE
		,'NAME_EXPR'
		,'IN (''社員マスタ'',''商品マスタ'')'
	);

	/***************************/
	/* データを出力しない */
	/***************************/
	DBMS_DATAPUMP.DATA_FILTER (
		J_HANDLE
		,'INCLUDE_ROWS'
		,0
	);

	/***************************/
	/* 開始 */
	/***************************/
	DBMS_DATAPUMP.START_JOB(
		J_HANDLE
		,0
	);

	/***************************/
	/* JOB 終了まで待機 */
	/***************************/
	DBMS_DATAPUMP.WAIT_FOR_JOB(
		J_HANDLE
		,:J_STS
	);

	/***************************/
	/* 終了ステータスの表示 */
	/***************************/
	DBMS_OUTPUT.PUT_LINE('処理終了:'||:J_STS);

/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/
EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
		:J_STS := '例外発生';
END;

' キャラクタセット
Dim SJIS_Enc As Encoding = Encoding.GetEncoding(932) 
' 読み込み用
' plsql.sql は、改行コードを LF のみにして下さい
Dim ReadFile As StreamReader = New StreamReader( "plsql.sql", SJIS_Enc )

' SHIFT_JIS で読み込み
Dim SqlText As String = ReadFile.ReadToEnd()
ReadFile.Close() 

Dim myCommand As New OracleCommand()
myCommand.Connection = myCon

Dim myParam1 As OracleParameter
' *******************************************
' プロシージャの実行
' *******************************************
' 実行タイプ( SQLとして実行 )
myCommand.CommandType = CommandType.Text
' プロシージャ名
myCommand.CommandText = SqlText

' パラメータクリア
myCommand.Parameters.Clear()
' 内容を取得する変数用パラメータ( OUT なので、Direction プロパティにセット )
myParam1 = myCommand.Parameters.Add("J_STS", OracleType.Varchar)
myParam1.Direction = ParameterDirection.Output 
myParam1.Size = 50		' 長さ

Try
	' 結果を受け取り、後で処理する
	myCommand.ExecuteNonQuery()
Catch ex As Exception
	myCon.Close()
	Console.WriteLine(ex.Message)
	Return
End Try

Console.WriteLine(myParam1.Value)

関連する記事

VB.net : 一時PL/SQL 内の変数をプログラムでやり取りする



posted by lightbox at 2011-10-23 20:21 | VB.NET : データベース | このブログの読者になる | 更新情報をチェックする

VB.net : 一時PL/SQL 内の変数をプログラムでやり取りする

一番重要なのは、PL/SQL の改行コードに CRLF の CR を含めない事です。

:変数名 でバインドさせると、自動的に PL/SQL 側で変数として定義されます

ここでは、テストの為に ParameterDirection.InputOutput を使っていますが、
入力データはもともと PL/SQL がテキストなので、文字列を置き換えて実行する
方法もあります。

関連する記事

http://logicalerror.seesaa.net/article/228467533.html

もっと言えば、PL/SQL 側の動的 PL/SQL を使わないでも比較的簡単に 動的な
SQL の実行が可能です。むしろ、パーツ化してマクロ化してしまえばより便利
になると思います。
DECLARE 

	WK_STRING VARCHAR2(50);

BEGIN 

	WK_STRING := :RET_VALUE;

	DBMS_OUTPUT.PUT_LINE(:RET_VALUE); 

	UPDATE 社員マスタ SET 氏名 = :RET_VALUE
		WHERE 社員コード = '0001';

	DBMS_OUTPUT.PUT_LINE(WK_STRING); 

	:RET_VALUE := '最終値';

END; 

Imports System.Data
Imports System.Data.OracleClient
Imports System.IO
Imports System.Text

Module Module1

	Sub Main()

		' 新しい OracleConnection オブジェクトを作成
		Dim myCon As OracleConnection = CreateConnection()

		if myCon is Nothing then
			Console.WriteLine("処理が異常終了しました")
			Return
		end if

		'---------------------------------------------------
		' キャラクタセット
		Dim SJIS_Enc As Encoding = Encoding.GetEncoding(932) 
		' 読み込み用
		' plsql.sql は、改行コードを LF のみにして下さい
		Dim ReadFile As StreamReader = New StreamReader( "plsql.sql", SJIS_Enc )

		' SHIFT_JIS で読み込み
		Dim SqlText As String = ReadFile.ReadToEnd()
		ReadFile.Close() 

		Dim myCommand As New OracleCommand()
		myCommand.Connection = myCon

		Dim myParam1 As OracleParameter
		' *******************************************
		' プロシージャの実行
		' *******************************************
		' 実行タイプ( SQLとして実行 )
		myCommand.CommandType = CommandType.Text
		' プロシージャ名
		myCommand.CommandText = SqlText

		' パラメータクリア
		myCommand.Parameters.Clear()
		' 内容を取得する変数用パラメータ( OUT なので、Direction プロパティにセット )
		myParam1 = myCommand.Parameters.Add("RET_VALUE", OracleType.Varchar)
		myParam1.Direction = ParameterDirection.InputOutput 
		myParam1.Size = 50		' 長さ
		myParam1.Value = "山田 太郎"

		Try
			' 結果を受け取り、後で処理する
			myCommand.ExecuteNonQuery()
		Catch ex As Exception
			myCon.Close()
			Console.WriteLine(ex.Message)
			Return
		End Try

		Console.WriteLine(myParam1.Value)

		'---------------------------------------------------

		Call EndConnection( myCon )

	End Sub


	' ******************************************************
	' 接続終了
	' ******************************************************
	Sub EndConnection( myCon As OracleConnection )

		' 接続を閉じる
		myCon.Close()

		' OracleConnection オブジェクトに使用されているすべてのリソースを解放
		myCon.Dispose()

		' 処理終了
		Console.WriteLine("処理が終了しました")

		' 一時停止
		Console.Write("Enterキーを押して下さい : ")
		Console.ReadLine()

	End Sub

	' ******************************************************
	' 接続作成
	' ******************************************************
	Function CreateConnection( ) As OracleConnection

		' 新しい OracleConnectionStringBuilder オブジェクトを作成
		Dim builder As New OracleConnectionStringBuilder()

		' 接続用のパラメータを追加
		builder.Add("Server", "light/xe")
		builder.Add("User ID", "LIGHTBOX")
		builder.Add("Password", "LIGHTBOX")

		' 内容を確認
		Console.WriteLine(builder.ConnectionString)

		' 新しい OracleConnection オブジェクトを作成
		Dim myCon As New OracleConnection()

		' 接続文字列を設定
		myCon.ConnectionString = builder.ConnectionString

		' 接続を開く
		Try
			myCon.Open()
		Catch ex As OracleException
			Console.WriteLine("接続エラーです")
			Console.WriteLine( ex.Message )
			Return Nothing
		End Try

		Return myCon

	End Function

End Module



posted by lightbox at 2011-10-23 17:48 | VB.NET : データベース | このブログの読者になる | 更新情報をチェックする

2011年10月14日


VB.net : PL/SQL の OUT パラメータから実行結果を取得する

PL/SQL 側で、以下のように定義されたプロシージャを呼び出します
PROCEDURE PARAM_OUT_TEST 
( 
	PM_STRING IN VARCHAR2, 
	PM_NUMBER OUT NUMBER 
) 
第一引数は、入力のみなので Direction の設定はしていません。第二引数は 入力はしませんが、出力するので Direction の設定をします。 ※ Inpit、Output、InputOutput、ReturnValue があります
Imports System.Data
Imports System.Data.OracleClient

Module Module1

	Sub Main()

		' 新しい OracleConnection オブジェクトを作成
		Dim myCon As OracleConnection = CreateConnection()

		if myCon is Nothing then
			Console.WriteLine("処理が異常終了しました")
			Return
		end if

		'---------------------------------------------------

		Dim myCommand As New OracleCommand()
		myCommand.Connection = myCon

		Dim myParam1 As OracleParameter
		Dim myParam2 As OracleParameter
		' *******************************************
		' プロシージャの実行
		' *******************************************
		' 実行タイプ( ストアードプロシージャとして実行 )
		myCommand.CommandType = CommandType.StoredProcedure
		' プロシージャ名
		myCommand.CommandText = "PARAM_OUT_TEST"

		' パラメータクリア
		myCommand.Parameters.Clear()
		' 1つ目のパラメータ( IN なのでデータセット )
		myParam1 = myCommand.Parameters.Add("PM_STRING", OracleType.VarChar)
		myParam1.Value = "0010"

		' 2つ目のパラメータ( OUT なので、Direction プロパティにセット )
		myParam2 = myCommand.Parameters.Add("PM_NUMBER", OracleType.Number)
		myParam2.Direction = ParameterDirection.Output
	
		Try
			' 結果を受け取り、後で処理する
			myCommand.ExecuteNonQuery()
		Catch ex As Exception
			myCon.Close()
			Console.WriteLine(ex.Message)
			Return
		End Try

		' Decimal で返されている
		Console.WriteLine(myParam2.Value.GetType())

		Dim myValue As Decimal = myParam2.Value
		Console.WriteLine(myValue)

		'---------------------------------------------------

		Call EndConnection( myCon )

	End Sub


	' ******************************************************
	' 接続終了
	' ******************************************************
	Sub EndConnection( myCon As OracleConnection )

		' 接続を閉じる
		myCon.Close()

		' OracleConnection オブジェクトに使用されているすべてのリソースを解放
		myCon.Dispose()

		' 処理終了
		Console.WriteLine("処理が終了しました")

		' 一時停止
		Console.Write("Enterキーを押して下さい : ")
		Console.ReadLine()

	End Sub

	' ******************************************************
	' 接続作成
	' ******************************************************
	Function CreateConnection( ) As OracleConnection

		' 新しい OracleConnectionStringBuilder オブジェクトを作成
		Dim builder As New OracleConnectionStringBuilder()

		' 接続用のパラメータを追加
		builder.Add("Server", "light/xe")
		builder.Add("User ID", "LIGHTBOX")
		builder.Add("Password", "LIGHTBOX")

		' 内容を確認
		Console.WriteLine(builder.ConnectionString)

		' 新しい OracleConnection オブジェクトを作成
		Dim myCon As New OracleConnection()

		' 接続文字列を設定
		myCon.ConnectionString = builder.ConnectionString

		' 接続を開く
		Try
			myCon.Open()
		Catch ex As OracleException
			Console.WriteLine("接続エラーです")
			Console.WriteLine( ex.Message )
			Return Nothing
		End Try

		Return myCon

	End Function

End Module

Function の場合の戻り値の取得は以下のようにして記述できます
この場合、戻り値は最初に定義しますが、変数名は何でもいいです。
' パラメータクリア
myCommand.Parameters.Clear()
' 1つ目のパラメータ( 戻り値として )
myParam1 = myCommand.Parameters.Add("RET_VALUE", OracleType.Number)
myParam1.Direction = ParameterDirection.ReturnValue

' 2つ目のパラメータ( 第一引数として )
myParam2 = myCommand.Parameters.Add("PM_STRING", OracleType.Varchar)
myParam2.Value = "0001"

※ :を使って、SQL文内にバインド変数を埋め込む方法もあります
( CommandType.Text を使います )

コロン( : ) を使った場合の引数の処理 :サーバー側のカーソルを取得して使用する



タグ:PL/SQL
posted by lightbox at 2011-10-14 22:38 | VB.NET : データベース | このブログの読者になる | 更新情報をチェックする

2011年09月18日


VB.net : ODBC + MySQL で CSV をインポート( クラス化サンプル )



VB.net : CSVによるインポート用ファイルの作成( つまりエクスポート ) で作成した
CSV をインポートする処理ですが、データベースの処理等をクラス化しています

MyClass.vb
社員マスタ.sql
社員マスタ.csv
Imports System.IO
Imports System.Text

Module Module1

	Sub Main()

		' データベースとその他の処理をまとめたクラス
		Dim MyObj As MyLib = New MyLib("localhost","lightbox","root","password")

		' オブジェクトを使って接続処理
		if Not MyObj.CreateConnection() then
			Console.WriteLine("処理が異常終了しました")
			Return
		end if

		'---------------------------------------------------

		' insert 構文固定部分
		Dim InsertQueryBase As String = "insert into `社員マスタ` ("
		' 行毎完成 insert 文 を格納する変数
		Dim InsertQuery As String = Nothing

		' SHIFT_JIS で入力する為のテキストファイルの準備
		Dim SJIS_Enc As Encoding = Encoding.GetEncoding(932)
		Dim ReadFile As StreamReader = New StreamReader( MyLib.CsvPath, SJIS_Enc )

		' SHIFT_JIS で読み込む準備
		Dim LineText As String = Nothing
		' ループカウンタ用
		Dim idx As Integer = 0

		' Peek() は、StreamReader オブジェクトの現在位置は変わりません
		' それ以上読み取り可能な文字がない場合、戻り値は -1 です。
		Do While ReadFile.Peek() >= 0

			' 一行を読み込む
			LineText = ReadFile.ReadLine()

			if idx = 0 then
				LineText = LineText.Replace("""","`")
				InsertQueryBase = InsertQueryBase + LineText + ")"
				idx += 1
				Continue Do
			end if

			InsertQuery = InsertQueryBase + " values("
			LineText = LineText.Replace("""""","NULL")
			LineText = LineText.Replace("""","'")
			InsertQuery = InsertQuery + LineText + ")"

'			Console.WriteLine( InsertQuery )
			MyObj.Execute(InsertQuery)

			' 読み込みカウンタアップ
			idx += 1
		Loop

		' リソースの後片づけ
		ReadFile.Close()
		ReadFile.Dispose()

		'---------------------------------------------------

		MyObj.EndConnection()

	End Sub

End Module



posted by lightbox at 2011-09-18 23:16 | VB.NET : データベース | このブログの読者になる | 更新情報をチェックする
Seesaa の各ページの表示について
Seesaa の 記事がたまに全く表示されない場合があります。その場合は、設定> 詳細設定> ブログ設定 で 最新の情報に更新の『実行ボタン』で記事やアーカイブが最新にビルドされます。

Seesaa のページで、アーカイブとタグページは要注意です。タグページはコンテンツが全く無い状態になりますし、アーカイブページも歯抜けページはコンテンツが存在しないのにページが表示されてしまいます。

また、カテゴリページもそういう意味では完全ではありません。『カテゴリID-番号』というフォーマットで表示されるページですが、実際存在するより大きな番号でも表示されてしまいます。

※ インデックスページのみ、実際の記事数を超えたページを指定しても最後のページが表示されるようです

対処としては、このようなヘルプ的な情報を固定でページの最後に表示するようにするといいでしょう。具体的には、メインの記事コンテンツの下に『自由形式』を追加し、アーカイブとカテゴリページでのみ表示するように設定し、コンテンツを用意するといいと思います。


※ エキスパートモードで表示しています

アーカイブとカテゴリページはこのように簡単に設定できますが、タグページは HTML 設定を直接変更して、以下の『タグページでのみ表示される内容』の記述方法で設定する必要があります

<% if:page_name eq 'archive' -%>
アーカイブページでのみ表示される内容
<% /if %>

<% if:page_name eq 'category' -%>
カテゴリページでのみ表示される内容
<% /if %>

<% if:page_name eq 'tag' -%>
タグページでのみ表示される内容
<% /if %>
この記述は、以下の場所で使用します
container 終わり

フリーフォントで簡単ロゴ作成
フリーフォントでボタン素材作成
フリーフォントで吹き出し画像作成
フリーフォントではんこ画像作成
ほぼ自由に利用できるフリーフォント
フリーフォントの書体見本とサンプル
画像を大きく見る為のウインドウを開くボタンの作成

CSS ドロップシャドウの参考デモ
BUTTONS (CSS でボタン)
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり