Imports System.Data.OleDb
Module MyModule
' ********************************************************' 実行' ********************************************************
Sub Main()
Dim myConnectString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=C:\Program Files\WinOfSql\Tool\parts\販売管理.mdb;"
Dim myCon As New OleDbConnection()
myCon.ConnectionString = myConnectString
myCon.Open()
Dim myQuery As String = "SELECT * from 社員マスタ"
Dim myCommand As New OleDbCommand()
myCommand.CommandText = myQuery
myCommand.Connection = myCon
Dim myReader As OleDbDataReader
myReader = myCommand.ExecuteReader()
Dim fld As Integer = 0
Do While myReader.Read()
fld = myReader.GetOrdinal("氏名")
Console.WriteLine(myReader.GetString(fld))
Loop
myReader.Close()
myCon.Close()
End Sub
End Module
Excel : System.Data.OleDb( Framework )
Imports System.Data.OleDb
Module MyModule
' ********************************************************' 実行' ********************************************************
Sub Main()
Dim myConnectString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=C:\Documents and Settings\lightbox\My Documents\社員マスタ.xls;" + _
"Extended Properties=""Excel 8.0;IMEX=1;"""
Dim myCon As New OleDbConnection()
myCon.ConnectionString = myConnectString
myCon.Open()
Dim myQuery As String = "SELECT * from 社員マスタ"
Dim myCommand As New OleDbCommand()
myCommand.CommandText = myQuery
myCommand.Connection = myCon
Dim myReader As OleDbDataReader
myReader = myCommand.ExecuteReader()
Dim fld As Integer = 0
Do While myReader.Read()
fld = myReader.GetOrdinal("氏名")
Console.WriteLine(myReader.GetString(fld))
Loop
myReader.Close()
myCon.Close()
End Sub
End Module
MySQL : System.Data.Odbc( Framework )
MySQL 4.1 以上の場合、データベースのキャラクタセットが何であっても、
クライアントのキャラクタセットを以下のように接続時に指定する事ができるはずです。
( ソースコード例では、myCommand.ExecuteNonQuery() で実行する )
【Shift_JIS の場合】
set names 'sjis'
【EUC-JP の場合】
set names 'ujis'
※ テーブル名に Shift_JIS が使用できるのは、MySQL5.1 以降のようです
' ********************************************************' ■ MySQL データベースアクセス' ********************************************************
Imports System.Data.Odbc
Module MyModule
' ********************************************************' MySQL / System.Data.Odbc' ********************************************************
Sub Main()
Dim myConnectString As String = _
"Driver={MySQL ODBC 5.1 Driver};" + _
"SERVER=localhost;" + _
"DATABASE=lightbox;" + _
"UID=root;" + _
"PWD=password"
Dim myCon As New OdbcConnection()
myCon.ConnectionString = myConnectString
myCon.Open()
Dim myQuery As String = _
"SELECT 社員マスタ.*,DATE_FORMAT(生年月日,'%Y-%m-%d') as 誕生日" _
+ " from 社員マスタ"
Dim myCommand As New OdbcCommand()
myCommand.CommandText = myQuery
myCommand.Connection = myCon
Dim myReader As OdbcDataReader
myReader = myCommand.ExecuteReader()
Do While myReader.Read()
' 文字列
Console.Write(GetValue(myReader,"社員コード") + " : ")
Console.Write(GetValue(myReader,"氏名") + " : ")
' 整数
Console.Write(GetValue(myReader,"給与") + " : ")
' 日付
Console.Write(GetValue(myReader,"作成日") + " : ")
Console.Write(GetValue(myReader,"更新日") + " : ")
Console.Write(GetValue(myReader,"生年月日") + " : ")
Console.Write(GetValue(myReader,"誕生日"))
Console.WriteLine()
Loop
myReader.Close()
myQuery = "update 社員マスタ set 生年月日 = '1982/01/01'" _
+ " where 社員コード = '0002'"
Execute( myCon, myQuery )
myCon.Close()
myReader.Dispose()
myCon.Dispose()
End Sub
' ********************************************************' 列データ取得' ********************************************************
Function GetValue(ByVal odr As OdbcDataReader, _
ByVal strName As String) As String
Dim ret As String = ""
Dim fld As Integer = 0
fld = odr.GetOrdinal(strName)
If odr.IsDBNull(fld) Then
ret = ""
Else
ret = odr.GetValue(fld).ToString()
End If
Return ret
End Function
' ********************************************************' 更新処理' ********************************************************
Function Execute(ByVal cn As OdbcConnection, _
ByVal SQL As String) As Integer
Dim ret As Integer
Dim execCommand As OdbcCommand = New OdbcCommand()
execCommand.CommandText = SQL
execCommand.Connection = cn
Try
ret = execCommand.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine( ex.Message )
End Try
execCommand.Dispose()
Return ret
End Function
End Module
Oracle 10g : System.Data.Odbc( Framework ) : Microsoft Driver
Oracle は、いろいろ試しましたが、結局 Microsoft のドライバが最も安定しています
Imports System.Data.Odbc
Module MyModule
' ********************************************************' 実行' ********************************************************
Sub Main()
Dim myConnectString As String = _
"Driver={Microsoft ODBC for Oracle};" + _
"SERVER=localhost/ORCL;" + _
"UID=lightbox;" + _
"PWD=lightbox"
Dim myCon As New OdbcConnection()
myCon.ConnectionString = myConnectString
myCon.Open()
Dim myQuery As String = "SELECT * from 社員マスタ"
Dim myCommand As New OdbcCommand()
myCommand.CommandText = myQuery
myCommand.Connection = myCon
Dim myReader As OdbcDataReader
myReader = myCommand.ExecuteReader()
Dim fld As Integer = 0
Do While myReader.Read()
fld = myReader.GetOrdinal("氏名")
Console.WriteLine(myReader.GetString(fld))
Loop
myReader.Close()
myCon.Close()
End Sub
End Module
Imports System.Data.Odbc
Module MyModule
' ********************************************************' 実行' ********************************************************
Sub Main()
Dim myConnectString As String = _
"Driver={PostgreSQL Japan};" + _
"SERVER=localhost;" + _
"DATABASE=lightbox;" + _
"UID=postgres;" + _
"PWD=password"
Dim myCon As New OdbcConnection()
myCon.ConnectionString = myConnectString
myCon.Open()
Dim myQuery As String = "SELECT * from 社員マスタ"
Dim myCommand As New OdbcCommand()
myCommand.CommandText = myQuery
myCommand.Connection = myCon
Dim myReader As OdbcDataReader
myReader = myCommand.ExecuteReader()
Dim fld As Integer = 0
Do While myReader.Read()
fld = myReader.GetOrdinal("氏名")
Console.WriteLine(myReader.GetString(fld))
Loop
myReader.Close()
myCon.Close()
End Sub
End Module
Imports System.Data.OleDb
Module MyModule
' ********************************************************' 実行' ********************************************************
Sub Main()
Dim myConnectString As String = _
"Provider=SQLOLEDB;" + _
"Data Source=layla;" + _
"Initial Catalog=mydb;" + _
"User ID=sa;" + _
"Password=;"
Dim myCon As New OleDbConnection()
myCon.ConnectionString = myConnectString
myCon.Open()
Dim myQuery As String = "SELECT * from 社員マスタ"
Dim myCommand As New OleDbCommand()
myCommand.CommandText = myQuery
myCommand.Connection = myCon
Dim myReader As OleDbDataReader
myReader = myCommand.ExecuteReader()
Dim fld As Integer = 0
Do While myReader.Read()
fld = myReader.GetOrdinal("氏名")
Console.WriteLine(myReader.GetString(fld))
Loop
myReader.Close()
myCon.Close()
End Sub
End Module
SQLServer : System.Data.SqlClient( Framework )
Imports System.Data.SqlClient
Module MyModule
' ********************************************************' 実行' ********************************************************
Sub Main()
Dim myConnectString As String = _
"Data Source=localhost;" + _
"Initial Catalog=mydb;" + _
"User ID=sa;" + _
"Password=;"
Dim myCon As New SqlConnection()
myCon.ConnectionString = myConnectString
myCon.Open()
Dim myQuery As String = "SELECT * from 社員マスタ"
Dim myCommand As New SqlCommand()
myCommand.CommandText = myQuery
myCommand.Connection = myCon
Dim myReader As SqlDataReader
myReader = myCommand.ExecuteReader()
Dim fld As Integer = 0
Do While myReader.Read()
fld = myReader.GetOrdinal("氏名")
Console.WriteLine(myReader.GetString(fld))
Loop
myReader.Close()
myCon.Close()
End Sub
End Module
SQLServer : System.Data.Odbc( Framework )
Imports System.Data.Odbc
Module MyModule
' ********************************************************' 実行' ********************************************************
Sub Main()
Dim myConnectString As String = _
"Driver={SQL Server};" + _
"SERVER=localhost;" + _
"DATABASE=mydb;" + _
"UID=sa;" + _
"PWD="
Dim myCon As New OdbcConnection()
myCon.ConnectionString = myConnectString
myCon.Open()
Dim myQuery As String = "SELECT * from 社員マスタ"
Dim myCommand As New OdbcCommand()
myCommand.CommandText = myQuery
myCommand.Connection = myCon
Dim myReader As OdbcDataReader
myReader = myCommand.ExecuteReader()
Dim fld As Integer = 0
Do While myReader.Read()
fld = myReader.GetOrdinal("氏名")
Console.WriteLine(myReader.GetString(fld))
Loop
myReader.Close()
myCon.Close()
End Sub
End Module
MySQL : java ( mysql-connector-java-5.0.6-bin.jar )
import java.sql.*;
public class mysql {
public static void main(String[] args) {
Connection con;
Statement stmt;
ResultSet rset;
// *********************************************************// MySQL 5.1// mysql-connector-java-5.0.6-bin.jar// *********************************************************
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:mysql://localhost/lightbox"
+"?user=root"
+"&password="// *********************************************************// 以下が無い場合、日本語指定の getString が失敗する// *********************************************************
+"&characterSetResults=sjis"
);
stmt = con.createStatement();
rset = stmt.executeQuery ( "select * from `社員マスタ`" );
ResultSetMetaData rm = rset.getMetaData();
int nCols = rm.getColumnCount();
String[] data = new String[nCols];
int i;
while( rset.next() ) {
System.out.print( rset.getString( "氏名" ) + ":" );
for( i = 0; i < nCols; i++ ) {
data[i] = rset.getString( i+1 );
if ( i != 0 ) {
System.out.print( "," );
}
System.out.print( data[i] );
}
System.out.println( );
}
stmt.close();
con.close();
}
catch (Exception e) {
System.out.println( e.getMessage() );
}
}
}
更新処理
// *****************************************************// DB 更新処理// *****************************************************
public int Execute( String Query ) {
ErrorMessage = "";
int nRet;
try {
nRet = stmt.executeUpdate( Query );
}
catch( SQLException e ) {
ErrorMessage = e.getMessage();
return -1;
}
return nRet;
}
' **********************************************************' オブジェクト作成' **********************************************************
Set OraSession = CreateObject( "OracleInProcServer.XOraSession" )
Set Fs = CreateObject( "Scripting.FileSystemObject" )
' **********************************************************' 接続' **********************************************************
on error resume next
OraSession.CreateDatabasePool 1,40,200,"lightbox", "lightbox/lightbox", 0
if Err.Number <> 0 then
Wscript.Echo OraSession.LastServerErrText
Wscript.Quit
end if
on error goto 0
Set OraDatabase = OraSession.GetDatabaseFromPool(10)
OraDatabase.LastServerErrReset
' **********************************************************' レコードセット取得' **********************************************************
Query = "select * from 社員マスタ"
on error resume next
Set OraDynaset = OraDatabase.CreateDynaset(Query,2)
if Err.Number <> 0 then
Wscript.Echo OraDatabase.LastServerErrText
Wscript.Quit
end if
on error goto 0
' **********************************************************' 出力ファイルオープン' **********************************************************
Set Csv = Fs.CreateTextFile( "社員マスタ.csv", True )
' **********************************************************' タイトル出力' **********************************************************
Buffer = ""
For i = 0 to OraDynaset.Fields.Count - 1
if Buffer <> "" then
Buffer = Buffer & ","
end if
Buffer = Buffer & OraDynaset.Fields(i).Name
Next
Csv.WriteLine Buffer
' **********************************************************' データ出力' **********************************************************
Do While not OraDynaset.EOF
Buffer = ""
For i = 0 to OraDynaset.Fields.Count - 1
if Buffer <> "" then
Buffer = Buffer & ","
end if
Buffer = Buffer & OraDynaset.Fields(i).Value
Next
Csv.WriteLine Buffer
OraDynaset.MoveNext
Loop
' **********************************************************' ファイルクローズ' **********************************************************
Csv.Close
' **********************************************************' オブジェクト解放' **********************************************************
Set Fs = Nothing
Set OraDynaset = Nothing
Set OraDatabase = Nothing
Set OraSession = Nothing
ASP による GLOBAL.ASA 利用
<OBJECT
runat=Server
scope=Application
id=OraSession
progid="OracleInProcServer.XOraSession"
></OBJECT>
<SCRIPT language=VBScript runat=Server>
' **********************************************************' アプリケーション開始' **********************************************************
Sub Application_OnStart
OraSession.CreateDatabasePool 1,40,200,"LIGHTBOX","STUDY/STUDY", 0
End Sub
' **********************************************************' アプリケーション終了' **********************************************************
Sub Application_OnEnd
End Sub
' **********************************************************' セッション開始' **********************************************************
Sub Session_OnStart
End Sub
' **********************************************************' セッション終了' **********************************************************
Sub Session_OnEnd
End Sub
</SCRIPT>