ODBC API 使用SQLPutData实现SQL数据库BLOB(TEXT、IMAGE类型字段)数据提交[原创](二)

最新推荐文章于 2021-01-29 09:37:24 发布
最新推荐文章于 2021-01-29 09:37:24 发布
阅读量2.9k 收藏 4
点赞数
分类专栏： 编程技巧[原创] MS-SQL 文章标签： sql 数据库 image api buffer integer
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/XinChiMaker/article/details/5999381
版权

接上章节
void ProcessLogMessages(SQLSMALLINT plm_handle_type, SQLHANDLE plm_handle,
                        char *logstring)
{
        RETCODE                plm_retcode = SQL_SUCCESS;
        SQLTCHAR        plm_szSqlState[MAXBUFLEN] = TEXT(""),plm_szErrorMsg[MAXBUFLEN] = TEXT("");
        SDWORD                plm_pfNativeError = 0L;
        SWORD                plm_pcbErrorMsg = 0;
        SQLSMALLINT        plm_cRecNmbr = 1;

        printf(logstring);        

        while (plm_retcode != SQL_NO_DATA_FOUND)
        {
                plm_retcode = SQLGetDiagRec(plm_handle_type, plm_handle,
                        plm_cRecNmbr, plm_szSqlState, &plm_pfNativeError,
                        plm_szErrorMsg, MAXBUFLEN - 1, &plm_pcbErrorMsg);
                //if (plm_retcode != SQL_NO_DATA_FOUND)
                //{
                printf("szSqlState = %S/n",plm_szSqlState);
                printf("pfNativeError = %d/n",plm_pfNativeError);
                printf("szErrorMsg = ");
                HANDLE hConsole        = ::GetStdHandle(STD_OUTPUT_HANDLE);
                DWORD        dwWritten;
                ::WriteConsole(hConsole, plm_szErrorMsg, ::lstrlen(plm_szErrorMsg), &dwWritten, NULL);
                printf("/n");
                printf("pcbErrorMsg = %d/n/n",plm_pcbErrorMsg);
                //}
                plm_cRecNmbr++;
        } // end while
        getchar();
}


void Cleanup(SQLHSTMT hStmt)
{
        if (hStmt != SQL_NULL_HSTMT)
        {
                ::SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
        }

        if (g_hEnv != SQL_NULL_HENV)
        {
                ::SQLFreeHandle(SQL_HANDLE_ENV, g_hEnv);
        }
}

// -------------- SQL 语句查询 -------------------
BOOL ReadBlobData(SQLTCHAR *sqlSQLStr)
{
        RETCODE                sqlRetCode        = SQL_SUCCESS;
        SQLHSTMT        hStmt;
        SQLCHAR                bBinaryPtr[BUFFER_LENGTH]; //保存user_memo TEXT类型字段的值
        SQLINTEGER        iUserID,iDataLen, cbLenth; //保存user_id INTEGER字段的值


        // 申请SQL句柄
        sqlRetCode = ::SQLAllocHandle(SQL_HANDLE_STMT, g_hDbc, &hStmt);
        if (sqlRetCode == SQL_SUCCESS || sqlRetCode == SQL_SUCCESS_WITH_INFO) 
        {
                // -------------- SQL 语句查询 -------------------
                sqlRetCode = ::SQLExecDirect(hStmt, sqlSQLStr, SQL_NTS);
                if (sqlRetCode == SQL_SUCCESS || sqlRetCode == SQL_SUCCESS_WITH_INFO)
                {
                        SQLSMALLINT        colCount        = 0;
                        sqlRetCode        = ::SQLNumResultCols(hStmt, &colCount);
                        if (SQL_NO_DATA != sqlRetCode)
                        {
                                printf("Column Count is %d./n", (int)colCount);
                        }
                        else
                        {
                                printf("No Column in the table!/n");
                                goto ReadBlobData_Cleanup;
                        }

                        while (SQL_NO_DATA != ::SQLFetch(hStmt))
                        {

                                sqlRetCode        = ::SQLGetData(hStmt, 1, SQL_C_LONG, &iUserID, 15, &cbLenth);
                                if (sqlRetCode != SQL_SUCCESS && sqlRetCode != SQL_SUCCESS_WITH_INFO && sqlRetCode != SQL_NO_DATA_FOUND)
                                {
                                        ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLGetData() Failed!/n"); 
                                        goto ReadBlobData_Cleanup;
                                }


                                sqlRetCode        = ::SQLGetData(hStmt, 2, SQL_C_LONG, &iDataLen, 0, &cbLenth);
                                if (sqlRetCode != SQL_SUCCESS && sqlRetCode != SQL_SUCCESS_WITH_INFO && sqlRetCode != SQL_NO_DATA_FOUND)
                                {
                                        ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLGetData() Failed!/n"); 
                                        goto ReadBlobData_Cleanup;
                                }
                                printf("user_id = %d , data length = %d/n data = %s/n",iUserID,iDataLen);
                                while ((sqlRetCode = SQLGetData(hStmt, 3, SQL_C_CHAR, bBinaryPtr, BUFFER_LENGTH, &cbLenth)) != SQL_NO_DATA) 
                                {//多次调用SQLGetData 一直等到所有数据被取出

                                        int iNumBytes = (cbLenth > BUFFER_LENGTH)? BUFFER_LENGTH : cbLenth;
                                        //判断当前有多少数据被读出
                                        printf("get data length = %d , left data length = %d/n",iNumBytes,cbLenth);
                                        printf("data[]=%s/n", bBinaryPtr);

                                }
                        }
                }
                else
                {
                        ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLExecDirect() Failed!/n"); 
                        goto ReadBlobData_Cleanup;
                }
        }
        else
        {
                ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLExecDirect() Failed!/n"); 
                goto ReadBlobData_Cleanup;
        }
ReadBlobData_Cleanup:
        Cleanup(hStmt);
        return sqlRetCode;
}
BOOL PutDataToSrv(SQLHSTMT hStmt,DWORD dwCommitLength, LPBYTE lpData)
{
        SQLRETURN        sqlRetCode        = SQL_SUCCESS;
        DWORD        count        = 0;
        while(dwCommitLength > MAX_SND_BUF_LEN)
        {
                count ++;
                //for (int i = 0;i < MAX_SND_BUF_LEN;i ++)
                //{
                //        if (i > 0 && (i % 16) == 0)
                //                printf("/n");
                //        printf("0x%2X", (BYTE)lpData[i]);
                //}
                sqlRetCode = ::SQLPutData(hStmt, (SQLPOINTER)lpData, MAX_SND_BUF_LEN); //每次提供的数据量不同
                if (sqlRetCode == SQL_ERROR || sqlRetCode == SQL_SUCCESS_WITH_INFO)
                {
                        printf("SQLPutData RetCode = %d/n", sqlRetCode);
                        ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLPutData() Failed!/n"); 
                        return        sqlRetCode;
                }
                
                lpData        += MAX_SND_BUF_LEN;
                dwCommitLength        -= MAX_SND_BUF_LEN;
                printf ("PutData %d , length %ld, dwCommitLength %ld/n", count, count * MAX_SND_BUF_LEN, dwCommitLength);
                //SQLPOINTER pToken;
                //while(::SQLParamData(hStmt, &pToken) == SQL_STILL_EXECUTING)   Sleep(100);
        }
        sqlRetCode = ::SQLPutData(hStmt, lpData, dwCommitLength); //每次提供的数据量不同
        if (sqlRetCode == SQL_ERROR || sqlRetCode == SQL_SUCCESS_WITH_INFO)
        {
                printf("SQLPutData RetCode = %d/n", sqlRetCode);
                //ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLPutData() Failed!/n");
                return        sqlRetCode;
        }

        return        sqlRetCode;
}
BOOL InsertBlobData(SQLTCHAR *sqlSQLStr)
{
        SQLRETURN        sqlRetCode        = SQL_SUCCESS;
        SQLHSTMT        hStmt;
        LPBYTE                lpBinaryBuff        = NULL;
        LPBYTE                lpTextBuff        = NULL;
        DWORD                dwBinarySize        = 0;
        DWORD                dwTextSize        = MAX_BUFFER_LENGTH;
        DWORD                dwOldBSAddr,dwOldTSAddr;
        SQLPOINTER        pToken;
        SQLINTEGER        cbLength1, cbLength2, iRetCode, cb1;

        // 申请SQL句柄
        sqlRetCode = ::SQLAllocHandle(SQL_HANDLE_STMT, g_hDbc, &hStmt);
        if (sqlRetCode != SQL_SUCCESS && sqlRetCode != SQL_SUCCESS_WITH_INFO) 
        {
                ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLAllocHandle() Failed!/n"); 
                Cleanup(hStmt);
                return sqlRetCode;
        }

        sqlRetCode = ::SQLBindParameter(hStmt, 
                                        1,
                                        SQL_PARAM_OUTPUT, 
                                        SQL_C_LONG, 
                                        SQL_INTEGER, 
                                        sizeof(long), 
                                        0, 
                                        (SQLPOINTER)&iRetCode, 
                                        sizeof(long), 
                                        &cb1); 

        if (sqlRetCode != SQL_SUCCESS && sqlRetCode != SQL_SUCCESS_WITH_INFO)
        {
                ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLBindParameter() Failed!/n"); 
                Cleanup(hStmt);
                return sqlRetCode;
        }
        //cbLength1 = SQL_LEN_DATA_AT_EXEC(dwTextSize+1);//设置数据长度
        sqlRetCode = ::SQLBindParameter(hStmt, 
                                        2,
                                        SQL_PARAM_INPUT, 
                                        SQL_C_BINARY, 
                                        SQL_LONGVARCHAR, 
                                        dwTextSize, 
                                        0, 
                                        (SQLPOINTER)2, 
                                        0, 
                                        &cbLength1); 
        
        if (sqlRetCode != SQL_SUCCESS && sqlRetCode != SQL_SUCCESS_WITH_INFO)
        {
                ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLBindParameter() Failed!/n"); 
                Cleanup(hStmt);
                return sqlRetCode;
        }
        cbLength1 = SQL_LEN_DATA_AT_EXEC(0);//设置数据长度
        
        lpBinaryBuff        = (LPBYTE)MALLOC(MAX_BUFFER_LENGTH * 10);
        dwOldBSAddr        = (DWORD)lpBinaryBuff;        // 保存地址
        printf("lpBinaryBuff Addr:0x%08X/n", lpBinaryBuff);
        if (!ReadBinaryData(TEXT("E://Maker//Projects//MyTools//WinDLLSample//Release//Beauty.jpg"), lpBinaryBuff, dwBinarySize))
        {
                FREE(lpBinaryBuff);
                return        -1;
        }

        printf("dwBinarySize = %ld/n", dwBinarySize);
        
        cbLength2 = SQL_LEN_DATA_AT_EXEC(dwBinarySize+1);//设置数据长度
        sqlRetCode = ::SQLBindParameter(hStmt, 
                                        3,
                                        SQL_PARAM_INPUT, 
                                        SQL_C_BINARY, 
                                        SQL_LONGVARBINARY, 
                                        dwBinarySize, 
                                        0, 
                                        (SQLPOINTER)3, 
                                        0, 
                                        &cbLength2); 
        
        if (sqlRetCode != SQL_SUCCESS && sqlRetCode != SQL_SUCCESS_WITH_INFO)
        {
                ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLBindParameter() Failed!/n"); 
                Cleanup(hStmt);
                return sqlRetCode;
        }
        cbLength2 = SQL_LEN_DATA_AT_EXEC(0);//设置数据长度
        
        sqlRetCode = ::SQLExecDirect(hStmt, sqlSQLStr, SQL_NTS);
        printf("SQLExecDirect sqlRetCode = %d/n", sqlRetCode);
        if (sqlRetCode != SQL_SUCCESS && sqlRetCode != SQL_SUCCESS_WITH_INFO && sqlRetCode != SQL_NEED_DATA)
        {
                ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLExecDirect() Failed!/n"); 
                Cleanup(hStmt);
                return sqlRetCode;
        }

        sqlRetCode = ::SQLParamData(hStmt, &pToken); 
        if (sqlRetCode != SQL_SUCCESS && sqlRetCode != SQL_SUCCESS_WITH_INFO && sqlRetCode != SQL_NEED_DATA)
        {
                ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLParamData() Failed!/n"); 
                Cleanup(hStmt);
                return sqlRetCode;
        }
        
        lpTextBuff        = (LPBYTE)malloc(MAX_BUFFER_LENGTH);
        dwOldTSAddr        = (DWORD)lpTextBuff;        // 保存地址
        
        //生成测试数据
        for(int i = 0;i < MAX_BUFFER_LENGTH;i ++)
        {
                TCHAR        ch        = 65 + rand() % 27;
                lpTextBuff[i]        = ch;
        }
        //SQLCHAR        *ptr        = bBinaryPtr;
        DWORD        count        = 0;
        while (sqlRetCode == SQL_NEED_DATA) 
        { 
                
                if ((int)pToken == 2)
                {
                        if (PutDataToSrv(hStmt, MAX_BUFFER_LENGTH, lpTextBuff) != SQL_SUCCESS)
                        {
                                lpTextBuff        = (LPBYTE)dwOldTSAddr;
                                free(lpTextBuff);
                                Cleanup(hStmt);
                                return        -1;
                        }
                }
                else if ((int)pToken == 3)
                {
                        if (PutDataToSrv(hStmt, dwBinarySize, lpBinaryBuff) != SQL_SUCCESS)
                        {
                                lpBinaryBuff        = (LPBYTE)dwOldBSAddr;
                                free(lpTextBuff);
                                Cleanup(hStmt);
                                return        -1;
                        }
                        printf("lpBinaryBuff Addr:0x%08X/n", lpBinaryBuff);
                }
                sqlRetCode        = ::SQLParamData(hStmt, &pToken);
                //        
        } 
        lpTextBuff        = (LPBYTE)dwOldTSAddr;
        free(lpTextBuff);
        lpBinaryBuff        = (LPBYTE)dwOldBSAddr;
        FREE(lpBinaryBuff);
        
        sqlRetCode = ::SQLEndTran(SQL_HANDLE_DBC, g_hDbc, SQL_COMMIT);
        if (sqlRetCode != SQL_SUCCESS && sqlRetCode != SQL_SUCCESS_WITH_INFO)
        {
                ProcessLogMessages(SQL_HANDLE_STMT, hStmt, "SQLEndTran() Failed!/n"); 
                Cleanup(hStmt);
                return sqlRetCode;
        }
        
        printf("procedure return code is 0x%8X/n", iRetCode);
        return SQL_SUCCESS;
}


int main() 
{
        srand((int)::GetTickCount());

        if (SQLConnectToServer((SQLTCHAR*)SQL_CONNECT_STRING))
        {
                printf("成功连接上数据库 maker-notepc//tzyj_motortest_data!/n");
        }
        else
        {
                printf("无法连接至数据库maker-notepc//tzyj_motortest_data!/n");
                getchar();
                return        -1;
        }

        //if (InsertBlobData(TEXT("insert into test_blob(user_memo)values(?)")) != SQL_SUCCESS)
        //if (InsertBlobData(TEXT("{? = call test_blob_pInsert('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')}")) != SQL_SUCCESS)
        if (InsertBlobData(TEXT("{? = call test_blob_pInsert1(?,?)}")) != SQL_SUCCESS)
        {
                getchar();
                return -1;
        }

        //WYY_JIAOCHENG();
        printf("Success!/n");
        getchar();
        ::SQLDisconnect( g_hDbc ); 
        ::SQLFreeHandle( SQL_HANDLE_DBC, g_hDbc ); 
        ::SQLFreeHandle( SQL_HANDLE_ENV, g_hEnv );

        return        0;
}

熬夜程序猴
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
ODBC API 学习总结
bcbobo21cn的专栏
03-28 4298
ODBC 编程API http://blog.csdn.net/bichenggui/article/details/5601381 转的ODBC API函数详细说明。 看了一遍，没有问题。 使用 ODBC API 之前要用到的头文件和 LIB 库  #include "sql.h"              // This is the the main inc
C/C++ ODBC API
MHSMIE的专栏
09-12 1296
1. SQLAllocHandle 创建ODBC句柄     SQLRETURN SQLAllocHandle（          SQlSMALLINT HandleType,          SQLHANDLE  InputHandle,          SQLHANDLE* OutputHandlePtr     ）;      第一个参数HandleType的取值为：  
MFC使用ODBC图片写入blob字段
02-23
在mfc，使用ODBC将图片文件存入库表中的blob类型的字段，同时将blob字段读出为.jpg文件，可以参考本人博客《MFC使用ODBC把图片写入blob字段》
SQLPutData()—Send Data in Segments
snail8384的专栏
12-30 741
From: http://docs.actian.com/ingres/10s/connectivity-guide/2446-sqlputdatasend-data-in-segments SQLPutData() sends blob data in segments. The length of the data must be known in advance, and
ODBC API 使用SQLPutData实现SQL数据库BLOB(TEXT、IMAGE类型字段)数据提交[原创](一)
mAKER's Blog
11-10 3434
问题帖链接:http://topic.csdn.net/u/20100928/16/19d8bca9-15c1-4c88-b65b-bc397e81ce92.html?seed=1898436924&r=68769050#r_68769050 存储过程如下 create proc test_blob_pInsert ( @user_memo text, @user
直接通过ODBC API访问SQL数据库
shawls的专栏
01-15 1110
*********************************ODBC - Open DataBase Connectivity*********************************Basic StepsConnecting to the SQL Server DataBase for retrieving information from tables********
ODBC API开发教程
gumingyaotangwei 的专栏
10-30 5765
ODBC API开发教程 作者：闻怡洋 未得到作者允许请勿转载 http://wyy.vchelp.net/ 目录 第 1 章        介绍... 2 第 2 章        ODBC API访问数据库...     2 2.1       ODBC简要介绍...     2 2.1.1        在没有ODBC以前...     2 2.1.2    O
ntext在mysql_SQL数据库中数据类型ntext和text的区别
weixin_42347925的博客
01-28 2256
展开全部ntext和text一样用来保存大量的文字数62616964757a686964616fe78988e69d8331333361306364据，不过text用单字节保存数据 ，ntext固定用双字节保存数据. ntext保存的是Uncode的字符 ，ntext支持跨语言平台。ntext：可变长度 Unicode 数据的最大长度为 230 -1 (1,073,741,823) 个字符。存储大...
ODBC_API开发教程
07-05
### ODBC_API开发教程知识点详解 ...此外，本文还列举了一些常见的API函数，并通过示例代码展示了如何使用这些函数来实现数据库连接、执行SQL语句等功能。希望本教程能为读者提供有价值的指导和帮助。
浅析 开放数据库互连(ODBC)
qq_38791687的博客
03-15 835
开放数据库连接（Open Database Connectivity，ODBC）是为解决异构数据库间的数据共享而产生的，现已成为WOSA(The Windows Open System Arhitecture,Windows )开放系统体系结构)的主要部分和基于Windows环境的一种数据库访问接口标准ODBC 为异构数据库访问提供统一接口，允许应用程序以SQL 为数据存取标准，存取不同DBMS管...
ODBC: SQL to C
duanbeibei的专栏
02-15 1040
Data conversion tables from SQL to C The tables in the following sections describe how the driver or data source converts data retrieved from the data source; drivers are required to support conver
SQLDescribeCol和SQLGetData
chaohaijie的专栏
05-25 6050
<br />这几天做一个东西，要用到ODBC API的一些东西，遇到几个问题，说出来，大家一起注意一下<br />1，SQLDescribeCol与smalldatetime<br />     SQLDescribeCol函数返回列的信息，其中包括列的大小，我在项目中，对于常用的类型，都用相应的数据类型来取数据，对于非常用的类型，就根据这个函数返回的列的大小，来申请一这个大小的空间，然后绑定接收数据，这个方法对于许多类型都是可以用的，但对于smalldatatime却出了意外。<br />    smal
mybatis如何操作text类型(mysql)数据呢？
热门推荐
happyrabbit456的专栏
09-23 2万+
Mybatis在预处理语句中设置一个参数，或者从结果集中获取一个值时，会使用类型处理器typeHandler将获取的值以合适的方式转换成Java类型。数据库中的基本类型之所以能被转化成JAVA类型，是因为Mybatis已经内置了这些类型的处理器， 另外。Mybatis同事提供了类型处理器的扩展功能，程序可以自定义类型处理器，或者替换内置的类型处理器，只需集成TypeHandler借口即可，然
Apache NiFi 入门指南
张伯毅的专栏
03-19 1万+
本指南使用于谁？ 本指南适用于从未使用过，在NiFi中有限度接触或仅完成特定任务的用户。本指南不是详尽的说明手册或参考指南。“用户指南”提供了大量信息，旨在提供更加详尽的资源，并且作为参考指南非常有用。相比之下，本指南旨在为用户提供所需的信息，以便了解如何使用NiFi，以便快速轻松地构建强大而灵活的数据流。 一些因为本指南中的某些信息仅适用于初次使用的用户，而其他信息可能适用于那些使用过Ni...
Nifi 常用Processor
Tine Aine
01-29 1100
ExecuteScript : 执行脚本处理器, 支持: clojure, ecmascript, groovy, lua, python, ruby QueryDatabaseTable : 数据库查询处理器, 支持: mysql ConvertAvroToJSON : avro 数据格式转换为 json SplitJson : 将JSON文件拆分为多个单独的FlowFiles, 用于由JsonPath表达式指定的数组元素。 EvaluateJsonPath : 根据FlowFile的内容评估一
SQL常用函数（持续更新中）
m0_48812571的博客
11-24 145
(一) 单行函数 1、字符函数 ------------length 查询参数值的字节数 select length("alice"); select length("函数"); ------------concat 拼接字符串 select concat (last_name,first_name) as "姓名" from employees; -------------upper/lower 变大写/变小写 select upper ("joy"); select lower ("TO.
SqlServer数据库长文本和二进制字段操作总结（54powerman原创）
记事本
01-23 7758
SqlServer数据库长文本和二进制字段操作总结（54powerman原创）--创建测试用表Create Table TestnText (RowID Int Identity(1,1) Primary Key,Content nText)--读写nText文本,一定要用在事物中Begin TranDECLARE @ptrval varbinary(16)Select @
控制DataGridView的滚动条位置
mAKER's Blog
03-12 1万+
使用DataGridView的FirstDisplayedScrollingRowIndex属性可以控制垂直滚动条位置.重载OnRowsAdded事件,在事件中添加如下代码(当然也可以不用重载,直接在RowsAdded事件中使用): public partial class RowMergeView : DataGridView {         protected override OnRowsAdded(DataGridViewRowsAddedEventArgs e)    
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值