F # Novice: getting an array of data from the server

Question

I am trying to grab data from a MySQL database.

Approach 2 - Application / Map Style

I am using the MySQL ADO Reference to try to create this system. In particular, the example found in 21.2.3.1.7.

(using pseudo code)

let table = build_sequence(query.read) 

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

I stared at the code below and this is the syntax above my head, I'm afraid.

Approach 1 - The Cycle.

Task 1: Inelegant, requiring change.

Problem 2: This is simply wrong, based on my previous experience with Prolog and Lisp. There should be a more ... functional way to do this.

I'm not sure where to start. Comments and 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 processing database cursors called generate_using (see F # Manual and the data access section in F # Fundamentals ). This is a higher order function that takes one function to open the cursor and another (repeatedly) to process records from the cursor. Here is the code that generate_using uses to execute the 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 determine the type of column definition and call 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" 

Answer 2

It is not easy to work with imperative APIs. I do not have MySql, but I made an approximation, I hope this will provide inspiration. Seq.unfold is a feature that people find pretty intimidating as soon as they feel for it. List.init (or Array.init) is also convenient for initializing data structures of known size without using variables.

 #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