Re: [sqlite] [EXTERNAL] No such column error

[Hick Gunter]

The mailing list is deprecated. You need to go to https://sqlite.org/forum/ for 
the sqlite forum.

Can you replicate the problem while using the sqlite shell? Are you checking 
column names returned from the second statement? Note that a.BIRTH.YYY from 
your example looks a bit weird for a qualified column name.

My guess is that you are either not accessing the database file you want (rumor 
has it that windows has some strange goings on under the hood for certain file 
locations) or not handling transactions properly.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Aydin Ozgur Yagmur
Gesendet: Montag, 23. März 2020 13:30
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] No such column error

I have been developing a c++ program in windows. In this program, in a loop, I 
open database, query and update the records then close it.

I am encountering randomly with "No such column error".

Query looks like that (but there are more columns than this STATUS table):
"select a.BIRTH_DATE, a.BIRTH_PLACE, a.BIRTH_XXX, a.BIRTH.YYY from STATUS a;"

I randomly encounter with this error while executing sqlite3_prepare_v2 method. 
When encountered this error, I made a second execution of
sqlite3_prepare_v2  with "select * from STATUS a;" statement. Return value was 
SQLITE_OK.

Do you have any idea what is going on?

Thank you very much in advance,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] No such column error

[Aydin Ozgur Yagmur]

I have been developing a c++ program in windows. In this program, in a
loop, I open database, query and update the records then close it.

I am encountering randomly with "No such column error".

Query looks like that (but there are more columns than this STATUS table):
"select a.BIRTH_DATE, a.BIRTH_PLACE, a.BIRTH_XXX, a.BIRTH.YYY from STATUS
a;"

I randomly encounter with this error while executing sqlite3_prepare_v2
method. When encountered this error, I made a second execution of
sqlite3_prepare_v2  with "select * from STATUS a;" statement. Return value
was SQLITE_OK.

Do you have any idea what is going on?

Thank you very much in advance,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Use of same aliases in single query

[Rob Golsteijn]

Hi list,

Accidentally I discovered that I could use the same table aliases for 2 tables 
in a single sql statement.
As long as I do not use a column name that exists in both tables Sqlite accepts 
this.
It looks strange to me that I can use the same aliases multiple times, and that 
they co-exist.
As a programmer I'm not used to this, but the Sqlite docs do not seem to forbid 
this. Is this a bug or a feature? 
I guess because of backward compatibility this behavior will not be changed. 
Something to document in https://www.sqlite.org/quirks.html ?

Similar behavior for result row aliases, but in that case the ambiguous aliases 
can even be used.

Examples duplicate table aliases:



CREATE TABLE aaa (a INT, z INT);
CREATE TABLE bbb (b INT, z INT);

SELECT 1 FROM aaa x, bbb x ON x.a = x.b;  -- Same alias "x" for 2 tables, but 
no complaints (all column names in the query can be resolved)
SELECT 1 FROM aaa x, bbb x ON x.z = x.z;  -- "Error: ambiguous column name: x.z"
SELECT * FROM aaa x, bbb x ON x.a = x.b;  -- "Error: ambiguous column name: 
x.z" (during expansion of *)


Example duplicate result rows aliases:



SELECT

    x.a as y,
    x.z as y    -- Same alias, but no complaints
  FROM aaa x;

And the result row aliases can even be used in the query.

INSERT INTO "aaa" VALUES(1,2);
INSERT INTO "aaa" VALUES(1,3);

SELECT count(),
   x.a as y,
   x.z as y
  FROM aaa x
GROUP BY y;   -- No complaints, even though "y" is ambiguous here

count()|y|y
2|1|3 -- Looks like the first alias "y" is used.

Tested with versions 3.27.2 and 3.15.2.

Regards,
Rob Golsteijn

Met Vriendelijke Groet, Kind Regards, 谨致问候,


Rob


---


Rob Golsteijn     Software Engineer     Mapscape

Luchthavenweg 34  |  5657 EB  Eindhoven  |  The Netherlands 
Phone  +31 (0)40 7113583  |  Fax: +31 (0)40 711 3599  

www.mapscape.eu  


 

Mapscape B.V. is ISO9001:2008 certified.This e-mail and any attachment may 
contain corporate proprietary information and may only be read, copied and used 
by the intended recipient. If you have received it by mistake, please notify us 
immediately by reply e-mail and delete this e-mail and its attachments from 
your system. We believe but do not warrant that this message and any 
attachments are virus free. Mapscape B.V. is registered at the Kamer van 
Koophandel Oost-Brabant located in Eindhoven, The Netherlands number 17210210


 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users