Scripting text, image columns

[matt tagliaferri]

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

[Erland Sommarskog]

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

[matt tagliaferri]

> 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

[Mystery Man]

I would suggest you use XML. Importing XML to/from a database is
really, really easy.

[Erland Sommarskog]

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