ISQL Sample

Copyright (c) 1997, 1998 by Jack Courtney
All Rights Reserved.

This is a sample of a database interface package for Mathematica.  To try it, you must be able to log on to a MS SQLServer installation, and you will need the command-line utility  isql.exe (which ships with SQLServer).  To run the included sample notebook, you will also need to have at least 'select' privileges on the table 'Pubs' (which is installed with MS SQLServer).  Finally, for Mma to be able to find isql.exe, its full path must be specified in ISL`Common (isqlPath=...).

<<  ISQL`Common`

Define the database connection structure:

To run the demo (below), you must change only the first, third and fourth entries, so that they match your own MS SqlServer access.  The last three entries alter the behavior of the ISQL utility; so, these likely should be left alone.

Pubs = { "yourserver.domain.type" (* The address of the server *)
       , "pubs"                   (* The name of the database on the server *)
       , "sa"                     (* The db login that you wish to use *)
       , ""                       (* The corresponding db password *)
       , 10000                    (* The maximum length of a returned line *)
       , 8192                     (* The packet size: OS and protocol dependent *)
       , "`"                      (* The field separator *) 
       };

What's in the database?

Option TableTypes can have the value All, {SystemTables}, {OtherTables}, {Views}, or any combination of the latter three.  Option TableOwner can be used to specify a single user's tables; e.g., TableOwner->"Grunewald".

TableForm[isqlTableList[Pubs,  TableTypes -> All ]]

What data types does the database support?

TableForm[
      isqlStringTable[  Pubs,   
        "Select * from dbo.systypes Order by type" ]]

Look at the structure of a couple of tables:

TableForm[isqlColumnList[ Pubs, "systypes" ]]TableForm[isqlColumnList[ Pubs, "jobs" ]]

Several flavors of a simple data access query:

qry  =   "Select * from dbo.jobs";

Raw:

This function returns the raw output of the ISQL tool at a list of record Strings, with no reformatting.  Each record is simply a String, with its fields delimited by the separator character specified by the user (in ISQL`Common).

TableForm[isql[ Pubs,  qry ]]

Trimmed Strings:

This function returns a 2-d array of Strings.  Each record is parsed into its component fields, and the leading and trailing white space is trimmed from each field.

TableForm[isqlStringTable[  Pubs,   qry ]]

Expressions:

This function returns a 2-d array of Expressions (with the field names as Strings).  Each record is parsed into its component fields, which are read as Expressions.  Currently, isqlTable reads ALL fields as Expressions; so, e.g., text fields can cause exceptions to be raised.

TableForm[isqlTable[  Pubs,   qry ]]

More complicated queries:

A complex select query with table joins:

qry2 = 
      "SELECT DISTINCT titleview.title, authors.au_lname, authors.au_fname, \
publishers.pub_name, publishers.city, publishers.state, publishers.country
FROM (authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id) \
INNER JOIN ((titles INNER JOIN titleview ON (titleview.ytd_sales = \
titles.ytd_sales) AND (titleview.price = titles.price) AND (titles.title = \
titleview.title)) INNER JOIN publishers ON (titles.pub_id = \
publishers.pub_id) AND (titleview.pub_id = publishers.pub_id)) ON \
(titleauthor.au_ord = titleview.au_ord) AND (titleview.au_lname = \
authors.au_lname) AND (titleauthor.title_id = titles.title_id)";

TableForm[isqlStringTable[  Pubs,   qry2 ]]

A data definition query that creates a table:

If you have only 'select' privileges for Pubs, you will not be able to complete the following query.

qry3 = "CREATE TABLE dbo.pseudonyms (
	pn_id varchar (11) NOT NULL ,
	pn_lname varchar (40) NOT NULL ,
	pn_fname varchar (20) NOT NULL ,
	phone char (12) NOT NULL ,
	address varchar (40) NULL ,
	city varchar (20) NULL ,
	state char (2) NULL ,
	zip char (5) NULL ,
	contract bit NOT NULL
)";

Notice that success for a data definition query produces the return value {}.

isql[ Pubs,  qry3 ]