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

[francotiveron]

Please see https://stackoverflow.com/questions/50401770/f-sqlprovider-fails-to-update-changes-in-a-dbase-dbf-file-with-odbc-connection

for a detailed description

[Thorium]

Sounds like possible underlying error in the SQL-clause... What you can do is:

• Test different Odbc quote characters (a static parameter to the provider)
• Add event listener to monitor the generated SQL-clause. Although I dont remember does the update raise the event...

[francotiveron]

Changing the quote character didn't change the outcome. What did was disabling transactions. However now I get a different exception.

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 <StartupCode$FSharp-Data-SqlProvider>.$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 <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.f@1-69(SqlDataContext __, IDbConnection con, Unit unitVar0) at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges()

How do I solve this primary key issue?

[francotiveron]

Please see the workaround I found in stackoverflow (link in the first post) and advise if needed,

[Thorium]

This is by design: If you want to update database values, you should have a primary key in the table.

The code line q.Addr <- "QQQ" will generate SQL-clause of update Variable set Addr = 'QQQ' where (...) and and to populate the "where" to hit only the record you want we have to use primary key, otherwise it could hit multiple records, what you probably wouldn't want.

[francotiveron]

Well imho there should be a way. Possible suggestions:

1) Add a provided method to SqlEntity, like SetPKField(name : string)

2) An event like MissingPrimaryKeyEvent to be raised when the schema is built and a table is found with no primary key. In the event handler there would be then the unique chance to set (a field in the event args or a method of a singleton explicitly passed in the event args) the field name to be used as index in all CRUD operations that need it.

[Thorium]

What you mean by table id? A column named "id" that is not marked as pk?

[francotiveron]

Sorry a typo, id->is