Back to Questions
3.8k
views6
comments
Database programming
f#

Hello, I would like to convert the results of a Sql query as a Array of a given type, to be able to do some Linq-like programming, but only using F# libraries (X.Map,X.Filters,etc..) For instance :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

//MS Access string & connect 

let access_connect file_path = 

let conn_str = "Driver= {Microsoft Access Driver (*.mdb)};DBQ=" ^file_path ^ ";Uid=;Pwd=;" in 

let db = new OdbcConnection(conn_str) in 

let _ = db.Open() in //ouverture de la connexion 

db;; 

//prepare the sql query 

let sql_prepare dbconn sql = new OdbcCommand(sql , dbconn) ;;

 //aux routine to return the full row of the sql query 

let multi_col (x:System.Data.Common.DbDataRecord) = 

let n = x.FieldCount in 

let y = Array.zero_create n in 

let _ = x.GetValues(y) in 

y;; 

// execute the query 

let sql_exec(sql_command : OdbcCommand) = sql_command.ExecuteReader(CommandBehavior.CloseConnection) 

|>IEnumerable.map_with_type (fun (x:DbDataRecord ) ->multi_col x) 

|> IEnumerable.to_array

So sql_exec returns a obj array array, but :

I can access the name of each column (with GetName)

and the type of each column (with GetFieldType )

So if I could dynamically define the dedicated record type : 

1

type myRecordSet = {field1 : datatype1 , field2 : datatype2, ...}

I could manipulate more easily the output : 

1

Array.filter ( record -> record.field1 > 12. ) my_output

instead of a clumsier 

1

 Array.filter ( record -> (record.[0] :?> float) > 12. ) my_output

So to summarize : is it possible to dynamically define types ? Does stuff like System.Reflection.Emit (which I don't know at all) allows for this kind of stuff, or is there a simple other way to do it? Many thanks in advance

Regards

Julien

[NB : updated with formatted code

btw, apparently, the Opera browser cannot see the editing tools facitility when posting ]

.

[ Hey guys, don't forget to use the lovely "F#" button to place formatting marks around your code! optionsScalper worked hard on that! ]

Hi Julien,

This is easy only to the extent that you can model the types as structural types using the structural type construction primitives provided by the F# type system - e.g. you can model your row types as tuples (without the labels - I know this is not ideal).

These primitives have their limitations: F# does not (as yet :-) ) support structural record types. However, even if we did it is not easy to model database schema as type schema. You inevitably end up using a code-generation approach - one .NET tool for doing this is SQLMetal.exe that is part of LINQ.

Runtime type generation is also possible, but not as useful as you might hope, as you inevitably want to refer to the runtime generated types from substantial chunks of static code, and you get dragged down the road of having your entire application runtime generated.

An alternative may be to have the client define the columns expected using a type declaration (like the one you have written), and use a bunch of runtime reflection tricks to check the shapes match up and to morph the data into the expected shape (e.g. match up column names with record field names). In otherwords, your generic SQL row reader would take a type parameter 'row, and would use reflection over the System.Type value generated by (type 'row) to match up field names/types and column names/types. I've never used that approach personally, but it looks feasible. You might like to look around in some of the fslib implementation to see how to do dynamic things like using reflection to inspect field types, names and values.

Don

By on 8/13/2006 5:22 PM ()

Hi Don,
and thanks for the answer.
As sqlMetal is apparently only for SQl Server databases, I cannot use it.
So I'll try the 3rd approach.

say the record is 

1
2

 
type rs = {c1 : string;c2 : float;c3 : float};;

I need to "discover" the structure of this record. With the following code : 

1
2
3
4
5
6

 
let xtract_info (t:TypeInfo) = 
match t with 
| RecordType(rt) -> let fieldnames = List.map (fun t -> fst t) rt in //not used for the moment
List.map (fun t-> GetTypeInfoOfType (snd t)) rt 
| _ -> failwith "not a simple record"

The analysis of

1

xtract_info (GetTypeInfoOfType (type rs))

shows that the sub-elements of the record are of type 'ExternalType' , which does not seem to be useful to me. Is there a way to retrieve the unit types Type? (e.g. string, float, float, or their equivalent in .NET)
Thanks in advance
Julien

By on 8/14/2006 4:11 PM ()

Hi Julien!

"ExternalType" means "a type from some other .NET language than F# (e.g. C#) which you should use the regular .NET Reflection APIs and System.Type methods to access". So you're one the right track - float will appear as System.Double, for example.

FWIW, you will need to become a proficient System.Reflection programmer to build such a generic manipulation framework. Start off with a moderate goal (e.g. creating objects of the target record type based on SQL column names). You may also need to pick up dependencies on some aspects of teh compiled representation of F# - e.g. you will need to know that record types are compiled as .NET class types with a constructor accepting arguments in the order of the record field declarations, as you will need to perform a dynamic invocation of the constructor in order to create the object. Similarly fields are compiled as properties. We would eventually like to augment the Microsoft.FSharp.Reflection API to ensure all the primitives are available to insulate you from these details, but they are not all yet present.

-- Don

By on 8/14/2006 4:44 PM ()

Hi again,

Sorry to bother you again, but I still can't see the right subtypes (resp. string,float,float), even when (trying to )using the .NET APIs. Probably I do not apply the right methods.

The query on the type of the elements of the records shows :

"System.Reflection.RuntimePropertyInfo"

I also tried a

System.Type.GetTypeCode on the sub-types, they throw a Object.

The BaseType of those subtypes are System.Reflection.PropertyInfo.

None of these informations are still useful.

Any ideas?

Thanks in advance

Regards

Julien

By on 8/15/2006 3:36 PM ()

Looks like a bug in the reflection APIs.

Line 219 of Reflection.fs should read:

let RecdDescOfProps props = map (fun (p:PropertyInfo) -> p.Name, p.PropertyType) props

Not:

let RecdDescOfProps props = map (fun (p:PropertyInfo) -> p.Name, p.GetType()) props

The call to GetType somewhat counter intuatively gives you the type object of the PropertyInfo object.

To be fair the reflection APIs are still pretty new to F#.

By on 8/16/2006 12:11 PM ()

Thanks for the bug report - now fixed in our tree.

don

By on 8/16/2006 12:58 PM ()

    Topic tags

    IntelliFactory Offices Copyright (c) 2011-2012 IntelliFactory. All rights reserved.
    Home | Products | Consulting | Trainings | Blogs | Jobs | Contact Us | Terms of Use | Privacy Policy | Cookie Policy     		Built with WebSharper