fatal error with create table

[wlemery]

I'm trying to create a new table programmatically from within a PRG or stored procedure. The table is created, but not added to the DBC and VFP crashes with the C0 fatal exception error. The fatal crash is inconsistent in that it will crash on some tables and not on others. I've included code that reproduces the error.

Can someone please point me in the right direction for a solution to this one?

-- begin code --
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* 7/17/2007
* When the program runs everything works as it should up until the
* audit trail table gets created in the trigger function sp_create_audit_table.
* The table gets created but is not added to the DBC and VFP goes belly up
* with Exception code=C0000005. This code reproduces the error.
*
* I'm on VFP 8 SP 1 on Windows 2K Pro SP4.
*
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


Close Data All
Create Database 'BDMA.DBC'

Create Table 'LAB_TRAINING_RECORDS.DBF' Name 'LAB_TRAINING_RECORDS' (Key I Not Null Autoinc Nextvalue 1 Step 1, ;
USERINFO_KEY I Null, ;
USERINFO_NAME C(10) Null, ;
USERINFO_REPNAME C(25) Null, ;
LAB_PROCEDURE_KEY I Null, ;
LAB_PROCEDURE_NAME C(50) Null, ;
LAB_PROCEDURE_GROUP C(50) Null, ;
LAB_PROCEDURE_SOP_NBR C(8) Null, ;
LAB_PROCEDURE_EFFECTIVE_DATE D Null, ;
DATE_READ D Null, ;
DATE_OBSERVED D Null, ;
INSTRUCTOR_OBSERVED_USERINFO_KEY I Null, ;
INSTRUCTOR_OBSERVED_USERINFO_NAME C(10) Null, ;
INSTRUCTOR_OBSERVED_USERINFO_REPNAME C(25) Null, ;
INSTRUCTOR_OBSERVED_INITIALS C(3) Null, ;
DATE_COMPLETE D Null, ;
INSTRUCTOR_COMPLETE_USERINFO_KEY I Null, ;
INSTRUCTOR_COMPLETE_USERINFO_NAME C(10) Null, ;
INSTRUCTOR_COMPLETE_USERINFO_REPNAME C(25) Null, ;
INSTRUCTOR_COMPLETE_INITIALS C(3) Null, ;
COMMENTS M Null)

***** Create each index for LAB_TRAINING_RECORDS *****
Index On LAB_PROCEDURE_SOP_NBR Tag SOP_NBR Collate 'MACHINE'
Index On INSTRUCTOR_COMPLETE_USERINFO_REPNAME Tag INSTCMPL Collate 'MACHINE'
Index On INSTRUCTOR_OBSERVED_USERINFO_REPNAME Tag INSTOBSV Collate 'MACHINE'
Index On LAB_PROCEDURE_NAME Tag PROCNAME Collate 'MACHINE'
Index On USERINFO_REPNAME Tag EMPNAME Collate 'MACHINE'
Index On Key Tag Key Collate 'MACHINE'
Index On LAB_PROCEDURE_NAME+Dtoc(DATE_READ,1) Tag PROCDRED Collate 'MACHINE'
Index On LAB_PROCEDURE_NAME+Dtoc(DATE_OBSERVED,1) Tag PROCDOBS Collate 'MACHINE'
Index On LAB_PROCEDURE_NAME+Dtoc(DATE_COMPLETE,1) Tag PROCDCMP Collate 'MACHINE'

***** Change properties for LAB_TRAINING_RECORDS *****
DBSetProp('LAB_TRAINING_RECORDS.KEY', 'Field', 'Comment', "Record ID - Primary Key")
DBSetProp('LAB_TRAINING_RECORDS.USERINFO_KEY', 'Field', 'Comment', "Key to the userinfo table for the subject of record.")
DBSetProp('LAB_TRAINING_RECORDS.USERINFO_NAME', 'Field', 'Comment', "Login ID from userinfo for the subject of record.")
DBSetProp('LAB_TRAINING_RECORDS.USERINFO_REPNAME', 'Field', 'Comment', "User's name from the userinfo table for the subject of record. This is the name of the person the record holds the data for.")
DBSetProp('LAB_TRAINING_RECORDS.LAB_PROCEDURE_KEY', 'Field', 'Comment', "Foreign key to lab_procedure for the selected procedure.")
DBSetProp('LAB_TRAINING_RECORDS.LAB_PROCEDURE_NAME', 'Field', 'Comment', "Name of the procedure.")
DBSetProp('LAB_TRAINING_RECORDS.LAB_PROCEDURE_GROUP', 'Field', 'Comment', "Group name, from lab_procedure")
DBSetProp('LAB_TRAINING_RECORDS.LAB_PROCEDURE_SOP_NBR', 'Field', 'Comment', "SOP Number from lab_procedure.")
DBSetProp('LAB_TRAINING_RECORDS.LAB_PROCEDURE_EFFECTIVE_DATE', 'Field', 'Comment', "Effective date of the procedure, from lab_procedure.")
DBSetProp('LAB_TRAINING_RECORDS.DATE_READ', 'Field', 'Comment', "Date that the staff read the procedure.")
DBSetProp('LAB_TRAINING_RECORDS.DATE_OBSERVED', 'Field', 'Comment', "Date that the staff observed the procedure.")
DBSetProp('LAB_TRAINING_RECORDS.INSTRUCTOR_OBSERVED_USERINFO_KEY', 'Field', 'Comment', "Instructor in charge of observation procedure. Foreign key to userinfo.")
DBSetProp('LAB_TRAINING_RECORDS.INSTRUCTOR_OBSERVED_USERINFO_NAME', 'Field', 'Comment', "Login ID from userinfo.name")
DBSetProp('LAB_TRAINING_RECORDS.INSTRUCTOR_OBSERVED_USERINFO_REPNAME', 'Field', 'Comment', "Instructor name from userinfo.repname")
DBSetProp('LAB_TRAINING_RECORDS.INSTRUCTOR_OBSERVED_INITIALS', 'Field', 'Comment', "Initials of the instructor in charge of observation derived from userinfo.repname.")
DBSetProp('LAB_TRAINING_RECORDS.DATE_COMPLETE', 'Field', 'Comment', "Date that the training was completed.")
DBSetProp('LAB_TRAINING_RECORDS.INSTRUCTOR_COMPLETE_USERINFO_KEY', 'Field', 'Comment', "Instructor in charge of completing procedure. Foreign key to users.dbf.")
DBSetProp('LAB_TRAINING_RECORDS.INSTRUCTOR_COMPLETE_USERINFO_NAME', 'Field', 'Comment', "Login ID from userinfo.name")
DBSetProp('LAB_TRAINING_RECORDS.INSTRUCTOR_COMPLETE_USERINFO_REPNAME', 'Field', 'Comment', "Instructor's name from userinfo.repname")
DBSetProp('LAB_TRAINING_RECORDS.INSTRUCTOR_COMPLETE_INITIALS', 'Field', 'Comment', "Initials of the instructor in charge of completing procedure. Derived from userinfo.repname.")
DBSetProp('LAB_TRAINING_RECORDS.COMMENTS', 'Field', 'Comment', "Free form comments.")
DBSetProp('LAB_TRAINING_RECORDS', 'Table', 'Comment', "Holds the procedure training records for each person in the lab. Has a many to many relation with the userinfo.dbf." + Chr(13) + "")
Create Trigger On 'LAB_TRAINING_RECORDS' For Delete As sp_table_general_trigger("BDMA","Delete")
Create Trigger On 'LAB_TRAINING_RECORDS' For Insert As sp_table_general_trigger("BDMA","Insert")
Create Trigger On 'LAB_TRAINING_RECORDS' For Update As sp_table_general_trigger("BDMA","Update")

Close Databases All
Clear All
Clear

Open Database bdma Shared
Use 'LAB_TRAINING_RECORDS' Shared

Append Blank


Return


* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* 7/17/2007
* Called on the triggers for Insert, Update and Delete.
* The procedure writes the current record to an audit trail table. If
* the audit trail table doesn't exist, it is created.
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Function sp_table_general_trigger
Lparameters cDataBaseContainer, cAction

Local v1, cAlias, oCurrentRec, nCurrentArea, cCurrentDBCAlias

nCurrentArea = Select(0)
cCurrentDBCAlias = Juststem(Dbc())

Set Database To (cDataBaseContainer)

cAlias = Alias()
Select (cAlias)

Scatter Memo Name oCurrentRec

Local cAuditTrailTableName, cAuditTrailAlias, cDBCAlias, cAuditTrailDbcDbf

cAuditTrailTableName = Addbs(Justpath(Dbf())) + Juststem(Dbf()) + "_AUDIT_TRAIL.DBF"
cAuditTrailAlias = Juststem(cAuditTrailTableName)
cDBCAlias = Juststem(Dbc())
cAuditTrailDbcDbf = cDBCAlias + "!" + cAuditTrailAlias

If !File(cAuditTrailTableName)
Do sp_create_audit_table With cAlias
Endif

If !Used((cAuditTrailAlias))
Use (cAuditTrailDbcDbf) In 0 Alias (cAuditTrailAlias) Shared Again
Endif

= AddProperty(oCurrentRec, "user_action", cAction)

Insert Into (cAuditTrailAlias) From Name oCurrentRec

Set Database To (cCurrentDBCAlias)
Select (nCurrentArea)
Return .T.


* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* 7/17/2007
* Code to create the audit table. The table will have the same name
* as specified in the argument cTableAlias with the addition of the
* suffix '_audit_trail'. The structure is altered to add three new
* columns.
*
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Procedure sp_create_audit_table

Lparameters cTableAlias

Local nCurrentArea
nCurrentArea = Select(0)

Select (cTableAlias)

Local cAuditTrailTableName, cAuditTrailAlias, cDBCAlias, cAuditTrailDbcDbf

cAuditTrailTableName = Addbs(Justpath(Dbf())) + Juststem(Dbf()) + "_AUDIT_TRAIL.DBF"
cAuditTrailAlias = Juststem(cAuditTrailTableName)
cDBCAlias = Juststem(Dbc())
cAuditTrailDbcDbf = cDBCAlias + "!" + cAuditTrailAlias

If !File(cAuditTrailTableName) And !Indbc(cAuditTrailAlias,"TABLE")
Local nFields, nSub1
Local Array aFieldsArray(1)

nFields = Afields(aFieldsArray, (cTableAlias))
For nSub1 = 1 To nFields Step 1

aFieldsArray(nSub1, 5) = .T. && NULL values always allowed
aFieldsArray(nSub1, 7) = "" && Field validation is NULL
aFieldsArray(nSub1, 8) = "" && Field validation Text is NULL
aFieldsArray(nSub1, 9) = "" && Field default value is NULL
aFieldsArray(nSub1, 10) = "" && Table validation is NULL
aFieldsArray(nSub1, 11) = "" && Table validation Text is NULL
aFieldsArray(nSub1, 13) = "" && Table Insert trigger is NULL
aFieldsArray(nSub1, 14) = "" && Table Update trigger is NULL
aFieldsArray(nSub1, 15) = "" && Table Delete trigger is NULL
aFieldsArray(nSub1, 16) = aFieldsArray(nSub1, 16) + " Audit Trail" && Table Comment
aFieldsArray(nSub1, 17) = 0 && Autoinc value is turned off
aFieldsArray(nSub1, 18) = 0 && Autoinc value is turned off

Next nSub1

nFields = nFields + 1
Dimension aFieldsArray(nFields, 18)
aFieldsArray(nFields, 1) = "User_ID" && Field Name
aFieldsArray(nFields, 2) = "C" && Type
aFieldsArray(nFields, 3) = 25 && Width
aFieldsArray(nFields, 4) = 0 && Decimals
aFieldsArray(nFields, 5) = .T. && NULL values always allowed
aFieldsArray(nFields, 6) = .F. && Code page translation not allowed
aFieldsArray(nFields, 7) = "" && Field validation is NULL
aFieldsArray(nFields, 8) = "" && Field validation Text is NULL
aFieldsArray(nFields, 9) = "Sys(0)" && Field default value
aFieldsArray(nFields, 10) = "" && Table validation is NULL
aFieldsArray(nFields, 11) = "" && Table validation Text is NULL
aFieldsArray(nFields, 13) = "" && Table Insert trigger is NULL
aFieldsArray(nFields, 14) = "" && Table Update trigger is NULL
aFieldsArray(nFields, 15) = "" && Table Delete trigger is NULL
aFieldsArray(nFields, 16) = ""
aFieldsArray(nFields, 17) = 0 && Autoinc value is turned off
aFieldsArray(nFields, 18) = 0 && Autoinc value is turned off

nFields = nFields + 1
Dimension aFieldsArray(nFields, 18)
aFieldsArray(nFields, 1) = "User_DateTime" && Field Name
aFieldsArray(nFields, 2) = "T" && Type
aFieldsArray(nFields, 3) = 0 && Width
aFieldsArray(nFields, 4) = 0 && Decimals
aFieldsArray(nFields, 5) = .T. && NULL values always allowed
aFieldsArray(nFields, 6) = .F. && Code page translation not allowed
aFieldsArray(nFields, 7) = "" && Field validation is NULL
aFieldsArray(nFields, 8) = "" && Field validation Text is NULL
aFieldsArray(nFields, 9) = "DateTime()" && Field default value
aFieldsArray(nFields, 10) = "" && Table validation is NULL
aFieldsArray(nFields, 11) = "" && Table validation Text is NULL
aFieldsArray(nFields, 13) = "" && Table Insert trigger is NULL
aFieldsArray(nFields, 14) = "" && Table Update trigger is NULL
aFieldsArray(nFields, 15) = "" && Table Delete trigger is NULL
aFieldsArray(nFields, 16) = ""
aFieldsArray(nFields, 17) = 0 && Autoinc value is turned off
aFieldsArray(nFields, 18) = 0 && Autoinc value is turned off

nFields = nFields + 1
Dimension aFieldsArray(nFields, 18)
aFieldsArray(nFields, 1) = "User_Action" && Field Name
aFieldsArray(nFields, 2) = "C" && Type
aFieldsArray(nFields, 3) = 6 && Width
aFieldsArray(nFields, 4) = 0 && Decimals
aFieldsArray(nFields, 5) = .T. && NULL values always allowed
aFieldsArray(nFields, 6) = .F. && Code page translation not allowed
aFieldsArray(nFields, 7) = "" && Field validation is NULL
aFieldsArray(nFields, 8) = "" && Field validation Text is NULL
aFieldsArray(nFields, 9) = "" && Field default value
aFieldsArray(nFields, 10) = "" && Table validation is NULL
aFieldsArray(nFields, 11) = "" && Table validation Text is NULL
aFieldsArray(nFields, 13) = "" && Table Insert trigger is NULL
aFieldsArray(nFields, 14) = "" && Table Update trigger is NULL
aFieldsArray(nFields, 15) = "" && Table Delete trigger is NULL
aFieldsArray(nFields, 16) = ""
aFieldsArray(nFields, 17) = 0 && Autoinc value is turned off
aFieldsArray(nFields, 18) = 0 && Autoinc value is turned off

Select 0

Create Table (cAuditTrailTableName) Name (cAuditTrailAlias) From Array aFieldsArray

Use In (cAuditTrailAlias)

If !Indbc(cAuditTrailAlias,"TABLE" )
Add Table (cAuditTrailTableName) Name (cAuditTrailAlias)
Endif

Endif

Return .T.

-- end code --

 

[Olaf Doschke]

Your code...

Create Table (cAuditTrailTableName) Name (cAuditTrailAlias) From Array aFieldsArray

Use In (cAuditTrailAlias)

If !Indbc(cAuditTrailAlias,"TABLE" )
   Add Table (cAuditTrailTableName) Name (cAuditTrailAlias)
Endif

...is problematic if any user has an open transaction in any database table. Then you don't explicitly create the table as a free one, it will be added to the open dbc, that's why you get the error.

You could CREATE CURSOR ... FROM ARRAY and then COPY TO ... DATABASE (DBC()).

Instead of creating the array with AFIELDs, deleting validation rules etc. you can easily create the cursor as Select *, cast("" as C(25)) as User_ID, ... from (cTableAlias) Where .F. into Cursor curAuditTrail Nofilter. That way you don't copy those things.

Bye, Olaf.

 

[AndyKr]

>>. The table is created, but not added to the DBC and VFP crashes with the C0 fatal exception error. The fatal crash is inconsistent in that it will crash on some tables and not on others. I've included code that reproduces the error.

Since you are hard-coding all the audit trail elements why not simply use COPY STRUCTURE EXTENDED to get the source table structure into a table, then you can add the extra fields to the structure table (using ALTER TABLE) and finally create the new table using

CREATE

DATABASE [dbc] FROM [struc_table]

It is a lot quicker, and simpler than messing around with the arrays.


----
Andy Kramek
Visual FoxPro MVP

 

[wlemery]

AndyKr: why not simply use COPY STRUCTURE EXTENDED

Principally because I didn't think of it. Revising the code to implement the method you suggest produces the same error.

OlafDoschke: is problematic if any user has an open transaction in any database table.

Truth, but I'm using it under strictly controlled conditions. Following your suggestion produces a fatal error as soon as I try and add the newly created table to the DBC.

My thanks to both of you for your suggestions. I've decided to scrap the project and put the code into a separate PRG file which I can run as a utility, and which does not produce the fatal error.

 

[dgrewe]

Wlemery,
Where exactly do you get the error in code above?
What line?



David W. Grewe Dave