SQL
From Erights
(Difference between revisions)
(→Statements and queries) |
(SQL examples) |
||
Line 1: | Line 1: | ||
Examples of using SQL from E can be found in the sql.updoc file. Here is an extract: | Examples of using SQL from E can be found in the sql.updoc file. Here is an extract: | ||
- | |||
- | |||
Create an in-memory relational database using HSQLDB (http://hsqldb.org/): | Create an in-memory relational database using HSQLDB (http://hsqldb.org/): | ||
Line 8: | Line 6: | ||
? def connection := driver.connect("jdbc:hsqldb:mem:test", null) | ? def connection := driver.connect("jdbc:hsqldb:mem:test", null) | ||
- | (replace "mem" with "file" to store the database on disk, or | + | (replace "mem" with "file" to store the database on disk, or replace "hsqldb" to connect to a full database server) |
Wrap it with an SQL quasi-parser: | Wrap it with an SQL quasi-parser: | ||
Line 14: | Line 12: | ||
? def makeSql__quasiParser := <import:org.erights.e.tools.database.makeSQLQuasiParser> | ? def makeSql__quasiParser := <import:org.erights.e.tools.database.makeSQLQuasiParser> | ||
? def sql__quasiParser := makeSql__quasiParser(connection) | ? def sql__quasiParser := makeSql__quasiParser(connection) | ||
+ | # value: <sql__quasiParser> | ||
- | + | Create a test table: | |
- | + | ||
- | Create a test table | + | |
? sql`CREATE TABLE users ( | ? sql`CREATE TABLE users ( | ||
Line 25: | Line 22: | ||
> karma BIGINT NOT NULL, | > karma BIGINT NOT NULL, | ||
> comments VARCHAR(256) DEFAULT NULL)` | > comments VARCHAR(256) DEFAULT NULL)` | ||
- | |||
- | |||
Insert some values: | Insert some values: | ||
Line 49: | Line 44: | ||
# | # | ||
- | + | Get out a single row: | |
- | + | ||
- | + | ||
? def lookupUser(name :String) :int { | ? def lookupUser(name :String) :int { | ||
Line 64: | Line 57: | ||
# problem: no rows returned by "SELECT id FROM users WHERE userName=?" | # problem: no rows returned by "SELECT id FROM users WHERE userName=?" | ||
- | We also get an exception if multiple rows are returned: | + | We also get an exception if multiple rows are returned: |
? sql`SELECT id FROM users`.singleton() | ? sql`SELECT id FROM users`.singleton() | ||
# problem: multiple rows returned by "SELECT id FROM users" | # problem: multiple rows returned by "SELECT id FROM users" | ||
- | + | ||
- | A default can be provided if there are no rows: | + | A default can be provided if there are no rows: |
- | + | ||
? sql`SELECT id FROM users WHERE userName='david'`.singleton(fn {-1}) | ? sql`SELECT id FROM users WHERE userName='david'`.singleton(fn {-1}) | ||
# value: -1 | # value: -1 | ||
Line 79: | Line 71: | ||
# problem: multiple rows returned by "SELECT id FROM users" | # problem: multiple rows returned by "SELECT id FROM users" | ||
- | Internally, we're constructing PreparedStatement objects, not doing string interpolation: | + | Internally, we're constructing PreparedStatement objects, not doing string |
+ | interpolation: | ||
? def query := "SELECT id FROM users" | ? def query := "SELECT id FROM users" | ||
Line 93: | Line 86: | ||
? sql`SELECT id FROM users WHERE karma < 0`.singleton() | ? sql`SELECT id FROM users WHERE karma < 0`.singleton() | ||
# value: [3] | # value: [3] | ||
+ | |||
+ | ? sql`INSERT INTO users (userName, karma) VALUES ('$$@@', -6)` | ||
+ | ? sql`SELECT userName FROM users WHERE karma = -6`.singleton() | ||
+ | # value: ["$@"] | ||
As a convenience, we can get all the results as a list too: | As a convenience, we can get all the results as a list too: | ||
Line 99: | Line 96: | ||
# value: [[0, 1], [1, 0], [2, 0]] | # value: [[0, 1], [1, 0], [2, 0]] | ||
- | + | See [http://labs.it-innovation.soton.ac.uk/index.php?option=com_content&view=article&id=74:safe-database-access-in-e&catid=36:experiments Safe database access in E] | |
- | + | ||
- | + | ||
- | + | ||
- | + |
Revision as of 11:06, 9 June 2010
Examples of using SQL from E can be found in the sql.updoc file. Here is an extract:
Create an in-memory relational database using HSQLDB (http://hsqldb.org/):
? def driver := <unsafe:org.hsqldb.makejdbcDriver>() ? def connection := driver.connect("jdbc:hsqldb:mem:test", null)
(replace "mem" with "file" to store the database on disk, or replace "hsqldb" to connect to a full database server)
Wrap it with an SQL quasi-parser:
? def makeSql__quasiParser := <import:org.erights.e.tools.database.makeSQLQuasiParser> ? def sql__quasiParser := makeSql__quasiParser(connection) # value: <sql__quasiParser>
Create a test table:
? sql`CREATE TABLE users ( > id BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY, > userName VARCHAR(256) NOT NULL, > created DATETIME DEFAULT NOW NOT NULL, > karma BIGINT NOT NULL, > comments VARCHAR(256) DEFAULT NULL)`
Insert some values:
? for name in ["alice", "bob", "carol"] { > sql`INSERT INTO users (userName, karma) VALUES ($name, 0)` > }
Update one row:
? sql`UPDATE users SET karma=1 WHERE userName='alice'` # value: 1
Read all the values out:
? for [name, karma] in sql`SELECT userName, karma FROM users ORDER BY id ASC` { > println(`$name => $karma`) > } # stdout: alice => 1 # bob => 0 # carol => 0 #
Get out a single row:
? def lookupUser(name :String) :int { > def [id] := sql`SELECT id FROM users WHERE userName=$name`.singleton() > return id > } ? lookupUser("bob") # value: 1 ? lookupUser("fred") # problem: no rows returned by "SELECT id FROM users WHERE userName=?" We also get an exception if multiple rows are returned: ? sql`SELECT id FROM users`.singleton() # problem: multiple rows returned by "SELECT id FROM users" A default can be provided if there are no rows: ? sql`SELECT id FROM users WHERE userName='david'`.singleton(fn {-1}) # value: -1
But this is still an error:
? sql`SELECT id FROM users`.singleton(fn {-1}) # problem: multiple rows returned by "SELECT id FROM users"
Internally, we're constructing PreparedStatement objects, not doing string interpolation:
? def query := "SELECT id FROM users" ? sql`${query}` # problem: <SQLException: unexpected token: ?>
This means we don't have to worry about quoting:
? def ono := "O'No! $@?" ? sql`INSERT INTO users (userName, karma) VALUES ($ono, -5)` # value: 1 ? sql`SELECT id FROM users WHERE karma < 0`.singleton() # value: [3] ? sql`INSERT INTO users (userName, karma) VALUES ('$$@@', -6)` ? sql`SELECT userName FROM users WHERE karma = -6`.singleton() # value: ["$@"]
As a convenience, we can get all the results as a list too:
? sql`SELECT id, karma FROM users WHERE karma >= 0 ORDER BY id ASC`.asList() # value: [[0, 1], [1, 0], [2, 0]]