F# SqlProvider fails to update changes in a dBase DBF file with ODBC connection

Question

I have the following F# code

open FSharp.Data.Sql
open FSharp.Data.Sql.Runtime
open System.IO

[<Literal>]
let private schemaConn = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\Citect\User\NPM;" 
type private schema = SqlDataProvider<Common.DatabaseProviderTypes.ODBC, schemaConn>
let private connStringFormat = Printf.StringFormat<string->string>(@"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=%s;")

type internal Project = {
    name : string
    path : string
    dcx : schema.dataContext
}

[<Literal>]
let private cUserPath = @"C:\Citect\User"

let private findPath projectName = 
    Directory.GetDirectories(cUserPath, projectName, SearchOption.AllDirectories)
    |> Array.find (fun d -> d.Contains("web") |> not)

let internal connect projectName =
    let path' = findPath projectName
    let connString = sprintf connStringFormat path'
    let dcx' = schema.GetDataContext(connString)
    { name = projectName; path = path'; dcx = dcx' }

let internal updVariable (project : Project) variable = 
    let dcx = project.dcx
    let q = query {
        for v in dcx.Dbo.Variable do
            where (v.Addr = "%MW217.0")
            select v
            exactlyOne
    }
    q.Addr <- "QQQ"
    dcx.SubmitUpdates() //error

let internal prj = connect "NPMUG_SCC35"
updVariable prj ()

Connection and query work as expected, but when I try to update the data source I get the following error coming from the odbc driver:

Message -> ERROR [HY092] [Microsoft][ODBC dBase Driver]Invalid attribute/option identifier Source -> odbcjt32.dll

Is there a way to get it working or do I need to give up the type provider and resort back to OleDb?

UPDATE

Disabling transactions makes things a little better, now the error is due to the missing primary key in the dbf files I have to work with.

The only code changed is getting the data context

let dcx = schema.GetDataContext( { Timeout = TimeSpan.MaxValue; IsolationLevel = Transactions.IsolationLevel.DontCreateTransaction } : FSharp.Data.Sql.Transactions.TransactionOptions)

And the new error is:

System.Exception: Error - you cannot update an entity that does not have a primary key. (dbo.variable) at FSharp.Data.Sql.Providers.OdbcProvider.createUpdateCommand(IDbConnection con, StringBuilder sb, SqlEntity entity, FSharpList`1 changedColumns)

at .$Providers.Odbc.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates@648-4.Invoke(SqlEntity e) at Microsoft.FSharp.Collections.SeqModule.Iterate[T](FSharpFunc2 action, IEnumerable1 source) at FSharp.Data.Sql.Providers.OdbcProvider.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates(IDbConnection con, ConcurrentDictionary2 entities, TransactionOptions transactionOptions, FSharpOption1 timeout) at .$SqlRuntime.DataContext.f@1-69(SqlDataContext __, IDbConnection con, Unit unitVar0) at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges()

Any idea on how to deal with this probem?

Answer 1

I found a tricky/dirty way that I would classify more as a workaround than a real solution, but it works in my case; so I am going to use it unless/until someone else suggests a conclusive one.

To get the type provider working I need to do 2 things not in the usual workflow:

1. The data context needs to be retrieved with transactions disabled
2. Before performing changing operations on a DBF, I create a primary key on that DBF using a lower level SQL statement

Here the working code

[<Literal>]
let private schemaConn = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\Citect\User\NPM;READONLY=FALSE" 
type private schema = SqlDataProvider<Common.DatabaseProviderTypes.ODBC, schemaConn>
let private connStringFormat = Printf.StringFormat<string->string>(@"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=%s;READONLY=FALSE")

type internal Project = {
    name : string
    path : string
    dcx : schema.dataContext
}

[<Literal>]
let private cUserPath = @"C:\Citect\User"

let private findPath projectName = 
    Directory.GetDirectories(cUserPath, projectName, SearchOption.AllDirectories)
    |> Array.find (fun d -> d.Contains("web") |> not)

let private createPK (cn : IDbConnection) = 
    let cm = cn.CreateCommand()
    cm.CommandText <- "ALTER TABLE Variable ADD PRIMARY KEY (Name)"
    try
        cn.Open()
        cm.ExecuteNonQuery() |> ignore
    finally cn.Close()

let internal connect projectName =
    let path' = findPath projectName
    let connString = sprintf connStringFormat path'
    let transOptions = { Timeout = TimeSpan.FromSeconds(3.0); IsolationLevel = Transactions.IsolationLevel.DontCreateTransaction }
    let dcx' = schema.GetDataContext(connectionString = connString, transactionOptions = transOptions)
    dcx'.CreateConnection() |> createPK
    { name = projectName; path = path'; dcx = dcx' }

let internal updVariable (project : Project) variable = 
    let dcx = project.dcx
    let q = query {
        for v in dcx.Dbo.Variable do
            where (v.Addr = "%MW217.0")
            select v
            exactlyOne
    }
    q.Addr <- "QQQ"
    dcx.SubmitUpdates()

let internal prj = connect "NPMUG_SCC35"
updVariable prj ()