F# SqlProvider 无法更新具有 ODBC 连接的 dBase DBF 文件中的更改

Question

我有以下 F# 代码

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:CitectUserNPM;" 
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:CitectUser"
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 ()

连接和查询按预期工作，但是当我尝试更新数据源时，我收到来自 odbc 驱动程序的以下错误：

消息 -> 错误 [HY092] [Microsoft][ODBC dBase 驱动程序]无效 属性/选项标识符源 -> odbcjt32.dll

有没有办法让它工作，或者我是否需要放弃类型提供程序并求助于 OleDb？

更新

禁用事务使事情变得更好一些，现在错误是由于我必须使用的dbf文件中缺少主键。

唯一更改的代码是获取数据上下文

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

新的错误是：

系统异常：错误 - 无法更新未更新的实体 具有主键。(dbo.variable( at FSharp.Data.Sql.Providers.OdbcProvider.createUpdateCommand(IDbConnection con， StringBuilder sb， SqlEntity entity， FSharpList'1 changedColumns(

at .$P[e-mail](SqlEntity e( 在 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 超时( at .$SqlRuntime.DataContext.f@1-69(SqlDataContext __， IDbConnection con， Unit unitVar0( at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges((

关于如何处理这个探头的任何想法？

Answer 1

我发现了一种棘手/肮脏的方法，我将更多地将其归类为解决方法而不是真正的解决方案，但它适用于我的情况; 所以我将使用它，除非/直到其他人提出一个结论性的方法。

要使类型提供程序正常工作，我需要做 2 件不在通常工作流程中的事情：

1. 需要在禁用事务的情况下检索数据上下文
2. 在对 DBF 执行更改操作之前，我创建了一个主数据库 使用较低级别的 SQL 语句对该 DBF 进行键

这里是工作代码

[<Literal>]
let private schemaConn = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:CitectUserNPM;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:CitectUser"
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 ()