Our billing software runs on Windows platforms and uses ODBC to store its data. Recently we called the makers of that software since we wanted to extract some Business Intelligence from our records.
It turned out that they where going to release a new program for that purpose. We asked to evaluate the beta version and to my surprise it just created an Microsoft Excel file with a dynamic table report.
So I worked on the needed SQL query to extract the records we wanted and exported the data into a CSV to be loaded in an Excel worksheet with a dynamic table report. It worked ok, however the sales and marketting people didn’t want to contact a technician every time they needed the reports to be updated.
The problem was that our application server is a Debian box and since we didn’t have to much time to expend on this, we looked for the easiest solution. I found ODBC Socket Server which seems unmaintained but the last version works ok on our Windows Server 2003 machine. It’s a service which acts as a proxy for ODBC databases in the local machine, so they can be accessed from other machines using a socket connection and a very simple XML grammar. Once everything was in place I wrote a simple PHP program to fetch the needed records (a few thousand rows). To my surprise the available PHP libraries to interact with ODBC Socket Server didn’t perform too well. They stored the full response in memory and then build up an array, since we need a few thousend rows, the amount of memory required was too high for our little Debian system.
The following code is a pretty simple set of classes to work with ODBC Socket Server. They fetch the data on demand, using a SAX type xml parser to discard the already processed records. It’s not a really thought of or tested solution, however I’m making it public since it’s far better than existing solutions.