Posts Tagged ‘ODBC’

Using MySQL BLOB Data via ODBC in SSIS, SQL Server & Business Objects

Whilst trying to build a centralised cross-platform alerting system I spotted a peculiar issue when trying to move the output of a SHOW FULL PROCESSLIST command on MySQL via ODBC.  It seems that the output of the SHOW FULL PROCESSLIST command returns both integers and binary (BLOB) data types even though to they eye (that is, in the MySQL Query Browser) most of the columns appear to be short text fields.

Despite the fact that the data looks like text whenever I tried to return the data into an application, I tried SSIS, SQL Server Linked Servers and Business Objects, each time the data would come back unusable or an error would be returned.  Business Objects gave me the key by declaring “This is a BLOB.” as you can see in the following screenshots…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

As with my recent post about loading data into MySQL with SSIS the saviour turns out to be an ODBC configuration setting, this time in the Metadata tab of the MySQL ODBC driver.  All you have to do is check the “Always handle binary function results as character data” and instantly your problems will be solved…

Out of a crazy fit of completeness I also took screenshots of the final results and it’d be a shame to waste them so here they are…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

Loading Data into MySQL with SSIS via ODBC (sql_mode = ANSI)

I was performing a test earlier, a proof-of-concept to show that we could use Integration Services to move data from one platform (PostgreSQL) to another (MySQL) without SQL Server itself being involved at all. 

Unfortunately I hit a slight snag on the first simple attempt, I could read the data without a hitch but despite multiple attempts at tweaking SSIS settings and reconfiguring connections I could not manage to get data into the MySQL target table.  Amongst other messages in the Output window in BIDS I could see the following error…

[MySQL][ODBC 5.1 Driver][mysqld-5.0.45-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”tblDestination” (“col1″, “col2″, “col3″, “col4″, “col5″, “c’ at line 1

After some Googling I determined that the issue was down to an incompatibility between the SQL being generated by SSIS and the default MySQL engine, specifically that MySQL was not setup to accept ANSI compliant SQL.  Most of the solutions out there seemed to require that the entire server was configured in the ANSI compliant mode which was a problem for me as it’s a shared server and I can’t go reconfiguring it at will.  Thankfully MySQL provides the ability to set the mode on a per connection level with the following command…

SET SESSION sql_mode= 'ansi';

The trick is – how to get SSIS to run this before inserting the data without messing with the package itself?  The easiest way is to have the ODBC driver send the command at the point SSIS opens the connection, this can be configured in the settings of the ODBC source…

This is a relatively common feature of ODBC drivers and allows you to setup initial configuration options for this exact reason, now I know that I will not impact any other users of the server and my SSIS package works just fine.

Using 32-bit Task Scheduler + CScript on Windows 2008 R2

One of my primary data sources for Business Objects is a replicated pair of MySQL servers where I am asked by the DBAs to report against the slave however during maintenance replication can fall behind and reports that require up-to-date data will be incomplete.  Since we don’t live in an ideal world we can’t always plan our maintenance windows so I wrote a small VBScript routine that will detect the replication delay and if if it exceeds a threshold will change the ODBC source to point to the master. 

If you’ve caught my earlier article on 32-bit ODBC Drivers in Windows Server 2008 R2 you’ll know that there’s plenty of fun to be had since my ODBC drivers are 32-bit.  This means that I need to run the VBScript using the 32-bit version of CScript and the schedule it using the 32-bit Task Scheduler and once again the solution is to use the 32-bit tools provided in the SysWOW64 directory….

• C:\Windows\SysWOW64\cscript
• C:\Windows\SysWOW64\taskschd.msc

Beyond that you shouldn’t have too much trouble but if you do please leave a comment below with details and I’ll get back to you if I can help.

MySQL 32-bit ODBC Invalid Attribute String 64-bit Windows 7

Having just migrated my development PC to Windows 7 I’m slowly encountering perculiar issues as I setup all of the software and connections I used to have in Windows XP.  This morning I was trying to write a Business Objects report against a MySQL database and because Desktop Intelligence is a 32-bit application if I want it to talk to MySQL I have to use the 32-bit driver. 

I obtained the latest driver (5.1.7) from MySQL’s standard ODBC Connector page, installed it and added a System DSN without a hitch, the odd part came when Business Objects was returning only one row from a query that should return a couple of hundred.  Having run the same query on XP (I’m parallel running now) I suspected that the problem must be with the ODBC configuration so I attempted to delete the DSN only to receive the “Invalid attribute string” error…

A little Googling later led me to MySQL Bug #56233, in the discussion Fred Zappert frames the simplest solution suggested which is to uninstall version 5.1.7 and install version 5.1.6 instead.  Oddly MySQL don’t make it especially clear how to get hold of previous minor versions of the ODBC drivers but you can get it from here: mysql-connector-odbc-5.1.6-win32.msi 

Alternatively you could always visit bisql.net’s Tools, Utilities and ODBC Drivers page where I have a link for 5.1.6 which I’ll keep in place until the next Windows 7 compatible driver is released.

How to Enable 32-bit Runtime Mode in SSIS

If you’re just beginning to play with SSIS on a 64-bit box you’re pretty likely to hit a roadblock if your data sources have 32-bit ODBC drivers.  The error will become obvious immediately when you come to execute your package in BIDS (Business Intelligence Development Studio)…

The ODBC Driver Manager error reported in the Output window will be: ”The specified DSN contains an architecture mismatch between the Driver and Application”.  Thankfully this is a pretty easy one to sort out, all you have to do is…

1. Right-click on the Project name in the Solution Explorer and select Properties…

   

2. Choose “Debugging” in the left-hand pane…

   

3. Change the Run64BitRuntime value to False (by double-clicking on it). 

   

   For reference the help-text states that if the 64-bit SSIS runtime is not installed this setting is ignored so if you’re working on a 32-bit box in all liklihood you wouldn’t experience this issue. 

   

4. Click OK and re-run your package. 

32-Bit ODBC Drivers in Windows Server 2008 R2

Lately I’ve been taking advantage of my MSDN subscription (thanks to Ken Simmons‘ competition last month) and playing with some new technologies, initially experimenting with Windows Server 2008 R2. If you hadn’t heard prior to launch period, Microsoft made 2008 R2 64-bit only – probably the right decision to make but whilst the software industry is still making a transition from 32-bit to 64-bit there are bound to be some niggles here and there.

Being a database guy, one of the issues I noticed right away was that the ODBC Data Source Administrator accessible via Control Panel / Administrative Tools is the 64-bit version and can only be used to setup connections for 64-bit ODBC drivers. Not only was the 64-bit version missing the Postgres driver I had just installed, there were no drivers at all other than SQL Server…

It turns out that there are are two, entirely identical ODBC tools and the one that most of us will end up using initially (unless we’re lucky enough to have an all 64-bit architecture) is kept in the basement that is the c:\Windows folder. The 32-bit ODBC Data Source Administrator can be found by going to the Start Menu, selecting Run and executing c:\Windows\SysWOW64\odbcad32.exe as follows…

Once launched, you’ll see a tool that appears to be identical in every way, except that the ‘missing’ ODBC drivers are now available…

Please note that you can’t run both 32-bit and 64-bit tools at the same time so please make sure you close the 64-bit one first, it’s also worth bearing in mind that if you’re running 32-bit applications they will probably be looking for 32-bit DSNs so even if you can get a 64-bit driver for your data source it doesn’t mean that it’s the right thing to do.

As much as I understand Microsoft’s decision to stop developing 32-bit operating systems, a little bit of a helping hand during the transition period would’ve been nice - perhaps a second shortcut in the Administrative tools folder and the ability to run them simultaneously?  The whole thing seems like a confisuing mess in their own words…

The 32-bit version of the ODBC Administrator tool displays 32-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs. The 64-bit version of the ODBC Administrator tool displays 64-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs.  

To maintain backward compatibility, no resolution for this problem is currently available… to work around this problem, use the appropriate version of the ODBC Administrator tool.

I’ve made my own shortcuts to the 32-bit version and if you’re like me and constantly diving in and out of ODBC Administrator then you’ll probably want to do the same.