Hi,
I am using MSSQL Server as the back end database server, I need to do the technical feasibility on how to store and retrieve the video from MSSQL (SQL Server 2005) Database.
Can any one help me with how to store and retrieve the video from MSSQL database.
Thanks,
Kumar
You want to store the video as a BLOB? As in, in a table row?
I don’t think that’s a good idea, or if it’s even possible. There is no “video” data type anyway. Anyway… generally when it comes to images, sound, video… and well, anything multimedia, the common practice seems to be to store them in the File System.
If you need to save some of the file information (like a description, author/uploader, etc.), you can use both a table and the file system. Save the video itself in the File System in a folder of your choice (Ex: C:\inetpub\wwwroot\helloworld\myvideos\1\myvideo.mp4)
When you upload a video, you first generate an ID for it in your table and save whatever data you want, like a video description. You can then retrieve the ID of the freshly inserted row with the @@IDENTITY command, using either an OUTPUT parameter, or return a scalar SELECT, or whatever you want.
DECLARE @VIDEO_ID INT -- Variable to save the ID into
INSERT INTO myvideos (description) VALUES ('My new video') -- Insert the information about the video
SET @VIDEO_ID=@@IDENTITY -- This returns the ID of the recently inserted row.
Note that the above is not an OUTPUT parameter in SQL Server, just some random variable I created. If you want to use an OUTPUT parameter with a stored procedure, it would be something like:
CREATE PROCEDURE InsVideoInfo
@DESCRIPTION NVARCHAR(300),
@VIDEO_ID INT OUTPUT
AS
INSERT INTO myvideos (description) VALUES (@DESCRIPTION) -- Insert the information about the video
SET @VIDEO_ID=@@IDENTITY -- This returns the ID of the recently inserted row.
You can then retrieve the Output parameter with your server side code.
With that ID in your PHP, you can just create a folder for the video and upload it there, or change the video’s name to that ID, or any other way in which you would rather store said video.
Retrieving the video is simple. You can either first get the video from the file system and then look for its data, or get the database row and look for the video later.
If you get the video from the File System first, you just need to browse through the directories in your designated videos folder (“myvideos” in my example), find all of the folders/videos (in my example, I would search for folders), look for the table row with the folder/video ID (for example, the folder named “1”). I can then get the information about the video from my database through a simple SELECT query WHERE id=1, then just grab whatever video is inside that 1 folder, since I’m only saving one inside of it.
This is just one approach, but you can do it any way you want. For example, you could have one folder with all of your videos, save the name of the video in the database, and once you get the table row, you look for the video name in the videos folder, and just grab it. Of course, this would mean that each video would need a unique name. Perhaps you could append a "_<videoID>" at the end of the file name.
I hope this helps you.
Thanks for your help. I will check for this solution