8 Replies - 1144 Views - Last Post: 10 October 2012 - 03:41 AM Rate Topic: -----

#1 Jonand  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 26-September 12

odbc blob insert problem c++

Posted 04 October 2012 - 02:27 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: odbc blob insert problem c++

#2 Aphex19  Icon User is offline

  • Born again Pastafarian.
  • member icon

Reputation: 614
  • View blog
  • Posts: 1,873
  • Joined: 02-August 09

Re: odbc blob insert problem c++

Posted 04 October 2012 - 02:32 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 Jonand  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 26-September 12

Re: odbc blob insert problem c++

Posted 04 October 2012 - 03:03 AM

View PostAphex19, 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.
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3168
  • View blog
  • Posts: 9,578
  • Joined: 05-May 12

Re: odbc blob insert problem c++

Posted 04 October 2012 - 07:44 AM

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.
Was This Post Helpful? 0
  • +
  • -

#5 #define  Icon User is offline

  • Duke of Err
  • member icon

Reputation: 1276
  • View blog
  • Posts: 4,396
  • Joined: 19-February 09

Re: odbc blob insert problem c++

Posted 05 October 2012 - 01:12 PM

Is szSqlStat2 the INSERT statement?
Was This Post Helpful? 0
  • +
  • -

#6 Jonand  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 26-September 12

Re: odbc blob insert problem c++

Posted 08 October 2012 - 04:53 AM

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.
Was This Post Helpful? 0
  • +
  • -

#7 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3168
  • View blog
  • Posts: 9,578
  • Joined: 05-May 12

Re: odbc blob insert problem c++

Posted 08 October 2012 - 07:44 AM

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?
Was This Post Helpful? 0
  • +
  • -

#8 Jonand  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 26-September 12

Re: odbc blob insert problem c++

Posted 10 October 2012 - 02:09 AM

View PostSkydiver, 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.

Was This Post Helpful? 0
  • +
  • -

#9 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3168
  • View blog
  • Posts: 9,578
  • Joined: 05-May 12

Re: odbc blob insert problem c++

Posted 10 October 2012 - 03:41 AM

If it is szSqlStat2, then you are doing the SQLBindParameter() call too late. Do the binding before executing the SQL statement.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1