duckdb/
statement.rs

1use std::{convert, ffi::c_void, fmt, mem, os::raw::c_char, ptr, str};
2
3use arrow::{array::StructArray, datatypes::SchemaRef};
4
5use super::{ffi, AndThenRows, Connection, Error, MappedRows, Params, RawStatement, Result, Row, Rows, ValueRef};
6#[cfg(feature = "polars")]
7use crate::{arrow2, polars_dataframe::Polars};
8use crate::{
9    arrow_batch::{Arrow, ArrowStream},
10    error::result_from_duckdb_prepare,
11    types::{TimeUnit, ToSql, ToSqlOutput},
12};
13
14/// A prepared statement.
15///
16/// # Thread Safety
17///
18/// `Statement` is neither `Send` nor `Sync`:
19/// - Not `Send` because it holds a reference to `Connection`, which is `!Sync`
20/// - Not `Sync` because DuckDB prepared statements don't support concurrent access
21///
22/// See the [DuckDB concurrency documentation](https://duckdb.org/docs/stable/connect/concurrency.html) for more details.
23pub struct Statement<'conn> {
24    conn: &'conn Connection,
25    pub(crate) stmt: RawStatement,
26}
27
28impl Statement<'_> {
29    /// Execute the prepared statement.
30    ///
31    /// On success, returns the number of rows that were changed or inserted or
32    /// deleted.
33    ///
34    /// ## Example
35    ///
36    /// ### Use with positional parameters
37    ///
38    /// ```rust,no_run
39    /// # use duckdb::{Connection, Result, params};
40    /// fn update_rows(conn: &Connection) -> Result<()> {
41    ///     let mut stmt = conn.prepare("UPDATE foo SET bar = 'baz' WHERE qux = ?")?;
42    ///     // The `duckdb::params!` macro is mostly useful when the parameters do not
43    ///     // all have the same type, or if there are more than 32 parameters
44    ///     // at once.
45    ///     stmt.execute(params![1i32])?;
46    ///     // However, it's not required, many cases are fine as:
47    ///     stmt.execute(&[&2i32])?;
48    ///     // Or even:
49    ///     stmt.execute([2i32])?;
50    ///     Ok(())
51    /// }
52    /// ```
53    ///
54    /// ### Use without parameters
55    ///
56    /// ```rust,no_run
57    /// # use duckdb::{Connection, Result, params};
58    /// fn delete_all(conn: &Connection) -> Result<()> {
59    ///     let mut stmt = conn.prepare("DELETE FROM users")?;
60    ///     stmt.execute([])?;
61    ///     Ok(())
62    /// }
63    /// ```
64    ///
65    /// # Failure
66    ///
67    /// Will return `Err` if binding parameters fails, the executed statement
68    /// returns rows (in which case `query` should be used instead), or the
69    /// underlying DuckDB call fails.
70    #[inline]
71    pub fn execute<P: Params>(&mut self, params: P) -> Result<usize> {
72        params.__bind_in(self)?;
73        self.execute_with_bound_parameters()
74    }
75
76    /// Execute an INSERT.
77    ///
78    /// # Note
79    ///
80    /// This function is a convenience wrapper around
81    /// [`execute()`](Statement::execute) intended for queries that insert a
82    /// single item. It is possible to misuse this function in a way that it
83    /// cannot detect, such as by calling it on a statement which _updates_
84    /// a single item rather than inserting one. Please don't do that.
85    ///
86    /// # Failure
87    ///
88    /// Will return `Err` if no row is inserted or many rows are inserted.
89    #[inline]
90    pub fn insert<P: Params>(&mut self, params: P) -> Result<()> {
91        let changes = self.execute(params)?;
92        match changes {
93            1 => Ok(()),
94            _ => Err(Error::StatementChangedRows(changes)),
95        }
96    }
97
98    /// Execute the prepared statement, returning a handle to the resulting
99    /// vector of arrow RecordBatch
100    ///
101    /// ## Example
102    ///
103    /// ```rust,no_run
104    /// # use duckdb::{Result, Connection};
105    /// # use arrow::record_batch::RecordBatch;
106    /// fn get_arrow_data(conn: &Connection) -> Result<Vec<RecordBatch>> {
107    ///     Ok(conn.prepare("SELECT * FROM test")?.query_arrow([])?.collect())
108    /// }
109    /// ```
110    ///
111    /// # Failure
112    ///
113    /// Will return `Err` if binding parameters fails.
114    #[inline]
115    pub fn query_arrow<P: Params>(&mut self, params: P) -> Result<Arrow<'_>> {
116        self.execute(params)?;
117        Ok(Arrow::new(self))
118    }
119
120    /// Execute the prepared statement, returning a handle to the resulting
121    /// vector of arrow RecordBatch in streaming way
122    ///
123    /// ## Example
124    ///
125    /// ```rust,no_run
126    /// # use duckdb::{Result, Connection};
127    /// # use arrow::record_batch::RecordBatch;
128    /// # use arrow::datatypes::SchemaRef;
129    /// fn get_arrow_data(conn: &Connection, schema: SchemaRef) -> Result<Vec<RecordBatch>> {
130    ///     Ok(conn.prepare("SELECT * FROM test")?.stream_arrow([], schema)?.collect())
131    /// }
132    /// ```
133    ///
134    /// # Failure
135    ///
136    /// Will return `Err` if binding parameters fails.
137    #[inline]
138    pub fn stream_arrow<P: Params>(&mut self, params: P, schema: SchemaRef) -> Result<ArrowStream<'_>> {
139        params.__bind_in(self)?;
140        self.stmt.execute_streaming()?;
141        Ok(ArrowStream::new(self, schema))
142    }
143
144    /// Execute the prepared statement, returning a handle to the resulting
145    /// vector of polars DataFrame.
146    ///
147    /// ## Example
148    ///
149    /// ```rust,no_run
150    /// # use duckdb::{Result, Connection};
151    /// # use polars::prelude::DataFrame;
152    ///
153    /// fn get_polars_dfs(conn: &Connection) -> Result<Vec<DataFrame>> {
154    ///     let dfs: Vec<DataFrame> = conn
155    ///         .prepare("SELECT * FROM test")?
156    ///         .query_polars([])?
157    ///         .collect();
158    ///
159    ///     Ok(dfs)
160    /// }
161    /// ```
162    ///
163    /// To derive a DataFrame from Vec\<DataFrame>, we can use function
164    /// [polars_core::utils::accumulate_dataframes_vertical_unchecked](https://docs.rs/polars-core/latest/polars_core/utils/fn.accumulate_dataframes_vertical_unchecked.html).
165    ///
166    /// ```rust,no_run
167    /// # use duckdb::{Result, Connection};
168    /// # use polars::prelude::DataFrame;
169    /// # use polars_core::utils::accumulate_dataframes_vertical_unchecked;
170    ///
171    /// fn get_polars_df(conn: &Connection) -> Result<DataFrame> {
172    ///     let mut stmt = conn.prepare("SELECT * FROM test")?;
173    ///     let pl = stmt.query_polars([])?;
174    ///     let df = accumulate_dataframes_vertical_unchecked(pl);
175    ///
176    ///    Ok(df)
177    /// }
178    /// ```
179    ///
180    ///
181    #[cfg(feature = "polars")]
182    #[inline]
183    pub fn query_polars<P: Params>(&mut self, params: P) -> Result<Polars<'_>> {
184        self.execute(params)?;
185        Ok(Polars::new(self))
186    }
187
188    /// Execute the prepared statement, returning a handle to the resulting
189    /// rows.
190    ///
191    /// Due to lifetime restricts, the rows handle returned by `query` does not
192    /// implement the `Iterator` trait. Consider using
193    /// [`query_map`](Statement::query_map) or
194    /// [`query_and_then`](Statement::query_and_then) instead, which do.
195    ///
196    /// ## Example
197    ///
198    /// ### Use without parameters
199    ///
200    /// ```rust,no_run
201    /// # use duckdb::{Connection, Result};
202    /// fn get_names(conn: &Connection) -> Result<Vec<String>> {
203    ///     let mut stmt = conn.prepare("SELECT name FROM people")?;
204    ///     let mut rows = stmt.query([])?;
205    ///
206    ///     let mut names = Vec::new();
207    ///     while let Some(row) = rows.next()? {
208    ///         names.push(row.get(0)?);
209    ///     }
210    ///
211    ///     Ok(names)
212    /// }
213    /// ```
214    ///
215    /// ### Use with positional parameters
216    ///
217    /// ```rust,no_run
218    /// # use duckdb::{Connection, Result};
219    /// fn query(conn: &Connection, name: &str) -> Result<()> {
220    ///     let mut stmt = conn.prepare("SELECT * FROM test where name = ?")?;
221    ///     let mut rows = stmt.query(duckdb::params![name])?;
222    ///     while let Some(row) = rows.next()? {
223    ///         // ...
224    ///     }
225    ///     Ok(())
226    /// }
227    /// ```
228    ///
229    /// Or, equivalently (but without the [`params!`] macro).
230    ///
231    /// ```rust,no_run
232    /// # use duckdb::{Connection, Result};
233    /// fn query(conn: &Connection, name: &str) -> Result<()> {
234    ///     let mut stmt = conn.prepare("SELECT * FROM test where name = ?")?;
235    ///     let mut rows = stmt.query([name])?;
236    ///     while let Some(row) = rows.next()? {
237    ///         // ...
238    ///     }
239    ///     Ok(())
240    /// }
241    /// ```
242    ///
243    /// ## Failure
244    ///
245    /// Will return `Err` if binding parameters fails.
246    #[inline]
247    pub fn query<P: Params>(&mut self, params: P) -> Result<Rows<'_>> {
248        self.execute(params)?;
249        Ok(Rows::new(self))
250    }
251
252    /// Executes the prepared statement and maps a function over the resulting
253    /// rows, returning an iterator over the mapped function results.
254    ///
255    /// `f` is used to transform the _streaming_ iterator into a _standard_
256    /// iterator.
257    ///
258    /// This is equivalent to `stmt.query(params)?.mapped(f)`.
259    ///
260    /// ## Example
261    ///
262    /// ### Use with positional params
263    ///
264    /// ```rust,no_run
265    /// # use duckdb::{Connection, Result};
266    /// fn get_names(conn: &Connection) -> Result<Vec<String>> {
267    ///     let mut stmt = conn.prepare("SELECT name FROM people")?;
268    ///     let rows = stmt.query_map([], |row| row.get(0))?;
269    ///
270    ///     let mut names = Vec::new();
271    ///     for name_result in rows {
272    ///         names.push(name_result?);
273    ///     }
274    ///
275    ///     Ok(names)
276    /// }
277    /// ```
278    ///
279    /// ## Failure
280    ///
281    /// Will return `Err` if binding parameters fails.
282    pub fn query_map<T, P, F>(&mut self, params: P, f: F) -> Result<MappedRows<'_, F>>
283    where
284        P: Params,
285        F: FnMut(&Row<'_>) -> Result<T>,
286    {
287        self.query(params).map(|rows| rows.mapped(f))
288    }
289
290    /// Executes the prepared statement and maps a function over the resulting
291    /// rows, where the function returns a `Result` with `Error` type
292    /// implementing `std::convert::From<Error>` (so errors can be unified).
293    ///
294    /// This is equivalent to `stmt.query(params)?.and_then(f)`.
295    ///
296    /// ## Example
297    ///
298    /// ### Use with positional params
299    ///
300    /// ```no_run
301    /// # use duckdb::{Connection, Result};
302    /// fn get_names(conn: &Connection) -> Result<Vec<String>> {
303    ///     let mut stmt = conn.prepare("SELECT name FROM people WHERE id = ?")?;
304    ///     let rows = stmt.query_and_then(["one"], |row| row.get::<_, String>(0))?;
305    ///
306    ///     let mut persons = Vec::new();
307    ///     for person_result in rows {
308    ///         persons.push(person_result?);
309    ///     }
310    ///
311    ///     Ok(persons)
312    /// }
313    /// ```
314    ///
315    /// # Failure
316    ///
317    /// Will return `Err` if binding parameters fails.
318    #[inline]
319    pub fn query_and_then<T, E, P, F>(&mut self, params: P, f: F) -> Result<AndThenRows<'_, F>>
320    where
321        P: Params,
322        E: convert::From<Error>,
323        F: FnMut(&Row<'_>) -> Result<T, E>,
324    {
325        self.query(params).map(|rows| rows.and_then(f))
326    }
327
328    /// Return `true` if a query in the SQL statement it executes returns one
329    /// or more rows and `false` if the SQL returns an empty set.
330    #[inline]
331    pub fn exists<P: Params>(&mut self, params: P) -> Result<bool> {
332        let mut rows = self.query(params)?;
333        let exists = rows.next()?.is_some();
334        Ok(exists)
335    }
336
337    /// Convenience method to execute a query that is expected to return a
338    /// single row.
339    ///
340    /// If the query returns more than one row, all rows except the first are
341    /// ignored.
342    ///
343    /// Returns `Err(QueryReturnedNoRows)` if no results are returned. If the
344    /// query truly is optional, you can call
345    /// [`.optional()`](crate::OptionalExt::optional) on the result of
346    /// this to get a `Result<Option<T>>` (requires that the trait
347    /// `duckdb::OptionalExt` is imported).
348    ///
349    /// # Failure
350    ///
351    /// Will return `Err` if the underlying DuckDB call fails.
352    pub fn query_row<T, P, F>(&mut self, params: P, f: F) -> Result<T>
353    where
354        P: Params,
355        F: FnOnce(&Row<'_>) -> Result<T>,
356    {
357        self.query(params)?.get_expected_row().and_then(f)
358    }
359
360    /// Convenience method to execute a query that is expected to return exactly
361    /// one row.
362    ///
363    /// Returns `Err(QueryReturnedMoreThanOneRow)` if the query returns more than one row.
364    ///
365    /// Returns `Err(QueryReturnedNoRows)` if no results are returned. If the
366    /// query truly is optional, you can call
367    /// [`.optional()`](crate::OptionalExt::optional) on the result of
368    /// this to get a `Result<Option<T>>` (requires that the trait
369    /// `duckdb::OptionalExt` is imported).
370    ///
371    /// # Failure
372    ///
373    /// Will return `Err` if the underlying DuckDB call fails.
374    pub fn query_one<T, P, F>(&mut self, params: P, f: F) -> Result<T>
375    where
376        P: Params,
377        F: FnOnce(&Row<'_>) -> Result<T>,
378    {
379        let mut rows = self.query(params)?;
380        let row = rows.get_expected_row().and_then(f)?;
381        if rows.next()?.is_some() {
382            return Err(Error::QueryReturnedMoreThanOneRow);
383        }
384        Ok(row)
385    }
386
387    /// Return the row count
388    #[inline]
389    pub fn row_count(&self) -> usize {
390        self.stmt.row_count()
391    }
392
393    /// Get next batch records in arrow-rs
394    #[inline]
395    pub fn step(&self) -> Option<StructArray> {
396        self.stmt.step()
397    }
398
399    /// Get next batch records in arrow-rs in a streaming way
400    #[inline]
401    pub fn stream_step(&self, schema: SchemaRef) -> Option<StructArray> {
402        self.stmt.streaming_step(schema)
403    }
404
405    #[cfg(feature = "polars")]
406    /// Get next batch records in arrow2
407    #[inline]
408    pub fn step2(&self) -> Option<arrow2::array::StructArray> {
409        self.stmt.step2()
410    }
411
412    #[inline]
413    pub(crate) fn bind_parameters<P>(&mut self, params: P) -> Result<()>
414    where
415        P: IntoIterator,
416        P::Item: ToSql,
417    {
418        let expected = self.stmt.bind_parameter_count();
419        let mut index = 0;
420        for p in params.into_iter() {
421            index += 1; // The leftmost SQL parameter has an index of 1.
422            if index > expected {
423                break;
424            }
425            self.bind_parameter(&p, index)?;
426        }
427        if index != expected {
428            Err(Error::InvalidParameterCount(index, expected))
429        } else {
430            Ok(())
431        }
432    }
433
434    /// Return the number of parameters that can be bound to this statement.
435    #[inline]
436    pub fn parameter_count(&self) -> usize {
437        self.stmt.bind_parameter_count()
438    }
439
440    /// Returns the name of the parameter at the given index.
441    ///
442    /// This can be used to query the names of named parameters (e.g., `$param_name`)
443    /// in a prepared statement.
444    ///
445    /// # Arguments
446    ///
447    /// * `one_based_col_index` - One-based parameter index (1 to [`Statement::parameter_count`])
448    ///
449    /// # Returns
450    ///
451    /// * `Ok(String)` - The parameter name (without the `$` prefix for named params, or the numeric index for positional params)
452    /// * `Err(InvalidParameterIndex)` - If the index is out of range
453    ///
454    /// # Example
455    ///
456    /// ```rust,no_run
457    /// # use duckdb::{Connection, Result};
458    /// fn query_parameter_names(conn: &Connection) -> Result<()> {
459    ///     let stmt = conn.prepare("SELECT $foo, $bar")?;
460    ///
461    ///     assert_eq!(stmt.parameter_count(), 2);
462    ///     assert_eq!(stmt.parameter_name(1)?, "foo");
463    ///     assert_eq!(stmt.parameter_name(2)?, "bar");
464    ///
465    ///     Ok(())
466    /// }
467    /// ```
468    #[inline]
469    pub fn parameter_name(&self, idx: usize) -> Result<String> {
470        self.stmt.parameter_name(idx)
471    }
472
473    /// Low level API to directly bind a parameter to a given index.
474    ///
475    /// Note that the index is one-based, that is, the first parameter index is
476    /// 1 and not 0. This is consistent with the DuckDB API and the values given
477    /// to parameters bound as `?NNN`.
478    ///
479    /// The valid values for `one_based_col_index` begin at `1`, and end at
480    /// [`Statement::parameter_count`], inclusive.
481    ///
482    /// # Caveats
483    ///
484    /// This should not generally be used, but is available for special cases
485    /// such as:
486    ///
487    /// - binding parameters where a gap exists.
488    /// - binding named and positional parameters in the same query.
489    /// - separating parameter binding from query execution.
490    ///
491    /// Statements that have had their parameters bound this way should be
492    /// queried or executed by [`Statement::raw_query`] or
493    /// [`Statement::raw_execute`]. Other functions are not guaranteed to work.
494    ///
495    /// # Example
496    ///
497    /// ```rust,no_run
498    /// # use duckdb::{Connection, Result};
499    /// fn query(conn: &Connection) -> Result<()> {
500    ///     let mut stmt = conn.prepare("SELECT * FROM test WHERE name = ? AND value > ?2")?;
501    ///     stmt.raw_bind_parameter(1, "foo")?;
502    ///     stmt.raw_bind_parameter(2, 100)?;
503    ///     let mut rows = stmt.raw_query();
504    ///     while let Some(row) = rows.next()? {
505    ///         // ...
506    ///     }
507    ///     Ok(())
508    /// }
509    /// ```
510    #[inline]
511    pub fn raw_bind_parameter<T: ToSql>(&mut self, one_based_col_index: usize, param: T) -> Result<()> {
512        // This is the same as `bind_parameter` but slightly more ergonomic and
513        // correctly takes `&mut self`.
514        self.bind_parameter(&param, one_based_col_index)
515    }
516
517    /// Low level API to execute a statement given that all parameters were
518    /// bound explicitly with the [`Statement::raw_bind_parameter`] API.
519    ///
520    /// # Caveats
521    ///
522    /// Any unbound parameters will have `NULL` as their value.
523    ///
524    /// This should not generally be used outside of special cases, and
525    /// functions in the [`Statement::execute`] family should be preferred.
526    ///
527    /// # Failure
528    ///
529    /// Will return `Err` if the executed statement returns rows (in which case
530    /// `query` should be used instead), or the underlying DuckDB call fails.
531    #[inline]
532    pub fn raw_execute(&mut self) -> Result<usize> {
533        self.execute_with_bound_parameters()
534    }
535
536    /// Low level API to get `Rows` for this query given that all parameters
537    /// were bound explicitly with the [`Statement::raw_bind_parameter`] API.
538    ///
539    /// # Caveats
540    ///
541    /// Any unbound parameters will have `NULL` as their value.
542    ///
543    /// This should not generally be used outside of special cases, and
544    /// functions in the [`Statement::query`] family should be preferred.
545    ///
546    /// Note that if the SQL does not return results, [`Statement::raw_execute`]
547    /// should be used instead.
548    #[inline]
549    pub fn raw_query(&self) -> Rows<'_> {
550        Rows::new(self)
551    }
552
553    /// Returns the underlying schema of the prepared statement.
554    ///
555    /// # Caveats
556    /// Panics if the query has not been [`execute`](Statement::execute)d yet.
557    #[inline]
558    pub fn schema(&self) -> SchemaRef {
559        self.stmt.schema()
560    }
561
562    // generic because many of these branches can constant fold away.
563    fn bind_parameter<P: ?Sized + ToSql>(&self, param: &P, col: usize) -> Result<()> {
564        let value = param.to_sql()?;
565
566        let ptr = unsafe { self.stmt.ptr() };
567        let value = match value {
568            ToSqlOutput::Borrowed(v) => v,
569            ToSqlOutput::Owned(ref v) => ValueRef::from(v),
570        };
571        // TODO: bind more
572        let rc = match value {
573            ValueRef::Null => unsafe { ffi::duckdb_bind_null(ptr, col as u64) },
574            ValueRef::Boolean(i) => unsafe { ffi::duckdb_bind_boolean(ptr, col as u64, i) },
575            ValueRef::TinyInt(i) => unsafe { ffi::duckdb_bind_int8(ptr, col as u64, i) },
576            ValueRef::SmallInt(i) => unsafe { ffi::duckdb_bind_int16(ptr, col as u64, i) },
577            ValueRef::Int(i) => unsafe { ffi::duckdb_bind_int32(ptr, col as u64, i) },
578            ValueRef::BigInt(i) => unsafe { ffi::duckdb_bind_int64(ptr, col as u64, i) },
579            ValueRef::HugeInt(i) => unsafe {
580                let hi = ffi::duckdb_hugeint {
581                    lower: i as u64,
582                    upper: (i >> 64) as i64,
583                };
584                ffi::duckdb_bind_hugeint(ptr, col as u64, hi)
585            },
586            ValueRef::UTinyInt(i) => unsafe { ffi::duckdb_bind_uint8(ptr, col as u64, i) },
587            ValueRef::USmallInt(i) => unsafe { ffi::duckdb_bind_uint16(ptr, col as u64, i) },
588            ValueRef::UInt(i) => unsafe { ffi::duckdb_bind_uint32(ptr, col as u64, i) },
589            ValueRef::UBigInt(i) => unsafe { ffi::duckdb_bind_uint64(ptr, col as u64, i) },
590            ValueRef::Float(r) => unsafe { ffi::duckdb_bind_float(ptr, col as u64, r) },
591            ValueRef::Double(r) => unsafe { ffi::duckdb_bind_double(ptr, col as u64, r) },
592            ValueRef::Text(s) => unsafe {
593                ffi::duckdb_bind_varchar_length(ptr, col as u64, s.as_ptr() as *const c_char, s.len() as u64)
594            },
595            ValueRef::Blob(b) => unsafe {
596                ffi::duckdb_bind_blob(ptr, col as u64, b.as_ptr() as *const c_void, b.len() as u64)
597            },
598            ValueRef::Timestamp(u, i) => unsafe {
599                let micros = match u {
600                    TimeUnit::Second => i * 1_000_000,
601                    TimeUnit::Millisecond => i * 1_000,
602                    TimeUnit::Microsecond => i,
603                    TimeUnit::Nanosecond => i / 1_000,
604                };
605                ffi::duckdb_bind_timestamp(ptr, col as u64, ffi::duckdb_timestamp { micros })
606            },
607            ValueRef::Interval { months, days, nanos } => unsafe {
608                let micros = nanos / 1_000;
609                ffi::duckdb_bind_interval(ptr, col as u64, ffi::duckdb_interval { months, days, micros })
610            },
611            _ => unreachable!("not supported: {}", value.data_type()),
612        };
613        result_from_duckdb_prepare(rc, ptr)
614    }
615
616    #[inline]
617    fn execute_with_bound_parameters(&mut self) -> Result<usize> {
618        self.stmt.execute()
619    }
620
621    /// Safety: This is unsafe, because using `sqlite3_stmt` after the
622    /// connection has closed is illegal, but `RawStatement` does not enforce
623    /// this, as it loses our protective `'conn` lifetime bound.
624    #[inline]
625    pub(crate) unsafe fn into_raw(mut self) -> RawStatement {
626        let mut stmt = RawStatement::new(ptr::null_mut());
627        mem::swap(&mut stmt, &mut self.stmt);
628        stmt
629    }
630}
631
632impl fmt::Debug for Statement<'_> {
633    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
634        let sql = if self.stmt.is_null() {
635            Ok("")
636        } else {
637            str::from_utf8(self.stmt.sql().unwrap().to_bytes())
638        };
639        f.debug_struct("Statement")
640            .field("conn", self.conn)
641            .field("stmt", &self.stmt)
642            .field("sql", &sql)
643            .finish()
644    }
645}
646
647impl Statement<'_> {
648    #[inline]
649    pub(super) fn new(conn: &Connection, stmt: RawStatement) -> Statement<'_> {
650        Statement { conn, stmt }
651    }
652}
653
654#[cfg(test)]
655mod test {
656    use crate::{params_from_iter, types::ToSql, Connection, Error, Result};
657
658    #[test]
659    fn test_execute() -> Result<()> {
660        let db = Connection::open_in_memory()?;
661        db.execute_batch("CREATE TABLE foo(x INTEGER)")?;
662
663        assert_eq!(db.execute("INSERT INTO foo(x) VALUES (?)", [&2i32])?, 1);
664        assert_eq!(db.execute("INSERT INTO foo(x) VALUES (?)", [&3i32])?, 1);
665
666        // TODO(wangfenjin): No column type for SUM(x)?
667        assert_eq!(
668            5i32,
669            db.query_row::<i32, _, _>("SELECT SUM(x) FROM foo WHERE x > ?", [&0i32], |r| r.get(0))?
670        );
671        assert_eq!(
672            3i32,
673            db.query_row::<i32, _, _>("SELECT SUM(x) FROM foo WHERE x > ?", [&2i32], |r| r.get(0))?
674        );
675        Ok(())
676    }
677
678    #[test]
679    fn test_stmt_execute() -> Result<()> {
680        let db = Connection::open_in_memory()?;
681        let sql = r#"
682        CREATE SEQUENCE seq;
683        CREATE TABLE test (id INTEGER DEFAULT NEXTVAL('seq'), name TEXT NOT NULL, flag INTEGER);
684        "#;
685        db.execute_batch(sql)?;
686
687        let mut stmt = db.prepare("INSERT INTO test (name) VALUES (?)")?;
688        stmt.execute([&"one"])?;
689
690        let mut stmt = db.prepare("SELECT COUNT(*) FROM test WHERE name = ?")?;
691        assert_eq!(1i32, stmt.query_row::<i32, _, _>([&"one"], |r| r.get(0))?);
692        Ok(())
693    }
694
695    #[test]
696    fn test_query() -> Result<()> {
697        let db = Connection::open_in_memory()?;
698        let sql = r#"
699        CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, flag INTEGER);
700        INSERT INTO test(id, name) VALUES (1, 'one');
701        "#;
702        db.execute_batch(sql)?;
703
704        let mut stmt = db.prepare("SELECT id FROM test where name = ?")?;
705        {
706            let id: i32 = stmt.query_one([&"one"], |r| r.get(0))?;
707            assert_eq!(id, 1);
708        }
709        Ok(())
710    }
711
712    #[test]
713    fn test_query_and_then() -> Result<()> {
714        let db = Connection::open_in_memory()?;
715        let sql = r#"
716        CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, flag INTEGER);
717        INSERT INTO test(id, name) VALUES (1, 'one');
718        INSERT INTO test(id, name) VALUES (2, 'one');
719        "#;
720        db.execute_batch(sql)?;
721
722        let mut stmt = db.prepare("SELECT id FROM test where name = ? ORDER BY id ASC")?;
723        let mut rows = stmt.query_and_then([&"one"], |row| {
724            let id: i32 = row.get(0)?;
725            if id == 1 {
726                Ok(id)
727            } else {
728                Err(Error::ExecuteReturnedResults)
729            }
730        })?;
731
732        // first row should be Ok
733        let doubled_id: i32 = rows.next().unwrap()?;
734        assert_eq!(1, doubled_id);
735
736        // second row should be Err
737        #[allow(clippy::match_wild_err_arm)]
738        match rows.next().unwrap() {
739            Ok(_) => panic!("invalid Ok"),
740            Err(Error::ExecuteReturnedResults) => (),
741            Err(_) => panic!("invalid Err"),
742        }
743        Ok(())
744    }
745
746    #[test]
747    fn test_unbound_parameters_are_error() -> Result<()> {
748        let db = Connection::open_in_memory()?;
749        let sql = "CREATE TABLE test (x TEXT, y TEXT)";
750        db.execute_batch(sql)?;
751
752        let mut stmt = db.prepare("INSERT INTO test (x, y) VALUES (?, ?)")?;
753        assert!(stmt.execute([&"one"]).is_err());
754        Ok(())
755    }
756
757    #[test]
758    fn test_insert_empty_text_is_none() -> Result<()> {
759        let db = Connection::open_in_memory()?;
760        let sql = "CREATE TABLE test (x TEXT, y TEXT)";
761        db.execute_batch(sql)?;
762
763        let mut stmt = db.prepare("INSERT INTO test (x) VALUES (?)")?;
764        stmt.execute([&"one"])?;
765
766        let result: Option<String> = db.query_row("SELECT y FROM test WHERE x = 'one'", [], |row| row.get(0))?;
767        assert!(result.is_none());
768        Ok(())
769    }
770
771    #[test]
772    fn test_raw_binding() -> Result<()> {
773        let db = Connection::open_in_memory()?;
774        db.execute_batch("CREATE TABLE test (name TEXT, value INTEGER)")?;
775        {
776            let mut stmt = db.prepare("INSERT INTO test (name, value) VALUES (?, ?)")?;
777
778            stmt.raw_bind_parameter(2, 50i32)?;
779            stmt.raw_bind_parameter(1, "example")?;
780            let n = stmt.raw_execute()?;
781            assert_eq!(n, 1);
782        }
783
784        {
785            let mut stmt = db.prepare("SELECT name, value FROM test WHERE value = ?")?;
786            stmt.raw_bind_parameter(1, 50)?;
787            stmt.raw_execute()?;
788            let mut rows = stmt.raw_query();
789            {
790                let row = rows.next()?.unwrap();
791                let name: String = row.get(0)?;
792                assert_eq!(name, "example");
793                let value: i32 = row.get(1)?;
794                assert_eq!(value, 50);
795            }
796            assert!(rows.next()?.is_none());
797        }
798
799        {
800            let db = Connection::open_in_memory()?;
801            db.execute_batch("CREATE TABLE test (name TEXT, value UINTEGER)")?;
802            let mut stmt = db.prepare("INSERT INTO test(name, value) VALUES (?, ?)")?;
803            stmt.raw_bind_parameter(1, "negative")?;
804            stmt.raw_bind_parameter(2, u32::MAX)?;
805            let n = stmt.raw_execute()?;
806            assert_eq!(n, 1);
807            assert_eq!(
808                u32::MAX,
809                db.query_row::<u32, _, _>("SELECT value FROM test", [], |r| r.get(0))?
810            );
811        }
812
813        {
814            let db = Connection::open_in_memory()?;
815            db.execute_batch("CREATE TABLE test (name TEXT, value UBIGINT)")?;
816            let mut stmt = db.prepare("INSERT INTO test(name, value) VALUES (?, ?)")?;
817            stmt.raw_bind_parameter(1, "negative")?;
818            stmt.raw_bind_parameter(2, u64::MAX)?;
819            let n = stmt.raw_execute()?;
820            assert_eq!(n, 1);
821            assert_eq!(
822                u64::MAX,
823                db.query_row::<u64, _, _>("SELECT value FROM test", [], |r| r.get(0))?
824            );
825        }
826
827        Ok(())
828    }
829
830    #[test]
831    #[cfg_attr(windows, ignore = "Windows doesn't allow concurrent writes to a file")]
832    fn test_insert_duplicate() -> Result<()> {
833        let db = Connection::open_in_memory()?;
834        db.execute_batch("CREATE TABLE foo(x INTEGER UNIQUE)")?;
835        let mut stmt = db.prepare("INSERT INTO foo (x) VALUES (?)")?;
836        // TODO(wangfenjin): currently always 1
837        stmt.insert([1i32])?;
838        stmt.insert([2i32])?;
839        assert!(stmt.insert([1i32]).is_err());
840        let mut multi = db.prepare("INSERT INTO foo (x) SELECT 3 UNION ALL SELECT 4")?;
841        match multi.insert([]).unwrap_err() {
842            Error::StatementChangedRows(2) => (),
843            err => panic!("Unexpected error {err}"),
844        }
845        Ok(())
846    }
847
848    #[test]
849    fn test_insert_different_tables() -> Result<()> {
850        // Test for https://github.com/duckdb/duckdb/issues/171
851        let db = Connection::open_in_memory()?;
852        db.execute_batch(
853            r"
854            CREATE TABLE foo(x INTEGER);
855            CREATE TABLE bar(x INTEGER);
856        ",
857        )?;
858
859        db.prepare("INSERT INTO foo VALUES (10)")?.insert([])?;
860        db.prepare("INSERT INTO bar VALUES (10)")?.insert([])?;
861        Ok(())
862    }
863
864    // When using RETURNING clauses, DuckDB core treats the statement as a query result instead of a modification
865    // statement. This causes execute() to return 0 changed rows and insert() to fail with an error.
866    // This test demonstrates current behavior and proper usage patterns for RETURNING clauses.
867    #[test]
868    fn test_insert_with_returning_clause() -> Result<()> {
869        let db = Connection::open_in_memory()?;
870        db.execute_batch(
871            "CREATE SEQUENCE location_id_seq START WITH 1 INCREMENT BY 1;
872             CREATE TABLE location (
873                 id INTEGER PRIMARY KEY DEFAULT nextval('location_id_seq'),
874                 name TEXT NOT NULL
875             )",
876        )?;
877
878        // INSERT without RETURNING using execute
879        let changes = db.execute("INSERT INTO location (name) VALUES (?)", ["test1"])?;
880        assert_eq!(changes, 1);
881
882        // INSERT with RETURNING using execute - returns 0 (known limitation)
883        let changes = db.execute("INSERT INTO location (name) VALUES (?) RETURNING id", ["test2"])?;
884        assert_eq!(changes, 0);
885
886        // Verify the row was actually inserted despite returning 0
887        let count: i64 = db.query_row("SELECT COUNT(*) FROM location", [], |r| r.get(0))?;
888        assert_eq!(count, 2);
889
890        // INSERT without RETURNING using insert
891        let mut stmt = db.prepare("INSERT INTO location (name) VALUES (?)")?;
892        stmt.insert(["test3"])?;
893
894        // INSERT with RETURNING using insert - fails (known limitation)
895        let mut stmt = db.prepare("INSERT INTO location (name) VALUES (?) RETURNING id")?;
896        let result = stmt.insert(["test4"]);
897        assert!(matches!(result, Err(Error::StatementChangedRows(0))));
898
899        // Verify the row was still inserted despite the error
900        let count: i64 = db.query_row("SELECT COUNT(*) FROM location", [], |r| r.get(0))?;
901        assert_eq!(count, 4);
902
903        // Proper way to use RETURNING - with query_row
904        let id: i64 = db.query_row("INSERT INTO location (name) VALUES (?) RETURNING id", ["test5"], |r| {
905            r.get(0)
906        })?;
907        assert_eq!(id, 5);
908
909        // Proper way to use RETURNING - with query_map
910        let mut stmt = db.prepare("INSERT INTO location (name) VALUES (?) RETURNING id")?;
911        let ids: Vec<i64> = stmt
912            .query_map(["test6"], |row| row.get(0))?
913            .collect::<Result<Vec<_>>>()?;
914        assert_eq!(ids.len(), 1);
915        assert_eq!(ids[0], 6);
916
917        // Proper way to use RETURNING - with query_one
918        let id: i64 = db
919            .prepare("INSERT INTO location (name) VALUES (?) RETURNING id")?
920            .query_one(["test7"], |r| r.get(0))?;
921        assert_eq!(id, 7);
922
923        // Multiple RETURNING columns
924        let (id, name): (i64, String) = db.query_row(
925            "INSERT INTO location (name) VALUES (?) RETURNING id, name",
926            ["test8"],
927            |r| Ok((r.get(0)?, r.get(1)?)),
928        )?;
929        assert_eq!(id, 8);
930        assert_eq!(name, "test8");
931
932        Ok(())
933    }
934
935    #[test]
936    fn test_exists() -> Result<()> {
937        let db = Connection::open_in_memory()?;
938        let sql = "BEGIN;
939                   CREATE TABLE foo(x INTEGER);
940                   INSERT INTO foo VALUES(1);
941                   INSERT INTO foo VALUES(2);
942                   END;";
943        db.execute_batch(sql)?;
944        let mut stmt = db.prepare("SELECT 1 FROM foo WHERE x = ?")?;
945        assert!(stmt.exists([1i32])?);
946        assert!(stmt.exists([2i32])?);
947        assert!(!stmt.exists([0i32])?);
948        Ok(())
949    }
950
951    #[test]
952    fn test_query_row() -> Result<()> {
953        let db = Connection::open_in_memory()?;
954        let sql = "BEGIN;
955                   CREATE TABLE foo(x INTEGER, y INTEGER);
956                   INSERT INTO foo VALUES(1, 3);
957                   INSERT INTO foo VALUES(2, 4);
958                   END;";
959        db.execute_batch(sql)?;
960        let mut stmt = db.prepare("SELECT y FROM foo WHERE x = ?")?;
961        let y: Result<i32> = stmt.query_row([1i32], |r| r.get(0));
962        assert_eq!(3i32, y?);
963        Ok(())
964    }
965
966    #[test]
967    fn test_query_one() -> Result<()> {
968        let db = Connection::open_in_memory()?;
969        let sql = "BEGIN;
970                   CREATE TABLE foo(x INTEGER, y INTEGER);
971                   INSERT INTO foo VALUES(1, 3);
972                   INSERT INTO foo VALUES(2, 4);
973                   END;";
974        db.execute_batch(sql)?;
975
976        // Exactly one row
977        let y: i32 = db
978            .prepare("SELECT y FROM foo WHERE x = ?")?
979            .query_one([1], |r| r.get(0))?;
980        assert_eq!(y, 3);
981
982        // No rows
983        let res: Result<i32> = db
984            .prepare("SELECT y FROM foo WHERE x = ?")?
985            .query_one([99], |r| r.get(0));
986        assert_eq!(res.unwrap_err(), Error::QueryReturnedNoRows);
987
988        // Multiple rows
989        let res: Result<i32> = db.prepare("SELECT y FROM foo")?.query_one([], |r| r.get(0));
990        assert_eq!(res.unwrap_err(), Error::QueryReturnedMoreThanOneRow);
991
992        Ok(())
993    }
994
995    #[test]
996    fn test_query_one_optional() -> Result<()> {
997        use crate::OptionalExt;
998
999        let db = Connection::open_in_memory()?;
1000        let sql = "BEGIN;
1001                   CREATE TABLE foo(x INTEGER, y INTEGER);
1002                   INSERT INTO foo VALUES(1, 3);
1003                   INSERT INTO foo VALUES(2, 4);
1004                   END;";
1005        db.execute_batch(sql)?;
1006
1007        // Exactly one row
1008        let y: Option<i32> = db
1009            .prepare("SELECT y FROM foo WHERE x = ?")?
1010            .query_one([1], |r| r.get(0))
1011            .optional()?;
1012        assert_eq!(y, Some(3));
1013
1014        // No rows
1015        let y: Option<i32> = db
1016            .prepare("SELECT y FROM foo WHERE x = ?")?
1017            .query_one([99], |r| r.get(0))
1018            .optional()?;
1019        assert_eq!(y, None);
1020
1021        // Multiple rows - should still return error (not converted by optional)
1022        let res = db
1023            .prepare("SELECT y FROM foo")?
1024            .query_one([], |r| r.get::<_, i32>(0))
1025            .optional();
1026        assert_eq!(res.unwrap_err(), Error::QueryReturnedMoreThanOneRow);
1027
1028        Ok(())
1029    }
1030
1031    #[test]
1032    fn test_query_by_column_name() -> Result<()> {
1033        let db = Connection::open_in_memory()?;
1034        let sql = "BEGIN;
1035                   CREATE TABLE foo(x INTEGER, y INTEGER);
1036                   INSERT INTO foo VALUES(1, 3);
1037                   END;";
1038        db.execute_batch(sql)?;
1039        let mut stmt = db.prepare("SELECT y FROM foo")?;
1040        let y: Result<i64> = stmt.query_row([], |r| r.get("y"));
1041        assert_eq!(3i64, y?);
1042        Ok(())
1043    }
1044
1045    #[test]
1046    fn test_get_schema_of_executed_result() -> Result<()> {
1047        use arrow::datatypes::{DataType, Field, Schema};
1048        let db = Connection::open_in_memory()?;
1049        let sql = "BEGIN;
1050                   CREATE TABLE foo(x STRING, y INTEGER);
1051                   INSERT INTO foo VALUES('hello', 3);
1052                   END;";
1053        db.execute_batch(sql)?;
1054        let mut stmt = db.prepare("SELECT x, y FROM foo")?;
1055        let _ = stmt.execute([]);
1056        let schema = stmt.schema();
1057        assert_eq!(
1058            *schema,
1059            Schema::new(vec![
1060                Field::new("x", DataType::Utf8, true),
1061                Field::new("y", DataType::Int32, true)
1062            ])
1063        );
1064        Ok(())
1065    }
1066
1067    #[test]
1068    #[should_panic(expected = "called `Option::unwrap()` on a `None` value")]
1069    fn test_unexecuted_schema_panics() {
1070        let db = Connection::open_in_memory().unwrap();
1071        let sql = "BEGIN;
1072                   CREATE TABLE foo(x STRING, y INTEGER);
1073                   INSERT INTO foo VALUES('hello', 3);
1074                   END;";
1075        db.execute_batch(sql).unwrap();
1076        let stmt = db.prepare("SELECT x, y FROM foo").unwrap();
1077        let _ = stmt.schema();
1078    }
1079
1080    #[test]
1081    fn test_query_by_column_name_ignore_case() -> Result<()> {
1082        let db = Connection::open_in_memory()?;
1083        let sql = "BEGIN;
1084                   CREATE TABLE foo(x INTEGER, y INTEGER);
1085                   INSERT INTO foo VALUES(1, 3);
1086                   END;";
1087        db.execute_batch(sql)?;
1088        let mut stmt = db.prepare("SELECT y as Y FROM foo")?;
1089        let y: Result<i64> = stmt.query_row([], |r| r.get("y"));
1090        assert_eq!(3i64, y?);
1091        Ok(())
1092    }
1093
1094    #[test]
1095    fn test_bind_parameters() -> Result<()> {
1096        let db = Connection::open_in_memory()?;
1097        // dynamic slice:
1098        db.query_row("SELECT ?1, ?2, ?3", [&1u8 as &dyn ToSql, &"one", &Some("one")], |row| {
1099            row.get::<_, u8>(0)
1100        })?;
1101        // existing collection:
1102        let data = vec![1, 2, 3];
1103        db.query_row("SELECT ?1, ?2, ?3", params_from_iter(&data), |row| row.get::<_, u8>(0))?;
1104        db.query_row("SELECT ?1, ?2, ?3", params_from_iter(data.as_slice()), |row| {
1105            row.get::<_, u8>(0)
1106        })?;
1107        db.query_row("SELECT ?1, ?2, ?3", params_from_iter(data), |row| row.get::<_, u8>(0))?;
1108
1109        let data: std::collections::BTreeSet<String> =
1110            ["one", "two", "three"].iter().map(|s| (*s).to_string()).collect();
1111        db.query_row("SELECT ?1, ?2, ?3", params_from_iter(&data), |row| {
1112            row.get::<_, String>(0)
1113        })?;
1114
1115        let data = [0; 3];
1116        db.query_row("SELECT ?1, ?2, ?3", params_from_iter(&data), |row| row.get::<_, u8>(0))?;
1117        db.query_row("SELECT ?1, ?2, ?3", params_from_iter(data.iter()), |row| {
1118            row.get::<_, u8>(0)
1119        })?;
1120        Ok(())
1121    }
1122
1123    #[test]
1124    fn test_empty_stmt() -> Result<()> {
1125        let conn = Connection::open_in_memory()?;
1126        let stmt = conn.prepare("");
1127        assert!(stmt.is_err());
1128
1129        Ok(())
1130    }
1131
1132    #[test]
1133    fn test_comment_empty_stmt() -> Result<()> {
1134        let conn = Connection::open_in_memory()?;
1135        assert!(conn.prepare("/*SELECT 1;*/").is_err());
1136        Ok(())
1137    }
1138
1139    #[test]
1140    fn test_comment_and_sql_stmt() -> Result<()> {
1141        let conn = Connection::open_in_memory()?;
1142        let mut stmt = conn.prepare("/*...*/ SELECT 1;")?;
1143        stmt.execute([])?;
1144        assert_eq!(1, stmt.column_count());
1145        Ok(())
1146    }
1147
1148    #[test]
1149    fn test_nul_byte() -> Result<()> {
1150        let db = Connection::open_in_memory()?;
1151        let expected = "a\x00b";
1152        let actual: String = db.query_row("SELECT CAST(? AS VARCHAR)", [expected], |row| row.get(0))?;
1153        assert_eq!(expected, actual);
1154        Ok(())
1155    }
1156
1157    #[test]
1158    fn test_parameter_name() -> Result<()> {
1159        let db = Connection::open_in_memory()?;
1160
1161        {
1162            let stmt = db.prepare("SELECT $foo, $bar")?;
1163
1164            assert_eq!(stmt.parameter_count(), 2);
1165            assert_eq!(stmt.parameter_name(1)?, "foo");
1166            assert_eq!(stmt.parameter_name(2)?, "bar");
1167
1168            assert!(matches!(stmt.parameter_name(0), Err(Error::InvalidParameterIndex(0))));
1169            assert!(matches!(
1170                stmt.parameter_name(100),
1171                Err(Error::InvalidParameterIndex(100))
1172            ));
1173        }
1174
1175        // Positional parameters return their index number as the name
1176        {
1177            let stmt = db.prepare("SELECT ?, ?")?;
1178            assert_eq!(stmt.parameter_count(), 2);
1179            assert_eq!(stmt.parameter_name(1)?, "1");
1180            assert_eq!(stmt.parameter_name(2)?, "2");
1181        }
1182
1183        // Numbered positional parameters also return their number
1184        {
1185            let stmt = db.prepare("SELECT ?1, ?2")?;
1186            assert_eq!(stmt.parameter_count(), 2);
1187            assert_eq!(stmt.parameter_name(1)?, "1");
1188            assert_eq!(stmt.parameter_name(2)?, "2");
1189        }
1190
1191        Ok(())
1192    }
1193
1194    #[test]
1195    fn test_bind_named_parameters_manually() -> Result<()> {
1196        use std::collections::HashMap;
1197
1198        let db = Connection::open_in_memory()?;
1199        let mut stmt = db.prepare("SELECT $foo > $bar")?;
1200
1201        let mut params: HashMap<String, i32> = HashMap::new();
1202        params.insert("foo".to_string(), 42);
1203        params.insert("bar".to_string(), 23);
1204
1205        for idx in 1..=stmt.parameter_count() {
1206            let name = stmt.parameter_name(idx)?;
1207            if let Some(value) = params.get(&name) {
1208                stmt.raw_bind_parameter(idx, value)?;
1209            }
1210        }
1211
1212        stmt.raw_execute()?;
1213
1214        let mut rows = stmt.raw_query();
1215        let row = rows.next()?.unwrap();
1216        let result: bool = row.get(0)?;
1217        assert!(result);
1218
1219        Ok(())
1220    }
1221}
duckdb/statement.rs

duckdb/
statement.rs
