Re: [sqlite] PRAGMA table_info fails with "no such tokenizer"

Hello, The fts5 table in your database was created with a custom FTS5 tokenizer named "TrackerTokenizer" (see https://www.sqlite.org/fts5.html#custom_tokenizers). Custom FTS5 tokenizers must be registered in each SQLite connection to the database file, or you get an error like "no such tokenizer:

Re: [sqlite] pragma table_info(tbl)

cid is the column number, to get the primary key look in the pk field and the primary key will have numbers 1, 2 etc. for each field in the primary key. SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to re

Re: [sqlite] pragma table_info(tbl)

-- Catalog Views using sqlite_master for SysObjects (Object Names) -- and the various pragma_(ObjectName) tables to retrieve schema data -- all TEXT columns in views have "collate nocase" attached to the output -- columns to ensure that where conditions on retrievals are not case sensitive -- Colu

Re: [sqlite] pragma table_info return column delimiters?

On 2018/01/03 12:15 PM, Bart Smissaert wrote: Is there a way with pragma table_info or otherwise (other than parsing the table create statement from SQLite_master) to get the column names including the column delimiters, eg double quotes or square brackets? So I would get eg: [column1] [column2]

Re: [sqlite] pragma table_info return column delimiters?

OK, thanks. I am getting the information now from the create table statements and sofar that seems to work OK. Just wanted to make sure there wasn't a better way to handle this. RBS On Wed, Jan 3, 2018 at 11:08 AM, Richard Hipp wrote: > On 1/3/18, Bart Smissaert wrote: > > Is there a way with

Re: [sqlite] pragma table_info return column delimiters?

On 2018/01/03 12:15 PM, Bart Smissaert wrote: Is there a way with pragma table_info or otherwise (other than parsing the table create statement from SQLite_master) to get the column names including the column delimiters, eg double quotes or square brackets? So I would get eg: [column1] [column2]

Re: [sqlite] pragma table_info return column delimiters?

delimiters delimit, so they are not part of the context.. such information aobut delimiters is never saved... since they havce done their job delimiiting already. Why do you want this? On Wed, Jan 3, 2018 at 2:50 AM, Bart Smissaert wrote: > > What are column delimiters? > > create table [table

Re: [sqlite] pragma table_info return column delimiters?

On 1/3/18, Bart Smissaert wrote: > Is there a way with pragma table_info or otherwise (other than parsing the > table create statement from SQLite_master) to get the column names > including the column delimiters, eg double quotes or square brackets? So I > would get eg: [column1] [column2] etc. i

Re: [sqlite] pragma table_info return column delimiters?

> What are column delimiters? create table [table1)([ID] integer, [text_field] text) I am talking about the square brackets here surrounding the field names. Sorry, used the wrong word in saying delimiters. RBS On Wed, Jan 3, 2018 at 10:44 AM, Luuk wrote: > > > On 03-01-18 11:15, Bart Smissae

Re: [sqlite] pragma table_info return column delimiters?

On 03-01-18 11:15, Bart Smissaert wrote: > Is there a way with pragma table_info or otherwise (other than parsing the > table create statement from SQLite_master) to get the column names > including the column delimiters, eg double quotes or square brackets? So I > would get eg: [column1] [column

Re: [sqlite] PRAGMA table_info could not update schema

On 21 August 2017 at 17:25, Clemens Ladisch wrote: > sanhua.zh wrote: > > I find that `PRAGMA table_info(tableName)` will not check the expired > schema which is modified by other sqlite connections. > > > > Here is the sample code: > > That code is incomplete and buggy. (Heed the compiler warni

Re: [sqlite] PRAGMA table_info could not update schema

It's great idea for reproducing with two command-line shells. Of cource, it can be reproduced with shells and here is the test code. Note that you should really care the order for running code. ``` Command-line shell 1: $ sqlite3 SQLite version 3.16.0 2016-11-04 19:09:39 Enter ".help" for usage

Re: [sqlite] PRAGMA table_info could not update schema

sanhua.zh wrote: > I find that `PRAGMA table_info(tableName)` will not check the expired schema > which is modified by other sqlite connections. > > Here is the sample code: That code is incomplete and buggy. (Heed the compiler warnings!) Anyway, I can reproduce this with two command-line shell

Re: [sqlite] PRAGMA table_info and not nullable rowid alias

That's interesting. Apparentely PRAGMA table_info() reports the declared column type not the operational column type. sqlite> CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL); Run Time: real 0.454 user 0.00 sys 0.00 sqlite> PRAGMA table_info(test); cid,name,type,notnull,dflt_value,pk 0

Re: [sqlite] PRAGMA table_info incomplete

My apologies, I was accessing the wrong database :( Thanks. Staffan On Fri, Nov 28, 2014 at 12:29 PM, Clemens Ladisch wrote: > Staffan Tylen wrote: > > I've just found out that the column data returned by PRAGMA table_info > does > > not include columns added using ALTER COLUMN ADD COLUMN. >

Re: [sqlite] PRAGMA table_info incomplete

Staffan Tylen wrote: > I've just found out that the column data returned by PRAGMA table_info does > not include columns added using ALTER COLUMN ADD COLUMN. sqlite> create table t(x); sqlite> pragma table_info(t); 0|x||0||0 sqlite> alter table t add y; sqlite> pragma table_info(t); 0|x||0||0 1|y|

Re: [sqlite] PRAGMA table_info(table-name); - column index reliable?

On Mon, Jun 9, 2014 at 7:06 AM, David Q. wrote: > > D. Richard Hipp: > > No. For a multi-column primary key, the columns that are part of the > > primary key are numbered 1, 2, 3, 4, etc, according to which column they > > are in the primary key. > > For single-column primary keys, they value wi

Re: [sqlite] PRAGMA table_info(table-name); - column index reliable?

> D. Richard Hipp: > No. For a multi-column primary key, the columns that are part of the > primary key are numbered 1, 2, 3, 4, etc, according to which column they > are in the primary key. > For single-column primary keys, they value will always be 1, yes. You're talking about a different thing

Re: [sqlite] PRAGMA table_info(table-name); - column index reliable?

David Q. wrote: > I get columns names in a table by executing the query > > pragma table_info('the_table_name'); > > and using > > sqlite3_column_text(&ps,1) //index 1 = name > > for each row in the result set. > > This works, but can I rely on the index being 1 for the 'name' column? It's very un

Re: [sqlite] PRAGMA table_info(table-name); - column index reliable?

On Sat, Jun 7, 2014 at 5:16 AM, David Q. wrote: > I get columns names in a table by executing the query > > pragma table_info('the_table_name'); > > and using > > sqlite3_column_text(&ps,1) //index 1 = name > > for each row in the result set. > > This works, but can I rely on the index being 1 fo

Re: [sqlite] PRAGMA table_info(second.table)

You should look more closely at the syntax for the Pragma statement. What you need is: PRAGMA test.table_info(tab1); This makes sense when you consider not all pragmas have arguments. Peter On 6/4/2014 11:19 PM, LacaK wrote: Hi, when I attach database using f.e ATTACH DATABASE 'test.db' AS

Re: [sqlite] PRAGMA table_info(second.table)

On 6/5/2014 2:19 AM, LacaK wrote: Then when I try PRAGMA table_info(test.tab1) , I get error: near ".": syntax error. The correct syntax is PRAGMA test.table_info(tab1) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cg

Re: [sqlite] pragma table_info(database.table) not supported

On Mar 21, 2013, at 6:41 PM, Peter Haworth wrote: > I found the code in the two attached files (are they allowed on this list?) > on the web a while back. The claim was that it created an information > schema database from an sqlite db. I know nothing about Lua but I managed > to get as far as

Re: [sqlite] pragma table_info(database.table) not supported

On 21 Mar 2013, at 5:41pm, Peter Haworth wrote: > I found the code in the two attached files (are they allowed on this list?) You can't attach things to messages to this list. Thanks for finding the code. I think it's interesting as a talking point but we don't actually need to see the code

Re: [sqlite] pragma table_info(database.table) not supported

no longer has the code. but I'd be interested to know if it works. On Thu, Mar 21, 2013 at 9:00 AM, wrote: > Message: 16 > Date: Wed, 20 Mar 2013 22:15:39 + > From: Simon Slavin > To: General Discussion of SQLite Database > Subject: Re: [sqlite] pragma table_info

Re: [sqlite] pragma table_info(database.table) not supported

On 20 Mar 2013, at 6:52pm, Petite Abeille wrote: > On Mar 20, 2013, at 6:53 PM, Jay A. Kreibich wrote: > >> If there is any change I'd like to see, it is that all the PRAGMAs >> that return tabular data should really be system catalog tables. > > Triple hurray for that! SQLite deserves a prop

Re: [sqlite] pragma table_info(database.table) not supported

On Mar 20, 2013, at 6:53 PM, Jay A. Kreibich wrote: > If there is any change I'd like to see, it is that all the PRAGMAs > that return tabular data should really be system catalog tables. Triple hurray for that! SQLite deserves a proper data dictionary, no question asked. What about adoptin

Re: [sqlite] pragma table_info(database.table) not supported

On Wed, Mar 20, 2013 at 07:00:29PM +0100, Stephan Beal scratched on the wall: > On Wed, Mar 20, 2013 at 6:53 PM, Jay A. Kreibich wrote: > > > That way I can use WHERE on them. In the past I've used virtual > > tables to wrap the PRAGMAs into something that looked and acted more > > like a

Re: [sqlite] pragma table_info(database.table) not supported

On Wed, Mar 20, 2013 at 6:53 PM, Jay A. Kreibich wrote: > That way I can use WHERE on them. In the past I've used virtual > tables to wrap the PRAGMAs into something that looked and acted more > like a real table. > Hi, Jay, can you give us an example of how that is done? -- - step

Re: [sqlite] pragma table_info(database.table) not supported

On Wed, Mar 20, 2013 at 05:57:12PM +0100, Staffan Tylen scratched on the wall: > >PRAGMA table_info(database.tablename) > > > > Any chance that SQLite4 might change this, or perhaps accept both forms ? > In addition, how about a pragma to get the tables in a database? We > currently have pragm

Re: [sqlite] pragma table_info(database.table) not supported

>PRAGMA table_info(database.tablename) > > Any chance that SQLite4 might change this, or perhaps accept both forms ? > > In addition, how about a pragma to get the tables in a database? We currently have pragma(database_list), pragma(index_list), and pragma(table_info), but no pragma(table_list

Re: [sqlite] pragma table_info(database.table) not supported

On 20 Mar 2013, at 11:03am, Richard Hipp wrote: > The syntax for PRAGMA is: > >PRAGMA database.table_info(tablename); Out of interest, is there some internal reason for this non-standard form ? I can understand it if it makes some internal part of SQLite easier to write, but my instinct

Re: [sqlite] pragma table_info(database.table) not supported

Ooops! Thank :) On Wed, Mar 20, 2013 at 12:03 PM, Richard Hipp wrote: > On Wed, Mar 20, 2013 at 6:54 AM, Staffan Tylen >wrote: > > > I've just found out that adding a database name as a table prefix is not > > supported by the pragma table_info function, giving a syntax error. The > > document

Re: [sqlite] pragma table_info(database.table) not supported

On Wed, Mar 20, 2013 at 6:54 AM, Staffan Tylen wrote: > I've just found out that adding a database name as a table prefix is not > supported by the pragma table_info function, giving a syntax error. The > documentation of the ATTACH DATABASE commands says: > > Tables in an attached database can be

Re: [sqlite] pragma table_info(database.table) not supported

Staffan Tylen wrote: > I've just found out that adding a database name as a table prefix is not > supported by the pragma table_info function, giving a syntax error. : PRAGMA mydatabase.table_info(mytable); Regards, Clemens _

Re: [sqlite] PRAGMA table_info documentation

On Feb 2, 2013, at 7:32 PM, chojra...@gmail.com wrote: > I'd like to ask about contents of last column in result of PRAGMA > table_info in SQLite 3.7.15.1 because it is not documented ( > http://www.sqlite.org/pragma.html#pragma_table_info). > It looks as if the value is '1', the column is in the

Re: [sqlite] PRAGMA table_info() with database name?

Kees Nuyt schrieb: > On Sun, 09 May 2010 12:18:41 +0200, Oliver Lange > wrote: > >> Hello, >> >> One thing i'm missing in PRAGMA table_info() is that i can't specify >> a database name, like: PRAGMA table_info(main.mytable); >> > > As a workaround, you can use: > select name from sqlite_master >

Re: [sqlite] PRAGMA table_info() with database name?

On Sun, May 9, 2010 at 6:18 AM, Oliver Lange wrote: > > Hello, > > One thing i'm missing in PRAGMA table_info() is that i can't specify > a database name, like: PRAGMA table_info(main.mytable); > The correct syntax is: PRAGMA main.table_info(mytable); > > Problem: if a TEMPORARY TABLE is

Re: [sqlite] PRAGMA table_info() with database name?

On Sun, 09 May 2010 12:18:41 +0200, Oliver Lange wrote: > >Hello, > >One thing i'm missing in PRAGMA table_info() is that i can't specify >a database name, like: PRAGMA table_info(main.mytable); > >Problem: if a TEMPORARY TABLE is defined using the same name as an >already existing table in the m

Re: [sqlite] pragma table_info on a table in attached db

On Thu, 21 Aug 2008 02:18:39 +0530, Mrinal wrote: >>As Dennis said, that looks like a bug. > > Created a new ticket (number 3320: > http://www.sqlite.org/cvstrac/tktview?tn=3320) > which has been fixed now. Wow, that's fast. >- Mrinal. -- ( Kees Nuyt ) c[_]

Re: [sqlite] pragma table_info on a table in attached db

>As Dennis said, that looks like a bug. Created a new ticket (number 3320: http://www.sqlite.org/cvstrac/tktview?tn=3320) which has been fixed now. - Mrinal. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/li

Re: [sqlite] pragma table_info on a table in attached db

On Wed, 20 Aug 2008 06:33:17 +0530, Mrinal wrote: >>Try: >>PRAGMA [database.]table_info(table-name); > >> ( Kees Nuyt > >Thankyou Kees for the solution yw. >But, I am facing another problem [...] As Dennis said, that looks like a bug. I just confirmed SQLite 3.6.0 behaves the same way. Appa

Re: [sqlite] pragma table_info on a table in attached db

Mrinal Kant wrote: >> Try: >> PRAGMA [database.]table_info(table-name); > >> ( Kees Nuyt > > Thankyou Kees for the solution > But, I am facing another problem which is as follows: I open an sqlite > db and create a table "trial". pragma main.table_info(trial) shows > correct info. Then I create

Re: [sqlite] pragma table_info on a table in attached db

On Tue, 19 Aug 2008 01:14:43 +0530, Mrinal wrote: >pragma table_info(dbname.tablename) syntax is not allowed. Is there >some other way to get the table_info for tables in attached db which >bear the same name as another table in either the main, temp or a >previously attached db? >F.e. >I connect

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

COS wrote: A small opinion on that matter: what I would really like to see is something like system tables. Today sqlite uses only sqlite_master to keep information about its objects and parsing is required to getter better info of each object (if one needs to). Using other system tables to keep

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

COS wrote: Hi, - Original Message - From: "Stef Mientki" <[EMAIL PROTECTED]> To: Sent: Tuesday, March 13, 2007 3:47 PM Subject: Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT You should also consider how your change might

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

Hi, - Original Message - From: "Stef Mientki" <[EMAIL PROTECTED]> To: Sent: Tuesday, March 13, 2007 3:47 PM Subject: Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT > > > > > > You should also consider how your change might

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

You should also consider how your change might effect backwards compatibility. The last time that table_info() was modified, the Ruby-On-Rails community got really upset. I'm rather of a mind to leave table_info() alone. Forgive my ignorance, I'm just a beginner in databases, but what abou

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

"Vivien Malerba" <[EMAIL PROTECTED]> wrote: > On 3/13/07, Martin Jenkins <[EMAIL PROTECTED]> wrote: > > Vivien Malerba wrote: > > > I've already sent a proposal along with a patch some time ago about > > > that, but nobody seemed to care, see > > > http://www.mail-archive.com/sqlite-users@sqlite.or

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

On 3/13/07, Martin Jenkins <[EMAIL PROTECTED]> wrote: Vivien Malerba wrote: > I've already sent a proposal along with a patch some time ago about > that, but nobody seemed to care, see > http://www.mail-archive.com/sqlite-users@sqlite.org/msg21285.html Vivien, I can't see any patch attached to t

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

Vivien Malerba wrote: I've already sent a proposal along with a patch some time ago about that, but nobody seemed to care, see http://www.mail-archive.com/sqlite-users@sqlite.org/msg21285.html Vivien, This mailing list does not pass attachments. A patch such as your is best handled by subm

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

Vivien Malerba wrote: I've already sent a proposal along with a patch some time ago about that, but nobody seemed to care, see http://www.mail-archive.com/sqlite-users@sqlite.org/msg21285.html Vivien, I can't see any patch attached to that post. Perhaps you should resubmit it? And I'll guess t

Re: [sqlite] Pragma table_info(), why no fields like UNIQUE, AUTOINCREMENT

On 3/12/07, Stef Mientki <[EMAIL PROTECTED]> wrote: If ask the table sturcture, with pragma table_info() I get of course the basic fields, like: CID,Name,Type, And also SOME special values, like Null, DefaultValue, PrimaryKey But NOT the following special values (and probably a lot more)

RE: [sqlite] pragma table_info not working on empty table

I think the trouble was somewhere else and it looks that pragma works fine Even with an empty table. Thanks for the tip in any case. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 24 January 2007 22:56 To: sqlite-users@sqlite.org Subject: Re: [sqlite

Re: [sqlite] pragma table_info not working on empty table

I'm not sure if this applies here, but you could try calling this first: PRAGMA empty_result_callbacks = true; -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message -- From: "RB Smissaert" <[EMAIL PROTECT

Re: [sqlite] PRAGMA table_info() reveals primary keys

Dennis Cote wrote: You should create a documentation bug ticket so these things will get fixed. Done. -- David - To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] PRAGMA table_info() reveals primary keys

David Crawshaw wrote: I had need of a programmatic interface to determine the primary keys of a table, so I went to add it to pragma table_info, only to discover it is already there. The comment in pragma.c seems out of date, failing to mention the sixth column 'pk', and so is sqlite.org/pragma.

Re: [sqlite] pragma table_info(...) documentation?

On Thu, 27 Apr 2006, Dan Baker wrote: [...] It appears to return 6 columns of data per table-column. I believe they are as follows: [1] = column# (0...) [2] = column name [3] = column affinity/type [...] [4] = flag: is the column NOT NULL? [5] = default value of the column [6] = flag: is th

Re: [sqlite] pragma table_info(...) documentation?

On 4/27/06, Dan Baker <[EMAIL PROTECTED]> wrote: > I'm needing to find some information about a table, and the table_info > pragma seems to be the right ticket. > > I've looked around, and failed, to find any documentation on this cool > pragma. > It appears to return 6 columns of data per table-co

Re: [sqlite] PRAGMA table_info

"Jackson, Douglas H" wrote: > > This leaves you to parse the DDL from sqlite_master. > Doug ... or to correlate 'pragma table_info(tablename)' with 'pragma index_info(tablename)'. Some (unreadable, imperfect) example code can be found in the ODBC driver e.g. in the functions SQLPrimaryKeys(), SQL

RE: [sqlite] PRAGMA table_info

> -Original Message- > From: Marco Bambini [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 13, 2006 2:38 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] PRAGMA table_info > > Using PRAGMA table_info(...) there is no way to know if a > column is declared as unique and/or autoincrem

RE: [sqlite] PRAGMA table_info

This leaves you to parse the DDL from sqlite_master. Doug -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, April 13, 2006 2:51 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] PRAGMA table_info Marco Bambini <[EMAIL PROTECTED]> wrote: &

Re: [sqlite] PRAGMA table_info

Marco Bambini <[EMAIL PROTECTED]> wrote: > Using PRAGMA table_info(...) there is no way to know if a column is > declared as unique and/or autoincrement. > Am I missing something? > Is there a way to get these info? > There is no pragma currently (that I recall) for finding if a column is uniq

Re: [sqlite] PRAGMA table_info oddness

Well, it's not the prettiest thing in the world, but it definitely works. Brilliant! Many thanks for your help, -Mike Ashmore On Jan 23, 2006, at 1:01 PM, Kurt Welgehausen wrote: Sorry, I didn't read your code carefully enough the first time. Your approach won't work because origin has no ex

Re: [sqlite] PRAGMA table_info oddness

Sorry, I didn't read your code carefully enough the first time. Your approach won't work because origin has no explicit type, and SQLite will not infer a type from the literal string; the unknown type defaults to numeric. As Dennis said, the problem is really with RoR, but you can work around it b

Re: [sqlite] PRAGMA table_info oddness

On Jan 22, 2006, at 7:43 PM, Kurt Welgehausen wrote: There's no string type in SQL. Go to and read section 2.1. Regards Okay, my previous message related to SQL syntax, so I suppose it's fair to point out that "string" isn't a SQL type. In fact, I shou

Re: [sqlite] PRAGMA table_info oddness

Mike Ashmore wrote: Hi folks, I'm trying to create a composite view from multiple database files, with an extra field for the origin of a particular record. A sample scenario: There's a table, "foo," which exists in two database files, 'a.db3' and 'b.db3'. Let's define it as: CREATE TAB

Re: [sqlite] PRAGMA table_info oddness

There's no string type in SQL. Go to and read section 2.1. Regards

Re: [sqlite] pragma table_info

On Sun, 2005-03-06 at 23:36 -0800, Charles Mills wrote: > Is there anyway to do 'pragma table_info' on a table in an attached > database (even if a table in the main database has the same name). I > guess I am wondering why this doesn't work: > PRAGMA table_info(database_name.table_name) > > I