exec

Example: Create table:

public static int main (string[] args) {
	Sqlite.Database db;
	string errmsg;

	// Open/Create a database:
	int ec = Sqlite.Database.open ("test.db", out db);
	if (ec != Sqlite.OK) {
		stderr.printf ("Can't open database: %d: %s\n", db.errcode (), db.errmsg ());
		return -1;
	}


	string query = """
		CREATE TABLE User (
			id		INT		PRIMARY KEY		NOT NULL,
			name	TEXT					NOT NULL
		);
		""";

	// Execute our query:
	ec = db.exec (query, null, out errmsg);
	if (ec != Sqlite.OK) {
		stderr.printf ("Error: %s\n", errmsg);
		return -1;
	}

	print ("Created.\n");

	return 0;
}

valac --pkg sqlite3 create-table.vala

Example: Insert:

public static int main (string[] args) {
	Sqlite.Database db;
	string errmsg;

	// Open a database:
	int ec = Sqlite.Database.open ("test.db", out db);
	if (ec != Sqlite.OK) {
		stderr.printf ("Can't open database: %d: %s\n", db.errcode (), db.errmsg ());
		return -1;
	}

	// Required table:
	//	CREATE TABLE User (
	//		id		INT		PRIMARY KEY		NOT NULL,
	//		name	TEXT					NOT NULL
	//	);


	// Insert: (See Sqlite.Database.prepare)
	string query = """
		INSERT INTO User (id, name) VALUES (1, 'Hesse');
		INSERT INTO User (id, name) VALUES (2, 'Frisch');
		""";
	ec = db.exec (query, null, out errmsg);
	if (ec != Sqlite.OK) {
		stderr.printf ("Error: %s\n", errmsg);
		return -1;
	}

	int64 last_id = db.last_insert_rowid ();
	print ("Last inserted id: %" + int64.FORMAT + "\n", last_id);

	return 0;
}

valac --pkg sqlite3 insert.vala

Example: Select:

private static int exec_callback (int n_columns, string[] values, string[] column_names) {
	for (int i = 0; i < n_columns; i++) {
		print ("%s: %s\n", column_names[i], values[i]);
	}
	return 0;
}

public static int main (string[] args) {
	Sqlite.Database db;
	string errmsg;

	// Open a database:
	int ec = Sqlite.Database.open ("test.db", out db);
	if (ec != Sqlite.OK) {
		stderr.printf ("Can't open database: %d: %s\n", db.errcode (), db.errmsg ());
		return -1;
	}

	// Required table:
	//	CREATE TABLE User (
	//		id		INT		PRIMARY KEY		NOT NULL,
	//		name	TEXT					NOT NULL
	//	);

	// Example data:
	//  INSERT INTO User (id, name) VALUES (1, 'Hesse');
	//  INSERT INTO User (id, name) VALUES (2, 'Frisch');

	// Insert:
	string query = "SELECT * FROM User";
	ec = db.exec (query, exec_callback, out errmsg);
	if (ec != Sqlite.OK) {
		stderr.printf ("Error: %s\n", errmsg);
		return -1;
	}

	// Outpu: (see exec_callback)
	//  ``id: 1``
	//  ``name: Hesse``
	//  ``id: 2``
	//  ``name: Frisch``

	return 0;
}

valac --pkg sqlite3 select.vala

Example: Update:

public static int main (string[] args) {
	Sqlite.Database db;
	string errmsg;

	// Open a database:
	int ec = Sqlite.Database.open ("test.db", out db);
	if (ec != Sqlite.OK) {
		stderr.printf ("Can't open database: %d: %s\n", db.errcode (), db.errmsg ());
		return -1;
	}

	// Required table:
	//	CREATE TABLE User (
	//		id		INT		PRIMARY KEY		NOT NULL,
	//		name	TEXT					NOT NULL
	//	);

	// Required data:
	//  INSERT INTO User (id, name) VALUES (1, 'Hesse');
	//  INSERT INTO User (id, name) VALUES (2, 'Frisch');


	// Delete "Frisch" via id:
	string query = "UPDATE User SET name = 'Hermann' WHERE id = 1;";
	ec = db.exec (query, null, out errmsg);
	if (ec != Sqlite.OK) {
		stderr.printf ("Error: %s\n", errmsg);
		return -1;
	}

	int changes = db.total_changes ();
	print ("Affected rows: %d\n", changes);

	return 0;
}

valac --pkg sqlite3 update.vala

Example: Delete:

public static int main (string[] args) {
	Sqlite.Database db;
	string errmsg;

	// Open a database:
	int ec = Sqlite.Database.open ("test.db", out db);
	if (ec != Sqlite.OK) {
		stderr.printf ("Can't open database: %d: %s\n", db.errcode (), db.errmsg ());
		return -1;
	}

	// Required table:
	//	CREATE TABLE User (
	//		id		INT		PRIMARY KEY		NOT NULL,
	//		name	TEXT					NOT NULL
	//	);

	// Required data:
	//  INSERT INTO User (id, name) VALUES (1, 'Hesse');
	//  INSERT INTO User (id, name) VALUES (2, 'Frisch');


	// Delete "Frisch" via id:
	string query = "DELETE FROM User WHERE id = 2;";
	ec = db.exec (query, null, out errmsg);
	if (ec != Sqlite.OK) {
		stderr.printf ("Error: %s\n", errmsg);
		return -1;
	}

	int changes = db.total_changes ();
	print ("Affected rows: %d\n", changes);

	return 0;
}

valac --pkg sqlite3 delete.vala