SaltyBlog

On VFP and MySql ConnectionStrings

Posted on 30. Nov, 2009 by Eric in Database, FoxPro

I’ve begun doing some preliminary data warehousing of VFP data into MySql in order to test the speed, similar to what I did a few posts back with Advantage Database Server. Since MySql can’t just use VFP tables the way Advantage can, I created a script to manually load the already extracted and transformed data into an actual MySql database. Thanks to VFP’s SQLPREPARE() and SQLEXEC() passthrough commands, it didn’t take too long to load the data (about 4 s. per campaign per day, which is about 250 records/s.).

But when it came time to check how quickly I could read back the data, I was befuddled by MySql’s insistence on returning my integer fields back as char(20). eg. If I try this

iHandle=SQLSTRINGCONNECT("Driver={MySQL ODBC 5.1 Driver};Server=xxxxx;Port=3306;Database=arc;User=xxxx;Password=xxxx;Option=3;",.t.)
SQLEXEC("SELECT 12345 AS id FROM arc_data", "crsData")

The id column will be a 20-digit character field.
If I try to force it with
SQLEXEC("SELECT CAST(12345 AS unsigned) AS id FROM arc_data", "crsData") or
SQLEXEC("SELECT CONVERT(12345, unsigned) AS id FROM arc_data", "crsData")

The result is still a 20-digit character field. There are other fields that come back this way too but this is the jist of my problem. [I'm using MySql Server 5.0.45, and I've tried this with ODBC drivers 3.51 and 5.1, both with the same result.]

On a whim, I tried using a DSN rather than a connection string. It didn’t work either, but at least I had access to all of the options. The one that first looked interesting was the one that said “Treat BIGINT columns like INT columns.” Hmmm…not exactly what I’m looking for but let’s try it.

It works perfectly now.

So how do we make that work with a connectionstring, my preferred method since I can keep those in a table and not have to distribute it to each machine? I suspect it has something to do with the OPTION= clause in the string, but I’ve never found that to be well documented. However this time when I googled around a bit I found this page, which perfectly laid out how the options in the DSN map to the OPTION= clause. Yay!

A quick change of code to add 16384 to my OPTIONS= mask…
iHandle=SQLSTRINGCONNECT("Driver={MySQL ODBC 5.1 Driver};Server=xxxxx;Port=3306;Database=arc;User=xxxx;Password=xxxx;Option=16387;",.t.)
SQLEXEC("SELECT 12345 AS id FROM arc_data", "crsData")

…and problem solved.

Here are the MySQL Options, copied here from the CodePowered blog page:

MySQL ODBC Driver 5.1 Connection String Options
In a MySQL connection string for ODBC, a parameter called OPTION can be specified to change the behavior of the server. It works as a bitmask, so to use it just take whatever flag you need from the following list and sum up the corresponding numbers, then put that as the value to the OPTION parameter.

2 = Return matched rows instead of affected rows
8 = Allow big result sets
16 = Don’t prompt when connecting
32 = Enable dynamic cursors
64 = Ignore schema in column specifications
128 = Disable driver-provided cursor support
256 = Don’t use setlocale()
512 = Pad CHAR to full length with space
1024 = Include table name in SQLDescribeCol()
2048 = Use compression
4096 = Ignore space after function names
8192 = Force use of named pipes
16384 = Treat BIGINT columns as INT columns
32768 = Disable catalog support
65536 = Read options from my.cnf
131072 = Enable safe options (see documentation)
262144 = Disable transaction support
524288 = Log queries to myodbc.sql
1048576 = Don’t cache results of forward-only cursors
2097152 = Force use of forward-only cursors
4194304 = Enable automatic reconnect
8388608 = Enable SQL_AUTO_IS_NULL
67108864 = Allow multiple statements
134217728 = Limit column size to signed 32-bit range
268435456 = Always handle binary function results as character data

For example if you want to enable ‘Return matched rows instead of affected rows’ and ‘Use compression’, the value to pass as OPTION is 2 + 2048 which is 2050:

Driver={MySQL ODBC 5.1 Driver};Option=2050;Server=localhost;Database=myDataBase;User=myUsername;Password=myPassword;

Tags: ConnectionString, MySql, ODBC, VFP