Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C++ Oracle insert Blob
Hello,
 
I am trying to insert an blob into a database . For this I am using c++ and microsoft ODBC driver.
 
The app works fine with files that have less than 4000 bytes. When i am trying to insert a file that has more than 4000 bytes , meaning 3.9 KB it returns : ORA-01460: unimplemented or unreasonable conversion requested .
 
The database is oracle 11.2.0 . I have searched the internat for a solution but find none .
 
How could i solve this problem ? What can i do ?
 
The code for my insert function is :
 
 
try{
		CString strSqlStat(szSqlStat);
 
		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;
 
		// 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		
		retcode = retCode = SQLExecDirect(hstmt,(SQLTCHAR*)szSqlStat, SQL_NTS);
		retcode = retCode = SQLParamData(hstmt, &pParmID);
			SQLRETURN ret; //ADI fix all warnings - including this var that is unreferenced - delete it if you don't use it
			SQLCHAR* SQLState; //ADI same here
			SQLINTEGER NativeError;
			SQLSMALLINT errmsglen;
			SQLWCHAR errmsg[255];
			SQLWCHAR errstate[50];
			
	
	SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, (SQLWCHAR*)errstate, &NativeError, (SQLWCHAR*)errmsg, sizeof(errmsg), &errmsglen);
	
	
		if(retCode == SQL_NEED_DATA)
		{
			// Put final batch.
			SQLPutData(hstmt, pData, lbytes); 
		}
		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);
		/*SQLRETURN ret; //ADI fix all warnings - including this var that is unreferenced - delete it if you don't use it
			SQLCHAR* SQLState; //ADI same here
			SQLINTEGER NativeError;
			SQLSMALLINT errmsglen;
			SQLWCHAR errmsg[255];
			SQLWCHAR errstate[50];
		*/	
	
	SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, (SQLWCHAR*)errstate, &NativeError, (SQLWCHAR*)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(...){
		WRITEERRORINLOGFILE(_T("Error: An exception has been caught in WriteBlob."));
		return -10;
	}
 
   return 0;
 
}
 
I could not find any explanation why it is not not working for large files.
 
How could I solve the problem ?
Tanks for your help.
Posted 14-Apr-13 22:40pm
Edited 14-Apr-13 22:46pm
Malli_S25.6K
v2
Comments
Matthew Faithfull at 15-Apr-13 4:45am
   
This doesn't sound like a problem with your code. More like an issue with the Oracle database. Have you tried Oracle support?
The_Inventor at 16-Apr-13 4:03am
   
There is a limit switch within the database files, and the engine handling the database. This is often the case with older machines. Do a search with your help files for MAX_FILESIZE or MAX_SQLSIZE. Also it could be the type limitations as well for SQL_C_BINARY, SDWORD,SQL_LONGVARBINARY. Often there is an initial size, 4KB for instance.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

After investigating and reflecting into the code I found that by changing the Direction of the Parameter to input output - the problem was resolved.
 
p.Direction = ParameterDirection.InputOutput;
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Marcin Kozub 275
1 OriginalGriff 264
2 Sergey Alexandrovich Kryukov 215
3 Praneet Nadkar 197
4 Richard MacCutchan 182
0 OriginalGriff 8,048
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 11 Nov 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100