Hello,

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

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.

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";
command.Parameters.Add(param);
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:

System.Data.SQLite.SQLiteException
  HResult=0x80004005
  Message=unknown error
Insufficient parameters supplied to the command
  Source=System.Data.SQLite
  StackTrace:
   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()

Hi,

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.

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.

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.

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.

Hi,

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

... 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 1.0.119.0 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
                    FileVersion:      1.0.119.0
                    FileDescription:  System.Data.SQLite Interop Assembly
                    Product:          System.Data.SQLite
                    ProductVersion:   1.0.119.0
                    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.

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.

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.

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:

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

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.

Tried:

build.bat DebugNativeOnly

with the following outcome:

FinalizeBuildStatus:
  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?

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.

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?

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 1.0.119.0 and I compile the source code, it might be handy to set the version to, say, 1.0.119.1 to indicate that it is not the pre-compiled version.

Thaks for sharing our insights.

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.

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.

Hi,

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 :)