|
5.8 Using DataSourceEvaluate Options
There are four DataSourceEvaluate options: ShowColumnHeadings, ShowResultSet, ShowDimensions and GetAsStrings. These options change the form of the result set returned by DataSourceEvaluate, as described in Section 5.1. Since only SQLSelect returns output, these options are only effective when DataSourceEvaluate is used to execute an SQLSelect statement. ShowColumnHeadings was also used in Section 5.3. The use of each of the DataSourceEvaluate options is demonstrated here with the database dbselect.
5.8.1 ShowColumnHeadings
ShowColumnHeadings can be set to True or False. The default value is False, and so column headings are not normally returned by DataSourceEvaluate when SQLSelect is executed. ShowColumnHeadings->True adds the column names to the list returned by SQLSelect.
In[1]:=
Out[2]=
For example, no column names are shown using DataSourceEvaluate without options.
In[3]:=
Out[3]//TableForm=
But with ShowColumnHeadings set to True, the column names do appear at the heads of the columns when the result is displayed in TableForm.
In[4]:=
Out[4]//TableForm=
The column headings are the first element of the output list as shown in this next execution.
In[5]:=
Out[5]=
In[6]:=
5.8.2 ShowResultSet
To suppress output from DataSourceEvaluate, set ShowResultSet to False. The default value of ShowResultSet is True, and DataSourceEvaluate returns the results sent from SQLSelect. If no error is returned upon execution, then the DataSourceEvaluate command has worked properly, but no listing is returned.
In[7]:=
In[8]:=
Out[8]=
In[9]:=
You can use this option in combination with ShowColumnHeadings->True to return only the column headings.
In[10]:=
Out[10]=
ShowResultSet->False does not just suppress display of output, it prevents the return of SQLSelect output. For example, send all of the output from the following command to a variable called Arr.
In[11]:=
Arr has no content.
In[12]:=
Out[12]//TableForm=
Repeat the DataSourceEvaluate commands with a result set returned.
In[13]:=
Out[13]=
And the content of Arr is just the list returned above.
In[14]:=
Out[14]=
In[15]:=
5.8.3 ShowDimensions
ShowDimensions->True returns a two-element list containing the number of rows and number of columns which would be returned by SQLSelect. The default value of ShowDimensions is False. ShowResultSet is automatically set to False when ShowDimensions is True.
In[16]:=
In[17]:=
Out[17]=
The following example shows an evaluation of an SQLSelect function which would return all of the data in the table royshed. With ShowDimensions->True, the return is the number of rows and columns of that table.
In[18]:=
Out[18]=
If SQLSelect accesses more than one column, the number of rows returned is the total number of rows that would be output by SQLSelect. In Section 5.3 a list was constructed showing the title column and pub_name column from the titles and publishers tables, respectively. The title column has eighteen rows, while the pub_name column in the publishers table has three rows, as determined from the following two commands.
In[19]:=
Out[19]=
In[20]:=
Out[20]=
If the pub_name and title columns are selected at once, the number of rows returned is not 21, the number of rows accessed, but rather 54, the number of rows returned by SQLSelect. There are three pub_name entries returned for each row of the title column. There are 54 rows in each of the two columns returned.
In[21]:=
Out[21]=
A portion of the actual list returned by the previous command is displayed in Section 5.3.
In[22]:=
5.8.4 GetAsStrings
GetAsStrings can be set to True. Its default value is False. When True, the elements of the list returned are converted from the column data type to strings.
For example, the column royalty in the royshed table has a column data type of DOUBLE. The column data type is found with DataSourceColumns.
In[23]:=
In[24]:=
Out[24]=
In[25]:=
Out[25]//TableForm=
As you would expect, the column data is returned in floating point form. The array is flattened here in order to perform further operations on the output list.
In[26]:=
Out[26]=
Now sum the list entries
In[27]:=
Out[27]=
or simply add the first two elements of the list.
In[28]:=
Out[28]=
Now return the list generated by SQLSelect as strings. There is no apparent difference between the output below and the list returned in the previous example.
In[29]:=
Out[29]=
Sum the first two elements of the list just returned, and note that the output is the addition of two strings.
In[30]:=
Out[30]=
Again sum the elements of the list. Since the entries are strings, the return is the number of instances of each string appearing in the list.
In[31]:=
Out[31]=
Be aware that GetAsStrings affects the form of the returned list elements from SQLSelect. The option does not affect the treatment of data types by SQL functions, which actually alter database data. Database drivers and systems can perform data type conversions to complete data manipulations requested by, for example, SQLUpdate.
Again consider the royalty column in the roysched table that has DOUBLE data type entries. Use SQLUpdate to add 0.5 to each row of the royalty column and, even though SQLUpdate does not return values, set GetAsStrings->True in DataSourceEvaluate.
In[32]:=
Upon viewing the royalty column notice that the addition of 0.5 was done in floating point form, not as a sum of strings.
In[33]:=
Out[33]=
In[34]:=
|
