Automation Problem

[Christopher_Pai]

I'm emitting sql scripts into a project just before building and I'm having a problem.

Is there a method or property for setting the sql script condition?

I'm looking at the ISWiSQLScript class and I don't see it.


Do I *REALLY* have to save the project, hack it with an XPath statement and then open it back up in the automation?

[hidenori]

I filed the work order #IOC-000075015 so that it will be addressed in a future release. To get it workaround, you need to work with MSXML if you are using an InstallScript project.

Thank you for reporting this.

[Christopher_Pai]

Is there any hotfix available for the primary key truncation issue?

[hidenori]

Are you looking for the hotfix of this issue?

[Christopher_Pai]

Yes, that's the one. I have a problem of length and uniqueness. I have automation that iterates through directories like

db1
schema.sql
db1\procs
some_really_long_named_stored_proc.sql

db2
schema.sql
db2\procs
some_really_long_named_stored_proc.sql ( same file name as in db1 because it's an object that belongs in multiple databases but has different USE statements )

So my DBA's get irritated when I tell them the filename is too long and that it isn't unique. They simply don't want to be bothered to call it db1_schema.sql, db2_schema.sql.

So I'm looking for something like some_sql_script~1 were it's unique and yet descripting as possible. I just might have to roll it myself.

[hidenori]

To get it workaround, you basically need to change the length of the ISSQLScriptFile column in the ISSQLScriptFile table from 72 to 46 as follows:

[LIST=1]

Open an .ism project in Orca.

Export the ISSQLScriptFile table to an idt file.

Open the idt file in Notepad.exe.

Change the type of the ISSQLScriptFile column from s72 to s46.

Save the idt file and close Notepad.exe.

Delete the ISSQLScriptFile table in Orca.

Import the idt file.

Save the .ism project and close Orca.

If you would like this change for every new project that you will create, please update the project templates located in the Support\0409 folder.

Basic MSI project template: IsProjBlankTpl.ism
InstallScript MSI project template: IsProjScriptTpl.ism

Hope that helps.

[Christopher_Pai]

Another question. I see ISWiSQLScript RonOnInstall, RunOnUninstall and RonOnRollback. Is there a way for setting the RunOnLogon attribute?

[Christopher_Pai]

I looked at my ISM and the column was already defined as s72. I changed my build automation to allow filenames that long and error -6151 has returned.

[hidenori]

I confirmed that the RunOnLogon proprety is missing in the ISWiSQLScript object. I filed the work order #IOC-000075114 so that it will be addressed in a future release.

Also, if you already have the keys longer than 46 characters in the ISSQLScriptFile table, you need to write automation code that will truncate them. The following is a sample VB code that illustrates what needs to be done:

Private Sub MainFunction()
    Dim pProject As ISWiAuto15.ISWiProject
    Set pProject = CreateObject("ISWiAuto15.ISWiProject")
    
    pProject.OpenProject "C:\InstallShield 2009 Projects\TestAuto.ism", False
    
    Dim colValidSQLScriptNames As New Dictionary
    Dim colInvalidSQLScriptNames As New Dictionary
   
    Dim pSQLConnection As ISWiSQLConnection
    Dim pSQLScript As ISWiSQLScript
    For Each pSQLConnection In pProject.ISWiSQLConnections
        For Each pSQLScript In pSQLConnection.ISWiSQLScripts
            If Len(pSQLScript.Name) > 46 Then
                colInvalidSQLScriptNames.Add pSQLScript.Name, pSQLScript
            Else
                colValidSQLScriptNames.Add pSQLScript.Name, pSQLScript
            End If
        Next
    Next

    Dim vSQLScript As Variant
    For Each vSQLScript In colInvalidSQLScriptNames.Items
        Set pSQLScript = vSQLScript
        pSQLScript.Name = GetValidUniqueName(pSQLScript, colValidSQLScriptNames)
    Next
    
    pProject.SaveProject
    pProject.CloseProject
End Sub    
    
Private Function GetValidUniqueName(ByVal pSQLScript As ISWiSQLScript, ByVal colValidSQLScriptNames As Dictionary) As String
    Dim lCount As Long
    Dim sNewName As String
    Dim sTemplate As String
    
    lCount = 1
    
    sNewName = Left$(pSQLScript.Name, 46)
    If colValidSQLScriptNames.Exists(sNewName) = False Then
        GoTo UniqueNameFound
    End If
    
    sTemplate = Left$(pSQLScript.Name, 44)
    Do
        sNewName = sTemplate + "_" + Trim(str(lCount))
        lCount = lCount + 1
        If colValidSQLScriptNames.Exists(sNewName) = False Then
            GoTo UniqueNameFound
        End If
    Loop While (lCount < 10)
 
    sTemplate = Left$(pSQLScript.Name, 43)
    Do
        sNewName = sTemplate + "_" + Trim(str(lCount))
        lCount = lCount + 1
        If colValidSQLScriptNames.Exists(sNewName) = False Then
            GoTo UniqueNameFound
        End If
    Loop While (lCount < 100)
    
  
    sTemplate = Left$(pSQLScript.Name, 42)
    Do
        sNewName = sTemplate + "_" + Trim(str(lCount))
        lCount = lCount + 1
        If colValidSQLScriptNames.Exists(sNewName) = False Then
            GoTo UniqueNameFound
        End If
    Loop While (lCount < 1000)

    sTemplate = Left$(pSQLScript.Name, 41)
    Do
        sNewName = sTemplate + "_" + Trim(str(lCount))
        lCount = lCount + 1
        If colValidSQLScriptNames.Exists(sNewName) = False Then
            GoTo UniqueNameFound
        End If
    Loop While (lCount < 10000)

    sTemplate = Left$(pSQLScript.Name, 40)
    Do
        sNewName = sTemplate + "_" + Trim(str(lCount))
        lCount = lCount + 1
        If colValidSQLScriptNames.Exists(sNewName) = False Then
            GoTo UniqueNameFound
        End If
    Loop While (lCount < 100000)

UniqueNameFound:
    GetValidUniqueName = sNewName
    colValidSQLScriptNames.Add sNewName, pSQLScript

End Function

[operaza]

I'm adding a script using IS automation and updating the ISWiSQLScript properties.
Do you know why MySQLScript.Name fail?
set MySQLScript = MySQLConnection.ISWiSQLScripts("SQLScript" & ScriptNo)
MySQLScript.FullPath=InstallersFilesPath & "SQLDeployments\" & scriptName
MySQLScript.Order=ScriptNo
MySQLScript.Name="test"

No matter the name I use MySQLScript.Name throws error:
Microsoft VBScript runtime error:
Object required: 'MySQLScript'

It only happen with MySQLScript.Name, I can set all the other properties and much more without a problem

And I can read MySQLScript.Name, wscript.echo MySQLScript.Name returns the script name, but it fails when I try to set a name by using MySQLScript.Name="newname"

Note: MySQLConnection.AddSQLScriptEx works as alternative to set the name when creating the script

Thanks,
Orlando