Communicating data with other applications? Any suggestions?

[zachattack05]

I am working on a project that stores it's data in serialized files on the hard drive in a custom file format. The server end of the application accepts TCP connections from clients and returns data to the clients based on the query, it's kinda like a light weight, custom SQL server, but no where near as complex.

I have been asked to make the server available to outside applications, such as Microsoft Word for the purposes of mail merges.

The question I have is, instead of implementing a custom ODBC driver or something insanely complex and beyond my expertise, I was wondering if it was possible, easier, or even legal, for the server application I have to "pose" or present itself as a SQL server, or something, and actually accept connections from remote computers who setup the mail merge to retrieve data from a SQL Server? So if a client machine has a SQL ODBC driver already installed, they can select it, type in the connection information to the server running my server application and have my application accept the connection, process queries from the driver and return the data it requested? Kind of like a mini-SQL server?

The only other thing I could think of is to have an option in the server controls to periodically dump the data into a csv file or something and network users can connect to the csv file to get data...I just seem to see file permission errors amok with that choice and really don't want to head down that path.

I would create my own ODBC driver, but C++ is beyond me, C# is what I know and I just can't see myself spending time learning C++ just for the sake of creating a driver that, from what I have read, is not the easiest thing in the world to do.

Any suggestions?

[mcriscolo]

Other than the details you gave in your post (so I don't know the *real* complexity of your app, or your budgetary constraints), might I suggest that you actually convert the data for your app to use a database? That is (and again, I'm not privy to the reasons why a custom format may be needed), developing a pseudo-ODBC driver or a server process that mimics the wire protocol of SQL Server (TDS, or Tabular Data Stream) seems like a whole lot of work. The time you spend developing, debugging, and then maintaining such a construct seems way more than what it would take to convert your data to use a database.

If the clients expect a database, then give 'em a database.

That being said, if a database just isn't going to work for you, there are some open source projects you may be able to look at to "borrow" some code - FreeTDS (though that's mainly for Unix/Linux programs that need to access a SQL Server database; and it's in C), unixODBC is an ODBC driver/manager developed in C.

Since you mentioned C# (probably running on Windows), there is a free edition of SQL Server 2008 (Express Edition) - it has some limitations, but it may be something you can try out.

Check this link out http://www.thefreecountry.com/sourcecode/database.shtml for a list of database projects where source may be available.

Good luck!

[zachattack05]

Hi Mike,

Thanks for the reply. The custom file format and server app is/was, used for security reasons and to prevent tech support nightmares. SQL servers, while they are secure and efficient when setup correctly can cause tech support nightmares and finger pointing (the problem isn't our application, its your server setup! Call Microsoft or read blah blah blah) you get the idea.... A standard business user may have 5 or 6 computers accessing the server at any given time, and a SQL server, while efficient, can be a problem for those clients who like to "tinker" with it, leaving us to find out what they tinkered with and fix it if it stops working...and god forbid they lower security and data gets stolen, it makes our application look bad even if it wasn't our application's fault.

The other problem we discussed and was a large factor for deciding against a SQL server was because of the lack of control on our end. If the client already has a SQL server running on their network and they wish to install our application, there could "possibly" be conflicts with other databases (names most likely) and possibly user permission problems. With a SQL server controlling permission to access certain data, a user is required to setup users on the server for everyone that could access the data. Another concern we had was our general database design. We would potentially need multiple databases and multiple tables and each would have to have configurable permissions. If the SQL server didn't provide enough permissions because of a configuration error or because the user is trying to control access to certain parts of a table but can't, the client computers application would have a fit. It was ultimately decided that a custom server was needed and would serve our purpose better. Not to mention having to constantly watch for any major changes to the releases of the SQL server (which is out of our hands) and try to make sure our application works well with it could be a problem.

The other thing we considered was ease. SQL servers require a great deal of configuration to make them secure and accessible to everyone who needs access. With our server application, we just need to make sure the ports are open on the firewall and communication can take place. A true SQL server is sorta...overkill. But maybe I'm wrong.

Also by requiring the client computers to access a "server" instead of a file data source, introduces an extra set of security. If everything was stored in an access database file and the clients could just connect to it as a data source for merging, they can also copy it to a thumb drive and walk out the door with it. Not something we want to worry about. The server application stores the data in a file, but the file is not shared, it's only accessible on the server workstation and has an option to encrypt the data files (unless the network admin makes the folder shared...which would be stupid).

Either way, I'd like to find some way of having the data shared through a server type protocol (not a shared file data source). The protocol would not need to be complex, it just needs to authenticate a user (which is already happening, just with a different protocol) and present the connecting computer with the "tables" or "databases" it can access. These tables and databases of course would be virtual, it's not a real SQL server.

I dunno, this seems like it's going to be a nightmare any way I approach it. Any other suggestions or resources? Is our worry about security and tech support unfounded?

*Sigh* off to the grind again I guess...

[mcriscolo]

zachattack05,

To answer your last question first - no, security and tech support issues are never unfounded. Once you deliver an application to a user, you have to support it. And you will also have to make sure that only authorized users access the system.

I may have misunderstood part of your explanation of the app - you will be delivering the "server" app to your customers? I was under the impression that the server would remain with you (or your company) and the clients would access it over the network. Of course, if you are packaging up this solution, then the decision to include or not to include a full-blown database is a big one.

If I also understand part of the use case for the clients, you would want to be able to access the "server" directly from programs like Word or Excel? Hmm... If I had to do that, I'd open the can of worms with the database. While there is a lot of management that comes with it, I still maintain that it would be easier and take less time than trying to develop a wire protocol mimic of SQL Server or ODBC. You could always lock down the security in the database, and also issue the standard warning that if the customer monkeys with the database that you're not responsible, blah, blah (even though you and I both know you really are... But maybe you can beat some support $$$ out of them for the incident!). But the data access capability would be way above what you could possibly program in a reasonable amount of time.

Here's a link to a discussion about someone who looks like they are trying to do the same thing you are.

http://stackoverflow.com/questions/335008/creating-a-custom-odbc-driver

All in all, I think I'd wrestle the database support issues, rather than the two-pronged issue of developing the driver AND supporting it, when the support could also mean figuring out if it's a coding error or a data issue.

Hope this helps. Good luck!

[zachattack05]

Hey, here's a thought...What about a COM addin or something for Word? We could have our own tab on the ribbon and custom commands that would communicate with the data server...that might be easier and we wouldn't have to imitate any sort of protocol, we can continue to use our own.

What do you think? I think I might suggest that instead of trying to deal with the built in MS Mail Merge feature.

[mcriscolo]

That may be a way to do it. The COM addin could handle the communications, and some client-side macro code (VBA) could take the results from the addin and do something meaningful with it in a Word doc (like create a new doc that has, for example, the label data needed to perform a mail merge).

Looks like you may even be able to use Visual Studio to do Office automation development - check this out: http://msdn.microsoft.com/en-us/library/bb157892.aspx - you may be able to develop your client side library as a DLL or an Add-in, and then call it from Word via buttons and get it do you what you need.