SQL Anywhere database - Re: xp_write_file Image larger than 32k

Author

Re: xp_write_file Image larger than 32k

Greg Fenton

2005-07-12, 9:23 am

Robert wrote:
> ASA 9.0.2.2451
> I have a pod that reads a image ( larger than 32k) into 4 long binary
> fields stored in my table.
> How do I combine these fields and write the image to disk.
> I write 32k to each field, but if the image is only 64k big --> field 3and
> 4 are left empty or null.

Why are you only storing/retrieving 32k from a long binary field? Long
binaries can hold up to 2GB.

Can you show us the code you use to store/retrieve the binary data?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Robert

2005-07-13, 3:23 am

written in embedded C++ 3
Originally got Sybase SA to write code for me. To Start me off .Limited to
32k image file.
Now I maintain this code and I am still learning.
Unfortunately time concerns forces me to use what I know and I don't have
time to experiment.
I will be doing a C++ course shortly so thing should improve.
Herewith code to read image file and place into binary fields
:
static PODSBoolean SendFileObjectPlace(
PODSObject *podsObj, PODSString
filename, PODSString table, PODSString inField, PODSString atField,
PODSString cBid, PODSString cOwner)
{
SendFileObject *self = (SendFileObject *)podsObj;

TCHAR filePathAndFileName[200];
TCHAR sqlStatement[200];
HANDLE fileHandle;
DWORD read;
int isfirst;
int fieldno;
isfirst=0;
bool nResult;
char fieldvar[50];
char fieldname[50];
p_ul_binary bindata = new rp_dbt_binary;

ULValue val;

// Place filedata into an ultralite binary buffer.
wsprintf(filePathAnd
FileName, L"%S", filename);
if ( !(fileHandle=CreateF
ile(filePathAndFileN
ame, GENERIC_READ,
FILE_SHARE_READ, NULL,
OPEN_EXISTING, 0, NULL) ) ) return false;
nResult= ReadFile( fileHandle, &bindata->data, MAX_RP_BINARY, &read, NULL);
if (!nResult || read==0)
return false;
bindata->len = (rp_length) read;
// Create SQL statement
wsprintf(sqlStatemen
t, L"UPDATE %S SET %S=? WHERE BID='%S' AND %S='%S'",
table, inField, cBid, atField, cOwner);

// Insert into the database.
//MessageBox( NULL, sqlStatement, L"Info", MB_OK );
PreparedStatement * ps = self->conn-> PrepareStatement(sql
Statement, NULL);
ps->SetParameter(1, bindata);
ps->ExecuteStatement();
fieldno = 1;
strcpy(fieldname,inF
ield);
while (read > 0 && fieldno <4)
{
fieldno++;
sprintf(fieldvar,"%s%d" ,fieldname,fieldno);

// MessageBox( NULL, (LPCTSTR) fieldvar, L"Info", MB_OK );
nResult= ReadFile( fileHandle, &bindata->data, MAX_RP_BINARY, &read,
NULL);
bindata->len = (rp_length) read;
// Create SQL statement
wsprintf(sqlStatemen
t, L"UPDATE %S SET %S=? WHERE BID='%S' AND %S='%S'",
table, fieldvar, cBid, atField, cOwner);

// Insert into the database.
//MessageBox( NULL, sqlStatement, L"Info", MB_OK );
PreparedStatement * ps = self->conn-> PrepareStatement(sql
Statement, NULL);
ps->SetParameter(1, bindata);
ps->ExecuteStatement();
}
//commit table
self->conn->Commit();

// close
CloseHandle(fileHand
le);

delete bindata;
return true;
}

Greg Fenton

2005-07-13, 8:23 pm

I have not tested this code in any way whatsoever, but here is an
attempt to use the StreamWriter to put the binary data into a single
column of the UltraLite database.

BTW: you might find that by posting to the UltraLite newsgroup you would
get to a more targetted audience (i.e. other UL developers):

sybase.public.sqlanywhere.ultralite

//-----------------------------------------------------------------
//-----------------------------------------------------------------
static PODSBoolean SendFileObjectPlace(
PODSObject *podsObj, PODSString
filename, PODSString table,
PODSString inField, PODSString
atField, PODSString cBid,
PODSString cOwner)
{
SendFileObject *self = (SendFileObject *)podsObj;

TCHAR filePathAndFileName[200];
TCHAR sqlStatement[200];
HANDLE fileHandle;
DWORD read;
int isfirst;
int fieldno;
isfirst=0;
bool nResult;
char fieldvar[50];
char fieldname[50];
p_ul_binary bindata = new rp_dbt_binary;

ULValue val;

Table *t;
t = self->conn->OpenTable( table );

ULValue img_col = t->GetSchema()->GetColumnID( "MyImageColumn" );
StreamWriter w = t->GetStreamWriter( img_col );

t->FindBegin();

ULValue bid_col = t->GetSchema()->GetColumnID( "BID" );
ULValue v_bid( cBid );
t->Set( bid_col, v_bid );

if ( ! t->FindFirst() ) {
return false; // could not find the row to add the image to
}

// Open the image file
wsprintf(filePathAnd
FileName, L"%S", filename);
if ( !(fileHandle=CreateF
ile(filePathAndFileN
ame, GENERIC_READ,
FILE_SHARE_READ, NULL,
OPEN_EXISTING, 0, NULL) ) ) {
return false;
}

if ( ! t->UpdateBegin() ) {
// Something bad happened. Check conn->GetSqlca->GetSQLCode()
// for details.
t->Release();
return false;
}

// Read the file in chunks and store those chunks to the database
while (1) {

// Read a chunk of binary data from the file
nResult= ReadFile( fileHandle, &bindata->data, MAX_RP_BINARY,
&read, NULL);
if (!nResult || read==0) {
break; // no more data to read
}

bindata->len = (rp_length) read;

if ( ! w->AppendByteChunk( &bindata->data, &bindata->len ) ) {
// Something bad happened. Check
// conn->GetSqlca->GetSQLCode() for details.
self->conn->Rollback();
t->Release();
CloseHandle(fileHand
le);
return false;
}

}

if ( ! t->Update() ) {
// Something bad happened. Check conn->GetSqlca->GetSQLCode()
// for details.
self->conn->Rollback();
t->Release();
CloseHandle(fileHand
le);
return false;
}

self->conn->Commit();

// close
t->Release();
CloseHandle(fileHand
le);

delete bindata;
return true;
}
//------------------------------------------------------
//-----------------------------------------------------------------

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Robert

2005-07-14, 3:23 am

Thank you for your time.
I will copy and make it work on my side.
Will keep you posted.

Thanks again
Robert

Robert

2005-07-14, 7:23 am

Thanks Greg.
With a few modifications it work perfectly.My primary key was on two fields.
Also I have two tables that use the same function to update binary images.
But It was easy to modify code.
Much appreciated.

Sponsored Links