Params.Tuple needed to be replaced by Params.Record

[rbauduin]

I had a query I couldn't issue successfully passing a tuple, but which worked using an anonymous record. The sql query was not changed, only the call to query.Sql was (seel the calls below). DbFun.Core.Diagnostics+CompileTimeException: Cannot compile query with this inner exception reported:

 ---> code = 65535 (65535), message = System.Data.SQLite.SQLiteException (0x80004005): unknown error
Insufficient parameters supplied to the command
   at System.Data.SQLite.SQLiteStatement.BindParameter(Int32 index, SQLiteParameter param)
   at System.Data.SQLite.SQLiteStatement.BindParameters()
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at DbFun.Core.Builders.QueryBuilder.executePrototypeQuery[Result](CommandType commandType, String commandText, FSharpFunc`2 setParams, FSharpFunc`2 resultReaderBuilder)
   at DbFun.Core.Builders.QueryBuilder.Sql[Params,Result](FSharpFunc`2 template, FSharpFunc`2 paramSpecifier, FSharpFunc`2 resultSpecifier, String sourcePath, Int32 sourceLine)

The calls:

        query.Sql
            (sql,
             Params.Record<
                 {| hoster: string
                    user: string
                    repo: string
                    request: string
                    overLimit: bool |}
              >(),

             Results.Unit)
            {| hoster = hoster
               user = user
               repo = repo
               request = request
               overLimit = overLimit |}
        |> Sqlite.run

        query.Sql
            (sql,
              Params.Tuple<string, string, string, string, bool>("hoster", "user", "repo", "request", "overLimit"),
               Results.Unit)
            (hoster, user, repo, request, overLimit)

        |> Sqlite.run

[jacentino]

I prepared similar example and couldn't reproduce the issue. Can you please send me SQL command and table definition?

How do you represent bool value in the database? As INTEGER? If so, then your parameter will be coerced, but to read data you need a converter:

let config = QueryConfig.Default(createConnection).AddRowConverter(fun (i: int) -> i <> 0)

[rbauduin]

Thanks for having a look. Here is how I can reproduce it. Edit: while reproducing, i saw it's the number of parameters (hence the number of tuple fields) that seem to cause the trouble. I removed one text parameter (request), and it worked fine. 5 parameters don't work, 4 do.

Create the db:

cat <<EOF |
create table logs(id INTEGER PRIMARY KEY, hoster text, user text, repo text,time text DEFAULT CURRENT_TIMESTAMP, request text, over_limit bool);
EOF
  sqlite3 db.sqlite

then execute (dotnet fsi demo.fsx) the demo script below:

#r "nuget: DbFun.Core, 1.1.0"
#r "nuget: System.Data.SQLite, 1.0.119"

open DbFun.Core
open DbFun.Core.Builders
open System.Data
open System.Data.SQLite
open DbFun.Core.Sqlite
(*
cat <<EOF |
create table logs(id INTEGER PRIMARY KEY, hoster text, user text, repo text,time text DEFAULT CURRENT_TIMESTAMP, request text, over_limit bool);
EOF
sqlite3 db.sqlite
*)

let createConnection () : IDbConnection =
    new SQLiteConnection($"""Data Source=db.sqlite""")

let config = QueryConfig.Default(createConnection).SqliteDateTimeAsString()
let query = QueryBuilder(config)
let run f = DbCall.Run(createConnection, f)

let sql =
    "insert into logs(hoster,user, repo, request, over_limit) VALUES (@hoster, @user, @repo, @request,@overLimit)"

let hoster = "github"
let user = "jacentino"
let repo = "DbFun"
let request = "http"

async {

    do!
        query.Sql
            (sql,
             Params.Tuple<string, string, string, string, bool>("hoster", "user", "repo", "request", "overLimit"),
             Results.Unit)
            (hoster, user, repo, request, false)

        |> run

    return 0
}
|> Async.RunSynchronously

This gives the error

DbFun.Core.Diagnostics+CompileTimeException: Cannot compile query in /tmp/dbfun/demo.fsx, line: 36
 ---> code = 65535 (65535), message = System.Data.SQLite.SQLiteException (0x80004005): unknown error
Insufficient parameters supplied to the command
   at System.Data.SQLite.SQLiteStatement.BindParameter(Int32 index, SQLiteParameter param)
   at System.Data.SQLite.SQLiteStatement.BindParameters()
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at DbFun.Core.Builders.QueryBuilder.executePrototypeQuery[Result](CommandType commandType, String commandText, FSharpFunc`2 setParams, FSharpFunc`2 resultReaderBuilder) in C:\Projekty\FSharp\DbFun\DbFun.Core\Query.fs:line 155
   at DbFun.Core.Builders.QueryBuilder.Sql[Params,Result](FSharpFunc`2 template, FSharpFunc`2 paramSpecifier, FSharpFunc`2 resultSpecifier, String sourcePath, Int32 sourceLine) in C:\Projekty\FSharp\DbFun\DbFun.Core\Query.fs:line 286
   --- End of inner exception stack trace ---
   at DbFun.Core.Builders.QueryBuilder.handleException[a,b](String sourcePath, Int32 sourceLine, Exception ex) in C:\Projekty\FSharp\DbFun\DbFun.Core\Query.fs:line 200
   at DbFun.Core.Builders.QueryBuilder.Sql[Params,Result](FSharpFunc`2 template, FSharpFunc`2 paramSpecifier, FSharpFunc`2 resultSpecifier, String sourcePath, Int32 sourceLine) in C:\Projekty\FSharp\DbFun\DbFun.Core\Query.fs:line 291
   at FSI_0002.it@35.Invoke(Unit unitVar) in /home/dotdev/tmp/dbfunbug/demo.fsx:line 35
   at Microsoft.FSharp.Control.AsyncPrimitives.CallThenInvoke[T,TResult](AsyncActivation`1 ctxt, TResult result1, FSharpFunc`2 part2) in D:\a\_work\1\s\src\FSharp.Core\async.fs:line 510
   at Microsoft.FSharp.Control.Trampoline.Execute(FSharpFunc`2 firstAction) in D:\a\_work\1\s\src\FSharp.Core\async.fs:line 112
--- End of stack trace from previous location ---
   at Microsoft.FSharp.Control.AsyncResult`1.Commit() in D:\a\_work\1\s\src\FSharp.Core\async.fs:line 454
   at Microsoft.FSharp.Control.AsyncPrimitives.QueueAsyncAndWaitForResultSynchronously[a](CancellationToken token, FSharpAsync`1 computation, FSharpOption`1 timeout) in D:\a\_work\1\s\src\FSharp.Core\async.fs:line 1139
   at Microsoft.FSharp.Control.AsyncPrimitives.RunSynchronously[T](CancellationToken cancellationToken, FSharpAsync`1 computation, FSharpOption`1 timeout) in D:\a\_work\1\s\src\FSharp.Core\async.fs:line 1166
   at Microsoft.FSharp.Control.FSharpAsync.RunSynchronously[T](FSharpAsync`1 computation, FSharpOption`1 timeout, FSharpOption`1 cancellationToken) in D:\a\_work\1\s\src\FSharp.Core\async.fs:line 1515
   at <StartupCode$FSI_0002>.$FSI_0002.main@() in /home/dotdev/tmp/dbfunbug/demo.fsx:line 33
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
   at System.Reflection.MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)

[jacentino]

This issue has been fixed in DbFun 1.2.0, that's why I couldn't reproduce it. Just upgrade to latest version and it will work as expected :-)

[rbauduin]

I hadn't realised I wasn't on the last release. Thanks for the rapid feedback!