Search code examples
sqlitef#type-providers

SqlTypeProvider error when accessing SQLITE database

I'm accessing an SQLite database via SQLProvider. I can actually connect to the database and query data in the table. However the type provider shows an error: Exception has been thrown by a target of an invocation. And intellisense doesn't really work, e.g. the tables or properties do not show. See the screenshot:

Type Provider Error

Since the tables and types appear not accessible VS shows the lookup on object of indeterminate type error when trying to access the properties (it does work though). Extracting the data from the tables also works. So the type provider can access the database but shows these errors. Is there way to make it recognize the db correctly and access its properties, etc. without the errors.

I'm using the 64-bit SQLite driver. Here's the code:

#if INTERACTIVE
#r @"..\packages\SQLProvider.1.0.8\lib\FSharp.Data.SqlProvider.dll" 
#r @"..\packages\System.Data.SQLite.Core.1.0.101.0\lib\net46\System.Data.SQLite.dll"
#r @"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.6\System.Data.Linq.dll"
#endif

open System
open FSharp.Data.Sql

[<Literal>]
let connectionString = "Data Source="+ @"C:\tmp\databaseFile.db3"
[<Literal>]
let resolutionPath = __SOURCE_DIRECTORY__ + @"..\..\packages\System.Data.SQLite.Core.1.0.101.0\lib\net46"

type sql = SqlDataProvider<
                Common.DatabaseProviderTypes.SQLITE, 
                ConnectionString = connectionString, 
                ResolutionPath = resolutionPath, 
                CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL
                >

let ctx = sql.GetDataContext()

let table2 = ctx.Main.Table2   //DateTime
let table3 = ctx.Main.Table3   //Text

query {                                         
        for r in table3 do
            select (r.Date1)                                
            } |> Seq.toList

query {                                         
        for r in table2 do
            select (r.Date1)                                
            } |> Seq.toList
Solution

  • This works as intended with the current SQLite and SQLTypeProvider (1.0.102.0 and 1.0.31 respectively):

    #if INTERACTIVE
#I @"..\packages\SQLProvider.1.0.31\lib"
#r "FSharp.Data.SqlProvider.dll" 
#I @"..\packages\System.Data.SQLite.Core.1.0.102.0\lib\net46"
#r "System.Data.SQLite.dll"
#I @"..\packages\System.Data.SQLite.Linq.1.0.102.0\lib\net46"
#r "System.Data.SQLite.Linq.dll"
#endif

open System
open FSharp.Data.Sql
//open System.Data.SQLite
//open System.Data.SQLite.Linq

[<Literal>]
let connectionString = "Data Source="+ @"C:\tmp\databaseFile.db3"
[<Literal>]
let resolutionPath = __SOURCE_DIRECTORY__ + @"..\..\packages\System.Data.SQLite.Core.1.0.102.0\lib\net46"

type sql = SqlDataProvider<
                Common.DatabaseProviderTypes.SQLITE, 
                connectionString, 
                ResolutionPath = resolutionPath, 
                CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL>

let ctx = sql.GetDataContext() 

let table2 = ctx.Main.Table2   //DateTime
let table3 = ctx.Main.Table3   //Text

    This might have had something to do with this issue #196.