Dave Noderer's Blog

November 2008 Entries

Emerging Business Showcase – Deerfield Beach, FL – 2008-11-07

Spending the day at the Emerging Business Showcase in Deerfield Beach, FL just north of Ft Lauderdale. It is put on each year by the Enterprise Development Corp. of South Florida (EDC)  (http://www.edc-tech.org/)  and strategically held just a few miles from my house and a block from my office, what planning!

The purpose is really just to get a bunch of venture capital, angel investors, lawyers and bankers together and the highlight is 14 startups giving  short (3 min) presentations / demos of their product and what they are looking for money and partner wise.

Christos M. Cotsakos, Ph.D., Founder, Chairman & CEO of Pennington Ventures, LLC is the keynote speaker.

I like it, he uses the term “Vulture Capitalists”. He is advising entrepreneurs to pay more attention to the people behind the money than the money itself.

Points out that Florida is one of the best small business environments in the country but near the bottom of availability of money.

DSCN0577

He is not a Microsoft supporter (did mention .net  but that Microsoft charges huge fees) and probably correct that increasingly more young people will use free tools no matter what Microsoft offers. Although I don’t think anyone gives Microsoft enough credit in the open space environment and pretty much all the tools are available for free… visual studio express, sql server express and now the http://www.microsoft.com/bizspark/ initiative to give startups lots of free software and support for free.

Shameless plug… our user group is a network provider in this program: http://www.fladotnet.com/MSBizSpark.aspx

Now he is complaining that Florida developers are all .net so they had to go to India to get php / mysql people. I feel like an Obama supporter in a room full of republicans.. probably true too… Heavy suit tie group, i’m one of the only people in civi’s.

Companies each get three minutes:

Accellogic - Simulation acceleration, hardware?? he is not really saying what the technology or product is. Looking for future partners.

AccuBreak - Pharmaceutical product to allow tables to be broken into smaller pieces in a reliable way.

Arkiva - Asset management, external hard drives, internet backups, documents, video tapes, etc. Digital storage and conversion.

CatalRoom - Online poker for people to play with each other, win prizes in demographically separate areas, golf, travel, etc.

CHS Pharma -Early stage, developing multiple drugs. Skin and pre-cancel skin conditions and another is an over the counter sun protection.

CuRNA -Scrippts Florida spinout. Drugs for heart disease and cancer. Based on RNAI interference, knocks down one specific cell type.

Cyclone Power - Modern steam engine, small compact power to weight ratio. Many fuels in. Burn in a centrifugal at low pressures. Range of product  80 watt to 1 mega watt. Late stage prototype. Available late 2009. Looking for $5 mil.

Evolux Transportation - Tech at Georgia tech, FIT aerospace and cs at FAU simulations for helicopter / tilt-rotor to use existing infrastructure. Need $5.5 mil immediately.

Linxter  -Secure and reliable communications. Cloud middleware. Looking for acquisition end of 2009.

radWebTech -Distributed world is dictating information sharing making managing many portals like facebook, myspace, etc all in one place. Data portability and interoperability. International Data Portability Association. Looking for $500k. http://www.dataportability.org/.

RoboVault -Self storage, deliver storage items to the user and biometric security. Storage is separate from access.  Transports from secure storage to access area. In Davie.. $60 - $100 mil to build more.

ScriptRx -Print electronic prescriptions, discharge instructions, especially in ER rooms and urgent care centers. New version has med records, drug dispenser.

Virsona - Give customized persona to automation / communications on the web. Help desk, customer service, licensing to 3d virtual worlds.

World Response Group - Green tech human hair, clean, sanitize, smart mat to allow eco friendly growing, polymat replacement. Need $500k

Ok, i need to talk about the lunch speaker talking about the new age of space exploitation.

Jeff Krukin (http://www.jeffkrukin.com/) is a space futurist talking about the existing companies in the private sector that are starting and some are making money.

A number of these i’m aware of but the http://www.rocketracingleague.com   i was not aware of. They are starting a NASCAR type of organization for atmospheric rocket racing. Just move the racetrack into the sky. Is a earth to moon and back race very far away??

Sub-orbital and even round the trips to the moon are in the works for private citizens.

Buy his book! –> http://www.jeffkrukin.com/NewSpace-Nation-2008/

 

DSCN0578

SQL Server 2008 Filestream and vb.net

I’ve become interested in a few aspects of SQL Server 2008. One is the new FILESTREAM datatype.

The basic idea is to have a SQL datatype you can use in queries and searches and is managed by SQL but which does not have any particular size limitation.

There are a number of articles on the multiple  steps it takes to setup a database  to utilize the new FILESTREAM features. I’m not going to cover that in this article. I highly recommend reading Paul Randal’s white paper on the subject, now published on MSDN: http://msdn.microsoft.com/en-us/library/cc949109.aspx. Both he and Kim  Tripp have lots of great SQL server content online at www.sqlskills.com. Paul also has some performance graphs of that will give you an idea of when to use FILESTREAM vs image or text.

Unfortunately there is very little on how to actually utilize the FILESTREAM from a programming standpoint. Most of them show how to utilize Win32 API and the SafeFileHandle, I only found one (I’m sure there are more) that talked about using the managed interface, SQLFileStream.  You can find that paper at: http://blogs.microsoft.co.il/blogs/bursteg , this was also all C#, not VB.

So I have created a vb project which illustrates using FILESTREAM… The presentation, visual  studio project and sql script can all be found here: http://www.fladotnet.com/downloads/SQL2k8TidBits_20081104.zip

One thing I found is that the old Text, nText and image sql types are being replace by varchar(max) and varbinay(max)  which all have a size limitation of 2 gb.

The FIlestream datatype is really just a flagged varbinary(max) column with a tag of FILESTREAM. The sample I've made uses a table declared as follows:

CREATE TABLE FSDemo.dbo.FSDemoTable
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
    [Description] varchar(50) null,
    [JPG] VARBINARY(MAX) FILESTREAM NULL
)

Besides the FILESTREAM tag on the column, each table containing a filestream  column must have a uniqueidentifier (guid) column.

After inserting a row (see the 1_3_InsertData.sql script) the program will need two things in order to access the filestream data.

First is the path as defined by sql server. This is a reference to the file (not accessible directly through normal system.io) that contains the data for the row/column selected. A simple query of this for the table above is:

Select JGP.PathName() from FSDemoTable Where ID = @ID

This will give you a path: \\PLOVER2V3\SQL2K8\v1\FSDemo\dbo\FSDemoTable\JPG\0925394D-79D7-41FB-B438-9EA85C0FA232

The trailing 79D7-41FB-B438-9EA85C0FA232 is the @ID above but you won’t find a file by that name.

The second piece of information is the transaction context. To utilize filestream for reading or writing, you need to establish a transaction and then get the transaction context, a 16 byte array back from the db to pass into the SQLFileStream call. You can see this in the 1_4_FSGet.sql script and it looks like this:

select JPG.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()  from dbo.FSDemoTable WHERE id = @id

Now for code… to write to a file stream the annotated code is below. I’m sure there are many improvements to be made but the idea is to illustrate the steps.

For this sample, a description of the file (photos) and the actual byte array containing the jpg file are passed to this routine for writing and an array of bytes returned after the read. Once  you get the path and context, the sqlfilestream operations are basically normal file IO.

Public Sub WriteFileStream(ByVal Description As String, ByVal SourceBytes As Byte())
    Dim cn As SqlConnection
    Dim tx As SqlTransaction = Nothing
    '
    Try
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Open a connection and start a transaction
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        cn = New SqlConnection(ConnectionStrings("conFSDemo").ToString)
        cn.Open()
        tx = cn.BeginTransaction
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Insert empty blob in the database
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        Dim cmd As New SqlCommand("FSInsert", cn, tx)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter("Description", Description))

        Dim r As SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow)
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Read the id and sql path back from the proc
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        r.Read()
        Dim ID As String = r(0).ToString
        Dim path As String = r(1).ToString
        r.Close()
        '
        ''''''''''''''''''''''''''''''''''''''''''''''
        ' Get the transaction token, required for filestream operations  should add this from the previous proc…
        ''''''''''''''''''''''''''''''''''''''''''''''
        '
        Dim cmd2 As New SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", cn, tx)
        Dim obj As Object = cmd2.ExecuteScalar()
        '
        Dim txCtx As Byte()
        txCtx = CType(obj, Byte())
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Open the special managed "SQLFileStream" and write the bytes
        '''''''''''''''''''''''''''''''''''''''''''''
        Dim fs As New SqlTypes.SqlFileStream(path, txCtx, FileAccess.Write)
        '
        fs.Write(SourceBytes, 0, SourceBytes.Length)
        fs.Close()
        '
        tx.Commit()
        '
    Catch ex As Exception
        '
        tx.Rollback()
        '
    End Try
    '
End Sub

 

To Read the image the code is:

 

Public Function ReadFileStream(ByVal ID As Guid) As Byte()
    Dim cn As SqlConnection
    Dim tx As SqlTransaction = Nothing
    Dim txCtx As Byte()
    Dim SQLpath As String
    '
    Try
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Open connection and establish transaction context
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        cn = New SqlConnection(ConnectionStrings("conFSDemo").ToString)
        cn.Open()
        tx = cn.BeginTransaction
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Retrieve Path and transaction context of the filestream
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        Dim cmd As New SqlCommand("FSGet", cn, tx)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@ID", ID)

        Dim r As SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow)
        '
        r.Read()
        SQLpath = r(0).ToString()
        txCtx = r(1)
        r.Close()
        '
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Read the file
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        Dim fs As New SqlTypes.SqlFileStream(SQLpath, txCtx, FileMode.Open, FileOptions.None, 0)
        Dim Buf(fs.Length) As Byte
        '
        fs.Read(Buf, 0, Buf.Length)
        fs.Close()
        '
        tx.Commit()
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Return the bytes
        '''''''''''''''''''''''''''''''''''''''''''''
        '
        Return Buf ' return the bytes
        '
    Catch ex As Exception
        '
        tx.Rollback()
        '
        Return Nothing
        '
    End Try
    '
End Function