Skip to content
NorscodeNorscode
NOEN

Store and retrieve data

ExampleBy the Norscode project

Create a table, write rows and read them back out — with the built-in database functions, and without one query per row.

The database functions live in the runtime itself, so you need no external driver. You open a file, run SQL, and close.

bruk src.kjerne.noformat som nf

funksjon start() -> heltall {
    la conn = db.open("notat.db")
    db.execute(conn, "CREATE TABLE IF NOT EXISTS notat (id INTEGER PRIMARY KEY, tekst TEXT NOT NULL);")

    la notatet = "Handle melk"
    db.execute(conn, "INSERT INTO notat (tekst) VALUES (" + nf.sql_text(notatet) + ");")

    la antall = db.query_int(conn, "SELECT COUNT(*) FROM notat;")
    skriv("Notater: " + tekst(antall))
    db.close(conn)
    returner 0
}

What happens here

db.open opens — or creates — a database file. The pattern of creating the table only if it does not exist lets the program be run again without failing because the table is already there. It is the pattern you want in anything that runs more than once.

Note the nf.sql_text around the value. Without it, a text containing a single quotation mark would break the SQL statement — at best with an error, at worst as an injection where someone else decides what the query does. The rule is the same as for HTML: never paste user data straight into structured text.

db.query_int is for queries that yield a single number. There is a corresponding db.query_text for a single text value. Both are thin, but cover surprisingly much when you combine them with SQL that does the work.

Fetch many rows in one query

la raa = db.query_text(conn, "SELECT group_concat(id || char(9) || tekst, char(10)) FROM notat;")
la rader = builtin.split(raa, "\
")
la i = 0
mens i < lengde(rader) {
    la felt = builtin.split(rader[i], "\	")
    hvis lengde(felt) == 2 { skriv(felt[0] + ": " + felt[1]) }
    i = i + 1
}

Why not a loop with one query per row

This is the most important habit to learn early. The obvious approach is to fetch the number of rows, and then fetch row zero, row one, row two. That gives one query against the database per row, and each of them has a fixed cost. With ten rows you notice nothing. With ten thousand it takes minutes.

The pattern above instead does all the work in SQL. The group_concat function joins the whole result into a single string, with a tab between the fields and a newline between the rows. Norscode gets back one string and splits it up. One query, no matter how many rows.

Be aware of one trap: if the text columns themselves can contain a tab or newline, you must scrub them out in the SQL before the concatenation, otherwise the splitting breaks. A nested replace that swaps both characters for spaces does the job.

Related

Back to the overview