MBase - VBScript/MSI Database

Note about error codes:
Some functions return an error number. Some return True/False. Some return an array. But wherever functions can return useful error information it will be returned in the ErrorString property. Most functions have MBase error codes as well as MSI error codes. For example, an invalid parameter might return an error number of 1 or 2, while a function that fails in a call to WindowsInstaller will have an MSI error number. In both cases there should be a descriptive text string returned in the ErrorString property if the function return is an error.

The latest update (7/2017) adds much improved error reporting. Windows Installer has the LastErrorRecord object, which often contains very exact information about errors. But the object is not officially documented. LastErrorRecord has at least 4 fields. The first is an MSI error code. That code corresponds to a description in the WI docs. The other fields provide additional info. For example, a failed table import error might tell what line the error was on. All of that has been incorporated into the MBase class so that, in many cases, checking ErrorString when a function fails will tell you exactly what the problem is.

Introduction

About Windows Installer and MSI Files

If you don't know about the Windows Installer system...

   A few years ago Microsoft developed a new system for installing software. The system is known as Windows Installer. Orca, Visual Studio Installer and current versions of InstallShield, Wise Installer, Advanced Installer, etc. are all different GUI programs to facilitate the creation of Windows Installer packages.

Problems with MSI from a software developer's point of view...

   Windows Installer is a very strange system. For the simple purpose of copying files and Registry settings during program installation, Microsoft has managed to create a stunningly complex and convoluted system. A Windows Installer installation file (.msi) is actually an SQL-like database of approximately 80 tables. (In addition, the MSI file usually contains the installation CAB file(s), but that is not necessary. The CABs can also be shipped separately.) An MSI installation can be created with nothing more than VBScript, but the MSI structure is so complex and so poorly designed that very few developers attempt to create their own installation files. Most people using MSI installers use some kind of program to build their installations.

   Actually, it seems likely that the Windows Installer system is difficult by design. By gradually moving to Windows Installer Microsoft is better able to control how software gets installed on Windows systems. For example, when Microsoft released SAPI 5, the speech API, the redistributables were made available only in the form of Windows Installer "merge modules". Developers who wanted to use SAPI 5 were forced to build an MSI installation, with no control - or even knowledge - of what went into it. The only other option was to dissect the merge modules in order to work out the details of how to ship SAPI 5 support. By creating such a convoluted system with Windows Installer, Microsoft went a long way toward discouraging the latter option.

   Another example of MSI design that appears to be specifically intended to cause difficulty: There is no direct way to discover the columns of a given table. There is a _Columns table that just lists all columns from all tables in the database. To find the columns for a given table, the entire _Columns table must be iterated. ... And it gets worse: The Type column of the _Columns table, which is necessary to discover the data type of specific column content, is undocumented. It is a "secret" column usable only by Microsoft insiders. Anyone who doesn't know about it is forced to use a very work intensive and awkward method. (Creating a View object for each table and iterating through the View.ColumnInfo properties.)

   Perhaps the most notable example of MSI limitations is the manner in which GUI dialogue windows are created. One might literally spend days setting up a simple, custom window. Each label, button, text field, etc. must be specified and cross-referenced across several tables, "created" by entering tedious x, y, width and height specifications in various table columns entries. As Microsoft says in their MSI help file: "it is recommended that package authors use the internal user interface provided."

...and advantages...

   There is one notable advantage to MSI installations: The MSI standardization provides 2 ways (via WindowsInstaller and via WMI) to document installed software in an orderly way. That may be attractive to system administrators. Although that advantage is somewhat limited: Both WindowsInstaller and WMI are incapable of returning information about any installed software except that which was installed via MSI.

   While a case can be made that Windows Installer is all but useless for its intended purpose of installing software, it is being used more and more by large software companies cooperating with Microsoft. From the customer point of view MSI installers at least have the advantage of being thoroughly transparent, given the proper tools. An MSI installer can be completely unpacked and documented. It can even be edited to change default installation settings.

   Other uses for MSI:

   MBase is short for "MSI database". The purpose of MBase has nothing to do with installing software. MBase is approaching Windows Installer from a different point of view:

• An MSI file is essentialy an SQL database (with a few added, specialized functions).

• Msiexec.exe, which handles MSI files, is essentially a database program with specialized functionality.

• The WindowsInstaller.Installer object provides script with very thoroughgoing access to the whole range of database functionality.

In other words, Windows Installer is a free, flexible SQL database program that is pre-installed on nearly all Windows PCs!

   The MBase VBScript class provides a set of functions that allow script to easily use an MSI database file without the need of knowing SQL. If you are familiar with SQL you can adapt the code in MBase for greater flexibility. If you don't know SQL and don't want to, you can still use MBase to efficiently use and manage an MSI database. The biggest advantage of writing custom script for use with MSI files is in filling a database initially. For example, the Zip Code Finder utility is made up of an HTA GUI and an MSI file with a single table that contains approximately 43,000 rows. In order to enter all of the zip codes into the database it was worthwhile to write a custom script for the job. (That script is included in the Zip Finder download from the JSWare website.)

The MSI Database: Using the WindowsInstaller.Installer Object

A basic description of what MBase is all about...

MBase is a database program written as a VBScript class and HTA webpage. The MBase class provides convenient translation functions that interface with the SQL-like structure of MSI databases. The functions are designed to translate the awkward syntax of SQL command strings into standard, Windows-style functions. No knowledge of SQL is needed to use MBase.

So MBase has two intended purposes: 1) The whole thing - the MBase Database Utility - can be used as a graphical database editor program that can read from and write to MSI database files (including software installers). 2) The MBase class itself can be used straight, or as a storehouse of VBS/MSI sample code, in order to carry out scripted database automation for purposes where a GUI is not desirable.

MBase Functions

A basic rundown of what MBase can do...

   MBase provides the basic SQL database functions without needing to know SQL or use awkward SQL command strings. Since an MSI uses a variant of SQL, the system is similar to the ADO object model. Using the WindowsInstaller.Installer object, an MSI database (any MSI file) can be loaded, unloaded, stripped of tables, and rebuilt. MBase has functions to create new MSI files, new tables ("recordsets") and columns ("properties"). There are functions to read, write, add, remove records (table rows) and record values ("fields").

   MBase is a very functional, but fairly simple, database front end. Windows Installer only provides a subset of SQL functions, and MBase is intended to be easy to use, so there are some limitations as written. (Since this is a script utility you can edit it as desired.) Limitations:

1) The first column in a table is a "primary key". The values for that column in each record must be unique. There are no other primary keys in tables.

2) Column values can be read or written in two ways: You can read or write all values in a column, or you can read or write all values where the value in another column matches a given value. Example: You can change all values in column "Size" to, say, 10. You can also change all values in column Size to 10 if and only if the value in column "Color" is "red". (This is the WHERE command of SQL.) The read/write functions do not get more complex than that.

3) MBase has 2 datatypes: integers and strings. A string may be up to 255 characters. Since SQL basically deals in strings, and since most datatypes are basically either text or a number, this approach seemed flexible while also being simple. A currency data type might have been a handy addition, but Windows Installer doesn't have such a type. (See the AddTableCustom function for an option to have more control over table structure.)

   You can always edit the script to use more datatypes. MBase is intended to provide the basics needed without getting into long, involved, SQL command strings. But for some specific purposes it may be better to write a new script or edit MBase. Functions could also be written to do things like use the dynamic _Streams table to store binary data. (The dynamic tables in the Windows Installer system - in particular the _Streams, _Columns and _Tables tables - are inherent to the system. They are not destroyed when an MSI file's tables are deleted.)

   Since MBase has only 2 data types, and a normal MSI file may have up to 25, it's best to use a blank MSI file, or one that has been stripped of tables and rebuilt, for your database. MBase is using the "secret" column Type property of the MSI _Columns table to distinguish data types. All string-based types will be considered to be a string of type CHAR(255). All other types will be treated as type LONG (4-byte integer). When you create table columns (unless you're using the AddTableCustom function) the parameter IfNumeric will specify whether the column values will be string or integer. Writing a non-numeric string to an integer value will result in an error.

As for editing MSI program installer files: That should generally be feasible. The varied datatypes in MSI tables should not be a problem when entering data. MBase is only limited in how it can define new table and column data types. But MBase is not designed specifically for editing software installation MSIs, and it's easy to make permanent mistakes with SQL. ...So be sure to make backups before starting.

Back to Top

Function LoadDB(MSIFilePath)

• Load a database from MSI file.
ex.: Ret = MB.LoadDB("C:\windows\desktop\base2.msi")

MSIFilePath - Full path of MSI file to load.
Return: - 0-success. 1-bad path. other: MSI error code. If return is non-zero, ErrorString property returns error description.

Back to Top

Sub ReLoadDB

• Reload current database.
ex.: MB.ReLoadDB

Notes: When a database is loaded, MBase keeps track of changes, and changes made through MBase are committed to the database as they're made. ReLoadDB is provided as an extra measure. It refreshes the data by unloading and reloading the database. ReloadDB should not normally be needed, but it might be handy occasionally.

Back to Top

Sub UnLoadDB

• Unloads the MSI database.
ex.: MB.UnLoadDB

Notes: The class will unload any loaded file when it is terminated, but UnLoadDB is a neater way to make sure that the object is released.

Back to Top

Function MakeNewDB(Path)

• Creates a new MSI database file.
ex.: Ret = MB.MakeNewDB("C:\new.msi")

Notes: Creates a new, blank MSI file. Caller must be sure that path is valid and that file does not exist. If file exists it will be overwritten without warning.

Return: Returns 0 on success. On error, ErrorString property will contain error description.

Back to Top

Sub ClearSummary

• Clears all Summary strings from the MSI.
ex.: MB.ClearSummary

Erases the values that display in a file's Properties menu, Summary tab. ClearSummary is used to strip data from an existing MSI file.

Back to Top

Sub SetSummaryValues(sTitle, sSubject, sAuthor, sNotes)

• Sets 4 Summary strings in the MSI.
ex.: MB.SetSummaryValues "Address Book", "Personal Addresses", "", "Contains email addresses"

Parameters - Summary strings to set. There are 4 options: "Title" "Subject" "Author" "Notes"

NOTE: As of this writing, the SetSummaryValues function is not working properly. Despite having some luck in erasing and re-writing new summary values, the result seems to be unpredictable. MBase will return the 4 values with GetSummaryValue, but seems to usually fail when writing new values.

Notes: Windows Installer uses a large number of "Summary" strings that will show in the Summary tab when the file is right-clicked and the Properties menu clicked. MBase deals with 4 of the more common and potentially useful properties. Note that setting properties with SetSummaryValues will also erase other values connected with a specific installation. This function assumes that you want to re-use an MSI file and erase its original content.

SetSummaryValues cannot create new values where none exist. It can only edit them. The scripting object model for MSI does not seem to provide any easy way to create new Summary entries.

Back to Top

Sub ClearAllTables

• Clears all data and tables (excpet summary strings) from an MSI file.
ex.: MB.ClearAllTables

Notes: ClearAllTables deletes all binary data stored, then deletes all permanent tables. It does not remove summary strings (see above). Also, in some cases ClearAllTables may not remove all data from the file. In general, it will probably make more sense to just create a new MSI rather than clean out a program installer. MBase has a function MakeNewDB that will create a new, blank MSI file.

Back to Top

Function AddTable(TableName, ColumnString)

• Adds a new table to MSI database.
ex.: Ret = MB.AddTable("NewTable", "Col1 s, Col2 i, Col3 s")

AddTable adds a new table to the database. One or more columns can be added when the table is created. A specific limitation of MBase is that the first column in a new table must be a primary key, while no other columns can be primary. In other words, column 1 cannot be null. All records must have a value in column 1.

TableName: Name for new table.
ColumnString: A string that specifies columns to create. At least one column must be created. Syntax is: column-name x
"x" must be "i" to represent an integer value, or "s" for a string value. Each column name must be followed by a space, then an "i" or "s". Each column name/type string must be separated by a comma.

Return: Returns 0 on success. Otherwise, the return is an MSI error code and the ErrorString property contains error description.

Back to Top

Function AddTableCustom(TableName, ColumnString)

• Adds a new table to MSI database.
ex.: Ret = MB.AddTableCustom("NewTable", "Col1 CHAR(70) NOT NULL, Col2 INT4 NOT NULL, Col3 CHAR(10)")

AddTableCustom adds a new table to the database. One or more columns can be added when the table is created. This function is similar to AddTable but the function caller is responsible for much of the SQL string. MBase simplifies SQL operations by providing functions that do not require any knowledge of SQL. That design also makes MBase less flexible. AddTableCustom takes the opposite approach: It's a harder function to use, but provides more options. Rather than just "s" or "i" for string or integer data types, the ColumnString parameter must include [column-name/space/data-type-string] for each column. In the sample above there are 3 columns, all with different data types, and first and second columns that cannot be null. (AddTableCustom will automatically add "NOT NULL" to the first column if it is not included. Only the first column may be the primary key - as with AddTable - but other columns may also be non-nullable.)

TableName: Name for new table.
ColumnString: A string that specifies columns to create. At least one column must be created. Syntax is: column-name column-data
Example: "FlowerName CHAR(60) NOT NULL, FlowerColor CHAR(15), FlowerHeight INT, FlowerNameLatin CHAR(255)"

Return: Returns 0 on success. Otherwise, the return is an MSI error code and the ErrorString property contains error description.

Back to Top

Sub ImportTable(Path)

• Imports a table (in the form of a text file) to MSI database.
ex.: Ret = MB.ImportTable "C:\ComboBox.txt"

This is a very handy way to make major changes, but it is a finicky function. If the export function is used to export all tables it will yield a text file version of each table in the database. According to the MSI help, those files are not meant to be imported. Nevertheless, it works fine for the common tables that do not contain unusual data, such as binary streams. But MSI is finicky about the layout and is very poor about returning error information.

Any table imported will replace a table of the same name. If the table import fails, it is probably because of an error in the table. Common problems that can cause a failed import:

- Repeat values in unique keys.
- Missing tabs for empty columns.
- Missing values in columns that are not nullable.
- String data in numeric columns (including such things as "002", which is not a number).
- The lack of a single carriage return after last row.

Back to Top

Sub RemoveTable(TableName)

• Removes a table from MSI database.
ex.: MB.RemoveTable "Table1"

Back to Top

Function AddColumn(TableName, ColName, IfNumeric)

• Adds a new column to an existing table.
ex.: Ret = MB.AddColumn("Table1", "Column2", True)

TableName is name of table to alter. ColName is name of new column. IfNumeric: True creates a column with a long (4-byte) integer value. False creates a column with a string value that may be up to 255 characters.

Return: Returns 0 on success, 1 if TableName is invalid, or MSI error code if other error. On error, ErrorString property contains error description. Back to Top

Function AddRecord(TableName, Values)

• Adds a new record (row) to an existing table.
ex.: Ret = MB.AddRecord("Table1", "red, 10, -, blue")

Values: Must be a comma-delimited string of values corresponding to table columns. All columns must be included. No quotes are necessary (or allowed) in string. String and integer values will be recognized by MBase without using extra quotes. For any column where the value that is not being set, use a dash: "-".

Return: 0-success. 1-invalid table name. 2-wrong number of values. 3-string sent for integer value. other-MSI error code. On error, ErrorString property contains error description.

Back to Top

Function RemoveRecord(TableName, ColName, Value)

• Remove table record.
ex.: Ret = MB.RemoveRecord("Table1", "Color", "red")

Removes any record (row) from table where Value matches value in ColName column.

Return: 0-success. 1-invalid table or column name. other-MSI error code. On error, ErrorString property contains error description.

Back to Top

Function GetSelectValues(TableName, ColumnName, ColumnToCompare, ValueToCompare)

• Returns an array of all values in specified column.
ex.: ARet = MB.GetAllValues("Table1", "Name", "Color", "Red")

GetSelectValues returns specific values WHERE a second column value matches. In the example above, all values in the Name column will be returned from rows where the Color column contains "Red".

Return: Return is an array. ARet(0) is an error code. If the column is a string data type then eror code must be converted from string. A return of -1 indicates an error and the ErrorString property will contain an error description. A value of 0 indicates no matches. Otherwise, UBound(ARet) is the number of values returned.

Back to Top

Function GetAllValues(TableName, ColumnName)

• Returns an array of all values in specified column.
ex.: ARet = MB.GetAllValues("Table1", "Column2")

Return: Return is an array. ARet(0) is an error code. If the column is a string data type then eror code must be converted from string. A return of -1 indicates an error and the ErrorString property will contain an error description. A value of 0 indicates no records in table. Otherwise, UBound(ARet) is the number of values returned.

Back to Top

Function SetSelectValues(TableName, ColumnToSet, ValueToSet, ColumnToCompare, ValueToCompare)

• Sets specific values in a table column.
ex.: Ret = MB.SetSelectValues("Table1", "Color", "Red", "Name", "Rose")

   SetSelectValues sets all column values WHERE another column value matches. In the example above, all values in the Color column would be set to "Red" if the value in the Name column of the same row is "Rose".

   SetSelectValues will typically be used to set a single value based on the unique key value. For instance, in example above, if the "Name" column is a key value then only one record can have the value "Rose" in the Name column. Therefore only one value in the Color column will be changed.
   Attempting to change a key value in MSI raises an error. It could be done by writing a function that first copies all record values, then deletes the record, then writes a new record. However, that has not been done in MBase. If you attempt to change a key value (usually the first column) it will just raise SetSelectValues error 3. To change a key value you must delete the record and create a new one.

Return: 0-success. 1-invalid table or column name. 2-invalid data type (an integer value was sent as string). 3-attempting to set key value. other-MSI error code. On error, ErrorString property contains error description.

Back to Top

Function SetAllValues(TableName, ColumnToSet, ValueToSet)

• Sets all values in a given column to a specific value.
ex.: Ret = MB.SetAllValues("Table1", "Color", "Red")

Return: 0-success. 1-invalid table or column name. 2-invalid data type (an integer value was sent as string). other-MSI error code. On error, ErrorString property contains error description.

Back to Top

Function GetCABList()

• Returns a list of embedded CAB files in an MSI or MSM file.
ex.: Array1 = MB.GetCABList()

Return: Returns an array where array(0) is a string representation of the number of CABs in the file. (Ex. "0", "1", etc. Generally there is only one CAB, at most.) If array(0) is greater than 0, other array elements will hold CAB names. For example, if array(0) = "2" then array(1) and array(2) will both hold CAB data stream names.

Back to Top

Function ExtractCAB(CabName)

• Extracts a CAB file from an MSI or MSM file.
ex.: ErrorCode = MB.ExtractCAB("data1.cab")

CabName: Must be the string that identifies a CAB in the file. Use GetCABList to obtain valid CAB names.

Return: 0-OK. 1-no MSI file loaded. 2-Extraction failed in some way without raising error. Other - return is an error code and ErrorString property contains error information. If method is successful, CAB file is extracted to parent folder of open MSI/MSM file.

Back to Top

Function InsertCAB(CabName, NewCABPath)

• Inserts a CAB file into an MSI or MSM file.
ex.: ErrorCode = MB.InsertCAB("data1.cab", "C:\folder1\newfiles.cab")

CabName: String that identifies name of CAB data stream in the file. This is not the name of the CAB file being inserted. It is the name of the resource in the MSI/MSM file. For example, in an MSM file the CAB is always named "MergeModule.CABinet". That is the name of the data stream in the MSM file. The name of the CAB being inserted is ignored.

NewCABPath: Full path of CAB file to be inserted into MSI or MSM file.

Notes: If CabName already exists in the MSI or MSM file then that data stream will be replaced by the new CAB. If the name does not already exist the new CAB will be inserted as a new data stream.

Return: 0-OK. 1-NewCABPath is an invalid path. Other - return is an error code and ErrorString property contains error information. If method is successful, CAB file is inserted into open MSI/MSM file.

Back to Top

Function GetTableContent(TableName)

• Returns all content of a specific table as a formatted string of rows.
ex.: sRet = MB.GetTableContent("Table1")

Return: On error (for example, if table name is invalid) return is "". Otherwise it is the table contents formatted such that each row ends with a carriage return and a tab character separates column values.

Back to Top

Function GetTableChart

• Returns a chart showing the file's table layout.
ex.: sRet = MB.GetTableChart

Return: On error, return is "". Otherwise return is a formatted string that lists each table with coulmns. Each table name is on its own line. Each column name is on a separate line, inset, below the respective table name. Next to each column is "0" to denote a numeric integer value or "1" to denote a string value.

Back to Top

Function ExportAllTables(FolderPath)

• Exports all tables to text file.
ex.: Ret = MB.ExportAllTables("C:\")

Uses the MSI database Export function to write copies of all tables to disk. Function will create a folder named "export" in FolderPath folder. The export folder will contain one text file for each table in database.

Return: 0-success. 1-Folder path is invalid. other- ErrorString property will contain error description.

Back to Top

Property ErrorString

• Returns error information about last error.
ex.: s = MB.ErrorString

Some error info may be related to things like an invalid file path that's checked in the code of a function. WI errors will usually use the LastErrorRecord object to return specific information. The following is an example. This error was generated by adding an apostrophe in the wrong place in a query string:

 Error 2232: Database: [2]. Unexpected token '[3]' in SQL query: [4] 
1: 2232 2: C:\WINDOWS\Desktop\MaxMind\MaxMind.msi
3:  4: SELECT `GeoID`,`Radius` FROM `IPList` WHERE `IPLow` <= '-788098414 AND `IPHigh` >= -788098414

The first line shows the error number and description. The LastErrorRecord fileds are that error number, 2232, the database path, and 4 is the query string. 3, in this case, is blank. The error description shows how the extra info applies. [*] indicates a field number.

Back to Top

Function GetTableNames(ReturnArray)

• Returns list of tables.
ex.: NumberTables = MB.GetTableNames(ARet)

A simple method that just returns list of table names in MSI file. The list is returned as an array. Function return is number of tables in array. (Array is 1-based).

Back to Top

Function GetTableColumns(TableName, ReturnArray)

• Returns an array of column names in table.
ex.: Ret = GetTableColumns("Table1", ARet)

In the example Ret returns number of columns. If Ret is greater than 0 then ReturnArray is a list of column names, starting with ReturnArray(1).

Back to Top

Function GetTableColumnTypes(TableName, ReturnArray)

• Returns an array of column data types.
ex.: Ret = GetTableColumnTypes("Table1", ARet)

In the example Ret returns number of columns. If Ret is greater than 0 then ReturnArray is a numeric list of column data types, starting with ReturnArray(1). A value of 0 indicates integer data. A value of 1 indicates string data.

Back to Top

Function GetTableColumnSpecificTypes(TableName, ReturnArray)

• Returns an array of column data type values.
ex.: Ret = GetTableColumnSpecificTypes("Table1", ARet)

This function is similar to the function above. The difference is that it returns specific column type information that might be relevant for Windows Installer. GetTableColumnTypes returns only a numeric or string distinction. It is designed for use with custom MSI usage. GetTableColumnSpecificTypes returns numeric flag values that indicate the full spectrum of Windows Installer data type information. See next listing, GetColumnDataType, for further explanation.

Return: 0-success. ReturnArray contains column data values. As with other functions here, this is a 1-based array. -1 - Invalid table name.

Back to Top

Function GetColumnDataType(TableName, ColumnName, ReturnArray)

• Returns extended column data type information.
ex.: Ret = GetColumnDataType("Table1", "Column1", ARet)

   GetColumnDataType is for use in the context of Windows Installer. It returns W.I.-specific column type information that may be useful when editing an installation MSI. This type data is displayed in the MSI Editor when a column name (along top) is clicked.

   The column data type information requires an explanation. Column data types are a good example of both the bizarre design of Windows Installer and the apparently deliberate obfuscation. The more one works with Microsoft designs, the more it seems that the company is populated by teenagers with a secret decoder ring fetish. Nearly everything is more obscure, abstruse, and complex than it needs to be.

   In regard to MSI column data types, to begin with, getting at column information is awkward because one needs to access the _Columns table, which lists all columns in the database in one big pile. Columns for a particular table have to be picked out of that. In addition, the _Columns table has a secret column named Type, which Microsoft has not documented. Without knowing about the Type column, documenting an MSI table is far more awkward.

   Despite the fact that Microsoft makes it so hard to look at column data types, those types can often be relevant. For instance, a string type usually has a maximum length specified. If you're editing an MSI file it helps to know that sort of thing.

   The actual types here also require explanation. Microsoft has designated a large number of data types for MSIs. If you look in the MSI help file under "data types" you'll find 25 types listed. But those are actually "pseudo-types". They are not data types in any conventional sense. For instance, the "Cabinet" type is a string - the name of a CAB file. Microsoft has just designated some such values as unique types, willy nilly.

   While this data type mess is not officially documented, it can be mapped out by using the secret Type column of the _Columns table to get the type flags for specific columns. The Type column type flag is a numeric value that packages several pieces of imformation about the column data type. Basically, there are 5 real types: string, short integer, long integer, binary (byte stream), and localized. A localized type is a value, usually (always?) a string, that is a local variable, typically pointing to another table.
   String values have a maximum length aspect to their type. Localized values also do, but often that value is set to 0. In addition to the basic 5 data types, there are the Boolean Nullable and Key "properties". When Nullable is True it means that the column does not require a value. It can be left blank. Key refers to key columns. Usually there is one primary key column, which must have a unique value in each row. But sometimes there are two columns that together comprise the primary key. (Not to be confused with an external key, a key into another table.) See the documentation on the various Windows Installer tables in the MSI.CHM help file for examples of these flags.

   So each table column has several aspects to it's data type. The flag value in the secret Type column of the _Columns table is a short integer that works as follows:

H1000 (4096) nullable
H2000 (8192) key

HD00 (3328) string
H500 (1280) short integer
H900 (2304) binary
H100 (256) long integer
HF00 (3840) localized [variable]

   The low byte of the integer specifies maximum length, where relevant. When formatted in hex, the Type flag will be a 3 or 4 digit number. For example, H1D20 denotes a nullable string of maximum length 32. (Note that short and long integer types also use the low byte marker, but in those cases the value is redundant. A long integer seems to always include flags for H104 and a short seems to always include H502. That makes sense. A long is 4 bytes and a short is two bytes. But there's no need to check the max. length value in order to know that, and there seem to be no other numeric types, such as an 8-byte integer or Double value. If desired, a max. length check could be added to this function to confirm numeric data types. As written, the function only checks max. length for string and localized types.)

   The GetColumnDataType function retrieves the flag integer from the secret Type column of the _Columns table and processes it. If the function succeeds, it returns a numeric array of UBound(3). The array is used in order to return all relevant information about the column data type while keeping the function lean, avoiding the creation of a class or dictionary for every call. (A custom data structure would have been ideal here, but VBScript can't handle that.) The return works as follows:

If GetColumnDataType returns 0 (success), ReturnArray will contain the following numeric values:

ReturnArray(0): IsNullable. 0=False. 1=True.
ReturnArray(1): IsKey. 0=False. 1=True.
ReturnArray(2): Data type. 0-string. 1-binary. 2-short int. 3-localized. 4-long int.
ReturnArray(3): Max. Length. Contains max. length value where relevant.

  So IsNullable is basically a Boolean value returned numerically. Likewise for IsKey. The data type return values were assigned in such a way as to make them more memorable. Binary, short int. and long int. were all assigned to the number that represents the size of their data: 1 byte for binary, 2 bytes for short, 4 bytes for long.
   Example returns: In the example above, where a nullable string of max. len. 32 has a Type flag of H1D20 (7456), that will come back in ReturnArray like so - ReturnArray(0) = 1. ReturnArray(1) = 0. ReturnArray(2) = 0. ReturnArray(3) = 32. A non-nullable, non-key long integer would return 0, 0, 4, 0. Nullable, non-key binary data would return 1, 0, 1, 0. Etc.

Return:
0 - success. ReturnArray contains information, as detailed above.
-1 - invalid table name.
-2 - invalid column name or invalid data returned for Type flag.

Back to Top

Function GetColumn1(TableName)

• Returns first column name for given table.
ex.: sCol = GetTableColumn1("Table1")

This is a simple function that just provides an easy way to get the name of the first column in a table. The first column is typically the primary key, and getting column names involves a multi-step process of getting an array item from a Dictionary object. So this function is provided to simplify things.

Back to Top

"Find" Functions

   The May, 2006 update to the editor includes expanded table search functionality. There are 3 methods involved:

Function ExportAllValidTables(FolderPath)
Function LoadFindData()
Function Find1(StringTofind)

   The way it works is that ExportAllValidTables is first called. In the MSI Editor the tables are exported to a folder in the TEMP folder when a file is loaded, then the folder is deleted when that file is unloaded. That folder path is a global variable within the class. It is hard-coded as:
%TEMP%\msi-ed-fol
and LoadFindData assumes that path. ExportAllValidTables is used because it exports only tables that have content and also filters out a few other tables (in the case of MSI software installation files) that are not relevant. That culling allows search to work faster.

   After the tables have been exported as text files, LoadFindData is called, which reads in each table and loads them into a Dictionary object wherein each Dictionary Item is an array of rows from a given table. LoadFindData returns True if the folder full of table files was found and at least one table file had data.

   Those two functions are necessary before using the search function. That's what the Enable Find button in the editor does. In order to avoid bogging down with extra large files, a file's tables are not processed and loaded into the Dictionary unless Enable Find is clicked after loading a given file.

   Having called the first two functions successfully, Find1 can then be used. Find1 searches the entire database for matching strings. Unfortunately, while the Export method exports tables alphabetically, the same does not seem to work when using a SELECT query. Even when ORDER BY is used in the query to enforce alphabetical return, MSIEXEC seems to usually return two cycles of alphabetical listings. In other words, the first column will be in alphabetical order, but the column items go from A-Z twice as one scrolls through them. As a result, it's difficult to predict in which row the string was found. So Find1 does not report the row where a match is found. Rather, the return is as follows:

If no matches are found, Find1 returns "".

When matches are found, a string is returned that is structured as

Table-name - 1st-column-value |
Table-name - 1st-column-value |
Table-name - 1st-column-value |

   In other words, each match will be represented by a line in the return string. Each line will contain the name of the table where the match was found, and the value of the first column in the row where the match was found. When a search is done in the MSI Editor, the multi-line textbox in the Find panel will fill with matches. You can then locate each match by loading the respective table and scrolling down to the respective first column value.

   So, imagine that you search for "blue" and a table named "Files" contains a row with "blue" or "bluesky" or "trueblue", etc. in one of the fields. And that row contains "red" in the first column. Find1 will return that match as: "Files - red |".

   The reason for the format is connected with DHTML requirements: In the MSI Editor, a line can be double-clicked in the list of found matches, which will load the respective table and scroll to the match. That function uses TextRange object methods to read the text of the line clicked. TextRange methods are limited and clunky. The process is made easier by using " - " and "|" as markers.

Note that the tables are exported when a file is loaded. Changes made to the database while the file is loaded will not be reflected in "Find" until the database is reloaded.

Back to Top

Using the Editor

   Most of the editor functions are self-explanatory. There are functions on the menu to open files and perform operations. There is no need to save files. When you add a table, remove a row, etc. the change is saved.

   Start by going to the File menu to open an MSI file. The file will be loaded and you can view each table by using the table dropdown list.

   Many of the menu items have help. Just click the ? button to see context help for that item.

Changing cell data in rows:

To change data in a cell, click the cell. A text prompt appears. Enter new text. To save changes, double-click the text prompt. To cancel, simply do not double click it. Clicking elswhere will make the text prompt disappear.

Find:

The Find function has been updated. See topic above for explanation.

Edit Summary Properties

See the GetSummary and SetSummary function topics for this. Editing summary info. has limitations.

Using the Editor to Adjust Software Installers - Tips

One of the problems that can commonly occur with software installation is that the software has limitations that may not be valid. For example, both Acrobat Reader 7 and HP printer software installers require Internet Explorer 5.01, yet neither requires a browser to function. Asking people to update Internet Explorer unnecessarily - a risky thing to do since IE is so embedded into Windows - is entirely unacceptable. The MSI Editor can be used to alter installation requirements.

It is usually possible to change installation requirements that are coded into MSI installer files...

In general, the LaunchCondition table of an MSI installer is the place to look for limitations to install. Other things to check: The AppSearch table is used to list files or settings that the installer might need to check. Typically the AppSearch table will list an item that gets checked during install. The _Signature property there points to a listing in the RegLocator, IniLocator, CompLocator, and/or DrLocator tables that identifies the item - a Registry setting, file version, etc. If one of these items is also involved with a pre-requisite for installation (for example, SHDOCVW.DLL needs to be located and checked to get the IE version) then it wil be referenced in the LaunchCondition table. The MinVersion and MaxVersion fields in the Signature table will be the likely place where something like a required version of IE will be listed. (In the Acrobat Reader 7 installer the culprit is a row in the Signature table where the FileName field is "SHDOCVW.DLL" (that's IE) and the MinVersion field is IE 5.01. (To further confuse things, there actually never was a version 5.01. Version "5.00" in Win98SE was v. 5.00.2614.3500. So-called version "5.01" began with Windows 2000 IE version 5.00.2516.1900 and runs through Win2000 IE version 5.00.3700.1000!)

Troubleshooting: Top table view goes blank

Occasionally when certain changes of focus occur, the top table view may go blank.This "blankout" problem is a DHTML issue. It will not affect working with the database but may disable the window in some cases if all menus have disappeared.

In the May, 2006 update this bug has been greatly improved upon, and may be fixed.The fix used, which seems to work well, was to call a Refresh on the main page table peridodically after focus has been shifted programmatically. The Refresh button does the same thing. It will call a Refresh on the main table and also scroll the page to the top. If you encounter "blankouts" and can still see the Refresh button and/or the table list, try using either one of those to refresh the view.

Troubleshooting: Errors writing values to table cells

There are a few issues to be aware of when editing databases:

• Values cannot be changed in the first column because it is the primary key. The row must be deleted and a new one created.

• Non-numeric values written to numeric cells will fail. That includes things like "00034". If you need to use ID numbers like that it should be a string data type.

• Some tables cannot be written. For example, in an MSI file you may sometimes see a table name starting with "_", such as a _Validation table that has been left in. Such MSI tables are "virtual" tables that cannot be written to.

Troubleshooting: Importing tables fails

Windows Installer is picky about imported tables. Check to make sure that all values are valid, that all lines contain all necessary entries, that all non-nullable values contain some data, and that there is a carriage return at the end of the file.

Troubleshooting: Table view hangs

The table view window is a series of TABLE elements, with each cell being a TD. The rows (TABLEs) and cells (TDs) are generated dynamically and with script and cell data is filled in. In cases of very big tables the view may appear to hang. If a table has several hundred rows it may take a few seconds to process. If a table has several thousand rows it may not be feasible to view it. The view is limited by Internet Explorer's ability to dynamically generate HTML.

Troubleshooting: "Object Required" error

When errors occur they are usually reported in lower left. If you see "Error 424: Object Required" that may be misleading. It probably does not mean an object reference is missing. That seems to be a quirk of Microsoft database error messages. If you get this error, check for errors in the syntax of the operation you are trying to perform: You may be entering a non-numeric value into a numeric column or you may have invalid syntax to create a row or table, for example.

Back to Top

License:

   You use all script code and components from JSWare at your own risk.

   The components (compiled DLL and EXE files) may be used for personal or commercial purposes. No payment or attribution is required for either use. The components may be redistributed if they are required as support files for scripts or software that you have written.
   Also, the script code may be used freely, in part or as whole scripts, for any purpose, personal or commercial, without payment or attribution.

   I ask only that you not redistribute these scripts and components, except as required for your direct use. Instead, please direct others to obtain copies of JSWare scripts and components directly from www.jsware.net.

   Also, none of the code here may be redistributed under another license. If a work using code from JSWare is distributed with restrictions of any kind the code from JSWare must be kept exempt from those restrictions. This includes, but is not limited to, code sold for profit, code with usage restrictions and code distributed as so-called "Open Source" with redistribution restrictions.

Joe Priestley

MBase Function Reference - Update 7/3/17

- A Complete Set of Functions for Using MSI Files as SQL Databases in VBScript -