From 9ed5ee9526d36df8d5b96fd461c08c7c56e1dc04 Mon Sep 17 00:00:00 2001
From: znone <glyc@sina.com.cn>
Date: Sat, 15 Apr 2017 09:32:33 +0000
Subject: [PATCH] 支持返回多结果集的查询
---
include/qtl_sqlite.hpp | 59 ++++++-
test/TestOdbc.cpp | 4
test/TestSqlite.cpp | 4
test/TestMysql.cpp | 4
include/qtl_common.hpp | 159 ++++++++++++++++++----
include/qtl_odbc.hpp | 31 +++-
include/qtl_mysql.hpp | 65 ++++++--
README.md | 40 +++++
8 files changed, 287 insertions(+), 79 deletions(-)
diff --git a/README.md b/README.md
index b519a03..06806b7 100644
--- a/README.md
+++ b/README.md
@@ -108,6 +108,46 @@
- length 数据的实际长度
- is_truncated 数据是否被截断
+#### 9. 支持标准库以外的字符串类型
+除了标准库提供的std::string，另外其他库也提供了自己的字符串类，比如QT的QString，MFC/ATL的CString等。qtl也可以将字符字段绑定到这些类型上。扩展方法是：
+1. 为你的字符串类型，对 qtl::bind_string_helper 实现一个专门化。如果该字符串类型有符合标准库字符串语义的以下成员函数，可以跳过这一步：assign，clear，resize，data，size；
+2. 为你的字符串类型，对 qtl::bind_field 实现一个专门化；
+
+因为QT的QString有兼容标准库的成员函数，所以绑定到QString只需要一步：
+
+```C++
+namespace qtl
+{
+ template<typename Command>
+ inline void bind_field(Command& command, size_t index, QString&& value)
+ {
+ command.bind_field(index, bind_string(std::forward<QString>(value)));
+ }
+}
+
+```
+
+#### 10.处理返回多个结果集的查询
+有些查询语句会返回多个结果集。使用函数query执行这些查询只能得到第一个结果集。要处理所有结果集需要使用query_multi或query_multi_with_params。query_multi不会为没有结果集的查询调用回调函数。例如：
+```SQL
+CREATE PROCEDURE test_proc()
+BEGIN
+ select 0, 'hello world' from dual;
+ select now() from dual;
+END
+```
+```C++
+db.query_multi("call test_proc",
+ [](uint32_t i, const std::string& str) {
+ printf("0=\"%d\", 'hello world'=\"%s\"\n", i, str.data());
+}, [](const qtl::mysql::time& time) {
+ struct tm tm;
+ time.as_tm(tm);
+ printf("current time is: %s\n", asctime(&tm));
+});
+
+```
+
## 有关MySQL的说明
访问MySQL时，包含头文件qtl_mysql.hpp。
diff --git a/include/qtl_common.hpp b/include/qtl_common.hpp
index af9f409..5015d9c 100644
--- a/include/qtl_common.hpp
+++ b/include/qtl_common.hpp
@@ -42,34 +42,11 @@
const size_t blob_buffer_size=64*1024;
-template<typename Binder, typename T>
-inline void bind(Binder& binder, const T& v)
+inline std::string& trim_string(std::string& str, const char* target)
{
- binder.bind(const_cast<T&>(v));
-}
-
-template<typename Binder, typename T>
-inline void bind(Binder& binder, T&& v)
-{
- binder.bind(v);
-}
-
-template<typename Binder>
-inline void bind(Binder& binder, const char* str)
-{
- binder.bind((char*)str, (unsigned long)strlen(str));
-}
-
-template<typename Binder>
-inline void bind(Binder& binder, const std::string& str)
-{
- binder.bind((char*)str.data(), (unsigned long)str.size());
-}
-
-template<typename Binder>
-inline void bind(Binder& binder, std::string&& str)
-{
- binder.bind((char*)str.data(), (unsigned long)str.size());
+ str.erase(0, str.find_first_not_of(target));
+ str.erase(str.find_last_not_of(target)+1);
+ return str;
}
template<typename T>
@@ -94,6 +71,29 @@
operator const data_type&() const { return data; }
};
+template<typename StringT>
+struct bind_string_helper
+{
+ typedef StringT string_type;
+ typedef typename string_type::value_type char_type;
+ bind_string_helper(string_type&& value) : m_value(std::forward<string_type>(value)) { }
+ void clear() { m_value.clear(); }
+ char_type* alloc(size_t n) { m_value.resize(n); return (char_type*)m_value.data(); }
+ void truncate(size_t n) { m_value.resize(n); }
+ void assign(const char_type* str, size_t n) { m_value.assign(str, n); }
+ const char_type* data() const { return m_value.data(); }
+ size_t size() const { return m_value.size(); }
+private:
+ string_type&& m_value;
+};
+
+template<typename StringT>
+inline bind_string_helper<typename std::decay<StringT>::type> bind_string(StringT&& value)
+{
+ typedef typename std::decay<StringT>::type string_type;
+ return bind_string_helper<string_type>(std::forward<string_type>(value));
+}
+
template<typename Command, typename T>
inline void bind_param(Command& command, size_t index, const T& param)
{
@@ -112,10 +112,46 @@
command.bind_field(index, value, N);
}
+template<typename Command, size_t N>
+inline void bind_field(Command& command, size_t index, wchar_t (&value)[N])
+{
+ command.bind_field(index, value, N);
+}
+
template<typename Command>
inline void bind_field(Command& command, size_t index, char* value, size_t length)
{
command.bind_field(index, value, length);
+}
+
+template<typename Command>
+inline void bind_field(Command& command, size_t index, wchar_t* value, size_t length)
+{
+ command.bind_field(index, value, length);
+}
+
+template<typename Command>
+inline void bind_field(Command& command, size_t index, std::string&& value)
+{
+ command.bind_field(index, bind_string(std::forward<std::string>(value)));
+}
+
+template<typename Command>
+inline void bind_field(Command& command, size_t index, std::wstring&& value)
+{
+ command.bind_field(index, bind_string(std::forward<std::wstring>(value)));
+}
+
+template<typename Command>
+inline void bind_field(Command& command, size_t index, std::vector<char>&& value)
+{
+ command.bind_field(index, bind_string(std::forward<std::vector<char>>(value)));
+}
+
+template<typename Command>
+inline void bind_field(Command& command, size_t index, std::vector<wchar_t>&& value)
+{
+ command.bind_field(index, bind_string(std::forward<std::vector<wchar_t>>(value)));
}
namespace detail
@@ -390,6 +426,28 @@
return make_values_noclass(&Functor::operator());
}
+template<typename Command, typename ValueProc>
+inline void fetch_command(Command& command, ValueProc&& proc)
+{
+ auto values=make_values(proc);
+ typedef decltype(values) values_type;
+ while(command.fetch(std::forward<values_type>(values)))
+ {
+ if(!detail::apply(std::forward<ValueProc>(proc), std::forward<values_type>(values)))
+ break;
+ }
+}
+
+template<typename Command, typename ValueProc, typename... OtherProc>
+inline void fetch_command(Command& command, ValueProc&& proc, OtherProc&&... other)
+{
+ fetch_command(command, std::forward<ValueProc>(proc));
+ if(command.next_result())
+ {
+ fetch_command(command, std::forward<OtherProc>(other)...);
+ }
+}
+
}
template<typename Command, typename T>
@@ -581,17 +639,17 @@
template<typename... Params>
void execute_direct(const char* query_text, size_t text_length, uint64_t* affected, const Params&... params)
{
- execute(query_text, text_length, std::make_tuple(params...), affected);
+ execute(query_text, text_length, std::forward_as_tuple(params...), affected);
}
template<typename... Params>
void execute_direct(const char* query_text, uint64_t* affected, const Params&... params)
{
- execute(query_text, std::make_tuple(params...), affected);
+ execute(query_text, std::forward_as_tuple(params...), affected);
}
template<typename... Params>
void execute_direct(const std::string& query_text, uint64_t* affected, const Params&... params)
{
- execute(query_text, std::make_tuple(params...), affected);
+ execute(query_text, std::forward_as_tuple(params...), affected);
}
template<typename Params>
@@ -621,19 +679,19 @@
template<typename... Params>
uint64_t insert_direct(const char* query_text, size_t text_length, const Params&... params)
{
- return insert(query_text, text_length, std::make_tuple(params...));
+ return insert(query_text, text_length, std::forward_as_tuple(params...));
}
template<typename... Params>
uint64_t insert_direct(const char* query_text, const Params&... params)
{
- return insert(query_text, strlen(query_text), std::make_tuple(params...));
+ return insert(query_text, strlen(query_text), std::forward_as_tuple(params...));
}
template<typename... Params>
uint64_t insert_direct(const std::string& query_text, const Params&... params)
{
- return insert(query_text.data(), query_text.length(), std::make_tuple(params...));
+ return insert(query_text.data(), query_text.length(), std::forward_as_tuple(params...));
}
template<typename Record, typename Params>
@@ -740,6 +798,41 @@
query_explicit(query_text, detail::make_values(proc), std::forward<ValueProc>(proc));
}
+ template<typename Params, typename... ValueProc>
+ void query_multi_with_params(const char* query_text, size_t text_length, const Params& params, ValueProc&&... proc)
+ {
+ T* pThis=static_cast<T*>(this);
+ Command command=pThis->open_command(query_text, text_length);
+ command.execute(params);
+ detail::fetch_command(command, std::forward<ValueProc>(proc)...);
+ command.close();
+ }
+ template<typename Params, typename... ValueProc>
+ void query_multi_with_params(const char* query_text, const Params& params, ValueProc&&... proc)
+ {
+ query_multi_with_params(query_text, strlen(query_text), params, std::forward<ValueProc>(proc)...);
+ }
+ template<typename Params, typename... ValueProc>
+ void query_multi_with_params(const std::string& query_text, const Params& params, ValueProc&&... proc)
+ {
+ query_multi_with_params(query_text.data(), query_text.size(), params, std::forward<ValueProc>(proc)...);
+ }
+ template<typename... ValueProc>
+ void query_multi(const char* query_text, size_t text_length, ValueProc&&... proc)
+ {
+ query_multi_with_params<std::tuple<>>(query_text, text_length, std::make_tuple(), std::forward<ValueProc>(proc)...);
+ }
+ template<typename... ValueProc>
+ void query_multi(const char* query_text, ValueProc&&... proc)
+ {
+ query_multi_with_params<std::tuple<>>(query_text, strlen(query_text), std::make_tuple(), std::forward<ValueProc>(proc)...);
+ }
+ template<typename... ValueProc>
+ void query_multi(const std::string& query_text, ValueProc&&... proc)
+ {
+ query_multi_with_params<std::tuple<>>(query_text.data(), query_text.size(), std::make_tuple(), std::forward<ValueProc>(proc)...);
+ }
+
template<typename Params, typename Values>
void query_first(const char* query_text, size_t text_length, const Params& params, Values&& values)
{
diff --git a/include/qtl_mysql.hpp b/include/qtl_mysql.hpp
index b0cb96b..198b82f 100644
--- a/include/qtl_mysql.hpp
+++ b/include/qtl_mysql.hpp
@@ -163,6 +163,24 @@
}
};
+
+template<typename T>
+inline void bind(binder& binder, const T& v)
+{
+ binder.bind(const_cast<T&>(v));
+}
+
+template<typename T>
+inline void bind(binder& binder, T&& v)
+{
+ binder.bind(v);
+}
+
+inline void bind(binder& binder, const char* str)
+{
+ binder.bind((char*)str, (unsigned long)strlen(str));
+}
+
class statement;
class database;
@@ -273,7 +291,7 @@
template<class Param>
void bind_param(size_t index, const Param& param)
{
- qtl::bind(m_binders[index], param);
+ bind(m_binders[index], param);
}
template<class Type>
@@ -281,7 +299,7 @@
{
if(m_result)
{
- qtl::bind(m_binders[index], std::forward<Type>(value));
+ bind(m_binders[index], std::forward<Type>(value));
m_binderAddins[index].m_after_fetch=if_null<typename std::remove_reference<Type>::type>(value);
}
}
@@ -311,17 +329,20 @@
bind_field(index, value.data(), value.size());
}
- void bind_field(size_t index, std::string&& value)
+ template<typename T>
+ void bind_field(size_t index, bind_string_helper<T>&& value)
{
if(m_result)
{
MYSQL_FIELD* field=mysql_fetch_field_direct(m_result, (unsigned int)index);
if(field==NULL) throw_exception();
value.clear();
- value.resize(field->length);
- m_binderAddins[index].m_after_fetch=resize_binder<std::string>(value);
- qtl::bind(m_binders[index], std::forward<std::string>(value));
- m_binders[index].buffer_type=field->type;
+ typename bind_string_helper<T>::char_type* data=value.alloc(field->length);
+ m_binderAddins[index].m_after_fetch= [value](const binder& b) mutable {
+ if(*b.is_null) value.clear();
+ else value.truncate(*b.length);
+ };
+ m_binders[index].bind(data, field->length, field->type);
}
}
void bind_param(size_t index, std::istream& param)
@@ -428,6 +449,23 @@
return false;
}
+ bool next_result()
+ {
+ if(m_result)
+ {
+ mysql_free_result(m_result);
+ m_result=NULL;
+ mysql_stmt_free_result(m_stmt);
+ }
+ int ret=0;
+ do
+ {
+ ret=mysql_stmt_next_result(m_stmt);
+ if(ret>0) throw_exception();
+ }while(ret==0 && mysql_stmt_field_count(m_stmt)<=0);
+ return ret==0;
+ }
+
my_ulonglong affetced_rows()
{
return mysql_stmt_affected_rows(m_stmt);
@@ -490,19 +528,6 @@
void throw_exception() { throw mysql::error(*this); }
private:
- template<typename CharCont>
- struct resize_binder
- {
- resize_binder(CharCont& cont) : m_cont(cont) { }
- void operator()(const binder& b) const
- {
- if(*b.is_null) m_cont.clear();
- else m_cont.resize(*b.length);
- }
-
- CharCont& m_cont;
- };
-
template<typename Value>
struct if_null
{
diff --git a/include/qtl_odbc.hpp b/include/qtl_odbc.hpp
index e021ef6..99a4c49 100644
--- a/include/qtl_odbc.hpp
+++ b/include/qtl_odbc.hpp
@@ -358,7 +358,7 @@
text[p.m_indicator]='\0';
}
};
- verify_error(SQLBindCol(m_handle, index+1, SQL_C_CHAR, v, n-1, &m_params[index].m_indicator));
+ verify_error(SQLBindCol(m_handle, index+1, SQL_C_CHAR, v, n, &m_params[index].m_indicator));
}
void bind_field(SQLUSMALLINT index, wchar_t* v, size_t n)
{
@@ -373,20 +373,20 @@
text[p.m_indicator]='\0';
}
};
- verify_error(SQLBindCol(m_handle, index+1, SQL_C_WCHAR, v, n-1, &m_params[index].m_indicator));
+ verify_error(SQLBindCol(m_handle, index+1, SQL_C_WCHAR, v, n, &m_params[index].m_indicator));
}
- template<typename CharType>
- void bind_field(SQLUSMALLINT index, std::basic_string<CharType>&& v)
+ template<typename T>
+ void bind_field(SQLUSMALLINT index, qtl::bind_string_helper<T>&& v)
{
SQLLEN length=0;
SQLColAttribute(m_handle, index+1, SQL_DESC_LENGTH, NULL, 0, NULL, &length);
- v.resize(length);
- bind_field(index, const_cast<char*>(v.data()), v.size()+1);
- m_params[index].m_after_fetch=[&v](const param_data& p) {
+ typename qtl::bind_string_helper<T>::char_type* data=v.alloc(length);
+ bind_field(index, data, length+1);
+ m_params[index].m_after_fetch=[v](const param_data& p) mutable {
if(p.m_indicator==SQL_NULL_DATA)
v.clear();
else
- v.resize(p.m_indicator);
+ v.truncate(p.m_indicator);
};
}
template<size_t N>
@@ -516,6 +516,21 @@
return false;
}
+ bool next_result()
+ {
+ SQLRETURN ret;
+ SQLSMALLINT count=0;
+ m_binded_cols=false;
+ do
+ {
+ ret=SQLMoreResults(m_handle);
+ if(ret==SQL_ERROR || ret==SQL_INVALID_HANDLE)
+ verify_error(ret);
+ verify_error(SQLNumResultCols(m_handle, &count));
+ }while(count==0);
+ return ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO;
+ }
+
SQLLEN affetced_rows()
{
SQLLEN count=0;
diff --git a/include/qtl_sqlite.hpp b/include/qtl_sqlite.hpp
index 20f7e00..f316bed 100644
--- a/include/qtl_sqlite.hpp
+++ b/include/qtl_sqlite.hpp
@@ -40,7 +40,8 @@
statement() : m_stmt(NULL), m_fetch_result(SQLITE_OK) { }
statement(const statement&) = delete;
statement(statement&& src)
- : m_stmt(src.m_stmt), m_fetch_result(src.m_fetch_result)
+ : m_stmt(src.m_stmt), m_fetch_result(src.m_fetch_result),
+ m_tail_text(std::forward<std::string>(src.m_tail_text))
{
src.m_stmt=NULL;
src.m_fetch_result=SQLITE_OK;
@@ -52,6 +53,7 @@
{
m_stmt=src.m_stmt;
m_fetch_result=src.m_fetch_result;
+ m_tail_text=std::forward<std::string>(src.m_tail_text);
src.m_stmt=NULL;
src.m_fetch_result=SQLITE_OK;
}
@@ -64,8 +66,13 @@
void open(sqlite3* db, const char* query_text, size_t text_length=-1)
{
+ const char* tail=NULL;
close();
- verify_error(sqlite3_prepare_v2(db, query_text, (int)text_length, &m_stmt, NULL));
+ verify_error(sqlite3_prepare_v2(db, query_text, (int)text_length, &m_stmt, &tail));
+ if(tail!=NULL)
+ m_tail_text.assign(tail, query_text+text_length);
+ else
+ m_tail_text.clear();
}
void close()
@@ -245,19 +252,17 @@
{
return sqlite3_column_text(m_stmt, col);
}
- void get_value(int col, std::string&& value) const
+
+ template<typename CharT>
+ const CharT* get_text_value(int col) const;
+
+ template<typename T>
+ void get_value(int col, qtl::bind_string_helper<T>&& value) const
{
+ typedef typename qtl::bind_string_helper<T>::char_type char_type;
int bytes=sqlite3_column_bytes(m_stmt, col);
if(bytes>0)
- value.assign((const char*)sqlite3_column_text(m_stmt, col), bytes/sizeof(char));
- else
- value.clear();
- }
- void get_value(int col, std::wstring&& value) const
- {
- int bytes=sqlite3_column_bytes16(m_stmt, col);
- if(bytes>0)
- value.assign((const wchar_t*)sqlite3_column_text16(m_stmt, col), bytes/sizeof(wchar_t));
+ value.assign(get_text_value<char_type>(col), bytes/sizeof(char_type));
else
value.clear();
}
@@ -326,6 +331,23 @@
return result;
}
+ bool next_result()
+ {
+ sqlite3* db=sqlite3_db_handle(m_stmt);
+ int count=0;
+ do
+ {
+ trim_string(m_tail_text, " \t\r\n");
+ if(!m_tail_text.empty())
+ {
+ open(db, m_tail_text.data(), m_tail_text.size());
+ count=sqlite3_column_count(m_stmt);
+ m_fetch_result=SQLITE_OK;
+ }
+ }while(!m_tail_text.empty() && count==0);
+ return count>0;;
+ }
+
int affetced_rows()
{
sqlite3* db=sqlite3_db_handle(m_stmt);
@@ -340,6 +362,8 @@
protected:
sqlite3_stmt* m_stmt;
+ std::string m_tail_text;
+
int m_fetch_result;
void verify_error(int e)
{
@@ -462,6 +486,17 @@
return stmt;
}
+template<>
+inline const char* statement::get_text_value<char>(int col) const
+{
+ return (const char*)sqlite3_column_text(m_stmt, col);
+}
+template<>
+inline const wchar_t* statement::get_text_value<wchar_t>(int col) const
+{
+ return (const wchar_t*)sqlite3_column_text16(m_stmt, col);
+}
+
}
}
diff --git a/test/TestMysql.cpp b/test/TestMysql.cpp
index 53330c1..d7dda0b 100644
--- a/test/TestMysql.cpp
+++ b/test/TestMysql.cpp
@@ -214,7 +214,7 @@
fs.clear();
fs.seekg(0, ios::beg);
id=db.insert("INSERT INTO test_blob (Filename, Content, MD5) values(?, ?, ?)",
- make_tuple(filename, ref((istream&)fs), qtl::const_blob_data(md5, sizeof(md5))));
+ forward_as_tuple(filename, (istream&)fs, qtl::const_blob_data(md5, sizeof(md5))));
}
catch(qtl::mysql::error& e)
{
@@ -238,7 +238,7 @@
fs.seekg(ios::beg);
std::string source_file;
db.query_first("SELECT Filename, Content, MD5 FROM test_blob WHERE id=?", make_tuple(id),
- make_tuple(ref(source_file), ref((ostream&)fs), qtl::blob_data(md5, sizeof(md5))));
+ forward_as_tuple(source_file, (ostream&)fs, qtl::blob_data(md5, sizeof(md5))));
fs.flush();
mysql_hex_string(md5_hex, (char*)md5, sizeof(md5));
printf("MD5 of file %s stored in database: %s.\n", source_file.data(), md5_hex);
diff --git a/test/TestOdbc.cpp b/test/TestOdbc.cpp
index 58cc44a..c018451 100644
--- a/test/TestOdbc.cpp
+++ b/test/TestOdbc.cpp
@@ -192,7 +192,7 @@
fs.clear();
fs.seekg(0, ios::beg);
m_db.execute("INSERT INTO test_blob (Filename, [Content], MD5) values(?, ?, ?)",
- make_tuple(filename, ref((istream&)fs), qtl::const_blob_data(md5, sizeof(md5))));
+ forward_as_tuple(filename, (istream&)fs, qtl::const_blob_data(md5, sizeof(md5))));
m_db.query_first("SELECT @@IDENTITY", id);
}
catch(qtl::odbc::error& e)
@@ -214,7 +214,7 @@
fs.seekg(ios::beg);
std::string source_file;
m_db.query_first("SELECT Filename, MD5 , [Content]FROM test_blob WHERE id=?", make_tuple(id),
- make_tuple(ref(source_file), qtl::blob_data(md5, sizeof(md5)), ref((ostream&)fs)));
+ forward_as_tuple(source_file, qtl::blob_data(md5, sizeof(md5)), (ostream&)fs));
fs.flush();
cout<<"MD5 of file "<<source_file<<" stored in database: ";
print_hex((const unsigned char*)md5, sizeof(md5));
diff --git a/test/TestSqlite.cpp b/test/TestSqlite.cpp
index 7ac4ad6..c349ad1 100644
--- a/test/TestSqlite.cpp
+++ b/test/TestSqlite.cpp
@@ -225,7 +225,7 @@
print_hex(md5, sizeof(md5));
cout<<endl;
id=db.insert("INSERT INTO test_blob (Filename, Content, MD5) values(?, ?, ?)",
- make_tuple(filename, qtl::const_blob_data(str.data(), str.size()), qtl::const_blob_data(md5, sizeof(md5))));
+ forward_as_tuple(filename, qtl::const_blob_data(str.data(), str.size()), qtl::const_blob_data(md5, sizeof(md5))));
}
catch(qtl::sqlite::error& e)
{
@@ -248,7 +248,7 @@
fs.seekg(ios::beg);
db.query_first("SELECT Filename, Content, MD5 FROM test_blob WHERE id=?", make_tuple(id),
- make_tuple(ref(source_file), ref(fs), qtl::blob_data(md5, 16)));
+ forward_as_tuple(source_file, fs, qtl::blob_data(md5, 16)));
fs.flush();
cout<<"MD5 of file "<<source_file<<" stored in database: ";
print_hex((const unsigned char*)md5, sizeof(md5));
--
Gitblit v1.9.3