How to save an object into a field?

Question

How to save an object into a field?

SSC Rookie

Points: 34

December 5, 2005 at 8:07 am

Can you show me how to save a rtf file into a field in the SQL Server 2K table? In Access, 
I am able to do that by assigning the datatype OLE Object and I go to Insert -> Oject and browse 
to the file that I want to store in the field. But I can not do that way in SQL Server. I guess I would 
declare the SQL server data type Image. However, I am stuck on how to store an object in this field. Also, 
assume I can store such an object like that (i.e., a rtf file or a wav file), can I declare a record set 
and manupulate these fields to my way?

Thank you for your help in advance.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76254 More actions · Answer 1

If you are using ADO to connect to the database have a look at the ADO Stream object.

If you are using .NET use a file stream and Binary reader to create an byte array. You can then load the ByteArray into a stored procedure with a image parameter.

LinkedIn Profile

curie SSC Rookie Points: 34 More actions · Answer 2

Thank you for your tip. Since I am a newbie, can you please tell me more about ADO Stream Object? Where can I take a look at it? Thanks

David.Poole SSC Guru Points: 76254 More actions · Answer 3

David.Poole

SSC Guru

Points: 76254

December 5, 2005 at 12:51 pm

#607777

ADO tutorial

LinkedIn Profile

Ian Yates SSCoach Points: 19738 More actions · Answer 4

There is also a command line utility you can used called textcopy for your bulk imports if it is a one-off job.

curie SSC Rookie Points: 34 More actions · Answer 5

Thanks David and Ian for your tips. While I will check ADO Tutorial, I'd like to try Ian's textcopy also. But can you please tell me more about it? Do you mean SQL Server 2K Enterprise Manager?? Can you show more in details?

I appreciate your help.

Thank you again.

Ian Yates SSCoach Points: 19738 More actions · Answer 6

The textcopy utility is a program you run from the command line (cmd.exe) - kind of like copy, dir, etc... As such, it is not useful for continuous use in an application as you would code the insert yourself using ADO.

BUT... if it is a one-off job, then you can use textcopy.

Goto Start->Run. Type in cmd

Then type

cd "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"

Now type

textcopy /?

This will give you the list of switches you can use.

For example (all on one line!)
textcopy /S MYSERVER /U USERNAME /P PASSWORD /D DATABASE 
/T TABLE /C COLUMN /W "where pkey=value" /F "C:\TEMP\MY_FILE.TXT" /I

Will connect to MYSERVER, using USERNAME and PASSWORD. It will switch to DATABASE and then import C:\TEMP\MY_FILE.TXT into TABLE.COLUMN. The /I means input - you can use /O to output/export instead.

If you have a lot of files to do this for, make a quick script in query analyser (or use excel to build the string using a formula) to make the list of commands to run, put them into notepad, save as something like IMPORT.CMD and then run IMPORT.CMD. Be sure to put c:\program files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe rather than just "textcopy" at the start of the line - this will let you run the batch file from anywhere.

Good luck

PS - this is useful for quick and dirty imports, initial bulk imports or if you have some other app that can call a command line proc based on some event (like file backup utilities for example). If you are writing an application yourself which will store these RTFs all the time, I suggest you go with ADO or ADO.NET (depending if you are writing in Delphi, VB6, C++, etc or if you are writing in a .NET language)

Cheers

curie SSC Rookie Points: 34 More actions · Answer 7

Ian, thank your for your details. Well, I may have to go with ADO. But right now, I just got "how to read and write BLOB data" topics via my research on internet, not "how to store it". I will spend time research it.

Thank you again