SQLite Forum

Insufficient parameters supplied to the command with ?NNN style

Insufficient parameters supplied to the command with ?NNN style

(1) By Florian (Flohack) on 2024-05-07 16:48:08 [source]


on System.Data.SQLite. We want to switch from ? anonymous parameters to indexed ones:

  • Create a query string with multiple ?1, ?2, ?3 parameter placeholders
  • Create parameters with param = command.CreateParameter()
  • Assign param.ParameterName = "?1" and so on

This results in the exception from the title. I have no clue and did not find any documentation about that. Also setting a null string for the parameter name does not help.

Why do we need it? We want to change a sequence of delete and insert commands to "upsert" style, improving performance. So suddenly we need the ability to reuse a parameter multiple times in the same query. Suggestions welcome!

BR Florian

(2) By Stephan Beal (stephan) on 2024-05-07 16:53:36 in reply to 1 [link] [source]

Suggestions welcome!

Your chances of getting helpful responses will skyrocket if you will show the actual code which is producing the error, rather than summarizing what you've done.

(3) By Florian (Flohack) on 2024-05-08 08:52:57 in reply to 2 [link] [source]

HI, sure...

So what I am trying to do is with the simplest statement in our code:

IDbCommand command = sqliteConnection.CreateCommand();
command.CommandText = "SELECT value FROM tcProperty WHERE name = ?1 OR name2 = ?1";
IDbDataParameter param = command.CreateParameter();
param.ParameterName = "?1";
param.Value = "SomeProperty";
return command.ExecuteScalar();

So I want to reuse a given parameter multiple times. But even if I just run

command.CommandText = "SELECT value FROM tcProperty WHERE name = ?1";

it wont work:

  Message=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.ExecuteScalar(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteScalar()

(4) By mistachkin on 2024-05-08 19:04:40 in reply to 3 [link] [source]

I think use of the question mark may be causing issues here.  Please try
using something like the following:

    param.ParameterName = ":1";

(5) By Florian (Flohack) on 2024-05-10 12:32:17 in reply to 4 [link] [source]


unfortunately this does not seem to work. Also ":p1" does not work in this case.

Thats strange, in case of an ?NNN parameter, should it not just take the corresponding entry out of the command.Parameters list, and not rely on a name? I understand that named parameters need a matching name,but documentation says that ?NNN is a more advanced use of single ? placeholder, and still kinda anonymous.

(6) By Florian (Flohack) on 2024-05-10 12:41:59 in reply to 4 [link] [source]

Hi again,

it actually fails to map the parameter in MapParameter(string s, SQLiteParameter p) - I just debugged that. I do not fully understand the code there, but it seems it cannot match ?1 from query string and any other parameter name, regardless of what the name is.

(7) By Florian (Flohack) on 2024-05-10 13:07:51 in reply to 6 [link] [source]

I guess the cause is that ? parameters are categorized as anonymous, so they are subsituted with ;NNN names. But, the logic in _sql.Bind_ParamName(this, _flags, i + 1) does not return null when ?NNN is being used.

That said, later on the question mark is not considered as a valid parameter prefix in MapParameter, so that mapping fails then.

(8) By mistachkin on 2024-05-10 19:47:47 in reply to 7 [link] [source]

Thanks for the additional information.  It is possible that the current code
does not support using the same named parameter more than once.  I'll try to
address this before the pending release.

(9) By Florian (Flohack) on 2024-05-10 22:19:28 in reply to 8 [link] [source]

Well again may I point out that the ones in style ?NNN are still anonymous parameters, but with their index attached. Maybe the reasoning why this is so important for us: Named parameters are much slower to bind, and we want to avoid using them, if possible. So its all not about havving same named parameter, but same anonymous parameter :)

So if using ?NNN style parameters does not work at all for managed sqlite, we will just use ? again, and copy/paster parameter assignment multiple times across the query setup. Thats not optimal, but probably the only way to keep the performance high.

(10) By mistachkin on 2024-05-12 03:19:30 in reply to 9 [link] [source]

I'm working on the fix now, please see:


Are you able to build from source? If so, I would appreciate it if you could check if the proposed fix works for your use case?

(11) By Florian (Flohack) on 2024-05-12 16:58:34 in reply to 10 [link] [source]


I´ve never tried to build from source, with some instructions I might be able to do so, yes. Can you point me to some resource?

BR Florian

(12.4) By Aask (AAsk1902) on 2024-05-12 19:07:51 edited from 12.3 in reply to 11 [link] [source]

... with some instructions I might be able to do so

I downloaded the ZIP Archive from here, extracted the ZIP to a new folder, opened SQLite.NET.2017.MSBuild.sln with Visual Studio, de-selected the Installer and test* builds and then recompiled.

This produces System.Data.SQLite.DLL v which is bound to SQLite Version 3.45.3 (I had expectations that it might be 3.46.0!) Full details:

PSPath            : Microsoft.PowerShell.Core\FileSystem::G:\Build_SQLiteSDS\bin\2017\Win32\ReleaseStatic\System.Data.SQLite.dll
PSParentPath      : Microsoft.PowerShell.Core\FileSystem::G:\Build_SQLiteSDS\bin\2017\Win32\ReleaseStatic
PSChildName       : System.Data.SQLite.dll
PSDrive           : G
PSProvider        : Microsoft.PowerShell.Core\FileSystem
PSIsContainer     : False
Mode              : -a----
VersionInfo       : File:             G:\Build_SQLiteSDS\bin\2017\Win32\ReleaseStatic\System.Data.SQLite.dll
                    InternalName:     SQLite.Interop
                    OriginalFilename: SQLite.Interop.dll
                    FileDescription:  System.Data.SQLite Interop Assembly
                    Product:          System.Data.SQLite
                    Debug:            False
                    Patched:          False
                    PreRelease:       False
                    PrivateBuild:     False
                    SpecialBuild:     False
                    Language:         English (United States)

BaseName          : System.Data.SQLite
Target            :
LinkType          :
Name              : System.Data.SQLite.dll
Length            : 1661440
DirectoryName     : G:\Build_SQLiteSDS\bin\2017\Win32\ReleaseStatic
Directory         : G:\Build_SQLiteSDS\bin\2017\Win32\ReleaseStatic
IsReadOnly        : False
Exists            : True
FullName          : G:\Build_SQLiteSDS\bin\2017\Win32\ReleaseStatic\System.Data.SQLite.dll
Extension         : .dll
CreationTime      : 12/05/2024 18:57:02
CreationTimeUtc   : 12/05/2024 17:57:02
LastAccessTime    : 12/05/2024 18:57:06
LastAccessTimeUtc : 12/05/2024 17:57:06
LastWriteTime     : 12/05/2024 18:57:06
LastWriteTimeUtc  : 12/05/2024 17:57:06
Attributes        : Archive

See this thread. for questions that occurred to me.

PS: Unable to help with this as I have no idea how table tcProperty is defined.

(13.1) By Florian (Flohack) on 2024-05-13 07:41:06 edited from 13.0 in reply to 12.4 [link] [source]


which VS version are you using? With my VS 2022 I hit the error from here and I tried various things, to no avail.

(14.1) By Aask (AAsk1902) on 2024-05-13 09:06:00 edited from 14.0 in reply to 13.1 [link] [source]

Follow these steps with VS2017 or later.

  1. There are 12 projects in this solution. Each project has its own (*.csproj file) reference to a Framework.

  2. When a referenced Framework does not exist on your machine, the simplest solution is to download the missing Framework. If you change the referenced Framework to a Framework that exists on your machine, you need to apply that change to all 12 projects.

  3. At the download page you will notice that the precompiled binaries are available for Frameworks up to 4.6.

    • I compiled with Framework 4.6 successfully
    • And then again with Framework 4.7 successfully
  4. If you hit errors, chances are that the errors are spurious: there is a build order for the 12 projects. If you hit an error with one, some of the errors reported relate to subsequent projects - hence spurious.

(15) By Florian (Flohack) on 2024-05-13 10:28:57 in reply to 14.1 [link] [source]

Seems I got it to work at least one time every time I delete obj and bin folders :D - But now it complains about a mismatching Interop dll, and that one, even being green in the build, is nowhere to be found.

I also tried the static target, but that complains then about a strong name discrepancy.

(16.1) By Aask (AAsk1902) on 2024-05-13 11:42:11 edited from 16.0 in reply to 15 [link] [source]

It seems you are caught up in a double jeopardy.

  • Do not try things ad doc
  • Visual Studio is not a homogeneous entity: each installation is subject to customization (user preferences & components installed). Do not be tempted to build any projects at random or selectively.

This solution has 12 projects and there is a build order (therefore a dependency order) for the projects.

Step 1: Download the source files; on completion, right click on the file, select Properties and check the Unblock box and click the Apply button.

13/05/2024  11:07       130,322,595 System.Data.SQLite-1764d76cee.zip

Step 2: Right click on the file and select Extract All to a location of our choice.

Step 3: At your location you will have a folder named System.Data.SQLite-1764d76cee. Expand the folder and double click on SQLite.NET.2017.MSBuild.sln to open the solution in Visual Studio.

Step 4: In Visual Studio, click Build | Batch Build. Click the Select All button then click the Clean button. Do this instead of manually clearing any bin or obj locations.

Step 5: On completion, click Build | Batch Build. Un-check the first TWO (names starting with Installer) and last SIX projects (names starting with test)

Step 6: Click Build | Batch Build then click the Rebuild button. This will take a good while: you can follow progress in the Output Window On completion you will see (hopefully)

========== Rebuild All: 28 succeeded, 0 failed, 0 skipped ==========

I use just one DLL, from this location:


With my build:

 Directory of G:\Build_SQLiteSDS\System.Data.SQLite-1764d76cee\bin\2017\Win32\ReleaseStatic

13/05/2024  12:18         2,097,152 System.Data.SQLite.dll
               1 File(s)      2,097,152 bytes

In your client project, add a reference to this DLL and set the Copy Local to True.

NOTE: While writing this, I have re-followed the guidance above implicitly to ensure that you experience the same outcome as myself. If there are surprises, then they may be due to either you not following the steps precisely or, more likely, differences in the configuration of your Visual Studio.

(17.2) By mistachkin on 2024-05-13 18:18:38 edited from 17.1 in reply to 16.1 [link] [source]

EDIT #1: Made example commands more complete.

Alternatively, open a Visual Studio Command Window, and use the following

    MKDIR "%TEMP%\build-for-sds"
    CD /D "%TEMP%\build-for-sds"

    REM Download the following files using wget, curl, etc, into the %CD%
    REM directory, which should be "%TEMP%\build-for-sds", per the above.
    REM "https://urn.to/r/unzip" (unzip EXE for Win32)
    REM "https://system.data.sqlite.org/index.html/zip/pst-76cb35b58d.zip?uuid=pst-76cb35b58d"
    REM Please note that the "unzip.exe" downloaded here should show up as
    REM being digitally signed by "Mistachkin Systems".

    .\unzip.exe pst-76cb35b58d.zip
    CD .\pst-76cb35b58d\Setup

    CALL build.bat DebugManagedOnly
    CALL build.bat DebugNativeOnly

(18) By Aask (AAsk1902) on 2024-05-13 17:58:32 in reply to 17.0 [link] [source]


build.bat DebugNativeOnly

with the following outcome:

  Deleting file "..\obj\2017\Win32\DebugNativeOnlyStatic\SQLite.I.490CBC51.tlog\unsuccessfulbuild".
  Touching "..\obj\2017\Win32\DebugNativeOnlyStatic\SQLite.I.490CBC51.tlog\SQLite.Interop.Static.2017.lastbuildstate".
Done Building Project "G:\Build_SQLiteSDS\System.Data.SQLite-1764d76cee\SQLite.Interop\SQLite.Interop.Static.2017.vcxpr
oj" (Rebuild target(s)).

Done Building Project "G:\Build_SQLiteSDS\System.Data.SQLite-1764d76cee\SQLite.Interop\SQLite.Interop.Static.2017.vcxpr
oj.metaproj" (Rebuild target(s)).

Done Building Project "G:\Build_SQLiteSDS\System.Data.SQLite-1764d76cee\SQLite.NET.2017.MSBuild.sln" (Rebuild target(s)

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:01:25.78

Build success, no errors were encountered.

If only this was documented online - I did not find it.

I assume that for the Release builds, I simply replace DebugNativeOnly by ReleaseNativeOnly. Correct?

(19) By mistachkin on 2024-05-13 18:14:02 in reply to 18 [link] [source]


Also, the build process is documented online:


That being said, it could certainly stand to be updated and fleshed out.

(20.1) By Aask (AAsk1902) on 2024-05-14 02:04:45 edited from 20.0 in reply to 17.2 [link] [source]

Thank you.

I've noticed that sqlite3.c (the amalgamation version) is in the download as is interrop.c.

I can download sqlite3.c from the amalgamations published. What about interrop.c? ... there must be an automated process.

Is it done this way:

"Creating primary interop assemblies manually in source code by using a language that is compliant with the Common Language Specification (CLS), such as C#. This approach is useful when a type library is unavailable."

Or created from a TLB file?

Or, else, how?

Thanks for sharing your insights.

(21) By mistachkin on 2024-05-14 04:59:29 in reply to 20.1 [link] [source]

I'm not sure what you are asking about.  The "interop.c" file is just
another C source code file.  It is not generated.  It is available in
all the source code archives and release tags.

(22) By Aask (AAsk1902) on 2024-05-14 05:35:11 in reply to 21 [link] [source]

As I understand, interop.c provides the signature of API calls in sqlite3.c.

If I am correct, and if a new API is introduced in the library or an existing API changes (quite unlikely), then interop.c becomes out of date. In this scenario if I compile system.data.sqlite.dll using the new shell.c, the new/altered API will not figure in SDS.

In other words, if I re-compile SDS with the current or next release of SQLite (the release of pre-compiled SDS binaries tends to lag behind SQLite releases) I would need both interop.c and shell.c.

  • I am not sufficiently C acquainted to be able to manage C source files.
  • SQLite amalgamation releases do not include interop.c

Hence my question: can interop.c be generated from shell.c?

(23) By mistachkin on 2024-05-14 15:08:41 in reply to 22 [link] [source]

No, "interop.c" cannot be generated.  It is a source code file unique to
the System.Data.SQLite project and can be downloaded from that web site.

It is designed to "#include" several other source code files, including
the "sqlite3.c" file and several optional extensions.

(24) By Aask (AAsk1902) on 2024-05-14 20:08:18 in reply to 23 [link] [source]

Thank you for the clarifications and update.

In the timeline, the check-in, say, https://system.data.sqlite.org/index.html/info/2d2cbf56030d8275 navigates to a page where downloads are available.

  1. Do those downloads have the System.Data.SQLite version updated? That is, is the version different from the version of the prior pre-compiled binaries release?

  2. Is it possible to set the version when compiling any such download? How?

The idea is to set a version that will make it obvious that it is a user-compiled version. For example, if the version in the prior pre-compiled binary release is and I compile the source code, it might be handy to set the version to, say, to indicate that it is not the pre-compiled version.

Thaks for sharing our insights.

(25.1) By mistachkin on 2024-05-14 20:40:08 edited from 25.0 in reply to 24 [link] [source]

1. The versions in the repository may or may match those in the pre-compiled
   binaries, depending on which check-in is used as the basis for downloading
   the linked source code archives.

2. Yes, by editing the necessary source code files, as described in the build
   steps I posted earlier on this thread.

(26) By Aask (AAsk1902) on 2024-05-14 21:22:43 in reply to 25.1 [link] [source]

Thank you.

While the pre-compiled binaries are a sound bonus for developers but knowing how to create them from source code empowers them further - thanks for sharing your insights. Much appreciated.

(28) By Florian (Flohack) on 2024-05-15 09:04:00 in reply to 16.1 [link] [source]


then it must be differences in Visual Studio. I am using Microsoft Visual Studio Professional 2022 (64-bit) - Current Version 17.9.6 and I followed all your steps precisely, only leading into the same problem again that it complains about missing framework reference. Note that I have installed SDKs for 4.7, 4.7.1 and 2.

I then cleaned obj folders away, and could switch to Debug/Any CPU build and get a green build for the 2 dlls needed.

But for the moment its not an issue for me, I could verify the fix by mistachkin and so hoping the best for the next release :)

(27) By Florian (Flohack) on 2024-05-15 08:22:40 in reply to 10 [link] [source]

Hello, finally got it to build...

It seems to work now, at least a query that uses ?1 two times returns the correct result. I also verified that the parameters are still anonymous, i.e. setting a parameter name other than ?1 does still work.