ThinkSQL ODBC Driver Source

2012-06-22 — Greg Gaughan

The ThinkSQL ODBC driver source code is now available here.

The driver is pure Delphi but the specification is C-based and very detailed. Memory handling and complex state transitions between client and server made this a large undertaking.

The Java JDBC specification was closely based on ODBC, but garbage collection made the JDBC driver much easier to implement.

It's worth mentioning that the Python DB API specification is tiny in comparison to the ODBC documents but led to a much simpler, smaller and more elegant driver codebase. The Python driver can do all the things that the ODBC driver can do, but with less than a tenth of the code. It is more readable and maintainable and less fragile, and easier to use. And it actually works on multiple platforms. The surprising thing was how much simpler Python made the low-level network communications (mostly thanks to the struct module), especially considering Java was originally designed for low-level systems. Compare this Python method with the Java and Delphi ones:

The Python version

    def getpUCHAR_SWORD(self):
        if self.bufferPtr + _sizeof_short > self.bufferLen:
            if self.bufferPtr == self.bufferLen:
                self.read()
            else:
                return _fail

        s = self.buffer.read(_sizeof_short)
        self.bufferPtr += _sizeof_short
        si=struct.unpack('<H', s)[0]
        self.bufferPtr += si
        return self.buffer.read(si)

The Java version

    public String getpUCHAR_SWORD() {
        if (bufferPtr + Global.sizeof_short > bufferLen) {
            if (bufferPtr == bufferLen) {
                Read();
            }
            else {  //the buffer is not quite empty
                return Global.failString;
            }
        }
        short usi = 0;
        for (int siz = Global.sizeof_short - 1; siz >= 0; siz--) {
            usi <<= Global.sizeof_byte;
            short b = (short)buffer[bufferPtr + siz];
            if (b < 0) {b = (short)(b + 256);}
            usi = (short)(usi | b);  //i.e. reverse order
        }
        bufferPtr = bufferPtr + Global.sizeof_short;
        if (bufferPtr + usi > bufferLen) {
            if (bufferPtr == bufferLen) {
                Read();
            }
            else {  //the buffer is not quite empty
                return Global.failString;
            }
        }
        bufferPtr = bufferPtr + usi;
        return new String(buffer, bufferPtr - usi, (int)usi - 1);
    }

The Delphi (C-like) version

    function TMarshalBuffer.getpUCHAR_SWORD(
        var puc:pUCHAR;
        allocated:SWORD;
        var sw:SWORD
    ):integer;
    {RETURNS:
         ok,
         fail = probably means data is left in buffer,
                but not enough
                else errors as from Read
     Assumes:
         puc has 'allocated' space allocated by caller,
         unless -1 => allocate here (but caller must free!)
     Note:
         returns buffer data up to the allocated length
         (including 1 character for a null terminator)
    }
    const routine = ':getpUCHAR_SWORD';
    var actual:SWORD;
    begin
        if bufferPtr + sizeof(sw) > bufferLen then
        begin
            if bufferPtr = bufferLen then
            begin  //the buffer is empty
                result := Read;
                if result <> ok then exit;
            end
            else
            begin  //the buffer is not quite empty
                result := fail;  //buffer overflow
                exit;
            end;
        end;

        move(buffer[bufferPtr], sw, sizeof(sw));
        bufferPtr := bufferPtr+sizeof(sw);

        if bufferPtr + sw > bufferLen then
        begin
            if bufferPtr = bufferLen then
            begin  //the buffer is empty
                result := Read;
                if result <> ok then exit;
            end
            else
            begin  //the buffer is not quite empty
                result := fail;  //buffer overflow
                exit;
            end;
        end;

        if allocated = DYNAMIC_ALLOCATION then
        begin
            {Now allocate the space for the buffer data}
            getMem(puc, sw + sizeof(nullterm));
            allocated := sw + sizeof(nullterm);
        end;

        if (allocated - sizeof(nullterm)) < sw then
            actual := (allocated - sizeof(nullterm))
        else
            actual := sw;
        move(buffer[bufferPtr], puc^, actual);
        move(nullterm, (puc + actual)^, sizeof(nullterm));
        bufferPtr := bufferPtr + sw;
        result := ok;
    end; {getpUCHAR_SWORD}

Tags: Database, Python, Software