When you select columns from a view, the (ADO.NET) DbDataReader that is
returned from the execute call does not contain sensible column names when the
select statement specifies "view-name"."column-name" or
[view-name].[column-name]. Instead of column names, it just contains the view
name for each column. This problem does not occur when selecting from a table,
nor does it occur when the quotes are omitted. This problem is also visible
with the command line sqlite3 program - it displays incorrect headers. This
leads me to suspect that the problem is in SQLite core, not in the ADO.NET
provider.
The following script demonstrates the issue in the sqlite3 command-line shell.
create table TestTable1 (id integer primary key autoincrement, data
varchar(200) not null);
create table TestTable2 (id integer primary key autoincrement, data
varchar(200) not null);
create view TestView as select t1.id, t1.data as data1, t2.data as data2 from
TestTable1 t1 inner join TestTable2 t2 on t1.id = t2.id;
insert into TestTable1 (data) values ('Fred');
insert into TestTable2 (data) values ('Miranda');
.headers on
select id, data2 from TestView;
select TestView.id, TestView.data2 from TestView;
select "TestView"."id", "TestView"."data2" from TestView;
select [TestView].[id], [TestView].[data2] from TestView;
select id, data from TestTable1;
select TestTable1.id, TestTable1.data from TestTable1;
select "TestTable1"."id", "TestTable1"."data" from TestTable1;
select [TestTable1].[id], [TestTable1].[data] from TestTable1;
If you look at the headers output from the 8 select statements you will see
that the headers contain only the view name for each column in the case of the
3rd and 4th statements.
This problem makes SQLite views completely unusable with ServiceStack.OrmLite
version 4.0.23 or newer since it always uses the
"view-or-table-name"."column-name" format for select statements.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
