Archive for August, 2010

pgLoader

One of the weaknesses of PostgreSQL compared to Oracle is the lack of a strong bulk loader utility like sqlldr. Natively Postgres offers only the COPY command, which does not include suppport for a bad file and reject log. EnterpriseDB have acknowledged the need for a better tool but it has not been a priority. There is, however, a Python based utility called pgloader available through pgFoundry that wraps COPY and provides a usable bulk loader. Using Python with Postgres requires psycopg2: for Windows an installable package is downloadable from Stickpeople,  for Ubuntu installation can performed with:

apt-get install tcllib libpgtcl-dev python-psycopg2

pgLoader itself is simply a collection of scripts in some folders so download and untar as follows:

wget http://pgfoundry.org/frs/download.php/2294/pgloader-2.3.2.tar.gz
tar zxvf pgloader-*.tar.gz

Ruby, Python & ODBC

I recently tried extracting data from an Access database using Ruby and ODBC.  The first challenge is that Windows ODBC drivers can be either 32 or 64-bit.  On a 64-bit host only the 64-bit configuration panel is present in the control panel, and the 32-bit panel must be accessed directly by invoking C:WindowsSysWOW64odbcad32.exe.  More seriously, however, the ruby-odbc home page only lists Win32 binaries compiled using Microsoft Visual C, but the popular RubyInstaller for Windows distribution is compiled with MinGW making the two effectively incompatible.

Contrast this with Python which has pyodbc downloads compatible with the canonical distribution from python.org, even if trailing the latest release.  For enterprise applications there is even a commercial ODBC driver supported by egenix.

Both Ruby and Python also have DBI interfaces, but Python DBI has been deprecated, and for Ruby the DBD-ODBC implementation relies on the same underlying ruby-odbc package.  For now it looks like I’ll be sticking with Python for ODBC.

OpenSolaris is Dead

Looks like concerns about Oracle’s Sun acquisition are being fulfilled.  After suing Google using a loophole in the Java license and losing James Gosling Oracle has now cut back significantly on the openness of Solaris development.  Whether or not this was a good business decision this is surely damaging to Oracle’s reputation amongst supporters of FOSS, and great news for Microsoft.  Like MySQL and MariaDB there is already now a community effort sponsored by Nexenta to fork OpenSolaris outside Oracle.

[42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on … (-1907)

I ran into this error extracting data using ODBC from an Access database. All access control within Access had been turned off or removed, but still the problem persisted. I tried using ‘repair’ utilities but to no avail. Eventually I split the database, copying all tables and indexes into a separate ‘back-end’ .mdb file and that resolved the issue.