Blobs füllen und auslesen (Oracle DB / C++)

[LordTerra]

moin

ich hab da nen problem ...
und zwar will ich eine tabelle füllen in dem ein feld ein blob ist ...
in dem blob soll eine datei abgespeichert werden die vorher im binärformat abgespeichert wurde ...

heist:

    CString name="test1.cfg";
    char *buf;
    CFile f;
    char* pFileName=name.GetBuffer(name.GetLength());
    if(f.Open(pFileName,CFile::modeRead | CFile::typeBinary)) {
        int flen=f.GetLength();
        buf = new char[flen];
        f.Read(buf, flen);
        f.Close();
    }
    name.ReleaseBuffer();

ich möchte nun *buf in meinem blob-feld ablegen...

ich hab eine tabelle die wie folgt aussieht:

tabellenname: sysconfig
spalten:
id num[4]
sgid vc2[255]
name vc2[255]
data blob

wie muss mein quellcode aussehn damit ich nun diesen buffer in mein blob rein bekomm???

vielen dank für eure hilfe
LT

[kuemmelchen]

Vielleicht hilft dir dieses Beispiel etwas weiter:
Die Spalte stammbaum ist vom Datentyp BLOB.

DECLARE
	positionszeiger		BFILE;
	temp_bild			BLOB;
	byteanzahl		BINARY_INTEGER;
	buffer			RAW (32767);
BEGIN
	positionszeiger := BFILENAME ('PASSBILDER', 'stammbaum.jpg');
	IF DBMS_LOB.FILEEXISTS (positionszeiger) = 1
		THEN	DBMS_LOB.FILEOPEN (positionszeiger);
			DBMS_LOB.CREATETEMPORARY (temp_bild, TRUE);
			byteanzahl := DBMS_LOB.GETLENGTH (positionszeiger);

			DBMS_LOB.READ (positionszeiger, byteanzahl, 1, buffer);

			UPDATE t_person
				SET stammbaum = buffer
				WHERE name = 'Tom';

			DBMS_LOB.FILECLOSE (positionszeiger);
	END IF;
END;
/

kuemmelchen

[Christian Marquardt]

wie muss mein quellcode aussehn damit ich nun diesen buffer in mein blob rein bekomm???

Wäre erstmal zu klären mit welcher C/C++ API du auf Oracle zugreifst?

[LordTerra]

hmmm also ich benutze visual c++6.0
ich weis die ist veraltet aber is nun mal so

also meine bisherigen inserts / selects sehen wie folgt aus :

connect:

BOOL DB::DBConnectNow(){
	CoInitialize (NULL);
	DBConOK=FALSE;
	// Stablishing a connection to the datasource
	try	{
		HRESULT hr = m_pConn.CreateInstance (__uuidof (Connection));

		if (FAILED (hr)){
			return FALSE;
		}
		
		if (FAILED (m_pConn->Open (_bstr_t ("Provider=MSDASQL.1;Persist Security Info=False;User ID=system;Data Source=ArgusDB"), _bstr_t (""), _bstr_t (""), adModeUnknown))){
			return FALSE;
		}
	}catch ( _com_error &e ){
		_bstr_t bstrSource (e.Source());
		_bstr_t bstrDescription (e.Description());
		TRACE ( "Exception thrown for classes generated by #import" );
		TRACE ( "\tCode = %08lx\n", e.Error ());
		TRACE ( "\tCode meaning = %s\n", e.ErrorMessage ());
		TRACE ( "\tSource = %s\n", (LPCTSTR) bstrSource);
		TRACE ( "\tDescription = %s\n", (LPCTSTR) bstrDescription);

		return FALSE;
	}catch (...){
		TRACE ( "*** Unhandled Exception ***" );
		return FALSE;
	}
	DBConOK=TRUE;
	return TRUE;		
}

insert:

BOOL DB::DBInsertNow(CString aktion){
	try	{
		_bstr_t strSQL=(_bstr_t) aktion;
       //Execute the insert statement
		m_pConn->Execute(strSQL,NULL,adExecuteNoRecords);
	} catch( _com_error &e )	{
		_bstr_t bstrSource(e.Source());
		_bstr_t bstrDescription(e.Description());
		TRACE( "Exception thrown for classes generated by #import" );
		TRACE( "\tCode = %08lx\n", e.Error());
		TRACE( "\tCode meaning = %s\n", e.ErrorMessage());
		TRACE( "\tSource = %s\n", (LPCTSTR) bstrSource);
		TRACE( "\tDescription = %s\n", (LPCTSTR) bstrDescription);

		return FALSE;		
	}catch (...){
		TRACE ( "*** Unhandled Exception ***" );

		return FALSE;		
	}

	return TRUE;		
}

select:

_RecordsetPtr DB::DBSelectNow(CString aktion){
	_RecordsetPtr pRecordset;
	try	{
		_CommandPtr pCommand;

		HRESULT hr = pCommand.CreateInstance (__uuidof (Command));

		if (FAILED (hr)){
			printf("Can't create an instance of Command");
		}

		pCommand->ActiveConnection = m_pConn;
		pCommand->CommandText = (_bstr_t) aktion;


		hr = pRecordset.CreateInstance (__uuidof (Recordset));

		if (FAILED (hr)){
			printf ("Can't create an instance of Recordset");
		}

		pRecordset->CursorLocation = adUseClient;
		pRecordset->Open ((IDispatch *) pCommand, vtMissing, adOpenForwardOnly,
							adLockReadOnly, adCmdUnknown);

	} catch( _com_error &e )	{
		_bstr_t bstrSource(e.Source());
		_bstr_t bstrDescription(e.Description());
		TRACE( "Exception thrown for classes generated by #import" );
		TRACE( "\tCode = %08lx\n", e.Error());
		TRACE( "\tCode meaning = %s\n", e.ErrorMessage());
		TRACE( "\tSource = %s\n", (LPCTSTR) bstrSource);
		TRACE( "\tDescription = %s\n", (LPCTSTR) bstrDescription);

	}catch (...){
		TRACE ( "*** Unhandled Exception ***" );
	}

	return pRecordset;
}

^^ also das hat bisher immer gut funktioniert ... nur jetzt wo ich blobs brauche geht das mit den inserts net mehr da ich sonst den blob in nen cstring verwandeln müsste und das ist in dem fall etwas schwierig ...

[LordTerra]

hnmmm nach langen hin und her gesuche hab ich ne funktion gefunden die angeblich blobs insertet... das problem: ich bekomm den recordset nicht geöffnet...

kann sich das mal wer angucken und mir den fehler verraten ???

als fehlermeldung bekomm ich im debugger:

Nicht abgefangene Ausnahme in PrinterClient2.exe (KERNEL32.DLL): 0xE06D7363: Microsoft C++ Exception.
Das Programm "D:\ws\c++\Projekte\AVS2XX\#DB-System\PrinterClient2\Debug\PrinterClient2.exe" wurde mit  Code 0 (0x0) beendet.

vielen dank
LT

BOOL DB::DBInsertBlob(){
	CString name="test1.cfg";
	char *buf;
	CString str;
	CFile f;
	char* pFileName=name.GetBuffer(name.GetLength()); 
	if(f.Open(pFileName,CFile::modeRead | CFile::typeBinary)) {
		int flen=f.GetLength();
		buf = new char[flen];
		f.Read(buf, flen);
		
		f.Close();
	}
	name.ReleaseBuffer();

	CString str2;
	str2="SELECT id, data FROM sysconfig WHERE id =1";
/*		
	_CommandPtr pCommand;
	_RecordsetPtr pRecordset;
	HRESULT hr = pCommand.CreateInstance (__uuidof (Command));

	if (FAILED (hr)){
		printf("Can't create an instance of Command");
	}

	pCommand->ActiveConnection = m_pConn;
	pCommand->CommandText = (_bstr_t) str2;


	hr = pRecordset.CreateInstance (__uuidof (Recordset));

	if (FAILED (hr)){
		printf ("Can't create an instance of Recordset");
	}

	pRecordset->CursorLocation = adUseClient;
TRACE("------- pRecordSetAdd->Open -------\n");
	pRecordset->Open ((IDispatch *) pCommand, vtMissing, adOpenKeyset, adLockOptimistic, adCmdUnknown);

*/

	_RecordsetPtr pRecordset;
	HRESULT hr = pRecordset.CreateInstance(__uuidof(Recordset));
	if (FAILED (hr)){
		printf ("Can't create an instance of Recordset");
	}
	pRecordset->CursorType = adOpenKeyset;
	pRecordset->LockType = adLockOptimistic;

	hr=(pRecordset->Open(_bstr_t(str2),_variant_t((IDispatch*)m_pConn,true), adOpenKeyset,adLockOptimistic,adCmdUnknown));//adCmdTable));
	if (FAILED (hr)){
		printf ("Can't create an instance of Recordset");
	}

	// FÜLLE SYSMEMBERS
	CString id="1";     
	
	LONG sizeArray=GlobalSize(buf); // Size of entire object
	_variant_t vpPhoto; // Temp var of VARIANT type

	SAFEARRAY FAR *pSA; // Safe array which helps to transfer data
	SAFEARRAYBOUND rgsabound[1]; // see SAFEARRAY spec.
	rgsabound[0].lLbound = 0; //

	//Create a safe array to store the array of BYTES
	rgsabound[0].cElements = sizeArray;
	pSA = SafeArrayCreate(VT_UI1,1,rgsabound);

	for(long index=0;index < sizeArray;index++){
		//Take BYTE by BYTE and advance Memory Location
		hr=(SafeArrayPutElement(pSA,&index,&buf[index]));
		if (FAILED (hr)){
			printf ("Can't create an instance of Recordset");
		}

	}
	
	vpPhoto.vt = VT_ARRAY|VT_UI1; // Specify data type to be placed in VARIANT variable
	vpPhoto.parray = pSA; // Pointer to safe array with object data

	pRecordset->Fields->GetItem("Photo")->AppendChunk(vpPhoto); // change data in field of the current record

	pRecordset->Update(); // update recordset and make appropriate changes in database
	pRecordset.Release();

	return TRUE;
}

[LordTerra]

so ich hab eine lösung hinbekommen... da hier so viele reingeschaut haben aber keiner geantwortet hat, will ich euch die lösung nu net vorenthalten... vielleicht könnt ihr sie ja auch mal gebrauchen :

Reinschreiben in Blob:

BOOL DB::DBInsertBlob(){
	CString name="test2.cfg";
	char *buf;
	CString str;
	CFile f;
	int flen;
	char* pFileName=name.GetBuffer(name.GetLength()); 
	if(f.Open(pFileName,CFile::modeRead | CFile::typeBinary)) {
		flen=f.GetLength();
		buf = new char[flen];
		f.Read(buf, flen);
		
		f.Close();
	}
	name.ReleaseBuffer();

	CString str2;
	str2="SELECT id, data, datasize FROM ARGUS.SYSCONFIG WHERE id = 1";
	
	_RecordsetPtr pRecordset;
	try	{
		_CommandPtr pCommand;
		HRESULT hr = pCommand.CreateInstance (__uuidof (Command));

		if (FAILED (hr)){
			AfxMessageBox ("Can't create an instance of Command");
			return FALSE;		
		}
		pCommand->ActiveConnection = m_pConn;
		pCommand->CommandText = (_bstr_t) str2;

		hr = pRecordset.CreateInstance (__uuidof (Recordset));

		if (FAILED (hr)){
			AfxMessageBox ("Can't create an instance of Recordset");
			return FALSE;		
		}
		pRecordset->CursorLocation = adUseClient;
		//pRecordset->Open((IDispatch *) pCommand, vtMissing, adOpenStatic, adLockBatchOptimistic, adCmdUnknown);
		pRecordset->Open((IDispatch *) pCommand, vtMissing, adOpenStatic, adLockOptimistic, adCmdUnknown);


		LONG sizeArray=flen; // Size of entire object
		_variant_t vpDATA; // Temp var of VARIANT type

		SAFEARRAY FAR *pSA; // Safe array which helps to transfer data
		SAFEARRAYBOUND rgsabound[1]; // see SAFEARRAY spec.
		rgsabound[0].lLbound = 0; //

		//Create a safe array to store the array of BYTES
		rgsabound[0].cElements = sizeArray;
		pSA = SafeArrayCreate(VT_UI1,1,rgsabound);

		for(long index=0;index < sizeArray;index++){
			//Take BYTE by BYTE and advance Memory Location
			hr = (SafeArrayPutElement(pSA,&index,&buf[index]));
			if (FAILED (hr)){
				printf ("Can't create an instance of Recordset");
			}

		}

		vpDATA.vt = VT_ARRAY|VT_UI1; // Specify data type to be placed in VARIANT variable
		vpDATA.parray = pSA; // Pointer to safe array with object data

		pRecordset->Fields->GetItem("data")->AppendChunk(vpDATA); // change data in field of the current record
		pRecordset->Fields->GetItem("datasize")->Value = (_bstr_t) sizeArray;

		pRecordset->Update(); // update recordset and make appropriate changes in database

	} catch( _com_error &e )	{
		_bstr_t bstrSource(e.Source());
		_bstr_t bstrDescription(e.Description());
		TRACE( "Exception thrown for classes generated by #import" );
		TRACE( "\tCode = %08lx\n", e.Error());
		TRACE( "\tCode meaning = %s\n", e.ErrorMessage());
		TRACE( "\tSource = %s\n", (LPCTSTR) bstrSource);
		TRACE( "\tDescription = %s\n", (LPCTSTR) bstrDescription);
	
		CString ausg;
		ausg.Format("Update: %s",(LPCTSTR) bstrDescription);
		AfxMessageBox (ausg);

		return FALSE;		
	}catch (...){
		TRACE ( "*** Unhandled Exception ***" );
		return FALSE;		
	}
	pRecordset->Close();
	return TRUE;
}

Auslesen aus Blob:

BOOL DB::DBSelectBlob(){
	_RecordsetPtr pRecordset;
	try	{
		int sizeArray;
		CString size;
		_variant_t vpDATA; 
		vpDATA.vt = VT_UI1;
		_RecordsetPtr pRecordset;

		CString select_string="Select * FROM ARGUS.SYSCONFIG where id = 1";
		pRecordset=DBSelectNow(select_string); 

		while (!pRecordset->GetadoEOF()){
			vpDATA = pRecordset->Fields->GetItem ("data")->Value;
			size =  (char *) (_bstr_t) pRecordset->Fields->GetItem ("datasize")->Value;
			pRecordset->MoveNext();
		} 
		pRecordset->Close();
		
		sizeArray = atoi(size);
		HRESULT hr;

		SAFEARRAY FAR *pSA = vpDATA.parray; // Safe array which helps to transfer data
		

		char * buf = new char[sizeArray];

		for(long index=0;index < sizeArray;index++){
			//Take BYTE by BYTE and advance Memory Location
			hr = (SafeArrayGetElement(pSA,&index,&buf[index]));

			if (FAILED (hr)){
				printf ("Can't create an instance of Recordset");
			}

		}

		
	CString name="test22.cfg";
	CFile f;
	int flen;
	char* pFileName=name.GetBuffer(name.GetLength()); 
	if(f.Open(pFileName,CFile::modeCreate | CFile::modeWrite | CFile::typeBinary)) {	

		f.Write(buf, sizeArray);
		f.Close();
	}
	name.ReleaseBuffer();
		
	} catch( _com_error &e )	{
		_bstr_t bstrSource(e.Source());
		_bstr_t bstrDescription(e.Description());
		TRACE( "Exception thrown for classes generated by #import" );
		TRACE( "\tCode = %08lx\n", e.Error());
		TRACE( "\tCode meaning = %s\n", e.ErrorMessage());
		TRACE( "\tSource = %s\n", (LPCTSTR) bstrSource);
		TRACE( "\tDescription = %s\n", (LPCTSTR) bstrDescription);
	
		CString ausg;
		ausg.Format("Update: %s",(LPCTSTR) bstrDescription);
		AfxMessageBox (ausg);

		return FALSE;		
	}catch (...){
		TRACE ( "*** Unhandled Exception ***" );
		return FALSE;		
	}
	return TRUE;
}