bool insertStmt_mysql_stmt_send_long_data()
{
std::unique_ptr<MYSQL, MysqlDestroy> mysql{ mysql_init(nullptr) };
if (mysql_options(&*mysql, MYSQL_SET_CHARSET_NAME, "utf8") != 0) {
printf("error mysql_options\n");
return false;
}
if (!mysql_real_connect(&*mysql, IP.c_str(), USER.c_str(), PASSWORD.c_str(), "test", PORT, nullptr, 0)) {
printf("error mysql_real_connect\n");
return false;
}
std::unique_ptr<MYSQL_STMT, MysqlStmtDestroy> mysql_stmt{mysql_stmt_init(&*mysql)};
std::string sql = "INSERT INTO `txx` (`fblob`, `fname`) VALUES (?, ?)";
mysql_stmt_prepare(&*mysql_stmt, sql.data(), sql.size());
std::string fname = "sdfa'a'b'看看空间x";
std::vector<int32_t> fblob = { 100,0,200,0,123456789,1234567890 };
std::array<MYSQL_BIND, 2> b{};
auto fblob_len = fblob.size() * sizeof(int32_t);
(void)fblob_len;
//unsigned long len;
b[0].buffer_type = MYSQL_TYPE_BLOB;
//b[0].length = &len;
b[1].buffer_type = MYSQL_TYPE_STRING;
b[1].buffer = (void*)fname.data();
unsigned long fname_len = fname.size();
b[1].length = &fname_len;
mysql_stmt_bind_param(&*mysql_stmt, b.data());
/*
if (mysql_stmt_send_long_data(&*mysql_stmt, 0, (const char*)fblob.data(), fblob_len)) {
printf("error mysql_stmt_send_long_data\n");
return false;
}
*/
for (const auto& val : fblob) {
if (mysql_stmt_send_long_data(&*mysql_stmt, 0, (const char*)&val, sizeof(int32_t))) {
printf("error mysql_stmt_send_long_data 0\n");
return false;
}
}
if (mysql_stmt_execute(&*mysql_stmt) != 0) {
printf("error mysql_stmt_execute\n");
return false;
}
return true;
}
int main(int argc, char* argv[])
{
MYSQL *mysql,*sock;
MYSQL_ROW row;
MYSQL_RES *result;
mysql = mysql_init(NULL);
if (!(sock = CONN(0))) {
fprintf(stderr, "Couldn't connect to engine!\n%s\n\n", mysql_error(mysql));
perror("");
exit(1);
}
// simple
char sql[100] = {};
sprintf(sql, "INSERT INTO blob_test(a, b) VALUE (1, 2)");
mysql_query(sock, sql);
mysql_query(sock, "select * from blob_test");
result = mysql_store_result(mysql);
mysql_free_result(result);
#define INSERT_QUERY "INSERT INTO blob_test(a, b) VALUES(?,?)"
MYSQL_STMT *stmt;
MYSQL_BIND bind[2];
long length;
int int_data = 10;
char str[100];
int ret;
stmt = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt, INSERT_QUERY, strlen(INSERT_QUERY));
memset(bind, 0, sizeof(bind));
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&int_data;
bind[0].is_null= 0;
bind[1].buffer_type= MYSQL_TYPE_BLOB;
bind[1].buffer = (char*)&str;
bind[1].is_null= 0;
mysql_stmt_bind_param(stmt, bind);
ret = mysql_stmt_send_long_data(stmt,1,"fails",5);
ret = mysql_stmt_send_long_data(stmt,1," - The most popular Open Source database",40);
mysql_stmt_execute(stmt);
mysql_stmt_close(stmt);
stmt = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt, INSERT_QUERY, strlen(INSERT_QUERY));
size_t s = sizeof(str);
memset(bind, 0, sizeof(bind));
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&int_data;
bind[0].is_null= 0;
bind[1].buffer_type= MYSQL_TYPE_BLOB;
bind[1].buffer = (char*)&str;
bind[1].is_null= 0;
bind[1].length= (char*)&s;
mysql_stmt_bind_param(stmt, bind);
snprintf(str, sizeof(str), "%s", "this success");
mysql_stmt_execute(stmt);
mysql_stmt_close(stmt);
return 0;
}
static int test_wl4166_4(MYSQL *mysql)
{
MYSQL_STMT *stmt;
int rc;
const char *stmt_text;
MYSQL_BIND bind_array[2];
/* Represented as numbers to keep UTF8 tools from clobbering them. */
const char *koi8= "\xee\xd5\x2c\x20\xda\xc1\x20\xd2\xd9\xc2\xc1\xcc\xcb\xd5";
const char *cp1251= "\xcd\xf3\x2c\x20\xe7\xe0\x20\xf0\xfb\xe1\xe0\xeb\xea\xf3";
char buf1[16], buf2[16];
ulong buf1_len, buf2_len;
if (mysql_get_server_version(mysql) < 50100) {
diag("Test requires MySQL Server version 5.1 or above");
return SKIP;
}
rc= mysql_query(mysql, "drop table if exists t1");
check_mysql_rc(rc, mysql);
/*
Create table with binary columns, set session character set to cp1251,
client character set to koi8, and make sure that there is conversion
on insert and no conversion on select
*/
rc= mysql_query(mysql,
"create table t1 (c1 varbinary(255), c2 varbinary(255))");
check_mysql_rc(rc, mysql);
rc= mysql_query(mysql, "set character_set_client=koi8r, "
"character_set_connection=cp1251, "
"character_set_results=koi8r");
check_mysql_rc(rc, mysql);
memset(bind_array, '\0', sizeof(bind_array));
bind_array[0].buffer_type= MYSQL_TYPE_STRING;
bind_array[1].buffer_type= MYSQL_TYPE_STRING;
bind_array[1].buffer= (void *) koi8;
bind_array[1].buffer_length= strlen(koi8);
stmt= mysql_stmt_init(mysql);
check_stmt_rc(rc, stmt);
stmt_text= "insert into t1 (c1, c2) values (?, ?)";
rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
check_stmt_rc(rc, stmt);
mysql_stmt_bind_param(stmt, bind_array);
mysql_stmt_send_long_data(stmt, 0, koi8, strlen(koi8));
/* Cause a reprepare at statement execute */
rc= mysql_query(mysql, "alter table t1 add column d int");
check_mysql_rc(rc, mysql);
rc= mysql_stmt_execute(stmt);
check_stmt_rc(rc, stmt);
stmt_text= "select c1, c2 from t1";
/* c1 and c2 are binary so no conversion will be done on select */
rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
check_stmt_rc(rc, stmt);
rc= mysql_stmt_execute(stmt);
check_stmt_rc(rc, stmt);
bind_array[0].buffer= buf1;
bind_array[0].buffer_length= sizeof(buf1);
bind_array[0].length= &buf1_len;
bind_array[1].buffer= buf2;
bind_array[1].buffer_length= sizeof(buf2);
bind_array[1].length= &buf2_len;
mysql_stmt_bind_result(stmt, bind_array);
rc= mysql_stmt_fetch(stmt);
check_stmt_rc(rc, stmt);
FAIL_UNLESS(buf1_len == strlen(cp1251), "");
FAIL_UNLESS(buf2_len == strlen(cp1251), "");
FAIL_UNLESS(!memcmp(buf1, cp1251, buf1_len), "");
FAIL_UNLESS(!memcmp(buf2, cp1251, buf1_len), "");
rc= mysql_stmt_fetch(stmt);
FAIL_UNLESS(rc == MYSQL_NO_DATA, "");
mysql_stmt_close(stmt);
rc= mysql_query(mysql, "drop table t1");
check_mysql_rc(rc, mysql);
rc= mysql_query(mysql, "set names default");
check_mysql_rc(rc, mysql);
return OK;
}
static int test_ps_i18n(MYSQL *mysql)
{
MYSQL_STMT *stmt;
int rc;
const char *stmt_text;
MYSQL_BIND bind_array[2];
/* Represented as numbers to keep UTF8 tools from clobbering them. */
const char *koi8= "\xee\xd5\x2c\x20\xda\xc1\x20\xd2\xd9\xc2\xc1\xcc\xcb\xd5";
const char *cp1251= "\xcd\xf3\x2c\x20\xe7\xe0\x20\xf0\xfb\xe1\xe0\xeb\xea\xf3";
char buf1[16], buf2[16];
ulong buf1_len, buf2_len;
stmt_text= "DROP TABLE IF EXISTS t1";
rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
check_mysql_rc(rc, mysql);
/*
Create table with binary columns, set session character set to cp1251,
client character set to koi8, and make sure that there is conversion
on insert and no conversion on select
*/
stmt_text= "CREATE TABLE t1 (c1 VARBINARY(255), c2 VARBINARY(255))";
rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
check_mysql_rc(rc, mysql);
stmt_text= "SET CHARACTER_SET_CLIENT=koi8r, "
"CHARACTER_SET_CONNECTION=cp1251, "
"CHARACTER_SET_RESULTS=koi8r";
rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
check_mysql_rc(rc, mysql);
memset(bind_array, '\0', sizeof(bind_array));
bind_array[0].buffer_type= MYSQL_TYPE_STRING;
bind_array[0].buffer= (void *) koi8;
bind_array[0].buffer_length= strlen(koi8);
bind_array[1].buffer_type= MYSQL_TYPE_STRING;
bind_array[1].buffer= (void *) koi8;
bind_array[1].buffer_length= strlen(koi8);
stmt= mysql_stmt_init(mysql);
check_stmt_rc(rc, stmt);
stmt_text= "INSERT INTO t1 (c1, c2) VALUES (?, ?)";
rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
check_stmt_rc(rc, stmt);
mysql_stmt_bind_param(stmt, bind_array);
mysql_stmt_send_long_data(stmt, 0, koi8, strlen(koi8));
rc= mysql_stmt_execute(stmt);
check_stmt_rc(rc, stmt);
stmt_text= "SELECT c1, c2 FROM t1";
/* c1 and c2 are binary so no conversion will be done on select */
rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
check_stmt_rc(rc, stmt);
rc= mysql_stmt_execute(stmt);
check_stmt_rc(rc, stmt);
bind_array[0].buffer= buf1;
bind_array[0].buffer_length= sizeof(buf1);
bind_array[0].length= &buf1_len;
bind_array[1].buffer= buf2;
bind_array[1].buffer_length= sizeof(buf2);
bind_array[1].length= &buf2_len;
mysql_stmt_bind_result(stmt, bind_array);
rc= mysql_stmt_fetch(stmt);
check_stmt_rc(rc, stmt);
FAIL_UNLESS(buf1_len == strlen(cp1251), "buf1_len != strlen(cp1251)");
FAIL_UNLESS(buf2_len == strlen(cp1251), "buf2_len != strlen(cp1251)");
FAIL_UNLESS(!memcmp(buf1, cp1251, buf1_len), "buf1 != cp1251");
FAIL_UNLESS(!memcmp(buf2, cp1251, buf1_len), "buf2 != cp1251");
rc= mysql_stmt_fetch(stmt);
FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
stmt_text= "DROP TABLE IF EXISTS t1";
rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
check_mysql_rc(rc, mysql);
/*
Now create table with two cp1251 columns, set client character
set to koi8 and supply columns of one row as string and another as
binary data. Binary data must not be converted on insert, and both
columns must be converted to client character set on select.
*/
stmt_text= "CREATE TABLE t1 (c1 VARCHAR(255) CHARACTER SET cp1251, "
"c2 VARCHAR(255) CHARACTER SET cp1251)";
rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
check_mysql_rc(rc, mysql);
stmt_text= "INSERT INTO t1 (c1, c2) VALUES (?, ?)";
rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
check_stmt_rc(rc, stmt);
/* this data must be converted */
bind_array[0].buffer_type= MYSQL_TYPE_STRING;
bind_array[0].buffer= (void *) koi8;
bind_array[0].buffer_length= strlen(koi8);
bind_array[1].buffer_type= MYSQL_TYPE_STRING;
bind_array[1].buffer= (void *) koi8;
bind_array[1].buffer_length= strlen(koi8);
mysql_stmt_bind_param(stmt, bind_array);
mysql_stmt_send_long_data(stmt, 0, koi8, strlen(koi8));
rc= mysql_stmt_execute(stmt);
check_stmt_rc(rc, stmt);
/* this data must not be converted */
bind_array[0].buffer_type= MYSQL_TYPE_BLOB;
bind_array[0].buffer= (void *) cp1251;
bind_array[0].buffer_length= strlen(cp1251);
bind_array[1].buffer_type= MYSQL_TYPE_BLOB;
bind_array[1].buffer= (void *) cp1251;
bind_array[1].buffer_length= strlen(cp1251);
mysql_stmt_bind_param(stmt, bind_array);
mysql_stmt_send_long_data(stmt, 0, cp1251, strlen(cp1251));
rc= mysql_stmt_execute(stmt);
check_stmt_rc(rc, stmt);
/* Fetch data and verify that rows are in koi8 */
stmt_text= "SELECT c1, c2 FROM t1";
/* c1 and c2 are binary so no conversion will be done on select */
rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
check_stmt_rc(rc, stmt);
rc= mysql_stmt_execute(stmt);
check_stmt_rc(rc, stmt);
bind_array[0].buffer= buf1;
bind_array[0].buffer_length= sizeof(buf1);
bind_array[0].length= &buf1_len;
bind_array[1].buffer= buf2;
bind_array[1].buffer_length= sizeof(buf2);
bind_array[1].length= &buf2_len;
mysql_stmt_bind_result(stmt, bind_array);
while ((rc= mysql_stmt_fetch(stmt)) == 0)
{
FAIL_UNLESS(buf1_len == strlen(koi8), "buf1_len != strlen(koi8)");
FAIL_UNLESS(buf2_len == strlen(koi8), "buf2_len != strlen(koi8)");
FAIL_UNLESS(!memcmp(buf1, koi8, buf1_len), "buf1 != koi8");
FAIL_UNLESS(!memcmp(buf2, koi8, buf1_len), "buf2 != koi8");
}
FAIL_UNLESS(rc == MYSQL_NO_DATA, "rc != MYSQL_NO_DATA");
mysql_stmt_close(stmt);
stmt_text= "DROP TABLE t1";
rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
check_mysql_rc(rc, mysql);
stmt_text= "SET NAMES DEFAULT";
rc= mysql_real_query(mysql, stmt_text, strlen(stmt_text));
check_mysql_rc(rc, mysql);
return OK;
}
bool CMySqlDatabase::BlobWriteDb( QString &strBlobFile, QString& strSql, QString& strError )
{
QFile file( strBlobFile );
bool bRet = false;
if ( !file.exists( ) ) {
return bRet;
}
bRet = file.open( QIODevice::ReadOnly );
if ( !bRet ) {
return bRet;
}
QByteArray byData = file.readAll( );
file.close( );
bRet = BlobWriteDb( byData, strSql, strError );
return bRet;
#if false
MYSQL_BIND bind[ 1 ];
ulong length = 0;
hStmt = mysql_stmt_init( &hConnect );
bRet = ( NULL != hStmt );
if ( !bRet ) {
GetErrorMsg( 0, strError, false, strSql );
goto FileHandle;
}
byteSql = pCodec->fromUnicode( strSql );
nSize = byteSql.count( );
pSql = byteSql.data( );
nRet = mysql_stmt_prepare( hStmt, pSql, nSize );
bRet = ( 0 == nRet );
if ( !bRet ){
GetErrorMsg( nRet, strError, false, strSql );
goto StmtHandle;
}
memset( bind, 0, sizeof ( bind ) );
bind[ 0 ].buffer_type = MYSQL_TYPE_LONG_BLOB;
bind[ 0 ].length = &length;
bind[ 0 ].is_null = 0; /* Bind the buffers */
//bind[ 0 ].buffer
//bind[ 0 ].buffer_length
bRet = ( 0 == mysql_stmt_bind_param( hStmt, bind ) );
if ( !bRet ) {
GetErrorMsg( 0, strError, false, strSql );
goto StmtHandle;
}
byteSql = file.readAll( );
nSize = byteSql.count( );
pSql = byteSql.data( );
if ( 0 < nSize ) {
nRet = mysql_stmt_send_long_data( hStmt, 0, pSql, nSize );
bRet = ( 0 == nRet );
if ( !bRet ) {
GetErrorMsg( nRet, strError, false, strSql );
goto StmtHandle;
}
}
nRet = mysql_stmt_execute( hStmt );
bRet = ( 0 == nRet );
if ( !bRet ) {
GetErrorMsg( nRet, strError, false, strSql );
}
StmtHandle:
bRet = ( 0 == mysql_stmt_close( hStmt ) );
if ( !bRet ) {
GetErrorMsg( 0, strError, false, strSql );
}
FileHandle:
file.close( );
return bRet;
#endif
}
bool CMySqlDatabase::BlobWriteDb( QByteArray &byData, QString &strSql, QString &strError )
{
MYSQL_BIND bind[ 1 ];
ulong length = 0;
QByteArray byteSql;
int nRet = 0;
int nSize = 0;
const char* pSql = NULL;
hStmt = mysql_stmt_init( &hConnect );
bool bRet = ( NULL != hStmt );
if ( !bRet ) {
GetErrorMsg( 0, strError, false, strSql );
goto FileHandle;
}
byteSql = pCodec->fromUnicode( strSql );
nSize = byteSql.count( );
pSql = byteSql.data( );
nRet = mysql_stmt_prepare( hStmt, pSql, nSize );
bRet = ( 0 == nRet );
if ( !bRet ){
GetErrorMsg( nRet, strError, false, strSql );
goto StmtHandle;
}
memset( bind, 0, sizeof ( bind ) );
bind[ 0 ].buffer_type = MYSQL_TYPE_LONG_BLOB;
bind[ 0 ].length = &length;
bind[ 0 ].is_null = 0; /* Bind the buffers */
//bind[ 0 ].buffer
//bind[ 0 ].buffer_length
bRet = ( 0 == mysql_stmt_bind_param( hStmt, bind ) );
if ( !bRet ) {
GetErrorMsg( 0, strError, false, strSql );
goto StmtHandle;
}
nSize = byData.count( );
pSql = byData.data( );
if ( 0 < nSize ) {
nRet = mysql_stmt_send_long_data( hStmt, 0, pSql, nSize );
bRet = ( 0 == nRet );
if ( !bRet ) {
GetErrorMsg( nRet, strError, false, strSql );
goto StmtHandle;
}
}
nRet = mysql_stmt_execute( hStmt );
bRet = ( 0 == nRet );
if ( !bRet ) {
GetErrorMsg( nRet, strError, false, strSql );
}
StmtHandle:
bRet = ( 0 == mysql_stmt_close( hStmt ) );
if ( !bRet ) {
GetErrorMsg( 0, strError, false, strSql );
}
FileHandle:
return bRet;
}
