Examples

test1.d

        
        import std.stdio;
        import std.string;
        
        import sdbo.odbc;
        
        int main(char[][] args)
        {
                
                char[] dsn;
                char[] sql;
                
                if(args.length < 3) {
                        writefln("Usage: test1 validDSN \"Select something from somewhere\"");
                        return 1;
                }

                dsn = args[1];
                sql = args[2];
                
                SqlResult result = (new OdbcEnvironment()).connect(dsn).execDirect(sql);
                
                if(!result.columnCount()) {
                        throw new Exception("Not a row source");
                }
                
                foreach(char[] column; result.columns())
                        writefln("column %s is of type %s and size %d", 
                                                result.columnSpec(column).name, result.columnSpec(column).type, result.columnSpec(column).csize);
                
                                                
                writefln("\nBinding all columns to char strings");
                result.setBinding(result.D_CHAR);
                
                writefln("\nFetching first row");
                
                if(result.fetch()) {
                        foreach(char[] column; result.columns()) {
                                printf("result.string(%s) = %s\n", toStringz(column), result.stringz(column));
                }
                } else {
                        writefln("Row source is empty.");
                }

                version(Windows) {
                        writefln("\nBinding all columns to wchar strings");
                        result.setBinding(result.D_WCHAR);
                }
                
                writefln("\nFetching second row");
                if(result.fetch()) {

                        foreach(char[] column; result.columns()) {

                                version(Windows)
                                        writefln("result.wstring(%s) = %s\n", column, result.wstring(column));
                                        
                                version(linux)
                                        printf("result.string(%s) = %s\n", toStringz(column), result.stringz(column));
                        }
                } else {
                        writefln("No more rows available.");
                }

                writefln("Any message?");
                if(result.lastMessage.recCount)
                        writefln(result.lastMessage.toString());
                else
                        writefln("no message");
                
                
                result.connection().environment().free();
                return 0;
                
        }

        

test2.d

private import std.string;
private import std.stdio;

import sdbo.odbc;

int main(char[][] args)
{
        odbcTest();     
        return 0;
}

void odbcTest()
{
        
        writefln("Creating environment...");
        OdbcEnvironment env = new OdbcEnvironment;
        
        version(Windows) {
                /* Windows:
                You have to create a Access Database test1.mdb in this file's directory for this program. 
                */
                writefln("Connecting to database test1.mdb...");
                OdbcConnection c = env.connectDriver("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=test1.mdb");
                
        } else {
                /* Linux: ODBC Driver Manager and MySQL ODBC driver required */
                
                writefln("Connecting to MySQL Server...");
                
                version(named_driver) {
                        // You have to make this work in odbc.ini odbcinst.ini files.
                        OdbcConnection c = 
                                env.connectDriver("DRIVER={MySQL};HOST=localhost;USER=root;PASSWORD=;");
                } else {
                        // Check the driver's path, hostname and user.
                        OdbcConnection c = 
                                env.connectDriver("DRIVER=/usr/lib/libmyodbc3.so;HOST=localhost;USER=root;PASSWORD=;");
                }
                
                
        }
                
        
        writefln("Connected! Connection string returned by driver: " ~ c.connectionString());
        
        SqlResult result;
        
        version(linux) {
                c.execDirect("create database if not exists _sdbo_teste");
                c.execDirect("use _sdbo_teste;");
        }
        
        
        try {
                
                writefln("Creating table TestCount with wrong syntax...");
                result = c.execDirect("create table TestCount ( counter int, label varRchar(50) )");
                
        } catch(SqlException e) {
                
                writefln("SQL Exception caught! Odbc diagnostics:");
                writefln(e.toString());
                writefln();
                
                char[] createtable = "create table TestCount ( counter_id int, counter_label varchar(50) )";
                
                try {
                        
                        writefln("Creating table TestCount with correct syntax...");
                        result = c.execDirect(createtable);
                
                } catch(SqlException e) {
        
                        writefln("2nd SQL Exception caught! Odbc diagnostics:");
                        writefln(e.toString());
                        writefln();
                        
                        if(e.sqlState == "42S01") {
                                
                                writefln("Table already exists. Dropping and recreating...");
                                result = c.execDirect("drop table TestCount");
                                result.execDirect(createtable);
                        } else {
                                
                                writefln("Can't recover. Giving up...");
                                
                                version(Windows)
                                        writefln("Create a database test1.mdb in this directory if it doesn't exist.");
                                
                                delete env; /* c gets deleted as well */
                                
                                return;
                        }
                        
                        
                }
                
        } 
        
        writefln("Inserting 10 values into TestCount...");
        for(int i=0; i<10; i++)
                result.execDirect(
                        "insert into TestCount values (" ~ toString(i) ~ ", 'counter id is " ~ toString(i) ~ "')"
                        );
        
        writefln("Updating 2 rows");
        result.execDirect("update TestCount set counter_label = '1st updated row' where counter_id = 3");
        result.execDirect("update TestCount set counter_label = '2nd updated row' where counter_id = 7");
        
        writefln("Deleting 2 rows");
        
        /* You can query directly on the connection, but then you should append a
         * free() to make sure the destructor of the resulting SqlResult object gets 
         * called and frees the handle
         *
         * It is faster, however, to instanciate one single result object and query upon that.
         * Doing so causes existing buffers (row source type queries only) to be free'd and 
         * the hstmt to be closed and reexecuted.
         */
        
        c.execDirect("delete from TestCount where counter_id = 1").free();
        c.execDirect("delete from TestCount where counter_id = 5").free();
        
        writefln("Selecting on the table");
        
        result.execDirect("select * from TestCount order by counter_id");
        
        writefln("Got result. Columns:");
        
        foreach(char[] column; result.columns()) {
                writef("Column %s: ", column);
        
                ColumnSpec* cs = result.columnSpec(column);
                
                writefln("%s, size %d, digits %d, nullable %d, numeric type %d",
                        cs.type, cs.csize, cs.digits, cs.nullable, cs.numericType);
                
        }
        
        writefln("Setting bindings to string conversion for all columns");
        
        result.setBinding(result.D_CHAR);
        
        writefln("Fetching first row");
        result.fetch();
                
        
        printf("%s; ", result.stringz("counter_id"));
        printf("%s; ", result.stringz(1));
        
        writefln();

        writefln("Loop fetching all remaining rows");
        
        char* test = result.stringz(1);
        
        printf("TEST: %s\n", test);
        
        while(result.fetch) {
                for(int col = 0; col < result.columnCount; col++) {
                        
                        if(result.isNull(col))
                                writef("NULL; ");
                        else
                                printf("%s; ", result.stringz(col));
                }
                writefln();
        }
        printf("TEST: %s\n", test);
        

        writefln("Add a date type column...");
        
        result.execDirect("alter table TestCount add counter_date date");
        
        writefln("... set it to now using native sql string");
        
        version(Windows)
                result.execDirect("update TestCount set counter_date = date()");
        else
                result.execDirect("update TestCount set counter_date = now()");
                
                
        writefln("... and query again");
        result.execDirect("select * from TestCount order by counter_id");
        
        result.fetch();
        
        writefln("\nid: %d; ", result.vint("counter_id"));
        writefln("label: %s; ", result.string("counter_label"));
        writefln("date: %s; ", result.string("counter_date"));

        writefln("Binding id as string");
        result.setBinding("counter_id", result.D_CHAR);
        
        result.fetch();
        
        writefln("\nid: %d; ", result.string(0));
        writefln("label: %s; ", result.string(1));
        writefln("date: %s; ", result. string(2));

        writefln("Any message?");
        if(result.lastMessage.recCount)
                writefln(result.lastMessage.toString());
        else
                writefln("no message");

        
        writefln("Cleanup");
        
        delete env; // This deletes ALL objects derived from this environment.
}

test3.d

private import std.string;
private import std.stdio;

import sdbo.odbc;

void main()
{
        
        version(Windows) {
                char[] constr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=test1.mdb";
        }
        else {
                char[] constr = "DRIVER=/usr/lib/libmyodbc3.so;HOST=localhost;USER=root";
        } 

        OdbcEnvironment env = new OdbcEnvironment;
        OdbcConnection c = env.connectDriver(constr);
        
        createTestTable(c, "create table TestCount ( 
                                                                counter_id int, 
                                                                counter_label varchar(50),
                                                                counter_date date )"
                                                );
        
        
        SqlResult result = c.newResult();
        
        /* Inserting values with a prepared statement 
         *
         * This will store the same data as 
         *
         * result.execDirect("insert into TestCount values(10, 'ID set to 10', '2010-10-20')")
         * result.execDirect("insert into TestCount values(20, 'ID set to 20', '2020-10-20')")
         * result.execDirect("insert into TestCount values(30, 'ID set to 30', '2030-10-20')")
         *
         * but execution time gets much better when using prepared statements.
         *
         */
        
        result.prepare("insert into TestCount values(?, ?, ?)");
        
        for(int i=1; i < 10; i++) {
                
                result.setInput(0, 10 * i); 
                result.setInput(1, "ID set to " ~ toString(10 * i));
                result.setInput(2, "20" ~ toString(10 * i) ~ "-10-20");
                
                result.execute();
                
        }
        
        result.setInput(0, 990); 
        result.setInput(1, "something different");
        result.setInput(2, "1999-3-4");
        
        result.execute();
        
        result.setInput(0, 991); 
        result.setInput(1, "more than 50 characters 
                            more than 50 characters 
                            more than 50 characters 
                            more than 50 characters");
        result.setInput(2, "1999-3-4");
        
        /* column 2 will be truncated but the insert goes */
        result.execute();
        
                
        result.execDirect("select * from TestCount order by counter_id");
        result.setBinding(result.D_CHAR);
        
        while(result.fetch) {
                writef("Fetch: ");
                foreach(char[] column; result.columns())
                        printf("%s = %s; ", toStringz(column), result.stringz(column));
                writefln();
        }
        
        
        delete env;
        
}

void createTestTable(DBCon c, char[]createtable)
{
        
        SqlResult result = c.newResult();
        
        version(linux) {
                result.execDirect("create database if not exists _sdbo_teste");
                result.execDirect("use _sdbo_teste;");
        }
        
        try {
                result = c.execDirect(createtable);
        } catch(SqlException e) {
                
                if(e.sqlState == "42S01") {
                        result = c.execDirect("drop table TestCount");
                        result.execDirect(createtable);
                } else {
                        
                        writefln("Can't recover. Giving up...");
                        writefln("Create a database test1.mdb in this directory if it doesn't exist.");
                        c.environment.free();
                        throw e;
                }
        }
        
        delete result;
}

test4.d

import sdbo.odbc;

int main(char[][] args)
{

         
        OdbcEnvironment env = new OdbcEnvironment;
        
        version(Windows) {
                char[] constr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=test1.mdb";
        }
        else {
                char[] constr = "DRIVER=/usr/lib/libmyodbc3.so;HOST=localhost;USER=root";
        } 
        
        OdbcConnection c = env.connectDriver(constr);
        
        SqlResult r = c.newResult();
        r = c.newResult();
        r = c.newResult();
        
        delete r; /* two result objects remain undeleted */
        delete c; /* this deletes ALL objects derived from c (and frees all handles in the right sequence) */
        
        
        c = env.newConnection(); 
        c = env.newConnection(); /* lost reference to former object */
        
        delete env; /* this deletes ALL objects derived from env (and frees all handles in the right sequence) */
        
        //c.connect("foo"); /* access violation */
        
        /* at this point, all created objects are deleted */
        
        (new OdbcEnvironment).connectDriver(constr)
                .newResult();
        /* 3 objects created */
        
        
        /* the 3 objects and their handles will be deleted on exit or garbage collection */
        
        /* this function does nothing and returns silently */
        return 0;
}

test5.d

private import std.string;
private import std.stdio;

import sdbo.odbc;

void main()
{
        OdbcEnvironment env = new OdbcEnvironment;
        
        writefln("List of available Driver:");
        foreach(char[][2] driver; env.drivers())
                writefln("%s - %s", driver[0], driver[1]);
        
        writefln();
                
        version(Windows) {
                char[] constr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=test1.mdb";
        }
        else {
                char[] constr = "DRIVER=/usr/lib/libmyodbc3.so;HOST=localhost;USER=root";
        } 
        

        
        OdbcConnection c = env.connectDriver(constr);
        
        SqlResult result;
        
        char[] createtable = "create table TestCount ( counter_id int, counter_label varchar(50), counter_date date )";
        
        version(linux) {
                c.execDirect("create database if not exists _sdbo_teste");
                c.execDirect("use _sdbo_teste;");
        }
        

        try {
                result = c.execDirect(createtable);
        } catch(SqlException e) {
                
                if(e.sqlState == "42S01") {
                        result = c.execDirect("drop table TestCount");
                        result.execDirect(createtable);
                } else {
                        
                        writefln(e.toString());
                        writefln("Can't recover. Giving up...");

                        delete env;
                        return;
                }
        }
        
        version(Windows) {
                result.execDirect("insert into TestCount values (10, 'counter id is 10', date())");
        } else {
                result.execDirect("insert into TestCount values (10, 'counter id is 10', now())");
        }
                
        result.execDirect("select * from TestCount order by counter_id");
        
        result.bindAll = false;
        result.setBinding(2, result.D_CHAR);
        
        result.fetch();
        
        writefln("display a bound column");
        writefln("string counter_date: %s", result.string(2)); 
        /* column 2 is bound */
        
        writefln("retrieving unbound columns with longData and stringData");
        
        writefln("long counter_id: %d", result.longData(0)); 
        /* column 0 is unbound */
        
        writefln("string counter_label: %s", result.stringData("counter_label")); 
        /* column counter_label is unbound */
        
        
        try {
                /* using getdata twice or out of order might cause a SqlException or return invalid data,
                   depending on the driver */
                   
                writefln("long counter_id: %d", result.longData(0)); 
        } catch(SqlException e) {
                e.print();
        }
        
        delete env;
        
}

---