什么是准系统 C++将 jpeg 放入 MySQL 表所需的代码吗？

Question

我创建了一个 MySQL 表，其中一列存储 BLOB 类型。 （互联网告诉我 BLOB 是图像的正确数据类型。）

我几乎是 C++ 和 MySQL 的初学者。我想做的是编写一个带有 main() 的小程序，将 jpeg 放入该表中。为了进行本练习，我不想存储对包含图像的目录的引用。

我是否错误地认为这就像填写下面第 2 块中的部分一样简单？

#include <iostream>
#include <string>
#include <mysql.h>

using namespace std;

int main(int argc, char **argv)
{

    //BLOCK 1: INIT
    MYSQL *connection, mysql;
    MYSQL_RES *result;
    MYSQL_ROW row;

    int query_state;

    mysql_init(&mysql);
    connection = mysql_real_connect(&mysql, "localhost", "root", "secret", "beginner_db",0,0,0);

    //BLOCK 2: SEND QUERY
    /* do something to insert image to table */

    //BLOCK 3: DISPLAY QUERY RESULTS
    result = mysql_store_result(connection);
    /* do something with result */

    //BLOCK 4: FREE
    mysql_free_result(result);
    mysql_close(connection);

    return 0;
}

Answer 1

对于这种情况，一个好的解决方案是使用 mysql_stmt_send_long_data() 函数。

我链接到的 MySQL 手册页面上有一个示例，但这里有一个更相关的发送文件内容的示例：

#ifdef _WIN32
#include <windows.h>
#endif

#include <cstddef>
#include <cstdio>
#include <cstdlib>
#include <cstring>
#include <iostream>

#include <boost/scope_exit.hpp>

#include <mysql.h>

#define ARR_LEN(arr_id) ((sizeof (arr_id))/(sizeof (arr_id)[0]))

int main()
{
    using namespace std;

    MYSQL *pconn = mysql_init(NULL);
    BOOST_SCOPE_EXIT( (pconn) ) {
        mysql_close(pconn);
    } BOOST_SCOPE_EXIT_END

    const char *db_name = "test";
    if (!mysql_real_connect(pconn, "localhost", "test", "********", db_name, 0, NULL, CLIENT_COMPRESS)) {
        cerr << "Error: mysql_real_connect() failed to connect to `" << db_name << "`." << endl;
        return EXIT_FAILURE;
    }

    MYSQL_STMT *pinsert_into_images_stmt = mysql_stmt_init(pconn);
    BOOST_SCOPE_EXIT( (pinsert_into_images_stmt) ) {
        mysql_stmt_close(pinsert_into_images_stmt);
    } BOOST_SCOPE_EXIT_END

    const char sql1[] = "INSERT INTO images(data) VALUES (?)";
    if (mysql_stmt_prepare(pinsert_into_images_stmt, sql1, strlen(sql1)) != 0) {
        cerr << "Error: mysql_stmt_prepare() failed to prepare `" << sql1 << "`." << endl;
        return EXIT_FAILURE;
    }

    MYSQL_BIND bind_structs[] = {
        { 0 } // One for each ?-placeholder
    };

    unsigned long length0;
    bind_structs[0].length = &length0;
    bind_structs[0].buffer_type = MYSQL_TYPE_BLOB;
    bind_structs[0].is_null_value = 0;

    if (mysql_stmt_bind_param(pinsert_into_images_stmt, bind_structs) != 0) {
        cerr << "Error: mysql_stmt_bind_param() failed." << endl;
        return EXIT_FAILURE;
    }

    const char *file_name = "image.jpg";
    FILE *fp = fopen(file_name, "rb");
    BOOST_SCOPE_EXIT( (fp) ) {
        fclose(fp);
    } BOOST_SCOPE_EXIT_END

    // Use mysql_stmt_send_long_data() to send the file data in chunks.
    char buf[10*1024];
    while (!ferror(fp) && !feof(fp)) {
        size_t res = fread(buf, 1, ARR_LEN(buf), fp);
        if (mysql_stmt_send_long_data(pinsert_into_images_stmt, 0, buf, res) != 0) {
            cerr << "Error: mysql_stmt_send_long_data() failed." << endl;
            return EXIT_FAILURE;
        }
    }

    if (!feof(fp)) {
        cerr << "Error: Failed to read `" << file_name << "` in its entirety." << endl;
        return EXIT_FAILURE;
    }

    if (mysql_stmt_execute(pinsert_into_images_stmt) != 0) {
        cerr << "Error: mysql_stmt_execute() failed." << endl;
        return EXIT_FAILURE;
    }

    cout << "Inserted record #" << mysql_insert_id(pconn) << endl;
    return EXIT_SUCCESS;
}

我正在使用表 `images` 的以下定义：

CREATE TABLE images (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    data MEDIUMBLOB NOT NULL,

    PRIMARY KEY (id)
);

运行此程序时，它成功地将 38,339 字节的 JPEG image.jpg 发送到服务器并输出“Inserted record #1”。

您可以验证发送的字节数是否正确：

mysql> SELECT octet_length(data) FROM images WHERE id=1;
+--------------------+
| octet_length(data) |
+--------------------+
|              38339 |
+--------------------+
1 row in set (0.00 sec)

Answer 2

我发现这个解决方案适用于 10kb 以下的图像。

//http://zetcode.com/tutorials/mysqlcapitutorial/
//g++ -o output source.cpp $(mysql_config --cflags) $(mysql_config --libs)
#include <stdio.h>
#include <iostream>
#include <mysql.h>

int main(int argc, char **argv)
{
  MYSQL *conn;

  int len, size;
  char data[1000*1024];
  char chunk[2*1000*1024+1];
  char query[1024*5000];

  FILE *fp;

  conn = mysql_init(NULL);
  mysql_real_connect(conn, "localhost", "root", "secret", "beginner_db", 0, NULL, 0);

  fp = fopen("filename.png", "rb");
  size = fread(data, 1, 1024*1000, fp);

  mysql_real_escape_string(conn, chunk, data, size);

  char *stat = "INSERT INTO pic_tbl(name, pic) VALUES('cexample', '%s')";
  len = snprintf(query, sizeof(stat)+sizeof(chunk) , stat, chunk);

  mysql_real_query(conn, query, len);

  fclose(fp);
  mysql_close(conn);
}

Answer 3

像这样的东西：

CString SaveFile( CMemoryFile& File )
{
    *pFileKey = -1;

        SQLRETURN       retcode;
        SQLHSTMT        hstmt;
        CLoggEntryList  LoggEntryList( this ); // logg entries cannot be made while busy inside the hstmt, use this class to add them later
        SQLINTEGER      cbDocumentBlock;        // For binding the main image

        long            lDocumentBufferSize = 0;
        unsigned char*  pDocumentBuffer;        // Will be set to point to the buffer that should be written into the document blob field
        unsigned char   pDummyChar[] = {'0'};   // Dummy buffer to write in the image/thumbnail blob fields when using external storage

                lDocumentBufferSize = File.m_lBufferSize;
                pDocumentBuffer = File.m_pFileBuffer;


        // Allocate statement handle
        retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_Database.m_hdbc, &hstmt); 

        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
        {   // Create a result set

            CString szSQL;

            szSQL = ( "INSERT INTO ObjectTable (ObjectData) VALUES (  ? )");

            retcode = SQLPrepare(hstmt, (SQLCHAR*)szSQL.GetBuffer(), SQL_NTS);

            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
            {
                // Bind the parameters. For parameter 1, pass the parameter number in ParameterValuePtr instead of a buffer address.

                SQLINTEGER cbNULL = 0;
                SQLINTEGER cbTEXT = SQL_NTS;
                int nColumn = 1;

                // Bind ObjectData
                cbDocumentBlock = SQL_LEN_DATA_AT_EXEC(0); //SQL_LEN_DATA_AT_EXEC(lImageBufferSize);                    
                retcode = SQLBindParameter(hstmt, nColumn++, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY,
                    0, 0, (SQLPOINTER) DOCUMENT, 0, &cbDocumentBlock);

                if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) 
                {
                    // Set values so data for parameter 1 will be passed at execution. Note that the length parameter in
                    // the macro SQL_LEN_DATA_AT_EXEC is 0. This assumes that the driver returns "N" for the 
                    // SQL_NEED_LONG_DATA_LEN information type in SQLGetInfo.

                    retcode = SQLExecute(hstmt);
                    const long      nMaxChunkSize = 400000;
                    // For data-at-execution parameters, call SQLParamData to get the parameter number set by SQLBindParameter.
                    // Call InitUserData. Call GetUserData and SQLPutData repeatedly to get and put all data for the parameter.
                    // Call SQLParamData to finish processing this parameter.

                    while (retcode == SQL_NEED_DATA) 
                    {
                        SQLPOINTER      pToken;
                        retcode = SQLParamData(hstmt, &pToken);

                        switch( (int)pToken )
                        {
                        case DOCUMENT:
                            {
                                if (retcode == SQL_NEED_DATA)
                                {
                                    for( int nPos = 0; nPos < lDocumentBufferSize; nPos += nMaxChunkSize )
                                    {
                                        int nBufferSize = min( lDocumentBufferSize - nPos, nMaxChunkSize );
                                        SQLRETURN retcode2  = SQLPutData(hstmt, pDocumentBuffer+nPos, nBufferSize );
                                        if (retcode2 != SQL_SUCCESS && retcode2 != SQL_SUCCESS_WITH_INFO) 
                                        {
                                            SQLCHAR         Sqlstate[6];
                                            SQLINTEGER      NativeError;
                                            SQLCHAR         MessageText[201];
                                            SQLSMALLINT      TextLengthPtr;

                                            retcode2 = SQLGetDiagRec( SQL_HANDLE_STMT, hstmt, 1, Sqlstate, &NativeError, MessageText, 200, &TextLengthPtr );
                                            if (retcode2 == SQL_SUCCESS || retcode2 == SQL_SUCCESS_WITH_INFO) 
                                            {
                                                MessageText[TextLengthPtr] = 0;
                                                Sqlstate[5] = 0;

                                                CString szSQLState( Sqlstate );
                                                CString szMessageText( MessageText );

                                                CString szMessage;
                                                szMessage.Format("Error in SaveFile(). SQL State %s. Native %ld. Source: %s", szSQLState, NativeError, szMessageText );
                                                SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
                                                return szMessage;
                                            }                                   
                                        }
                                    }
                                }
                                break;
                            }
                        default:
                            {
                                CString szMessage;
                                szMessage.Format("Error in SaveBuffer(). Unknown parameter buffer.");
                                SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
                                return szMessage;
                            }
                            break;
                        }
                    }
                }
                SQLRETURN retcode3;
                retcode3 = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
                ASSERT(retcode3 == SQL_SUCCESS);
            }
        }
}

此代码未经测试，甚至未编译，但它应该为您指明正确的方向。