Geek Answers Handbook

Web SQL get list of columns from table

I am trying to get a list of columns from Web sql (local Chrome database). One solution is to get information from sqlite_master

SELECT name, sql FROM sqlite_master WHERE type="table" AND name = "'+name+'";

For example, I get this result

 CREATE TABLE table_name ( id INTEGER PRIMARY KEY AUTOINCREMENT, number INTEGER unique, description TEXT, password TEXT, url TEXT )

So help me write regex to get the column names or maybe show me another easy way to get the column names.

PS. I do not want to do select * from table ... to get the column names. I think this is a bad decision.

+4
source share
2 answers

To get the columns of a table, run PRAGMA table_info(table_name) :

PRAGMA table_info ()

Returns one row for each column of the specified table. Columns returned dataset:

  • cid: Column ID (numbered from left to right, starting at 0)
  • name: column name
  • type: The type of the column declaration.
  • notnull: True if ' NOT NULL ' is part of a column declaration
  • dflt_value: the default value for the column, if any.

Unfortunately, Chrome blocks all PRAGMA s, so this does not work in WebSQL.

In WebSQL, you can only access the tables created by your application, so you just need to remember which columns your tables have.

Alternatively, you can just try reading from the table:

 SELECT * FROM table_name LIMIT 1

With the LIMIT this will be very effective because you are only reading some random record. (Except there is a very big blob in this entry.)

+4
source

In chrome, this worked for me with html5sql . I also made code that uses pure-HTML5 with a cool Promise-based query function, here .

 function getDB(cb){ html5sql.process("SELECT * FROM sqlite_master WHERE name NOT LIKE 'sqlite\\_%' escape '\\' AND name NOT LIKE '\\_%' escape '\\'", function(txTables, rsTables, tables){ if (!tables.length) return cb(null, []); tables.forEach(function(table){ var s = table.sql.split(','); s[0] = s[0].replace(new RegExp('create\\s+table\\s+' + table.name + '\\s*\\(', 'i'),''); table.fields = s.map(function(i){ return i.trim().split(/\s/).shift(); }) .filter(function(i){ return (i.indexOf(')') === -1) }) }); cb(null, tables) }, cb); }

This will result in your return message (error, tables) as follows:

 [{ "type": "table", "name": "Users", "tbl_name": "Users", "rootpage": 6, "sql": "CREATE TABLE Users(\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n firstName VARCHAR(255),\n lastName VARCHAR(255),\n email VARCHAR(255),\n created TIMESTAMP DEFAULT (DATETIME('now','localtime'))\n)", "fields": [ "id", "firstName", "lastName", "email", "created" ] }]

Pay attention to the fields section. This works even if there are no entries. Parsing regular expressions / strings could probably use some improvement, and you could probably grab type information as well, but that seems to work with all of my affairs. Alternative method when you know the field names in SQL:

 SELECT TYPEOF(id) as id, TYPEOF(firstName) AS firstName , TYPEOF(lastName) AS lastName, TYPEOF(email) AS email, TYPEOF(created) AS created FROM Users;
+1
source

More articles:


All Articles