469,594 Members | 2,220 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,594 developers. It's quick & easy.

Scripting text, image columns

We have remote users running MSDE entering information into a
database. To send the data back to the home office, we've written some
routines that export the data into SQL Scripts in text files:

DELETE <table> where KeyID=<x>
INSERT INOT <table> (fields) VALUES (fields).

We then zip up these scripts, and either email or ftp the ZIPs back to
the home office, where a program opens them up and simply "runs" the
SQL statements.

The new version of my database application contains both TEXT fields
(containing rich text) and IMAGE fields (containing file attachments
like JPGs or excel spreadsheets). I'm worried that our SQL Scripter
engine (which we really like) is going to choke on the text and image
fields. Can anyone suggest a similar method to send the SQL data
around?

thanks
matt tag
Jul 20 '05 #1
4 3142
matt tagliaferri (mt******@cleindians.com) writes:
We have remote users running MSDE entering information into a
database. To send the data back to the home office, we've written some
routines that export the data into SQL Scripts in text files:

DELETE <table> where KeyID=<x>
INSERT INOT <table> (fields) VALUES (fields).

We then zip up these scripts, and either email or ftp the ZIPs back to
the home office, where a program opens them up and simply "runs" the
SQL statements.

The new version of my database application contains both TEXT fields
(containing rich text) and IMAGE fields (containing file attachments
like JPGs or excel spreadsheets). I'm worried that our SQL Scripter
engine (which we really like) is going to choke on the text and image
fields. Can anyone suggest a similar method to send the SQL data
around?


Well, the obvious tool would be replication, presumably merge replication.
But merge replication is for the faint of heart, and if you have your
scripting solution working, you may find that extending it so that
it handles text and image data is relatively simple affair.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
> Well, the obvious tool would be replication, presumably merge replication.
But merge replication is for the faint of heart, and if you have your
scripting solution working, you may find that extending it so that
it handles text and image data is relatively simple affair.


Agreed. I use Merge replication in another application where people
take data on the road with them for short periods, but come back into
the office. I'm leery of using it in situations where the remote users
are always on the road, which is the case in this new application.

I've tried taking the results of a "select * from <t>" query that
included an image column, altered it in notepad to be an "insert into
<t>" with the hex representation of the image data, and this works.

I just need to figure out how, in code, to turn the image data into
the hex representation, and I think I'll be on my way.

Any ideas how to do that?

matt tag
Jul 20 '05 #3
I would suggest you use XML. Importing XML to/from a database is
really, really easy.
Jul 20 '05 #4
matt tagliaferri (mt******@adelphia.net) writes:
I just need to figure out how, in code, to turn the image data into
the hex representation, and I think I'll be on my way.

Any ideas how to do that?


There is an undocumented UDF that you can find in master which performs
the task. I'm not sure that it handles image though.

And in any case, I would definitely do the binary-to-hex translation
in client code. That would be more effecient.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Steel | last post: by
3 posts views Thread by JOSEPHINE ALVAREZ | last post: by
4 posts views Thread by Arif Çimen | last post: by
9 posts views Thread by shapper | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.