<html dir="LTR" xmlns="http://www.w3.org/1999/xhtml" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:MSHelp="http://msdn.microsoft.com/mshelp" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:ndoc="urn:ndoc-preprocess">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252" />
<title>TYPES</title>
<link rel="stylesheet" type="text/css" href="ndoc.css" />
</head>
<body>
<div id="header">
<table width="100%" id="topTable">
<tr id="headerTableRow1">
<td align="left">
<span id="runningHeaderText">TYPES</span> </td>
</tr>
<tr id="headerTableRow2">
<td align="left">
<span id="nsrTitle">SQLite Language Reference Documentation</span>
</td>
</tr>
<tr id="headerTableRow3" style="display:none">
<td>
<a id="seeAlsoSectionLink" href="#seeAlsoToggle" onclick="OpenSection(seeAlsoToggle)">See Also</a>
<a id="exampleSectionLink" href="#codeExampleToggle" onclick="OpenSection(codeExampleToggle)">Example</a>
</td>
</tr>
</table>
<table width="100%" id="bottomTable" cellspacing="0" cellpadding="0" style="display:none">
<tr>
<td>
<span onclick="ExpandCollapseAll(toggleAllImage)" style="cursor:default;" onkeypress="ExpandCollapseAll_CheckKey(toggleAllImage)" tabindex="0">
<img ID="toggleAllImage" class="toggleAll" src="collall.gif" />
<label id="collapseAllLabel" for="toggleAllImage" style="display: none;">
Collapse All
</label>
<label id="expandAllLabel" for="toggleAllImage" style="display: none;">
Expand All
</label>
</span>
</td>
</tr>
</table>
</div>
<div id="mainSection">
<div id="mainBody">
<h1 class="heading">
SQL As Understood By SQLite (sortof)</h1>
<h4>
TYPES</h4>
<p>
<table cellpadding="10">
<tr>
<td align="right" width="1%" nowrap>
<i><font color="#ff3434">sql-statement</font></i> ::=</td>
<td>
<b><font color="#2c2cf0">TYPES </font></b>[<b><font color="#2c2cf0"></font></b><i><font
color="#ff3434">datatype name</font></i><b><font color="#2c2cf0"></font></b>][,<b><font color="#2c2cf0"></font></b><i><font
color="#ff3434">datatype name</font></i><b><font color="#2c2cf0"></font></b>][,<b><font color="#2c2cf0"></font></b><i><font
color="#ff3434">datatype
name</font></i><b><font color="#2c2cf0"></font></b>][,<b><font color="#2c2cf0"></font></b><i><font
color="#ff3434">...</font></i>] ; <em><span style="color: #ff3434">select-stmt</span></em></td>
</tr>
<tr>
<td align="right" width="1%" nowrap>
<i><font color="#ff3434">select-stmt</font></i> ::=</td>
<td>
see <a href="lang_select.html">SELECT</a></td>
</tr>
</table>
</p>
<p>
Use the TYPES keyword before a SELECT statement to provide the SQLite ADO.NET provider
a list of return datatypes to expect from the subsequent SELECT statement.
</p>
<p>
This is a language extension (aka <strong>hack</strong>) to SQLite specifically for the ADO.NET data
provider. It is a pseudo-statement, meaning only the ADO.NET provider understands
it.</p>
<h3>
Background</h3>
<p>
Due to SQLite's typeless nature, there are certain kinds of queries for which the
ADO.NET provider cannot determine the proper return data type. Scalar and
aggregate functions pose a particular problem because
there is no requirement for a given scalar or aggregate function to return any particular
datatype. As a matter of fact, scalar functions could theoretically return
a different datatype for every row or column in a query and this is perfectly legal
from SQLite's point of view.</p>
<p>
Since ADO.NET is designed around a typed system and we're shoe-horning SQLite into
it, this keyword helps the provider out in cases where the return type cannot be easily determined.</p>
<p>
This command must be used in conjunction with a SELECT statement. It only
works when both the TYPES keyword and its value(s) are passed along with a SELECT
statement as a single semi-colon separated unit.</p>
<h3>
Examples</h3>
<p>
<strong><span style="color: #2c2cf0">TYPES</span> [bigint], [int], [smallint], [tinyint];<br />
<span style="color: #2c2cf0">SELECT</span> 1, 2, 3, 4;</strong></p>
<p>
The above query would return the columns as types System.Int64, System.Int32, System.Int16
and System.Byte respectively.</p>
<p>
<strong><span style="color: #2c2cf0">TYPES</span> [bigint], [int], , [tinyint];<br />
<span style="color: #2c2cf0">SELECT</span> 1, 2, 3, 4;</strong></p>
<p>
In this sample, only columns 1, 2 and 4 would have explicit typing. Column
3's datatype would pass though the system and be discovered normally.</p>
<p>
<strong><span style="color: #2c2cf0">TYPES</span> real;<br />
<span style="color: #2c2cf0">SELECT</span> SUM(Cost) FROM [Products];</strong></p>
<p>
The above query explicitly tells the provider that the SUM aggregate function returns
a System.Double.</p>
<h3>
Usage Notes</h3>
<ul>
<li>You cannot use parameters in the TYPES statement.</li>
<li>The TYPES statement must be immediately followed by a SELECT statement.</li>
<li>It is legal to pass multiple TYPES and SELECT statements in a multi-statement
command.</li>
<li>You may enclose datatypes in quotes <strong>""</strong> or brackets <strong>[]</strong>
or those <strong>``</strong> thingies if you want.<br />
</li>
</ul>
<hr>
<div id="footer">
<p>
</p>
<p>
</p>
</div>
</div>
</div>
</body>
</html>
