CREATE DATABASE within a transaction

[Olaf Doschke]

Creating a database is one of the non working commands within a transaction (see help topic of BEGIN TRANSACTION).

But we can still create files and in the simplest case we could copy an empty dbc/dct/dcx file triple and rename them to whatever DBC name we need.
We can also do that without having such template files available, though:

#Define ccNewDBC "mynew.dbc"

Cd (GetEnv("TEMP"))

Begin Transaction 
* Just to demonstrate the CREATE DATABASE statement errors:
Try
  Create Database (ccNewDBC)
Catch to loException 
  ? "catch:",loException.Message
EndTry

* Create a DBC by creating a DBF, first as Cursor
Create Cursor newdb (objectid i, parentid i, objecttype c(10), objectname c(128), property M nocptran, code M nocptran, riinfo c(6), user M)
Index On STR(parentid)+objecttype+LOWER(objectname) TAG OBJECTNAME  COLLATE 'Machine' FOR !DELETED()
Index On STR(parentid)+objecttype TAG OBJECTTYPE COLLATE 'Machine' FOR !DELETED()
Insert Into newdb Values (1,1,'Database','Database',0h0B0000000100180000000A,"","","")
Insert Into newdb Values (2,1,'Database','TransactionLog',"","","")
Insert Into newdb Values (3,1,'Database','StoredProceduresSource',"","","")
Insert Into newdb Values (4,1,'Database','StoredProceduresObject',"","","")
Insert Into newdb Values (5,1,'Database','StoredProceduresDependencies',"","","")

* Copy that data including CDX to disc:
Copy To (ccNewDBC) WITH CDX
Use

*Need to change 1 bit as flag for dbc
h = FOpen(ccNewDBC,12)
FSeek(h,28)
FWrite(h,Chr(7),1)
FClose(h)

* Now the DBC can be used as usual
Open Database (ccNewDBC)
End Transaction

The files differ binary from a normal DBC, mainly because the index expressions CREATE DATABASE creates are not all upper case. There are some other binary differences, but the resulting DBC still works.

Notice: The dbc file ending must be specified, so the COPY TO command creates dbc/dct/dcx instead of dbf/fpt/cdx file extensions. Otherwise the files would need to be renamed.

 

[vgulielmus]

Very interesting.
I haven't tested yet, but I'll surely do.
Could be useful in the future and I'm grateful for sharing.

Thank you

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

 

[Olaf Doschke]

The reason I need it is for doing transaction logging into DBFs created on the fly in DBCs created on the fly. I could create a DBC as setup step, but it's beyond a small talk why I want to be able to even create a DBC within a transaction. Let's say some code in a timer has no own control, if it runs within a transaction, but needs a private and new DBC anyway and shouldn't wait for the end of the current transaction(s).

Besides all this it's always fun to do things that are said to be impossible. And in this case I don't see a point in that restriction of the VFP language.

Bye, Olaf.