How to query video from SQL Server into Perspective VideoPlayer component?

[ericmoulton]

I am storing images in a SQL Server as varbinary(max), and retrieving them into the source property of an image component with a scalar named query like:
select cast(’’ as xml).value(‘xs:base64Binary(sql:column("[tbl].[ImageCol]"))’, ‘varchar(max)’)
and the script transform:
“data:imagef;base64,%s”%value

That works great.
How can I do this with videos though? I don’t have a specific format\codec in mind - I’m flexible if there’s a solution.

Thanks for your help!
Eric

[andrews]

@pturmel would your blob module be of use here?

[pturmel]

Probably, if the video isn’t too large. Worth a try.

[andrews]

Automation Professionals' Blob Server Module

Automation Professionals is pleased to announce an alpha release of its new Blob Server Module. Available only for Ignition v8.1, here. The Blob Server Module provides a simple way to deliver images, PDF files, or other binary content from a database to your Perspective clients or other web applications. Without needing the WebDev module. All you need is a Named Query that has appropriate security settings and returns a single row with two columns: Content, and ContentType. Modest documentati…

[ericmoulton]

It works and it works great! Thanks!

In looking for some guidance from Google this morning, the overwhelming advice from the db community is to never, ever store media like this in a db, but to use a filesystem instead.

So… that’s that I guess.

Thanks for your help though

[pturmel]

@ericmoulton

never, ever store media like this in a db

This is why I said if "isn't too large". Media players accessing URLs will typically request specific byte ranges to avoid a monster transfer in one gulp. That gets converted into the appropriate filesystem offsets for efficiency.

Some DBs provide similar fractional BLOB transfer, but it isn't part of JDBC and Ignition can't expose it. So running the named query will grab the whole content, even if the client requested just a chunk. There's a couple trade-offs that apply:

• Allow caching of the named query. It'll run just once and the blob held in memory. Allows the client to request chunks without hammering the DB. But Gateway memory usage will skyrocket.

• Don't cache the named query. Your DB will be hammered, but memory will be returned quickly.

So, stick to small files. Or switch to WebDev.

[pturmel]

I just realized that there’s another option: segment your video into small chunks and stick those into your DB. The tools for that will produce a playlist file that you supply to clients from the main URL. Modify that file’s content to point at the individual chunks. Then you can use a short cache duration with that named query.