Reading/Editing SQL Scripts Schema Version

[pradana]

Hi all,

Is it possible to manually manipulate a row in InstallShield table in database created by Installshield (e.g. reading it from the table & put the value to a property, or adding a new row, etc)?

Thanks in advance.

[pradana]

Any idea? :confused:

[hidenori]

You can update the InstallShield table by executing sql commands. The following is a sample code that will add the value of 3.0.0 to the ISSchema column of the InstallShield table.

[Code]INSERT INTO [dbo].[InstallShield] (ISSchema) VALUES('3.0.0')[/Code]

[pradana]

Thanks hidenori... How about reading it? Maybe I should use SELECT statement, but if so, where the selected value will be stored?

Thanks.

[hidenori]

You should be able to store in a local variable as follows. Please consult with the MSDN for more information.

Declare @local_variable As char(15)
SELECT @local_variable=ISSchema FROM InstallShield ORDER BY ISSchema ASC

[pradana]

hidenori wrote:
You should be able to store in a local variable as follows. Please consult with the MSDN for more information.

Declare @local_variable As char(15)
SELECT @local_variable=ISSchema FROM InstallShield ORDER BY ISSchema ASC

I mean, can we store the query result into an installer property (eg. DB_SCHEMA_VERSION), so we can make use of the value within installshield (eg. in installscript as in MsiGetProperty("DB_SCHEMA_VERSION", schemaVer, size))

[hidenori]

Unfortunately, the built-in InstallShield SQL feature currently does not support the ability that you are looking for. At this time, you will need to write your own InstallScript code that will manage connections and execute your SQL scripts in order to accomplish your requirement. There are many sample code posted in this community forum. Please take a look at this thread as a starting point.

[pradana]

Thanks for your advise hidenori.

Btw, I tried the following script to test:

function CheckDbSchemaVersion(hMSI)
    STRING server, user, pwd, dbname, sqlCmd, connStr;
    OBJECT adoConn, adoCmd, adoRecordSet;
begin
    set adoConn = CreateObject("ADODB.Connection");
    connStr = "Driver={SQL Server};Server=10.32.7.147;Database=databasename;Uid=sa;Pwd=password";
    try
    	adoConn.Open(connStr);
    	adoRecordSet = CreateObject("ADODB.Recordset");
    	
    	adoRecordSet.CursorType = 3;
    	adoRecordSet.ActiveConnection = adoConn;
    	
    	sqlCmd = "SELECT Version FROM DbSchemaVersion";
    	
    	adoRecordSet.Open(sqlCmd);
    	
    	MessageBox(adoRecordSet.RecordCount, INFORMATION);
    catch                      
    	MessageBox(Err.Description, SEVERE);
    endcatch;
end;

This script returns me a message box with empty error message and an error icon, which I suspect coming from the MessageBox in 'catch' block. Any part I did wrongly?

PS: the WMDBVersion only has one column and one row. The IP, database name, user, and password are all correct.

[hidenori]

Did you try debugging into the code to see which line is causing the exception?

[pradana]

OK solved, my mistake 🙂

I added "set" keyword before "adoRecordSet = CreateObject("ADODB.Recordset");" and it works

Thanks