Cannot get SQL queries to run!!!

[seth_at_work]

I've been spending my entire weekend trying to find a way around having to use the "Always Overwrite" option in my basic MSI project. I have two 3rd party EXEs in my project and the manufacturer has downgraded the version number in the current release. I don't want to use Version Lying because that will cause issues during the next release. What I've been trying to do is copy the cached MSI from the previous release to a different location (which I have done successfully), downgrade the file versions in it, then put it back. However none of my SQL queries are executing properly! The API functions all return 0 (success), but the file version isn't updated. Not only that, but my SELECT statements to check the update only return blank values.

I have tested both my queries in the MSI query tool and they both perform the actions I want. So why won't it work in my custom action!?

Here is the code. It runs in immediate mode after FindRelatedProducts under the condition 'IS_MAJOR_UPGRADE'

OLDVERSIONINSTALLED is set by a major upgrade item.

function DowngradeFileVersions(hInstall)
    NUMBER nBuffer;
    STRING sCachedMsi, sPatchedMsi, sSQL, sGuid, sDir, sValue;
    HWND hDB, hView, hRec;
begin
    nBuffer = 65535;
    MsiGetProperty(hInstall, "OLDVERSIONINSTALLED", sGuid, nBuffer);
    nBuffer = 65535;
    MsiGetProductInfo(sGuid, INSTALLPROPERTY_LOCALPACKAGE, sCachedMsi, nBuffer);

    nBuffer = 65535;
    MsiGetProperty(hInstall, "INSTALLDIR", sDir, nBuffer);
    sPatchedMsi = sDir ^ "Patched.msi";
    DeleteFile(sPatchedMsi);
    CopyFile(sCachedMsi, sPatchedMsi);
    
    SprintfBox(MB_OK, "MsiOpenDatabase", "%ld", MsiOpenDatabase(sPatchedMsi, MSIDBOPEN_TRANSACT, hDB));

// check version
    sSQL = "SELECT `File`.`Version` FROM `File` WHERE `File`.`FileName` = 'CRA.exe' AND `File`.`Component_` = 'cra.exe'";
    SprintfBox(MB_OK, "MsiDatabaseOpenView", "%ld", MsiDatabaseOpenView(hDB, sSQL, hView));

    hRec = MsiCreateRecord(1);
    SprintfBox(MB_OK, "MsiViewExecute", "%ld", MsiViewExecute(hView, hRec));
    nBuffer = 65535;
    SprintfBox(MB_OK, "MsiRecordGetString", "%ld", MsiRecordGetString(hRec, 1, sValue, nBuffer));
    MessageBox(sValue, INFORMATION);
    MsiViewClose(hView);
    MsiCloseHandle(hView);
    MsiCloseHandle(hRec);

// update version
    sSQL = "UPDATE `File` SET `File`.`Version` = '0.0.0.0' WHERE `File`.`FileName` = 'CRA.exe' AND `File`.`Component_` = 'cra.exe'";
    SprintfBox(MB_OK, "MsiDatabaseOpenView", "%ld", MsiDatabaseOpenView(hDB, sSQL, hView));
    SprintfBox(MB_OK, "MsiViewExecute", "%ld", MsiViewExecute(hView, NULL));
    SprintfBox(MB_OK, "MsiDatabaseCommit", "%ld", MsiDatabaseCommit(hDB));
    MsiViewClose(hView);
    MsiCloseHandle(hView);

// check version again
    sSQL = "SELECT `File`.`Version` FROM `File` WHERE `File`.`FileName` = 'CRA.exe' AND `File`.`Component_` = 'cra.exe'";
    SprintfBox(MB_OK, "MsiDatabaseOpenView", "%ld", MsiDatabaseOpenView(hDB, sSQL, hView));

    hRec = MsiCreateRecord(1);
    SprintfBox(MB_OK, "MsiViewExecute", "%ld", MsiViewExecute(hView, hRec));
    nBuffer = 65535;
    SprintfBox(MB_OK, "MsiRecordGetString", "%ld", MsiRecordGetString(hRec, 1, sValue, nBuffer));
    MessageBox(sValue, INFORMATION);
    MsiViewClose(hView);
    MsiCloseHandle(hView);
    MsiCloseHandle(hRec);

// clean up
    MsiCloseHandle(hDB);

    DeleteFile(sCachedMsi);
    CopyFile(sPatchedMsi, sCachedMsi);


    return ERROR_SUCCESS;
end;

[Christopher_Pai]

I didn't read your whole code but the first part peeked my interest:

nBuffer = 65535;
    MsiGetProperty(hInstall, "OLDVERSIONINSTALLED", sGuid, nBuffer);
    MsiGetProductInfo(sGuid, INSTALLPROPERTY_LOCALPACKAGE, sCachedMsi, nBuffer);

    MsiGetProperty(hInstall, "INSTALLDIR", sDir, nBuffer);

Each call to a function that uses nBuffer is going to set nBuffer to the number of bytes that was fetched. Subsequent calls needing a larger buffer will fail with an ERROR_MORE_DATA return code. But you aren't actually bothering to check return codes...

Also make sure this custom action isn't running deferred. It must be scheduled for immeadiate to have any hope to access the MSI session handle. You are then using functions to copy files and what not (changing machine state) which should never be done in immeadiate execution as this won't support elevation scenarios and rollback scenarios.

You have some flaws in your design. I'd suggest starting by reading: http://www.installsite.org/pages/en/isnews/200108/

[seth_at_work]

Thank you for your reply. My code showed almost every return code in a message box; they all returned ERROR_SUCCESS. Calling each Msi function that uses a buffer like this:

nBuffer = MAX_PATH;
sDir = "";

if (MsiGetProperty(hInstall, "INSTALLDIR", sDir, nBuffer) == ERROR_MORE_DATA) then
    nBuffer++;
    MsiGetProperty(hInstall, "INSTALLDIR", sDir, nBuffer);
endif;

or this:

nBuffer = 65535;
sDir = "";
MsiGetProperty(hInstall, "INSTALLDIR", sDir, nBuffer);

made no difference. I should have reinitialized the buffer before each call, but that still does not make any difference. In my original post I stated that my action was not deferred. Also, this is not the code that will be used in my installation; this is merely a test project.

[Christopher_Pai]

I don't understand what updating the cached MSI would do with regard to file versioning. File Costing evaluates the File version to be installed and the file version that is installed. In doing this it looks at the installed file. It doesn't look at the cached MSI at all.

What is your concern with version lying? Have you tried scheduling RemoveExistingProducts prior to costing?

[seth_at_work]

Thanks for you patience; I'm obviously misunderstanding something fundamental.

My concern is this: if I use version lying/always overwrite in this release the file will get overwritten, but won't the file version in the cached MSi be 65535.0.0.0? If this is the case, won't that prevent the file from ever getting overwritten again?