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

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())

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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"

Method 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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0