这里使用mariadb,其他数据库的操作应该是类似的。
MySQL字符串和二进制
数据类型mariadb官方文档
odbc api官方文档
@[TOC]
# 定长二进制
使用BINARY保存定长二进制。
这里以保存哈希后的密码为例。
## 定义表
CREATE TABLE pw (
id BIGINT UNSIGNED PRIMARY KEY,
4)
pw BINARY( )
¶ 写入
官方文档中使用了SQLPutData
但是比较麻烦。这里直接使用SQLBindParameter把数据绑定到参数上,然后用SQLExecDirect执行insert即可。
关键代码:
sizeof(binary);
SQLLEN length = 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BINARY,
retcode = SQLBindParameter(hstmt, sizeof(binary), 0, binary, sizeof(binary), &length);
const char *stmt = "INSERT INTO pw VALUES(3, ?);";
retcode = SQLExecDirect(hstmt, (SQLCHAR*)stmt, SQL_NTS);
注意SQLBindParameter的最后一个参数如果为NULL,则相当于告诉odbc遇到NULL时停止,也就是null-terminated。如果要写入含NULL的数据,则必须指定要写入的数据长度。
¶ 读取
直接用SQLExecDirect执行select,然后用SQLBindCol绑定参数,然后用SQLFetch把数据读出来即可。
关键代码:
"SELECT * FROM pw;", SQL_NTS);
retcode = SQLExecDirect(hstmt, (SQLCHAR*)1, SQL_C_UBIGINT, &id, sizeof(id), &length);
SQLBindCol(hstmt, 2, SQL_C_BINARY, binary, sizeof(binary), &length);
SQLBindCol(hstmt, while (SQL_NO_DATA != SQLFetch(hstmt)) {
'\t' << hex << *(uint32_t*)binary << endl;
cout << id << }
¶ 完整代码
#include <iostream>
#include <cstring>
#if defined(_WIN16) || defined(_WIN32) || defined(_WIN64)
#include <windows.h>
#endif
#include <sqlext.h>
using namespace std;
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt = SQL_NULL_HSTMT;
void Cleanup() {
//释放语句句柄
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc != SQL_NULL_HDBC) {
//断开数据库连接
SQLDisconnect(hdbc); //释放连接句柄
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
//释放环境句柄句柄
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
int main() {
SQLRETURN retcode;
SQLLEN length;
// Allocate the ODBC environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv); if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
"SQLAllocHandle(Env) Failed\n\n");
printf(
Cleanup(); return(9);
}
// Notify ODBC that this is an ODBC 3.0 app.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER); if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
"SQLSetEnvAttr(ODBC version) Failed\n\n");
printf(
Cleanup(); return(9);
}
// Allocate ODBC connection handle and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
"SQLAllocHandle(hdbc1) Failed\n\n");
printf(
Cleanup(); return(9);
}
//数据库连接
//第二个参数是之前配置的数据源,后面是数据库用户名和密码,如果数据源中已经指定了就直接写NULL即可。
"company",SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
retcode = SQLConnect(hdbc,(SQLCHAR*)if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLConnect() Failed\n\n");
printf(
Cleanup(); return(9);
}
//分配执行语句句柄
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLAllocHandle(hstmt1) Failed\n\n");
printf(
Cleanup(); return(9);
}
"use test;", SQL_NTS);
retcode = SQLExecDirect(hstmt,(SQLCHAR*)if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLExecDirect Failed: use test;\n\n");
printf(
Cleanup(); return(9);
}
SQLUBIGINT id;uint8_t binary[4] = {0x01, 0x02, 0x00, 0x04};
sizeof(binary);
length = 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BINARY,
retcode = SQLBindParameter(hstmt, sizeof(binary), 0, binary, sizeof(binary), &length);
if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLBindParameter Failed\n\n");
printf(
Cleanup(); return(9);
}
const char *stmt = "INSERT INTO pw VALUES(6, ?);";
retcode = SQLExecDirect(hstmt, (SQLCHAR*)stmt, SQL_NTS);if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLExecDirect Failed: %s\n\n", stmt);
printf(
Cleanup(); return(9);
}
2333;
id = 0, sizeof(binary));
memset(binary, "SELECT * FROM pw;", SQL_NTS);
retcode = SQLExecDirect(hstmt, (SQLCHAR*)if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLExecDirect Failed: SELECT * FROM pw;\n\n");
printf(
Cleanup(); return(9);
}"id\tpw\n";
cout << 1, SQL_C_UBIGINT, &id, sizeof(id), &length);
SQLBindCol(hstmt, 2, SQL_C_BINARY, binary, sizeof(binary), &length);
SQLBindCol(hstmt, while (SQL_NO_DATA != SQLFetch(hstmt)) {
'\t' << hex << *(uint32_t*)binary << endl;
cout << id <<
}
Cleanup();
return 0;
}
结果:
6 4000201
可见已经把数据写入了,只是因为我的机器是小端字节序的,所以字节序反了。
# 非定长二进制
blob系列和varbinary都可以保存非定长二进制。
它们的区别是blob系列的数据是存储在表外的,而varbinary的数据是存储在表内的,所以blob在select的时候相当于定长域,效率比较高,但是需要额外从表外取数据。而binary在select的时候相当于变长域,会影响效率,但是可以直接从表中获取数据。
blob系列官方文档:
TINYBLOB: 最长255字节
BLOB: 最长65535字节
LONGBLOB: 4,294,967,295字节
varbinary文档:https://mariadb.com/kb/en/varbinary/
¶ blob
¶ 建表
CREATE TABLE msgcontent(
PRIMARY KEY,
msgid BIGINT UNSIGNED BLOB(2000)
content );
¶ 写入
关键代码:
uint8_t content[11] = {1, 2, 3, 4, 5, 0, 7, 8, 9, 10};
sizeof(content);
SQLLEN contentLen = 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BINARY,
retcode = SQLBindParameter(hstmt, 0, content, sizeof(content), &contentLen);
MAX_CONTENT_LEN, const char *stmt = "INSERT INTO msgcontent VALUES(3, ?);";
retcode = SQLExecDirect(hstmt, (SQLCHAR*)stmt, SQL_NTS);
注意SQLBindParameter的最后一个参数如果为NULL,则相当于告诉odbc遇到NULL时停止,也就是null-terminated。如果要写入含NULL的数据,则必须指定要写入的数据长度。
¶ 读取
关键代码:
3332;
SQLUBIGINT id = 0, sizeof(content));
memset(content, "SELECT * FROM msgcontent;";
stmt =
retcode = SQLExecDirect(hstmt, (SQLCHAR*)stmt, SQL_NTS);
SQLLEN idLen;"id\tcontent\n";
cout << 1, SQL_C_UBIGINT, &id, sizeof(id), &idLen);
SQLBindCol(hstmt, 2, SQL_C_BINARY, content, sizeof(content), &contentLen);
SQLBindCol(hstmt, while (SQL_NO_DATA != SQLFetch(hstmt)) {
'\t';
cout << id << if (SQL_NULL_DATA == contentLen) {
"<NULL>";
cout << else {
} '(' << contentLen << ')';
cout << for (size_t i = 0; i < contentLen; ++i) {
"%02x", content[i]);
printf(
}
}
cout << endl; }
¶ 完整代码
#include <iostream>
#include <cstring>
#if defined(_WIN16) || defined(_WIN32) || defined(_WIN64)
#include <windows.h>
#endif
#include <sqlext.h>
using namespace std;
#define MAX_CONTENT_LEN 2000
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt = SQL_NULL_HSTMT;
void Cleanup() {
//释放语句句柄
if (hstmt != SQL_NULL_HSTMT)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc != SQL_NULL_HDBC) {
//断开数据库连接
SQLDisconnect(hdbc); //释放连接句柄
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
//释放环境句柄句柄
if (henv != SQL_NULL_HENV)
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
int main() {
SQLRETURN retcode;
// Allocate the ODBC environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv); if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
"SQLAllocHandle(Env) Failed\n\n");
printf(
Cleanup(); return(9);
}
// Notify ODBC that this is an ODBC 3.0 app.
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER); if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
"SQLSetEnvAttr(ODBC version) Failed\n\n");
printf(
Cleanup(); return(9);
}
// Allocate ODBC connection handle and connect.
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); if ((retcode != SQL_SUCCESS_WITH_INFO) && (retcode != SQL_SUCCESS)) {
"SQLAllocHandle(hdbc1) Failed\n\n");
printf(
Cleanup(); return(9);
}
//数据库连接
//第二个参数是之前配置的数据源,后面是数据库用户名和密码,如果数据源中已经指定了就直接写NULL即可。
"company",SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
retcode = SQLConnect(hdbc,(SQLCHAR*)if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLConnect() Failed\n\n");
printf(
Cleanup(); return(9);
}
//分配执行语句句柄
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt); if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLAllocHandle(hstmt1) Failed\n\n");
printf(
Cleanup(); return(9);
}
"use test;", SQL_NTS);
retcode = SQLExecDirect(hstmt,(SQLCHAR*)if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLExecDirect Failed: use test;\n\n");
printf(
Cleanup(); return(9);
}
uint8_t content[11] = {1, 2, 3, 4, 5, 0, 7, 8, 9, 10};
sizeof(content);
SQLLEN contentLen = 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BINARY,
retcode = SQLBindParameter(hstmt, 0, content, sizeof(content), &contentLen);
MAX_CONTENT_LEN, if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLBindParameter Failed\n\n");
printf(
Cleanup(); return(9);
}
const char *stmt = "INSERT INTO msgcontent VALUES(3, ?);";
retcode = SQLExecDirect(hstmt, (SQLCHAR*)stmt, SQL_NTS);if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLExecDirect Failed: %s\n\n", stmt);
printf(
Cleanup(); return(9);
}
3332;
SQLUBIGINT id = 0, sizeof(content));
memset(content, "SELECT * FROM msgcontent;";
stmt =
retcode = SQLExecDirect(hstmt, (SQLCHAR*)stmt, SQL_NTS);if ((retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO)) {
"SQLExecDirect Failed: %s\n\n", stmt);
printf(
Cleanup(); return(9);
}
SQLLEN idLen;"id\tcontent\n";
cout << 1, SQL_C_UBIGINT, &id, sizeof(id), &idLen);
SQLBindCol(hstmt, 2, SQL_C_BINARY, content, sizeof(content), &contentLen);
SQLBindCol(hstmt, while (SQL_NO_DATA != SQLFetch(hstmt)) {
'\t';
cout << id << if (SQL_NULL_DATA == contentLen) {
"<NULL>";
cout << else {
} '(' << contentLen << ')';
cout << for (size_t i = 0; i < contentLen; ++i) {
"%02x", content[i]);
printf(
}
}
cout << endl;
}
Cleanup();
return 0;
}