creating tables programmatically-some questions

creating tables programmatically-some questions

• ivanotto

  Hi Alex,
  
  What exactly is your purpose for these tables Have you considered using a temporary cursor instead Have you considered setting up the table once and using GenDBC.prg to create code for you that you can use to create the table/cursor when you need it
  
  Code page translation not allowed - what is it
  Field validation expression, Field Validation Text - unclear
  Table validation expression, Table Validation Text - unclear
  Insert, Update and Delete Trigger Expressions
  
  Are you saying that you don't understand what these things are for or how they are used
  
  Code pages have to do with mapping characters between one character set and another. For example, European languages have accents umlauts and cidillas. These characters could be mapped to their plain equivalents when going from these languages to an English code page.
  
  Table and field validation rules act when data is saved and are used to verify values. An example would be a person's height or weight must be greater than zero.
  
  Insert, Update and Delete triggers happen when rows are committed. For example, they might be used to write to a transaction log.

   

  
  
  

• Sunil Unnithan

  The field/record validation text holds a message you want to show to the user if the rule is broken.
  
  I think "table" validation is a bit of a misnomer. The Fox Help discusses "record" validation, which is the same thing. As the example in the Help discusses (Contents > Microsoft Visual FoxPro 9.0 > Using Visual FoxPro > Developing Visual FoxPro Applications > Working with Data > Working with Tables > Working with Validation Rules), record validation validates one field of a record against another field.
  
  Again, I'd like to know why you've chosen an array as a method to create tables Where are you storing the values for the array In code
  
  An array is a great way to create a temporary cursor to match a table via AFields(). However, I think a sensible way to create tables is using the Create Table - SQL commands, or open an existing table and using Copy Structure.

  
  
  

• TonyBermudez

  Cindy, thanks.
  
  Are you saying that you don't understand what these things are for or how they are used   - Both, but after your explanation I think I can just assign NULL values to those table field parameters.
  
  Of course, I am familiar with the notion of fields validation value but field validation text It does not make sense even after your explanation. For a character field (strings, memos one can wish exclude some ASCI character/value equivalents, for instance ASC("char") > 127, etc but what do they mean by text
  
  "An example would be a person's height or weight must be greater than zero." - this is an example of field validation but how about table validation
  
  "Insert, Update and Delete triggers happen when rows are committed. For example, they might be used to write to a transaction log." A more down to earth example could help more but I feel it is not what I really will be using. Again, I am planning to assign NULL values to those without clear understanding.
  
  I will take a look at GenDBC.prg. Thanks you.
  
  
  
  
  
  

• yudee

  Was out of town last week so could not check on the newsgroup discussions.

  Thank you very much, Cetin. Your answers are always valuable to me.

  
  
  

• RLyons

  Alex,
  As Cindy pointed out check what gendbc generates for a manually created dbc and its tables.
  At runtime sometimes you might need the same code to create a dbc + its tables. If they're temporary tables (most likely cursors or might really be tables) without a dbc (cursors and free tables) then you should take a few things into consideration.
  -Codepage. Don't bother with this. It defaults to current codepage.
  -Codepage translation not allowed. Use this with sensitive data that is generally binary (hence should not be codepage translated when used at another place with a different codepage - if it's a cursor doesn't matter since codepage doesn't change at all). In code correspond to "NoCPTrans".
  -Field validation expression, text: Available when part of a dbc. Validation expression to check for 'valid' when an entry is made, text is message to display for invalid entry. Generally you won't need these.
  -Table validation expression, text. Same as field but row level validation.
  -Trigger expressions are expressions, called if any, when action their name refers to occur.
  
  Now you want to create tables (or cursors) from array arrName. To do that you need a two dimensional array where columncount is a minimum of 4. ie:
  
  FieldName: String
  FieldType: 1 char datatype 
  FieldLen: Total size
  FieldDec: Decimal places
  
  Sample:
  
  dimension aStruc[3,4] && table/cursor with 3 fields
  aStruc[1,1] = "Field1"
  aStruc[1,2] = "C"
  aStruc[1,3] = 10
  aStruc[1,4] = 0
  aStruc[2,1] = "Field2"
  aStruc[1,2] = "D"
  aStruc[1,3] = 8
  aStruc[1,4] = 0
  aStruc[2,1] = "Field3"
  aStruc[1,2] = "I"
  aStruc[1,3] = 4
  aStruc[1,4] = 0
  create cursor myCursor from array aStruc
  * Correspond to
  create cursor myCursor (Field1 c(10), Field2 d, Field3 i)
  
  
  You can use afields() but be warned. afields() have 16 or 18 columns depending on version. That means if you create a cursor or table directly from an afields() result then you're also transferring extra info like trigger expression, table validation etc. That might lead to unpredictable results (ie: If table/cursor used in afields had trigger, that trigger would fire when you use your new table/cursor). Browse recent threads related with that to see how you could eliminate afields() extra columns.
  PS:As Cindy noted think twice if you need a table or cursor. Generally what you need is a cursor but users tend to think "this can't be done with a cursor" and create unnecessary tables. 
  

• Zhimao Guo

  Cindy, thanks.

  "Again, I'd like to know why you've chosen an array as a method to create tables Where are you storing the values for the array In code "

  Well, I want to create tables programmatically in FoxPro. I have been doing it for years in Visual dBase. It is a very convenient method to update structure, add/remove indices, shorten/lenghten the field length (size) especially for character fields, etc. I am so used to it. When I started looking into it from the FoxPro standpoing I found out that I could do it by assigning field parameters to an array. Actually it did not work for me. Errors ensued and I now use a standard form of CREAT TABLE SQL command with extensive involvement of macro substitutions.

  I still have some trouble with indices. I will address it in a separate post.

  
   

   

   

  
  
  
• creating tables programmatically-some questions