Créer un fichier DBF dBase
Ce programme me permet de créer une table dBase DBF de niveau 7.
Les champs créés sont ceux possibles avec la commande CREATE TABLE.
Attention aux noms des champs interdits, je ne les connais pas tous, modifier si besoin this.Tableau2
J'utilise la version de dBasplus 2.01 (DB2K).
* CreeUneTable.wfm - Créer une table DBF dBase.
* ---------------------------------------------
*
/*
En cours :
A faire :
- Mettre message d'attente pendant création et importation
- Insertion de champ
- Copy and Paste
- Voir _DBASELOCK
- Mettre verrou si 1er nom de champ est un chiffre
- Faire tableau des noms déjà saisis et vérifier à la saisie et la création
*/
*
LOCAL F
F = new CreeTable()
F.readModal()
*
* ------------------------------------------------------------------------------
CLASS CreeTable of FORM
* ------------------------------------------------------------------------------
*
* Table des informations de la table à créer.
*
IF _app.databases[1].tableExists("0CreeTableInfo35854698.dbf")
_app.databases[1].dropTable("0CreeTableInfo35854698.dbf")
ENDIF
*
CREATE TABLE "0CreeTableInfo35854698.dbf" ( ;
ChampNom CHAR(64) , ;
ChampType CHAR(16) , ;
ChampLong NUMERIC(16), ;
ChampDec NUMERIC(10))
*
this.Tableau1 = new Array() && Tableau choix du type.
this.Tableau1.add("Caractère")
this.Tableau1.add("Numérique")
this.Tableau1.add("Date")
this.Tableau1.add("Logique")
this.Tableau1.add("Double")
this.Tableau1.add("Mémo")
this.Tableau1.add("Binaire")
this.Tableau1.add("OLE")
this.Tableau1.add("Timestamp")
this.Tableau1.add("Integrer Long")
this.Tableau1.add("AutoIncrement")
*
this.Tableau2 = new Array() && Noms de champs interdits CREATE TABLE.
this.Tableau2.add("AUTOINC")
this.Tableau2.add("CHAR")
this.Tableau2.add("CHARACTER")
this.Tableau2.add("DATE")
this.Tableau2.add("DOUBLE")
this.Tableau2.add("FLOAT")
this.Tableau2.add("INT")
this.Tableau2.add("LONG")
this.Tableau2.add("NUMERIC")
this.Tableau2.add("OF")
this.Tableau2.add("TIMESTAMP")
*
this.Dossier = set("Directory")
this.Enregistre = 0 && Information enregistré.
this.TableNom = null && Nom de la table avec chemin.
*
with (this)
mdi := .F.
sysMenu := .F.
escExit := .F.
height := 18
left := 30
top := 2
width := 90.5
text := "Créer une table"
onOpen := class::Form_OnOpen
endwith
*
this.QUERY1 = new QUERY()
this.QUERY1.parent = this
with (this.QUERY1)
left := -0.1429
top := -0.0455
sql := 'select * from "0CreeTableInfo35854698.dbf"'
active := .T.
parent := this.QUERY1.rowset
rowset.fields["ChampType"].onChange := class::Field_OnChange
rowset.fields["ChampType"].Forme = form
rowset.fields["ChampLong"].onChange := class::Field_OnChange
rowset.fields["ChampLong"].Forme = form
rowset.fields["ChampDec"].onChange := class::Field_OnChange
rowset.fields["ChampDec"].Forme = form
endwith
*
this.GRID1 = new GRID(this)
with (this.GRID1)
dataLink = form.QUERY1.rowset
*
columns["COLUMN1"] = new GRIDCOLUMN(form.GRID1)
columns["COLUMN1"].dataLink := form.QUERY1.rowset.fields["ChampNom"]
columns["COLUMN1"].editorType := 1 && Entryfield
columns["COLUMN1"].width := 30
columns["COLUMN1"].headingControl.value := "Nom"
*
columns["COLUMN2"] = new GRIDCOLUMN(form.GRID1)
columns["COLUMN2"].dataLink := form.QUERY1.rowset.fields["ChampType"]
columns["COLUMN2"].editorType := 4 && Combobox
columns["COLUMN2"].width := 20
columns["COLUMN2"].headingControl.value := "Type"
columns["COLUMN2"].editorControl.dataSource := "array form.Tableau1"
columns["COLUMN2"].editorControl.dropDownHeight := 14
*
columns["COLUMN3"] = new GRIDCOLUMN(form.GRID1)
columns["COLUMN3"].dataLink := form.QUERY1.rowset.fields["ChampLong"]
columns["COLUMN3"].editorType := 3 && Spinbox
columns["COLUMN3"].width := 20
columns["COLUMN3"].headingControl.value := "Longueur"
*
columns["COLUMN4"] = new GRIDCOLUMN(form.GRID1)
columns["COLUMN4"].dataLink := form.QUERY1.rowset.fields["ChampDec"]
columns["COLUMN4"].editorType := 3 && Spinbox
columns["COLUMN4"].width := 14
columns["COLUMN4"].headingControl.value := "Décimale"
*
allowColumnMoving := .F.
allowColumnSizing := .F.
allowRowSizing := .F.
cellHeight := 1
allowAddRows := .F.
height := 15
left := 0.9
top := 0
width := form.width
endwith
*
this.Pb1 = new PUSHBUTTON(this)
with (this.Pb1)
onClick := class::Ajouter
height := 1
left := this.GRID1.left
top := this.GRID1.top + this.GRID1.height + 0.1
width := 14.06
text := "Ajouter"
endwith
*
this.Pb2 = new PUSHBUTTON(this)
with (this.Pb2)
onClick := class::Detruit
height := this.Pb1.height
left := this.Pb1.left + this.Pb1.width + 1
top := this.Pb1.top
width := this.Pb1.width
text := "Supprimer"
endwith
*
this.Pb3 = new PUSHBUTTON(this)
with (this.Pb3)
onClick := class::Importer
height := this.Pb2.height
left := this.Pb2.left + this.Pb2.width + 1
top := this.Pb2.top
width := this.Pb2.width
text := "Import"
endwith
*
this.Pb4 = new PUSHBUTTON(this)
with (this.Pb4)
onClick := class::DossierCherche
height := this.Pb3.height
left := this.Pb3.left + this.Pb3.width + 1
top := this.Pb3.top
width := this.Pb3.width
text := "Dossier"
endwith
*
this.Pb5 = new PUSHBUTTON(this)
with (this.Pb5)
onClick := class::Creer
height := this.Pb4.height
left := this.Pb4.left + this.Pb4.width + 1
top := this.Pb4.top
width := this.Pb4.width
text := "Créer"
endwith
*
this.Pb6 = new PUSHBUTTON(this)
with (this.Pb6)
onClick := class::Quitte
height := this.Pb5.height
left := this.Pb5.left + this.Pb5.width + 1
top := this.Pb5.top
width := this.Pb5.width
text := "Quitter"
endwith
*
this.TEXT1 = new TEXT(this)
with (this.TEXT1)
top := this.Pb1.top + this.Pb1.height + 0.1
height := 1
left := this.Pb1.left
width := this.Pb1.width
text := "Fichier : "
alignVertical := 1
alignHorizontal := 2
endwith
*
this.ENTRYFIELD1 = new ENTRYFIELD(this)
with (this.ENTRYFIELD1)
top := this.TEXT1.top
height := 1
left := this.TEXT1.left + this.TEXT1.width + 1
width := form.width - this.ENTRYFIELD1.left
value := ""
endwith
*
this.TEXT2 = new TEXT(this)
with (this.TEXT2)
top := this.TEXT1.top + this.TEXT1.height + 0.1
height := 1
left := this.TEXT1.left
width := this.TEXT1.width
text := "Dossier : "
alignVertical := 1
alignHorizontal := 2
endwith
*
this.TEXT3 = new TEXT(this)
with (this.TEXT3)
top := this.TEXT2.top
height := 1
left := this.TEXT2.left + this.TEXT2.width + 1
width := form.width - this.TEXT3.left
text := this.Dossier
alignVertical := 1
endwith
*
* ------------------------------------------------------------------------------
FUNCTION Ajouter
* ------------------------------------------------------------------------------
*
LockWindowUpdate(form.hWnd) && Bloquer l'affichage pendant travail.
*
with (form.QUERY1.rowset)
beginAppend()
fields["ChampNom"].value := "C" ;
+ LTRIM(STR(form.QUERY1.rowset.rowCount() + 1, 3, 0))
fields["ChampType"].value := "Caractère"
fields["ChampLong"].value := 10
fields["ChampDec"].value := 0
save() && Pour afficher l'enregistrement.
endwith
*
class::Rafaichit()
*
RETURN null
*
* ------------------------------------------------------------------------------
FUNCTION Creer
* ------------------------------------------------------------------------------
*
IF form.QUERY1.rowset.rowCount() = 0
MSGBOX("Pas de champ créé, abandon", "Création table", 48)
form.GRID1.setFocus()
RETURN null
ENDIF
*
IF EMPTY(form.ENTRYFIELD1.value)
MSGBOX("Pas de nom de table créé", "Création table", 48)
form.GRID1.setFocus()
RETURN null
ENDIF
*
LockWindowUpdate(form.hWnd) && Bloquer l'affichage pendant travail.
*
LOCAL C, I
*
* Vérification si tout est conforme.
*
form.QUERY1.rowset.first()
DO WHILE .NOT. form.QUERY1.rowset.endOfSet
*
* Vérification du nom du champ.
*
IF EMPTY(form.QUERY1.rowset.fields["ChampNom"].value)
MSGBOX("Nom non saisi", "Création de la table", 48)
class::Rafaichit()
RETURN null
ENDIF
*
C = LTRIM(RTRIM(form.QUERY1.rowset.fields["ChampNom"].value))
form.QUERY1.rowset.fields["ChampNom"].value = C
C = UPPER(C)
FOR I = 1 TO LEN(C)
IF .NOT. SUBS(C, I, 1) $ "ABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789"
MSGBOX("Mauvais caractère nom : " + form.QUERY1.rowset.fields["ChampNom"].value ;
, "Création de la table", 48)
class::Rafaichit()
RETURN null
ENDIF
ENDFOR
*
* Vérification des noms interdits par CREATE TABLE.
*
SET EXACT ON
IF form.Tableau2.scan(C) <> 0
MSGBOX("Nom de champ interdit : " + form.QUERY1.rowset.fields["ChampNom"].value ;
, "Création de la table", 48)
SET EXACT OFF
class::Rafaichit()
RETURN null
ENDIF
SET EXACT OFF
*
form.QUERY1.rowset.next(1)
ENDDO
*
* Vérification si 2 noms de champ identiques.
*
form.QUERY1.rowset.first()
DO WHILE .NOT. form.QUERY1.rowset.endOfSet
I = form.QUERY1.rowset.rowNo()
C = RTRIM(UPPER(form.QUERY1.rowset.fields["ChampNom"].value))
form.QUERY1.rowset.next(1)
DO WHILE .NOT. form.QUERY1.rowset.endOfSet
IF C == RTRIM(UPPER(form.QUERY1.rowset.fields["ChampNom"].value))
MSGBOX("Deux noms identiques : " + form.QUERY1.rowset.fields["ChampNom"].value ;
, "Création de la table", 48)
class::Rafaichit()
RETURN null
ENDIF
form.QUERY1.rowset.next(1)
ENDDO
form.QUERY1.rowset.goto(I)
form.QUERY1.rowset.next(1)
ENDDO
*
form.QUERY1.rowset.save()
*
* Mise en forme du nom du fichier et vérification si table existe déjà.
*
form.ENTRYFIELD1.value = LTRIM(RTRIM(form.ENTRYFIELD1.value))
IF UPPER(RIGHT(form.ENTRYFIELD1.value,4)) <> ".DBF"
form.ENTRYFIELD1.value += ".dbf"
ENDIF
form.TableNom := form.Dossier + "\" + RTRIM(form.ENTRYFIELD1.value)
*
IF _app.databases[1].tableExists(form.TableNom)
IF MSGBOX("La Table : " + form.TableNom + " existe" + CHR(10) ;
+ "Voulez-vous l'écraser ?", "Création d'une table", 4 + 32) == 7
class::Rafaichit()
RETURN null
ENDIF
_app.databases[1].dropTable(form.TableNom)
ENDIF
*
* Création de la table.
*
I = 1 && Indication table créée.
*
C = 'CREATE TABLE "' + form.TableNom + '" ('
*
form.QUERY1.rowset.first()
DO WHILE .NOT. form.QUERY1.rowset.endOfSet
*
DO CASE
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "Caractère"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " CHAR("
C += LTRIM(STR(form.QUERY1.rowset.fields["ChampLong"].value, 3, 0)) + '),'
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "Numérique"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " NUMERIC("
C += LTRIM(STR(form.QUERY1.rowset.fields["ChampLong"].value, 3, 0)) + ','
C += LTRIM(STR(form.QUERY1.rowset.fields["ChampDec"].value, 3, 0)) + '),'
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "Date"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " DATE,"
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "Logique"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " BOOLEAN,"
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "Logique"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " BOOLEAN,"
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "Double"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " FLOAT,"
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "Mémo"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " BLOB(10,1),"
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "Binaire"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " BLOB(10,2),"
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "OLE"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " BLOB(10,4),"
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "Timestamp"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " TIMESTAMP,"
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "Integrer Long"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " INT,"
*
CASE RTRIM(form.QUERY1.rowset.fields["ChampType"].value) == "AutoIncrement"
C += RTRIM(form.QUERY1.rowset.fields["ChampNom"].value) + " AUTOINC,"
*
OTHERWISE
MSGBOX("Erreur de programmation type de champ : " ;
+ form.QUERY1.rowset.fields["ChampType"].value ;
, "Création de la table", 48)
class::Rafaichit()
RETURN null
*
ENDCASE
*
form.QUERY1.rowset.next(1)
ENDDO
*
C = SUBS(C, 1, LEN(C) - 1) && Enlever la dernière virgule
C += ')'
*
PRIVATE MACROdbf
MACROdbf = C
TRY
&MACROdbf
CATCH (Exception e)
MSGBOX("Nom de la table non conforme ou trop de champs", "Création de la table", 48)
I = 0
ENDTRY
RELEASE MACROdbf
form.Enregistre := I
*
* Sortie de la forme si appellé par ViewverDbfForm.
*
C = FINDINSTANCE("ViewverDbfForm")
IF .NOT. EMPTY(C)
LockWindowUpdate(0)
class::Quitte()
ENDIF
*
C := null
*
class::Rafaichit()
*
RETURN null
*
* ------------------------------------------------------------------------------
FUNCTION Detruit
* ------------------------------------------------------------------------------
*
TRY
form.QUERY1.rowset.delete()
CATCH (Exception e)
ENDTRY
*
form.QUERY1.rowset.save()
*
form.GRID1.setFocus()
*
RETURN null
*
* ------------------------------------------------------------------------------
FUNCTION DossierCherche
* ------------------------------------------------------------------------------
*
form.Dossier := GETDIRECTORY()
*
form.TEXT3.text := form.Dossier
*
form.GRID1.setFocus()
*
RETURN null
*
* ------------------------------------------------------------------------------
FUNCTION Field_OnChange
* ------------------------------------------------------------------------------
*
* this concerne form.QUERY1.rowset.fields[" x "] x = Nom du champ
*
class::QUERY1_Verifie(this.Forme)
*
this.Forme.GRID1.refresh()
*
RETURN null
*
* ------------------------------------------------------------------------------
FUNCTION Form_OnOpen
* ------------------------------------------------------------------------------
*
* Pour bloquerl'affichage pendant le travail.
*
IF TYPE("LockWindowUpdate") <> "FP"
EXTERN CLOGICAL LockWindowUpdate( CHANDLE ) User32
ENDIF
*
RETURN null
*
* ------------------------------------------------------------------------------
FUNCTION Importer
* ------------------------------------------------------------------------------
*
LOCAL vTable
*
vTable = null
vTable = GETFILE("*.dbf")
*
LockWindowUpdate(form.hWnd) && Bloquer l'affichage pendant travail.
*
IF .NOT. EMPTY(vTable)
LOCAL T, I, oTd
oTd = new TableDef()
oTd.tablename = vTable
oTd.load()
FOR I = 1 TO oTd.fields.size
*
T = oTd.fields[I].type
DO CASE
CASE T =="CHARACTER"
T = "Caractère"
CASE T == "NUMERIC"
T = "Numérique"
CASE T == "DATE"
T = "Date"
CASE T == "LOGICAL"
T = "Logique"
CASE T == "FLOAT"
T = "Numérique"
CASE T == "MEMO"
T = "Mémo"
CASE T == "BINARY"
T = "Binaire"
CASE T == "OLE"
T = "OLE"
CASE T == "DATETIME"
T = "Timestamp"
CASE T == "LONG"
T = "Integrer Long"
CASE T == "AUTOINC"
T = "AutoIncrement"
CASE T == "DOUBLE"
T = "Double"
ENDCASE
*
with (form.QUERY1.rowset)
beginAppend()
fields["ChampNom"].value := oTd.fields[I].fieldname
fields["ChampType"].value := T
fields["ChampLong"].value := oTd.fields[I].length
fields["ChampDec"].value := oTd.fields[I].decimalLength
endwith
ENDFOR
oTd := null
*
ENDIF
*
form.QUERY1.rowset.save() && Pour afficher le dernier enregistrement.
*
class::Rafaichit()
*
RETURN null
*
* ------------------------------------------------------------------------------
FUNCTION QUERY1_Verifie(Forme)
* ------------------------------------------------------------------------------
*
LOCAL ChampType, ChampLong, ChampDec
*
ChampType = RTRIM(Forme.QUERY1.rowset.fields["ChampType"].value)
ChampLong = Forme.QUERY1.rowset.fields["ChampLong"].value
ChampDec = Forme.QUERY1.rowset.fields["ChampDec"].value
*
IF ChampLong < 1
Forme.QUERY1.rowset.fields["ChampLong"].value := 1
ENDIF
*
IF ChampDec < 0
Forme.QUERY1.rowset.fields["ChampDec"].value := 0
ENDIF
*
DO CASE
*
CASE ChampType == "Caractère"
IF ChampLong > 254
Forme.QUERY1.rowset.fields["ChampLong"].value := 254
ENDIF
IF ChampDec <> 0
Forme.QUERY1.rowset.fields["ChampDec"].value := 0
ENDIF
*
CASE ChampType == "Numérique"
IF ChampLong > 20
Forme.QUERY1.rowset.fields["ChampLong"].value := 20
ENDIF
IF ChampLong < 3
Forme.QUERY1.rowset.fields["ChampDec"].value := 0
ELSEIF ChampDec > ChampLong - 2
Forme.QUERY1.rowset.fields["ChampDec"].value := ;
ChampLong - 2
ENDIF
*
CASE ChampType == "Date"
IF ChampLong <> 8
Forme.QUERY1.rowset.fields["ChampLong"].value := 8
ENDIF
IF ChampDec <> 0
Forme.QUERY1.rowset.fields["ChampDec"].value := 0
ENDIF
*
CASE ChampType == "Logique"
IF ChampLong <> 1
Forme.QUERY1.rowset.fields["ChampLong"].value := 1
ENDIF
IF ChampDec <> 0
Forme.QUERY1.rowset.fields["ChampDec"].value := 0
ENDIF
*
CASE ChampType == "Double"
IF ChampLong <> 8
Forme.QUERY1.rowset.fields["ChampLong"].value := 8
ENDIF
IF ChampDec <> 0
Forme.QUERY1.rowset.fields["ChampDec"].value := 0
ENDIF
*
CASE ChampType == "Mémo"
IF ChampLong <> 10
Forme.QUERY1.rowset.fields["ChampLong"].value := 10
ENDIF
IF ChampDec <> 1
Forme.QUERY1.rowset.fields["ChampDec"].value := 1
ENDIF
*
CASE ChampType == "Binaire"
IF ChampLong <> 10
Forme.QUERY1.rowset.fields["ChampLong"].value := 10
ENDIF
IF ChampDec <> 2
Forme.QUERY1.rowset.fields["ChampDec"].value := 2
ENDIF
*
CASE ChampType == "OLE"
IF ChampLong <> 10
Forme.QUERY1.rowset.fields["ChampLong"].value := 10
ENDIF
IF ChampDec <> 4
Forme.QUERY1.rowset.fields["ChampDec"].value := 4
ENDIF
*
CASE ChampType == "Timestamp"
IF ChampLong <> 8
Forme.QUERY1.rowset.fields["ChampLong"].value := 8
ENDIF
IF ChampDec <> 0
Forme.QUERY1.rowset.fields["ChampDec"].value := 0
ENDIF
*
CASE ChampType == "Integrer Long"
IF ChampLong <> 4
Forme.QUERY1.rowset.fields["ChampLong"].value := 4
ENDIF
IF ChampDec <> 0
Forme.QUERY1.rowset.fields["ChampDec"].value := 0
ENDIF
*
CASE ChampType == "AutoIncrement"
IF ChampLong <> 4
Forme.QUERY1.rowset.fields["ChampLong"].value := 4
ENDIF
IF ChampDec <> 0
Forme.QUERY1.rowset.fields["ChampDec"].value := 0
ENDIF
*
ENDCASE
*
RETURN null
*
* ------------------------------------------------------------------------------
FUNCTION Quitte
* ------------------------------------------------------------------------------
*
IF form.Enregistre == 0
IF MSGBOX("Abandonner la création en cours", "Table non créée", 4 + 32) == 7
form.GRID1.setFocus()
RETURN null
ENDIF
ENDIF
*
form.QUERY1.active := .F.
*
_app.databases[1].dropTable("0CreeTableInfo35854698.dbf")
*
form.close()
*
RETURN null
*
* ------------------------------------------------------------------------------
FUNCTION Rafaichit
* ------------------------------------------------------------------------------
*
form.GRID1.refresh()
form.GRID1.setFocus()
LockWindowUpdate(0)
*
RETURN null
*
endclass
