Include sql scripts in a VB6 application

Question

I am maintaining an old VB6 application, and would like to include SQL scripts directly in part of the project. The VB6 application should then extract the text of this script and execute it on the server.

The reasons for this approach are various - among others, we want to deliver only an updated executable rather than a complete update/installation package. Hence, the SQL scripts need to be compiled into the application just like a resource file. And, obviously, one has to be able to get at the content from code, in order to send it to the database server.

Does anyone have a good way to do this?

Answer 1

The simplest solution is to just create a VB module with the scripts as strings.

If you want to use a resource file instead, you can do that too. You can associate a resfile with a VB project (I don't remember how to do this directly in the VB IDE but the VBP file supports a ResFile32 parameter).

EDIT: It seems like the issue here is mostly about formatting -- you don't want to store SQL queries as one long string, but formatting the query nicely inside VB is tedious because you have to add quotes, add string concatenation operators to join the lines together, etc.

I would recommend placing the SQL in a text file and formatting it in whatever way you like. Write a script that will take the text and convert it into a VB module. The build process would be modified to always apply this script first before compiling the application.

For scripting, use your favorite scripting language; if you don't have a favorite scripting language, this is an easy enough task that you could do it in VB, C#, or any other language. If it were me, I'd probably use awk (gawk) or Python.

Answer 2

If you want to use a resource (.RES) to store your SQL, go to the menu:

Add-ins > Add-in Manager...

and select VB 6 Resource Editor. Configure the add-in to be loaded and to load at startup.

From the editor add-in, VB provides a simple interface to add resource strings. You will refer to these using the provided constant values. To load the strings at runtime, use the LoadResString function:

Public Const SQL_INSERT As Integer = 101
Dim strSQL As String 
strSQL = LoadResString(SQL_INSERT)

(replace "101" with the constant value of the string you wish to load)

Answer 3

Just another thought on your approach. Because I find myself tweaking the program's behavior or UI for customers I might be in the middle of a change that either is not ready or has not yet been tested and approved. So if I have properties that change from time to time, but I want to maintain control of, for instance connection settings to our ftp server, I will create a resource only dll exposing my properties and use a resource file in the dll to supply the values. When my network manager changes something on the ftp server I change the strings in the resource maanger, recompile the dll and release just the updated dll. I'm sure there are many more solutions, but that is how I do it. If you don't think you might have to change your SQL scripts at the same time you are changing you exe this probably only complicates your work. It has worked well enough for me that now this is pretty much standard for me.