Article: Images and SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Images and SQL Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-10-12 : 13:05:08

One of our most asked questions is "How do you store images (BMP, JPG, GIF, etc.) in SQL Server?" In this article we'll discuss the options, the pitfalls and try to point you in the right direction. I don't have all the answers yet but you should be able to get started looking in the proper places.

Article Link.

hwdev
Starting Member

17 Posts

Posted - 2003-08-05 : 12:46:08

I just joined the Forum and I am unclear about this topic.

I have a table in SQL Server that contains a BINARY Image Blob which is actually an entire XML transaction. I am desperately looking for a way in SQL Server to convert the Binary Blob to an XML String and then I need a way to parse out certain fields. I was able to use the following 'SELECT FORMMODEL FROM dbo.FORMMODEL
WHERE FORMMODELID = 10512 FOR XML AUTO, XMLDATA' but the result was a <Long Text> and I needed it to be the converted XML string.

I would appreciate any help!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-05 : 20:23:44

quote:
have a table in SQL Server that contains a BINARY Image Blob which is actually an entire XML transaction

?????

Besides having NO IDEA what an "XML transaction" is, I can't fathom how an image blob can be part of it either.

Anyway, if the image data is encoded and can be parsed out from the XML, you'll probably need to specify XML EXPLICIT in order to extract that data from the XML in the image column. SQL Server will probably not be able to parse just the image data, you'll have to use another parsing run outside of SQL Server to accomplish that.

I'd also seriously suggest that you NOT store image data, in any form, in a SQL Server database. As you know, it's extremely difficult to extract it in a usable form. It is much easier to store images as files on the file system and store the path to the file in the database instead.

hwdev
Starting Member

17 Posts

Posted - 2003-08-06 : 13:10:33

The Binary Image Blob is being stored in a SQL Server table by a Vendor and it's my job to figure out how to convert the Blob back into XML and then parse it to look for certain fields.

SELECT FORMMODEL
FROM dbo.FORMMODEL
WHERE (FORMMODELID = 10512) FOR XML AUTO

The above query produced the following;
<dbo.FORMMODEL FORMMODEL="dbobject/dbo.FORMMODEL[@FORMMODELID='10512']/@FORMMODEL" FORMMODELID="10512"/>

Now, how do I take the @FORMMODEL which is the Binary Imgage in the dbobject command and break it out to XML and then Parse it?

Thank You

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-06 : 13:15:13

Instead of XML AUTO, have you tried XML EXPLICIT as Rob mentioned?

Tara

hwdev
Starting Member

17 Posts

Posted - 2003-08-06 : 13:37:25

Raw and Explicit are not supported to address Binary Data.

Thank You

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-06 : 19:40:56

See if this helps:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28157

You can modify it to set an XML extension on the files. You just need to make sure the query will only return one row (otherwise the XML from two or more rows will be combined into one file)

You won't be able to parse the XML from SQL Server directly. The best you can do is extract all the XML into a file and have another process work on it from there. Remove the FOR XML clause from your query, just do a regular SELECT...FROM...WHERE.

And if you get a chance, smack the shit out of the vendor that set up that table, especially for not providing the functionality you need to work with the data.

If this doesn't work or becomes to unwieldly to use, you can also write some fairly easy VB/VBScript to open an ADO Stream and read the image column, then run your XML DOM methods on the results, or use XSL to do it. I just finished a project using ADO with XML and XSL, and it works very nicely indeed (wish it LOOKED better, but it works!)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-06 : 22:12:23

quote:

And if you get a chance, smack the shit out of the vendor that set up that table, especially for not providing the functionality you need to work with the data.

I enjoyed that one.

"And if you get a chance..."

*chuckle*

- Jeff

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-08-07 : 01:59:24

Hi there,

certainly this is one of the 'hottest' topics around.

And also certainly there are as much pros as there are cons. From my point of view it is best to first get a solid understanding of BLOB's, image data type and SQL Server.
The most comprehensive article I have found on this is here
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part3/c1161.asp

Cheers,
Frank

hwdev
Starting Member

17 Posts

Posted - 2003-08-13 : 13:42:06

In regards to the response from robvolk.

Are you suggesting to change my select as follows, and FORMMODEL represents the Binary Image Blob.

SELECT FORMMODEL into temptable FROM dbo.FORMMODEL
WHERE (FORMMODELID = 10512)

When I do this Query Analyzer I get the following:

FORMMODEL ------------------------------------------------
0x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0D0A3C666F72
6D2D6D6F64656C3E0D0A202020203C70616765206E616D653D22223E0D0A20202020202020203C7A6F6E652069
643D223131313832353122206E616D653D22454D504C4F594D454E545F494E464F223E0D0A2020202020202020
202020203C6669656C64206368616E67652D646174653D2230303A30303A30303A3030302220646973706C6179
3D227472756522206564697461626C653D227472756522206E616D653D22534253425F434B222076616C69643D
2274727565223E313131383235303C2F6669656C643E0D0A2020202020202020202020203C6669656C64206368
616E67652D646174653D2230303A30303A30303A3030302220646973706C61793D227472756522206564697461
626C653D227472756522206E616D653D22435343535F4944222076616C69643D2274727565223E413030333C2F
6669656C643E0D0A2020202020202020202020203C6669656C64206368616E67652D646174653D2230303A3030
3A30303A3030302220646973706C61793D227472756522206564697461626C653D227472756522206E616D653D
22435343535F44455343222076616C69643D2274727565223E414354495645202D20424342534B433C2F666965
6C643E0D0A202020202020202020202020

I assume this is the Hex representation and could I then us XML XPath to go thru and read the temptable to pick out certain fields and create other Tables which I will use later to report from??

<edit> to fix display </edit>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-13 : 19:44:16

Yes, that query would work (although you don't need SELECT INTO, just SELECT FormModel FROM FormModel).

Run the following from a command-line prompt:

textcopy /S server /U username /P password /T formmodel /D databasename /C formmodel /W "WHERE FormModelID=10512" /O /F C:\myfile.xml

That will create a file called "myfile.xml" that contains the contents of the FormModel column (change the server, database, user, password, etc. to match your settings)

Once the file is created, you can use your favorite XML parser to work on it. You may need to play with textcopy a bit to get it to extract correctly. Run "textcopy/?" by itself to get a list of the flags it uses.

The example you posted looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<form-model>
    <page name="">
        <zone id="1118251" name="EMPLOYMENT_INFO">
            <field change-date="00:00:00:000" display="true" editable="true" name="SBSB_CK" valid="true">1118250</field>
            <field change-date="00:00:00:000" display="true" editable="true" name="CSCS_ID" valid="true">A003</field>
            <field change-date="00:00:00:000" display="true" editable="true" name="CSCS_DESC" valid="true">ACTIVE - BCBSKC</field>

That's what the file should look like once textcopy copies it out.

hwdev
Starting Member

17 Posts

Posted - 2003-08-18 : 11:01:46

Can the textcopy be ran inside of SQL Server?

I would think I would want to do a Select INTO to create a temptable and then do the 'textcopy'???

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-18 : 19:06:45

You can run textcopy from a SQL Server stored procedure, but it cannot access a temp table (*maybe* a global temp table, but most likely not, and it's not worth it anyway)

You can call textcopy from a sproc using the xp_cmdshell procedure:

EXECUTE master..xp_cmdshell 'textcopy /S server /U username /P password /T formmodel /D databasename /C formmodel /W "WHERE FormModelID=10512" /O /F C:\myfile.xml'

Remember that the file you save it to will refer to the SQL Server's C: drive, not your local C: drive. You can substitute a UNC path for the output file however, as long as the SQL Server runs under an account that has permissions to that path.

hwdev
Starting Member

17 Posts

Posted - 2003-08-19 : 13:34:53

Let me make sure I understand I can just create a Stored Procedure with EXECUTE master..xp_cmdshell 'textcopy /S server /U username /P password /T formmodel /D databasename /C formmodel /W "WHERE FormModelID=10512" /O /F C:\myfile.xml'. This will return an XML file on the c: SQL Server drive and then I can use an XML parser to extract the data I need? Can the 'FormModelID = 10512' be 'FormModelID = IDNBR' because the FormModelID will be passed in from another source? For testing purposes how would I print the result of the above EXECUTE in Query Analyzer.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-19 : 14:25:06

When you say "result", do you mean the content of the XML file? You could run ANOTHER xp_cmdshell call with the following:

EXECUTE master..xp_cmdshell 'type c:\myfile.xml'

The output will appear in the QA results window.

You can pass a variable to your procedure and execute it like this:

CREATE PROCEDURE OutputXML @formmodelID int AS
DECLARE @cmd varchar(8000)
SELECT @cmd='textcopy /S server /U username /P password /T formmodel /D databasename /C formmodel /W "WHERE FormModelID=' + cast(@formmodelID AS varchar) + '" /O /F C:\myfile.xml'
EXECUTE master..xp_cmdshell @cmd

hwdev
Starting Member

17 Posts

Posted - 2003-08-19 : 16:42:38

Any ideas on the following?

I created the Stored Procedure named OUTPUTXML and it only contained the EXECUTE master..xp_cmdshell statement.

I executed OUTPUTXML and received the following error;

'textcopy' is not recognized as an internal or external command,
operable program or batch file.
NULL

Thank You

hwdev
Starting Member

17 Posts

Posted - 2003-08-19 : 16:53:13

Is the 'textcopy' unique to C++ or can it be used in SQL Server?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-19 : 19:13:41

My fault. You can put the textcopy.exe file anywhere, as long as you specify the (drive and) path to the file. The example given earlier would only work in textcopy.exe was on the hard drive of the SQL Server (it probably is, but in a folder somewhere).

Run this from Query Analyzer:

EXEC master_xp_cmdshell 'dir/s c:\textcopy.exe'

That will search the entire C:\ drive of the SQL Server, including all the directories. The results it returns will give you the path to where the file is. Either copy it from that path to C:\, or include the entire path in the xp_cmdshell call:

EXEC master_xp_cmdshell 'C:\Program Files\Microsoft\textcopy.exe /S server /U username /P password /T formmodel /D databasename /C formmodel /W "WHERE FormModelID=' + cast(@formmodelID AS varchar) + '" /O /F C:\myfile.xml'

Textcopy was written in C++, but it is a command line utility that can be run from any program that can execute command line utilities.

hwdev
Starting Member

17 Posts

Posted - 2003-08-20 : 16:36:48

I executed;
EXEC master..xp_cmdshell 'c:\Program Files\Microsoft SQL Server\MSSQL\Binn\textcopy.exe /S Server /U Userid /P password /T formmodel /D databasename /C formmodel /W "WHERE FormModelID=10512" /O /F C:\myfile.xml'

but still rcvd the following.

Any thoughts.

output 'c:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-20 : 16:39:43

You need to put the path in double quotes or use the 8.2 format. If Program Files is the only directory that is named like that, then use progra~1 instead of Program Files. Microsoft SQL Server, you might need to use micros~1 or micros~2, just depends on how many directories you have that start with micros.

Here's an example:

xp_cmdshell 'dir "c:\program files\"'

And another example:

xp_cmdshell 'dir c:\progra~1\'

Tara

hwdev
Starting Member

17 Posts

Posted - 2003-08-25 : 11:09:22

Could you tell me if textcopy can be written to memory instead of to a File? I was told the IO of writing to a file on a large scale could have a negative impact.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources