示例数据库表如下 数据库为SQL2000
USE [tzyj_motortest_data]
GOif exists(select * from sysobjects where [name] = N'test_blob')
drop table test_blob
GOcreate table test_blob
(
user_id int identity(1, 1) not null,
user_memo text
)
GOif exists (select * from sysobjects where [name] = N'test_blob_pQuery')
drop proc test_blob_pQuery
GOcreate proc test_blob_pQueryAS
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
GOif exists (select * from sysobjects where [name] = N'test_blob_pInsert')
drop proc test_blob_pInsert
GOcreate 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
GOcreate 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
GOif exists (select * from sysobjects where name = N'test_blob_pDelete')
drop proc test_blob_pDelete
GOcreate 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在线文档的等所有代码均是无法实现
郁闷.......................................................
USE [tzyj_motortest_data]
GOif exists(select * from sysobjects where [name] = N'test_blob')
drop table test_blob
GOcreate table test_blob
(
user_id int identity(1, 1) not null,
user_memo text
)
GOif exists (select * from sysobjects where [name] = N'test_blob_pQuery')
drop proc test_blob_pQuery
GOcreate proc test_blob_pQueryAS
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
GOif exists (select * from sysobjects where [name] = N'test_blob_pInsert')
drop proc test_blob_pInsert
GOcreate 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
GOcreate 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
GOif exists (select * from sysobjects where name = N'test_blob_pDelete')
drop proc test_blob_pDelete
GOcreate 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在线文档的等所有代码均是无法实现
郁闷.......................................................
首先你用
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 - -
问题要解决 链接
http://topic.csdn.net/u/20100928/16/19d8bca9-15c1-4c88-b65b-bc397e81ce92.html?seed=1898436924&r=68769050#r_68769050