| Subject | Re: Field Names in a Table |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Mon, 19 Feb 2018 17:36:58 +0200 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | create_tablecode.wfm |
On 2017-07-03 6:59 PM, steve dupuie wrote: > > Sorry I'm late getting back. I just tried using the alter table command, and it works. This is going to be grueling. > The problem stems from using an older version of dBASE with a newer version of Windows. If you upgrade your dBASE to 11.3 it will play nicely with Windows 10 and the table designer will work properly. In the meantime though, as you say, gruelling. I post many little example programs in the newsgroup and to make them self-contained I need to create and populate tables. The easiest way to do this is to use CREATE TABLE to create the table and then INSERT INTO to place data in the files. With this code in the header section of a form the table(s) are created before the form opens and the form can use it. Writing suitable code by hand was more than gruelling, it was a PITA. :-) To get round this I wrote a little program that would generate the code for me. I have used this little program as a starting point to create a DIY table designer. The resulting program has some major limitations. Unfortunately I'm busy with two separate projects and so I can't spend any more time on this now. The program only creates tables in the same folder as the program. There is a limit to the length of a CREATE TABLE command. This means there is a limit to the number of fields that an be created. This number is not a specific number as it depends on the length of the fieldnames and the type of fields. I'm not sure what the limit is but cretable7.prg in the dUFLP set the limit to 1024 characters. If additional fields are required it is necessary to use UPDATE TABLE to add them one by one. As I was only making small example tables I didn't need to do this. I'll probably get round to fixing it later but I'm afraid it's not high on the priority list. If this limit affects you now then feel free to modify the program. Cretable7.prg should give you an idea of how to do this. If the 'field_type' column is left blank (or c is entered) a character field is created. If the 'field-spec' column is left blank the values set in the entryfields at the top of the form are used. Only single characters are needed for the 'field_type' column. You can look in the code to see which characters will be accepted. Mervyn. | |
if file('mbtemptable.dbf') drop table mbtemptable endif if not file('mbtemptable.dbf') create table mbtemptable (name character(20),field_type character(3),field_spec character(10)) endif if file('mbtempdata.dbf') drop table mbtempdata endif ** END HEADER -- do not remove this line // // Generated on 2018/02/19 // parameter bModal local f f = new create_tablecodeForm() if (bModal) f.mdi = false // ensure not MDI f.readModal() else f.open() endif class create_tablecodeForm of FORM with (this) height = 31.1364 left = 55.5714 top = 0.4091 width = 81.4286 text = "Table designer" endwith this.MBTEMPTABLE1 = new QUERY(this) with (this.MBTEMPTABLE1) width = 10.0 height = 1.0 sql = 'select * from "mbtemptable.DBF"' active = true endwith this.ENTRYFIELD1 = new ENTRYFIELD(this) with (this.ENTRYFIELD1) onKey = class::ENTRYFIELD1_ONKEY height = 1.0 left = 22.0 top = 1.3636 width = 18.4286 value = " " endwith this.TEXTLABEL1 = new TEXTLABEL(this) with (this.TEXTLABEL1) height = 1.0 left = 8.7143 top = 1.3636 width = 12.0 text = "Name of table" endwith this.GRID1 = new GRID(this) with (this.GRID1) dataLink = form.mbtemptable1.rowset height = 20.5909 left = 6.2857 top = 5.4091 width = 66.0 endwith this.PUSHBUTTON2 = new PUSHBUTTON(this) with (this.PUSHBUTTON2) onClick = class::PUSHBUTTON2_ONCLICK height = 1.0909 left = 32.0 top = 27.5 width = 17.8571 text = "Create table" endwith this.TEXTLABEL4 = new TEXTLABEL(this) with (this.TEXTLABEL4) height = 1.0 left = 9.0 top = 3.6818 width = 12.0 text = "Default char" endwith this.TEXTLABEL5 = new TEXTLABEL(this) with (this.TEXTLABEL5) height = 1.0 left = 35.4286 top = 3.6818 width = 12.0 text = "Default num" endwith this.ENTRYFIELD4 = new ENTRYFIELD(this) with (this.ENTRYFIELD4) onKey = class::ENTRYFIELD4_ONKEY height = 1.0 left = 22.0 top = 3.6818 width = 8.0 value = "15" endwith this.ENTRYFIELD5 = new ENTRYFIELD(this) with (this.ENTRYFIELD5) onKey = class::ENTRYFIELD5_ONKEY height = 1.0 left = 48.2857 top = 3.6818 width = 8.0 value = "10,2" endwith this.PUSHBUTTON5 = new PUSHBUTTON(this) with (this.PUSHBUTTON5) onClick = class::PUSHBUTTON5_ONCLICK height = 1.0909 left = 3.5714 top = 27.5 width = 15.2857 text = "Clear " endwith this.rowset = this.mbtemptable1.rowset function ENTRYFIELD1_onKey(nChar, nPosition,bShift,bControl) if nChar = 13 or nChar = 9 form.entryfield4.setfocus() endif return function ENTRYFIELD4_onKey(nChar, nPosition,bShift,bControl) if nChar = 13 or nChar = 9 form.entryfield5.setfocus() endif return function ENTRYFIELD5_onKey(nChar, nPosition,bShift,bControl) if nChar = 13 or nChar = 9 form.addrow() form.grid1.setfocus() endif return function addrow use mbtemptable append blank use if type("form.mbtemptable1") # "U" form.mbtemptable1.active = false form.mbtemptable1.active = true endif form.grid1.setfocus() return function PUSHBUTTON2_onClick if "."$form.entryfield1.value form.entryfield1.value = left(form.entryfield1.value,at(".",form.entryfield1.value)-1) endif if empty(form.entryfield1.value) form.entryfield1.value = "mbtesttable" endif cFile = trim(form.entryfield1.value)+'.dbf' if file(cFile) cmd = 'drop table '+form.entryfield1.value &cmd endif cStr= "create table "+form.entryfield1.value+" (" form.rowset.first() cStr += trim(form.rowset.fields["name"].value) cStr += " "+class::convert_type() form.rowset.next() do while not form.rowset.endofset cStr += "," cStr += trim(form.rowset.fields["name"].value) cStr += " "+class::convert_type() form.rowset.next() enddo cStr += ")" &cStr return function PUSHBUTTON5_onClick clear form.mbtemptable1.active = false if type("form.mbtempdata") # "U" form.mbtempdata.active = false endif if file('mbtemptable.dbf') drop table mbtemptable endif if not file('mbtemptable.dbf') create table mbtemptable (name character(20),field_type character(3),field_spec character(10)) form.mbtemptable1.active = false form.mbtemptable1.active = true form.grid1.datalink = form.mbtemptable1.rowset endif if file('mbtempdata.dbf') drop table mbtempdata endif form.entryfield1.value = "" form.entryfield1.setfocus() return function convert_type local cNewtype do case case form.rowset.fields["field_type"].value.toUpperCase() = " " cNewType = "character(" if not empty(form.rowset.fields["field_spec"].value) cNewType += trim(form.rowset.fields["field_spec"].value)+")" else cNewType += form.entryfield4.value + ")" endif case form.rowset.fields["field_type"].value.toUpperCase() = "C" cNewType = "character(" if not empty(form.rowset.fields["field_spec"].value) cNewType += trim(form.rowset.fields["field_spec"].value)+")" else cNewType += form.entryfield4.value + ")" endif case form.rowset.fields["field_type"].value.toUpperCase() = "N" cNewType = "numeric(" if not empty(form.rowset.fields["field_spec"].value) cNewType += trim(form.rowset.fields["field_spec"].value)+")" else cNewType += form.entryfield5.value + ")" endif case form.rowset.fields["field_type"].value.toUpperCase() = "F" cNewType = "float" case form.rowset.fields["field_type"].value.toUppercase() = "I" cNewType ="integer" case form.rowset.fields["field_type"].value.toUpperCase() = "A" cNewType = "autoinc" case form.rowset.fields["field_type"].value.toUpperCase() = "D" cNewType = "date" case form.rowset.fields["field_type"].value.toUpperCase() = "T" cNewType = "timestamp" case form.rowset.fields["field_type"].value.toUpperCase() = "L" cNewtype = "boolean" case form.rowset.fields["field_type"].value.toUpperCase() = "M" cNewtype = "blob(0,1)" case form.rowset.fields["field_type"].value.toUpperCase() = "B" cNewtype = "blob(0,2)" case form.rowset.fields["field_type"].value.toUpperCase() = "O" cNewtype = "blob(0,4)" endcase return cNewType function shapeData(data,cType) do case case upper(cType) = "C" shapedData = '"'+trim(data)+'"' case upper(cType) = "N" shapedData = data case upper(cType) = "F" shapedData = data case upper(cType) = "D" shapedData = "'"+str(data.month+1,2,0,"0")+"/"+str(data.date,2,0,"0") shapedData += "/"+str(data.year,4,0)+"'" case upper(cType) = "T" shapedData = '"'+trim(data)+'"' case upper(cType) = "L" if data = true shapedData = "true" else shapedData = "false" endif case upper(cType) = "M" shapedData = '"'+data+'"' case upper(cType) = "B" shapedData = "null" case upper(cType) = "O" shapedData = "null" otherwise shapedData = '"'+trim(data)+'"' endcase return shapedData endclass | |