Insert into IMAGE column

I need to insert binary data into IMAGE column with SQL.

I cann't use ADO, ODBC, OLE DB or DB Library.

I know, that I have to use INSERT INTO or WRITETEXT or BULK INSERT.

My question is this:
In my application I've binary data stored in variable. I'd like to use SQL to insert this data into SQL server. But how? I cann't use sql="INSERT INTO MyTable (ImageColumn) values (" & MyBinaryValue & ")". This failed.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Don SmithCommented:
Generally, it is a three step process:

1.   INSERT a row containing a TEXT column (for example, txtData) into the target table.  The value of the TEXT column for this INSERT can be anything, like 'placeholder'.  This causes SQL Server to establish the first data page.  

2.   SELECT the pointer for this new row's TEXT column's datapage with a query like "SELECT TEXTPTR(txtData) FROM tablename WHERE ... (some condition that lets you identify the row you just inserted, like its key.)  Get the resulting one-row single column value into a variable, depending on your application called something like ptrVal.

3.   Finally, execute a query like "WRITETEXT tablename.txtData ptrVAL ptrYourBinaryData".

Incidentally, I think your database has to be in "Select Into/Bulkcopy" mode for this to work.  Make sure the production environment will allow this.  Some don't.

This is the general approach.  You can look up WRITETEXT, etc. in SQL Server Books Online if you have it.   the tricky part depends on what language you use and whether you are using ODBC, ADO, etc.  If you are in Visual Basic, you will have to convert the location of your data to a C-like pointer, for which reference to Dan Appleman's excellent book on using the C/C++ API in Visual Basic is one place to start.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.