[Jonand]

0 down vote favorite


I just finished my code for inserting a blob in a oracle database.But for some while i am stoked at this problem. In order to insert a blob ,first i insert an empty blob then i update it whit the data.The insert is working but the update is not inserting the data . Also it gives no error ,and the functions returns are all as should be .

the code is :

try{
    CString strSqlStat(szSqlStat);
    CString strSqlStat2(szSqlStat2);

    if(IsConnectionDead())
    {
        if(!Reconnect())
            return ERR_RECONNECT_FAILED;
    }

    CFileException exFile;
    CFile sourceFile;
    if(!sourceFile.Open(szFilePath, CFile::modeRead | CFile::shareDenyNone, &exFile))
        return ERR_BLOB_READFILE;

    int nrBytesToRead = (int)sourceFile.GetLength();
    char* pData = new char[nrBytesToRead+1];

    DWORD nrBytesRead;

    if(!ReadFile((HANDLE)sourceFile.m_hFile,pData,nrBytesToRead, &nrBytesRead, NULL))
    {
        delete pData;
        return ERR_BLOB_READFILE;
    }

    sourceFile.Close();

    if(nrBytesRead == 0)//file empty
    {
        delete pData;
        return 0;
    }

    //variables
    SQLRETURN retCode; 
    SDWORD   cbTextSize, lbytes;
    lbytes = (SDWORD)nrBytesRead;
    cbTextSize = SQL_LEN_DATA_AT_EXEC(lbytes);
    PTR pParmID;
    SDWORD cbBatch = nrBytesRead;
    int rgbValue = 1;

    retcode = retCode = SQLExecDirect(hstmt,(SQLTCHAR*)szSqlStat2, SQL_NTS);
    SQLRETURN ret;
        SQLCHAR* SQLState;
        SQLINTEGER NativeError;
        SQLSMALLINT errmsglen;
        SQLCHAR errmsg[255];
        SQLCHAR errstate[50];
        SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, (SQLCHAR*)errstate, &NativeError, (SQLCHAR*)errmsg, sizeof(errmsg), &errmsglen);


    // Bind the parameter marker.
    retCode = retcode = SQLBindParameter(hstmt,  // hstmt
         1,                     // ipar
         SQL_PARAM_INPUT,            // fParamType
         SQL_C_BINARY,               // fCType
         SQL_LONGVARBINARY,           // FSqlType
         lbytes,                  // cbColDef
         0,                     // ibScale
         &rgbValue,       // rgbValue
         0,                     // cbValueMax
         &cbTextSize);            // pcbValue

    SqlError(hstmt,SQL_HANDLE_STMT,_T("WriteBlob"), _T("CTLSqlConnection"), _T("SQLBindParameter"));
    if(retCode != SQL_SUCCESS)
    {
        delete pData;

        if(!EndTransaction(FALSE))
            return ERR_ENDTRANSACTION_FAILED;
        else
            return -3;
    }

    //SQLExec
    //SELECT Id FROM RH_Report WHERE Name = N'strName' strWhereLevel

    retcode = retCode = SQLExecDirect(hstmt,(SQLTCHAR*)szSqlStat, SQL_NTS);

        /*SQLRETURN ret;
        SQLCHAR* SQLState;
        SQLINTEGER NativeError;
        SQLSMALLINT errmsglen;
        SQLCHAR errmsg[255];
        SQLCHAR errstate[50];*/
        SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, (SQLCHAR*)errstate, &NativeError, (SQLCHAR*)errmsg, sizeof(errmsg), &errmsglen);


   // retCode = SQLParamData(hstmt, &pParmID);
    SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, (SQLCHAR*)errstate, &NativeError, (SQLCHAR*)errmsg, sizeof(errmsg), &errmsglen);


    if(retCode == SQL_NEED_DATA)
    {
        // Put final batch.
        SQLPutData(hstmt, pData, lbytes); 
        SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, (SQLCHAR*)errstate, &NativeError, (SQLCHAR*)errmsg, sizeof(errmsg), &errmsglen);
    }
    else
    {
        delete pData;

        SqlError(hstmt,SQL_HANDLE_STMT,_T("WriteBlob"), _T("CTLSqlConnection"), _T("SQLExecDirect or SQLParamData"));
        if(!EndTransaction(FALSE))
            return ERR_ENDTRANSACTION_FAILED;
        else
            return -4;
    }

    delete pData;

    // Make final SQLParamData call.
    //retcode = retCode = SQLParamData(hstmt, &pParmID);
    SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, (SQLCHAR*)errstate, &NativeError, (SQLCHAR*)errmsg, sizeof(errmsg), &errmsglen);
    if(SqlError(hstmt,SQL_HANDLE_STMT,_T("WriteBlob"), _T("CTLSqlConnection"), _T("The last SQLParamData")))
        if(!EndTransaction(FALSE))
            return ERR_ENDTRANSACTION_FAILED;
        else
            return -5;

    retcode = SQLCloseCursor(hstmt);

    if(!EndTransaction(TRUE))
        return ERR_ENDTRANSACTION_FAILED;

}catch(...){
    return -10;
}
  return 0; 

I can't figure out what the problem could be .The statements look like :

"UPDATE "+ table_name +" SET "+ column_name +" = ? WHERE filename ='insert'")

and the insert statement:

INSERT INTO "+ table_name +" VALUES ('insert',EMPTY_BLOB())")

I hope you can help me . I am a c++ beginner.Waiting for your replys . Thanks.

[Aphex19]

Which functions aren't returning what you expect and what do you expect them to return? Try to trace the problem(s) to the most specific place(s) you can.

edit:
my mistake, misread your post.

This post has been edited by Aphex19: 04 October 2012 - 03:29 AM

[Jonand]

Aphex19, on 04 October 2012 - 02:32 AM, said:

Which functions aren't returning what you expect and what do you expect them to return? Try to trace the problem(s) to the most specific place(s) you can.

Hello,

The functions are returning as they should return .There is not problem there ,only that the blob data is not inserted in the table. I believe the statements are ok ,so i am thinking it could be an logic error or something, and i missed .

Thanks for your reply.

[Skydiver]

This looks suspicious:

    // Bind the parameter marker.
    retCode = retcode = SQLBindParameter(hstmt,  // hstmt
         1,                     // ipar
         SQL_PARAM_INPUT,            // fParamType
         SQL_C_BINARY,               // fCType
         SQL_LONGVARBINARY,           // FSqlType
         lbytes,                  // cbColDef
         0,                     // ibScale
         &rgbValue,       // rgbValue
         0,                     // cbValueMax
         &cbTextSize);            // pcbValue

Why are you passing a pointer to rgbValue? Don't you want to be passing your pData? Why are you passing 0 for the cbValueMax? Don't you want to pass the size of your data lBytes?

I only say suspicious and not definitely incorrect, because there is not enough information in the question you posted. What is the value of the string stored in szSqlStat2 and szSqlStat?

Additionally, aren't you supposed to bind parameters before you execute them? Yet the comments hint that szSqlStat is a SELECT statement. But why would the SELECT statement be the one handling the SQL_NEED_DATA?

If szSqlStat is really a SELECT statement, then binding should have happened before executing szSqlStat2, and the handling for SQL_NEED_DATA should also be directly after that as well.

[#define]

Is szSqlStat2 the INSERT statement?

[Jonand]

hello ,

Thank for your reply ,i'm trying to use SQLBindingParameter for binding an blob ,my table has only two columns ,an varchar type and an BLOB type .I believe that the problem is from my bindingparam function but cant figure it out .
I haven't worked with blob type before so it is abstract to me. The date is stored in an char array ,pData.The two statements that i use are :

UPDATE "+ table_name +" SET "+ column_name +" = ? WHERE filename ='insert'")
INSERT INTO "+ table_name +" VALUES ('insert',EMPTY_BLOB())")

the insert for inserting an empty blob ,and the update to update the inserted blob with the data.
Is this not correct ?

Thaks ,looking forward for your rely's.

[Skydiver]

We know that you have two SQL statements. You already said that previously. You didn't answer our questions in the previous posts about which variable is holding which statement.

Let me try rephrasing the question: Where is INSERT INTO "+ table_name +" VALUES ('insert',EMPTY_BLOB())") stored? Is it it szSqlStat or in szSqlStat2?

[Jonand]

Skydiver, on 08 October 2012 - 07:44 AM, said:

We know that you have two SQL statements. You already said that previously. You didn't answer our questions in the previous posts about which variable is holding which statement.

Let me try rephrasing the question: Where is INSERT INTO "+ table_name +" VALUES ('insert',EMPTY_BLOB())") stored? Is it it szSqlStat or in szSqlStat2?

My bad ,i misunderstand your question.

 INSERT INTO "+ table_name +" VALUES ('insert',EMPTY_BLOB()) 

it is stored in szSqlStat2 .

Thanks.

This post has been edited by Skydiver: 10 October 2012 - 03:39 AM
Reason for edit:: Fixed code tags. Ending tag should use forward slash.

[Skydiver]

If it is szSqlStat2, then you are doing the SQLBindParameter() call too late. Do the binding before executing the SQL statement.