Lightweight 0.20250904.0
Loading...
Searching...
No Matches
SqlStatement.hpp
1// SPDX-License-Identifier: Apache-2.0
2
3#pragma once
4
5#if defined(_WIN32) || defined(_WIN64)
6 #include <Windows.h>
7#endif
8
9#include "Api.hpp"
10#include "SqlConnection.hpp"
11#include "SqlDataBinder.hpp"
12#include "SqlQuery.hpp"
13#include "SqlServerType.hpp"
14#include "Utils.hpp"
15
16#include <cstring>
17#include <expected>
18#include <optional>
19#include <ranges>
20#include <source_location>
21#include <stdexcept>
22#include <type_traits>
23#include <vector>
24
25#include <sql.h>
26#include <sqlext.h>
27#include <sqlspi.h>
28#include <sqltypes.h>
29
30namespace Lightweight
31{
32
33/// @brief Represents an SQL query object, that provides a ToSql() method.
34template <typename QueryObject>
35concept SqlQueryObject = requires(QueryObject const& queryObject) {
36 { queryObject.ToSql() } -> std::convertible_to<std::string>;
37};
38
39class SqlResultCursor;
40class SqlVariantRowCursor;
41
42/// @brief High level API for (prepared) raw SQL statements
43///
44/// SQL prepared statement lifecycle:
45/// 1. Prepare the statement
46/// 2. Optionally bind output columns to local variables
47/// 3. Execute the statement (optionally with input parameters)
48/// 4. Fetch rows (if any)
49/// 5. Repeat steps 3 and 4 as needed
50class [[nodiscard]] SqlStatement final: public SqlDataBinderCallback
51{
52 public:
53 /// Construct a new SqlStatement object, using a new connection, and connect to the default database.
54 LIGHTWEIGHT_API SqlStatement();
55
56 LIGHTWEIGHT_API SqlStatement(SqlStatement&& other) noexcept;
57 LIGHTWEIGHT_API SqlStatement& operator=(SqlStatement&& other) noexcept;
58
59 SqlStatement(SqlStatement const&) noexcept = delete;
60 SqlStatement& operator=(SqlStatement const&) noexcept = delete;
61
62 /// Construct a new SqlStatement object, using the given connection.
63 LIGHTWEIGHT_API explicit SqlStatement(SqlConnection& relatedConnection);
64
65 /// Construct a new empty SqlStatement object. No SqlConnection is associated with this statement.
66 LIGHTWEIGHT_API explicit SqlStatement(std::nullopt_t /*nullopt*/);
67
68 LIGHTWEIGHT_API ~SqlStatement() noexcept final;
69
70 [[nodiscard]] LIGHTWEIGHT_API bool IsAlive() const noexcept;
71
72 [[nodiscard]] LIGHTWEIGHT_API bool IsPrepared() const noexcept;
73
74 /// Retrieves the connection associated with this statement.
75 [[nodiscard]] LIGHTWEIGHT_API SqlConnection& Connection() noexcept;
76
77 /// Retrieves the connection associated with this statement.
78 [[nodiscard]] LIGHTWEIGHT_API SqlConnection const& Connection() const noexcept;
79
80 /// Retrieves the last error information with respect to this SQL statement handle.
81 [[nodiscard]] LIGHTWEIGHT_API SqlErrorInfo LastError() const;
82
83 /// Creates a new query builder for the given table, compatible with the SQL server being connected.
84 LIGHTWEIGHT_API SqlQueryBuilder Query(std::string_view const& table = {}) const;
85
86 /// Creates a new query builder for the given table with an alias, compatible with the SQL server being connected.
87 [[nodiscard]] LIGHTWEIGHT_API SqlQueryBuilder QueryAs(std::string_view const& table,
88 std::string_view const& tableAlias) const;
89
90 /// Retrieves the native handle of the statement.
91 [[nodiscard]] LIGHTWEIGHT_API SQLHSTMT NativeHandle() const noexcept;
92
93 /// Prepares the statement for execution.
94 ///
95 /// @note When preparing a new SQL statement the previously executed statement, yielding a result set,
96 /// must have been closed.
97 LIGHTWEIGHT_API void Prepare(std::string_view query) &;
98
99 LIGHTWEIGHT_API SqlStatement Prepare(std::string_view query) &&;
100
101 /// Prepares the statement for execution.
102 ///
103 /// @note When preparing a new SQL statement the previously executed statement, yielding a result set,
104 /// must have been closed.
105 void Prepare(SqlQueryObject auto const& queryObject) &;
106
107 SqlStatement Prepare(SqlQueryObject auto const& queryObject) &&;
108
109 [[nodiscard]] std::string const& PreparedQuery() const noexcept;
110
111 template <SqlInputParameterBinder Arg>
112 void BindInputParameter(SQLSMALLINT columnIndex, Arg const& arg);
113
114 template <SqlInputParameterBinder Arg, typename ColumnName>
115 void BindInputParameter(SQLSMALLINT columnIndex, Arg const& arg, ColumnName&& columnNameHint);
116
117 /// Binds the given arguments to the prepared statement to store the fetched data to.
118 ///
119 /// @note The statement must be prepared before calling this function.
120 template <SqlOutputColumnBinder... Args>
121 void BindOutputColumns(Args*... args);
122
123 /// Binds the given arguments to the prepared statement to store the fetched data to.
124 ///
125 /// @param records The records to bind each member to.
126 ///
127 /// @note The statement must be prepared before calling this function.
128 /// @note The records must be aggregate types, i.e. classes with public members and no user-defined constructors.
129 template <typename... Records>
130 requires(((std::is_class_v<Records> && std::is_aggregate_v<Records>) && ...))
131 void BindOutputColumnsToRecord(Records*... records);
132
133 template <SqlOutputColumnBinder T>
134 void BindOutputColumn(SQLUSMALLINT columnIndex, T* arg);
135
136 /// Binds the given arguments to the prepared statement and executes it.
137 template <SqlInputParameterBinder... Args>
138 void Execute(Args const&... args);
139
140 /// Binds the given arguments to the prepared statement and executes it.
141 LIGHTWEIGHT_API void ExecuteWithVariants(std::vector<SqlVariant> const& args);
142
143 /// Executes the prepared statement on a batch of data.
144 ///
145 /// Each parameter represents a column, to be bound as input parameter.
146 /// The element types of each column container must be explicitly supported.
147 ///
148 /// In order to support column value types, their underlying storage must be contiguous.
149 /// Also the input range itself must be contiguous.
150 /// If any of these conditions are not met, the function will not compile - use ExecuteBatch() instead.
151 template <SqlInputParameterBatchBinder FirstColumnBatch, std::ranges::contiguous_range... MoreColumnBatches>
152 void ExecuteBatchNative(FirstColumnBatch const& firstColumnBatch, MoreColumnBatches const&... moreColumnBatches);
153
154 /// Executes the prepared statement on a batch of data.
155 ///
156 /// Each parameter represents a column, to be bound as input parameter,
157 /// and the number of elements in these bound column containers will
158 /// mandate how many executions will happen.
159 ///
160 /// This function will bind and execute each row separately,
161 /// which is less efficient than ExecuteBatchNative(), but works non-contiguous input ranges.
162 template <SqlInputParameterBatchBinder FirstColumnBatch, std::ranges::range... MoreColumnBatches>
163 void ExecuteBatchSoft(FirstColumnBatch const& firstColumnBatch, MoreColumnBatches const&... moreColumnBatches);
164
165 /// Executes the prepared statement on a batch of data.
166 ///
167 /// Each parameter represents a column, to be bound as input parameter,
168 /// and the number of elements in these bound column containers will
169 /// mandate how many executions will happen.
170 template <SqlInputParameterBatchBinder FirstColumnBatch, std::ranges::range... MoreColumnBatches>
171 void ExecuteBatch(FirstColumnBatch const& firstColumnBatch, MoreColumnBatches const&... moreColumnBatches);
172
173 /// Executes the given query directly.
174 LIGHTWEIGHT_API void ExecuteDirect(std::string_view const& query,
175 std::source_location location = std::source_location::current());
176
177 /// Executes the given query directly.
178 void ExecuteDirect(SqlQueryObject auto const& query, std::source_location location = std::source_location::current());
179
180 /// Executes an SQL migration query, as created b the callback.
181 template <typename Callable>
182 requires std::invocable<Callable, SqlMigrationQueryBuilder&>
183 void MigrateDirect(Callable const& callable, std::source_location location = std::source_location::current());
184
185 /// Executes the given query, assuming that only one result row and column is affected, that one will be
186 /// returned.
187 template <typename T>
188 requires(!std::same_as<T, SqlVariant>)
189 [[nodiscard]] std::optional<T> ExecuteDirectScalar(std::string_view const& query,
190 std::source_location location = std::source_location::current());
191
192 template <typename T>
193 requires(std::same_as<T, SqlVariant>)
194 [[nodiscard]] T ExecuteDirectScalar(std::string_view const& query,
195 std::source_location location = std::source_location::current());
196
197 /// Executes the given query, assuming that only one result row and column is affected, that one will be
198 /// returned.
199 template <typename T>
200 requires(!std::same_as<T, SqlVariant>)
201 [[nodiscard]] std::optional<T> ExecuteDirectScalar(SqlQueryObject auto const& query,
202 std::source_location location = std::source_location::current());
203
204 template <typename T>
205 requires(std::same_as<T, SqlVariant>)
206 [[nodiscard]] T ExecuteDirectScalar(SqlQueryObject auto const& query,
207 std::source_location location = std::source_location::current());
208
209 /// Retrieves the number of rows affected by the last query.
210 [[nodiscard]] LIGHTWEIGHT_API size_t NumRowsAffected() const;
211
212 /// Retrieves the number of columns affected by the last query.
213 [[nodiscard]] LIGHTWEIGHT_API size_t NumColumnsAffected() const;
214
215 /// Retrieves the last insert ID of the given table.
216 [[nodiscard]] LIGHTWEIGHT_API size_t LastInsertId(std::string_view tableName);
217
218 /// Fetches the next row of the result set.
219 ///
220 /// @note Automatically closes the cursor at the end of the result set.
221 ///
222 /// @retval true The next result row was successfully fetched
223 /// @retval false No result row was fetched, because the end of the result set was reached.
224 [[nodiscard]] LIGHTWEIGHT_API bool FetchRow();
225
226 [[nodiscard]] LIGHTWEIGHT_API std::expected<bool, SqlErrorInfo> TryFetchRow(
227 std::source_location location = std::source_location::current()) noexcept;
228
229 /// Closes the result cursor on queries that yield a result set, e.g. SELECT statements.
230 ///
231 /// Call this function when done with fetching the results before the end of the result set is reached.
232 void CloseCursor() noexcept;
233
234 /// Retrieves the result cursor for reading an SQL query result.
235 SqlResultCursor GetResultCursor() noexcept;
236
237 /// Retrieves the variant row cursor for reading an SQL query result of unknown column types and column count.
238 SqlVariantRowCursor GetVariantRowCursor() noexcept;
239
240 /// Retrieves the value of the column at the given index for the currently selected row.
241 ///
242 /// Returns true if the value is not NULL, false otherwise.
243 template <SqlGetColumnNativeType T>
244 [[nodiscard]] bool GetColumn(SQLUSMALLINT column, T* result) const;
245
246 /// Retrieves the value of the column at the given index for the currently selected row.
247 template <SqlGetColumnNativeType T>
248 [[nodiscard]] T GetColumn(SQLUSMALLINT column) const;
249
250 /// Retrieves the value of the column at the given index for the currently selected row.
251 ///
252 /// If the value is NULL, std::nullopt is returned.
253 template <SqlGetColumnNativeType T>
254 [[nodiscard]] std::optional<T> GetNullableColumn(SQLUSMALLINT column) const;
255
256 /// Retrieves the value of the column at the given index for the currently selected row.
257 ///
258 /// If the value is NULL, the given @p defaultValue is returned.
259 template <SqlGetColumnNativeType T>
260 [[nodiscard]] T GetColumnOr(SQLUSMALLINT column, T&& defaultValue) const;
261
262 private:
263 LIGHTWEIGHT_API void RequireSuccess(SQLRETURN error,
264 std::source_location sourceLocation = std::source_location::current()) const;
265 LIGHTWEIGHT_API void PlanPostExecuteCallback(std::function<void()>&& cb) override;
266 LIGHTWEIGHT_API void PlanPostProcessOutputColumn(std::function<void()>&& cb) override;
267 [[nodiscard]] LIGHTWEIGHT_API SqlServerType ServerType() const noexcept override;
268 [[nodiscard]] LIGHTWEIGHT_API std::string const& DriverName() const noexcept override;
269 LIGHTWEIGHT_API void ProcessPostExecuteCallbacks();
270
271 LIGHTWEIGHT_API void RequireIndicators();
272 LIGHTWEIGHT_API SQLLEN* GetIndicatorForColumn(SQLUSMALLINT column) noexcept;
273
274 // private data members
275 struct Data;
276 std::unique_ptr<Data, void (*)(Data*)> m_data; // The private data of the statement
277 SqlConnection* m_connection {}; // Pointer to the connection object
278 SQLHSTMT m_hStmt {}; // The native oDBC statement handle
279 std::string m_preparedQuery; // The last prepared query
280 std::optional<SQLSMALLINT> m_numColumns; // The number of columns in the result set, if known
281 SQLSMALLINT m_expectedParameterCount {}; // The number of parameters expected by the query
282};
283
284/// API for reading an SQL query result set.
285class [[nodiscard]] SqlResultCursor
286{
287 public:
288 explicit LIGHTWEIGHT_FORCE_INLINE SqlResultCursor(SqlStatement& stmt) noexcept:
289 m_stmt { &stmt }
290 {
291 }
292
293 SqlResultCursor() = delete;
294 SqlResultCursor(SqlResultCursor const&) = delete;
295 SqlResultCursor& operator=(SqlResultCursor const&) = delete;
296
297 constexpr SqlResultCursor(SqlResultCursor&& other) noexcept:
298 m_stmt { other.m_stmt }
299 {
300 other.m_stmt = nullptr;
301 }
302
303 constexpr SqlResultCursor& operator=(SqlResultCursor&& other) noexcept
304 {
305 if (this != &other)
306 {
307 m_stmt = other.m_stmt;
308 other.m_stmt = nullptr;
309 }
310 return *this;
311 }
312
313 LIGHTWEIGHT_FORCE_INLINE ~SqlResultCursor()
314 {
315 if (m_stmt)
316 {
317 m_stmt->CloseCursor();
318 m_stmt = nullptr;
319 }
320 }
321
322 /// Retrieves the number of rows affected by the last query.
323 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE size_t NumRowsAffected() const
324 {
325 return m_stmt->NumRowsAffected();
326 }
327
328 /// Retrieves the number of columns affected by the last query.
329 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE size_t NumColumnsAffected() const
330 {
331 return m_stmt->NumColumnsAffected();
332 }
333
334 /// Binds the given arguments to the prepared statement to store the fetched data to.
335 ///
336 /// The statement must be prepared before calling this function.
337 template <SqlOutputColumnBinder... Args>
338 LIGHTWEIGHT_FORCE_INLINE void BindOutputColumns(Args*... args)
339 {
340 m_stmt->BindOutputColumns(args...);
341 }
342
343 template <SqlOutputColumnBinder T>
344 LIGHTWEIGHT_FORCE_INLINE void BindOutputColumn(SQLUSMALLINT columnIndex, T* arg)
345 {
346 m_stmt->BindOutputColumn(columnIndex, arg);
347 }
348
349 /// Fetches the next row of the result set.
350 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE bool FetchRow()
351 {
352 return m_stmt->FetchRow();
353 }
354
355 /// Retrieves the value of the column at the given index for the currently selected row.
356 ///
357 /// Returns true if the value is not NULL, false otherwise.
358 template <SqlGetColumnNativeType T>
359 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE bool GetColumn(SQLUSMALLINT column, T* result) const
360 {
361 return m_stmt->GetColumn<T>(column, result);
362 }
363
364 /// Retrieves the value of the column at the given index for the currently selected row.
365 template <SqlGetColumnNativeType T>
366 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE T GetColumn(SQLUSMALLINT column) const
367 {
368 return m_stmt->GetColumn<T>(column);
369 }
370
371 /// Retrieves the value of the column at the given index for the currently selected row.
372 ///
373 /// If the value is NULL, std::nullopt is returned.
374 template <SqlGetColumnNativeType T>
375 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE std::optional<T> GetNullableColumn(SQLUSMALLINT column) const
376 {
377 return m_stmt->GetNullableColumn<T>(column);
378 }
379
380 /// Retrieves the value of the column at the given index for the currently selected row.
381 ///
382 /// If the value is NULL, the given @p defaultValue is returned.
383 template <SqlGetColumnNativeType T>
384 [[nodiscard]] T GetColumnOr(SQLUSMALLINT column, T&& defaultValue) const
385 {
386 return m_stmt->GetColumnOr(column, std::forward<T>(defaultValue));
387 }
388
389 private:
390 SqlStatement* m_stmt;
391};
392
393struct [[nodiscard]] SqlSentinelIterator
394{
395};
396
397class [[nodiscard]] SqlVariantRowIterator
398{
399 public:
400 explicit SqlVariantRowIterator(SqlSentinelIterator /*sentinel*/) noexcept:
401 _cursor { nullptr }
402 {
403 }
404
405 explicit SqlVariantRowIterator(SqlResultCursor& cursor) noexcept:
406 _numResultColumns { static_cast<SQLUSMALLINT>(cursor.NumColumnsAffected()) },
407 _cursor { &cursor }
408 {
409 _row.reserve(_numResultColumns);
410 ++(*this);
411 }
412
413 SqlVariantRow& operator*() noexcept
414 {
415 return _row;
416 }
417
418 SqlVariantRow const& operator*() const noexcept
419 {
420 return _row;
421 }
422
423 SqlVariantRowIterator& operator++() noexcept
424 {
425 _end = !_cursor->FetchRow();
426 if (!_end)
427 {
428 _row.clear();
429 for (auto const i: std::views::iota(SQLUSMALLINT(1), SQLUSMALLINT(_numResultColumns + 1)))
430 _row.emplace_back(_cursor->GetColumn<SqlVariant>(i));
431 }
432 return *this;
433 }
434
435 bool operator!=(SqlSentinelIterator /*sentinel*/) const noexcept
436 {
437 return !_end;
438 }
439
440 bool operator!=(SqlVariantRowIterator const& /*rhs*/) const noexcept
441 {
442 return !_end;
443 }
444
445 private:
446 bool _end = false;
447 SQLUSMALLINT _numResultColumns = 0;
448 SqlResultCursor* _cursor;
449 SqlVariantRow _row;
450};
451
452class [[nodiscard]] SqlVariantRowCursor
453{
454 public:
455 explicit SqlVariantRowCursor(SqlResultCursor&& cursor):
456 _resultCursor { std::move(cursor) }
457 {
458 }
459
460 SqlVariantRowIterator begin() noexcept
461 {
462 return SqlVariantRowIterator { _resultCursor };
463 }
464
465 static SqlSentinelIterator end() noexcept
466 {
467 return SqlSentinelIterator {};
468 }
469
470 private:
471 SqlResultCursor _resultCursor;
472};
473
474/// @brief SQL query result row iterator
475///
476/// Can be used to iterate over rows of the database and fetch them into a record type.
477/// @tparam T The record type to fetch the rows into.
478/// @code
479///
480/// struct MyRecord
481/// {
482/// Field<SqlGuid, PrimaryKey::AutoAssign> field1;
483/// Field<int> field2;
484/// Field<double> field3;
485/// };
486///
487/// for (auto const& row : SqlRowIterator<MyRecord>(stmt))
488/// {
489/// // row is of type MyRecord
490/// // row.field1, row.field2, row.field3 are accessible
491/// }
492/// @endcode
493template <typename T>
495{
496 public:
497 explicit SqlRowIterator(SqlConnection& conn):
498 _connection { &conn }
499 {
500 }
501
502 class iterator
503 {
504 public:
505 using difference_type = bool;
506 using value_type = T;
507
508 iterator& operator++()
509 {
510 _is_end = !_stmt->FetchRow();
511 if (!_is_end)
512 {
513 }
514 else
515 {
516 _stmt->CloseCursor();
517 }
518 return *this;
519 }
520
521 LIGHTWEIGHT_FORCE_INLINE value_type operator*() noexcept
522 {
523 auto res = T {};
524
525 Reflection::EnumerateMembers(res, [this]<size_t I>(auto&& value) {
526 auto tmp = _stmt->GetColumn<typename Reflection::MemberTypeOf<I, value_type>::ValueType>(I + 1);
527 value = tmp;
528 });
529
530 return res;
531 }
532
533 LIGHTWEIGHT_FORCE_INLINE constexpr bool operator!=(iterator const& other) const noexcept
534 {
535 return _is_end != other._is_end;
536 }
537
538 constexpr iterator(std::default_sentinel_t /*sentinel*/) noexcept:
539 _is_end { true }
540 {
541 }
542
543 explicit iterator(SqlConnection& conn):
544 _stmt { std::make_unique<SqlStatement>(conn) }
545 {
546 }
547
548 LIGHTWEIGHT_FORCE_INLINE SqlStatement& Statement() noexcept
549 {
550 return *_stmt;
551 }
552
553 private:
554 bool _is_end = false;
555 std::unique_ptr<SqlStatement> _stmt;
556 };
557
558 iterator begin()
559 {
560 auto it = iterator { *_connection };
561 auto& stmt = it.Statement();
562 stmt.Prepare(it.Statement().Query(RecordTableName<T>).Select().template Fields<T>().All());
563 stmt.Execute();
564 ++it;
565 return it;
566 }
567
568 iterator end() noexcept
569 {
570 return iterator { std::default_sentinel };
571 }
572
573 private:
574 SqlConnection* _connection;
575};
576
577// {{{ inline implementation
578inline LIGHTWEIGHT_FORCE_INLINE bool SqlStatement::IsAlive() const noexcept
579{
580 return m_connection && m_connection->IsAlive() && m_hStmt != nullptr;
581}
582
583inline LIGHTWEIGHT_FORCE_INLINE bool SqlStatement::IsPrepared() const noexcept
584{
585 return !m_preparedQuery.empty();
586}
587
588inline LIGHTWEIGHT_FORCE_INLINE SqlConnection& SqlStatement::Connection() noexcept
589{
590 return *m_connection;
591}
592
593inline LIGHTWEIGHT_FORCE_INLINE SqlConnection const& SqlStatement::Connection() const noexcept
594{
595 return *m_connection;
596}
597
598inline LIGHTWEIGHT_FORCE_INLINE SqlErrorInfo SqlStatement::LastError() const
599{
600 return SqlErrorInfo::FromStatementHandle(m_hStmt);
601}
602
603inline LIGHTWEIGHT_FORCE_INLINE SQLHSTMT SqlStatement::NativeHandle() const noexcept
604{
605 return m_hStmt;
606}
607
608inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::Prepare(SqlQueryObject auto const& queryObject) &
609{
610 Prepare(queryObject.ToSql());
611}
612
613inline LIGHTWEIGHT_FORCE_INLINE SqlStatement SqlStatement::Prepare(SqlQueryObject auto const& queryObject) &&
614{
615 return Prepare(queryObject.ToSql());
616}
617
618inline LIGHTWEIGHT_FORCE_INLINE std::string const& SqlStatement::PreparedQuery() const noexcept
619{
620 return m_preparedQuery;
621}
622
623template <SqlOutputColumnBinder... Args>
624inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::BindOutputColumns(Args*... args)
625{
626 RequireIndicators();
627
628 SQLUSMALLINT i = 0;
629 ((++i, RequireSuccess(SqlDataBinder<Args>::OutputColumn(m_hStmt, i, args, GetIndicatorForColumn(i), *this))), ...);
630}
631
632template <typename... Records>
633 requires(((std::is_class_v<Records> && std::is_aggregate_v<Records>) && ...))
635{
636 RequireIndicators();
637
638 SQLUSMALLINT i = 0;
639 ((Reflection::EnumerateMembers(*records,
640 [this, &i]<size_t I, typename FieldType>(FieldType& value) {
641 ++i;
642 RequireSuccess(SqlDataBinder<FieldType>::OutputColumn(
643 m_hStmt, i, &value, GetIndicatorForColumn(i), *this));
644 })),
645 ...);
646}
647
648template <SqlOutputColumnBinder T>
649inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::BindOutputColumn(SQLUSMALLINT columnIndex, T* arg)
650{
651 RequireIndicators();
652
653 RequireSuccess(SqlDataBinder<T>::OutputColumn(m_hStmt, columnIndex, arg, GetIndicatorForColumn(columnIndex), *this));
654}
655
656template <SqlInputParameterBinder Arg>
657inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::BindInputParameter(SQLSMALLINT columnIndex, Arg const& arg)
658{
659 // tell Execute() that we don't know the expected count
660 m_expectedParameterCount = (std::numeric_limits<decltype(m_expectedParameterCount)>::max)();
661 RequireSuccess(SqlDataBinder<Arg>::InputParameter(m_hStmt, columnIndex, arg, *this));
662}
663
664template <SqlInputParameterBinder Arg, typename ColumnName>
665inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::BindInputParameter(SQLSMALLINT columnIndex,
666 Arg const& arg,
667 ColumnName&& columnNameHint)
668{
669 SqlLogger::GetLogger().OnBindInputParameter(std::forward<ColumnName>(columnNameHint), arg);
670 BindInputParameter(columnIndex, arg);
671}
672
673template <SqlInputParameterBinder... Args>
674void SqlStatement::Execute(Args const&... args)
675{
676 // Each input parameter must have an address,
677 // such that we can call SQLBindParameter() without needing to copy it.
678 // The memory region behind the input parameter must exist until the SQLExecute() call.
679
680 SqlLogger::GetLogger().OnExecute(m_preparedQuery);
681
682 if (!(m_expectedParameterCount == (std::numeric_limits<decltype(m_expectedParameterCount)>::max)()
683 && sizeof...(args) == 0)
684 && !(m_expectedParameterCount == sizeof...(args)))
685 throw std::invalid_argument { "Invalid argument count" };
686
687 SQLUSMALLINT i = 0;
688 ((++i,
690 RequireSuccess(SqlDataBinder<Args>::InputParameter(m_hStmt, i, args, *this))),
691 ...);
692
693 auto const result = SQLExecute(m_hStmt);
694
695 if (result != SQL_NO_DATA && result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO)
696 throw SqlException(SqlErrorInfo::FromStatementHandle(m_hStmt), std::source_location::current());
697
698 ProcessPostExecuteCallbacks();
699}
700
701// clang-format off
702template <typename T>
703concept SqlNativeContiguousValueConcept =
704 std::same_as<T, bool>
705 || std::same_as<T, char>
706 || std::same_as<T, unsigned char>
707 || std::same_as<T, wchar_t>
708 || std::same_as<T, std::int16_t>
709 || std::same_as<T, std::uint16_t>
710 || std::same_as<T, std::int32_t>
711 || std::same_as<T, std::uint32_t>
712 || std::same_as<T, std::int64_t>
713 || std::same_as<T, std::uint64_t>
714 || std::same_as<T, float>
715 || std::same_as<T, double>
716 || std::same_as<T, SqlDate>
717 || std::same_as<T, SqlTime>
718 || std::same_as<T, SqlDateTime>
719 || std::same_as<T, SqlFixedString<T::Capacity, typename T::value_type, T::PostRetrieveOperation>>;
720
721template <typename FirstColumnBatch, typename... MoreColumnBatches>
722concept SqlNativeBatchable =
723 std::ranges::contiguous_range<FirstColumnBatch>
724 && (std::ranges::contiguous_range<MoreColumnBatches> && ...)
725 && SqlNativeContiguousValueConcept<std::ranges::range_value_t<FirstColumnBatch>>
726 && (SqlNativeContiguousValueConcept<std::ranges::range_value_t<MoreColumnBatches>> && ...);
727
728// clang-format on
729
730template <SqlInputParameterBatchBinder FirstColumnBatch, std::ranges::contiguous_range... MoreColumnBatches>
731void SqlStatement::ExecuteBatchNative(FirstColumnBatch const& firstColumnBatch,
732 MoreColumnBatches const&... moreColumnBatches)
733{
734 static_assert(SqlNativeBatchable<FirstColumnBatch, MoreColumnBatches...>,
735 "Must be a supported native contiguous element type.");
736
737 if (m_expectedParameterCount != 1 + sizeof...(moreColumnBatches))
738 throw std::invalid_argument { "Invalid number of columns" };
739
740 auto const rowCount = std::ranges::size(firstColumnBatch);
741 if (!((std::size(moreColumnBatches) == rowCount) && ...))
742 throw std::invalid_argument { "Uneven number of rows" };
743
744 size_t rowStart = 0;
745
746 // clang-format off
747 // NOLINTNEXTLINE(performance-no-int-to-ptr)
748 RequireSuccess(SQLSetStmtAttr(m_hStmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) rowCount, 0));
749 RequireSuccess(SQLSetStmtAttr(m_hStmt, SQL_ATTR_PARAM_BIND_OFFSET_PTR, &rowStart, 0));
750 RequireSuccess(SQLSetStmtAttr(m_hStmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0));
751 RequireSuccess(SQLSetStmtAttr(m_hStmt, SQL_ATTR_PARAM_OPERATION_PTR, SQL_PARAM_PROCEED, 0));
752 RequireSuccess(SqlDataBinder<std::remove_cvref_t<decltype(*std::ranges::data(firstColumnBatch))>>::
753 InputParameter(m_hStmt, 1, *std::ranges::data(firstColumnBatch), *this));
754 SQLUSMALLINT column = 1;
755 (RequireSuccess(SqlDataBinder<std::remove_cvref_t<decltype(*std::ranges::data(moreColumnBatches))>>::
756 InputParameter(m_hStmt, ++column, *std::ranges::data(moreColumnBatches), *this)), ...);
757 RequireSuccess(SQLExecute(m_hStmt));
758 ProcessPostExecuteCallbacks();
759 // clang-format on
760}
761
762template <SqlInputParameterBatchBinder FirstColumnBatch, std::ranges::range... MoreColumnBatches>
763inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::ExecuteBatch(FirstColumnBatch const& firstColumnBatch,
764 MoreColumnBatches const&... moreColumnBatches)
765{
766 // If the input ranges are contiguous and their element types are contiguous and supported as well,
767 // we can use the native batch execution.
768 if constexpr (SqlNativeBatchable<FirstColumnBatch, MoreColumnBatches...>)
769 ExecuteBatchNative(firstColumnBatch, moreColumnBatches...);
770 else
771 ExecuteBatchSoft(firstColumnBatch, moreColumnBatches...);
772}
773
774template <SqlInputParameterBatchBinder FirstColumnBatch, std::ranges::range... MoreColumnBatches>
775void SqlStatement::ExecuteBatchSoft(FirstColumnBatch const& firstColumnBatch, MoreColumnBatches const&... moreColumnBatches)
776{
777 if (m_expectedParameterCount != 1 + sizeof...(moreColumnBatches))
778 throw std::invalid_argument { "Invalid number of columns" };
779
780 auto const rowCount = std::ranges::size(firstColumnBatch);
781 if (!((std::size(moreColumnBatches) == rowCount) && ...))
782 throw std::invalid_argument { "Uneven number of rows" };
783
784 for (auto const rowIndex: std::views::iota(size_t { 0 }, rowCount))
785 {
786 std::apply(
787 [&]<SqlInputParameterBinder... ColumnValues>(ColumnValues const&... columnsInRow) {
788 SQLUSMALLINT column = 0;
789 ((++column, SqlDataBinder<ColumnValues>::InputParameter(m_hStmt, column, columnsInRow, *this)), ...);
790 RequireSuccess(SQLExecute(m_hStmt));
791 ProcessPostExecuteCallbacks();
792 },
793 std::make_tuple(std::ref(*std::ranges::next(std::ranges::begin(firstColumnBatch), rowIndex)),
794 std::ref(*std::ranges::next(std::ranges::begin(moreColumnBatches), rowIndex))...));
795 }
796}
797
798template <SqlGetColumnNativeType T>
799inline bool SqlStatement::GetColumn(SQLUSMALLINT column, T* result) const
800{
801 SQLLEN indicator {}; // TODO: Handle NULL values if we find out that we need them for our use-cases.
802 RequireSuccess(SqlDataBinder<T>::GetColumn(m_hStmt, column, result, &indicator, *this));
803 return indicator != SQL_NULL_DATA;
804}
805
806namespace detail
807{
808
809 template <typename T>
810 concept SqlNullableType = (std::same_as<T, SqlVariant> || IsSpecializationOf<std::optional, T>);
811
812} // end namespace detail
813
814template <SqlGetColumnNativeType T>
815inline T SqlStatement::GetColumn(SQLUSMALLINT column) const
816{
817 T result {};
818 SQLLEN indicator {};
819 RequireSuccess(SqlDataBinder<T>::GetColumn(m_hStmt, column, &result, &indicator, *this));
820 if constexpr (!detail::SqlNullableType<T>)
821 if (indicator == SQL_NULL_DATA)
822 throw std::runtime_error { "Column value is NULL" };
823 return result;
824}
825
826template <SqlGetColumnNativeType T>
827inline std::optional<T> SqlStatement::GetNullableColumn(SQLUSMALLINT column) const
828{
829 T result {};
830 SQLLEN indicator {}; // TODO: Handle NULL values if we find out that we need them for our use-cases.
831 RequireSuccess(SqlDataBinder<T>::GetColumn(m_hStmt, column, &result, &indicator, *this));
832 if (indicator == SQL_NULL_DATA)
833 return std::nullopt;
834 return { std::move(result) };
835}
836
837template <SqlGetColumnNativeType T>
838T SqlStatement::GetColumnOr(SQLUSMALLINT column, T&& defaultValue) const
839{
840 return GetNullableColumn<T>(column).value_or(std::forward<T>(defaultValue));
841}
842
843inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::ExecuteDirect(SqlQueryObject auto const& query,
844 std::source_location location)
845{
846 return ExecuteDirect(query.ToSql(), location);
847}
848
849template <typename Callable>
850 requires std::invocable<Callable, SqlMigrationQueryBuilder&>
851void SqlStatement::MigrateDirect(Callable const& callable, std::source_location location)
852{
853 auto migration = SqlMigrationQueryBuilder { Connection().QueryFormatter() };
854 callable(migration);
855 auto const queries = migration.GetPlan().ToSql();
856 for (auto const& query: queries)
857 {
858 ExecuteDirect(query, location);
859 CloseCursor();
860 }
861}
862
863template <typename T>
864 requires(!std::same_as<T, SqlVariant>)
865inline std::optional<T> SqlStatement::ExecuteDirectScalar(std::string_view const& query, std::source_location location)
866{
867 auto const _ = detail::Finally([this] { CloseCursor(); });
868 ExecuteDirect(query, location);
869 RequireSuccess(FetchRow());
870 return GetNullableColumn<T>(1);
871}
872
873template <typename T>
874 requires(std::same_as<T, SqlVariant>)
875inline T SqlStatement::ExecuteDirectScalar(std::string_view const& query, std::source_location location)
876{
877 auto const _ = detail::Finally([this] { CloseCursor(); });
878 ExecuteDirect(query, location);
879 RequireSuccess(FetchRow());
880 if (auto result = GetNullableColumn<T>(1); result.has_value())
881 return *result;
882 return SqlVariant { SqlNullValue };
883}
884
885template <typename T>
886 requires(!std::same_as<T, SqlVariant>)
887inline std::optional<T> SqlStatement::ExecuteDirectScalar(SqlQueryObject auto const& query, std::source_location location)
888{
889 return ExecuteDirectScalar<T>(query.ToSql(), location);
890}
891
892template <typename T>
893 requires(std::same_as<T, SqlVariant>)
894inline T SqlStatement::ExecuteDirectScalar(SqlQueryObject auto const& query, std::source_location location)
895{
896 return ExecuteDirectScalar<T>(query.ToSql(), location);
897}
898
899inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::CloseCursor() noexcept
900{
901 // SQLCloseCursor(m_hStmt);
902 SQLFreeStmt(m_hStmt, SQL_CLOSE);
904}
905
906inline LIGHTWEIGHT_FORCE_INLINE SqlResultCursor SqlStatement::GetResultCursor() noexcept
907{
908 return SqlResultCursor { *this };
909}
910
911inline LIGHTWEIGHT_FORCE_INLINE SqlVariantRowCursor SqlStatement::GetVariantRowCursor() noexcept
912{
913 return SqlVariantRowCursor { SqlResultCursor { *this } };
914}
915
916// }}}
917
918} // namespace Lightweight
Represents a connection to a SQL database.
LIGHTWEIGHT_API bool IsAlive() const noexcept
Tests if the connection is still active.
SqlQueryFormatter const & QueryFormatter() const noexcept
Retrieves a query formatter suitable for the SQL server being connected.
virtual void OnExecute(std::string_view const &query)=0
Invoked when a prepared query is executed.
static LIGHTWEIGHT_API SqlLogger & GetLogger()
Retrieves the currently configured logger.
virtual void OnFetchEnd()=0
Invoked when fetching is done.
void OnBindInputParameter(std::string_view const &name, T &&value)
Invoked when an input parameter is bound.
Definition SqlLogger.hpp:87
Query builder for building SQL migration queries.
Definition Migrate.hpp:185
API Entry point for building SQL queries.
Definition SqlQuery.hpp:29
API for reading an SQL query result set.
LIGHTWEIGHT_FORCE_INLINE bool GetColumn(SQLUSMALLINT column, T *result) const
LIGHTWEIGHT_FORCE_INLINE void BindOutputColumns(Args *... args)
LIGHTWEIGHT_FORCE_INLINE std::optional< T > GetNullableColumn(SQLUSMALLINT column) const
LIGHTWEIGHT_FORCE_INLINE T GetColumn(SQLUSMALLINT column) const
Retrieves the value of the column at the given index for the currently selected row.
T GetColumnOr(SQLUSMALLINT column, T &&defaultValue) const
LIGHTWEIGHT_FORCE_INLINE size_t NumColumnsAffected() const
Retrieves the number of columns affected by the last query.
LIGHTWEIGHT_FORCE_INLINE size_t NumRowsAffected() const
Retrieves the number of rows affected by the last query.
LIGHTWEIGHT_FORCE_INLINE bool FetchRow()
Fetches the next row of the result set.
SQL query result row iterator.
High level API for (prepared) raw SQL statements.
LIGHTWEIGHT_API void Prepare(std::string_view query) &
LIGHTWEIGHT_API SQLHSTMT NativeHandle() const noexcept
Retrieves the native handle of the statement.
LIGHTWEIGHT_API SqlQueryBuilder QueryAs(std::string_view const &table, std::string_view const &tableAlias) const
Creates a new query builder for the given table with an alias, compatible with the SQL server being c...
void MigrateDirect(Callable const &callable, std::source_location location=std::source_location::current())
Executes an SQL migration query, as created b the callback.
LIGHTWEIGHT_API SqlConnection & Connection() noexcept
Retrieves the connection associated with this statement.
void ExecuteBatch(FirstColumnBatch const &firstColumnBatch, MoreColumnBatches const &... moreColumnBatches)
void ExecuteBatchSoft(FirstColumnBatch const &firstColumnBatch, MoreColumnBatches const &... moreColumnBatches)
void Execute(Args const &... args)
Binds the given arguments to the prepared statement and executes it.
void ExecuteBatchNative(FirstColumnBatch const &firstColumnBatch, MoreColumnBatches const &... moreColumnBatches)
LIGHTWEIGHT_API SqlErrorInfo LastError() const
Retrieves the last error information with respect to this SQL statement handle.
SqlVariantRowCursor GetVariantRowCursor() noexcept
Retrieves the variant row cursor for reading an SQL query result of unknown column types and column c...
SqlResultCursor GetResultCursor() noexcept
Retrieves the result cursor for reading an SQL query result.
bool GetColumn(SQLUSMALLINT column, T *result) const
LIGHTWEIGHT_API SqlStatement()
Construct a new SqlStatement object, using a new connection, and connect to the default database.
void BindOutputColumnsToRecord(Records *... records)
T GetColumnOr(SQLUSMALLINT column, T &&defaultValue) const
LIGHTWEIGHT_API SqlStatement(std::nullopt_t)
Construct a new empty SqlStatement object. No SqlConnection is associated with this statement.
std::optional< T > ExecuteDirectScalar(std::string_view const &query, std::source_location location=std::source_location::current())
LIGHTWEIGHT_API void ExecuteDirect(std::string_view const &query, std::source_location location=std::source_location::current())
Executes the given query directly.
std::optional< T > GetNullableColumn(SQLUSMALLINT column) const
LIGHTWEIGHT_API SqlStatement(SqlConnection &relatedConnection)
Construct a new SqlStatement object, using the given connection.
void BindOutputColumns(Args *... args)
Represents an SQL query object, that provides a ToSql() method.
constexpr auto SqlNullValue
Represents an ODBC SQL error.
Definition SqlError.hpp:33
static SqlErrorInfo FromStatementHandle(SQLHSTMT hStmt)
Constructs an ODBC error info object from the given ODBC statement handle.
Definition SqlError.hpp:45
Represents a value that can be any of the supported SQL data types.