Problem trying to INSERT INTO ServiceControl table

[ARobinson]

Hi,

I'm trying to update the ServiceControl table at runtime (the names and number of our service instances can be setup by users and are therefore unknown to us until runtime). I'm using a custom action that calls the MsiDatabaseOpenView function. I've stepped through a debugger and the sqlquery passed to the function appears as:

"INSERT INTO `ServiceControl`(`ServiceControl`.`ServiceControl`,`ServiceControl`.`Name`,`ServiceControl`.`Event`,`ServiceControl`.`Arguments`,`ServiceControl`.`Wait`,`ServiceControl`.`Component_`)VALUES('ServiceEvent11809','DOORS DB Server 9.0 00001',160,'one',1,'ControlService1') TEMPORARY"

The function is returning an ERROR_BAD_QUERY_SYNTAX from this statement, I can't see any errors in it, can anyone see any problems with the sql statement, or what I'm trying to do?

Thanks.

[andyamstrad]

"INSERT INTO `ServiceControl`(`ServiceControl`.`ServiceControl`,`ServiceControl`.`Name`,`ServiceControl`.`Event`,`ServiceControl`.`Arguments`,`ServiceControl`.`Wait`,`ServiceControl`.`Component_`)VALUES('ServiceEvent11809','DOORS DB Server 9.0 00001',160,'one',1,'ControlService1') TEMPORARY"

Try this

"INSERT INTO `ServiceControl`(`ServiceControl`.`ServiceControl`,`ServiceControl`.`Name`,`ServiceControl`.`Event`,`ServiceControl`.`Arguments`,`ServiceControl`.`Wait`,`ServiceControl`.`Component_`) VALUES ('ServiceEvent11809','DOORS DB Server 9.0 00001',160,'one',1,'ControlService1') TEMPORARY"

Note the spaces before and after VALUES

Also ensure that the fields you are inserting into have the correct datatype e.g. string, long etc and you are specifying the correct number of fileds per row.

[MichaelU]

Windows Installer has halfhearted SQL support, so you may find it easier to use their record-based interface. Open a view on the table (select *), create a record with MsiCreateRecord (or its equivalent in the language you are using), populate it with MsiRecordSet{String,Integer}, and insert it with MsiViewModify.

[ARobinson]

MichaelU wrote:
Windows Installer has halfhearted SQL support, so you may find it easier to use their record-based interface. Open a view on the table (select *), create a record with MsiCreateRecord (or its equivalent in the language you are using), populate it with MsiRecordSet{String,Integer}, and insert it with MsiViewModify.

Thanks very much, I changed the code to use the record-based interface, and it appears to be working now. 🙂