On VFP and MySql ConnectionStrings

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.

MySql ODBC Options
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, with a link to the archived page above:

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;


Posted

in

,

by

Comments

10 responses to “On VFP and MySql ConnectionStrings”

  1. mpirringer Avatar
    mpirringer

    Good info. I just made it a habit to handle it on the VFP side with VAL()

    In regards to the ODBC connection. As I Blogged I found – especially over slow connection to get quite a boost on large recordsets by also setting “Use compression”, “Allow Large Record sets”, “Force Forward Only Cursors”

    There is another drastic perf. improvement if you set “Do not cache Forward only cursors” But you will not be able to use shared connections or more than 1 connection at a time then nor run the connection async or you get an “SQL out of Sequence” Error from the Driver. There are some bug reports on those and they hopefully get fixed in the future.

    With compression & fwd only cursurs you are talking getting data in 1/4 the time – with the cache disabled and compression the overall time is reduced to about 1/10. (on a 512k DSL line)

  2. rpupko Avatar
    rpupko

    I had “solved” the problem by casting the result as Decimal so it took me a couple of days to come back here.

    Thanks for the response. Good Info. I have a question, though. You posted in your blog

    Option = 16387

    but

    16384 = Treat BIGINT columns as INT columns

    Is your number a typo? If not, I couldn’t see what other options you had combined to reach that number.

    Again……thanks for your input.

  3. Eric Avatar

    The option is cumulative, like VFP’s MessageBox() parameters. So 16387 = 16384 (Treat BigInt as Int) + 2 (Return matched rows instead of affected rows) + 1 (I have no idea what this is).

    I also don’t know why 16384 works. But it looked like the most likely candidate so I tried it and it fixed my problem so….voila.

    Eric

  4. Prabir Avatar
    Prabir

    How can I run VFP-MySql programme in a client machine without installing MySql ODBC driver but keeping all MySql files in my programme folder.

  5. Eric Selje Avatar

    Prabir, you pretty much HAVE to install the ODBC drivers to get VFP to talk to MySql, even if it’s all on the local machine. I’ve heard of workarounds, but they’re not easy.

  6. B. Turner Avatar
    B. Turner

    Eric,

    It seems to me that – since there is no direct equivalent to BigInt in VFP, one option (the one selected) is convert the value to text.

    You can then convert the resulting text back into a numeric value after receiving it (using the VAL(c) function.

    Of course, that adds to processing time (on both ends), but does give you the capability of receiving records with a BigInt of greater than 4.2B (4,294,967,296 unsigned).

  7. balaclava Avatar

    Nice post

    question, though.

    Is it possible to
    Options, is it possible to set multiple options in the string?

  8. Eric Selje Avatar

    @Balaclava Yes, check out http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html#codbc-dsn-option-flags to see how to translate the options on the GUI into a value that you add to the connection string.

  9. Gil Gzz Avatar
    Gil Gzz

    Thats great tip!

    You can resolve issue SELECT SUM(field) and return CHAR?

    Regards
    Gilberto Gzz

  10. Matt Avatar
    Matt

    Gilberto Gzz:

    select sum(myNumber)/1

    will cast correctly.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.