[OSGeoJapan-discuss] spatialite形式レイヤの属性テーブルを ADOで接続・編集したい

[西村和志]

皆様

お世話になります。
標記の件から派生してsqlite3のDBをodbc経由でADO接続できるかチェックしていたのですが、
ちょっとカオスな感じになっています。
ご興味のない方々は読み飛ばすよう、お願いします。

先に結論だけ
１．sqlite3.exeで和名データベースを作成すると、データベース名が文字化けする
２．sqlite3.exeで和名フィールド、和名レコードを作成するとsqlite3.exe内では正常に表示されるが、ExcelVBAでADO接続するとレコードセットがオープンできない、英
名フィールドに和名レコードが入っている場合はレコードセットがオープンできてもレコードの和名部分は（excel表示で）文字化ける。
３．sqlite3.exeの文字コードが原因？


本日の作業20100714
１．日本語のデータベースを作成、英名フィールド、英字レコードの挿入

.\sqlite3.exe テストdb.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 );
COMMIT; 

結果
・テストdb.sqlite3の「テスト」部分が化けて「eXgdb.sqlite3」というファイルができる。これに接続してみると
.\sqlite3.exe eXgdb.sqlite3
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
データベース名は化けるが、テーブル、レコードは生成されている。
でも、ちょっと気になる。

２．英名データベースを作成、和名フィールド、英字レコードの挿入

.\sqlite3.exe testdb.sqlite3

CREATE TABLE testtable1 (id INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
    西暦 INTEGER ,
    名前 TEXT ,
    価格 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 );
COMMIT; 

フィールド名が文字化けていないかチェック

select * from sqlite_master;
table|testtable1|testtable1|2|CREATE TABLE testtable1 (id INTEGER  PRIMARY KEY A
UTOINCREMENT NOT NULL,
    西暦 INTEGER ,
    名前 TEXT ,
    価格 REAL)

大丈夫そう
レコードをチェック

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

大丈夫

日本語混じりのレコードを挿入してみる

BEGIN TRANSACTION; 
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; 

問題なし

次に、ADODBでtestdb.sqlite3のtesttable1に接続してみる。
(結果、エラーになる。詳細は下記コード)

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\testdb.sqlite3"
'sqliteに接続
con.Open
Set rs = New ADODB.Recordset
'この直下のrs.openでエラーになる
rs.Open "SELECT id,西暦,名前,価格 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("西暦").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

testdb.sqlite3に英名フィールド、和名レコードを挿入してみる

CREATE TABLE testtable2 (id INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
    year INTEGER ,
    name TEXT ,
    price REAL);
BEGIN TRANSACTION; 
INSERT INTO testtable2 VALUES(7, 2009,  '西邑', 1.1 );
INSERT INTO testtable2 VALUES(8, 2010,  '棚か', 1.1 );
INSERT INTO testtable2 VALUES(9, 2011,  '粟飯原', 1.1 );
INSERT INTO testtable2 VALUES(10, 2015,  '五島', 1.1 );
INSERT INTO testtable2 VALUES(11, 2020,  '橘川', 1.1 );
INSERT INTO testtable2 VALUES(12, 2025,  '尾化咲', 1.1 );
COMMIT; 

ADODBでtesttable2を取得、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\testdb.sqlite3"
'sqliteに接続
con.Open
Set rs = New ADODB.Recordset
rs.Open "SELECT id,year,name,price FROM testtable2;", 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

結果は下記の通り
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
nameフィールドに入力した和名データが文字化け・・・
でもsqlite3.exeでselect文で確認すると正常に表示される・・・

本日の結論
１．sqlite3.exeで和名データベースを作成すると、データベース名が文字化けする
２．sqlite3.exeで和名フィールド、和名レコードを作成するとsqlite3.exe内では正常に表示されるが、ExcelVBAでADO接続するとレコードセットがオープンできない、英
名フィールドに和名レコードが入っている場合はレコードセットがオープンできてもレコードの和名部分は文字化ける。
３．sqlite3.exeの文字コードが原因？

では、ADODBでsqlite3のデータベースに和名フィールドや和名レコードを挿入するとどうなるのだらうか？
挿入できた場合、sqlite3.exeのselect文で正常に表示されるのだろうか？

なかなかspatialiteにたどりつきません・・・

////////////////////////////////////
旬のものを食べよう！
それが食料自給率向上への近道！
///////////////////////////////////