接上章节
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;
}