RETURN binary contents of a server-side file as IMAGE

Question

Post reply

RETURN binary contents of a server-side file as IMAGE

Justin Mecham

Valued Member

Points: 71
More actions
April 6, 2006 at 10:12 pm

#152895

Using xp_cmdshell, an application I wrote and ADO, I can insert any server side file into an SQL SERVER 2000 image field. I can then retrieve this file from the server and save it to a local file. This serves as a sort of roundabout one-connection file transfer method for me.
Rather than use this method, I would like to use a server-side procedure, extended procedure or dll call to return the binary data as an SQL image field value, with no table storage required.
What I would like to see:
EXEC sp_getbinary_from_file 'c:\fil.ext'
Returns scalar:
(no column name)
0x0394a....(or whatever binary representation of the file you like)
Any thoughts?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

chisholmd SSCrazy Points: 2490 More actions · Answer 1

I hope I understand the question.

Is there a reason not to simply store the path in SQL then use your script for access?

In my webapps that manage file up/dl's I usually insert a record containing all the file details, name, path, etc. My table contains an identity column so then I rename the file to that value.

Does that make sense in your case?

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Justin Mecham Valued Member Points: 71 More actions · Answer 2

I can already do what I want. But its roundabout.

I generate a unique (autonumber) column on the SQL server, with an IMAGE field (NULL). Then I call xp_cmdshell against an application I wrote that fills that NULL field with a file from the server. Then I make a third connection and download that SQLImage value as binary and stream it back out, client side, to a file.

What I want is a 1 connect/download solution.

EXEC xp_get_file 'server-side-file-loc'

That returns the same way a

SELECT imgfld FROM Tbl

I'm all for making some kind of extended assembly, if that's what I need.

chisholmd SSCrazy Points: 2490 More actions · Answer 3

Ahh. I am guessing that the application in question does not have direct access to the file system in question. So SQL is sort of serving as a proxy for access.

I'll think about that...

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Justin Mecham Valued Member Points: 71 More actions · Answer 4

Exactly what its doing. 🙂 No way am I opening up a file-sharing port for external users. 😉 I feel bad enough with my (modified) SQL ports open.

chisholmd SSCrazy Points: 2490 More actions · Answer 5

Couldn't you use a stored procedure to do all operations at once to a temp table or table variable?

In psuedo code.

Proc getFile

@fileSpec varchar(200)

set nocount on

1) create temp table with img col

2) insert file into temp table using xpProc

set nocount off

3) select out of temp table to the client

tmp Tbl destroyed at end of proc.

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Justin Mecham Valued Member Points: 71 More actions · Answer 6

Lets see if I can shed a little more light on how it works now...

Client-Side:

INSERT INTO File_Store(binData) VALUES(NULL)

@row_loc = (SELECT MAX(AutoN) FROM File_Store) (Yeah, I know that's wonky-I want to replace it because of that, and the many round trips)

EXEC master..xp_cmdshell 'c:\imgstorehouse.exe File_Store;WHERE AutoN = @row_loc;c:\fil.bin'

Basically what imgstorehouse.exe does is parse that string to retrieve the filename it needs to dump into the table that is named. Then it does that.

So now I have the binary data from c:\fil.bin in File_Store where the AutoN field = @row_loc

Simple to then retrieve it. But I'm sure you can see why I want to get rid of all that. 🙂

One method I tried, using VB.NET was to use xp_cmdshell to try and return the data with a Console.GetStandardOutput.Write(binary_data_array) inside of imgstorehouse.exe-didn't work out. It returned the data but in a format that didn't work-makes me think I could do that quite nicely with text files or something though. 🙂

Justin Mecham Valued Member Points: 71 More actions · Answer 7

Oh you are a very smart man. That will work excellently. I think. 🙂 Saves me some work too with the whole number generation and everything. I'll post the code when it's done-I doubt there's a huge market for it but it _is_ handy to be able to transmit whatever file you want across your SQL connection.

chisholmd SSCrazy Points: 2490 More actions · Answer 8

If your using .NET and this is a web app then you can access the file system directly without using iusr_

I found this out after writting a complex ASP webservice type app that used authenticated serverXMLHttp to proxy the actual file access.

file access with .ASPX is via the um.. network service account I think. Its worth a read and if applicable you can have secure file access without using SQL as a middle man.

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Justin Mecham Valued Member Points: 71 More actions · Answer 9

CREATE PROCEDURE procGetFile_bin

@FileSpec varchar(500) = ""

AS

SET NOCOUNT ON

IF OBJECT_ID('##tmp_table', 'U') IS NOT NULL DROP TABLE tmp_table

CREATE TABLE ##tmp_table(auton INT PRIMARY KEY IDENTITY(1,1), ImgFld image )

INSERT INTO ##tmp_table(ImgFld) VALUES(NULL)

DECLARE @Add_Str varchar(200)

SET @Add_Str = 'e:\ImageStoreHouse.exe mode2 ##tmp_table;imgfld;;' + @FileSpec

DECLARE @res int

EXECUTE @res = master..xp_cmdshell @Add_Str, NO_OUTPUT

SELECT * FROM ##tmp_table

DROP TABLE ##tmp_table

GO

Justin Mecham Valued Member Points: 71 More actions · Answer 10

contents of imagestorehouse, used for this project:

Sub Main()

Dim CommandLine As String = Microsoft.VisualBasic.Command()

If CommandLine.Substring(0, CommandLine.IndexOf(" ")) = "mode2" Then

Dim vars As String() = CommandLine.Substring(CommandLine.IndexOf("mode2") + 6).Split(";".ToCharArray)

Dim tbl As String = vars(0)

Dim fld_name As String = vars(1)

Dim whr As String = vars(2)

Dim fil As String = vars(3)

Dim sql_str As String

sql_str = "UPDATE " + tbl + " SET " + fld_name + "=@ImgData"

If Not whr = "" Then

sql_str = sql_str + " " + whr

End If

Dim sqlcon As New SqlClient.SqlConnection(ConnectionString)

Dim fs As New FileStream(fil, FileMode.Open)

Dim Data_byt() As Byte = New [Byte](CInt(fs.Length)) {}

fs.Read(Data_byt, 0, CInt(fs.Length))

Dim cmd2 As New SqlClient.SqlCommand(sql_str, sqlcon)

cmd2.Parameters.Add("@ImgData", Data_byt)

sqlcon.Open()

cmd2.ExecuteNonQuery()

sqlcon.Close()

Else

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 11

Maybe an issue, or maybe not. ##temp_table is a global table, so concurrent executes will share the same table. Perhaps it's just as well to make it a permanent table for all (eventual) concurrent calls to use the same 'scratch pad'...? Would save some overhead on create and drops, fwiw...

/Kenneth