[OSGeoJapan-discuss] spatialite形式レイヤの属性テーブルを ADOで接続・編集したい
西村和志
kazushin ＠ affrc.go.jp
2010年 7月 15日 (木) 00:01:46 EDT
皆様
お世話になります。
もはや私的メモと化したスレッドですが・・・
SQlite3データベースのExcel/ADO接続の試行です。
先に結論を
結論1:レコードに日本語が含まれている場合はsqlite3.exe、Excel/ADOの併用は難しい（互いの入力値が文字化ける）。
結論２：sqlite3はテーブル・フィールド作成、データ入出力を全てexcel/ADOで行うならexcel/ADOで和名テーブル、和名フィールド、日本語データを取り扱える。この場
合、sqlite3.exeではテーブル名、フィールド名が文字化けて表示され、かつselect文でテーブルを開くことができない（？）
以下が試行内容です。
まずはsqlite3.exeで英名テーブル、英名フィールドを作成、英語・日本語混じりでレコードを追加してみる
.\sqlite3.exe test100715.sqlite3
CREATE TABLE testtable1 (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
year INTEGER ,
name TEXT ,
price REAL);
BEGIN TRANSACTION;
INSERT INTO testtable1 VALUES(1, 2009, 'nishimura', 1.1 );
INSERT INTO testtable1 VALUES(2, 2010, 'tanaka', 1.1 );
INSERT INTO testtable1 VALUES(3, 2011, 'aihara', 1.1 );
INSERT INTO testtable1 VALUES(4, 2015, 'gotou', 1.1 );
INSERT INTO testtable1 VALUES(5, 2020, 'kikkawa', 1.1 );
INSERT INTO testtable1 VALUES(6, 2025, 'okazaki', 1.1 );
INSERT INTO testtable1 VALUES(7, 2009, '西邑', 1.1 );
INSERT INTO testtable1 VALUES(8, 2010, '棚か', 1.1 );
INSERT INTO testtable1 VALUES(9, 2011, '粟飯原', 1.1 );
INSERT INTO testtable1 VALUES(10, 2015, '五島', 1.1 );
INSERT INTO testtable1 VALUES(11, 2020, '橘川', 1.1 );
INSERT INTO testtable1 VALUES(12, 2025, '尾化咲', 1.1 );
COMMIT;
select * from testtable1;
結果は
1|2009|nishimura|1.1
2|2010|tanaka|1.1
3|2011|aihara|1.1
4|2015|gotou|1.1
5|2020|kikkawa|1.1
6|2025|okazaki|1.1
7|2009|西邑|1.1
8|2010|棚か|1.1
9|2011|粟飯原|1.1
10|2015|五島|1.1
11|2020|橘川|1.1
12|2025|尾化咲|1.1
特に問題は見当たらない
このテーブルをADOでExcelに表示してみる
Sub test()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
'Connection生成と接続文字列の設定
Set con = New ADODB.Connection
con.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=F:\gisdata\spatialite\sqlite3\test100715.sqlite3"
'sqliteに接続
con.Open
Set rs = New ADODB.Recordset
rs.Open "SELECT id,year,name,price FROM testtable1;", con, adOpenStatic, adLockPessimistic, adCmdText
rs.MoveFirst
i = 1
Do Until rs.EOF = True
'以下３行はデータをExcelに書き出す
Cells(i, 1).Value = rs.Fields("id").Value
Cells(i, 2).Value = rs.Fields("year").Value
Cells(i, 3).Value = rs.Fields("name").Value
Cells(i, 4).Value = rs.Fields("price").Value
rs.MoveNext
i = i + 1
Loop
'接続を閉じる
con.Close
Set con = Nothing
End Sub
結果は
1 2009 nishimura 1.1
2 2010 tanaka 1.1
3 2011 aihara 1.1
4 2015 gotou 1.1
5 2020 kikkawa 1.1
6 2025 okazaki 1.1
7 2009 W 1.1
8 2010 I 1.1
9 2011 ь 1.1
10 2015 ? 1.1
11 2020 ki 1.1
12 2025 c 1.1
レコード7から12の名前が文字化け
続けてADOで全てのレコードのnameフィールドに"西村"を入力
Sub test()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
'Connection生成と接続文字列の設定
Set con = New ADODB.Connection
con.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=F:\gisdata\spatialite\sqlite3\test100715.sqlite3"
'sqliteに接続
con.Open
Set rs = New ADODB.Recordset
rs.Open "SELECT id,year,name,price FROM testtable1;", con, adOpenStatic, adLockPessimistic, adCmdText
rs.MoveFirst
i = 1
Do Until rs.EOF = True
'以下2行は全てのレコードの"name"フィールドに"西村"を入力する→ステップインで実行したところ、レコード7のupdateでエラーになる
rs.Fields("name") = "西村"
rs.Update
rs.MoveNext
i = i + 1
Loop
'接続を閉じる
con.Close
Set con = Nothing
End Sub
ステップインで実行したところ、レコード7のupdateでエラーになる
続けてExcelシートにテーブルを出力してみると
1 2009 西村 1.1
2 2010 西村 1.1
3 2011 西村 1.1
4 2015 西村 1.1
5 2020 西村 1.1
6 2025 西村 1.1
7 2009 W 1.1
8 2010 I 1.1
9 2011 ь 1.1
10 2015 ? 1.1
11 2020 ki 1.1
12 2025 c 1.1
レコード1から6のnameフィールドには"西村"が入力されているが、レコード7以下は以前のまま。
このテーブルを今度はsqlite3.exeのselect文で表示してみる。
.\sqlite3.exe test100715.sqlite3
select * from testtable1;
結果は
1|2009|隘ソ譚掃1.1
2|2010|隘ソ譚掃1.1
3|2011|隘ソ譚掃1.1
4|2015|隘ソ譚掃1.1
5|2020|隘ソ譚掃1.1
6|2025|隘ソ譚掃1.1
7|2009|西邑|1.1
8|2010|棚か|1.1
9|2011|粟飯原|1.1
10|2015|五島|1.1
11|2020|橘川|1.1
12|2025|尾化咲|1.1
Excelに正しく出力されていたレコード1から6は文字化け、
Excelで文字化けし、updateに失敗したレコード7から12は（最初の入力値のまま）正しく表示。
結論1:レコードに日本語が含まれている場合はsqlite3.exe、Excel/ADOの併用は難しい。互いに文字化ける。
対策？：sqlite3テーブルをExcel/ADOで読み書きする場合はVBAコード内で文字コードの変換を行う必要がある？
おまけ
Excel/ADOでtest100715.sqlite3に和名テーブル、和名フィールド、日本語レコードを追加するとどうなるか
Sub test()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
'Connection生成と接続文字列の設定
Set con = New ADODB.Connection
con.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=F:\gisdata\spatialite\sqlite3\test100715.sqlite3"
'sqliteに接続
con.Open
con.Execute "CREATE TABLE 日本語テーブル (番号, 名前, 価格);"
con.Execute "INSERT INTO 日本語テーブル VALUES(3, '西村', 55.1);"
con.Execute "INSERT INTO 日本語テーブル VALUES(4, '九州', 88);"
con.Execute "INSERT INTO 日本語テーブル VALUES(5, '北海道', 55.11111);"
con.Execute "INSERT INTO 日本語テーブル VALUES(6, '東北', 55.1222);"
con.Execute "INSERT INTO 日本語テーブル VALUES(7, '関東', 55.12);"
con.Execute "INSERT INTO 日本語テーブル VALUES(8, '中国', 5.1);"
con.Close
Set con = Nothing
End Sub
特にエラーは起きない
作成したテーブルをExcelに出力してみる
Sub test2()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
'Connection生成と接続文字列の設定
Set con = New ADODB.Connection
con.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=F:\gisdata\spatialite\sqlite3\test100715.sqlite3"
'sqliteに接続
con.Open
Set rs = New ADODB.Recordset
rs.Open "SELECT 番号,名前,価格 FROM 日本語テーブル;", con, adOpenStatic, adLockPessimistic, adCmdText
rs.MoveFirst
i = 1
Do Until rs.EOF = True
'以下３行はデータをExcelに書き出す
Cells(i, 1).Value = rs.Fields("番号").Value
Cells(i, 3).Value = rs.Fields("名前").Value
Cells(i, 4).Value = rs.Fields("価格").Value
rs.MoveNext
i = i + 1
Loop
'接続を閉じる
con.Close
Set con = Nothing
End Sub
結果
3 西村 55.1
4 九州 88
5 北海道 55.11111
6 東北 55.1222
7 関東 55.12
8 中国 5.1
文字化けることなくexcelに出力表示される。
このテーブルがsqlit3.exeでどのように認識されているか、というと
.\sqlite3.exe test100715.sqlite3
select * from sqlite_master;
結果は
table|譌・譛ャ隱槭ユ繝シ繝悶Ν|譌・譛ャ隱槭ユ繝シ繝悶Ν|6|CREATE TABLE 譌・譛ャ隱槭ユ繝シ
繝悶Ν (逡ェ蜿キ, 蜷榊燕, 萓。譬シ)
テーブル名、フィールド名が文字化ける・・・
結論２：sqlite3はテーブル・フィールド作成、データ入出力を全てexcel/ADOで行うならexcel/ADOで和名テーブル、和名フィールド、日本語データを取り扱える。この場
合、sqlite3.exeではテーブル名、フィールド名が文字化けて表示され、かつselect文でテーブルを開くことができない（？）
////////////////////////////////////
全ての農作物には何かしら効能がある。
結局、バランスの良い食事に勝るものはない。
///////////////////////////////////
OSGeoJapan-discuss メーリングリストの案内