ODBC API SQLPutData使用疑问

熬夜程序猴 2010-09-28 04:15:03

示例数据库表如下数据库为SQL2000
USE [tzyj_motortest_data]
GO

if exists(select * from sysobjects where [name] = N'test_blob')
drop table test_blob
GO

create table test_blob
(
user_id int identity(1, 1) not null,
user_memo text
)
GO

if exists (select * from sysobjects where [name] = N'test_blob_pQuery')
drop proc test_blob_pQuery
GO

create proc test_blob_pQuery

AS
begin
set nocount on
-- select user_id, DataLength(user_memo) as Length, user_memo from test_blob where [user_id] = @user_id
select user_id, DataLength(user_memo) as Length, user_memo from test_blob
end
GO

if exists (select * from sysobjects where [name] = N'test_blob_pInsert')
drop proc test_blob_pInsert
GO

create proc test_blob_pInsert
(
@user_memo text
)
AS
begin
set nocount on
-- declare @ptrval binary(16)
-- select @ptrval = textptr(user_memo)

insert into test_blob
values (@user_memo)

return @@ROWCOUNT
end
GO

if exists (select * from sysobjects where [name] = N'test_blob_pUpdate')
drop proc test_blob_pUpdate
GO

create proc test_blob_pUpdate
(
@user_id int,
@user_memo text
)
AS
begin
set nocount on
update tb
set tb.user_memo = @user_memo
from test_blob AS tb
where user_id = @user_id
end
GO

if exists (select * from sysobjects where name = N'test_blob_pDelete')
drop proc test_blob_pDelete
GO

create proc test_blob_pDelete
(
@user_id int
)
AS
begin
set nocount on
delete from test_blob where user_id = @user_id
end
GO

运行上面脚本后生成数据表及存储过程.
VC6 里使用ODBC API 插入数据出现问题,
在 SQL2000的查询分析器里使用select * from test_blob 查看数据没有列没有插入
insert into test_blob(user_memo)values('sdfsdfsdffffffffffffffffffffffffffffffffffffffffffffff')
select * from test_blob 后
发现user_id发生了变化出现了递增但是就是没有数据

具体代码如下:
为简化去掉了容错处理的代码

BOOL InsertBlobData(SQLTCHAR *sqlSQLStr)
{
SQLRETURN sqlRetCode = SQL_SUCCESS;
SQLHSTMT hStmt;
SQLCHAR bBinaryPtr[MAX_BUFFER_LENGTH]; //保存user_memo TEXT类型字段的值
LPBYTE lpBinaryBuff = NULL;
DWORD dwSize;
SQLPOINTER pToken;
SQLINTEGER cbLenth, iRetCode, cb1;

// 申请SQL句柄
sqlRetCode = ::SQLAllocHandle(SQL_HANDLE_STMT, g_hDbc, &hStmt);

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

//cbLenth = SQL_LEN_DATA_AT_EXEC(MAX_BUFFER_LENGTH);//设置数据长度
sqlRetCode = ::SQLBindParameter(hStmt,
2,
SQL_PARAM_INPUT,
SQL_C_BINARY,
SQL_LONGVARCHAR,
400000,
0,
(SQLPOINTER)bBinaryPtr,
0,
&cbLenth);

SQLHDESC hIpd;
SQLGetStmtAttr(hStmt,SQL_ATTR_IMP_PARAM_DESC,&hIpd,0,0);
SQLSetDescField(hIpd,2,SQL_DESC_NAME,"@user_memo",SQL_NTS);
cbLenth = SQL_LEN_DATA_AT_EXEC(0);//设置数据长度
//sqlRetCode = ::SQLExecute(hStmt);
sqlRetCode = ::SQLExecDirect(hStmt, sqlSQLStr, SQL_NTS);

sqlRetCode = ::SQLParamData(hStmt, &pToken);

//生成测试数据
memset(bBinaryPtr,'A'+rand() % 25, MAX_BUFFER_LENGTH);
DWORD dwCommitLength = MAX_BUFFER_LENGTH;
SQLCHAR *ptr = bBinaryPtr;
DWORD count = 0;
while (sqlRetCode == SQL_NEED_DATA)
{
while(dwCommitLength > MAX_SND_BUF_LEN)
{
count ++;
sqlRetCode = ::SQLPutData(hStmt, ptr, MAX_SND_BUF_LEN); //每次提供的数据量不同
ptr += MAX_SND_BUF_LEN;
dwCommitLength -= MAX_SND_BUF_LEN;
printf ("PutData %d , length %ld\n", count, count * MAX_SND_BUF_LEN);
}
sqlRetCode = ::SQLPutData(hStmt, ptr, dwCommitLength); //每次提供的数据量不同

while(::SQLParamData(hStmt, &pToken) == SQL_STILL_EXECUTING) Sleep(100);
printf("0x%08X 0x%08X %d\n", pToken, &bBinaryPtr, sqlRetCode);
}
sqlRetCode = ::SQLParamData(hStmt, &pToken); //本次数据传送完成
printf("procedure return code is %d\n", iRetCode);
return SQL_SUCCESS;
}
调用语句如下
SQLTCHAR sqlszSQL=TEXT('{?=call test_blob_pInsert(?)}');
InsertBlobData(sqlszSQL);
以上代码运行之后没有出现任何的错误,本人尝试了无数的网上的\MSDN的\SQL在线文档的等所有代码均是无法实现
郁闷.......................................................

...全文

83 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

熬夜程序猴 2010-09-30

打赏
举报

{?=call test_blob_pInsert(?)} 是ODBC API 执行存储过程的语法
问题要解决链接
http://topic.csdn.net/u/20100928/16/19d8bca9-15c1-4c88-b65b-bc397e81ce92.html?seed=1898436924&r=68769050#r_68769050

starseeker7 2010-09-30

打赏
举报

學習了－　－

claro 2010-09-29

打赏
举报

帮顶。

不了解。

熬夜程序猴 2010-09-29

打赏
举报

今天继续顶贴不要沉啊

starseeker7 2010-09-29

打赏
举报

C語言看得我腦殼暈
首先你用
insert into test_blob(user_memo)values('sdfsdfsdffffffffffffffffffffffffffffffffffffffffffffff')
直接run到底是否能夠正常插入數據行？
然後
你還是放C語言區問吧。。。
實在不懂- -這裡懂C調用API的還是少吧--

SQLTCHAR sqlszSQL=TEXT('{?=call test_blob_pInsert(?)}');
則這個語句到底是什麽個意思啊
test_blob_pInsert不是需要參數的嗎？參數呢？
SQL中運行存儲過程是exec 阿為啥是call - -

熬夜程序猴 2010-09-29