Struggling with Active Reports integration with MySQL? Here’s a comprehensive guide on using an ODBC connector to sync up your data and troubleshoot common issues.
Working with Active Reports often means navigating through scarce online resources for troubleshooting or resolving specific issues. Although there is plenty of documentation available, it sometimes doesn’t show go through specific examples. In this guide, we’ll walk through a detailed step-by-step process to integrate Active Reports with MySQL database using an ODBC connector.
We were asked by a client to provide an active reports designer that could integrate with their MySQL database. To do this we had to use an ODBC connector to sync up the data and embed it in the report. So in theory this solution should work with any database that can connect an through to the ODBC driver.
Download ODBC Driver
First install the ODBC connector for mysql. Note that in our case – it only worked with the 32bit version, but feel free to try 64 if your machine allows it:
https://dev.mysql.com/downloads/connector/odbc/
Download C++ Redistributable
Next you’ll need to install the C++ Redistributable packages that match the version of the ODBC driver you just installed. For us, in development we only needed the 32 bit release, but a colleague mentioned he needed 64 as well. Here’s both for good measure:
https://aka.ms/vs/17/release/vc_redist.x86.exe
https://aka.ms/vs/17/release/vc_redist.x64.exe
Create ODBC Connection
Launch the trusty command prompt as an admin and change directories to the C:\windows\syswow64 folder. Then run the odbcad32.exe (or 62 if that’s what you have). Here you can setup your data source.
We wanted it system wide instead of per user so had to select System DSN. Then you’ll be prompted for a driver, choose MySQL ODBC (Ansi or Unicode depending on your preference). Enter your database connection info and click test to make sure it’s all valid. If you can’t connect here you’ll have to double check your MySQL Settings.
Connect ODBC to Active Reports
Now you should be able to use this new data source through your Active Report. Create your report and click Add/Edit datasource then choose the type : Microsoft Odbc Provider
Here you can use a connection string like so :
DSN=MyDatasourceIJustCreated;server=DataServerIP;user=Username;password=Password
Click OK and you should be able to query your database!
Bonus Tip
Another issue we ran into – if you’re hosting an API or Website that generates these active reports, you may run into an error message like so:
“Error creating file: An unexpected error occurred. Additional information: ‘ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application'”
This likely means there’s a mismatch somewhere between a 32bit and 64bit program. Since we had to use the 32bit version of ODBC we also had to update our hosting application pool to allow for 32bit connections. In IIS this is in App Pools -> right click your app pool -> advanced -> choose the dropdown for enable 32 bit application – but keep in mind this will be different for each hosting platform.
Hopefully that helped, Active Reports has a bunch of documentation but integrating it with MySQL turned out to be more complex than we expected. This guide aimed to simplify this process and troubleshoot common issues. If you find this guide helpful or have any more queries, feel free to drop a comment. Happy coding!
Great post!