Last updated: December 3, 2014
MySQL Installation guide
This guide will help you to install and configure MySQL for IQCare 3.6.0. IQCare 3.6.0 uses MySQL database for audit trail data storage. Please follow the following steps to install and configure MySQL on your server / machine.
IQCare uses MySQL 5.6.14 version.
-Click on mysql-installer-community-5.6.14.0.msi
-Select “Install MySQL Products”
-Select “Skip the check for updates” for speedy installation, you install the updates later.
-On second screen, select “Full” setup option, press Next
-
-Click Next to proceed
-
-Click Execute to run the setup.
-
-It will start the installation process
-Click Next after installation, to configure MySQL
-Click Next, to configure
-Click Next to enter other information
-Click Next to select more
-Click Next to finish installation
Enter the password “c0nstella”
IQCare Database creation in MySQL
-Open the MySQL query editor by clicking the Local instance MySQL box and then enter the password you entered in the set up.
Copy below SQL statement to create IQCare database and a stored procedure
CREATE DATABASE IQCare;
Use IQCare;
CREATE TABLE tbl_iqcareaudit ( VisitIdint(11) DEFAULT NULL, LocationIdint(11) DEFAULT NULL, VisitDatedatetime DEFAULT NULL, VisitTypesmallint(6) DEFAULT NULL,
OperationTypevarchar(30) DEFAULT NULL, TableTypevarchar(5) DEFAULT NULL,
TableNamevarchar(150) DEFAULT NULL, UserIdvarchar(10) DEFAULT NULL,
FieldNamevarchar(150) DEFAULT NULL, OldValuevarchar(1000) DEFAULT NULL,
NewValuevarchar(1000) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Use IQCare;
ALTER TABLE iqcare.tbl_iqcareaudit ADD ptn_pkint first;
ALTER TABLE iqcare.tbl_iqcareaudit ADD createdatedatetime;
Use IQCare;
Drop procedure GetAuditTrails;
DELIMITER $$
CREATE PROCEDUREGetAuditTrails()
BEGIN
Select ptn_pk, VisitId, LocationId, visitdate, VisitType, OperationType, TableName, UserId, FieldName, OldValue, NewValue, createdate from tbl_iqcareaudit;
END
-You can find IQCare database, table and stored procedure in your MySQL.
MySQL ODBC configuration steps
-On your computer open the Control Panel, Click on Administrative Tools.
-double click on ODBC Data Sources to configure for 64 bit machine. It will have same steps for 32 bit machine as well.Please Configure for System DSN as well same way as User DSN.
-Enter data source name“MySQL56”(instance of MySQL), credentials to connect and database – in this case it’s IQCare.
Steps to Create Scheduler
Click Control Panel and Select Administrative Tool
Double click on Task Scheduler
Click Create task… under Actions
Enter the task name
Click on Trigger Tab and click New…
Specify Time like by selecting Radio Delete and repeat task every 1 hour
Click on Actions Tab and select New. Select action – Start a Program
Select Backup.bat from folder in C:\Backup – or the location you had restored it. Press OK to finish.
Create a linked server in SQL Server
-Please run the following SQL in SQL Server to create a Linked Server with MySQL.
Please get the latest version of the script.
USE [master]
GO
/****** Object: LinkedServer [MySQL] Script Date: 9/10/2014 4:03:19 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MySQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'MySQL', @provstr=N'DRIVER={MySQL ODBC 5.3 ANSI Driver}; SERVER=localhost; PORT=3306; USER=root; PASSWORD=c0nstella; OPTION=3;'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MySQL',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'remoteproc transaction promotion', @optvalue=N'true'
GO