Temp table support, and table as a variable

As was sort of hinted at on the forums today, we will be adding temp table support to VistaDB 3.3.  I got a number of emails from people asking if it was true (and could they test it), so I wanted to write up a brief entry about it for everyone. VistaDB 3.3 will be adding User Defined Functions (UDFs).  This was sort of a late addition to our todo list because we really didn’t think we would get to them this round of changes. 

After adding the TSQL Procs it was decided to go ahead with the UDF code after all.  Once UDFs were in the code we realized something else though.  The most important feature of a UDF is that it can return more than just an integer (Stored Procs can only return an integer, but they do support OUT parameters). During the declaration of a function you can state what it will return with RETURNS scalar_return_data_type. 

One of the most common return types is a temp table (also known as a table variable to some).  The team then decided to look at how hard it would be to add support for these temp tables.  It turns out that it was not that big of a change (well, compared to my original fears).  So we decided to put it into this release. We will probably not be putting cursors into this release though.  The guys are looking at the SQL Server implementation to get some ideas of the complexity.  It will likely ship in a 3.4 or later build.

One more beta or release candidate

The guys are still completing the implementation of TEMP tables, so I can’t really comment too much on what types will be supported or their syntax yet.  But this does mean we will have to add another cycle to our beta tests.  Since this is a major new feature we need to get one round of user testing before we sign off on it as complete.  We may just call this one a release candidate since we feel that all of the features are complete, but we need some testing from you guys on what you think.  By changing it to a release candidate the 3.3 customers who are not subscribers could also test it, and we want to get as much testing in this version as possible.

Update on remote updates

And just a quick update on the Subversion / remote updating idea.  I have not given up on it yet, but I may go with a method we have used at Emerald for years.  The basic idea is that a client version and key is transmitted to a server asking for updates.  If the client is eligible for updated the server transmits back a manifest of the files the user should have on their machine, and the timestamp / CRC codes for each file.  The local updater then downloads any files that don’t match the server versions.  It is not as elegant as a full blown version control system, but it does work. 

We have been shipping updates to remote servers this way for over 7 years.  It is fault tolerant (the web services are hosted in multiple data centers). So I may implement this form of updates for now, just so we can get something implemented in 3.3.  We can continue to look at the Subversion repository going forward.  I was even thinking of a way to avoid subversion DLLs on the client by leaving them on the machine with the web service.  The web service would then add the version history information, and let a client specify what version it wants.  The web service could then check it out and return it that way.  It would avoid some of the messy issues I am running into with Subversion.