This isn't an insult but why does this module exists? What's the reason you started to create it? I migrated my code base to rusqlite, and I noticed no more panics -- this issue doesn't exist, #67

Moreoever, this other issue about reading into a Vec also doesn't exist in rusqlite,

-pub fn read_vecu8( row: &sqlite::Row, col: &str ) -> Vec<u8> {
-       row[col].clone().try_into().unwrap()
+pub fn read_vecu8( row: &rusqlite::Row, col: &str ) -> Result<Vec<u8>, CookieError> {
+       let val: Vec<u8> = row.get(col)?;
+       Ok( val )
 }

You can see row[col].clone().try_into().unwrap() goes away and just becomes let val: Vec<u8> = row.get(col)?; #76

Not saying this is a bad module, I just don't understand how it's different from rusqlite

How can I use binding for data insertion, rather than data collection.

Something like:

"INSERT INTO person (name, time_created, data)
                  VALUES (?1, ?2, ?3)",
        me.name, me.time_created, me.data,

I'd expect rows that contain data in the specified column to return that and the others simply fail but all the rows fail during serialization for the specified column

                 CREATE TABLE IF NOT EXISTS current 
                 (user VARCHAR(50) ,
                 admin VARCHAR(50),
                 price FLOAT,
                 new_credit FLOAT,
                 card_id INT,
                 OP VARCHAR(10),
                 table_id VARCHAR(50),
                 game_id INT,
                 game_name VARCHAR(20),
                 winning_type varchar(20) not null default '',  #if theres no default here it fails the resulting rows are always empty during the serialization even if theres data
                 winning_amount float not null default 0.0,
                 op_time INT default (CURRENT_TIMESTAMP) )

Hello. When I'm trying to read a text column using the following code I get the error "cannot read a text column"

pub fn get(&self, key: String) -> String {
        let stmt = self.conn.prepare(format!("SELECT * FROM data WHERE key = '{}';", key)).unwrap();

        stmt.read::<String>(1).unwrap()
}

Am I doing anything wrong?

I've had a hard time nailing down what the problem is with this, but at very least, I've gotten something that reproduces the problem somewhat.

Doesn't seem to be anything in the code, and it does the same thing regardless whether you use a disk-backed database, or an in-memory based database.

But it only happens when you run a binary under memusage

# main.rs
use sqlite;

fn main() {
    let connection = sqlite::open(":memory:").unwrap();
    println!("Hello, world!");
}

# valgrind target/debug/sqlite-mfr

==18989== Memcheck, a memory error detector
==18989== Copyright (C) 2002-2017, and GNU GPL'd, by Julian Seward et al.
==18989== Using Valgrind-3.16.1 and LibVEX; rerun with -h for copyright info
==18989== Command: target/debug/sqlite-mfr
==18989== 
Hello, world!
==18989== 
==18989== HEAP SUMMARY:
==18989==     in use at exit: 0 bytes in 0 blocks
==18989==   total heap usage: 298 allocs, 298 frees, 76,130 bytes allocated
==18989== 
==18989== All heap blocks were freed -- no leaks are possible
==18989== 
==18989== For lists of detected and suppressed errors, rerun with: -s
==18989== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 0 from 0)

# memusage target/debug/sqlite-mfr
/usr/bin/memusage: line 253: 19481 Segmentation fault      LD_PRELOAD=/\$LIB/libmemusage.so "$@"

# memusage gdb --args target/debug/sqlite-mfr
# run
Program received signal SIGSEGV, Segmentation fault.
__memset_sse2_unaligned_erms () at ../sysdeps/x86_64/multiarch/memset-vec-unaligned-erms.S:192
192		VMOVU	%VEC(0), -VEC_SIZE(%rdi,%rdx)
# bt full
#0  __memset_sse2_unaligned_erms () at ../sysdeps/x86_64/multiarch/memset-vec-unaligned-erms.S:192
No locals.
#1  0x00007ffff7cebb66 in memset (__len=<optimized out>, __ch=0, __dest=0x5555555a7b30) at /usr/include/bits/string_fortified.h:71
No locals.
#2  rehash (new_size=4293843945, pH=0x5555555a4c98) at sqlite3.c:33016
        new_ht = 0x5555555a7b30
        elem = <optimized out>
        next_elem = <optimized out>
#3  sqlite3HashInsert (pH=pH@entry=0x5555555a4c98, pKey=0x5555555a7ac8 "fts5_expr", data=data@entry=0x5555555a7a80) at sqlite3.c:33145
        h = 0
        elem = <optimized out>
        new_elem = 0x5555555a7af0
#4  0x00007ffff7cf215f in sqlite3FindFunction (db=db@entry=0x5555555a4a60, zName=zName@entry=0x7ffff7dbb1e8 "fts5_expr", nArg=nArg@entry=-1, 
    enc=enc@entry=1 '\001', createFlag=createFlag@entry=1 '\001') at sqlite3.c:116005
        pOther = <optimized out>
        z = 0x5555555a7ad1 ""
        p = <optimized out>
        pBest = 0x5555555a7a80
        bestScore = <optimized out>
        h = <optimized out>
        nName = 9
#5  0x00007ffff7cfed97 in sqlite3CreateFunc (db=db@entry=0x5555555a4a60, zFunctionName=zFunctionName@entry=0x7ffff7dbb1e8 "fts5_expr", nArg=nArg@entry=-1, 
    enc=enc@entry=1, pUserData=pUserData@entry=0x5555555a7760, xSFunc=xSFunc@entry=0x7ffff7d46c82 <fts5ExprFunctionHr>, xStep=0x0, xFinal=0x0, xValue=0x0, 
    xInverse=0x0, pDestructor=0x0) at sqlite3.c:162920
        p = <optimized out>
        nName = <optimized out>
        extraFlags = 2097152
#6  0x00007ffff7d130b3 in createFunctionApi (db=db@entry=0x5555555a4a60, zFunc=0x7ffff7dbb1e8 "fts5_expr", nArg=nArg@entry=-1, enc=enc@entry=1, 
    p=p@entry=0x5555555a7760, xSFunc=0x7ffff7d46c82 <fts5ExprFunctionHr>, xStep=0x0, xFinal=0x0, xValue=0x0, xInverse=0x0, xDestroy=0x0) at sqlite3.c:162986
        rc = 1
        pArg = 0x0
#7  0x00007ffff7d130d6 in sqlite3_create_function (db=db@entry=0x5555555a4a60, zFunc=<optimized out>, nArg=nArg@entry=-1, enc=enc@entry=1, 
    p=p@entry=0x5555555a7760, xSFunc=<optimized out>, xStep=0x0, xFinal=0x0) at sqlite3.c:163014
No locals.
#8  0x00007ffff7d1709f in sqlite3Fts5ExprInit (db=0x5555555a4a60, pGlobal=0x5555555a7760) at sqlite3.c:215601
        p = 0x7fffffffca50
        aFunc = {{z = 0x7ffff7dbb1e8 "fts5_expr", x = 0x7ffff7d46c82 <fts5ExprFunctionHr>}, {z = 0x7ffff7dbb1f2 "fts5_expr_tcl", 
            x = 0x7ffff7d46c6f <fts5ExprFunctionTcl>}, {z = 0x7ffff7dbb200 "fts5_isalnum", x = 0x7ffff7cf3e19 <fts5ExprIsAlnum>}, {
            z = 0x7ffff7dbb20d "fts5_fold", x = 0x7ffff7cf3dad <fts5ExprFold>}}
        i = 0
        rc = 0
        pCtx = 0x5555555a7760
#9  fts5Init (db=0x5555555a4a60) at sqlite3.c:29244
        p = 0x5555555a7760
        rc = 0
        pGlobal = 0x5555555a7760
        fts5Mod = {iVersion = 3, xCreate = 0x7ffff7d97d8b <fts5CreateMethod>, xConnect = 0x7ffff7d97d68 <fts5ConnectMethod>, 
          xBestIndex = 0x7ffff7d34710 <fts5BestIndexMethod>, xDisconnect = 0x7ffff7d4828f <fts5DisconnectMethod>, 
          xDestroy = 0x7ffff7d8256e <fts5DestroyMethod>, xOpen = 0x7ffff7d8891d <fts5OpenMethod>, xClose = 0x7ffff7d498b7 <fts5CloseMethod>, 
          xFilter = 0x7ffff7d99f69 <fts5FilterMethod>, xNext = 0x7ffff7d999b5 <fts5NextMethod>, xEof = 0x7ffff7cda9a5 <fts5EofMethod>, 
          xColumn = 0x7ffff7d8a4ca <fts5ColumnMethod>, xRowid = 0x7ffff7ce8a3b <fts5RowidMethod>, xUpdate = 0x7ffff7d9c496 <fts5UpdateMethod>, 
          xBegin = 0x7ffff7d8890a <fts5BeginMethod>, xSync = 0x7ffff7d9bd23 <fts5SyncMethod>, xCommit = 0x7ffff7cda9c7 <fts5CommitMethod>, 
          xRollback = 0x7ffff7d46ce1 <fts5RollbackMethod>, xFindFunction = 0x7ffff7cdaa98 <fts5FindFunctionMethod>, 
          xRename = 0x7ffff7d9bc56 <fts5RenameMethod>, xSavepoint = 0x7ffff7d9bd55 <fts5SavepointMethod>, xRelease = 0x7ffff7d9bd69 <fts5ReleaseMethod>, 
          xRollbackTo = 0x7ffff7d46cc2 <fts5RollbackToMethod>, xShadowName = 0x7ffff7cdab11 <fts5ShadowName>}
        rc = <optimized out>
        pGlobal = <optimized out>
        p = <optimized out>
#10 sqlite3Fts5Init (db=0x5555555a4a60) at sqlite3.c:29310
No locals.
#11 0x00007ffff7da7ac6 in openDatabase (zFilename=0x5555555a4740 ":memory:", ppDb=0x7fffffffcbe8, flags=<optimized out>, zVfs=<optimized out>) at sqlite3.c:164379
        db = 0x5555555a4a60
        rc = 0
        isThreadsafe = <optimized out>
        zOpen = 0x5555555a47c4 ":memory:"
        zErrMsg = 0x0
        i = 1
#12 0x00007ffff7da7c49 in sqlite3_open_v2 (filename=<optimized out>, ppDb=<optimized out>, flags=<optimized out>, zVfs=<optimized out>) at sqlite3.c:164461
No locals.
#13 0x000055555555ae19 in sqlite::connection::Connection::open_with_flags (path=..., flags=...)
    at /home/kent/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlite-0.25.3/src/connection.rs:31
        raw = 0x0
#14 0x000055555555b199 in sqlite::connection::Connection::open (path=...) at /home/kent/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlite-0.25.3/src/connection.rs:24
No locals.
#15 0x000055555555a99a in sqlite::open (path=...) at /home/kent/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlite-0.25.3/src/lib.rs:304
No locals.
#16 0x000055555555b5e0 in sqlite_mfr::main () at src/main.rs:4
No locals.

It could be something in glibc, it could be something in sqlite, but seems a more likely bet is something is "off" in FFI.

I had to rebuild both with debug symbols to make it clearer what's happening here 😅

I don't know if you've already considered these, but handling NULL values would be more convenient if Bindable and Readable were also implemented for Option<T>, for appropriate types of T.

Similarly, it would be nice if Value implemented Bindable alongside Readable.

I can give a shot at implementing these if you're interested.

When running this MVE:

use std::error::Error;
use sqlite::Connection;

fn main() -> Result<(), Box<dyn Error>> {
  let dbname = "file::memory:?cache=shared";
  let connection = Connection::open(dbname)?;
  Ok(())
}

Without the sqlite3-src package in Cargo.toml all is well. But if I add to my dependencies:

sqlite3-src = { version = "0.4", features = ["bundled"] }

Observed outcome:

Running the same code will create file::memory:?cache=shared as a file instead as using it as a memory DB.

Expected outcome:

Do not create any file but rely on a memory DB whose cache is shared as detailed here.

It appears to me that you provide no way to enable extension loading. Trying to call sqlite3_sys::sqlite3_enable_load_extension myself, I find that the raw field of the Connection struct is private. I have an application for which I need to load the math extensions provided by sqlite. Perhaps I have overlooked something. Do you have any suggestions for accomplishing this or do I need to switch to rusqlite, which does provide a mechanism for enabling and loading extensions.

While working on #61, I found that clippy was very displeased by the implementation of ReadableWithIndex for Vec<u8>:

error: calling `set_len()` immediately after reserving a buffer creates uninitialized values
   --> src/statement.rs:559:13
    |
559 |             let mut buffer = Vec::with_capacity(count);
    |             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
560 |             buffer.set_len(count);
    |             ^^^^^^^^^^^^^^^^^^^^^
    |
    = help: initialize the buffer or wrap the content in `MaybeUninit`
    = help: for further information visit https://rust-lang.github.io/rust-clippy/master/index.html#uninit_vec
    = note: `#[deny(clippy::uninit_vec)]` on by default

Looking at how the buffer is used, I doubt it's an actual problem (it's immediately written into with ptr::copy_nonoverlapping, thus initializing it) but I am not well versed in the arts of unsafe and clippy considers this bad enough to warrant making it a deny lint, so I figured it would be worth reporting.

The Clippy site suggests using MaybeUninit.

hello, dev author, I use gcc-toolchain ( gcc-linaro-7.5.0-2019.12-rc1-x86_64_arm-linux-gnueabihf, linaro website ) build my demo, bui it fail.

The error message as:

my Cargo.toml example:

my guess

arm-linux-gnueabihf is armv7l gcc-toolchain, instructions are 32-bit, but this cargo library compiled sqllite is 64-bit, so the error tell me:

undefined reference to `fcntl64'
collect2: error: ld returned 1 exit status

similar problems：rusqlite#1180

last

Thank you very much for taking time out of your busy schedule to check this problem

As asked in the title, does sqlite support creation of, querying, etc virtual tables?

Hi..
I build my first "Hello-World" Flutter/Rust app that run smoothly with Android and iOS, I need to dive deeper, I know flutter already has sqlite plugin, but I want to try it with native code using rust.

What is the first step to be followed here, as seen in flutter, the first step is finding the location of the DataBase path:

// Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, "demo.db");

Is there something similar with this library?

Hello,
stupid question, but how i can load a simple sqlite-file?

i take a string or an Path and give this the open function, but he allways returns is_ok()->true, even the file not exists?
And tables he found not, if it exists or not.

thx

If a cursor is for a statement which has a literal wildcard in it (no binding '?') like the following, it returns all the matching rows as expected:

let mut no_bind = connection.prepare( "SELECT value from english where value like '%type'").unwrap();
let cur = no_bind.cursor();

However if the wild card is bound to the statement it returns less rows (often 0) but I've seen any number of rows in between.

let mut with_bind = connection.prepare( "SELECT value from english where value like ?").unwrap();
ok!(with_bind.bind(1, "%type"));
let cur = with_bind.cursor();

I've submitted pull request #2 for a failing test that reproduces it.

Calling the Row::try_read() function will panic if the input column does not exist. This seems to originate from the "debug_assert" in cursor.rs:

impl RowIndex for &str {
    #[inline]
    fn index(self, row: &Row) -> usize {
        debug_assert!(
            row.column_mapping.contains_key(self),
            "the index is out of range",
        );
        row.column_mapping[self]
    }
}

Seeing as this function is the more handle-able version of "Row::read", it would be great if this error could be propagated to the try_read for handling. I've only tested this with &str column indexes, but I'm sure the usize variant might be in the same position.

Currently, it is hard to track what goes in inside a single crate release.

I suspect that what it is in master goes is, but there is no proof of this, it is
possible to make a tag for each release?

Hey all, I believe there is a memory leak when opening a new connection fails:

https://github.com/stainless-steel/sqlite/blob/master/src/connection.rs#L28

The "ffi::sqlite3_open_v2" function returns immediately upon encountering an error (this is trivially reproducible with a file that does not exist). However, the correct behavior here is probably to invoke sqlite3_close() instead -- even on error.

From http://www.sqlite.org/c3ref/open.html:

"Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required."

I have a build setup using clang's leak-sanitizer, and this was flagged - but even without this setup, calling the "Connection::open" function in a loop with a bad path should should a process with always-increasing memory consumption.

I am using this crate in a project that adds rows with an auto-incremented id, but I need to update a single row without affecting other rows that have the same data excluding ID. I found out about SQLITE_ENABLE_UPDATE_DELETE_LIMIT but I don't see anything about this in this crate. What should I do from here?

can not use Connection struct at public.
want to able to use.

use sqlite::connection::Connection;

struct Foo {
    conn: sqlite::connection::Connection
}

@IvanUkhov

I've noticed that there are multiple SQLite libraries with quite similar approaches and active commits.

Maybe you could join a discussion for potential joining of efforts here: https://users.rust-lang.org/t/sqlite-libraries-potential-for-unification/8702

The trigger is just a workaround because you cannot use SELECT RAISE(...) directly:

sqlite> SELECT RAISE(FAIL, 'Boom');
Parse error: RAISE() may only be used within a trigger-program

#[test]
fn iter_error_count() {
    let conn = ok!(sqlite::open(":memory:"));
    ok!(conn.execute("CREATE TABLE foo(x)"));
    ok!(conn
        .execute("CREATE TRIGGER oops BEFORE INSERT ON foo BEGIN SELECT RAISE(FAIL, 'Boom'); END"));
    let mut stmt = ok!(conn.prepare("INSERT INTO foo VALUES (0) RETURNING rowid;"));
    let count = stmt.iter().count();
    assert_eq!(count, 0);
}

#[test]
fn iter_error_last() {
    let conn = ok!(sqlite::open(":memory:"));
    ok!(conn.execute("CREATE TABLE foo(x)"));
    ok!(conn
        .execute("CREATE TRIGGER oops BEFORE INSERT ON foo BEGIN SELECT RAISE(FAIL, 'Boom'); END"));
    let mut stmt = ok!(conn.prepare("INSERT INTO foo VALUES (0) RETURNING rowid;"));
    let last = stmt.iter().last();
    assert!(last.is_none());
}

Hi,

It seems out of the box the extenstion is not supported. I did try to use json1 and got error like function json_extract does not exists.

How can I build the library with json1 ext support for example?

Thanks

Consider the following (wrong) insert statement (

use sqlite::Connection;

fn main() {
    let conn = Connection::open("data.db").unwrap();
    //let _ = conn.execute("CREATE TABLE test(number INTEGER, name TEXT)");
    match conn.execute("INSERT INTO test(field) VALUES(1 \"text\")") {
        Ok(_) => {}
        Err(e) => {
            println!("{e}")
        }
    };
}

The error you get is

near ""text"": syntax error (code 1)

Which is a pretty confusing error message, Not too bad here but when you're formatting arguments it can be pretty bad

Ideally the error message should look something like this

INSERT INTO test(field) VALUES(1 "text")
                                 ^^^^^^ <- error near ""text"": syntax error (code 1)

Ideally if possible it should show the source code from which the query originated but I have no idea if that's possible

Please add a wrapper function for https://sqlite.org/c3ref/last_insert_rowid.html

[REMOVED]

Hi,

I'm trying to use this cargo and exexute "SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();" and it return an empty although tested on python and it returns result. Just wondering if I'm doing it wrong or the lib doesn't supprt such query.

fn check_size(){
    let connection = connectdb();
    let xx = connection.execute("SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();");
    println!("{:?}",xx);
    }

I have some code that updates a sqlite database which works perfectly with version 0.28.0 of the sqlite crate.

However, if I upgrade to the crate sqlite 0.29.0 and above, I get the following error:

error[E0061]: this function takes 1 argument but 2 arguments were supplied
--> src/main.rs:23:12
|
23 | db.bind(1,username)?;
| ^^^^ - -------- supplied 2 arguments
| |
| expected 1 argument

Is this a bug in the sqlite crate, or do I need to change the syntax I am using when using the latest versions of this crate?

Many thanks.

I have attached the code that I am using (in a text file to allow upload to git):
main.txt

Hey there! I'm a big fan of this crate (much simpler and cleaner than rusqlite), and I was wondering if you'd be interested in a PR implementing From<Option<T>> (where T: Into<Value>) and From<bool> for Value, as well as their TryFrom inverses.

I've already been using these semantics in some free helper functions:

• If an Option<T> holds a Some(T), perform the appropriate conversion into a Value on the T. If it holds None, instead yield a Value::Null.
• false becomes Value::Integer(0) and true becomes Value::Integer(1) (as recommended by the SQLite devs.)

What do you think?

I'm using macOS, and trying to compile for Android using the command

cargo build --target x86_64-linux-android --release

I got the error:

= note: /Users/../NDK/x86_64/bin/../lib/gcc/x86_64-linux-android/4.9.x/../../../../x86_64-linux-android/bin/ld: error: cannot find -lsqlite3
clang70: error: linker command failed with exit code 1 (use -v to see invocation)

I already have sqlite3 installed:

Hasans-Air:~ h_ajsf$ brew install sqlite
Warning: sqlite 3.26.0 is already installed and up-to-date
To reinstall 3.26.0, run `brew reinstall sqlite`

And it compiled smoothly for mac lib, using

cargo build --release

this is some code i used to debug what the problem was when reading the cookies database for chrome that other programs like navicat open fine

    // use platform_dirs::AppDirs;
    let app_dirs = AppDirs::new(Some(""), true).unwrap();
    let local = app_dirs.cache_dir; // basically finding %localappdata%
    let chrome_cookies = local.join("Google").join("Chrome").join("User Data").join("Default").join("Cookies");
    let connection = sqlite::open(chrome_cookies).unwrap();
    connection
        .iterate("SELECT * FROM meta", |pairs| {
            for &(column, value) in pairs.iter() {
                println!("{} = {}", column, value.unwrap());
            }
            true
        })
        .unwrap(); // this iter works so the file is able to be read
        
    // connection
    //     .iterate("SELECT * FROM thisdoesnotexist", |pairs| {
    //         for &(column, value) in pairs.iter() {
    //             println!("{} = {}", column, value.unwrap());
    //         }
    //         true
    //     })
    //     .unwrap(); // this would return a no such table error`

    connection
        .iterate("SELECT name,host_key FROM cookies", |pairs| {
            for &(column, value) in pairs.iter() {
                println!("{} = {}", column, value.unwrap());
            }
            true
        })
        .unwrap(); // this works so the cookies table is also able to be read

    connection
        .iterate("SELECT encrypted_value FROM cookies", |pairs| {
            for &(column, value) in pairs.iter() {
                println!("{} = {}", column, value.unwrap());
            }
            true
        })
        .unwrap(); // this is a blob that gives me this error: thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: Utf8Error { valid_up_to: 3, error_len: Some(2) }'}

this is where it says the error is: sqlite-0.25.3\src\connection.rs:267:49

looking at that error it seems that it is failing to parse it to utf8 but i am failing to understand why you would want to parse blobs as strings and not u8 values or at least give the option to parse as u8 values
not all blobs are strings and in this case would make the program panic due to this design decision

Hello, I was wondering how to prepare a statement. I followed the instructions but the new entries in the table are all NULL for some reason and I'd like to know what I did wrong.

        let query = "CREATE TABLE Matches (
            Challenger TEXT,
            Challenged TEXT,
            Match_time TEXT,
            Winner TEXT
        );";

This is the query used to create the table and this is how I insert values and this is how I prepare the statement.

        let query = "INSERT INTO Matches VALUES (:challenger, :challenged, :match_time, :winner);";
        let mut stmt = self.conn.prepare(query).unwrap();

        stmt.bind((":challenger", challenger)).unwrap();
        stmt.bind((":challenged", challenged)).unwrap();
        stmt.bind((":match_time", match_time)).unwrap();
        stmt.bind((":winner", "N/A")).unwrap();

        self.conn.execute(query).unwrap();
        println!("Created");

Everything goes through but I get null.

If I do it like this then it'll work.

        let query = format!(
            "INSERT INTO Matches VALUES ('{}', '{}', '{}', '{}');",
            challenger, challenged, match_time, "me"
        );
        self.conn.execute(query).unwrap();

Is there something I'm missing here?

Below is the full code. The version I'm using is the latest at the time of writing which is 0.30.4

use sqlite::{self, Connection as DbConn, Value};

pub struct Connection {
    conn: DbConn,
}

impl Connection {
    pub fn new() -> Self {
        let conn = sqlite::open("challenges.db").unwrap();
        let query = "CREATE TABLE Matches (
            Challenger varchar(255),
            Challenged varchar(255),
            Match_time varchar(255),
            Winner varchar(255)
        );";

        if let Err(_) = conn.execute(query) {
            println!("Table already exists.");
        } else {
            println!("Table doesn't exist. Creating.");
        }

        Self { conn }
    }

    pub fn new_challenge(&mut self, challenger: &str, challenged: &str, match_time: &str) {
        let query = format!(
            "INSERT INTO Matches VALUES ('{}', '{}', '{}', '{}');",
            challenger, challenged, match_time, "me"
        );
        self.conn.execute(query).unwrap();
    }
}

fn main() {
    let mut conn = Connection::new();
    conn.new_challenge("me", "you", "now");
}

Simple example:

    #[test]
    fn it_does_not_panic() {
        let conn = sqlite::open(":memory:").unwrap();
        conn.execute("create table tbl (x, y)").unwrap();
        conn.execute("insert into tbl (x, y) values (42, 43)")
            .unwrap();
        let st = conn.prepare("select x, y from tbl").unwrap();
        for row in st.into_iter().map(|row| row.unwrap()) {
            row.try_read::<i64, _>("z"); // <--- this will panic
        }
    }

Output:

thread 'cached_rpc::test::it_does_not_panic' panicked at 'the index is out of range', ~/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlite-0.30.4/src/cursor.rs:172:9

Does a safer read method exist? This might be a deliberate design decision, but regardless it would be good to think about offering a safer interface.

I would like to open a SQL Lite database in read-only immutable mode to bypass the lock that stops me from reading it. I don't see this supported but it sounds very useful.

https://www.sqlite.org/c3ref/open.html
https://stackoverflow.com/a/69574421/124486

Hello,

I recently discovered this problem. To better illustrate the problem, I created a repo of a minimal reproduction of the problem I found.

TL;DR: Using upsert inside Docker would fail. I have tried to compile SQLite from source too.

Here's the repo I mentioned earlier.

Thank you and have a nice day :)

How can i write and read bolb data.

Its a bit annoying to use the Cursor interface while also accessing column names. Column names are exposed by the base Statement but because it is consumed by into_cursor() you cannot hold references to column_names() across into_cursor().

Motivating example:

let s = conn.prepare("select * from dual")?;
let cols = s.column_names();
let c = s.into_cursor();
let err0505 = cols[1];

A simple solution would be to also expose column_names() on the cursor directly.

I'm having trouble getting column type information from Cursors:

let db = sqlite::open("db.sqlite").unwrap();
let statement = db.prepare("select * from users;").unwrap();
let mut cursor = statement.into_iter();

while let Some(Ok(row)) = cursor.next() {
    for column_index in 0..cursor.column_count() {
        let column_type = cursor.column_type(column_index);
        println!("col type: {:?}", column_type);
    }
}

The users table contains two columns. name is a string, and age is an integer. Instead this code prints None for every column.

Any help is much appreciated!

Inspired by this problem https://stackoverflow.com/q/77235264/124486

Currently you can't call,

row.read::<Vec<u8>, _>(col).into()

Because a Row must own the Vec<Values> and a Value must own the data. I would propose making a newtype of Option<> like MaybeRead<> and making a Row a Vec<MaybeRead<Value>> this would allow you to call something like,

row.read_owned::<Vec<u8>, _>(col).into()

Where could read_owned replace the Some(Value) in the MaybeRead<Value> with None using std::mem::take providing the owned type. The largest use case of selecting from a database is to move the data from this library into native Rust structs that represent that data. I feel this library's API falls short of making this reasonable and easy for this use case.

Greetings,
i'm trying to use a select in statement but don't seem to able to.

let mut cursor = conn
		.prepare("SELECT * FROM users WHERE age in (?)").     //  <-- carray
		.unwrap()
		.into_cursor();

cursor.bind(&[[Value::Integer(42),Value::Integer(50)]]).unwrap();   // <-- fails since expectation is Value not array/tuple

is this currently possible ?

I'm using in :memory: connection, it is possible to export the entire session as *.db file? Opening local *.db file & saving the data took so long. But processing the data in :memory: only take less than 2 minutes.

My data is over > 2M rows

Hello, I'm not sure if it's just me but I do not understand how to configure the layout after reading the help page. In the help is also said there is a default config above but I don't see it. It also says that if layout is set to -1 but I don't know what layout is. Could I be given an example? Thanks.

Will it work with Axum which is async web framework https://github.com/tokio-rs/axum ?

Thanks

Using sqlite version 0.32.0
Windows 11
rustc 1.75.0

I am trying to prepare a statement with an UPSERT clause in it. Connection.prepare(Statement); hangs up and never returns.

let connection = sqlite::open(":memory:").expect(ERROR_SQLITE_ACCESS);
let query = "UPSERT INTO data_last_update (file, sha, date) VALUES (?, ?, unixepoch())";
let mut statement = connection .prepare(query)?;
println!("Here");

The println! is never reached.
If I replace UPSERT with INSERT, everything works fine.

There are no version of SQLite installed on the computer.

I also read #70 and tried to apply sqlite3-sys or sqlite3-src with no success.

The bundled sqlite version is old and does not support unixepoch() which was added in version 3.38.0

This makes it easy to get sql errors between builds targetting native vs bundled library.

Great light weight library!
I'm trying to use share a Connection between threads (with sqlite::Connection::open_with_flags and flag set_full_mutex() which corresponds to the serialized multi-thread mode of sqlite3, which allows sharing a connection between threads - works well in C)
The sqlite connection is wrapped in an Arc and rustc gives the "(dyn FnMut(usize) -> bool + 'static) cannot be shared between threads safely" error with "note: required because of the requirements on the impl of Send for Arc<Connection>"
Am I missing something? The sqlite serialized mode is allowed in open_with_flags so it would be logical to allow sharing the connection between threads
Thanks!

Attempting to compile the final example with Rust 2018 fails:

use sqlite::Value;

let mut cursor = connection
    .prepare("SELECT * FROM users WHERE age > ?")
    .unwrap()
    .cursor();

cursor.bind(&[Value::Integer(50)]).unwrap();

while let Some(row) = cursor.next().unwrap() {
    println!("name = {}", row[0].as_string().unwrap());
    println!("age = {}", row[1].as_integer().unwrap());
}

The error is on the line while let Some(row) = cursor.next().unwrap() and the compiler complains: error[E0499]: cannot borrow 'cursor' as mutable more than once at a time.

I would like to use the bundled feature of sqlite3-src, but this is not exposed currently. Any chance of adding that?

https://www.sqlite.org/c3ref/close.html

Also, is there any way to verify an error code. For example when calling open is there a way to verify the returned error is sqlite::Error::CANTOPEN for testing purposes?