FetchAll - Maple Help

Database[SQLite]

 FetchAll
 fetch all rows from a prepared SQL statement

 Calling Sequence FetchAll( statement, opts )

Parameters

 statement - prepared SQL statement obtained using the Prepare command opts - (optional) equation(s) of the form option = value; specify options for the FetchAll command

Options

 True means return column names in first row of the output; default is false.
 • valuetype = string or list(string)
 The valuetype option specifies the type of the value. It may be one of the strings "auto", "blob", "float", "integer", "text", or a list of those strings. If a list, then the type of a column is specified by the corresponding element in the list; if a string, then it specifies the type of all columns. See the Database[SQLite] help page for more information about type conversion between SQLite and Maple. The default is "auto".

Description

 • The FetchAll command returns all available rows from the prepared statement.
 • There is no need to call Step after preparing the statement as FetchAll command will perform this.

Examples

 > $\mathrm{with}\left(\mathrm{Database}\left[\mathrm{SQLite}\right]\right):$
 > $\mathrm{db}≔\mathrm{FileTools}\left[\mathrm{JoinPath}\right]\left(\left[\mathrm{kernelopts}\left('\mathrm{datadir}'\right),"SQLite","G20-Population.db"\right]\right):$
 > $\mathrm{connection}≔\mathrm{Open}\left(\mathrm{db}\right):$

Select all data from table - prepare statement

 > $\mathrm{stmt}≔\mathrm{Prepare}\left(\mathrm{connection},"SELECT * FROM population"\right)$
 ${\mathrm{stmt}}{≔}{"SQLite statement"}{,}{"SELECT * FROM population"}$ (1)

Fetch all rows

 > $\mathrm{FetchAll}\left(\mathrm{stmt}\right)$
 $\begin{array}{c}\left[\begin{array}{ccccccccccc}{"1960-12-31"}& {1.80671000}{×}{{10}}^{{8}}& {6.67070000}{×}{{10}}^{{8}}& {9.2500572}{×}{{10}}^{{7}}& {7.2814900}{×}{{10}}^{{7}}& {4.6637746}{×}{{10}}^{{7}}& {7.2775883}{×}{{10}}^{{7}}& {5.2400000}{×}{{10}}^{{7}}& {5.0199700}{×}{{10}}^{{7}}& {1.19897000}{×}{{10}}^{{8}}& {\dots }\\ {"1961-12-31"}& {1.83691000}{×}{{10}}^{{8}}& {6.60330000}{×}{{10}}^{{8}}& {9.4943000}{×}{{10}}^{{7}}& {7.3377632}{×}{{10}}^{{7}}& {4.7293811}{×}{{10}}^{{7}}& {7.4988037}{×}{{10}}^{{7}}& {5.2800000}{×}{{10}}^{{7}}& {5.0536350}{×}{{10}}^{{7}}& {1.21236340900000006}{×}{{10}}^{{8}}& {\dots }\\ {"1962-12-31"}& {1.86538000}{×}{{10}}^{{8}}& {6.65770000}{×}{{10}}^{{8}}& {9.5832000}{×}{{10}}^{{7}}& {7.4025784}{×}{{10}}^{{7}}& {4.7990159}{×}{{10}}^{{7}}& {7.7286629}{×}{{10}}^{{7}}& {5.3250000}{×}{{10}}^{{7}}& {5.0879450}{×}{{10}}^{{7}}& {1.22590643400000006}{×}{{10}}^{{8}}& {\dots }\\ {"1963-12-31"}& {1.89242000}{×}{{10}}^{{8}}& {6.82335000}{×}{{10}}^{{8}}& {9.6812000}{×}{{10}}^{{7}}& {7.4714353}{×}{{10}}^{{7}}& {4.8697015}{×}{{10}}^{{7}}& {7.9643017}{×}{{10}}^{{7}}& {5.3650000}{×}{{10}}^{{7}}& {5.1252000}{×}{{10}}^{{7}}& {1.23960074400000006}{×}{{10}}^{{8}}& {\dots }\\ {"1964-12-31"}& {1.91889000}{×}{{10}}^{{8}}& {6.98355000}{×}{{10}}^{{8}}& {9.7826000}{×}{{10}}^{{7}}& {7.5318337}{×}{{10}}^{{7}}& {4.9361165}{×}{{10}}^{{7}}& {8.2017016}{×}{{10}}^{{7}}& {5.4000000}{×}{{10}}^{{7}}& {5.1675350}{×}{{10}}^{{7}}& {1.25344802900000006}{×}{{10}}^{{8}}& {\dots }\\ {"1965-12-31"}& {1.94303000}{×}{{10}}^{{8}}& {7.15185000}{×}{{10}}^{{8}}& {9.8883000}{×}{{10}}^{{7}}& {7.5963695}{×}{{10}}^{{7}}& {4.9945471}{×}{{10}}^{{7}}& {8.4379369}{×}{{10}}^{{7}}& {5.4348050}{×}{{10}}^{{7}}& {5.2112350}{×}{{10}}^{{7}}& {1.26745000}{×}{{10}}^{{8}}& {\dots }\\ {"1966-12-31"}& {1.96560000}{×}{{10}}^{{8}}& {7.35400000}{×}{{10}}^{{8}}& {9.9790000}{×}{{10}}^{{7}}& {7.6600311}{×}{{10}}^{{7}}& {5.0430631}{×}{{10}}^{{7}}& {8.6721875}{×}{{10}}^{{7}}& {5.4648500}{×}{{10}}^{{7}}& {5.2519000}{×}{{10}}^{{7}}& {1.27468492900000006}{×}{{10}}^{{8}}& {\dots }\\ {"1967-12-31"}& {1.98712000}{×}{{10}}^{{8}}& {7.54550000}{×}{{10}}^{{8}}& {1.00725000}{×}{{10}}^{{8}}& {7.6951336}{×}{{10}}^{{7}}& {5.0829214}{×}{{10}}^{{7}}& {8.9052356}{×}{{10}}^{{7}}& {5.4943600}{×}{{10}}^{{7}}& {5.2900500}{×}{{10}}^{{7}}& {1.28196115799999997}{×}{{10}}^{{8}}& {\dots }\\ {"1968-12-31"}& {2.00706000}{×}{{10}}^{{8}}& {7.74510000}{×}{{10}}^{{8}}& {1.01061000}{×}{{10}}^{{8}}& {7.7294314}{×}{{10}}^{{7}}& {5.1175036}{×}{{10}}^{{7}}& {9.1377490}{×}{{10}}^{{7}}& {5.5211700}{×}{{10}}^{{7}}& {5.3235750}{×}{{10}}^{{7}}& {1.28927892099999994}{×}{{10}}^{{8}}& {\dots }\\ {"1969-12-31"}& {2.02677000}{×}{{10}}^{{8}}& {7.96025000}{×}{{10}}^{{8}}& {1.03172000}{×}{{10}}^{{8}}& {7.7909682}{×}{{10}}^{{7}}& {5.1518731}{×}{{10}}^{{7}}& {9.3709914}{×}{{10}}^{{7}}& {5.5441750}{×}{{10}}^{{7}}& {5.3537950}{×}{{10}}^{{7}}& {1.29663845500000000}{×}{{10}}^{{8}}& {\dots }\\ {⋮}& {⋮}& {⋮}& {⋮}& {⋮}& {⋮}& {⋮}& {⋮}& {⋮}& {⋮}& {}\end{array}\right]\\ \hfill {\text{53 × 21 Matrix}}\end{array}$ (2)
 > $\mathrm{Finalize}\left(\mathrm{stmt}\right):$

Limit amount of data to date and population for Canada and first five rows

 > $\mathrm{stmt}≔\mathrm{Prepare}\left(\mathrm{connection},"SELECT date, CAN FROM population LIMIT 5"\right)$
 ${\mathrm{stmt}}{≔}{"SQLite statement"}{,}{"SELECT date, CAN FROM population LIMIT 5"}$ (3)

Fetch all rows with column names

 > $\mathrm{FetchAll}\left(\mathrm{stmt},'\mathrm{header}'=\mathrm{true}\right)$
 $\left[\begin{array}{cc}{"Date"}& {"CAN"}\\ {"1960-12-31"}& {1.7909009}{×}{{10}}^{{7}}\\ {"1961-12-31"}& {1.8271000}{×}{{10}}^{{7}}\\ {"1962-12-31"}& {1.8614000}{×}{{10}}^{{7}}\\ {"1963-12-31"}& {1.8964000}{×}{{10}}^{{7}}\\ {"1964-12-31"}& {1.9325000}{×}{{10}}^{{7}}\end{array}\right]$ (4)

Fetch the same data, but change the valuetype to "integer" for the first column and "text" for the second

 > $\mathrm{Reset}\left(\mathrm{stmt}\right)$
 > $\mathrm{FetchAll}\left(\mathrm{stmt},'\mathrm{header}'=\mathrm{true},'\mathrm{valuetype}'=\left["integer","text"\right]\right)$
 $\left[\begin{array}{cc}{"Date"}& {"CAN"}\\ {1960}& {"17909009.0"}\\ {1961}& {"18271000.0"}\\ {1962}& {"18614000.0"}\\ {1963}& {"18964000.0"}\\ {1964}& {"19325000.0"}\end{array}\right]$ (5)
 > $\mathrm{Finalize}\left(\mathrm{stmt}\right):$
 > $\mathrm{Close}\left(\mathrm{connection}\right):$

Compatibility

 • The Database[SQLite][FetchAll] command was introduced in Maple 18.