|
QuickStart
A quick introduction on using the database interface
The interface at a glancePublic module methods Connection connect (
char[] dsn
, TransactionMode transactionMode = TransactionMode.AUTO_COMMIT
, TransactionType transactionType = TransactionType.DEFAULT)The Connection object bool commit () void rollback () bool close () Cursor cursor () The Cursor object Execute a SQL statement void execute (char[] operation, ...) Retrieve data from a query where the type is not known at time of compilation Box[] fetchone () Box[][] fetchmany (int size = -1) Box[][] fetchall () Retrieve data from a query where the type is known at time of compilation bool next () Row!(T) fetchrow(T...) () Row!(T)[] fetchmanyrows(T...) (int size = -1) Row!(T)[] fetchallrows(T...) () Retrieve data from a query into a structure bool next () T fetchstruct(T) () T[] fetchmanystructs(T) (int size = -1) T[] fetchallstructs(T) () Execute a parametrized SQL with a batch of values int[] executemany (char[] operation, ...) int[] executemany (char[] operation, Box[][] values) Connecting to a databaseThe connect module function creates a connection to a SQLite database. The dsn is the name of the database that will be opened. There are two special names that can be provided as a dsn, IN_MEMORY_DB and TMP_DB. The IN_MEMORY_DB will create an in-memory database and TMP_DB will create a database in the OS directory used for temporary files. The transaction modes and types are described here TransactionLogic. Executing a SQL statementAll SQL operation are performed within the context of a cursor. To create a cursor, call the cursor () method on the Connection instance returned by connect. To execute a SQL statement, call the execute method of the cursor. The first argument is the SQL statement. If the SQL statement has parameters (either positional '?' or named ': <name>'), the parameter values can be specified after the SQL statement. For positional parameters, the value is supplied but for named parameters, the value must be bound to the name using a NamedParameter structure. The NamedParameter structure is imported from dbapi.base. The SQL statement must never end with a semi-colon ';'. The interface does not support multiple SQL statements. ExamplesAll examples will be done on the following table Employee
Name varchar2(20) NOT NULL
EmpNum number(10) NOT NULL -- Equivalent of an int
Status varchar2(10) NULLThe cu variable contains a reference to a Cursor instance. Example 1: Execute an insert statement without parameterscu.execute ("insert into Employee values ('Myron', 1, 'ACTIVE')");Example 2: Execute an insert statement with position parameterscu.execute ("insert into Employee values (?,?,?)", "Myron", 1, "ACTIVE");Example 3: Execute an insert statement with named parametersalias dbapi.NamedParameter np;
cu.execute ("insert into Employee values (:name, :empnum, :status)", np("name","Myron"), np("empnum", 1), np("status","ACTIVE"));Example 4: Execute a query statementcu.execute ("select * from Employee");
cu.execute ("select * from Employee where name = 'Myron'");
cu.execute ("select * from Employee where name = ?", "Myron");
cu.execute ("select * from Employee where name = :name", np("name","Myron"));Fetching data returned from a queryFetching data where the type is not known at compile timeFetching one row: fetchoneThe fetchone method will fetch a row from the query result set into a Box array. The fetchone method will move the cursor position to the next available row. If the result set does not contain any more rows, then null is returned. For example, assume you have a table Employee and you want to list all the employees by name: SELECT
Name
, EmpNum
, Status
FROM
Employee
ORDER BY
NameTo retrieve the row where cu is the Cursor instance: Box[] row = cu.fetchone (); You must test that a row was returned: if (row is null) {
// no more rows so take appropriate action
} else {
// Do something with row
}Then you can read the values as (using helper functions from dbapi.helper): /* For employee name */
char[] employeeName = row.xstr (0);
/* For employee number */
int employeeNumber = row.xint (1);
/* For employee status, after checking if it is null */
if (!isNull (row[2])) {
char[] employeeStatus = row.xstr (2);
}The isNull method is imported from dbapi.base. There are a few different ways of working with boxes including the standard unbox, dbapi.base.unboxNullable, dbapi.base.unboxNullDefault, and the helper functions. Fetching more than one row: fetchmany and fetchallThe fetchmany and fetchall function as for fetchone except that they return an array of Box, one for each row retrieved. Once all rows are returned, the methods return null. The difference between fetchmany and fetchall is that the number of rows fetched can be restricted by fetchmany whereas fetchall will return all the remaining rows in the result set. Box[][] emps = cu.fetchall ();
/* Records were fetched when emps is not null.
*/
if (emps !is null) {
foreach (Box[] emp; emps) {
writefln (
"%s, %d, %s"
, emp.xstr (0)
, emp.xint (1)
, emp.xstr (2, "")
);
}
}Fetching data where the type is known at compile timeFetching one row: fetchrowThe fetchrow method will fetch a row from the query result set into a Row instance that has the values stored in an expression tuple. Unlike the fetchone method that returns null when the end of the result set is reached, fetchrow will throw an exception. Method fetchrow does not move the cursor pointer, to move the cursor pointer and test if a row is available, use the next method. Do not call fetchone straight after calling next as fetchone does move the cursor pointer and you will skip a record. If you want to mix paradigms, call next, then fetchrow, then you can call fetchone. What differentiates fetchrow from fetchone is that you specify the types of the returned fields; using template semantics. For example, assume you have a table Employee and you want to list all the employees by name: SELECT
Name
, EmpNum
, Status
FROM
Employee
ORDER BY
NameTo retrieve the row where cu is the Cursor instance: auto row = cu.fetchrow!(char[], int, char[]) (); Then you can read the values as: /* For employee name */
char[] employeeName = row.c[0];
/* For employee number */
int employeeNumber = row.c[1];
/* For employee status, after checking if it is null */
if (!row.nullFlag[2]) {
char[] employeeStatus = row.c[2];
}
Fetching more than one row: fetchmanyrows and fetchallrowsThe fetchmanyrows and fetchallrows function as for fetchrow except that they return an array of Row structs, one for each row retrieved. You must not call next for these methods as they call it themselves. Once all rows are returned, the methods return null just like for fetchmany and fetchall. The difference between fetchmanyrows and fetchallrows is that the number of rows fetched can be restricted by fetchmanyrows whereas fetchallrows will return all the remaining rows in the result set. /* What the auto below resolves to:
Row!(char[], int, char[])[] emps = cu.fetchallrows!(char[], int, char[]) ();
*/
auto emps = cu.fetchallrows!(char[], int, char[]) ();
/* Records were fetched when emps is not null.
*/
if (emps !is null) {
// do somthing with emps
}
Fetching data into a structureFetching one row: fetchstructSimilar to fetchrow, the fetchstruct method depends on next for cursor positioning. The difference from fetchrow is that the type passed in is a struct that has fields corresponding to the types expected from the database. Since D does not provide native reflection capability beyond RTTI, convention is used to populate the struct. The struct is populated in field order such that field 1 of the struct will be populated from column 1 of the query, field 2 from column 2 and so on. If the last field of the struct is a static size bool array, called nullFlag, and the size of the array is the number of fields other than nullFlag, the nullFlag array will be populated with the null state of each field where nullFlag0 is the null state of the first field, nullFlag1 is the state of the second field and so on. When nullFlag is true, the field is null. Method fetchstruct will create, populate and return the struct. For example, assume you have a table Employee and you want to list all the employees by name: SELECT
Name
, EmpNum
, Status
FROM
Employee
ORDER BY
NameTo retrieve the row where cu is the Cursor instance: struct Employee {
char[] name;
int empNum;
char[] status;
bool[3] nullFlag;
}
Employee emp = cu.fetchstruct!(Employee) ();Then you can read the values as: /* For employee name */
char[] employeeName = emp.name;
/* For employee number */
int employeeNumber = emp.empNum;
/* For employee status, after checking if it is null */
if (!emp.nullFlag[2]) {
char[] employeeStatus = emp.status;
}
It is up to the developer to ensure that the structs and queries are in sync as there is no way for the interface to check it. Fetching more than one row: fetchmanystructs and fetchallstructsAs for fetchmanyrows and fetchallrows but using fetchstruct to fetch each row and returning an array of value structs. Employee[] emps = cu.fetchallstructs!(Employee) ();
/* Records were fetched when emps is not null.
*/
if (emps !is null) {
// do somthing with emps
}Putting it all together in sample_01.d/+
Example sample_01.d - Example of how to use the dbapi library.
Copyright (C) 2007 Myron Alexander (myron.alexander...gmail.com)
Licence: Public domain
+/
import std.boxer;
import std.stdio;
import std.string;
import dbapi = dbapi.base;
import sqlite3 = dbapi.sqlite3;
import dbapi.helper;
alias dbapi.NamedParameter np;
alias dbapi.unboxNullable unboxN;
alias dbapi.unboxNullDefault unboxD;
alias dbapi.isNull isNull;
alias dbapi.BatchBuilder BatchBuilder;
void main () {
auto c = sqlite3.connect ("MyDb.db", sqlite3.TransactionMode.ON_MODIFY);
//auto c = sqlite3.connect ("MyDb.db", sqlite3.TransactionMode.AUTO_COMMIT);
//auto c = sqlite3.connect ("MyDb.db", sqlite3.TransactionMode.ALWAYS);
//auto c = sqlite3.connect ("MyDb.db", sqlite3.TransactionMode.USER);
scope (exit) c.close ();
auto cu = c.cursor ();
scope (exit) cu.close ();
//---------------------------------------------------------------------------
writefln ("\n=== Table Creation =========================================");
/* SQL statements must not end with a semi-colon; a multi-statement error
* will be generated if the semi-colon is used to terminate the statement.
*/
cu.execute (
"create table atable (id int, name varchar2 (20), "
"balance number (10,2))");
writefln ("\n=== Populate Table =========================================");
cu.execute ("insert into atable values (1,'First Person',0)");
cu.execute ("insert into atable values (?,?,?)", 2, "Second Person", 1);
cu.execute ("insert into atable values (?,?,?)", 3, "Third Person", 123.45);
cu.execute (
"insert into atable values (:id, :name, :balance)",
np("id",4), np("name","Fourth Person"), np("balance",999.99));
//---------------------------------------------------------------------------
cu.execute ("select * from atable");
writefln ("\n=== Table Description ======================================");
outputDescription (cu);
writefln ("\n=== select * from atable (no nulls) ========================");
/* If no nulls are possible, then you can use unbox directly.
*/
Box[][] values = cu.fetchall ();
printValues (values);
//---------------------------------------------------------------------------
writefln ("\n=== select * from atable where name = :name ================");
cu.execute (
"select * from atable where name = :name",
np("name", "Third Person"));
values = cu.fetchall ();
printValues (values);
//---------------------------------------------------------------------------
cu.execute ("insert into atable values (?,?,?)", null, null, null);
cu.execute ("select * from atable");
writefln ("\n=== select * from atable (nulls) ===========================");
/* If nulls are expected, then you can use unboxNullable or unboxNullDefault.
*/
values = cu.fetchall ();
foreach (Box[] val; values) {
writefln (
"%d, %s, %#.2f",
unboxD!(int)(val[0], -1), unboxD!(char[])(val[1], "<NULL>"),
unboxD!(double)(val[2], -1.0));
}
//---------------------------------------------------------------------------
cu.execute ("select * from atable");
writefln ("\n=== select * from atable (alt-nulls) =======================");
/* Alternatively, you can use the helper functions.
*/
values = cu.fetchall ();
foreach (Box[] val; values) {
writefln (
"%d, %s, %#.2f",
val.xint (0), val.xstr (1, "<NULL>"), val.xdbl (2, 0.00));
}
writefln ("------------------------------------------------------------");
foreach (Box[] val; values) {
writefln (
"%d, %s, %#.2f",
xint (val[0]), xstr (val[1], "<NULL>"), xdbl (val[2], 0.00));
}
//---------------------------------------------------------------------------
writefln ("\n=== Insert multiple ========================================");
int[] affectedValues = cu.executemany (
"insert into atable values (?,?,?)"
, boxArray (10, "Multi 1", 1.0)
, boxArray (11, "Multi 2", 2.0)
, boxArray (12, "Multi 3", 3.0)
, boxArray (13, "Multi 4", 4.0)
//, boxArray (14, "Multi 5", 5.0)
);
writefln ("------------------------------------------------------------");
writef ("Affected rows: [");
foreach (i; affectedValues) {
writef (" %d ", i);
}
writefln ("]");
writefln ("------------------------------------------------------------");
cu.execute ("select * from atable");
while ((values = cu.fetchmany (2)) !is null) {
foreach (Box[] val; values) {
writefln (
"%d, %s, %#.2f",
val.xint (0), val.xstr (1, "<NULL>"), val.xdbl (2, 0.00));
}
writefln (" ++++++++++++++++++++");
}
//---------------------------------------------------------------------------
writefln ("\n=== Insert multiple with BatchBuilder ======================");
BatchBuilder batch = new BatchBuilder (2, 3);
batch
.append (20, "Batch 1", 1.0)
.append (21, "Batch 2", 2.0)
.append (22, "Batch 3", 3.0)
.append (23, "Batch 4", 4.0)
.append (24, "Batch 5", 5.0);
affectedValues =
cu.executemany ("insert into atable values (?,?,?)", batch.toArray());
writefln ("------------------------------------------------------------");
writef ("Affected rows: [");
foreach (i; affectedValues) {
writef (" %d ", i);
}
writefln ("]");
writefln ("\n=== Retrieve using type aware fetch method =================");
cu.execute ("select * from atable");
while (cu.next ()) {
auto row = cu.fetchrow!(int, char[], double) ();
writefln ("%d, %s, %#.2f"
, row.nullFlag[0] ? 0 : row.c[0]
, row.nullFlag[1] ? "<NULL>" : row.c[1]
, row.nullFlag[2] ? 0.00 : row.c[2] );
}
writefln ("\n=== Retrieve using many rows method ========================");
cu.execute ("select * from atable");
{
dbapi.Row!(int, char[], double)[] rows;
while ((rows = cu.fetchmanyrows!(typeof(rows[0].c)) (3)) !is null) {
foreach (r; rows) {
writefln ("%d, %s, %#.2f"
, r.nullFlag[0] ? 0 : r.c[0]
, r.nullFlag[1] ? "<NULL>" : r.c[1]
, r.nullFlag[2] ? 0.00 : r.c[2] );
}
writefln (" ++++++++++++++++++++");
}
}
writefln ("\n=== Retrieve using all rows method =========================");
cu.execute ("select * from atable");
{
auto rows = cu.fetchallrows!(int, char[], double) ();
foreach (r; rows) {
writefln ("%d, %s, %#.2f"
, r.nullFlag[0] ? 0 : r.c[0]
, r.nullFlag[1] ? "<NULL>" : r.c[1]
, r.nullFlag[2] ? 0.00 : r.c[2] );
}
rows = cu.fetchallrows!(int, char[], double) ();
if (rows !is null) writefln ("\nERROR: fetchallrows left a row");
if (cu.rowAvailable) writefln ("\nERROR: fetchallrows left a row");
}
writefln ("\n=== Retrieve using struct method ===========================");
cu.execute ("select * from atable");
struct Atable {
int id;
char[] name;
double balance;
bool[3] nullFlag;
}
while (cu.next ()) {
Atable atable = cu.fetchstruct!(Atable) ();
writefln ("%d, %s, %#.2f"
, atable.nullFlag[0] ? 0 : atable.id
, atable.nullFlag[1] ? "<NULL>" : atable.name
, atable.nullFlag[2] ? 0.00 : atable.balance );
}
writefln ("\n=== Retrieve using many structs method =====================");
cu.execute ("select * from atable");
{
Atable[] atables;
while ((atables = cu.fetchmanystructs!(Atable) (4)) !is null) {
foreach (atable; atables) {
writefln ("%d, %s, %#.2f"
, atable.nullFlag[0] ? 0 : atable.id
, atable.nullFlag[1] ? "<NULL>" : atable.name
, atable.nullFlag[2] ? 0.00 : atable.balance );
}
writefln (" ++++++++++++++++++++");
}
}
writefln ("\n=== Retrieve using all structs method ======================");
cu.execute ("select * from atable");
{
auto atables = cu.fetchallstructs!(Atable) ();
foreach (atable; atables) {
writefln ("%d, %s, %#.2f"
, atable.nullFlag[0] ? 0 : atable.id
, atable.nullFlag[1] ? "<NULL>" : atable.name
, atable.nullFlag[2] ? 0.00 : atable.balance );
}
}
//---------------------------------------------------------------------------
writefln ("\n=== Commit =================================================");
c.commit ();
writefln ("\n=== Rollback ===============================================");
c.rollback ();
}
void outputDescription (sqlite3.Cursor cu) {
dbapi.ColumnAttributes[] caa = cu.description;
foreach (dbapi.ColumnAttributes a ; caa) {
writefln ("[%s, %s, %d, %d]", a.name, a.typeCode, a.precision, a.scale);
}
}
void printValues (Box[][] values) {
if (values is null) {
writefln ("Nothing returned");
} else {
foreach (Box[] val; values) {
writefln (
"%d, %s, %#.2f",
unbox!(int)(val[0]), unbox!(char[])(val[1]),
unbox!(double)(val[2]));
}
}
}
|
Sign in to add a comment
