Explaining my conquest of the jdbc-odbc Bridge for Dooz!
Posted: Tue Jan 31, 2012 6:12 pm
So a while back I was writing a program for an old employer and had to learn how to use the jdbc-odbc bridge to access Microsoft Office databases from Java. I kept seemingly randomly getting "SQL Exception - Invalid Buffer Length" errors whilst trying to read from the database. I have made it my mission to explain the nature of this error to Doozie (because I am a nerd).
My first attempt was to equate the bit parser that was generating the error to an equivalent situation in musical note reading. She immediately said "too many numbers, didn't read". My nerdy-sense was tingling so I decided not to give up in my pursuit of a real world bug equivalent. I came up with a new extended metaphor, it doesn't translate perfectly and it isn't "real world", but it is alright.
You are no longer a computer reading 0's and 1's, nor are you a musician reading notes. You are now a builder.... in MINECRAFT! Instead of building a jdbc-odbc BRIDGE, we're going to have you build a STAIRCASE. Your goal is to make a staircase that will allow you to reach a height of 8 blocks. You have 6 full blocks and 2 half-blocks to work with, and you CANNOT jump any higher than 1 block (and for the purposes of this explanation you can't combine the half blocks).
As seen below, if you place the half blocks anywhere in the middle of the staircase you'll fail, because you'd have to jump up 1.5 blocks to the next step (top left). The best solution is to use the half blocks as the final two steps in staircase allowing you to hop up to your 8 block goal height at the end (bottom right).
The problem with my programming was that I didn't know that I was limited to taking full steps (and that the driver wouldn't account for taking halfsteps) so I was just putting the steps in whilly nilly. Once I realized what the problem was, I was able to reorganize my code to put all the 32 bit steps at the end of my attempts!
(Detailed explanation below)
/Analogy
The problem I was encountering was that I didn't know that the jdbc-odbc bridge was limited to taking full block (64 bit) steps. So I would try to read an int (32 bit) from the database at the beginning and wouldn't get any further because the driver buffer (step) would become unaligned (off by a half step or 32 bits).
Through trial and error I found that I could introduce a new random dummy variable before trying to read from the database (int dummyVar = 0;) and it would magically work. Sometimes I needed the dummy variable, sometimes I didn't... was too annoyed to try to determine why, so I just moved on. Turns out the introduction of the new 32 bit dummy var was offsetting the top of the memory stack by 32 bits, doing something a bit like what is shown in the pic on the upper right.
Another fix I encountered by randomly messing with stuff was that if I turned the ints (32 bit halfblocks) in the database into longs (64 bit full blocks) the program ran fine (bottom left). At this point I really should have realized what the problem was, but I was in a hurry and working with tools I'd never used before.
These solutions lead to the program working flawlessly on my 64 bit machine, and since the client was using a 32 bit system (reads in half steps only) it never came up again... until last week when I had to install it on a new 64 bit computer they had purchased. The need for the 32 bit offsetting dummy variable varies from system to system so all of a sudden a bunch of the functions in the program were throwing exceptions again. At this point my laziness was overcome by my OCD and I sprung into action (googled the error for 5 minutes). Now I know that as long as I do my int readings at the end of the result set then my program wont be effected by Oracle's buggy-ass jdbc-odbc bridge!
https://forums.oracle.com/forums/thread ... ID=2316875
My first attempt was to equate the bit parser that was generating the error to an equivalent situation in musical note reading. She immediately said "too many numbers, didn't read". My nerdy-sense was tingling so I decided not to give up in my pursuit of a real world bug equivalent. I came up with a new extended metaphor, it doesn't translate perfectly and it isn't "real world", but it is alright.
You are no longer a computer reading 0's and 1's, nor are you a musician reading notes. You are now a builder.... in MINECRAFT! Instead of building a jdbc-odbc BRIDGE, we're going to have you build a STAIRCASE. Your goal is to make a staircase that will allow you to reach a height of 8 blocks. You have 6 full blocks and 2 half-blocks to work with, and you CANNOT jump any higher than 1 block (and for the purposes of this explanation you can't combine the half blocks).
As seen below, if you place the half blocks anywhere in the middle of the staircase you'll fail, because you'd have to jump up 1.5 blocks to the next step (top left). The best solution is to use the half blocks as the final two steps in staircase allowing you to hop up to your 8 block goal height at the end (bottom right).
The problem with my programming was that I didn't know that I was limited to taking full steps (and that the driver wouldn't account for taking halfsteps) so I was just putting the steps in whilly nilly. Once I realized what the problem was, I was able to reorganize my code to put all the 32 bit steps at the end of my attempts!
(Detailed explanation below)
/Analogy
The problem I was encountering was that I didn't know that the jdbc-odbc bridge was limited to taking full block (64 bit) steps. So I would try to read an int (32 bit) from the database at the beginning and wouldn't get any further because the driver buffer (step) would become unaligned (off by a half step or 32 bits).
Through trial and error I found that I could introduce a new random dummy variable before trying to read from the database (int dummyVar = 0;) and it would magically work. Sometimes I needed the dummy variable, sometimes I didn't... was too annoyed to try to determine why, so I just moved on. Turns out the introduction of the new 32 bit dummy var was offsetting the top of the memory stack by 32 bits, doing something a bit like what is shown in the pic on the upper right.
Another fix I encountered by randomly messing with stuff was that if I turned the ints (32 bit halfblocks) in the database into longs (64 bit full blocks) the program ran fine (bottom left). At this point I really should have realized what the problem was, but I was in a hurry and working with tools I'd never used before.
These solutions lead to the program working flawlessly on my 64 bit machine, and since the client was using a 32 bit system (reads in half steps only) it never came up again... until last week when I had to install it on a new 64 bit computer they had purchased. The need for the 32 bit offsetting dummy variable varies from system to system so all of a sudden a bunch of the functions in the program were throwing exceptions again. At this point my laziness was overcome by my OCD and I sprung into action (googled the error for 5 minutes). Now I know that as long as I do my int readings at the end of the result set then my program wont be effected by Oracle's buggy-ass jdbc-odbc bridge!
https://forums.oracle.com/forums/thread ... ID=2316875
