By Phone or Online

Access the help you need to use our software from representatives who are knowledgeable in data mining and predictive analytics

  • Banner 201707

    By Phone or Online

    Access the help you need to use our software from representatives who are knwoledgeable in data mining and predictive analytics

Download Now Instant Evaluation
Get Price Quote

How to access data in relational databases via ODBC

*************
SPM 6.6 (TreeNet TN 6.4) or greater supports data access to Microsoft SQL Server, Oracle, MySQL and other RDMS via ODBC interface.

Since SQL Queries cannot be entered via standard Windows ODBC dialog data source selection dialog, one has to use command line to open data directly from SQL Server.

*************

Below is a brief description how to do this:
The syntax is:USE "odbc://odbc_connection_spec[;//sql_query]"

The quotes are required around the argument. sql_query parameter
is optional unless data source does contain multiple tables.
Below are some examples which connect to MS Access single-table
dataset and an MS SQL Database. Line wraps are for clarity.

Example 1. Connect to MS Access database myTable.mdb

USE "odbc://DSN=MS Access Database;DBQ=C:\Datasets\myTable.mdb
DefaultDir=C:\Datasets;DriverId=25;FIL=MS Access;
MaxBufferSize=2048;PageTimeout=5;UID=admin;"

Example 2. Connect to MS SQL Server database MyData,
table MyTable

USE "odbc://Driver={SQL Server Native Client 10.0};Server=localhost\
MSSQLSERVER_INSTANCENAME;Database=MyData;Trusted_Connection=yes;//
select top 100 * from [MyData].[dbo].[MyTable]"

Please note that you have to specify the columns explicitly. "Select *" queries are not supported.

Data can be saved to a database via ODBC. Use the following syntax:

SAVE "odbc://odbc_connection_spec[;//table_name]"

Quotes are required around the argument. must be the name
of the table and the connection string must determine the database.
Below are some examples which connect to MS Access single-table
dataset and an MS SQL Database. Line wraps are for clarity.

Example 1. Save to MS Access database myTable.mdb

SAVE "odbc://DSN=MS Access Database;DBQ=C:\Datasets\myTable.mdb
;DefaultDir=C:\Datasets;DriverId=25;FIL=MS Access;
MaxBufferSize=2048;PageTimeout=5;UID=admin;"

Example 2. Save to MS SQL Server database MyData, table MyTable

SAVE "odbc://Driver={SQL Server Native Client 10.0};Server=localhost\
MSSQLSERVER_INSTANCENAME;Database=MyData;Trusted_Connection=yes;//MyTable

If data table already exists the data will be appended to it.

[J#352:1602]

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

Get In Touch With Us

Contact Us

9685 Via Excelencia, Suite 208, San Diego, CA 92126
Ph: 619-543-8880
Fax: 619-543-8888
info (at) salford-systems (dot) com