sam. 11 juin 2011, 10h16

GLS
France
atoutfox.public.association
Re: sqlexec et count etc
Bonjour
ci après un exemple (merci Francis) adapté par et pour moi avec PostGresSql ce sera quasiment la même chose avec sqlserver.
Cordialement
G.L.S
[vfp]
* Francis Faure
* 9/7/2006
* Dump Dbf->Sql
* but: générer un fichier texte pour importation dans PostGresSql
* a vérifier : les types et a tester...
* v1.1 du 10/07/2006 !: gestion des binary, date, numérique
* Ne gére pas les champs de type "GENERAL"
CLEAR
SET DATE FRENCH
SET CENTURY ON
CLEAR ALL
LOCAL ls_import_PostGresSql_vfp_data_file AS STRING, cPrimaryKey AS STRING , cTable AS STRING , iCount AS INTEGER, cCheminData AS STRING, cBase AS STRING
LOCAL t_table[2]
m.cCheminData="d\tablevfp\"
m.cBase="mabase"
ls_import_PostGresSql_vfp_data_file = m.cCheminData+"datatable_"+m.cBase+".sql"
IF FILE(ls_import_PostGresSql_vfp_data_file)
DELETE FILE (ls_import_PostGresSql_vfp_data_file)
ENDIF
t_table[1]="tabledbf1"
t_table[2]="tabledbf2"
OPEN DATABASE (m.cCheminData+m.cBase+'.dbc')
FOR m.iCount=1 TO ALEN(t_table)
m.cTable=LOWER(t_table[m.iCount])
IF !EMPTY(m.cTable) AND FILE(m.cCheminData+m.cTable+".dbf")
USE (m.cCheminData+m.cTable+".dbf")
m.cPrimaryKey=DBGETPROP(JUSTFNAME(m.cTable),"TABLE","PrimaryKey")
=a_insert(JUSTFNAME(m.cTable), m.cPrimaryKey,ls_import_PostGresSql_vfp_data_file,.T.)
USE
ENDIF
ENDFOR
CLOSE DATABASES ALL
* fichier a incoporer dans le gestionnaire de base de données
*MODIFY COMMAND (ls_import_PostGresSql_vfp_data_file)
RETURN
FUNCTION Transformer(t_champ, tl_en_binaire) AS STRING
LOCAL ls_return AS STRING
LOCAL li_i
IF ISNULL(t_champ)
ls_return = "NULL"
ELSE
DO CASE
CASE tl_en_binaire
* cas du binaire a transformer en hexadecimal
IF LEN(t_champ)>1
ls_return = "0x"
FOR li_i=1 TO LEN(t_champ)
ls_return = ls_return + RIGHT(TRANSFORM( ASC(SUBSTR(t_champ,li_i,1)), "@0"),2)
NEXT
ELSE
ls_return = "''"
ENDIF
CASE TYPE("t_champ")$"CMV"
ls_return = ALLTRIM(t_champ)
ls_return = STRTRAN(ls_return, CHR(92), CHR(92)+CHR(92)) && \ en \\
ls_return = STRTRAN(ls_return, CHR(39), CHR(39)+CHR(39)) && ' en ''
* ls_return = STRTRAN(ls_return, CHR(39), CHR(92)+CHR(39)) && ' en \'
ls_return = "'"+ls_return+"'"
CASE TYPE("t_champ")$"NYBFI"
ls_return = ALLTRIM(STR(t_champ))
CASE TYPE("t_champ")=="D"
ls_return = "'" + LEFT(TTOC(t_champ,3),10) + "'"
CASE TYPE("t_champ")=="T"
ls_return = "'" + TTOC(t_champ,3) + "'"
CASE TYPE("t_champ")=="L"
* boolean .T. = 1
IF t_champ
ls_return = "1"
ELSE
ls_return = "0"
ENDIF
CASE TYPE("t_champ")=="Q"
* cas particulier en binaire directement
IF LEN(t_champ)>1
ls_return = "0x" + TRANSFORM(t_champ)
ELSE
ls_return = "''"
ENDIF
OTHERWISE
ls_return = "'?'"
=MESSAGEBOX("a traiter Transformer type="+TYPE("t_champ"))
ENDCASE
ENDIF
RETURN ls_return
FUNCTION a_insert(p_table AS STRING, p_clepk AS STRING, p_file AS STRING, p_append AS logical )
LOCAL ARRAY la_Champs[1,18]
LOCAL li_nombre_de_champs AS INTEGER
LOCAL li_i AS INTEGER
LOCAL c_sqlinsert AS STRING
LOCAL ls_champ AS STRING
LOCAL iRecno AS INTEGER, iCount AS INTEGER
#DEFINE csCRLF CHR(13)+CHR(10) && peut être " " c'est pour la lisibilité
#DEFINE cs_delimiteur SPACE(0) && délimiteur champ de table
#DEFINE cs_table CHR(34) &&" comme délimiteur nom de table
IF EMPTY(ALIAS())
=MESSAGEBOX("Import basesql impossible : pas de table en cours d'utilisation dans la zone active",16, "Dump Dbf2Sql")
RETURN .F.
ENDIF
IF USED(p_table)
SELECT (p_table)
m.iRecno=RECCOUNT(p_table)
m.iCount=0
m.c_sqlinsert=""
SCAN
SELECT (p_table)
m.iCount=m.iCount+1
*IF !inactif
WAIT "INSERT "+ALLTRIM(STR(m.iCount))+" sur "+ALLTRIM(STR(m.iRecno)) WINDOWS NOWAIT
li_nombre_de_champs = AFIELDS(la_Champs)
m.c_sqlinsert = m.c_sqlinsert+ 'INSERT INTO "'+LOWER(p_table)+'" ('+ csCRLF
FOR li_i=1 TO li_nombre_de_champs
IF la_Champs[li_i, 2]="G" OR IIF(!EMPTY(p_clepk),LOWER(la_Champs[li_i,1])=p_clepk,.F.) OR LOWER(la_Champs[li_i,1])="dateheure" OR LOWER(la_Champs[li_i,1])="inactif" && ignorer champ/colonne dateheure et inactif
ELSE
ls_champ = LOWER(la_Champs[li_i, 1])
IF ls_champ='user'
* Traitement du cas spécifique user qui doit être coté en SQL
ls_champ='"'+ls_champ+'"'
ENDIF
m.c_sqlinsert = m.c_sqlinsert + SPACE(0)+ ls_champ + SPACE(0)
m.c_sqlinsert = m.c_sqlinsert + ","
ENDIF
ENDFOR
IF RIGHT(m.c_sqlinsert,1)=","
* Suppression du dernier caractère si c'est ","
m.c_sqlinsert=LEFT(m.c_sqlinsert,LEN(m.c_sqlinsert)-1)
ENDIF
m.c_sqlinsert = m.c_sqlinsert + ")" + csCRLF
m.c_sqlinsert = m.c_sqlinsert + " VALUES ("
FOR li_i=1 TO li_nombre_de_champs
IF la_Champs[li_i, 2]="G" OR IIF(!EMPTY(p_clepk),LOWER(la_Champs[li_i,1])=p_clepk,.F.) OR LOWER(la_Champs[li_i,1])="dateheure" OR LOWER(la_Champs[li_i,1])="inactif" && ignorer champ/colonne dateheure et inactif
ELSE
ls_champ = EVALUATE(la_Champs[li_i, 1])
IF la_Champs[li_i, 2]='D' OR la_Champs[li_i, 2]='T'
IF EMPTY(ls_champ)
ls_champ=.NULL.
ENDIF
ENDIF
m.c_sqlinsert = m.c_sqlinsert + Transformer(ls_champ, la_Champs[li_i, 6])
m.c_sqlinsert = m.c_sqlinsert + ","
ENDIF
ENDFOR
IF RIGHT(m.c_sqlinsert,1)=","
* Suppression du dernier caractère si c'est ","
m.c_sqlinsert=LEFT(m.c_sqlinsert,LEN(m.c_sqlinsert)-1)
ENDIF
m.c_sqlinsert = m.c_sqlinsert + ");"+ csCRLF
*ENDIF
ENDSCAN
m.c_sqlinsert = m.c_sqlinsert + csCRLF
ENDIF
SET SAFETY OFF
=STRTOFILE(m.c_sqlinsert, p_file, p_append)
SET SAFETY ON
ENDFUNC
[/vp]
Permalink : http://www.atoutfox.org/nntp.asp?ID=0000006100