Solved

From disk (file) to SQL and back?

Posted on 2001-09-18
21
350 Views
Last Modified: 2007-12-19
I would like to store files of many types (exe, dwg, drw, txt, bin) in SQL programmatically, and read them back to save them on disk in their original form.
I use ADO to access the SQL database.
I think I must use the varbinary field in SQL but I?m not sure.

Could someone help me with code please.
0
Comment
Question by:mac_barnard
  • 8
  • 4
  • 4
  • +3
21 Comments
 
LVL 6

Expert Comment

by:Triskelion
ID: 6490465
Why would you do this?
It sounds as if you simply need a file server and not a database.
0
 
LVL 6

Expert Comment

by:Triskelion
ID: 6490475
And on second thought, maybe you could just keep the path name to the files in the database, then as they're requested, you can flag the number of times they've been downloaded or who retrieved them and whatever.

0
 
LVL 32

Expert Comment

by:jhance
ID: 6490482
What database engine are you using?  This problem, that is storing "raw" data in the database, is not exactly a standard across all DB engines.  By SQL do you mean MS SQL Server??

In general, this is called a BLOB in the database world.  BLOB is an acronym for Binary Large OBject.
0
 
LVL 10

Expert Comment

by:makerp
ID: 6490496
maybe a better way would be store the file on disk and hold an entry in the database pointing to the path of the file. i dont know to much about the internals of a database but i can not see the advantage of storing massive binary objects in a database. if anything it must slow the db engine down and may inpact the running of other queries.

Paul
0
 

Author Comment

by:mac_barnard
ID: 6490499
I'm using MS SQL Server 2000
0
 
LVL 32

Expert Comment

by:jhance
ID: 6490510
SQL server does supports BLOBs and while there may be a case for including the data in the DB vs. only storing a pointer, you can do it either way.

See the section in the MSDN titled:

Recordset: Working with Large Data Items (ODBC)

It provides a discussion and some example code.
0
 

Author Comment

by:mac_barnard
ID: 6490514
I'm using MS SQL Server 2000
0
 
LVL 32

Expert Comment

by:jhance
ID: 6490523
Yes, we know that now.  Why are you repeating yourself?

As I said:

SQL server does supports BLOBs and while there may be a case for including the data in the DB vs. only
storing a pointer, you can do it either way.

See the section in the MSDN titled:

Recordset: Working with Large Data Items (ODBC)

It provides a discussion and some example code.

0
 

Author Comment

by:mac_barnard
ID: 6490534
I'm using MS SQL Server 2000
0
 
LVL 32

Expert Comment

by:jhance
ID: 6490570
Yes, we know that now THREE TIMES!!!!  

Why are you repeating yourself?

As I said:

SQL server does supports BLOBs and while there may be a case for including the data in the DB vs. only

storing a pointer, you can do it either way.

See the section in the MSDN titled:

Recordset: Working with Large Data Items (ODBC)

It provides a discussion and some example code.

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 3

Expert Comment

by:cwrea
ID: 6490688
Don't have a cow, man. :-)  Maybe he was reloading the page and causing a repost of the form data.  Blame the EE software for not checking for that kind of scenario.
0
 
LVL 10

Expert Comment

by:makerp
ID: 6490756
thats why EE has reload question link,

'Don't have a cow, man. :-) ' although thats a bloddy funny thing to say LOL, hahahahahaha,

Paul
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6491383
There are several excellent reasons to store large files into a database.  One major reason is that the clients that need to access the data can use their database connection rather than a filesystem access -- avoiding network security problems.

mac_barnard,
Describe what help you need.  

In general, it is simply a matter of declaring a variable and using ADO to read data into it or store data from it into a db table.  With ADO, it is really quite similar to accessing regular variables.  When using 'raw' SQL fns it is more involved because you need to process chunks of data manually.

-- Dan
0
 

Author Comment

by:mac_barnard
ID: 6492570
I'm using MS SQL Server 2000
0
 

Author Comment

by:mac_barnard
ID: 6492577
Thanks cwrea, I did reload the page?sorry.
0
 

Author Comment

by:mac_barnard
ID: 6492722
Dan

That is exactly my reason, I don?t want the client to use file system
access because of network security.

I?ve got a dialog-based application that acts as a document manager for our drawing office.

My application is currently working fine with file system access for the documents and a table for the index info.

I must change my app to act in the following manner

When they check a document IN, the app should then insert the document index info as well as the document itself into a table. On a check OUT the reverse must happen, get the document index info and the document itself from the table.

The code I?ve got to read the file don?t seem to work for me, when I do the read ( is.read (buffer,length) ) it seems as if it encounters a carriage return and stop filling the buffer.

int length;
char * buffer;
char name[1024];

strcpy(name, "c:\\TEST.Bin");

ifstream is;
is.open (name, ios::binary );

// get length of file:
is.seekg (0, ios::end);
length = is.tellg();
is.seekg (0, ios::beg);

// allocate memory:
buffer = new char [length];

// read data as a block:
is.read (buffer,length);

is.close();

//Insert into table
strcpy(SqlStr,"Insert into INFO (FileName, BIN) values ('");
strcat(SqlStr,name);
strcat(SqlStr,"','");
strcat(SqlStr,buffer);
strcat(SqlStr,"')");

adoRs.CreateInstance(_uuidof(Recordset));
try
{
   adoRs->Open   (SqlStr,"DSN=TEST;UID=sa;PWD=;",adOpenStatic,adLockOptimist ic,adCmdText);              
}
catch (...)
{
  ;
}
adoRs = NULL;

//Clear buffer
buffer = NULL;
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6492945
Well part of this is easy...
you use strcat to append the raw binary data into a text string.  Consider what will happen when the binary data contains a null (ASCII 0x00).  That will terminate the strng and the SQL command goes kaput.

The fil io looks ok.  Ill get back with the proper coding technique...

-- Dan
0
 

Author Comment

by:mac_barnard
ID: 6492958
Thanks Dan
0
 
LVL 49

Accepted Solution

by:
DanRollins earned 200 total points
ID: 6493059
Here is how I got a blob to go in to my SQL database.

void CAdo1Dlg::OnButton1()
{
   HRESULT       hr;      
   _variant_t    vNull( DISP_E_PARAMNOTFOUND, VT_ERROR );        // Null variant

    hr= m_pRecSet.CreateInstance(__uuidof(Recordset));
    m_pRecSet->PutRefActiveConnection( m_pDBConnection );

   _bstr_t  bstrQuery( "TheTableWithTheBlobs" );
    hr= m_pRecSet->Open(_variant_t(bstrQuery), vNull,
        adOpenStatic,
        adLockOptimistic,
        adCmdTable
    );
    // int nBlobCnt= m_pRecSet->GetRecordCount(); // eyeball check

    hr= m_pRecSet->AddNew();

    //--------------------------------------- set up the blob with this
    //--------------------------------------- UGLY UGLY Code
    SAFEARRAYBOUND rgsabound[1];
    rgsabound[0].lLbound = 0;
    rgsabound[0].cElements= nFileLen; // size of data

    COleVariant v;
    v.vt= VT_ARRAY|VT_UI1;
    v.parray= SafeArrayCreate(VT_UI1,1,rgsabound);

    void* pArrayData= NULL;
    SafeArrayAccessData( v.parray, &pArrayData );

    memcpy( pArrayData, pFileData, nFileLen); // copy file data to the array

    SafeArrayUnaccessData( v.parray );

    COleVariant vTmp= 1234567L;
    m_pRecSet->Fields->GetItem("nInqIdx")->PutValue( vTmp );
    hr= m_pRecSet->Fields->GetItem("sResp")->AppendChunk( v );

    hr= m_pRecSet->Update();     //Update the table
}

=-=-=-=-=-=-=-
Lookup AppendChunk and GetChunk in MDSN for some even uglier code.  I can't think of any way they could make  ADO harder to use in VC++.  VB users laugh at us.  In JavaScript, this is three lines of code.  In C++ it is a heaving monster.

-- Dan
0
 

Author Comment

by:mac_barnard
ID: 6493540
Thanks Dan,

I'm halfway there.

Please help me with GetChunk to get the blob out.

 
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6494979
First, I did not mention that the above code copies the file data unnecessarity.  It would be wise to allocate the SafeArray first, then use SafeArrayAccessData to get a pointer to it, and then read the file into that address.

-=-=-=-=-=-=-=
To read the file from the database:  
You will either need to know its size in advance, or use GetChuck multiple times.  

For the former, I suggest adding an integer nFileLen to the database table and set it when you insert the record.  Then your code will look like this:

   _bstr_t  bstrQuery( "SELECT * FROM TheTableWithTheBlobs WHERE nIdx=1234 " );
   hr= m_pRecSet->Open(_variant_t(bstrQuery), vNull,
       adOpenStatic,
       adLockOptimistic,
       adCmdText
   );
   // int nBlobCnt= m_pRecSet->GetRecordCount(); // eyeball check


   COleVariant vTmp= 0L;
   m_pRecSet->Fields->GetItem("nFileLen")->GetValue( vTmp );
   long nFileLen= vTmp;

   SAFEARRAYBOUND rgsabound[1];
   rgsabound[0].lLbound = 0;
   rgsabound[0].cElements= nFileLen; // size of data

   COleVariant v;
   v.vt= VT_ARRAY|VT_UI1;

   v= m_pRecSet->Fields->Item["rawBlob"]->GetChunk( nFileLen );
 
   void* pArrayData= NULL;
   SafeArrayAccessData( v.parray, &pArrayData );

   // here put the code to...
   // save the data from the blob to a local file
   // the data is at pArrayData and should be nFileLen bytes long

   SafeArrayUnaccessData( v.parray );
}

The alternate method would be to read it in pieces

long lOffset = 0;
long index1=  0;
const long CNUM_ChunkSize= 1024;
bool fDone= FALSE;
while( !fDone )
{
     v= m_pRecSet->Fields->Item["logo"]->GetChunk(CNUM_ChunkSize);
     note: v will be NULL after final read.
     if (v == NULL) {fDone= TRUE; break; }
     void* pArrayData= NULL;
     SafeArrayAccessData( v.parray, &pArrayData );
     // here add the code to
     // write CNUM_ChunkSize bytes to the file.  It starts at pArrayData
     SafeArrayUnaccessData( v.parray );
     lOffSet +=  CNUM_ChunkSize;
}

I think you will need to modify this code so that on the final chunk you only write the actual amount of data that is in the variant.  I don't have much expertise with these ugly VB constructs so I can't say offhand how to do that.  That size may be available in v.parray->rgsabound[0].cElements   I really hate it when the only examples of use were written by and for VB programmers.

-- Dan
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Errors will happen. It is a fact of life for the programmer. How and when errors are detected have a great impact on quality and cost of a product. It is better to detect errors at compile time, when possible and practical. Errors that make their wa…
Introduction This article is the first in a series of articles about the C/C++ Visual Studio Express debugger.  It provides a quick start guide in using the debugger. Part 2 focuses on additional topics in breakpoints.  Lastly, Part 3 focuses on th…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now