• Banner 201707

    INTRODUCING

    Fast, highly accurate platform for data mining and predictive analytics

  • Banner 201707

    INTRODUCING

    Fast, highly accurate platform for data mining and predictive analytics

Download Now Instant Evaluation
Get Price Quote

Reading MySQL tables with SPM®

SPM for Windows has long had the ability to read tables in relational databases through the ODBC interface. This capability was also recently added to the command line version on Windows and it is planned on UNIX platforms (including MacOS X). The purpose of this article is to describe how to access MySQL databases specifically, but the same principles will apply to accessing data stored in other relational database systems. Probably, the only thing that will differ will be the driver used.

Procuring a Driver

MySQL is distributed by Oracle and the drivers for various computing platforms are available on the MySQL website. The URL is http://dev.mysql.com/downloads/connector/odbc/. The drivers for Windows are distributed as MSI installers, so all that needs to be done to install them is to download and click on them.

Configuring the Driver

Once the driver is installed, open the Windows Control Panel, change the view from "Category" to one of the icon views, and open "ODBC". The window will look something like this:

ReadingMySQL-001

Click on the "Add button" and you will see:

ReadingMySQL-002

Either MySQL driver may be selected, depending on the contents of the database.

Once the selection is made, the configuration dialogue will appear.

ReadingMySQL-003

The "Data Source Name" (DSN) will be the name by which the data source will be known to ODBC. "TCP/IP Server" is the name of the server hosting the MySQL database. "User" and "Passwords" are the username and password for the MySQL installation you are trying to access. "Database" is the name of the desired database. When everything is filled out (together with such items under "Details" as seem appropriate). Then click on the "Test" button to make sure everything was entered correctly and that you can access the database. Once all appears to be in order, click on "OK" to add the new data source. Once you have verified that the all is correct, you may exit the applet.

Reading data over ODBC using the SPM GUI

Click on File:Open and select "SQL Query". You will get the following dialogue box:

ReadingMySQL-004

The connection string will be of the form "DSN=", where DSN is the name of the data source you are trying to use (do not quote the names; SPM will do that for you). The SQL query will be whatever will produce your desired input dataset. The simplest case (reading an entire table) will look like "SELECT * FROM ", but the SQL can be as complex as needed, as long as it is a single command. If all is well, the usual dialogue will appear and you can then perform the desired analyses.

Reading Data Over ODBC from the SPM command line

The command will be of the following form:

use "odbc://DSN=;;"

...where DSN is the data source name and query is the desired SQL query. If all goes well, you will receive the same response as if you had opened a flat file.

[J#356:1602]

Tags: Frequently Asked Questions, FAQs, Support, SPM, Salford-Systems

  • SPM Version 8 Just Released!

    SPM Version 8 Just Released!

    NEW Salford Predictive Modeler software suite.

    Read more

  • Environmental Forecasting

    Environmental Forecasting

    Forecast the evolution of environmental outcomes using changes in habitat and climate as predictors.
  • Sports Analytics

    Sports Analytics

    "Discover the undisclosed predictors to successful athletic performance using modern decision trees."
  • Targeted Marketing

    Targeted Marketing

    Enabling you to get appropriate prospective customers more efficiently than any other marketing strategies.
  • Text Mining

    Text Mining

    Derive high-quality information from text to improve your understanding of behaviours and patterns.
  • Bioinformatics

    Bioinformatics

    "Increase your probability of solving formal and practical challenges arising from the analysis of biological data."
  • Bioinformatics

    Bioinformatics

    Learn how to make knowledge-driven decisions that can revolutionize your business performance.
  • Financial Services

    Financial Services

    Analyze your spending and financial investments to help influence a profitable future for your company
  • Industrial Optimisation

    Industrial Optimisation

    Overcome retail challenges and achieve new levels of predictive accuracy, profitability and reliability.
  • Music

    Music

    Predict musical score groupings, composers that complement each other and what song listeners prefer to listen to.
  • Retail Analytics

    Retail Analytics

    Make smarter decisions to help manage your business more effectively and efficiently.
  • SPM Version 8 Just Released!

    SPM Version 8 Just Released!

    Salford Systems' applications span every major industry and business function

    Read more

  • Environmental Forecasting

    Environmental Forecasting

    Forecast the evolution of environmental outcomes using changes in habitat and climate as predictors.
  • Sports Analytics

    Sports Analytics

    Discover the undisclosed predictors to successful athletic performance using modern decision trees.
  • Targeted Marketing

    Targeted Marketing

    Enabling you to get appropriate prospective customers more efficiently than any other marketing strategies.
  • Text Mining

    Text Mining

    Derive high-quality information from text to improve your understanding of behaviours and patterns.
  • Bioinformatics

    Bioinformatics

    Increase your probability of solving formal and practical challenges arising from the analysis of biological data.
  • Business

    Business

    Learn how to make knowledge-driven decisions that can revolutionize your business performance.
  • Financial Services

    Financial Services

    Analyze your spending and financial investments to help influence a profitable future for your company
  • Industrial Optimisation

    Industrial Optimisation

    Overcome retail challenges and achieve new levels of predictive accuracy, profitability and reliability.
  • Music

    Music

    Predict musical score groupings, composers that complement each other and what song listeners prefer to listen to.
  • Retail Analytics

    Retail Analytics

    Make smarter decisions to help manage your business more effectively and efficiently.

Get In Touch With Us

Request online support

Ph: 619-543-8880
9685 Via Excelencia, Suite 208, San Diego, CA 92126