Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

varbinary(max) filestream - how to insert/reference file location?

I have the below table - when I use the below insert - and then set a full text index - it is apparent, it is just storing the file name or something as when I check the indexed words - only the document name is indexed - so my question is - when trying to use filestream and varbinary(max) - is the simple example below correct?

CREATE TABLE cptDocuments(
  [docID] [int] IDENTITY(1,1) NOT NULL,
  [document] varbinary(max) FILESTREAM NULL,
  [docExtension] varchar(30),
  [RowGuid] UNIQUEIDENTIFIER NOT NULL  ROWGUIDCOL UNIQUE DEFAULT NEWID(),
CONSTRAINT [PK_cptDocuments_docID] PRIMARY KEY CLUSTERED
(
  [docID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO cptDocuments(Document, [docExtension]  )
VALUES (
CAST('C:\inetpub\wwwroot\PDE\CPTAssistant\Archives\Updated Reporting Instruction for Hydration Infusion (Code 90760).htm' as varbinary(max))
,'.htm'
)
0
tbaseflug
Asked:
tbaseflug
  • 2
  • 2
1 Solution
 
tknudsen-qecCommented:
Short answer, no.  

INSERT INTO cptDocuments(Document)
    SELECT *
    FROM OpenRowSet(BULK N'C:\inetpub\wwwroot\PDE\CPT....', SINGLE_BLOB) AS Document

You can also drop it into a variable thusly:

DECLARE @mybinary VARBINARY(MAX)
SELECT @mybinary = BulkColumn
FROM OPENROWSET(BULK N'C:\inetpub\wwwroot\PDE\CPT....', SINGLE_BLOB) AS Document

And then insert that.

HTH
0
 
tbaseflugAuthor Commented:
is there a way - say after I do that or during that - to put in a few other variables - such as 'file name here.htm' and '.htm' - the file name and the extension - as part of that insert statement in openrowset?
0
 
tknudsen-qecCommented:
Absolutely.  Its particularly clear with the second example, since you can insert your extensions PLUS the variable:

INSERT INTO MyTable (ExtensionColumn, BinaryColumn) VALUES ('.htm', @myBinary)


But with the first example, that "SELECT *" only returns one column (BulkColumn).  You could also:
"SELECT *, '.htm' as extension"
if you like.  

If I'd been using both my braincells I would have used BulkColumn in both examples.
0
 
tbaseflugAuthor Commented:
Thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now