Bulk Insert of Blob data type in Oracle

[Niraj]

Hi,
I am trying to do bulk insert of binary data (array of bytes) in an
Oracle table. The data type of the table is BLOB. I am using Oracle
Objects for OLE (OO4O) in C++.

The binary data that I have to insert is in the form of byte array.
My problem is that the bulk inserts happen only as long as the length
of array is less than 4K. Any data more than 4K gets truncated.

Is it possible to do bulk inserts in a BLOB field with data more that
4K per record ? Here is the code that works fine only as long as byte
array is less that 4000 bytes:


OStartup();

ODatabase conn;
conn.Open("tnsname", "user", "password");


if (conn.IsOpen())
{

long recordCount = 10;

OParameterCollection params = conn.GetParameters();
OParamArray blobParam =
params.AddTable("PARAMBLOB", OPARAMETER_INVAR,
OTYPE_RAW_BIN, recordCount, 4000);

//lets put 10 dumy records
int bufSize = 4000;
for (int i=0;i<recordCount;i++)
{
//simulate a byte array of size bufSize.
//This is normally more than 4000 bytes in real life
BYTE * byteArray = new BYTE[bufSize];
for (int j=0;j<bufSize;j++)
{
byteArray[j] = 60;//an arbit value

}

//copy it over to a char buffer
char *buff = (char *)malloc(bufSize);

memcpy(buff, byteArray, bufSize);

blobParam.SetValue(buff, bufSize, i);

free(buff);

free(byteArray);

}

oresult result =
conn.ExecuteSQL("insert into BLOB_TABLE (BLOB_FIELD) values
PARAMBLOB)");

blobParam.Close();
params.Close();

conn.Close();
}

OShutdown();



Any help will be greatly appreciated.
Thanks

 

[Alf P. Steinbach]

* (e-mail address removed) (Niraj) schriebt:

Off-topic in [comp.lang.c++].

Please don't cross-post to groups unrelated to the question.

 

[Jim Kennedy]

Hi,
I am trying to do bulk insert of binary data (array of bytes) in an
Oracle table. The data type of the table is BLOB. I am using Oracle
Objects for OLE (OO4O) in C++.

The binary data that I have to insert is in the form of byte array.
My problem is that the bulk inserts happen only as long as the length
of array is less than 4K. Any data more than 4K gets truncated.

Is it possible to do bulk inserts in a BLOB field with data more that
4K per record ? Here is the code that works fine only as long as byte
array is less that 4000 bytes:


OStartup();

ODatabase conn;
conn.Open("tnsname", "user", "password");


if (conn.IsOpen())
{

long recordCount = 10;

OParameterCollection params = conn.GetParameters();
OParamArray blobParam =
params.AddTable("PARAMBLOB", OPARAMETER_INVAR,
OTYPE_RAW_BIN, recordCount, 4000);

//lets put 10 dumy records
int bufSize = 4000;
for (int i=0;i<recordCount;i++)
{
//simulate a byte array of size bufSize.
//This is normally more than 4000 bytes in real life
BYTE * byteArray = new BYTE[bufSize];
for (int j=0;j<bufSize;j++)
{
byteArray[j] = 60;//an arbit value

}

//copy it over to a char buffer
char *buff = (char *)malloc(bufSize);

memcpy(buff, byteArray, bufSize);

blobParam.SetValue(buff, bufSize, i);

free(buff);

free(byteArray);

}

oresult result =
conn.ExecuteSQL("insert into BLOB_TABLE (BLOB_FIELD) values
PARAMBLOB)");

blobParam.Close();
params.Close();

conn.Close();
}

OShutdown();



Any help will be greatly appreciated.
Thanks

You have defined the record to be only 4,000 bytes long in the parameter. A
BLOB can hold up to 4 gigs so you shouldn't have trouble fitting something
larger in it.
Change:

params.AddTable("PARAMBLOB", OPARAMETER_INVAR,
OTYPE_RAW_BIN, recordCount, 4000); to
params.AddTable("PARAMBLOB", OPARAMETER_INVAR,
OTYPE_RAW_BIN, recordCount,

SOMETHING_LARGE_ENOUGH_TO_HOLD_LARGEST_VALUE);

To test you could change the 4000 to 3000 and I bet it will be truncated to
3,000 bytes not 4,000 bytes.
Jim