Yes, it looks indeed explain doesn't help me out there, thanks. Best probably to compare to the pragma list and check the SQL length.
RBS On Wed, Aug 1, 2018 at 4:06 PM, David Raymond <david.raym...@tomtom.com> wrote: > Looking like the generalized answer is no, as you can still get that with > some valid pragma statements, especially ones that don't return a value. > > D:\>sqlite3 > SQLite version 3.24.0 2018-06-04 19:24:41 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> explain pragma index_list; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 1 0 00 Start at 1 > 1 Halt 0 0 0 00 > > sqlite> explain pragma index_list();--with parenthesis but no table name > Error: near ")": syntax error > > sqlite> explain pragma index_list(missingTable); > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 1 0 00 Start at 1 > 1 Halt 0 0 0 00 > > sqlite> create table noIndexes (a int); > > sqlite> explain pragma index_list(noIndexes); > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 2 0 00 Start at 2 > 1 Halt 0 0 0 00 > 2 Transaction 0 0 1 0 01 usesStmtJournal=0 > 3 Goto 0 1 0 00 > > sqlite> create table withIndexes (a text primary key, b unique); > > sqlite> explain pragma index_list(withIndexes); > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 14 0 00 Start at 14 > 1 Integer 0 1 0 00 r[1]=0 > 2 String8 0 2 0 sqlite_autoindex_withIndexes_2 00 > r[2]='sqlite_autoindex_withIndexes_2' > 3 Integer 1 3 0 00 r[3]=1 > 4 String8 0 4 0 u 00 r[4]='u' > 5 Integer 0 5 0 00 r[5]=0 > 6 ResultRow 1 5 0 00 output=r[1..5] > 7 Integer 1 1 0 00 r[1]=1 > 8 String8 0 2 0 sqlite_autoindex_withIndexes_1 00 > r[2]='sqlite_autoindex_withIndexes_1' > 9 Integer 1 3 0 00 r[3]=1 > 10 String8 0 4 0 pk 00 r[4]='pk' > 11 Integer 0 5 0 00 r[5]=0 > 12 ResultRow 1 5 0 00 output=r[1..5] > 13 Halt 0 0 0 00 > 14 Transaction 0 0 2 0 01 usesStmtJournal=0 > 15 Goto 0 1 0 00 > > sqlite> explain pragma thisIsABadPragmaName; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 1 0 00 Start at 1 > 1 Halt 0 0 0 00 > > sqlite> explain pragma foreign_keys; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 1 0 00 Start at 1 > 1 Int64 0 1 0 1 00 r[1]=1 > 2 ResultRow 1 1 0 00 output=r[1] > 3 Halt 0 0 0 00 > > sqlite> explain pragma cache_spill; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 1 0 00 Start at 1 > 1 Int64 0 1 0 15857 00 r[1]=15857 > 2 ResultRow 1 1 0 00 output=r[1] > 3 Halt 0 0 0 00 > > sqlite> explain pragma case_sensitive_like; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 1 0 00 Start at 1 > 1 Halt 0 0 0 00 > > sqlite> explain pragma case_sesitive_like = 1;--typo > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 1 0 00 Start at 1 > 1 Halt 0 0 0 00 > > sqlite> explain pragma case_sensitive_like = 1; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 1 0 00 Start at 1 > 1 Halt 0 0 0 00 > > sqlite> pragma case_sensitive_like = 1; > > sqlite> select 'a' like 'A'; > 'a' like 'A' > 0 > > sqlite> explain pragma case_sensitive_like = 0; > addr opcode p1 p2 p3 p4 p5 comment > ---- ------------- ---- ---- ---- ------------- -- ------------- > 0 Init 0 1 0 00 Start at 1 > 1 Halt 0 0 0 00 > > sqlite> pragma case_sensitive_like = 0; > > sqlite> select 'a' like 'A'; > 'a' like 'A' > 1 > > sqlite> > > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Wednesday, August 01, 2018 10:50 AM > To: SQLite mailing list > Subject: Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied > table name valid SQL? > > I think you might be right there, but for my practical purpose I need the > result to be invalid. > I just wonder if a Halt at row 2 and no further rows produced is good way > to determine this. > > RBS > > On Wed, Aug 1, 2018 at 3:39 PM, Hick Gunter <h...@scigames.at> wrote: > > > Judging from the following output, I would say it is data producing, but > > returns no rows for no table or a table that has no indexes. Just > because a > > given select statement returns no matching rows does not make it invalid > > > > asql> create temp table test (i integer, t text); > > asql> create index test_i on test(i); > > asql> pragma index_list(test); > > seq name uniq > > ---- ------------- ---- > > 0 test_i 0 > > asql> explain pragma index_list(test); > > addr opcode p1 p2 p3 p4 p5 comment > > ---- ------------- ---- ---- ---- ------------- -- ------------- > > 0 Integer 0 1 0 00 NULL > > 1 String8 0 2 0 test_i 00 NULL > > 2 Integer 0 3 0 00 NULL > > 3 ResultRow 1 3 0 00 NULL > > 4 Halt 0 0 0 00 NULL > > > > -----Ursprüngliche Nachricht----- > > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > Im Auftrag von David Raymond > > Gesendet: Mittwoch, 01. August 2018 16:31 > > An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > > Betreff: [EXTERNAL] Re: [sqlite] Is pragma index_list without supplied > > table name valid SQL? > > > > Don't forget this point about pragmas: > > > > https://www.sqlite.org/pragma.html > > "No error messages are generated if an unknown pragma is issued. Unknown > > pragmas are simply ignored. This means if there is a typo in a pragma > > statement the library does not inform the user of the fact." > > > > That way if there's a typo, or if you try a new pragma in an old version > > then it won't complain, it just won't do anything. > > > > -----Original Message----- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > On Behalf Of Bart Smissaert > > Sent: Wednesday, August 01, 2018 10:13 AM > > To: General Discussion of SQLite Database > > Subject: [sqlite] Is pragma index_list without supplied table name valid > > SQL? > > > > Using SQLite 3.22.0 > > > > In my app I have code to determine if a given SQL string is > > data-producing, non-data producing or invalid. It uses these 3 SQLite > > functions: > > > > sqlite3_prepare_v3 > > sqlite3_stmt_readonly > > sqlite3_column_count > > > > Have been using this code for a few years and sofar never failed, but now > > come across: > > pragma index_list > > so, without a supplied table name. > > This gives me data-producing and I think it should give me invalid. > > > > Firstly is this SQL indeed invalid? > > > > Secondly, if it is I need to add some code to pick this up and was > > thinking about using explain for that. Explain pragma index_list gives > me: > > > > addr opcode p1 p2 p3 p4 p5 comment > > --------------------------------------------------- > > 0 Init 0 1 0 00 Start at 1 > > 1 Halt 0 0 0 00 > > > > And that to me looks it is indeed an invalid SQL as it gives a Halt > > already in the second row and produces no further rows. > > > > Am I right here and would this be a good way to pick up invalid SQL? > > > > > > RBS > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > > 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-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
