Re: Quoted identifiers

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Quoted identifiers

quique-4
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.
Reply | Threaded
Open this post in threaded view
|

Re: Quoted identifiers

Dennis Cote
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