#moonsqls

moon add moonbitstack/moonsqls

async fn run() -> Unit raise {
  let db = @moonsqls.SqlServerConn::connect(
    "127.0.0.1", 1433, "sa", "your-password", "master",
  )
  db.execute("CREATE TABLE hero (id INT PRIMARY KEY, name NVARCHAR(64))", []) |> ignore
  db.execute("INSERT INTO hero VALUES (?, ?)", [@moondb.Int(1), @moondb.Text("Nova")])
  |> ignore
  let rows = db.query("SELECT name FROM hero WHERE id = ?", [@moondb.Int(1)])
  println(rows[0].text_by("name")) // Nova
  db.close()
}

#What it speaks

prelogin  → encryption negotiated in the same packet as the login
    ↓         TLS handshake carried inside prelogin packets
login7    → credentials, the database, and the 7.4 extension block,
    ↓         or TDS 8.0 with TLS already under the whole session
batch/RPC → one TDS packet per message, wrapped in TLS once it is on
    ↓
tokens    → COLMETADATA, ROW, NBCROW, DONE, ERROR, ENVCHANGE, RETURNVALUE

• Encryption is negotiated before the login, and the handshake runs inside prelogin packets. Afterwards the arrangement flips: whole TDS packets — headers included — are the plaintext inside TLS. A driver that encrypts only payloads connects, gets a completed handshake, and then has the server close the socket without a message.
• The transaction descriptor has to be carried. BEGIN TRANSACTION hands out eight bytes in an ENVCHANGE, and every later request must repeat them; the server answers error 3989 otherwise.
• Parameters travel through sp_executesql, by name. ? becomes @p1, the values go as named RPC parameters beside the statement, and the statement's own two parameters are @statement and @params.
• A message may not be sealed into one TLS record. The server reads a record at a time, so two TDS packets in one record leave it waiting for the rest of the first and it closes the connection without a message. Each packet is sealed and sent on its own.

#Prepared statements

#Bulk copy

let copied = conn.bulk_copy("hero", ["id", "name"], [
  [@moondb.Int(1), @moondb.Text("Nova")],
  [@moondb.Int(2), @moondb.Text("Ivan")],
])

#TDS 8.0

#Type mapping

#What the session sets for itself

#Tested against a real server

#Not covered

• MARS (multiple active result sets) — the option is declined in prelogin, so one statement at a time per connection.
• XML columns and spatial types. They raise NotSupported rather than reading back as something they are not.
• DECIMAL in a bulk stream. A bulk column is written as one of the types the driver infers from the values; a decimal destination converts from the nvarchar(max) an exact numeric travels as, which is correct but not the narrowest form.
• Integrated (Windows) authentication. SQL authentication only.

#The TLS dependency is local, for now

#License