SQL

Structured Query Language or SQL is a query language used to access data in a relational database. Whether "SQL" should be pronounced as "ess-kyu-ell" or "sequel" is a bit of a holy war — see Pronouncing SQL: S-Q-L or Sequel? .

See Also

SQLScreens

Create query screens quickly.

subSQL

Implements some SQL commands in Tcl, without need for an external database.

Tutorials

SQLZOO

The webpage has only tutorials, which don't really give a good understanding of the language.

1keydata

Provides written description of the language and is easier to follow.

Further Reading

SQL Flaws

Book Review: SQL Hacks , Cameron Laird, 2006-12

Description

Vincent Thomas:

I think SQL queries should look structured but tools like Microsoft Query make it very hard to read. So I wanted a program that I could cut and paste from the tool and as it pasted it would structure it, e.g. convert

SELECT ORDERS.ORDERNUMBER, ORDERS.CUSTNUM, ORDERITEM.AMOUNT, PART.TYPE, PART.MODEL, PART.PRICE
FROM VTHOMAS.ORDERITEM ORDERITEM, VTHOMAS.ORDERS ORDERS, VTHOMAS.PART PART
WHERE ORDERS.ORDERNUMBER = ORDERITEM.ORDERNUM AND PART.PARTNUM = ORDERITEM.PARTNUM

into

SELECT ORDERS.ORDERNUMBER
    , ORDERS.CUSTNUM
    , ORDERITEM.AMOUNT
    , PART.TYPE
    , PART.MODEL
    , PART.PRICE
FROM VTHOMAS.ORDERITEM ORDERITEM
    , VTHOMAS.ORDERS ORDERS
    , VTHOMAS.PART PART
WHERE ORDERS.ORDERNUMBER = ORDERITEM.ORDERNUM
    and PART.PARTNUM = ORDERITEM.PARTNUM

so here it is in Tcl

package require Tk

proc my_textPaste w {
     $w delete 1.0 end 
     set txt [::tk::GetSelection $w CLIPBOARD]
     regsub -all {,} $txt "\n    ," txt
     regsub -nocase -all {\sand\s} $txt "\n    and " txt
     $w insert 1.0 $txt  
}

text .t -width 80 -height 40
pack .t

bind . <<Paste>> {my_textPaste %W; break}

A testament to the power of the text widget. Let me see, that many lines in Java? I'd still be putting a stream together.

Anyone have a good function built to escape user defined variables for inclusion in SQL statements? If so, why not put it here.

MG mainly accesses MySQL from PHP, and often uses the addslashes() function for escaping data for MySQL queries. So, here it is, extremely simply, in Tcl:

  proc addslashes {str} {
    return [string map [list \" "\\\"" \' "\\\'" "\\" "\\\\" "\00" "\\\00"] $str];
  };# addslashes
  % addslashes {This "is" MG's test string}
  This \"is\" MG\'s test string

CMM Thanks MG. Pgintcl does it this way for postgresql strings.

  proc pg_escape_string {s} {
          return [string map {' '' \\ \\\\} $s]
  }

DKF: I was under the impression that PG supports parameterized queries, which is a far superior way to handle this sort of problem (i.e. no worries about whether you got your quoting right...)

NEM 2008-04-14: Does anyone know if there is a standard way of quoting SQL values safely? My experience is that different SQL engines have different ideas of how to quote characters and even which characters need quoting, which is one factor that makes migrating from one RDBMS to another rather difficult.

schlenk 2008-04-14: There is no real standard, and quoting depends on context quite a bit. For example string literals are quoted differently to identifiers and quoted differently to patterns in LIKE expressions. In general the best way is to use the parameterized queries wherever possible.

RS 2008-07-01: Selecting for a prefix of a field content can be done with

 field LIKE 'PREFIX%'

but that does not use indices, and can thus be very slow. The following proc converts a prefix into two range points like

 field >= 'PREFIX' AND field < 'PREFIY'

where indices are hopefully used:

 proc prefixrange {col prefix} {
    set last [string index $prefix end]
    set next [format %c [expr {[scan $last %c]+1}]]
    set end [string range $prefix 0 end-1]$next
    return "$col >= '$prefix' AND $col < '$end''"
 }

HolgerJ Sometimes you have some list of SQL commands, but in order to send them to a database connection, you have to split them into the commands. Therefore, it would be nice to have exactly one command per line, so that you can use Tcl's gets or Java's readLine() to get one complete command at a time.

Here's a Tcl script reading a bunch of SQL commands and writing them again line by line:

#! /bin/env tclsh

# sql2line.tcl
# rewrite SQL commands into a single line each and remove
# any empty lines and comments

# [email protected] 2014-12-25

if {$argc != 2} {
    puts stderr "\nUsage:\n\t$argv0 fromfile { tofile | - }\n"
    exit 1
} ;# if

if [catch {open [lindex $argv 0]} in] {
    puts stderr "\nError opening input file:\n\t$in\n"
    exit 2
} ;# if

if {[lindex $argv 1] eq {-}} {
    set out stdout
} elseif [catch {open [lindex $argv 1] w} out] {
    puts stderr "\nError opening output file:\n\t$out\n"
    exit 2
} ;# if

set parenLevel 0
set quoteLevel 0
set line {} 
set prevCh {} 
set ch {} 

while 1 {
    set prevCh $ch
    set ch [read $in 1]
    if {$ch eq {-} && $prevCh eq {-}} {
        gets $in
        set ch {} 
        set line [string range $line 0 end-1]
        continue
    } ;# if
    if {[eof $in]} {
        if {[string trim $line] ne {}} {
            puts stderr "incomplete command at end of file:\n\t'$line'"
            exit 3
        } ;# if
        break
    } ;# if
    switch -- $ch {
        {(}  {incr parenLevel}
        {)}  {incr parenLevel -1}
        {'}  {incr quoteLevel}
        \n - \r {
            set ch { }
        }
    } ;# switch
    append line $ch
    set quoteLevel [expr $quoteLevel % 2]
    if {$ch eq  {;} && $quoteLevel == 0 && $parenLevel == 0} {
        puts $out [string trim $line]
        set line {} 
    } ;# if
} ;# while

close $out
close $in

SQLScreens is a simple relational database screen form generator written in TCL/TK. it will let you create query screens very quickly by specifying what data (tables and columns) you want to see and what operations should be allowed (query/update, etc...). It will take care of creating the user interface and the behind-the-scenes SQL.