Another interesting post on the PowerDesigner group today. Mark wanted to add seed data to the after script on a table, which is fairly common (we do it all the time); but with a brilliant twist. The goal is to store the seed data script in an external file and have it end up in the DDL when we generate the database.
I thought this would be pretty easy by tweaking pieces of code I already, but there were a few gotchas.
1. You can’t directly read the contents of an external file within the PowerDesigner VBScript methods (as far as I know). It may be possible to write your own routine to process the file, but I found an easier (at least for me workaround).
2. My workaround for getting a the file contents ended up converting the file contents to hex (sort of). In a rather bizarre conversion, what I ended up with was a big string showing me the hex values representing the original file, but with a carriage return/line feed inserted periodically in a weird word wrap. Since VBScript doesn’t have a convenient way of converting hex back to ASCII, a Google search got me most of the answer, but I ended up having to strip out the carriage returns first.
Anyway, here’s the solution… suggestions for improvements are always welcome (I plan on adding a toggle so I can include seed data when I’m generating an empty database and omit it when I’m altering an existing database which already has the seed data present). As always, I’m doing this with an extended model file rather than stand alone scripts so I can get a little more control. If you need information on how to create and work with extended model definition files, see Sybase’s documentation here.
Step 1: Create a Table Method
Here’s my method:
Sub %Method%(obj) dim exDep, seedFile, fileLoc ' loop through all the extended dependencies on the table, looking for one with the stereotype of SEEDDATA for each exDep in obj.ExtendedInfluences if exDep.Stereotype = "SEEDDATA" then ' if we've found an extended dependency with the right type, get the target file set seedFile = exDep.DependentObject ' write out some handy diagnostic information output "Loading seed data for " & obj.name & " from " & seedFile.LocationOrName ' convert to an embedded file - can't find another way to get at the contents ' this does a crazy hex conversion fileLoc = seedFile.LocationOrName seedFile.LoadFileAsEmbedded(fileLoc) 'The file contents get converted to hex (not really sure why), when they're loaded 'there may be a better way to do this but I haven't found it yet. with obj .EndScript = seedFile.Content 'The hex contents get some lovely CrLf characters in them which cause the hex to ascii 'conversion to fail - strip them out. .EndScript = replace(.EndScript, vbCrLf, "") ' now run them through this handy hex2ascii function (thanks google) .EndScript = hex2ascii(.EndScript) end with ' now convert it back to an external reference seedFile.ChangeToExternalFile(fileLoc) end if next End Sub Public Function hex2ascii(hextext) dim y, num, char, value For y = 1 To Len(hextext) step 2 num = Mid(hextext, y, 2) char = chr(cint("&h" & num)) Value = Value & char Next hex2ascii = Value End Function
Step 2: Make the Method Accessible
Okay, this isn’t technically necessary, but it sure makes testing easier. So, I created a menu on the table meta-class and put the method above on it. Now I can add seed data to an individual table with a right mouse click.
Step 3: Create a Model Method
Again, I could have skipped the table, but we get a lot of versatility doing it this way. Here’s the method code.
Sub %Method%(obj) Dim myObj Dim mySel Set mySel = obj.CreateSelection mySel.AddObjects obj, cls_table, false, true For each myObj in mySel.Objects With myObj myObj.ExecuteCustomMethod("SeedData.LoadSeedData") end with Next End Sub
If you’re not familiar with selections and ExecuteCustomMethod, then please see my earlier posts. You won’t regret it.
Step 4: Make this Method Accessible
Again, not strictly necessary, but it’s only a few mouse clicks for a little extra versatility.
Step 5: Add the Model Trigger
The BeforeDatabaseGenerate trigger fires before you generate your DDL and is the piece that makes it all work automatically. Here’s the code:
Function %BeforeDatabaseGenerate%(model, fct) ActiveModel.ExecuteCustomMethod("SeedData.Load Seed Data") %BeforeDatabaseGenerate% = True End Function
Now, if you’ve done all the work above, here’s a completed PDM file that has an extended model definition with all the code above, pre-made. I know I could have put this at the top, but really, would you have learned as much?