F# Beginner: retrieving an array of data from a server

Question

I'm trying to grab data from a MySQL database.

Approach 2 - apply/map style

I'm using the MySQL ADO Reference to try to build this system. In particular, the example found at 21.2.3.1.7.

(using a pseudo code)

let table = build_sequence(query.read)

Where query.read returns a row in the table(Or rather, a list of elements that happen to be a row in the table). And the table variable is a list of lists that will represent a table returned from the query.

I've stared at the code given below, and it's syntax is over my head, I'm afraid.

Approach 1 - looping.

Problem 1: It's inelegant, requiring a mutable.

Problem 2: This just feels wrong, based on my prior experience with Prolog & Lisp. There's gotta be a more...functional way to do this.

I'm not sure where to begin though. Comments & thoughts?

let reader : MySql.Data.MySqlClient.MySqlDataReader = command.ExecuteReader()
let arr = []
let mutable rowIter = 0
let readingLoop() =
 while(reader.Read()) do
 rowIter = rowIter + 1
 for i = 0 to reader.FieldCount do
 //set arr[someiterator, i] = reader.GetValue[i].ToString())

Answer 1

The Seq type has a neat function for handling database cursors called generate_using (see F# Manual and the Data Access chapter in Foundations of F#). This is a higher order function that takes one function to open the cursor and another (called repeatedly) to process records from the cursor. Here is some code that uses generate_using to execute a sql query:

let openConnection (connectionName : string) =
 let connectionSetting = ConfigurationManager.ConnectionStrings.Item(connectionName)
 let connectionString = connectionSetting.ConnectionString
 let connection = new OracleConnection(connectionString)
 connection.Open()
 connection
let generator<'a> (reader : IDataReader) =
 if reader.Read() then
 let t = typeof<'a>
 let props = t.GetProperties()
 let types = props
 |> Seq.map (fun x -> x.PropertyType)
 |> Seq.to_array
 let cstr = t.GetConstructor(types)
 let values = Array.create reader.FieldCount (new obj())
 reader.GetValues(values) |> ignore
 let values = values
 |> Array.map (fun x -> match x with | :? DBNull -> null | _ -> x)
 Some (cstr.Invoke(values) :?> 'a)
 else
 None
let executeSqlReader<'a> (connectionName : string) (sql : string) : 'a list = 
 let connection = openConnection connectionName
 let opener() = 
 let command = connection.CreateCommand(CommandText = sql, CommandType = CommandType.Text)
 command.ExecuteReader()
 let result = Seq.to_list(Seq.generate_using opener generator) 
 connection.Close()
 connection.Dispose()
 result

For example to list all the tables in an Oracle database we need to define a column definition type and invoke executeSqlReader as follows:

type ColumnDefinition = {
 TableName : string;
 ColumnName : string;
 DataType : string;
 DataLength : decimal; 
}
let tableList = executeSqlReader<ColumnDefinition>
 "MyDatabase"
 "SELECT t.table_name, column_name, data_type, data_length FROM USER_TABLES t, USER_TAB_COLUMNS c where t.TABLE_NAME = c.table_name order by t.table_name, c.COLUMN_NAME"

• Paul Nathan: OK. I'm still not totally comfortable with all the syntactic elements in that, but I follow the semantics and it answers my question. Thank you very much.
• Mauricio Scheffer: I'd use the use keyword to dispose the connection instead of doing it manually.
• Paul Nathan: After time has passed, I still can't get the syntax to sort itself out.

Answer 2

It can be hard to work with imperative APIs in a non-imperative way. I don't have MySql handy, but I made an approxmiation, hopefully this will provide inspiration. Seq.unfold is a function people find pretty awesome once they grok it. List.init (or Array.init) are also handy for initializing known-size data structures without using mutables.

#light
type ThingLikeSqlReader() =
 let mutable rowNum = 0
 member this.Read() =
 if rowNum > 3 then
 false
 else
 rowNum <- rowNum + 1
 true
 member this.FieldCount = 5
 member this.GetValue(i) = i + 1
let reader = new ThingLikeSqlReader() 
let data = reader |> Seq.unfold (fun (reader : ThingLikeSqlReader) ->
 if reader.Read() then
 Some (List.init reader.FieldCount (fun i -> reader.GetValue(i)), reader)
 else
 None) |> Seq.to_list 
printfn "%A" data