Lightweight 0.1.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&&) noexcept;
54 LIGHTWEIGHT_API SqlStatement& operator=(SqlStatement&&) 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 SQLSMALLINT m_expectedParameterCount {}; // The number of parameters expected by the query
272};
273
274/// API for reading an SQL query result set.
275class [[nodiscard]] SqlResultCursor
276{
277 public:
278 explicit LIGHTWEIGHT_FORCE_INLINE SqlResultCursor(SqlStatement& stmt) noexcept:
279 m_stmt { &stmt }
280 {
281 }
282
283 SqlResultCursor() = delete;
284 SqlResultCursor(SqlResultCursor const&) = delete;
285 SqlResultCursor& operator=(SqlResultCursor const&) = delete;
286
287 constexpr SqlResultCursor(SqlResultCursor&& other) noexcept:
288 m_stmt { other.m_stmt }
289 {
290 other.m_stmt = nullptr;
291 }
292
293 constexpr SqlResultCursor& operator=(SqlResultCursor&& other) noexcept
294 {
295 if (this != &other)
296 {
297 m_stmt = other.m_stmt;
298 other.m_stmt = nullptr;
299 }
300 return *this;
301 }
302
303 LIGHTWEIGHT_FORCE_INLINE ~SqlResultCursor()
304 {
305 if (m_stmt)
306 SQLCloseCursor(m_stmt->NativeHandle());
307 }
308
309 /// Retrieves the number of rows affected by the last query.
310 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE size_t NumRowsAffected() const
311 {
312 return m_stmt->NumRowsAffected();
313 }
314
315 /// Retrieves the number of columns affected by the last query.
316 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE size_t NumColumnsAffected() const
317 {
318 return m_stmt->NumColumnsAffected();
319 }
320
321 /// Binds the given arguments to the prepared statement to store the fetched data to.
322 ///
323 /// The statement must be prepared before calling this function.
324 template <SqlOutputColumnBinder... Args>
325 LIGHTWEIGHT_FORCE_INLINE void BindOutputColumns(Args*... args)
326 {
327 m_stmt->BindOutputColumns(args...);
328 }
329
330 template <SqlOutputColumnBinder T>
331 LIGHTWEIGHT_FORCE_INLINE void BindOutputColumn(SQLUSMALLINT columnIndex, T* arg)
332 {
333 m_stmt->BindOutputColumn(columnIndex, arg);
334 }
335
336 /// Fetches the next row of the result set.
337 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE bool FetchRow()
338 {
339 return m_stmt->FetchRow();
340 }
341
342 /// Retrieves the value of the column at the given index for the currently selected row.
343 ///
344 /// Returns true if the value is not NULL, false otherwise.
345 template <SqlGetColumnNativeType T>
346 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE bool GetColumn(SQLUSMALLINT column, T* result) const
347 {
348 return m_stmt->GetColumn<T>(column, result);
349 }
350
351 /// Retrieves the value of the column at the given index for the currently selected row.
352 template <SqlGetColumnNativeType T>
353 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE T GetColumn(SQLUSMALLINT column) const
354 {
355 return m_stmt->GetColumn<T>(column);
356 }
357
358 /// Retrieves the value of the column at the given index for the currently selected row.
359 ///
360 /// If the value is NULL, std::nullopt is returned.
361 template <SqlGetColumnNativeType T>
362 [[nodiscard]] LIGHTWEIGHT_FORCE_INLINE std::optional<T> GetNullableColumn(SQLUSMALLINT column) const
363 {
364 return m_stmt->GetNullableColumn<T>(column);
365 }
366
367 private:
368 SqlStatement* m_stmt;
369};
370
371struct [[nodiscard]] SqlSentinelIterator
372{
373};
374
375class [[nodiscard]] SqlVariantRowIterator
376{
377 public:
378 explicit SqlVariantRowIterator(SqlSentinelIterator /*sentinel*/) noexcept:
379 _cursor { nullptr }
380 {
381 }
382
383 explicit SqlVariantRowIterator(SqlResultCursor& cursor) noexcept:
384 _numResultColumns { static_cast<SQLUSMALLINT>(cursor.NumColumnsAffected()) },
385 _cursor { &cursor }
386 {
387 _row.reserve(_numResultColumns);
388 ++(*this);
389 }
390
391 SqlVariantRow& operator*() noexcept
392 {
393 return _row;
394 }
395
396 SqlVariantRow const& operator*() const noexcept
397 {
398 return _row;
399 }
400
401 SqlVariantRowIterator& operator++() noexcept
402 {
403 _end = !_cursor->FetchRow();
404 if (!_end)
405 {
406 _row.clear();
407 for (auto const i: std::views::iota(SQLUSMALLINT(1), SQLUSMALLINT(_numResultColumns + 1)))
408 _row.emplace_back(_cursor->GetColumn<SqlVariant>(i));
409 }
410 return *this;
411 }
412
413 bool operator!=(SqlSentinelIterator /*sentinel*/) const noexcept
414 {
415 return !_end;
416 }
417
418 bool operator!=(SqlVariantRowIterator const& /*rhs*/) const noexcept
419 {
420 return !_end;
421 }
422
423 private:
424 bool _end = false;
425 SQLUSMALLINT _numResultColumns = 0;
426 SqlResultCursor* _cursor;
427 SqlVariantRow _row;
428};
429
430class [[nodiscard]] SqlVariantRowCursor
431{
432 public:
433 explicit SqlVariantRowCursor(SqlResultCursor&& cursor):
434 _resultCursor { std::move(cursor) }
435 {
436 }
437
438 SqlVariantRowIterator begin() noexcept
439 {
440 return SqlVariantRowIterator { _resultCursor };
441 }
442
443 static SqlSentinelIterator end() noexcept
444 {
445 return SqlSentinelIterator {};
446 }
447
448 private:
449 SqlResultCursor _resultCursor;
450};
451
452/// @brief SQL query result row iterator
453///
454/// Can be used to iterate over rows of the database and fetch them into a record type.
455/// @tparam T The record type to fetch the rows into.
456/// @code
457///
458/// struct MyRecord
459/// {
460/// Field<SqlGuid, PrimaryKey::AutoAssign> field1;
461/// Field<int> field2;
462/// Field<double> field3;
463/// };
464///
465/// for (auto const& row : SqlRowIterator<MyRecord>(stmt))
466/// {
467/// // row is of type MyRecord
468/// // row.field1, row.field2, row.field3 are accessible
469/// }
470/// @endcode
471template <typename T>
473{
474 public:
475 explicit SqlRowIterator(SqlConnection& conn):
476 _connection { &conn }
477 {
478 }
479
480 class iterator
481 {
482 public:
483 using difference_type = bool;
484 using value_type = T;
485
486 iterator& operator++()
487 {
488 _is_end = !_stmt->FetchRow();
489 if (!_is_end)
490 {
491 }
492 else
493 {
494 _stmt->CloseCursor();
495 }
496 return *this;
497 }
498
499 LIGHTWEIGHT_FORCE_INLINE value_type operator*() noexcept
500 {
501 auto res = T {};
502
503 Reflection::EnumerateMembers(res, [this]<size_t I>(auto&& value) {
504 auto tmp = _stmt->GetColumn<typename Reflection::MemberTypeOf<I, value_type>::ValueType>(I + 1);
505 value = tmp;
506 });
507
508 return res;
509 }
510
511 LIGHTWEIGHT_FORCE_INLINE constexpr bool operator!=(iterator const& other) const noexcept
512 {
513 return _is_end != other._is_end;
514 }
515
516 constexpr iterator(std::default_sentinel_t /*sentinel*/) noexcept:
517 _is_end { true }
518 {
519 }
520
521 explicit iterator(SqlConnection& conn):
522 _stmt { std::make_unique<SqlStatement>(conn) }
523 {
524 }
525
526 LIGHTWEIGHT_FORCE_INLINE SqlStatement& Statement() noexcept
527 {
528 return *_stmt;
529 }
530
531 private:
532 bool _is_end = false;
533 std::unique_ptr<SqlStatement> _stmt;
534 };
535
536 iterator begin()
537 {
538 auto it = iterator { *_connection };
539 auto& stmt = it.Statement();
540 stmt.Prepare(it.Statement().Query(RecordTableName<T>).Select().template Fields<T>().All());
541 stmt.Execute();
542 ++it;
543 return it;
544 }
545
546 iterator end() noexcept
547 {
548 return iterator { std::default_sentinel };
549 }
550
551 private:
552 SqlConnection* _connection;
553};
554
555// {{{ inline implementation
556inline LIGHTWEIGHT_FORCE_INLINE bool SqlStatement::IsAlive() const noexcept
557{
558 return m_connection && m_connection->IsAlive() && m_hStmt != nullptr;
559}
560
561inline LIGHTWEIGHT_FORCE_INLINE bool SqlStatement::IsPrepared() const noexcept
562{
563 return !m_preparedQuery.empty();
564}
565
566inline LIGHTWEIGHT_FORCE_INLINE SqlConnection& SqlStatement::Connection() noexcept
567{
568 return *m_connection;
569}
570
571inline LIGHTWEIGHT_FORCE_INLINE SqlConnection const& SqlStatement::Connection() const noexcept
572{
573 return *m_connection;
574}
575
576inline LIGHTWEIGHT_FORCE_INLINE SqlErrorInfo SqlStatement::LastError() const
577{
578 return SqlErrorInfo::fromStatementHandle(m_hStmt);
579}
580
581inline LIGHTWEIGHT_FORCE_INLINE SQLHSTMT SqlStatement::NativeHandle() const noexcept
582{
583 return m_hStmt;
584}
585
586inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::Prepare(SqlQueryObject auto const& queryObject) &
587{
588 Prepare(queryObject.ToSql());
589}
590
591inline LIGHTWEIGHT_FORCE_INLINE SqlStatement SqlStatement::Prepare(SqlQueryObject auto const& queryObject) &&
592{
593 return Prepare(queryObject.ToSql());
594}
595
596inline LIGHTWEIGHT_FORCE_INLINE std::string const& SqlStatement::PreparedQuery() const noexcept
597{
598 return m_preparedQuery;
599}
600
601template <SqlOutputColumnBinder... Args>
602inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::BindOutputColumns(Args*... args)
603{
604 RequireIndicators();
605
606 SQLUSMALLINT i = 0;
607 ((++i, RequireSuccess(SqlDataBinder<Args>::OutputColumn(m_hStmt, i, args, GetIndicatorForColumn(i), *this))), ...);
608}
609
610template <typename... Records>
611 requires(((std::is_class_v<Records> && std::is_aggregate_v<Records>) && ...))
613{
614 RequireIndicators();
615
616 SQLUSMALLINT i = 0;
617 ((Reflection::EnumerateMembers(*records,
618 [this, &i]<size_t I, typename FieldType>(FieldType& value) {
619 ++i;
620 RequireSuccess(SqlDataBinder<FieldType>::OutputColumn(
621 m_hStmt, i, &value, GetIndicatorForColumn(i), *this));
622 })),
623 ...);
624}
625
626template <SqlOutputColumnBinder T>
627inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::BindOutputColumn(SQLUSMALLINT columnIndex, T* arg)
628{
629 RequireIndicators();
630
631 RequireSuccess(SqlDataBinder<T>::OutputColumn(m_hStmt, columnIndex, arg, GetIndicatorForColumn(columnIndex), *this));
632}
633
634template <SqlInputParameterBinder Arg>
635inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::BindInputParameter(SQLSMALLINT columnIndex, Arg const& arg)
636{
637 // tell Execute() that we don't know the expected count
638 m_expectedParameterCount = (std::numeric_limits<decltype(m_expectedParameterCount)>::max)();
639 RequireSuccess(SqlDataBinder<Arg>::InputParameter(m_hStmt, columnIndex, arg, *this));
640}
641
642template <SqlInputParameterBinder Arg, typename ColumnName>
643inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::BindInputParameter(SQLSMALLINT columnIndex,
644 Arg const& arg,
645 ColumnName&& columnNameHint)
646{
647 SqlLogger::GetLogger().OnBindInputParameter(std::forward<ColumnName>(columnNameHint), arg);
648 BindInputParameter(columnIndex, arg);
649}
650
651template <SqlInputParameterBinder... Args>
652void SqlStatement::Execute(Args const&... args)
653{
654 // Each input parameter must have an address,
655 // such that we can call SQLBindParameter() without needing to copy it.
656 // The memory region behind the input parameter must exist until the SQLExecute() call.
657
658 SqlLogger::GetLogger().OnExecute(m_preparedQuery);
659
660 if (!(m_expectedParameterCount == (std::numeric_limits<decltype(m_expectedParameterCount)>::max)()
661 && sizeof...(args) == 0)
662 && !(m_expectedParameterCount == sizeof...(args)))
663 throw std::invalid_argument { "Invalid argument count" };
664
665 SQLUSMALLINT i = 0;
666 ((++i,
668 RequireSuccess(SqlDataBinder<Args>::InputParameter(m_hStmt, i, args, *this))),
669 ...);
670
671 auto const result = SQLExecute(m_hStmt);
672
673 if (result != SQL_NO_DATA && result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO)
674 throw SqlException(SqlErrorInfo::fromStatementHandle(m_hStmt), std::source_location::current());
675
676 ProcessPostExecuteCallbacks();
677}
678
679// clang-format off
680template <typename T>
681concept SqlNativeContiguousValueConcept =
682 std::same_as<T, bool>
683 || std::same_as<T, char>
684 || std::same_as<T, unsigned char>
685 || std::same_as<T, wchar_t>
686 || std::same_as<T, std::int16_t>
687 || std::same_as<T, std::uint16_t>
688 || std::same_as<T, std::int32_t>
689 || std::same_as<T, std::uint32_t>
690 || std::same_as<T, std::int64_t>
691 || std::same_as<T, std::uint64_t>
692 || std::same_as<T, float>
693 || std::same_as<T, double>
694 || std::same_as<T, SqlDate>
695 || std::same_as<T, SqlTime>
696 || std::same_as<T, SqlDateTime>
697 || std::same_as<T, SqlFixedString<T::Capacity, typename T::value_type, T::PostRetrieveOperation>>;
698
699template <typename FirstColumnBatch, typename... MoreColumnBatches>
700concept SqlNativeBatchable =
701 std::ranges::contiguous_range<FirstColumnBatch>
702 && (std::ranges::contiguous_range<MoreColumnBatches> && ...)
703 && SqlNativeContiguousValueConcept<std::ranges::range_value_t<FirstColumnBatch>>
704 && (SqlNativeContiguousValueConcept<std::ranges::range_value_t<MoreColumnBatches>> && ...);
705
706// clang-format on
707
708template <SqlInputParameterBatchBinder FirstColumnBatch, std::ranges::contiguous_range... MoreColumnBatches>
709void SqlStatement::ExecuteBatchNative(FirstColumnBatch const& firstColumnBatch,
710 MoreColumnBatches const&... moreColumnBatches)
711{
712 static_assert(SqlNativeBatchable<FirstColumnBatch, MoreColumnBatches...>,
713 "Must be a supported native contiguous element type.");
714
715 if (m_expectedParameterCount != 1 + sizeof...(moreColumnBatches))
716 throw std::invalid_argument { "Invalid number of columns" };
717
718 auto const rowCount = std::ranges::size(firstColumnBatch);
719 if (!((std::size(moreColumnBatches) == rowCount) && ...))
720 throw std::invalid_argument { "Uneven number of rows" };
721
722 size_t rowStart = 0;
723
724 // clang-format off
725 // NOLINTNEXTLINE(performance-no-int-to-ptr)
726 RequireSuccess(SQLSetStmtAttr(m_hStmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) rowCount, 0));
727 RequireSuccess(SQLSetStmtAttr(m_hStmt, SQL_ATTR_PARAM_BIND_OFFSET_PTR, &rowStart, 0));
728 RequireSuccess(SQLSetStmtAttr(m_hStmt, SQL_ATTR_PARAM_BIND_TYPE, SQL_PARAM_BIND_BY_COLUMN, 0));
729 RequireSuccess(SQLSetStmtAttr(m_hStmt, SQL_ATTR_PARAM_OPERATION_PTR, SQL_PARAM_PROCEED, 0));
730 RequireSuccess(SqlDataBinder<std::remove_cvref_t<decltype(*std::ranges::data(firstColumnBatch))>>::
731 InputParameter(m_hStmt, 1, *std::ranges::data(firstColumnBatch), *this));
732 SQLUSMALLINT column = 1;
733 (RequireSuccess(SqlDataBinder<std::remove_cvref_t<decltype(*std::ranges::data(moreColumnBatches))>>::
734 InputParameter(m_hStmt, ++column, *std::ranges::data(moreColumnBatches), *this)), ...);
735 RequireSuccess(SQLExecute(m_hStmt));
736 ProcessPostExecuteCallbacks();
737 // clang-format on
738}
739
740template <SqlInputParameterBatchBinder FirstColumnBatch, std::ranges::range... MoreColumnBatches>
741inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::ExecuteBatch(FirstColumnBatch const& firstColumnBatch,
742 MoreColumnBatches const&... moreColumnBatches)
743{
744 // If the input ranges are contiguous and their element types are contiguous and supported as well,
745 // we can use the native batch execution.
746 if constexpr (SqlNativeBatchable<FirstColumnBatch, MoreColumnBatches...>)
747 ExecuteBatchNative(firstColumnBatch, moreColumnBatches...);
748 else
749 ExecuteBatchSoft(firstColumnBatch, moreColumnBatches...);
750}
751
752template <SqlInputParameterBatchBinder FirstColumnBatch, std::ranges::range... MoreColumnBatches>
753void SqlStatement::ExecuteBatchSoft(FirstColumnBatch const& firstColumnBatch, MoreColumnBatches const&... moreColumnBatches)
754{
755 if (m_expectedParameterCount != 1 + sizeof...(moreColumnBatches))
756 throw std::invalid_argument { "Invalid number of columns" };
757
758 auto const rowCount = std::ranges::size(firstColumnBatch);
759 if (!((std::size(moreColumnBatches) == rowCount) && ...))
760 throw std::invalid_argument { "Uneven number of rows" };
761
762 for (auto const rowIndex: std::views::iota(size_t { 0 }, rowCount))
763 {
764 std::apply(
765 [&]<SqlInputParameterBinder... ColumnValues>(ColumnValues const&... columnsInRow) {
766 SQLUSMALLINT column = 0;
767 ((++column, SqlDataBinder<ColumnValues>::InputParameter(m_hStmt, column, columnsInRow, *this)), ...);
768 RequireSuccess(SQLExecute(m_hStmt));
769 ProcessPostExecuteCallbacks();
770 },
771 std::make_tuple(std::ref(*std::ranges::next(std::ranges::begin(firstColumnBatch), rowIndex)),
772 std::ref(*std::ranges::next(std::ranges::begin(moreColumnBatches), rowIndex))...));
773 }
774}
775
776template <SqlGetColumnNativeType T>
777inline bool SqlStatement::GetColumn(SQLUSMALLINT column, T* result) const
778{
779 SQLLEN indicator {}; // TODO: Handle NULL values if we find out that we need them for our use-cases.
780 RequireSuccess(SqlDataBinder<T>::GetColumn(m_hStmt, column, result, &indicator, *this));
781 return indicator != SQL_NULL_DATA;
782}
783
784namespace detail
785{
786
787template <typename T>
788concept SqlNullableType = (std::same_as<T, SqlVariant> || IsSpecializationOf<std::optional, T>);
789
790} // end namespace detail
791
792template <SqlGetColumnNativeType T>
793inline T SqlStatement::GetColumn(SQLUSMALLINT column) const
794{
795 T result {};
796 SQLLEN indicator {};
797 RequireSuccess(SqlDataBinder<T>::GetColumn(m_hStmt, column, &result, &indicator, *this));
798 if constexpr (!detail::SqlNullableType<T>)
799 if (indicator == SQL_NULL_DATA)
800 throw std::runtime_error { "Column value is NULL" };
801 return result;
802}
803
804template <SqlGetColumnNativeType T>
805inline std::optional<T> SqlStatement::GetNullableColumn(SQLUSMALLINT column) const
806{
807 T result {};
808 SQLLEN indicator {}; // TODO: Handle NULL values if we find out that we need them for our use-cases.
809 RequireSuccess(SqlDataBinder<T>::GetColumn(m_hStmt, column, &result, &indicator, *this));
810 if (indicator == SQL_NULL_DATA)
811 return std::nullopt;
812 return { std::move(result) };
813}
814
815inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::ExecuteDirect(SqlQueryObject auto const& query,
816 std::source_location location)
817{
818 return ExecuteDirect(query.ToSql(), location);
819}
820
821template <typename Callable>
822 requires std::invocable<Callable, SqlMigrationQueryBuilder&>
823void SqlStatement::MigrateDirect(Callable const& callable, std::source_location location)
824{
825 auto migration = SqlMigrationQueryBuilder { Connection().QueryFormatter() };
826 callable(migration);
827 auto const queries = migration.GetPlan().ToSql();
828 for (auto const& query: queries)
829 {
830 ExecuteDirect(query, location);
831 CloseCursor();
832 }
833}
834
835template <typename T>
836 requires(!std::same_as<T, SqlVariant>)
837inline std::optional<T> SqlStatement::ExecuteDirectScalar(std::string_view const& query, std::source_location location)
838{
839 auto const _ = detail::Finally([this] { CloseCursor(); });
840 ExecuteDirect(query, location);
841 RequireSuccess(FetchRow());
842 return GetNullableColumn<T>(1);
843}
844
845template <typename T>
846 requires(std::same_as<T, SqlVariant>)
847inline T SqlStatement::ExecuteDirectScalar(std::string_view const& query, std::source_location location)
848{
849 auto const _ = detail::Finally([this] { CloseCursor(); });
850 ExecuteDirect(query, location);
851 RequireSuccess(FetchRow());
852 if (auto result = GetNullableColumn<T>(1); result.has_value())
853 return *result;
854 return SqlVariant { SqlNullValue };
855}
856
857template <typename T>
858 requires(!std::same_as<T, SqlVariant>)
859inline std::optional<T> SqlStatement::ExecuteDirectScalar(SqlQueryObject auto const& query, std::source_location location)
860{
861 return ExecuteDirectScalar<T>(query.ToSql(), location);
862}
863
864template <typename T>
865 requires(std::same_as<T, SqlVariant>)
866inline T SqlStatement::ExecuteDirectScalar(SqlQueryObject auto const& query, std::source_location location)
867{
868 return ExecuteDirectScalar<T>(query.ToSql(), location);
869}
870
871inline LIGHTWEIGHT_FORCE_INLINE void SqlStatement::CloseCursor() noexcept
872{
873 // SQLCloseCursor(m_hStmt);
874 SQLFreeStmt(m_hStmt, SQL_CLOSE);
876}
877
878inline LIGHTWEIGHT_FORCE_INLINE SqlResultCursor SqlStatement::GetResultCursor() noexcept
879{
880 return SqlResultCursor { *this };
881}
882
883inline LIGHTWEIGHT_FORCE_INLINE SqlVariantRowCursor SqlStatement::GetVariantRowCursor() noexcept
884{
885 return SqlVariantRowCursor { SqlResultCursor { *this } };
886}
887
888// }}}
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.
void OnBindInputParameter(std::string_view const &name, T &&value)
Invoked when an input parameter is bound.
Definition SqlLogger.hpp:78
virtual void OnFetchEnd()=0
Invoked when fetching is done.
static SqlLogger & GetLogger()
Retrieves the currently configured logger.
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.
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()
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.