1 use std::ffi::CStr; 2 use std::iter::IntoIterator; 3 use std::os::raw::{c_char, c_int, c_void}; 4 #[cfg(feature = "array")] 5 use std::rc::Rc; 6 use std::slice::from_raw_parts; 7 use std::{convert, fmt, mem, ptr, result, str}; 8 9 use super::ffi; 10 use super::{len_as_c_int, str_for_sqlite, str_to_cstring}; 11 use super::{ 12 AndThenRows, Connection, Error, MappedRows, RawStatement, Result, Row, Rows, ValueRef, 13 }; 14 use crate::types::{ToSql, ToSqlOutput}; 15 #[cfg(feature = "array")] 16 use crate::vtab::array::{free_array, ARRAY_TYPE}; 17 18 /// A prepared statement. 19 pub struct Statement<'conn> { 20 conn: &'conn Connection, 21 pub(crate) stmt: RawStatement, 22 } 23 24 impl Statement<'_> { 25 /// Execute the prepared statement. 26 /// 27 /// On success, returns the number of rows that were changed or inserted or 28 /// deleted (via `sqlite3_changes`). 29 /// 30 /// ## Example 31 /// 32 /// ```rust,no_run 33 /// # use rusqlite::{Connection, Result}; 34 /// fn update_rows(conn: &Connection) -> Result<()> { 35 /// let mut stmt = conn.prepare("UPDATE foo SET bar = 'baz' WHERE qux = ?")?; 36 /// 37 /// stmt.execute(&[1i32])?; 38 /// stmt.execute(&[2i32])?; 39 /// 40 /// Ok(()) 41 /// } 42 /// ``` 43 /// 44 /// # Failure 45 /// 46 /// Will return `Err` if binding parameters fails, the executed statement 47 /// returns rows (in which case `query` should be used instead), or the 48 /// underling SQLite call fails. execute<P>(&mut self, params: P) -> Result<usize> where P: IntoIterator, P::Item: ToSql,49 pub fn execute<P>(&mut self, params: P) -> Result<usize> 50 where 51 P: IntoIterator, 52 P::Item: ToSql, 53 { 54 self.bind_parameters(params)?; 55 self.execute_with_bound_parameters() 56 } 57 58 /// Execute the prepared statement with named parameter(s). If any 59 /// parameters that were in the prepared statement are not included in 60 /// `params`, they will continue to use the most-recently bound value 61 /// from a previous call to `execute_named`, or `NULL` if they have 62 /// never been bound. 63 /// 64 /// On success, returns the number of rows that were changed or inserted or 65 /// deleted (via `sqlite3_changes`). 66 /// 67 /// ## Example 68 /// 69 /// ```rust,no_run 70 /// # use rusqlite::{Connection, Result}; 71 /// fn insert(conn: &Connection) -> Result<usize> { 72 /// let mut stmt = conn.prepare("INSERT INTO test (name) VALUES (:name)")?; 73 /// stmt.execute_named(&[(":name", &"one")]) 74 /// } 75 /// ``` 76 /// 77 /// Note, the `named_params` macro is provided for syntactic convenience, 78 /// and so the above example could also be written as: 79 /// 80 /// ```rust,no_run 81 /// # use rusqlite::{Connection, Result, named_params}; 82 /// fn insert(conn: &Connection) -> Result<usize> { 83 /// let mut stmt = conn.prepare("INSERT INTO test (name) VALUES (:name)")?; 84 /// stmt.execute_named(named_params!{":name": "one"}) 85 /// } 86 /// ``` 87 /// 88 /// # Failure 89 /// 90 /// Will return `Err` if binding parameters fails, the executed statement 91 /// returns rows (in which case `query` should be used instead), or the 92 /// underling SQLite call fails. execute_named(&mut self, params: &[(&str, &dyn ToSql)]) -> Result<usize>93 pub fn execute_named(&mut self, params: &[(&str, &dyn ToSql)]) -> Result<usize> { 94 self.bind_parameters_named(params)?; 95 self.execute_with_bound_parameters() 96 } 97 98 /// Execute an INSERT and return the ROWID. 99 /// 100 /// # Note 101 /// 102 /// This function is a convenience wrapper around `execute()` intended for 103 /// queries that insert a single item. It is possible to misuse this 104 /// function in a way that it cannot detect, such as by calling it on a 105 /// statement which _updates_ a single 106 /// item rather than inserting one. Please don't do that. 107 /// 108 /// # Failure 109 /// 110 /// Will return `Err` if no row is inserted or many rows are inserted. insert<P>(&mut self, params: P) -> Result<i64> where P: IntoIterator, P::Item: ToSql,111 pub fn insert<P>(&mut self, params: P) -> Result<i64> 112 where 113 P: IntoIterator, 114 P::Item: ToSql, 115 { 116 let changes = self.execute(params)?; 117 match changes { 118 1 => Ok(self.conn.last_insert_rowid()), 119 _ => Err(Error::StatementChangedRows(changes)), 120 } 121 } 122 123 /// Execute the prepared statement, returning a handle to the resulting 124 /// rows. 125 /// 126 /// Due to lifetime restricts, the rows handle returned by `query` does not 127 /// implement the `Iterator` trait. Consider using `query_map` or 128 /// `query_and_then` instead, which do. 129 /// 130 /// ## Example 131 /// 132 /// ```rust,no_run 133 /// # use rusqlite::{Connection, Result, NO_PARAMS}; 134 /// fn get_names(conn: &Connection) -> Result<Vec<String>> { 135 /// let mut stmt = conn.prepare("SELECT name FROM people")?; 136 /// let mut rows = stmt.query(NO_PARAMS)?; 137 /// 138 /// let mut names = Vec::new(); 139 /// while let Some(row) = rows.next()? { 140 /// names.push(row.get(0)?); 141 /// } 142 /// 143 /// Ok(names) 144 /// } 145 /// ``` 146 /// 147 /// ## Failure 148 /// 149 /// Will return `Err` if binding parameters fails. query<P>(&mut self, params: P) -> Result<Rows<'_>> where P: IntoIterator, P::Item: ToSql,150 pub fn query<P>(&mut self, params: P) -> Result<Rows<'_>> 151 where 152 P: IntoIterator, 153 P::Item: ToSql, 154 { 155 self.check_readonly()?; 156 self.bind_parameters(params)?; 157 Ok(Rows::new(self)) 158 } 159 160 /// Execute the prepared statement with named parameter(s), returning a 161 /// handle for the resulting rows. If any parameters that were in the 162 /// prepared statement are not included in `params`, they will continue 163 /// to use the most-recently bound value from a previous 164 /// call to `query_named`, or `NULL` if they have never been bound. 165 /// 166 /// ## Example 167 /// 168 /// ```rust,no_run 169 /// # use rusqlite::{Connection, Result}; 170 /// fn query(conn: &Connection) -> Result<()> { 171 /// let mut stmt = conn.prepare("SELECT * FROM test where name = :name")?; 172 /// let mut rows = stmt.query_named(&[(":name", &"one")])?; 173 /// while let Some(row) = rows.next()? { 174 /// // ... 175 /// } 176 /// Ok(()) 177 /// } 178 /// ``` 179 /// 180 /// Note, the `named_params!` macro is provided for syntactic convenience, 181 /// and so the above example could also be written as: 182 /// 183 /// ```rust,no_run 184 /// # use rusqlite::{Connection, Result, named_params}; 185 /// fn query(conn: &Connection) -> Result<()> { 186 /// let mut stmt = conn.prepare("SELECT * FROM test where name = :name")?; 187 /// let mut rows = stmt.query_named(named_params!{ ":name": "one" })?; 188 /// while let Some(row) = rows.next()? { 189 /// // ... 190 /// } 191 /// Ok(()) 192 /// } 193 /// ``` 194 /// 195 /// # Failure 196 /// 197 /// Will return `Err` if binding parameters fails. query_named(&mut self, params: &[(&str, &dyn ToSql)]) -> Result<Rows<'_>>198 pub fn query_named(&mut self, params: &[(&str, &dyn ToSql)]) -> Result<Rows<'_>> { 199 self.check_readonly()?; 200 self.bind_parameters_named(params)?; 201 Ok(Rows::new(self)) 202 } 203 204 /// Executes the prepared statement and maps a function over the resulting 205 /// rows, returning an iterator over the mapped function results. 206 /// 207 /// ## Example 208 /// 209 /// ```rust,no_run 210 /// # use rusqlite::{Connection, Result, NO_PARAMS}; 211 /// fn get_names(conn: &Connection) -> Result<Vec<String>> { 212 /// let mut stmt = conn.prepare("SELECT name FROM people")?; 213 /// let rows = stmt.query_map(NO_PARAMS, |row| row.get(0))?; 214 /// 215 /// let mut names = Vec::new(); 216 /// for name_result in rows { 217 /// names.push(name_result?); 218 /// } 219 /// 220 /// Ok(names) 221 /// } 222 /// ``` 223 /// 224 /// ## Failure 225 /// 226 /// Will return `Err` if binding parameters fails. query_map<T, P, F>(&mut self, params: P, f: F) -> Result<MappedRows<'_, F>> where P: IntoIterator, P::Item: ToSql, F: FnMut(&Row<'_>) -> Result<T>,227 pub fn query_map<T, P, F>(&mut self, params: P, f: F) -> Result<MappedRows<'_, F>> 228 where 229 P: IntoIterator, 230 P::Item: ToSql, 231 F: FnMut(&Row<'_>) -> Result<T>, 232 { 233 let rows = self.query(params)?; 234 Ok(MappedRows::new(rows, f)) 235 } 236 237 /// Execute the prepared statement with named parameter(s), returning an 238 /// iterator over the result of calling the mapping function over the 239 /// query's rows. If any parameters that were in the prepared statement 240 /// are not included in `params`, they will continue to use the 241 /// most-recently bound value from a previous call to `query_named`, 242 /// or `NULL` if they have never been bound. 243 /// 244 /// ## Example 245 /// 246 /// ```rust,no_run 247 /// # use rusqlite::{Connection, Result}; 248 /// fn get_names(conn: &Connection) -> Result<Vec<String>> { 249 /// let mut stmt = conn.prepare("SELECT name FROM people WHERE id = :id")?; 250 /// let rows = stmt.query_map_named(&[(":id", &"one")], |row| row.get(0))?; 251 /// 252 /// let mut names = Vec::new(); 253 /// for name_result in rows { 254 /// names.push(name_result?); 255 /// } 256 /// 257 /// Ok(names) 258 /// } 259 /// ``` 260 /// 261 /// ## Failure 262 /// 263 /// Will return `Err` if binding parameters fails. query_map_named<T, F>( &mut self, params: &[(&str, &dyn ToSql)], f: F, ) -> Result<MappedRows<'_, F>> where F: FnMut(&Row<'_>) -> Result<T>,264 pub fn query_map_named<T, F>( 265 &mut self, 266 params: &[(&str, &dyn ToSql)], 267 f: F, 268 ) -> Result<MappedRows<'_, F>> 269 where 270 F: FnMut(&Row<'_>) -> Result<T>, 271 { 272 let rows = self.query_named(params)?; 273 Ok(MappedRows::new(rows, f)) 274 } 275 276 /// Executes the prepared statement and maps a function over the resulting 277 /// rows, where the function returns a `Result` with `Error` type 278 /// implementing `std::convert::From<Error>` (so errors can be unified). 279 /// 280 /// # Failure 281 /// 282 /// Will return `Err` if binding parameters fails. query_and_then<T, E, P, F>(&mut self, params: P, f: F) -> Result<AndThenRows<'_, F>> where P: IntoIterator, P::Item: ToSql, E: convert::From<Error>, F: FnMut(&Row<'_>) -> result::Result<T, E>,283 pub fn query_and_then<T, E, P, F>(&mut self, params: P, f: F) -> Result<AndThenRows<'_, F>> 284 where 285 P: IntoIterator, 286 P::Item: ToSql, 287 E: convert::From<Error>, 288 F: FnMut(&Row<'_>) -> result::Result<T, E>, 289 { 290 let rows = self.query(params)?; 291 Ok(AndThenRows::new(rows, f)) 292 } 293 294 /// Execute the prepared statement with named parameter(s), returning an 295 /// iterator over the result of calling the mapping function over the 296 /// query's rows. If any parameters that were in the prepared statement 297 /// are not included in 298 /// `params`, they will 299 /// continue to use the most-recently bound value from a previous call 300 /// to `query_named`, or `NULL` if they have never been bound. 301 /// 302 /// ## Example 303 /// 304 /// ```rust,no_run 305 /// # use rusqlite::{Connection, Result}; 306 /// struct Person { 307 /// name: String, 308 /// }; 309 /// 310 /// fn name_to_person(name: String) -> Result<Person> { 311 /// // ... check for valid name 312 /// Ok(Person { name: name }) 313 /// } 314 /// 315 /// fn get_names(conn: &Connection) -> Result<Vec<Person>> { 316 /// let mut stmt = conn.prepare("SELECT name FROM people WHERE id = :id")?; 317 /// let rows = 318 /// stmt.query_and_then_named(&[(":id", &"one")], |row| name_to_person(row.get(0)?))?; 319 /// 320 /// let mut persons = Vec::new(); 321 /// for person_result in rows { 322 /// persons.push(person_result?); 323 /// } 324 /// 325 /// Ok(persons) 326 /// } 327 /// ``` 328 /// 329 /// ## Failure 330 /// 331 /// Will return `Err` if binding parameters fails. query_and_then_named<T, E, F>( &mut self, params: &[(&str, &dyn ToSql)], f: F, ) -> Result<AndThenRows<'_, F>> where E: convert::From<Error>, F: FnMut(&Row<'_>) -> result::Result<T, E>,332 pub fn query_and_then_named<T, E, F>( 333 &mut self, 334 params: &[(&str, &dyn ToSql)], 335 f: F, 336 ) -> Result<AndThenRows<'_, F>> 337 where 338 E: convert::From<Error>, 339 F: FnMut(&Row<'_>) -> result::Result<T, E>, 340 { 341 let rows = self.query_named(params)?; 342 Ok(AndThenRows::new(rows, f)) 343 } 344 345 /// Return `true` if a query in the SQL statement it executes returns one 346 /// or more rows and `false` if the SQL returns an empty set. exists<P>(&mut self, params: P) -> Result<bool> where P: IntoIterator, P::Item: ToSql,347 pub fn exists<P>(&mut self, params: P) -> Result<bool> 348 where 349 P: IntoIterator, 350 P::Item: ToSql, 351 { 352 let mut rows = self.query(params)?; 353 let exists = rows.next()?.is_some(); 354 Ok(exists) 355 } 356 357 /// Convenience method to execute a query that is expected to return a 358 /// single row. 359 /// 360 /// If the query returns more than one row, all rows except the first are 361 /// ignored. 362 /// 363 /// Returns `Err(QueryReturnedNoRows)` if no results are returned. If the 364 /// query truly is optional, you can call `.optional()` on the result of 365 /// this to get a `Result<Option<T>>`. 366 /// 367 /// # Failure 368 /// 369 /// Will return `Err` if the underlying SQLite call fails. query_row<T, P, F>(&mut self, params: P, f: F) -> Result<T> where P: IntoIterator, P::Item: ToSql, F: FnOnce(&Row<'_>) -> Result<T>,370 pub fn query_row<T, P, F>(&mut self, params: P, f: F) -> Result<T> 371 where 372 P: IntoIterator, 373 P::Item: ToSql, 374 F: FnOnce(&Row<'_>) -> Result<T>, 375 { 376 let mut rows = self.query(params)?; 377 378 rows.get_expected_row().and_then(|r| f(&r)) 379 } 380 381 /// Convenience method to execute a query with named parameter(s) that is 382 /// expected to return a single row. 383 /// 384 /// If the query returns more than one row, all rows except the first are 385 /// ignored. 386 /// 387 /// Returns `Err(QueryReturnedNoRows)` if no results are returned. If the 388 /// query truly is optional, you can call `.optional()` on the result of 389 /// this to get a `Result<Option<T>>`. 390 /// 391 /// # Failure 392 /// 393 /// Will return `Err` if `sql` cannot be converted to a C-compatible string 394 /// or if the underlying SQLite call fails. query_row_named<T, F>(&mut self, params: &[(&str, &dyn ToSql)], f: F) -> Result<T> where F: FnOnce(&Row<'_>) -> Result<T>,395 pub fn query_row_named<T, F>(&mut self, params: &[(&str, &dyn ToSql)], f: F) -> Result<T> 396 where 397 F: FnOnce(&Row<'_>) -> Result<T>, 398 { 399 let mut rows = self.query_named(params)?; 400 401 rows.get_expected_row().and_then(|r| f(&r)) 402 } 403 404 /// Consumes the statement. 405 /// 406 /// Functionally equivalent to the `Drop` implementation, but allows 407 /// callers to see any errors that occur. 408 /// 409 /// # Failure 410 /// 411 /// Will return `Err` if the underlying SQLite call fails. finalize(mut self) -> Result<()>412 pub fn finalize(mut self) -> Result<()> { 413 self.finalize_() 414 } 415 416 /// Return the index of an SQL parameter given its name. 417 /// 418 /// # Failure 419 /// 420 /// Will return Err if `name` is invalid. Will return Ok(None) if the name 421 /// is valid but not a bound parameter of this statement. parameter_index(&self, name: &str) -> Result<Option<usize>>422 pub fn parameter_index(&self, name: &str) -> Result<Option<usize>> { 423 let c_name = str_to_cstring(name)?; 424 Ok(self.stmt.bind_parameter_index(&c_name)) 425 } 426 bind_parameters<P>(&mut self, params: P) -> Result<()> where P: IntoIterator, P::Item: ToSql,427 fn bind_parameters<P>(&mut self, params: P) -> Result<()> 428 where 429 P: IntoIterator, 430 P::Item: ToSql, 431 { 432 let expected = self.stmt.bind_parameter_count(); 433 let mut index = 0; 434 for p in params.into_iter() { 435 index += 1; // The leftmost SQL parameter has an index of 1. 436 if index > expected { 437 break; 438 } 439 self.bind_parameter(&p, index)?; 440 } 441 assert_eq!( 442 index, expected, 443 "incorrect number of parameters: expected {}, got {}", 444 expected, index 445 ); 446 447 Ok(()) 448 } 449 bind_parameters_named(&mut self, params: &[(&str, &dyn ToSql)]) -> Result<()>450 fn bind_parameters_named(&mut self, params: &[(&str, &dyn ToSql)]) -> Result<()> { 451 for &(name, value) in params { 452 if let Some(i) = self.parameter_index(name)? { 453 self.bind_parameter(value, i)?; 454 } else { 455 return Err(Error::InvalidParameterName(name.into())); 456 } 457 } 458 Ok(()) 459 } 460 bind_parameter(&self, param: &dyn ToSql, col: usize) -> Result<()>461 fn bind_parameter(&self, param: &dyn ToSql, col: usize) -> Result<()> { 462 let value = param.to_sql()?; 463 464 let ptr = unsafe { self.stmt.ptr() }; 465 let value = match value { 466 ToSqlOutput::Borrowed(v) => v, 467 ToSqlOutput::Owned(ref v) => ValueRef::from(v), 468 469 #[cfg(feature = "blob")] 470 ToSqlOutput::ZeroBlob(len) => { 471 return self 472 .conn 473 .decode_result(unsafe { ffi::sqlite3_bind_zeroblob(ptr, col as c_int, len) }); 474 } 475 #[cfg(feature = "array")] 476 ToSqlOutput::Array(a) => { 477 return self.conn.decode_result(unsafe { 478 ffi::sqlite3_bind_pointer( 479 ptr, 480 col as c_int, 481 Rc::into_raw(a) as *mut c_void, 482 ARRAY_TYPE, 483 Some(free_array), 484 ) 485 }); 486 } 487 }; 488 self.conn.decode_result(match value { 489 ValueRef::Null => unsafe { ffi::sqlite3_bind_null(ptr, col as c_int) }, 490 ValueRef::Integer(i) => unsafe { ffi::sqlite3_bind_int64(ptr, col as c_int, i) }, 491 ValueRef::Real(r) => unsafe { ffi::sqlite3_bind_double(ptr, col as c_int, r) }, 492 ValueRef::Text(s) => unsafe { 493 let (c_str, len, destructor) = str_for_sqlite(s)?; 494 ffi::sqlite3_bind_text(ptr, col as c_int, c_str, len, destructor) 495 }, 496 ValueRef::Blob(b) => unsafe { 497 let length = len_as_c_int(b.len())?; 498 if length == 0 { 499 ffi::sqlite3_bind_zeroblob(ptr, col as c_int, 0) 500 } else { 501 ffi::sqlite3_bind_blob( 502 ptr, 503 col as c_int, 504 b.as_ptr() as *const c_void, 505 length, 506 ffi::SQLITE_TRANSIENT(), 507 ) 508 } 509 }, 510 }) 511 } 512 execute_with_bound_parameters(&mut self) -> Result<usize>513 fn execute_with_bound_parameters(&mut self) -> Result<usize> { 514 let r = self.stmt.step(); 515 self.stmt.reset(); 516 match r { 517 ffi::SQLITE_DONE => { 518 if self.column_count() == 0 { 519 Ok(self.conn.changes()) 520 } else { 521 Err(Error::ExecuteReturnedResults) 522 } 523 } 524 ffi::SQLITE_ROW => Err(Error::ExecuteReturnedResults), 525 _ => Err(self.conn.decode_result(r).unwrap_err()), 526 } 527 } 528 finalize_(&mut self) -> Result<()>529 fn finalize_(&mut self) -> Result<()> { 530 let mut stmt = RawStatement::new(ptr::null_mut()); 531 mem::swap(&mut stmt, &mut self.stmt); 532 self.conn.decode_result(stmt.finalize()) 533 } 534 535 #[cfg(not(feature = "bundled"))] 536 #[inline] check_readonly(&self) -> Result<()>537 fn check_readonly(&self) -> Result<()> { 538 Ok(()) 539 } 540 541 #[cfg(feature = "bundled")] 542 #[inline] check_readonly(&self) -> Result<()>543 fn check_readonly(&self) -> Result<()> { 544 /*if !self.stmt.readonly() { does not work for PRAGMA 545 return Err(Error::InvalidQuery); 546 }*/ 547 Ok(()) 548 } 549 550 /// Returns a string containing the SQL text of prepared statement with 551 /// bound parameters expanded. 552 #[cfg(feature = "bundled")] expanded_sql(&self) -> Option<&str>553 pub fn expanded_sql(&self) -> Option<&str> { 554 unsafe { 555 self.stmt 556 .expanded_sql() 557 .map(|s| str::from_utf8_unchecked(s.to_bytes())) 558 } 559 } 560 561 /// Get the value for one of the status counters for this statement. get_status(&self, status: StatementStatus) -> i32562 pub fn get_status(&self, status: StatementStatus) -> i32 { 563 self.stmt.get_status(status, false) 564 } 565 566 /// Reset the value of one of the status counters for this statement, 567 /// returning the value it had before resetting. reset_status(&self, status: StatementStatus) -> i32568 pub fn reset_status(&self, status: StatementStatus) -> i32 { 569 self.stmt.get_status(status, true) 570 } 571 } 572 573 impl Into<RawStatement> for Statement<'_> { into(mut self) -> RawStatement574 fn into(mut self) -> RawStatement { 575 let mut stmt = RawStatement::new(ptr::null_mut()); 576 mem::swap(&mut stmt, &mut self.stmt); 577 stmt 578 } 579 } 580 581 impl fmt::Debug for Statement<'_> { fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result582 fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result { 583 let sql = str::from_utf8(self.stmt.sql().to_bytes()); 584 f.debug_struct("Statement") 585 .field("conn", self.conn) 586 .field("stmt", &self.stmt) 587 .field("sql", &sql) 588 .finish() 589 } 590 } 591 592 impl Drop for Statement<'_> { 593 #[allow(unused_must_use)] drop(&mut self)594 fn drop(&mut self) { 595 self.finalize_(); 596 } 597 } 598 599 impl Statement<'_> { new(conn: &Connection, stmt: RawStatement) -> Statement<'_>600 pub(crate) fn new(conn: &Connection, stmt: RawStatement) -> Statement<'_> { 601 Statement { conn, stmt } 602 } 603 value_ref(&self, col: usize) -> ValueRef<'_>604 pub(crate) fn value_ref(&self, col: usize) -> ValueRef<'_> { 605 let raw = unsafe { self.stmt.ptr() }; 606 607 match self.stmt.column_type(col) { 608 ffi::SQLITE_NULL => ValueRef::Null, 609 ffi::SQLITE_INTEGER => { 610 ValueRef::Integer(unsafe { ffi::sqlite3_column_int64(raw, col as c_int) }) 611 } 612 ffi::SQLITE_FLOAT => { 613 ValueRef::Real(unsafe { ffi::sqlite3_column_double(raw, col as c_int) }) 614 } 615 ffi::SQLITE_TEXT => { 616 let s = unsafe { 617 let text = ffi::sqlite3_column_text(raw, col as c_int); 618 assert!( 619 !text.is_null(), 620 "unexpected SQLITE_TEXT column type with NULL data" 621 ); 622 CStr::from_ptr(text as *const c_char) 623 }; 624 625 // sqlite3_column_text returns UTF8 data, so our unwrap here should be fine. 626 let s = s 627 .to_str() 628 .expect("sqlite3_column_text returned invalid UTF-8"); 629 ValueRef::Text(s) 630 } 631 ffi::SQLITE_BLOB => { 632 let (blob, len) = unsafe { 633 ( 634 ffi::sqlite3_column_blob(raw, col as c_int), 635 ffi::sqlite3_column_bytes(raw, col as c_int), 636 ) 637 }; 638 639 assert!( 640 len >= 0, 641 "unexpected negative return from sqlite3_column_bytes" 642 ); 643 if len > 0 { 644 assert!( 645 !blob.is_null(), 646 "unexpected SQLITE_BLOB column type with NULL data" 647 ); 648 ValueRef::Blob(unsafe { from_raw_parts(blob as *const u8, len as usize) }) 649 } else { 650 // The return value from sqlite3_column_blob() for a zero-length BLOB 651 // is a NULL pointer. 652 ValueRef::Blob(&[]) 653 } 654 } 655 _ => unreachable!("sqlite3_column_type returned invalid value"), 656 } 657 } 658 step(&self) -> Result<bool>659 pub(crate) fn step(&self) -> Result<bool> { 660 match self.stmt.step() { 661 ffi::SQLITE_ROW => Ok(true), 662 ffi::SQLITE_DONE => Ok(false), 663 code => Err(self.conn.decode_result(code).unwrap_err()), 664 } 665 } 666 reset(&self) -> c_int667 pub(crate) fn reset(&self) -> c_int { 668 self.stmt.reset() 669 } 670 } 671 672 /// Prepared statement status counters. 673 /// 674 /// See https://www.sqlite.org/c3ref/c_stmtstatus_counter.html 675 /// for explanations of each. 676 /// 677 /// Note that depending on your version of SQLite, all of these 678 /// may not be available. 679 #[repr(i32)] 680 #[derive(Clone, Copy, PartialEq, Eq)] 681 pub enum StatementStatus { 682 /// Equivalent to SQLITE_STMTSTATUS_FULLSCAN_STEP 683 FullscanStep = 1, 684 /// Equivalent to SQLITE_STMTSTATUS_SORT 685 Sort = 2, 686 /// Equivalent to SQLITE_STMTSTATUS_AUTOINDEX 687 AutoIndex = 3, 688 /// Equivalent to SQLITE_STMTSTATUS_VM_STEP 689 VmStep = 4, 690 /// Equivalent to SQLITE_STMTSTATUS_REPREPARE 691 RePrepare = 5, 692 /// Equivalent to SQLITE_STMTSTATUS_RUN 693 Run = 6, 694 /// Equivalent to SQLITE_STMTSTATUS_MEMUSED 695 MemUsed = 99, 696 } 697 698 #[cfg(test)] 699 mod test { 700 use crate::types::ToSql; 701 use crate::{Connection, Error, Result, NO_PARAMS}; 702 703 #[test] test_execute_named()704 fn test_execute_named() { 705 let db = Connection::open_in_memory().unwrap(); 706 db.execute_batch("CREATE TABLE foo(x INTEGER)").unwrap(); 707 708 assert_eq!( 709 db.execute_named("INSERT INTO foo(x) VALUES (:x)", &[(":x", &1i32)]) 710 .unwrap(), 711 1 712 ); 713 assert_eq!( 714 db.execute_named("INSERT INTO foo(x) VALUES (:x)", &[(":x", &2i32)]) 715 .unwrap(), 716 1 717 ); 718 719 assert_eq!( 720 3i32, 721 db.query_row_named::<i32, _>( 722 "SELECT SUM(x) FROM foo WHERE x > :x", 723 &[(":x", &0i32)], 724 |r| r.get(0) 725 ) 726 .unwrap() 727 ); 728 } 729 730 #[test] test_stmt_execute_named()731 fn test_stmt_execute_named() { 732 let db = Connection::open_in_memory().unwrap(); 733 let sql = "CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, flag \ 734 INTEGER)"; 735 db.execute_batch(sql).unwrap(); 736 737 let mut stmt = db 738 .prepare("INSERT INTO test (name) VALUES (:name)") 739 .unwrap(); 740 stmt.execute_named(&[(":name", &"one")]).unwrap(); 741 742 let mut stmt = db 743 .prepare("SELECT COUNT(*) FROM test WHERE name = :name") 744 .unwrap(); 745 assert_eq!( 746 1i32, 747 stmt.query_row_named::<i32, _>(&[(":name", &"one")], |r| r.get(0)) 748 .unwrap() 749 ); 750 } 751 752 #[test] test_query_named()753 fn test_query_named() { 754 let db = Connection::open_in_memory().unwrap(); 755 let sql = r#" 756 CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, flag INTEGER); 757 INSERT INTO test(id, name) VALUES (1, "one"); 758 "#; 759 db.execute_batch(sql).unwrap(); 760 761 let mut stmt = db 762 .prepare("SELECT id FROM test where name = :name") 763 .unwrap(); 764 let mut rows = stmt.query_named(&[(":name", &"one")]).unwrap(); 765 766 let id: Result<i32> = rows.next().unwrap().unwrap().get(0); 767 assert_eq!(Ok(1), id); 768 } 769 770 #[test] test_query_map_named()771 fn test_query_map_named() { 772 let db = Connection::open_in_memory().unwrap(); 773 let sql = r#" 774 CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, flag INTEGER); 775 INSERT INTO test(id, name) VALUES (1, "one"); 776 "#; 777 db.execute_batch(sql).unwrap(); 778 779 let mut stmt = db 780 .prepare("SELECT id FROM test where name = :name") 781 .unwrap(); 782 let mut rows = stmt 783 .query_map_named(&[(":name", &"one")], |row| { 784 let id: Result<i32> = row.get(0); 785 id.map(|i| 2 * i) 786 }) 787 .unwrap(); 788 789 let doubled_id: i32 = rows.next().unwrap().unwrap(); 790 assert_eq!(2, doubled_id); 791 } 792 793 #[test] test_query_and_then_named()794 fn test_query_and_then_named() { 795 let db = Connection::open_in_memory().unwrap(); 796 let sql = r#" 797 CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, flag INTEGER); 798 INSERT INTO test(id, name) VALUES (1, "one"); 799 INSERT INTO test(id, name) VALUES (2, "one"); 800 "#; 801 db.execute_batch(sql).unwrap(); 802 803 let mut stmt = db 804 .prepare("SELECT id FROM test where name = :name ORDER BY id ASC") 805 .unwrap(); 806 let mut rows = stmt 807 .query_and_then_named(&[(":name", &"one")], |row| { 808 let id: i32 = row.get(0)?; 809 if id == 1 { 810 Ok(id) 811 } else { 812 Err(Error::SqliteSingleThreadedMode) 813 } 814 }) 815 .unwrap(); 816 817 // first row should be Ok 818 let doubled_id: i32 = rows.next().unwrap().unwrap(); 819 assert_eq!(1, doubled_id); 820 821 // second row should be Err 822 #[allow(clippy::match_wild_err_arm)] 823 match rows.next().unwrap() { 824 Ok(_) => panic!("invalid Ok"), 825 Err(Error::SqliteSingleThreadedMode) => (), 826 Err(_) => panic!("invalid Err"), 827 } 828 } 829 830 #[test] test_unbound_parameters_are_null()831 fn test_unbound_parameters_are_null() { 832 let db = Connection::open_in_memory().unwrap(); 833 let sql = "CREATE TABLE test (x TEXT, y TEXT)"; 834 db.execute_batch(sql).unwrap(); 835 836 let mut stmt = db 837 .prepare("INSERT INTO test (x, y) VALUES (:x, :y)") 838 .unwrap(); 839 stmt.execute_named(&[(":x", &"one")]).unwrap(); 840 841 let result: Option<String> = db 842 .query_row("SELECT y FROM test WHERE x = 'one'", NO_PARAMS, |row| { 843 row.get(0) 844 }) 845 .unwrap(); 846 assert!(result.is_none()); 847 } 848 849 #[test] test_unbound_parameters_are_reused()850 fn test_unbound_parameters_are_reused() { 851 let db = Connection::open_in_memory().unwrap(); 852 let sql = "CREATE TABLE test (x TEXT, y TEXT)"; 853 db.execute_batch(sql).unwrap(); 854 855 let mut stmt = db 856 .prepare("INSERT INTO test (x, y) VALUES (:x, :y)") 857 .unwrap(); 858 stmt.execute_named(&[(":x", &"one")]).unwrap(); 859 stmt.execute_named(&[(":y", &"two")]).unwrap(); 860 861 let result: String = db 862 .query_row("SELECT x FROM test WHERE y = 'two'", NO_PARAMS, |row| { 863 row.get(0) 864 }) 865 .unwrap(); 866 assert_eq!(result, "one"); 867 } 868 869 #[test] test_insert()870 fn test_insert() { 871 let db = Connection::open_in_memory().unwrap(); 872 db.execute_batch("CREATE TABLE foo(x INTEGER UNIQUE)") 873 .unwrap(); 874 let mut stmt = db 875 .prepare("INSERT OR IGNORE INTO foo (x) VALUES (?)") 876 .unwrap(); 877 assert_eq!(stmt.insert(&[1i32]).unwrap(), 1); 878 assert_eq!(stmt.insert(&[2i32]).unwrap(), 2); 879 match stmt.insert(&[1i32]).unwrap_err() { 880 Error::StatementChangedRows(0) => (), 881 err => panic!("Unexpected error {}", err), 882 } 883 let mut multi = db 884 .prepare("INSERT INTO foo (x) SELECT 3 UNION ALL SELECT 4") 885 .unwrap(); 886 match multi.insert(NO_PARAMS).unwrap_err() { 887 Error::StatementChangedRows(2) => (), 888 err => panic!("Unexpected error {}", err), 889 } 890 } 891 892 #[test] test_insert_different_tables()893 fn test_insert_different_tables() { 894 // Test for https://github.com/jgallagher/rusqlite/issues/171 895 let db = Connection::open_in_memory().unwrap(); 896 db.execute_batch( 897 r" 898 CREATE TABLE foo(x INTEGER); 899 CREATE TABLE bar(x INTEGER); 900 ", 901 ) 902 .unwrap(); 903 904 assert_eq!( 905 db.prepare("INSERT INTO foo VALUES (10)") 906 .unwrap() 907 .insert(NO_PARAMS) 908 .unwrap(), 909 1 910 ); 911 assert_eq!( 912 db.prepare("INSERT INTO bar VALUES (10)") 913 .unwrap() 914 .insert(NO_PARAMS) 915 .unwrap(), 916 1 917 ); 918 } 919 920 #[test] test_exists()921 fn test_exists() { 922 let db = Connection::open_in_memory().unwrap(); 923 let sql = "BEGIN; 924 CREATE TABLE foo(x INTEGER); 925 INSERT INTO foo VALUES(1); 926 INSERT INTO foo VALUES(2); 927 END;"; 928 db.execute_batch(sql).unwrap(); 929 let mut stmt = db.prepare("SELECT 1 FROM foo WHERE x = ?").unwrap(); 930 assert!(stmt.exists(&[1i32]).unwrap()); 931 assert!(stmt.exists(&[2i32]).unwrap()); 932 assert!(!stmt.exists(&[0i32]).unwrap()); 933 } 934 935 #[test] test_query_row()936 fn test_query_row() { 937 let db = Connection::open_in_memory().unwrap(); 938 let sql = "BEGIN; 939 CREATE TABLE foo(x INTEGER, y INTEGER); 940 INSERT INTO foo VALUES(1, 3); 941 INSERT INTO foo VALUES(2, 4); 942 END;"; 943 db.execute_batch(sql).unwrap(); 944 let mut stmt = db.prepare("SELECT y FROM foo WHERE x = ?").unwrap(); 945 let y: Result<i64> = stmt.query_row(&[1i32], |r| r.get(0)); 946 assert_eq!(3i64, y.unwrap()); 947 } 948 949 #[test] test_query_by_column_name()950 fn test_query_by_column_name() { 951 let db = Connection::open_in_memory().unwrap(); 952 let sql = "BEGIN; 953 CREATE TABLE foo(x INTEGER, y INTEGER); 954 INSERT INTO foo VALUES(1, 3); 955 END;"; 956 db.execute_batch(sql).unwrap(); 957 let mut stmt = db.prepare("SELECT y FROM foo").unwrap(); 958 let y: Result<i64> = stmt.query_row(NO_PARAMS, |r| r.get("y")); 959 assert_eq!(3i64, y.unwrap()); 960 } 961 962 #[test] test_query_by_column_name_ignore_case()963 fn test_query_by_column_name_ignore_case() { 964 let db = Connection::open_in_memory().unwrap(); 965 let sql = "BEGIN; 966 CREATE TABLE foo(x INTEGER, y INTEGER); 967 INSERT INTO foo VALUES(1, 3); 968 END;"; 969 db.execute_batch(sql).unwrap(); 970 let mut stmt = db.prepare("SELECT y as Y FROM foo").unwrap(); 971 let y: Result<i64> = stmt.query_row(NO_PARAMS, |r| r.get("y")); 972 assert_eq!(3i64, y.unwrap()); 973 } 974 975 #[test] 976 #[cfg(feature = "bundled")] test_expanded_sql()977 fn test_expanded_sql() { 978 let db = Connection::open_in_memory().unwrap(); 979 let stmt = db.prepare("SELECT ?").unwrap(); 980 stmt.bind_parameter(&1, 1).unwrap(); 981 assert_eq!(Some("SELECT 1"), stmt.expanded_sql()); 982 } 983 984 #[test] test_bind_parameters()985 fn test_bind_parameters() { 986 let db = Connection::open_in_memory().unwrap(); 987 // dynamic slice: 988 db.query_row( 989 "SELECT ?1, ?2, ?3", 990 &[&1u8 as &dyn ToSql, &"one", &Some("one")], 991 |row| row.get::<_, u8>(0), 992 ) 993 .unwrap(); 994 // existing collection: 995 let data = vec![1, 2, 3]; 996 db.query_row("SELECT ?1, ?2, ?3", &data, |row| row.get::<_, u8>(0)) 997 .unwrap(); 998 db.query_row("SELECT ?1, ?2, ?3", data.as_slice(), |row| { 999 row.get::<_, u8>(0) 1000 }) 1001 .unwrap(); 1002 db.query_row("SELECT ?1, ?2, ?3", data, |row| row.get::<_, u8>(0)) 1003 .unwrap(); 1004 1005 use std::collections::BTreeSet; 1006 let data: BTreeSet<String> = ["one", "two", "three"] 1007 .iter() 1008 .map(|s| s.to_string()) 1009 .collect(); 1010 db.query_row("SELECT ?1, ?2, ?3", &data, |row| row.get::<_, String>(0)) 1011 .unwrap(); 1012 1013 let data = [0; 3]; 1014 db.query_row("SELECT ?1, ?2, ?3", &data, |row| row.get::<_, u8>(0)) 1015 .unwrap(); 1016 db.query_row("SELECT ?1, ?2, ?3", data.iter(), |row| row.get::<_, u8>(0)) 1017 .unwrap(); 1018 } 1019 } 1020