Creating update scripts through Packager API

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

Creating update scripts through Packager API

Postby AvonWyss » Mon Nov 03, 2008 2:29 pm

I'm trying to write a small tool (to be used on our build server) which should create a migration script for two different versions of a database.

However, I would like to get the update scripts only; I'm not looking for a C# or EXE type project. Basically, I'm looking to get the same scripts one can review when using the UI during the packaging process.

Since the packaging API is quite small, I'm afraid that this functionality has not been exposed through the packaging API. So how can I do this? If the packaging API does not expose it, is there some Compare API and Data Compare API which can be used to achieve this?
AvonWyss
 
Posts: 30
Joined: Wed Oct 29, 2008 4:45 pm

Postby Michelle Taylor » Tue Nov 04, 2008 12:40 pm

The API should support outputting the update script - just set the OutputType to Script in the PackagerEngine constructor.
Michelle Taylor
 
Posts: 529
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software

Re:

Postby AvonWyss » Tue Nov 04, 2008 2:17 pm

Michelle Taylor wrote:The API should support outputting the update script - just set the OutputType to Script in the PackagerEngine constructor.

In the API V7.0, OutputType had only the values Executable and Project. Thank you for pointing out the Script option, which I now have tried to use. However, I found it odd that it still required the template directory to be valid (what are the C# templates for when only creating scripts?).

Anyways, I still do not get a script file... here's my code snippet I'm using for testing (only schema migration, no data):
Code: Select all
using (Database oldScripts = new Database()) {
   Console.WriteLine("Reading old schema version scripts...");
   oldScripts.Register(Path.GetFullPath(commandLine.OldPath), scriptDatabaseInformation, options);
   using (Database newScripts = new Database()) {
      Console.WriteLine("Reading new schema version scripts...");
      newScripts.Register(Path.GetFullPath(commandLine.NewPath), scriptDatabaseInformation, options);
      Console.WriteLine("Finding differences...");
      Differences differences = oldScripts.CompareWith(newScripts, options);
      Work work = new Work();
      work.BuildFromDifferences(differences, commandLine.GetDatabaseOptions(), false, outputPaths);
      Console.WriteLine("Creating migration script...");
      using (PackagerEngine packager = new PackagerEngine(@"C:\\Program Files\\Red Gate\\SQL Packager 6\\SQL Packager Code Templates\\C#\", Environment.CurrentDirectory, "test", work.ExecutionBlock, new ExecutionBlock(), OutputType.Script)) {
         packager.Package();
      }
   }
}
AvonWyss
 
Posts: 30
Joined: Wed Oct 29, 2008 4:45 pm

Postby Michelle Taylor » Tue Nov 04, 2008 8:05 pm

I'm investigating why the Script option isn't quite working at the moment - meanwhile, hopefully you can work around the problem by calling the command line from your code?

I'm sorry about the problem with the API.

BTW, your code seems to be missing a step - you need a line that looks a bit like:

Code: Select all
work.GenerateSql(differences, null, Options.Default, false);


after your line which looks like:

Code: Select all
work.BuildFromDifferences(differences, Options.Default, false,new WriteToFileOptions());
Last edited by Michelle Taylor on Wed Nov 05, 2008 2:40 pm, edited 1 time in total.
Michelle Taylor
 
Posts: 529
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software

Re:

Postby AvonWyss » Tue Nov 04, 2008 10:47 pm

Michelle Taylor wrote:meanwhile, hopefully you can work around the problem by calling the command line from your code?

My understanding is that the SQL Packager (non-API) can only be run against databases, and not against scripts.

Michelle Taylor wrote:BTW, your code seems to be missing a step - you need a line that looks a bit like:

Thanks for the hint, I'll try that.
AvonWyss
 
Posts: 30
Joined: Wed Oct 29, 2008 4:45 pm

Re:

Postby AvonWyss » Wed Nov 05, 2008 2:12 pm

After adding the missin GenerateSql call, I now get an error message in a message box:
Code: Select all
Output is not a Project or an Executable
Redgate.SQLPackager.Engine.PackagerException at d.b()

(I would have loved to attach a screenshot, but I cannot find the option to do so in this forum.)

Even worse, this message box is opened by the API, not by my code, which may make the application hang if run on a server without GUI (such as a buld server...) - that should certainly not happen in a non-visible API!

Can you please provide me a working sample to generate the SQL update scripts?
AvonWyss
 
Posts: 30
Joined: Wed Oct 29, 2008 4:45 pm

Is there a solution

Postby acaterino » Fri Nov 19, 2010 4:25 pm

I'm getting the same message that it is not a Project or Executeable.
acaterino
 
Posts: 3
Joined: Fri Feb 12, 2010 11:00 pm


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests