Standard library · Database

db/mysql

Connecting to MySQL and MariaDB.

import db.mysql

On this page

Types
Functions

This package implements the MySQL and MariaDB protocol directly and needs no outside library; when the server offers TLS the connection is encrypted. The example needs a running server, so it is checked and not run. For text outside ASCII, call SetCharset with utf8mb4 once you are connected.

Types

`Database`

struct Database

One MySQL connection.

Name	Type	Description
`handle`	`void*`	The pointer to the connection state.

db-mysql-Client.salam

import db
import db.mysql

func main:
    if not mysql.Available():
        println "the MySQL client is not available"
        ret
    end
    _ := mysql.LibraryInit()
    _ := mysql.ThreadInit()
    println mysql.ClientVersion()
    d := mysql.Open("127.0.0.1", "app", "s3cret", "shop", 3306)
    if not mysql.Ok(d):
        println mysql.Errno(d), mysql.Error(d)
        ret
    end
    println mysql.ServerVersion(d), mysql.Ping(d)
    _ := mysql.SetCharset(d, "utf8mb4")
    _ := mysql.SelectDB(d, "shop")
    name := mysql.Escape(d, "O'Brien")
    _ := mysql.Exec(d, "INSERT INTO customers (name) VALUES ('" + name + "')")
    println mysql.LastInsertId(d), mysql.AffectedRows(d), mysql.Changes(d)

    _ := mysql.Autocommit(d, false)
    _ := mysql.Begin(d)
    _ := mysql.Exec(d, "UPDATE products SET stock = stock - 1 WHERE id = 7")
    if mysql.QueryInt(d, "SELECT stock FROM products WHERE id = 7") < 0:
        _ := mysql.Rollback(d)
    else:
        _ := mysql.Commit(d)
    end

    mut result := mysql.Query(d, "SELECT id, name, price, note FROM products")
    if mysql.QueryOk(result):
        println mysql.ColumnCount(result), mysql.RowCount(result), mysql.ColumnName(result, 1)
        until mysql.Next(result):
            println mysql.Int(result, 0), mysql.Text(result, 1), mysql.Float(result, 2), mysql.Int64(result, 0), mysql.IsNull(result, 3)
        end
        mysql.Finish(result)
    end
    println mysql.QueryText(d, "SELECT VERSION()")

    c := mysql.Conn(d)
    println c.Kind(), db.QueryInt(c, "SELECT COUNT(*) FROM products")
    println d.Kind(), d.Ping(), d.Quote("x'y")
    rows := d.Query("SELECT name FROM customers")
    until rows.Next():
        println rows.Text(0)
    end
    _ := rows.Close()
    mysql.Close(d)
    mysql.ThreadEnd()
    mysql.LibraryEnd()
    _ := mysql.OpenFull("localhost", "app", "s3cret", "shop", 0, "/tmp/mysql.sock", 0 as u64)
end

`Kind`

func Kind(): str

The name of the database engine.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Ok`

func Ok(): bool

It is healthy.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Ping`

func Ping(): bool

The connection is alive.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Exec`

func Exec(sql: str): int

Runs one statement; zero means success.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Query`

func Query(sql: str): dyn db.Rows

Runs a query and returns the rows.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Begin`

func Begin(): dyn db.Tx

Starts a transaction.

The example for this item is shown with the ones above: db-mysql-Client.salam

`LastInsertId`

func LastInsertId(): i64

The id of the last inserted row.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Changes`

func Changes(): i64

The number of rows the last statement changed.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Error`

func Error(): str

The last error message.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Quote`

func Quote(s: str): str

Returns the string quoted and escaped, safe for SQL.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Close`

func Close(): int

Closes and frees it.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Result`

struct Result

The result of one query.

Name	Type	Description
`handle`	`void*`	The result pointer.
`row`	`void*`	The current row.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Ok`

func Ok(): bool

It is healthy.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Next`

func Next(): bool

Moves to the next row; false when no row is left.

The example for this item is shown with the ones above: db-mysql-Client.salam

`ColumnCount`

func ColumnCount(): int

The number of columns.

The example for this item is shown with the ones above: db-mysql-Client.salam

`ColumnName`

func ColumnName(col: int): str

The column name.

The example for this item is shown with the ones above: db-mysql-Client.salam

`ColumnType`

func ColumnType(col: int): int

The type of the column value in the current row, one of the TYPE_... constants in db.

The example for this item is shown with the ones above: db-mysql-Client.salam

`IsNull`

func IsNull(col: int): bool

The column value is null.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Text`

func Text(col: int): str

The column value as text.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Int`

func Int(col: int): int

The column value as an integer.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Int64`

func Int64(col: int): i64

The column value as a 64 bit integer.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Float`

func Float(col: int): f64

The column value as a float.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Close`

func Close(): int

Closes and frees it.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Txn`

struct Txn

One transaction.

Name	Type	Description
`handle`	`void*`	The connection pointer.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Ok`

func Ok(): bool

It is healthy.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Exec`

func Exec(sql: str): int

Runs one statement; zero means success.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Query`

func Query(sql: str): dyn db.Rows

Runs a query and returns the rows.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Commit`

func Commit(): int

Commits the transaction.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Rollback`

func Rollback(): int

Rolls the transaction back.

The example for this item is shown with the ones above: db-mysql-Client.salam

Functions

`Available`

func Available(): bool

The MySQL client is available; since it is inside Salam itself, this is always true.

The example for this item is shown with the ones above: db-mysql-Client.salam

`ClientVersion`

func ClientVersion(): str

The name and version of the client.

The example for this item is shown with the ones above: db-mysql-Client.salam

`ServerVersion`

func ServerVersion(db: Database): str

The server version.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Open`

func Open(host: str, user: str, pass: str, dbname: str, port: int): Database

Connects to the server.

The example for this item is shown with the ones above: db-mysql-Client.salam

`OpenFull`

func OpenFull(host: str, user: str, pass: str, dbname: str, port: int, socket: str, flags: u64): Database

Connects over a Unix socket and with connection flags.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Ok`

func Ok(db: Database): bool

The connection is up.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Close`

func Close(db: Database)

Closes the connection.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Ping`

func Ping(db: Database): bool

The connection is alive.

The example for this item is shown with the ones above: db-mysql-Client.salam

`SelectDB`

func SelectDB(db: Database, name: str): int

Changes the current database.

The example for this item is shown with the ones above: db-mysql-Client.salam

`SetCharset`

func SetCharset(db: Database, charset: str): int

Sets the character encoding; utf8mb4 for text outside ASCII.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Error`

func Error(db: Database): str

The last error message.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Errno`

func Errno(db: Database): int

The code of the last error.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Exec`

func Exec(db: Database, sql: str): int

Runs one statement; zero means success.

The example for this item is shown with the ones above: db-mysql-Client.salam

`AffectedRows`

func AffectedRows(db: Database): i64

The number of rows changed.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Changes`

func Changes(db: Database): i64

The same.

The example for this item is shown with the ones above: db-mysql-Client.salam

`LastInsertId`

func LastInsertId(db: Database): i64

The id of the last inserted row.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Begin`

func Begin(db: Database): int

Starts a transaction.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Commit`

func Commit(db: Database): int

Commits it.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Rollback`

func Rollback(db: Database): int

Rolls it back.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Autocommit`

func Autocommit(db: Database, enabled: bool): int

Turns committing every statement by itself on or off.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Query`

func Query(db: Database, sql: str): Result

Runs a query.

The example for this item is shown with the ones above: db-mysql-Client.salam

`QueryOk`

func QueryOk(res: Result): bool

The query gave a result.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Next`

func Next(res &: Result): bool

Moves to the next row.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Finish`

func Finish(res: Result)

Frees the result.

The example for this item is shown with the ones above: db-mysql-Client.salam

`ColumnCount`

func ColumnCount(res: Result): int

The number of columns.

The example for this item is shown with the ones above: db-mysql-Client.salam

`RowCount`

func RowCount(res: Result): i64

The number of rows.

The example for this item is shown with the ones above: db-mysql-Client.salam

`ColumnName`

func ColumnName(res: Result, col: int): str

The column name.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Text`

func Text(res: Result, col: int): str

The column value as text.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Int`

func Int(res: Result, col: int): int

As an integer.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Int64`

func Int64(res: Result, col: int): i64

As a 64 bit integer.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Float`

func Float(res: Result, col: int): f64

As a float.

The example for this item is shown with the ones above: db-mysql-Client.salam

`IsNull`

func IsNull(res: Result, col: int): bool

It is null.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Escape`

func Escape(db: Database, s: str): str

Escapes the special characters of a string for SQL; put the quotes around it yourself.

The example for this item is shown with the ones above: db-mysql-Client.salam

`QueryInt`

func QueryInt(db: Database, sql: str): int

The first column of the first row as an integer.

The example for this item is shown with the ones above: db-mysql-Client.salam

`QueryText`

func QueryText(db: Database, sql: str): str

The same, as text.

The example for this item is shown with the ones above: db-mysql-Client.salam

`LibraryInit`

func LibraryInit(): bool

Readies the client; kept for compatibility and nothing else is needed.

The example for this item is shown with the ones above: db-mysql-Client.salam

`LibraryEnd`

func LibraryEnd()

Ends the client; kept for compatibility.

The example for this item is shown with the ones above: db-mysql-Client.salam

`ThreadInit`

func ThreadInit(): bool

Readies the current thread.

The example for this item is shown with the ones above: db-mysql-Client.salam

`ThreadEnd`

func ThreadEnd()

Frees the resources of the thread.

The example for this item is shown with the ones above: db-mysql-Client.salam

`Conn`

func Conn(d: Database): dyn db.Connection

Returns the connection as a shared Connection.

The example for this item is shown with the ones above: db-mysql-Client.salam