SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Images and SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/12/2000 :  13:05:08  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 11/10/2000 :  21:39:26  Show Profile  Reply with Quote
There's a much better way...

...using the new internet access and XML features in SQL Server. Specifically, the dbobject query

http://iisserver/virtualdirectory/dbobject/Employees[@EmployeeID=8]/@Photo

will retrieve the photograph of Employee 8 in the Northwind database.

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 12/19/2000 :  16:59:18  Show Profile  Visit graz's Homepage  Reply with Quote
Another link

is here: http://www.asptoday.com/articles/19990524.htm

Go to Top of Page

hwdev
Starting Member

17 Posts

Posted - 08/05/2003 :  12:46:08  Show Profile  Reply with Quote
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!
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 08/05/2003 :  20:23:44  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

hwdev
Starting Member

17 Posts

Posted - 08/06/2003 :  13:10:33  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36782 Posts

Posted - 08/06/2003 :  13:15:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
Instead of XML AUTO, have you tried XML EXPLICIT as Rob mentioned?

Tara
Go to Top of Page

hwdev
Starting Member

17 Posts

Posted - 08/06/2003 :  13:37:25  Show Profile  Reply with Quote
Raw and Explicit are not supported to address Binary Data.

Thank You
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 08/06/2003 :  19:40:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
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!)
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/06/2003 :  22:12:23  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 08/06/2003 22:12:47
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

Switzerland
413 Posts

Posted - 08/07/2003 :  01:59:24  Show Profile  Visit Frank Kalis's Homepage  Reply with Quote
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
Go to Top of Page

hwdev
Starting Member

17 Posts

Posted - 08/13/2003 :  13:42:06  Show Profile  Reply with Quote
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>

Edited by - robvolk on 08/13/2003 19:45:40
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 08/13/2003 :  19:44:16  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

hwdev
Starting Member

17 Posts

Posted - 08/18/2003 :  11:01:46  Show Profile  Reply with Quote
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'???

Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 08/18/2003 :  19:06:45  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Edited by - robvolk on 08/18/2003 19:08:11
Go to Top of Page

hwdev
Starting Member

17 Posts

Posted - 08/19/2003 :  13:34:53  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 08/19/2003 :  14:25:06  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

hwdev
Starting Member

17 Posts

Posted - 08/19/2003 :  16:42:38  Show Profile  Reply with Quote
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
Go to Top of Page

hwdev
Starting Member

17 Posts

Posted - 08/19/2003 :  16:53:13  Show Profile  Reply with Quote
Is the 'textcopy' unique to C++ or can it be used in SQL Server?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 08/19/2003 :  19:13:41  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Edited by - robvolk on 08/19/2003 19:16:18
Go to Top of Page

hwdev
Starting Member

17 Posts

Posted - 08/20/2003 :  16:36:48  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000