Can't insert new record doc <david.daugherty[ at ]gmail.com> 22.06.2007 03:52:05 I'm trying to add a new record to the Component table. I've tried using a normal INSERT INTO query but can't seem to get the syntax right. I've been trying to use the temp record approach and sometimes it works, sometimes it doesn't. I always get an error telling me insert.vbs(25, 1) Msi API Error: Modify,Mode,Record My code looks like: set oMSI = CreateObject("WindowsInstaller.Installer") set oDB = oMSI.OpenDatabase("<absolute path>\test.msi",1) : checkError set oView = oDB.OpenView("SELECT * FROM `Component`") : CheckError oView.execute : CheckError set oRec = oView.fetch : CheckError zero = 0 one = 1 set oRec = oMSI.CreateRecord(14) : CheckError oRec.StringData(1) = "blah" oRec.StringData(2) = "{D1ECD74D-A4F3-4DF4-8143-263975AE4D00}" oRec.StringData(3) = "PROG" oRec.IntegerData(4) = zero oRec.StringData(5) = "" oRec.StringData(6) = "" oRec.IntegerData(7) = one oRec.StringData(8) = "" oRec.StringData(9) = "" oRec.StringData(10) = "" oRec.StringData(11) = "/LogFile=" oRec.StringData(12) = "/LogFile=" oRec.StringData(13) = "/LogFile=" oRec.StringData(14) = "/LogFile=" oView.Modify 7,oRec : CheckError oDB.commit : CheckError oView.close Sub CheckError Dim message, errRec If Err = 0 Then Exit Sub message = Err.Source & " " & Hex(Err) & ": " & Err.Description If Not oMSI Is Nothing Then Set errRec = oMSI.LastErrorRecord If Not errRec Is Nothing Then message = message & vbLf & errRec.FormatText End If Fail message End Sub Any ideas what might be missing here???

Re: Can't insert new record doc <david.daugherty[ at ]gmail.com> 25.06.2007 03:22:16 On Jun 21, 8:52 pm, doc <david.daughe...[ at ]gmail.com> wrote: [Quoted Text] > I'm trying to add a new record to the Component table. I've tried > using a normal INSERT INTO query but can't seem to get the syntax > right. I've been trying to use the temp record approach and sometimes > it works, sometimes it doesn't. I always get an error telling me > insert.vbs(25, 1) Msi API Error: Modify,Mode,Record > > My code looks like: > set oMSI = CreateObject("WindowsInstaller.Installer") > set oDB = oMSI.OpenDatabase("<absolute path>\test.msi",1) : checkError > set oView = oDB.OpenView("SELECT * FROM `Component`") : CheckError > oView.execute : CheckError > set oRec = oView.fetch : CheckError > zero = 0 > one = 1 > > set oRec = oMSI.CreateRecord(14) : CheckError > oRec.StringData(1) = "blah" > oRec.StringData(2) = "{D1ECD74D-A4F3-4DF4-8143-263975AE4D00}" > oRec.StringData(3) = "PROG" > oRec.IntegerData(4) = zero > oRec.StringData(5) = "" > oRec.StringData(6) = "" > oRec.IntegerData(7) = one > oRec.StringData(8) = "" > oRec.StringData(9) = "" > oRec.StringData(10) = "" > oRec.StringData(11) = "/LogFile=" > oRec.StringData(12) = "/LogFile=" > oRec.StringData(13) = "/LogFile=" > oRec.StringData(14) = "/LogFile=" > > oView.Modify 7,oRec : CheckError > oDB.commit : CheckError > oView.close > > Sub CheckError > Dim message, errRec > If Err = 0 Then Exit Sub > message = Err.Source & " " & Hex(Err) & ": " & Err.Description > If Not oMSI Is Nothing Then > Set errRec = oMSI.LastErrorRecord > If Not errRec Is Nothing Then message = message & vbLf & > errRec.FormatText > End If > Fail message > End Sub > > Any ideas what might be missing here??? I WAS finally able to get something similar working using the INSERT INTO query. Would have been much cleaner if I would have been able to get this working.

Re: Can't insert new record "Adrian Accinelli" <hclnospamalias2[ at ]newsgroup.nospam> 25.06.2007 16:31:01 "doc" <david.daugherty[ at ]gmail.com> wrote in message news:1182741736.404776.99850[ at ]w5g2000hsg.googlegroups.com... [Quoted Text] > On Jun 21, 8:52 pm, doc <david.daughe...[ at ]gmail.com> wrote: >> I'm trying to add a new record to the Component table. I've tried >> using a normal INSERT INTO query but can't seem to get the syntax >> right. I've been trying to use the temp record approach and sometimes >> it works, sometimes it doesn't. I always get an error telling me >> insert.vbs(25, 1) Msi API Error: Modify,Mode,Record >> >> My code looks like: >> set oMSI = CreateObject("WindowsInstaller.Installer") >> set oDB = oMSI.OpenDatabase("<absolute path>\test.msi",1) : checkError >> set oView = oDB.OpenView("SELECT * FROM `Component`") : CheckError >> oView.execute : CheckError >> set oRec = oView.fetch : CheckError >> zero = 0 >> one = 1 >> >> set oRec = oMSI.CreateRecord(14) : CheckError >> oRec.StringData(1) = "blah" >> oRec.StringData(2) = "{D1ECD74D-A4F3-4DF4-8143-263975AE4D00}" >> oRec.StringData(3) = "PROG" >> oRec.IntegerData(4) = zero >> oRec.StringData(5) = "" >> oRec.StringData(6) = "" >> oRec.IntegerData(7) = one >> oRec.StringData(8) = "" >> oRec.StringData(9) = "" >> oRec.StringData(10) = "" >> oRec.StringData(11) = "/LogFile=" >> oRec.StringData(12) = "/LogFile=" >> oRec.StringData(13) = "/LogFile=" >> oRec.StringData(14) = "/LogFile=" >> >> oView.Modify 7,oRec : CheckError >> oDB.commit : CheckError >> oView.close >> >> Sub CheckError >> Dim message, errRec >> If Err = 0 Then Exit Sub >> message = Err.Source & " " & Hex(Err) & ": " & Err.Description >> If Not oMSI Is Nothing Then >> Set errRec = oMSI.LastErrorRecord >> If Not errRec Is Nothing Then message = message & vbLf & >> errRec.FormatText >> End If >> Fail message >> End Sub >> >> Any ideas what might be missing here??? > > I WAS finally able to get something similar working using the INSERT > INTO query. Would have been much cleaner if I would have been able to > get this working. > I tried your original code and it works for me. Ok -- it's a bit odd to fetch a record and then create an oversized one and then use that instead but it works. One reason I would see that this failing is if you tried to insert a row whose primary key already exists -- e.g. if you changed the Modify 7 to Modify 1 and ran the script twice, or if temporary row already existed from a previous call. Also I'm not sure about why you think INSERT INTO is not cleaner... It's one view operation (execute) instead of two (execute, modify) so I think that is "cleaner" because there's less code. You are using parameters markers right? As in: set oView = oDB.OpenView("INSERT INTO `Component` (`Component`,`ComponentId`,`Directory_`,`Attributes`,`Condition`,`KeyPath`) VALUES (?, ?, ?, ?, ?, ?) TEMPORARY") : CheckError set oRec = oMSI.CreateRecord(6) : CheckError oRec.StringData(1) = "blah" oRec.StringData(2) = "{D1ECD74D-A4F3-4DF4-8143-263975AE4D00}" oRec.StringData(3) = "PROG" oRec.IntegerData(4) = zero oRec.StringData(5) = "" oRec.StringData(6) = "" oView.execute ( oRec ) : CheckError Sincerely, Adrian Accinelli

Re: Can't insert new record doc <david.daugherty[ at ]gmail.com> 26.06.2007 15:12:57 On Jun 25, 12:31 pm, "Adrian Accinelli" <hclnospamali...[ at ]newsgroup.nospam> wrote: [Quoted Text] > Also I'm not sure about why you think INSERT INTO is not cleaner... It's > one view operation (execute) instead of two (execute, modify) so I think > that is "cleaner" because there's less code. You are using parameters > markers right? As in: > > set oView = oDB.OpenView("INSERT INTO `Component` > (`Component`,`ComponentId`,`Directory_`,`Attributes`,`Condition`,`KeyPath`) > VALUES (?, ?, ?, ?, ?, ?) TEMPORARY") : CheckError > > set oRec = oMSI.CreateRecord(6) : CheckError > oRec.StringData(1) = "blah" > oRec.StringData(2) = "{D1ECD74D-A4F3-4DF4-8143-263975AE4D00}" > oRec.StringData(3) = "PROG" > oRec.IntegerData(4) = zero > oRec.StringData(5) = "" > oRec.StringData(6) = "" > > oView.execute ( oRec ) : CheckError The reason I think it will be cleaner is because it's being used to add hundreds of files to an install and is being used in a iterative loop. Now that you mention it though it's really about the same amount of code using the INSERT INTO statement. Just the INSERT statement gets pretty long.

Re: Can't insert new record "Adrian Accinelli" <hclnospamalias2[ at ]newsgroup.nospam> 27.06.2007 23:57:19 "doc" <david.daugherty[ at ]gmail.com> wrote in message news:1182870777.760533.315410[ at ]i38g2000prf.googlegroups.com... [Quoted Text] > On Jun 25, 12:31 pm, "Adrian Accinelli" >> oView.execute ( oRec ) : CheckError > The reason I think it will be cleaner is because it's being used to > add hundreds of files to an install and is being used in a iterative > loop. Now that you mention it though it's really about the same amount > of code using the INSERT INTO statement. Just the INSERT statement > gets pretty long. > I started thinking about the real performance differences between these two approaches and so I just did a simple test of an increasing number of unique row inserts into the Property table using two methods (and an alternate for the second method). First method is standard Select + ViewModify with no closing of handles in between inserts. The second method is based on Insert into query and uses markers and record changes to deal with the multiple inserts. The alternate second method is the Insert into query but uses only constants and no records. For each case the same steps are taken -- open the same database with transaction, perform the inserts in for loop and then close the database without a commit. The data consists of very small properties based on the current insert number (insert 1 gives Property "1" with value "1", etc). Anyways these results are from single processor P4 without hyper threading and program was C++ compiled with VS2005 SP1: Case 1 is Select + ViewModify. Case 2A is Insert with markers and using record. Case 2B is Insert with constants and no record. Number ------------ Time (MS) ------------ Records Case 1: Case 2A: Case 2B: ------- ------- -------- -------- 1 2.09859 0.662654 0.993143 2 0.916876 0.888102 1.03533 4 0.776356 0.980292 1.42588 8 0.848152 1.36973 2.68861 16 1.02052 2.18575 4.41676 32 1.44432 4.95705 8.46476 64 3.02273 9.12462 18.0699 128 4.04493 23.8019 41.4726 256 7.43083 62.0227 105.307 512 11.2869 171.963 272.722 1024 12.3588 521.68 849.985 2048 19.3667 2083.21 3764.98 It was a bit surprising at first but then I realized that the ViewExecute is clearly more complex than the ViewModify so over the long haul the fewer ViewExecutes there are the faster it goes. So clearly ViewModify is king as long as you insert more than one record. As soon as you close your view though the cost again goes up for the first record with ViewModify so it's best to add everything in one go if possible. Sincerely, Adrian Accinelli