/+
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;

alias dbapi.namedParameter   np;
alias dbapi.unboxNullable    unboxN;
alias dbapi.unboxNullDefault unboxD;
alias dbapi.isNull           isNull;

void main () {

   auto c = sqlite3.connect ("MyDb.db");

   auto cu = c.cursor ();

   /* 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))");

   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");

   outputDescription (cu);

   /* If no nulls are possible, then you can use unbox directly.
    */
   Box[][] values = cu.fetchall ();
   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]));
      }
   }

   cu.execute ("insert into atable values (?,?,?)", null, null, null);

   cu.execute ("select * from atable");

   /* If nulls are expected, then you can use unboxNullable or unboxNullDefault.
    */
   values = cu.fetchall ();
   if (values is null) {
      writefln ("Nothing returned");
   } else {
      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.close ();
   c.close ();
}


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);
   }
}