INSTALLDIR in SQL Scripts

[hazosTAS]

I'm creating an InstallScript type install that contains SQL Server Database files (MDF and LOG), and I would like to attach these files to SQL Server, the best way I found to do this is with SQL Scripts. However I ran into a problem.

I am trying to do a Text Replacement in my SQL Script where the "Find What" is @DB_Path, and I would like to replace that with the directory path the files were installed in. How can I do this, any Ideas?

[TheTraveler]

Hello,

I have several ideas. I use ADO objects in my Install Shield Script project. It works really well. Here is what I do.

1) Created a function that goes into each of my SQL Script files and do a StrReplace on variables I have placed in the SQL File.

2) I developed a set of functions that uses the ADO objects to run all the SQL scripts files. Here is a link on how you can use the ADO objects.

With this concept, I can
1) Create a database in SQL code and place the database files anywhere on the server.
2) Create an end-user defined database user.
3) I give the option to create a new database or allow the user to use an existing database already defined on the SQL Server.
4) Connect to any SQL Server Installed Instance.
5) I'm able to query the SQL Server database for information. This includes getting the version of the database and what service pack that has been applied.
6) When I run the SQL Scripts and if it errors, I can get the Error reported by the database engine, I know what SQL command errored and the location of the SQL command in my file.
7) I can better do upgrades of the database. Here is how I do it. I have placed a DB version column in one of the tables I create in our database. I have put into my SQL Script files break points. These break points are put into comment areas in the SQL file. So if you want to run the file in Query Analyzer, you can run it without any problems. With these two variables, I can traverse the file, find the appropriate break point that I can deduce from my database version number, and then run the SQL file from that point to the end of the file.

I can go on and on. The point is, I can do anything a windows application can do in install shield script. So the possibilities are endless...

I hope this helps...

[hazosTAS]

Thats awesome thanks, that function solved a possible second question I had. Do you have an example of a function that goes through the SQL Scripts to modify and replace specific keywords? That would great, thanks in advance.

[jchristman]

I a have retrieved all my data sql server and setup the connections but I am looking for a way to execute a sql script and send the output to a file using InstallScript.

[jchristman]

Can an ADO object run a script and send output to a file. I ask because my script has "PRINT" commands in it from when it was ran manually.

[Christopher_Pai]

Have you tried just replacing with [INSTALLDIR]? The replacement mechanism seems to honor property formatting from my tests.

[hidenori]

If you are using a pure InstallScript project, you need to use .

[TheTraveler]

Take a look at this...

EXTERNAL prototype My_ConfigureFiles(BYVAL string, BYVAL string);

function My_ConfigureFiles( strDestinationFile, strSourceFile)
    LIST    listTemp;
    string  strLine;
    string  strTemp;
    //NUMBER    nCounter;
    number  nSize;
    number  nPos;
    number  nCheck;
    string  strFind;
    string  strSearch;
    string  strValue;
    number  nIndex;
begin
    ///////////////////////////////////////////////////////////////////////////
    ///////////////////////////////////////////////////////////////////////////
    listTemp = ListCreate (STRINGLIST);
    ListReadFromFile ( listTemp, strSourceFile );

    ///////////////////////////////////////////////////////////////////////////
    ///////////////////////////////////////////////////////////////////////////
    //nCounter  = 0;
    nSize     = ListCount(m_listMacroItems);
    strFind   = ""; 
    strTemp   = "";
    nCheck = ListSetIndex(listTemp, LISTFIRST);

    while (nCheck != END_OF_LIST)
        ListCurrentString(listTemp, strLine);
        StrToUpper(strTemp, strLine);
        m_nChecks = m_nChecks + 1;

        ///////////////////////////////////////////////////////////////////////
        // Check the line for the begining of a Macro...                     //
        ///////////////////////////////////////////////////////////////////////
        if StrFind(strLine, "{") >= 0 then
            ///////////////////////////////////////////////////////////////////
            //   Loop through the list of Macros and replace it with the     //
            // Macro Value                                                   //
            ///////////////////////////////////////////////////////////////////
            for nIndex = 0 to nSize -1
                ///////////////////////////////////////////////////////////////
                // Get the Macro and it's Value...                           //
                ///////////////////////////////////////////////////////////////
                ListSetIndex(m_listMacroItems,  nIndex);
                ListCurrentString(m_listMacroItems, strSearch);
                
                ListSetIndex(m_listMacroValues, nIndex);
                ListCurrentString(m_listMacroValues, strValue);
    
                ///////////////////////////////////////////////////////////////
                ///////////////////////////////////////////////////////////////
                m_nChecks = m_nChecks + 1;
                nPos = StrFind(strTemp, strSearch);
                if nPos >= 0 then
                    ///////////////////////////////////////////////////////////
                    //   For the purpose of Case Insensitive, I grad the     // 
                    // macro as it appears in the file and target it for     //
                    // replacement.                                          //
                    ///////////////////////////////////////////////////////////
                    StrSub(strFind, strLine, nPos, StrLength(strSearch) );  

                    ///////////////////////////////////////////////////////////
                    //   Perform a search and replace of the on the line     //
                    ///////////////////////////////////////////////////////////
                    StrReplace ( strLine, strFind, strValue, nPos );
                    
                    ///////////////////////////////////////////////////////////
                    // Overwrite the old string with the new one in the list //
                    ///////////////////////////////////////////////////////////
                    ListSetCurrentString(listTemp, strLine);
                    
                    ///////////////////////////////////////////////////////////
                    //   Assign the new string to strTemp to continue macro  //
                    // replacement                                           //
                    ///////////////////////////////////////////////////////////
                    StrToUpper(strTemp, strLine);
                    m_nCount = m_nCount + 1;
                endif;
            endfor;
        endif;

        nCheck = ListSetIndex(listTemp, LISTNEXT);
    endwhile;
    
    ///////////////////////////////////////////////////////////////////////////
    ///////////////////////////////////////////////////////////////////////////
    ListWriteToFile( listTemp, strDestinationFile );
    ListDestroy(listTemp);
    return 0;
end;

This is how this function works. I use the m_listMacroItems and m_listMacroValues together. "m_listMacroItems" contains a list of variables to be replaced in the SQL file. The companion list contains the values that will replace the variables. So when you add an variable item to the list, you must add the value to the m_listMacroValues at the same index. The Index is used as a key between the two lists.

Hope this helps...