Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Upsizing Wizard - SQL Server
Message
From
10/02/2007 18:20:21
Dragan Nedeljkovich (Online)
Now officially retired
Zrenjanin, Serbia
 
 
To
10/02/2007 15:55:40
Hilmar Zonneveld
Independent Consultant
Cochabamba, Bolivia
General information
Forum:
Visual FoxPro
Category:
Client/server
Miscellaneous
Thread ID:
01194464
Message ID:
01194479
Views:
17
>Do later versions of VFP include the capability to upsize VFP tables to MySQL, or some other databases? I remember that VFP 6 includes support for Oracle and SQL Server only.
>
>If not, what other tools can be used?

Command window?

At home()+"Tools\xsource\VFPSource\Wizards\wzupsize" there's the complete source of the upsizing wizard. You can just copy the whole thing into a new project, rework the syntax for MySQL and use that.

Frankly, I've done it one table at a time - by having a template prg for one table, and then just using Frank Dietrich's intellisense script to insert field lists. That's for inserting the data. For creating MySql tables from DBFs, this:
Local lc_Alias, lc_Prefix, lc_List, ln_i, ln_WasSel
Local ll_LongName, lc_LongName
Local N
#Define cr Chr(13)
#Define lf Chr(10)
#Define crlf cr+lf
#Define hTab Chr(9)
#Define tz ";"
#Define nv '"'
#Define lnv [`]
#Define apo "'"


lc_Alias = Inputbox("Select alias to get fields from?", "Create MyQ table", Alias())

lc_List   = ""
ln_WasSel = Select()

N= Afields(aF, lc_Alias)
llGotPk=.f.
For i = 1 To N
	lc_List = lc_List +  hTab + lnv+Lower(aF[i,1])+lnv+hTab
	llGotPK=llGotPk or Lower(aF[i,1])=="pk"
	lcType=aF[i,2]
	lnLen=aF[i,3]
	lnDec=aF[i,4]
	llNull=aF[i,5]
	llBinary=aF[i,6]

	lcLen="("+Transform(lnLen)
	lcDec=")"
	lcOptions=" "
	Do Case
	Case lcType="V"
		lcQType=Iif(llBinary, "varbinary","Varchar")
	Case lcType="C"
		lcQType=Iif(llBinary, "CharBinary","Char")
	Case lcType$"NFBY" And lnDec>0
		lcQType="Decimal"
		lcDec=Textmerge(", <<lnDec>>)"
	Case lcType$"NFBY" And lnLen<3
		lcQType="Tinyint"
		lcLen="(2"
		lcOptions = lcOptions + " unsigned"
	Case lcType$"NFBY" And lnLen<10
		lcQType="int"
	CASE lcType="I"
		lcQtype="int"
		lcLen="(10"
	Case lcType$"NIFBY"
		lcQType="longint"
	Case lcType="L"
		lcQType="bit"
		lcLen="(1"
	Case lcType="D"
		lcQType="Date"
		Store "" To lcLen, lcDec
	Case lcType="T"
		lcQType="Datetime"
		Store "" To lcLen, lcDec
	Case lcType="M"
		lcQType=Iif(llBinary, "Text", "MediumBlob")
		Store "" To lcLen, lcDec
	Case lcType="WG"
		lcQType="MediumBlob"
		Store "" To lcLen, lcDec
	Endcase
	lcOptions = lcOptions + Iif(llNull, " Null", " Not Null")
	lc_List = lc_List +lcqtype+ lcLen+lcDec+lcOptions+","+crlf
Endfor &&* ln_i = 1 to fcount()

IF llGotPk
*-- there'll be a hanging comma if there are no other indices
	lc_List = lc_List + Textmerge(hTab+"	primary key (`pk`),")+crlf
ENDIF

N=Ataginfo(aTg)
For i=1 To N
	IF NOT LOWER(atg[i,3])=="pk"
	lc_List = lc_List + Textmerge(hTab+"	key `<<atg[i,1]>>` (`<<atg[i,3]>>`)")+;
		IIF(i=n, "", ","+crlf)
	ENDIF
Endfor

lcTable=Forceext(Proper(Juststem(CursorGetProp("Database",Alias()))), Proper(Alias()))


*-- TEXT BLOCK BEGIN
TEXT TO _cliptext NOSHOW TEXTMERGE
drop table if exists <<lcTable>>;
create table <<lcTable>> (
<<lc_list>>	
) 	ENGINE=InnoDB DEFAULT CHARSET=`UTF8`	;
ENDTEXT
*-- TEXT BLOCK END

Select (ln_WasSel)
It saves the resulting script into the clipboard. I'd just it paste into MySqlQueryBrowser and run it there while I was testing. Later, I began building longer scripts. But then, if you do a backup in MySql, the backup is a script again, so once you get your database built, just back it up and a properly formatted script is inside the backup.

[update] now I see I relied on my own convention for primary key fields. You may need to adjust that part.

back to same old

the first online autobiography, unfinished by design
What, me reckless? I'm full of recks!
Balkans, eh? Count them.
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform