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