Artful Code

For the past few days have been spent trying to get the newLISP MySQL module to work with the 64-bit version of the libmysqlclient library. Here is what I did to get things working properly.

Updating paths

The file paths in the MySQL module only include the most common libmysqlclient paths. Update the files list to contain /usr/lib64 as well:

(set 'files '(
    "/usr/lib64/libmysqlclient.so" ; 64 bit Linux, UNIX
    "/usr/lib/libmysqlclient.so" ; Linux, UNIX
    "/usr/local/mysql/lib/libmysqlclient.dylib" ; MacOS X
))

If you are not sure where you libmysqlclient library is, you can find it with this command (assuming it is somewhere in /usr):

find /usr -name "libmysqlclient.so"

Updating alignments

The offsets for most structure fields are set at the beginning of mysql5.lsp. They need to be updated:

(constant 'NUM_ROWS_OFFSET (if big-endian 4 0))
(constant 'NUM_FIELDS_OFFSET 96)
(constant 'ERROR_OFFSET 141)
(constant 'INSERT_ID_OFFSET (if big-endian 836 832))
(constant 'AFFECTED_ROWS_OFFSET (if big-endian 828 824))

This is not necessarily the same on all systems. In the newLISP source tree is a folder named util. Compile sql.c and execute it to get the proper offsets. Note that the numbers generated are only one of the two used in this; you need to first determine the endianness of your system. The module itself uses this newLISP form:

(= (pack ">ld" 1) (pack "ld" 1))

If that returns true, update the first number and offset the other number by -4. Otherwise, update the second number and offset the first by 4 (except for NUM_FIELDS_OFFSET and ERROR_OFFSET).

The offsets for the MYSQL_FIELD structure fields are hard-coded in the functions fetch-row and keep-type.

In keep-type, update the line:

(set 'data (get-int (int (+ type_ptr (* 19 4)))))

…to:

(set 'data (get-int (int (+ type_ptr (+ (* 9 8) (* 10 4))))))

That reflects that there are 9 8-byte fields (all char* or ulong) and 10 4-byte fields (all uint) in the MYSQL_FIELD struct. That will keep the coerced types returned from queries straight.

Next, in fetch-row, the pointer to the field structure is found using:

(set 'field_addr (get-int (int (+ rdata (* field 4)))))

Change the 4 to 8:

(set 'field_addr (get-int (int (+ rdata (* field 8)))))

…or you will only get half the fields’ values :).

Data types

On the newLISP forum, Lutz pointed out that in fetch-row, the call to get-int must be changed to get-long for the 64-bit library. After a quick look at the types returned in the MySQL C api docs, I also updated num-rows, affected-rows and inserted-id.

Test it out

Load the updated module in the newLISP shell and run:

(test-mysql)