embedding /PRESQL & /POSTSQL

rbennet8

Other than a .bat file, is it possible to embed the /presql & /postsql with the compiled executable? I would like to be able to employee just one executable and at most the two SQL scripts.

Brian Donahue

Hi,

This is smething that I thought about, basically being able to put custom SQL into the package as an an additional embedded resource. The only way I could come up with, after lots of consultation, was to disassemble the executable, embed the SQL script as a resource, and recompile it.

The level of complication necessary to do this resulted in a simpler solution of the /presql and /postsql arguments to get the functionality out there on a shorter timescale and to avoid complications.

johnpaulcook

I have no idea what /presql and /postsql are, which indicates a problem with the documentation. I started SQL Packager and opened up the Help. I also opened up the help from the shortcut The SQL Comparison and Synchronization Toolkit. Using the Index for both help files resulted in no hits for any of these strings

/presql
/postsql
/makedatabase
presql
postsql
makedatabase

All flags and switches need to be easily found in the help.

Now, back to the original post. I add custom SQL to every package. I add a version check TSQL script to the first resx file. I add a cleanup TSQL script to the last resx file. Then I build a new executable.

Brian Donahue

Hello,

These features were added at the late minute so the documentation hadn't been updated. If you run the package and click the 'more info' button, then the 'Command-line options' tab, you will see the usage.

Otherwise, it's okay to tell packager to create a c# project, add an embedded resource that's a SQL file, and run that SQL file using Packager's ExecuteSql method in the code.

Note that trying to add anything onto the Package's existing resource in the project is asking for trouble! The resource is an 'executionblock' object and not a plain SQL resource.

rbennet8

Where would I find more information about creating a SQL script as an embedded resource so that I could add it to my project?

Brian Donahue

Hello,

That is a feature supported in the Visual Studio Environment. You can add a new file to the project, make it a plain text file, then change the compile type to 'Embedded Resource' in the file's properties. You can put some SQL code in this file. To read the'file' (remember after the compile it's an array of bytes inside the assembly!) back inside the project's code, you can code up a resource reader function to return the resource as a string:

/// <summary>
		/// Return a string representation of an embedded resource
		/// </summary>
		/// <param name="ResourceName">The name of the embedded 'file'</param>
		/// <returns>A string of SQL code</returns>
		private string GetSql(string ResourceName) 
		{
			Assembly asm=Assembly.GetExecutingAssembly();
			Stream stm=asm.GetManifestResourceStream(asm.GetName().Name.Replace(" ", "_")+"."+ResourceName);
			StreamReader reader=new StreamReader(stm);
			string SqlCode=reader.ReadToEnd();	
			reader.Close();
			
			return SqlCode;
		}

rbennet8

Thank you.
I also found that I can replace the string.empty with my SQL file name in the packageexecutor.cs file for m_presqlfile. Since these are unique for each executable, it works quite well for our needs.

m_PreSqlFile=string.empty;

m_PreSqlFile="presql_pd0008.sql";