Support binding user-defined values to statements

[pchx]

Support binding user-defined values to statements

[pchx]

My use case is just to use an extension written in C from Dart, but I guess for completeness you would want to be able to consume/return such values from Dart extensions.

In my case there is a C API that knows how to generate such values, so all I need is to get a sqlite_stmt* to pass into that API.

[simolus3]

I'm not entirely opposed to this, but I'm not super convinced this feature should be part of the sqlite3 package either. In my mind, that package provides a direct wrapper around sqlite3 that looks like Dart. So we have an OOP API based on sound types, but we don't have stuff that sqlite3 doesn't support either (like creating CRUD statements automatically or a direct transaction API). Adding Bindable isn't much, but it also feels a lot like something that a higher-level package should do.

Do you have an example where it's considerably more convenient to use Bindable instead of just doing the translation manually? The way I see it, Bindable.Bind would just recursively call stmt.bind to set the translated value at the same index? Are you using a sqlite3 library for another language that has that feature?

[pchx]

Sorry, I should have made this clearer: by user-defined value, I mean the kind specified in the sqlite pointer passing interface. For example, a user-defined function might accept a special pointer type containing data from the application.

From what I can tell, there is no way to bind a user-defined value to a statement at all: you must pass all parameters when you call execute(), select(), etc. so you can't just invoke sqlite3_bind_pointer on the raw handle beforehand.

[simolus3]

Ah yeah, that's fair. I wasn't aware of that functionality in sqlite3, but I think we can add basic support for it. (Basic in the sense that it's going to be really low-level, I think even exposing the type pointer as a Dart string would be wrong given how much the document talks about how types must not be dynamic values)

Do you also need an API to read pointer values as arguments from user-defined Dart functions? Or is this just about accessing existing extensions using that mechanism?

[simolus3]

My use case is just to use an extension written in C from Dart, but I guess for completeness you would want to be able to consume/return such values from Dart extensions.

I agree that a complete solution should have that. But given how rarely the feature is used and how poorly it translates to Dart with dynamic strings, I think it might be better to not have support for pointer passing interfaces at all.

From what I can tell, there is no way to bind a user-defined value to a statement at all: you must pass all parameters

Yes, the intention here is to avoid bugs caused by parameters that haven't been bound.

To support your use case, I've followed a different approach in d3382c5. It adds a StatementParameters class which explicitly controls the way parameters are bound. This validation can be completely bypassed by using StatementParameters.bindCustom:

stmt.executeWith(StatementParameters.bindCustom((stmt) {
  stmt as PreparedStatement;

  callMyNativeFunction(stmt.handle); // stmt.handle is a sqlite_stmt*
}));

[pchx]

Thanks! I expect many users of this feature would commonly pass a mix of standard and custom types, but this certainly works and is easiest to implement.

Appreciate the quick turnaround. :)

[pchx]

Just noticed this was committed to the v2 branch and not main, is v2 considered suitable for production use?

[simolus3]

Right, I've pushed the change on the v2 branch which will become the next stable release but has some features that are still in development.

I might have to make breaking changes before the final release. But apart from that, the FFI implementation on the v2 branch is working and I consider it suitable for production if you depend on a fixed version (e.g. sqlite3: 2.0.0-dev.0). If breaking changes happen you'd have to migrate eventually, but so do users who are currently on 1.x of this package.

The WASM implementation on the v2 branch is not stable yet.

[pchx]

Just as a single point of feedback:

I've tried working with this a little bit and think the original proposal may be slightly more usable -- otherwise to bind both custom and standard types in the same statement, you need to obtain a RawSqliteStatement in order to call sqlite3_bind_*.

[simolus3]

I've added a CustomStatementParameter class in 5ddbf8c. It can be used to mix the two modes. So you can do something like execute([123, MyCustomValue()]) where MyCustomValue implements CustomStatementParameter and calls the C function with the statement handle. But there's no need to manually call sqlite3_bind for 123 anymore.

[pchx]

Thank you, excited to try this out!