by way of Darren Duncan wrote:
You don´t undertand me, maybe my poor english.
I will try to show with examples in the sqlite command line.
create table test("Full Name" varchar(30), "Login" varchar(15), Age
integer);
insert into test ("Full Name", "Login", Age) values ("Enrique Esquivel",
"the_kique", 24);
.headers on
select * from test;
SQLite returns:
Full Name|Login|Age
Enrique Esquivel|the_kique|24
But when write:
select "Full Name", "Login", Age from test;
returns:
"Full Name"|"Login"|Age
Enrique Esquivel|the_kique|24
Moreover when quote all fields:
select "Full Name", "Login", "Age" from test;
returns:
"Full Name"|"Login"|"Age"
Enrique Esquivel|the_kique|24
Also:
select [Full Name], [Login], [Age] from test;
SQLite returns wrong:
"Full Name"|"Login"|"Age"
Enrique Esquivel|the_kique|24
The quotes should be used for SQLite only for understand the
identifiers, the
fields in result must be unquoted. Try to test with other dbms and
anyone has
this behavior.
Your problem is related to SQLite's column name pragmas. The script
below demonstrates how SQLite displays the names in all four possible
combinations of these settings.
SQLite version 3.2.7
Enter ".help" for instructions
sqlite> create table test("Full Name" varchar(30), "Login" varchar(15),
Age integer);
sqlite> insert into test ("Full Name", "Login", Age) values ('Enrique
Esquivel', 'the_kique', 24);
sqlite> .mode column
sqlite> .header on
sqlite> select * from test;
Full Name Login Age
---------------- ---------- ----------
Enrique Esquivel the_kique 24
sqlite> select "Full Name", Age from test;
Full Name Age
---------------- ----------
Enrique Esquivel 24
sqlite> pragma short_column_names;
short_column_names
------------------
1
sqlite> pragma full_column_names;
full_column_names
-----------------
0
sqlite> pragma short_column_names = 0;
sqlite> select '0-0' as 'Short-Full', "Full Name", Age from test;
Short-Full "Full Name" Age
---------- ---------------- ----------
0-0 Enrique Esquivel 24
sqlite> pragma full_column_names = 1;
sqlite> select '0-1' as 'Short-Full', "Full Name", Age from test;
Short-Full test.Full Name test.Age
---------- ---------------- ----------
0-1 Enrique Esquivel 24
sqlite> pragma short_column_names = 1;
sqlite> select '1-1' as 'Short-Full', "Full Name", Age from test;
Short-Full test.Full Name test.Age
---------- ---------------- ----------
1-1 Enrique Esquivel 24
sqlite> pragma full_column_names = 0;
sqlite> select '1-0' as 'Short-Full', "Full Name", Age from test;
Short-Full Full Name Age
---------- ---------------- ----------
1-0 Enrique Esquivel 24
You appear to have both short_column_names and full_column_names set to
0. If you can't change the short_column_name pragma back to 1 (the
default value), you can still get your desired behavior by giving the
column an alias in your query as shown below. This isn't really a
general solution though. It won't work for user supplied SQL for example.
sqlite> pragma short_column_names = 0;
sqlite> select "Full Name" as 'Full Name', Age from test;
Full Name Age
---------------- ----------
Enrique Esquivel 24
The column name pragmas in SQLite are somewhat messed up (and their
behavior doesn't match the documentation), so don't expect to get
rational behavior from them. All you can do is try different
combinations to see what works best.
Also note, when inserting the strings into the table you were using
double quotes around the literal strings. SQLite has an extension that
lets this work, but you really should be using single quotes as I have
done above. Double quotes are used to quote identifiers in standard SQL,
so they won't work with other SQL database engines.
HTH
Dennis Cote
