Database[SQLite] - Maple Programming Help

Home : Support : Online Help : Connectivity : Database Package : SQLite : Database/SQLite/FetchAll

Database[SQLite]

 FetchAll
 fetch all rows

 Calling Sequence FetchAll( statement, header, valuetype  )

Parameters

 statement - prepared statement obtained using Prepare command header - return column names in first row of the output, default is false valuetype - type of the output data, 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.
 • If valuetype is a string then the type conversion it specifies applies to all columns.
 • If valuetype is a list of strings then type of column is determined by a corresponding entry in the list.

Examples

 > $\mathrm{with}\left({\mathrm{Database}}_{\mathrm{SQLite}}\right)$
 $\left[{\mathrm{Attach}}{,}{\mathrm{Bind}}{,}{\mathrm{ClearBindings}}{,}{\mathrm{Close}}{,}{\mathrm{ColumnCount}}{,}{\mathrm{ColumnNames}}{,}{\mathrm{Execute}}{,}{\mathrm{Fetch}}{,}{\mathrm{FetchAll}}{,}{\mathrm{FetchRow}}{,}{\mathrm{Finalize}}{,}{\mathrm{Open}}{,}{\mathrm{Opened}}{,}{\mathrm{Prepare}}{,}{\mathrm{RESULT_BUSY}}{,}{\mathrm{RESULT_DONE}}{,}{\mathrm{RESULT_ROW}}{,}{\mathrm{Reset}}{,}{\mathrm{Step}}\right]$ (1)
 > $\mathrm{db}≔{\mathrm{FileTools}}_{\mathrm{JoinPath}}\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"}$ (2)

Fetch all rows

 > $\mathrm{FetchAll}\left(\mathrm{stmt}\right)$
  (3)
 > $\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"}$ (4)

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]$ (5)

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]$ (6)
 > $\mathrm{Finalize}\left(\mathrm{stmt}\right):$
 > $\mathrm{Close}\left(\mathrm{connection}\right):$

Compatibility

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