如何用ADO将文件存入Oracle8i的BLOB字段中？？？

[henrylu21]

无法用select(SQL)来显示BLOB字段，从而得不到此字段的Field的对象。也就不能使用AppendChunk(),痛苦！ who can help me!!!

[henrylu21]

高手，快来帮帮我！！！
万分感谢！！！

[anggogo]

用OO4O
ADO连Oracle会导致数据库crash

[henrylu21]

我后来用了oo4o,成功地插入了一个文件，并且也能读出来；但是以后向数据表中插入的文件都不能读出，错误为“在OCI lob 函数中非法的参数值”。即在控数据表中插入的第一个文件是正常的，以后的文件都不正常。这是为什么？？？ 大侠快来！！！

[anggogo]

这就不知道了。
你先用SQL PLUS试试是否可以直接用SQL去做。
如果不行，试试用事务，并捕捉错误，看看文档里有没有相应的说明和解决方法。

[seesi]

See Also
AppendChunk Method | Field Object | GetChunk Method
ADO Samples
AppendChunk and GetChunk Methods Example (VC++)
This example uses the AppendChunk and GetChunk methods to fill an image field with data from another record.

// BeginAppendChunkCpp
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF", "EndOfFile")

#define ChunkSize 100

#include <ole2.h>
#include <stdio.h>
#include "conio.h"
#include "malloc.h"
#include "AppendChunkX.h"

//Function declarations
inline void TESTHR(HRESULT x) {if FAILED(x) _com_issue_error(x);};
void AppendChunkX(VOID);
void PrintProviderError(_ConnectionPtr pConnection);

///////////////////////////////////////////////////////////
// //
// Main Function //
// //
///////////////////////////////////////////////////////////
void main()
{
HRESULT hr = S_OK;

if(FAILED(::CoInitialize(NULL)))
return;

AppendChunkX();

//Wait here for the user to see the output
printf("\n\nPress any key to continue..");
getch();
::CoUninitialize();
}


///////////////////////////////////////////////////////////
// //
// AppendChunkX Function //
// //
///////////////////////////////////////////////////////////
VOID AppendChunkX(VOID)
{
// Define ADO object pointers.
// Initialize pointers on define.
// These are in the ADODB:: namespace.
_RecordsetPtr pRstPubInfo = NULL;
_ConnectionPtr pConnection = NULL;

//Define other variables
IADORecordBinding *picRs = NULL; //Interface Pointer declared.(VC++ Extensions)
CPubInfoRs pubrs; //C++ class object

HRESULT hr = S_OK;
_bstr_t strCnn("Provider=sqloledb;Data Source=MyServer;"
"Initial Catalog=pubs;User Id=sa;Password=;");

_bstr_t strMessage,strPubID,strPRInfo;
_variant_t varChunk;
long lngOffSet,lngLogoSize;
char pubId[50];
lngOffSet = 0;

UCHAR chData;
CHAR ch;
SAFEARRAY FAR *psa;
SAFEARRAYBOUND rgsabound[1];
rgsabound[0].lLbound = 0;
rgsabound[0].cElements = ChunkSize;

try
{
//Open a Connection.
TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
hr = pConnection->Open(strCnn,"","",adConnectUnspecified);

TESTHR(hr= pRstPubInfo.CreateInstance(__uuidof(Recordset)));

pRstPubInfo->CursorType = adOpenKeyset;
pRstPubInfo->LockType = adLockOptimistic;

hr = pRstPubInfo->Open("pub_info",
_variant_t((IDispatch*)pConnection,true),
adOpenKeyset,adLockOptimistic,adCmdTable);

//Open an IADORecordBinding interface pointer which we'll use
//for Binding Recordset to a class
TESTHR(pRstPubInfo->QueryInterface(
__uuidof(IADORecordBinding),(LPVOID*)&picRs);

//Bind the Recordset to a C++ Class here
TESTHR(picRs->BindToRecordset(&pubrs));

//Display the available logos here
strMessage = "Available logos are: " + (_bstr_t)"\n\n";
printf(strMessage);
int Counter = 0;
while(!(pRstPubInfo->EndOfFile))
{
printf("\n%s",pubrs.m_sz_pubid);
printf("\n%s",strtok(pubrs.m_sz_prinfo,","));

//Display 5 records at a time and wait for user to continue..
if (++Counter >= 5)
{
Counter = 0;
printf("\nPress any key to continue...");
getch();
}
pRstPubInfo->MoveNext();
}

//Prompt For a Logo to Copy
printf("\nEnter the ID of a logo to copy: ");
scanf("%s",pubId);
strPubID = pubId;

//Copy the logo to a variable in chunks

pRstPubInfo->Filter = "pub_id = '" + strPubID + "'";
lngLogoSize = pRstPubInfo->Fields->Item["logo"]->ActualSize;

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

long index1 = 0;
while(lngOffSet < lngLogoSize)
{
varChunk = pRstPubInfo->Fields->
Item["logo"]->GetChunk(ChunkSize);

//Copy the data only upto the Actual Size of Field.
for(long index=0;index<=(ChunkSize-1);index++)
{
hr= SafeArrayGetElement(varChunk.parray,&index,&chData);
if(SUCCEEDED(hr))
{
//Take BYTE by BYTE and advance Memory Location
hr = SafeArrayPutElement(psa,&index1,&chData);
index1++;
}
else
break;
}
lngOffSet = lngOffSet + ChunkSize;
}
lngOffSet = 0;

printf("Enter a new Pub Id: ");
scanf("%s",pubrs.m_sz_pubid);
strPubID = pubrs.m_sz_pubid;
printf("Enter descriptive text: " );
scanf("%c",&ch);
gets(pubrs.m_sz_prinfo);

pRstPubInfo->AddNew();
pRstPubInfo->Fields->GetItem("pub_id")->PutValue(strPubID);
pRstPubInfo->Fields->GetItem("pr_info")->
PutValue(pubrs.m_sz_prinfo);

//Assign the Safe array to a variant.
varChunk.vt = VT_ARRAY|VT_UI1;
varChunk.parray = psa;
hr = pRstPubInfo->Fields->GetItem("logo")->
AppendChunk(varChunk);

//Update the table
pRstPubInfo->Update();

lngLogoSize = pRstPubInfo->Fields->Item["logo"]->ActualSize;

//Show the newly added record.
printf("New Record : %s\n Description : %s\n Logo Size : %s",
pubrs.m_sz_pubid,
pubrs.m_sz_prinfo,(LPCSTR)(_bstr_t)pRstPubInfo->Fields->
Item["logo"]->ActualSize);

//Delete new record because this is demonstration.
pConnection->Execute("DELETE FROM PUB_INFO WHERE pub_id = '"
+ strPubID +"'",NULL,adCmdText);

pRstPubInfo->Close();
pConnection->Close();
}

catch(_com_error &e)
{
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());

PrintProviderError(pConnection);
printf("Source : %s \n Description : %s\n",(LPCSTR)bstrSource,(LPCSTR)bstrDescription);

}
}


///////////////////////////////////////////////////////////
// //
// PrintProviderError Function //
// //
///////////////////////////////////////////////////////////

VOID PrintProviderError(_ConnectionPtr pConnection)
{
// Print Provider Errors from Connection object.
// pErr is a record object in the Connection's Error collection.
ErrorPtr pErr = NULL;
long nCount = 0;
long i = 0;

if( (pConnection->Errors->Count) > 0)
{
nCount = pConnection->Errors->Count;
// Collection ranges from 0 to nCount -1.
for(i = 0; i < nCount; i++)
{
pErr = pConnection->Errors->GetItem(i);
printf("\t Error number: %x\t%s", pErr->Number,(LPCSTR) pErr->Description);
}
}
}

// EndAppendChunkCpp

AppendChunkX.h:

// BeginAppendChunkH
#include "icrsint.h"

//This Class extracts pubid,prinfo.

class CPubInfoRs : public CADORecordBinding
{
BEGIN_ADO_BINDING(CPubInfoRs)

ADO_VARIABLE_LENGTH_ENTRY2(1, adVarChar, m_sz_pubid,
sizeof(m_sz_pubid), l_pubid, TRUE)

ADO_VARIABLE_LENGTH_ENTRY2(3, adVarChar, m_sz_prinfo,
sizeof(m_sz_prinfo), l_prinfo, TRUE)

END_ADO_BINDING()

public:
CHAR m_sz_pubid[10];
ULONG l_pubid;
CHAR m_sz_prinfo[200];
ULONG l_prinfo;
};
// EndAppendChunkH

See Also
AppendChunk Method | Field Object | GetChunk Method

基于SQL Server的看看吧。

[seesi]

其他的不知道了

[henrylu21]

谢谢，但是这个我早就试过了，关键是
hr = pRstPubInfo->Open("pub_info",
_variant_t((IDispatch*)pConnection,true),
adOpenKeyset,adLockOptimistic,adCmdTable);
这一句要取出所有的字段时，如果有BLOB类型，则会出错！！！
后来我用oo4o,试了好久，并且上OTN上看了别人相同的问题，现在有点眉目了:)

[fatty]

我也想知道答案, 谁能告诉我!

[greatman]

我也想知道答案, 谁能告诉我!
[e-mail]
谢了！

[henrylu21]

#include <oracl.h>
#include <iostream.h>
#include <fstream.h>

// Example for OBlob::Write
int main()
{

//Initialize oo4o, connect, execute sql
OStartup();
ODatabase odb("ExampleDB", "scott", "tiger");
ODynaset odyn(odb, "SELECT * FROM PART");

if (!odyn.IsOpen())
{
cout <<"Connect Error: "<<odb.GetErrorText()<<endl;

cout <<"SQL Error: "<<odyn.GetErrorText()<<endl;
return 1;
}

//prior to modifying a LOB for the first time,
//we must first insert an Empty LOB and then commit
OValue val;
val.SetEmpty();
odyn.AddNewRecord();
oresult ores = odyn.SetFieldValue((const char *)"PART_IMAGE", val);
ores = odyn.Update();

odyn.MoveLast()//非常重要！！！，这是oo4o例子中所忽视的；否则当你向已有数据的表中插入数据后，blob不能正常地存入

OBlob oblob;
odyn.StartEdit();
odyn.GetFieldValue("PART_IMAGE", &oblob);

unsigned char *buffer = 0;

try
{
// calculate an optimum buffersize of approximately 32k bytes
unsigned long optchunk = oblob.GetOptimumChunkSize();
unsigned int bufsize = ((int)(32768/optchunk)) *optchunk;
buffer = (unsigned char *)malloc(bufsize);

//open file and get file size
fstream fs;
fs.open("C:\\Oracle\\Ora81\\Oo4o\\Cpp\\Workbook\\Lob\\partimage.dat", ios::in);
fs.setmode(filebuf::binary);
fs.seekg(0, ios::end);

unsigned long filesize = fs.tellg();
fs.seekg(0, ios::beg);

unsigned long totalwritten = 0;
unsigned long amtread = 0;
int piecetype = OLOB_FIRST_PIECE;

//By taking advantage of streaming we get the best performance
//and we don't need to allocate a huge buffer.
if (filesize <= bufsize)
piecetype = OLOB_ONE_PIECE;
else
oblob.EnableStreaming(filesize);

while(totalwritten != filesize)
{
fs.read(buffer, bufsize);
amtread = fs.gcount();

oblob.Write(buffer, amtread, piecetype);
totalwritten = totalwritten + amtread;

if ((filesize - totalwritten)<=bufsize)
piecetype = OLOB_LAST_PIECE;
else
piecetype = OLOB_NEXT_PIECE;
}

oblob.DisableStreaming();

ores = odyn.Update();
fs.close();
}
catch(OException E)
{
cout<<E.GetFailedMethodName()<< " Error: "<<E.GetErrorText()<<endl;
}

if (buffer)
free(buffer);

OShutdown();
return 0;
}

[fatty]

henrylu21:
如果你知道答案,请把你源码的这部分告诉我, 我会给你很多分的

SELECT\INSERT\UPDATE(DELETE就不要啦)

[hp1200]

up