Robert? - Creating a Custom Table dynamically using CREATE TABLE

[andyamstrad]

Is it possible to dynamically create a temporary custom table (Direct Editor) using CREATE TABLE or using any other method?

The idea is I create a table on the fly and populate it at run time during an installation it then dissapears after the end of the instalaltion.

Ive tried using CREATE TABLE cant seem to get it to work, any example code perforking this task would be great.

A temporary workaround is to just create an empty table but it then persists.

Thanks

[RobertDickau]

I haven't tried myself, so I don't know from memory; what code have you tried? What was the error?

[andyamstrad]

RobertDickau wrote:
I haven't tried myself, so I don't know from memory; what code have you tried? What was the error?

Rough Example:
step1 query
CREATE TABLE `Temp` (`ID` INT(2) NOT NULL, `Name` CHAR(72),
NOT NULL PRIMARY KEY `ID`)
step2 create a view - creates handle to view
step3 execute view
step4 ModifyView with view handle , MSIMODIFY_INSERT_TEMPORARY, hRecord = 0

Just wondered if there was any example code floating aroundas I think Im going about this the wrong way, I can select records no problem using SELECT.

Cheers

[RobertDickau]

What language (InstallScript, C, VBScript, ...)? What error number or message?

[RobertDickau]

As a sloppy example (needs error checking, proper cleanup, formatting, spelling checker, sensible variable names, much more), this seems to work as a VBScript custom action:

Const msiViewModifyInsertTemporary = 7

' create the temporary table
Set oView = _
    Database.OpenView("CREATE TABLE `FakeTable`" & _
      "(`FirstColumn` CHAR(72) NOT NULL, " & _
      " `SecondColumn` CHAR(72) PRIMARY KEY `FirstColumn`)")
oView.Execute
oView.Close: Set oView = Nothing

' add one temp record
Set oView2 = Database.OpenView("SELECT * FROM `FakeTable`")
oView2.Execute

Set newrec = Installer.CreateRecord(2)
newrec.StringData(1) = "FakeFirstColumnValue"
newrec.StringData(2) = "FakeSecondColumnValue"

oView2.Modify msiViewModifyInsertTemporary, newrec
oView2.Close: Set oView2 = Nothing

' read back the temp record
Set oView3 = Database.OpenView("SELECT * FROM `FakeTable`")
oView3.Execute
' assuming only one record; normally this is a loop
Set oRec = oView3.Fetch

MsgBox "Reading back temp data: " & _
    oRec.StringData(1) & ", " & oRec.StringData(2)

oView3.Close: Set oView3 = Nothing

Standard caution about database being unloaded between the UI and Execute sequences, so choose the action placement carefully...

[andyamstrad]

Thanks Robert I got it working now, your code pointed me in the right direction.

Incidentally I was using Installscript.