2 sql snippets

A General Purpose SQL-to-HTML Routine for CoffeeScript/JavaScript/Node.js

Using node-mysql or similar, the following CoffeeScript routine will generate an HTML table containing the data in a SQL result set, including column headings:

sql_to_html:(connection,query,bindvars,callback)->
  connection.query query, bindvars, (err,rows,fields)->
    if err?
       callback(err)
    else
      buffer = '<table border=1><tr>'
      for field in fields
        buffer += "<th>#{field.name}</th>"
      buffer += '</tr>'
      for row in rows
        buffer += '<tr>'
        for field in fields
          buffer += "<td>#{row[field.name]}</td>"
        buffer += '</tr>'
      buffer += '</table>'
      callback(null,buffer)
Published 8 Feb 2014

 

A Cheat Sheet for SQLite

General

  • Most of the SQLite "meta" commands begin with a dot. When in doubt, try .help
  • Use Ctrl-d or .exit or .quit to exit (and Ctrl-c to terminiate a long-running SQL query).
  • Enter .show to see current settings.

Meta-data

  • Enter .databases to see a list of mounted databases.
  • Enter .tables to see a list of table names.
  • Enter .index to see a list of index names.
  • Enter .schema TABLENAME to see the create table statement for a given table.

Import and Export

  • Enter .output FILENAME to pipe output to the specified file. (Use .output stdout to return to the default behavior or printing results to the console.)
  • Enter .mode [csv|column|html|insert|line|list|tabs|tcl] to change the way in which query results are printed.
  • Enter .separator DELIM to change the delimiter used in (list-mode) exports and imports. (Defaults to |.)
  • Enter .dump [TABLEPATTERN] to create a collection of SQL statements for recreating the database (or just those tables with naames matching the optional TABLEPATTERN).
  • Enter .read FILENAME to execute the specified file as a SQL script.
Published 18 Sep 2013

 

This page was generated at 9:56 PM on 15 Jan 2016.
Copyright © 1999 - 2016 Rodney Waldhoff.