locked
VFP 9.0 and SQL RRS feed

  • Question

  • Hi,

     

    I am trying to migrate from VFP 9.0 free tables to SQL2005. i would like to get to know on how to migrate my data to SQL2005 and how to manipulate it (add,delete,update...) by using wizards and programatically. If anyone can point me to some articles or the like that could assist me in this, it would be highly appreciated.

    thanks 

    Saturday, March 29, 2008 8:53 AM

Answers

All replies

  • Hi,

    You need to download and install the mysql-connector-odbc-3.51.12-win32 on the station first. Then in some event code have the following.....

    MySQLstr="DRIVER={MySQL ODBC 3.51 Driver};SERVER=xxx.xxx.xxx.xxx;"+;
    "PORT=3306;DATABASE=somedatabase;USER="+alltrim(thisform.logidTxt.Value)+";"+;
    "PASSWORD="+alltrim(thisform.pwdTxt.Value)+";OPTION=3;"


    MySQLhandle=sqlstringconnect(MySQLstr)


    if MySQLhandle>0 then

    code....

    else

    error 

    endif

    Saturday, March 29, 2008 9:43 AM
  • Well, there are several ways, easy, fast and/or by using wizardsSmile

     

    For a one time transferring you might directly use SSMS, create a linked server using VFPOLEDB and execute queries like:

     

    select * into SQLTable1

      from OpenQuery(MYVFPLinkedServer, 'select * from ("c:\MyFolder\\myFreeTable")')

     

     

    This would get with data and create a structure for you. You can edit the structure in SSMS. You could execute this from within VFP as well. During conversion empty dates would be a problem and/or you might want to change types on the fly, then cast() come to the rescue:

     

    select * into SQLTable1

      from OpenQuery(MYVFPLinkedServer,

    'select cast( evl(myDate, .null.) as datetime) as myDate, 

     cast(myFirstName as varchar(20)) as myFirstName,

     cast(myGeneralThatIShouldHaveNeverUsed as Blob) as myGeneralThatIShouldHaveNeverUsed

     from ("c:\MyFolder\\myFreeTable")')

     

    etc.

    If you don't want to or can't create a linked server, then you might roll your own series of SQLExec() with "create table ...", "insert ...". Sounds too much work but I found it to be easier than clicking N times in wizard (of course I didn't type them all but instead wrote code generators).

     

    You could use upsizing wizard in VFP (I don't know the details because I don't use it). If it doesn't support free tables, then easy to solve, copy your tables to a work folder, createa dbc and using a loop with adir(), add all to that dbc and use the wizard for upsizing.

     

    Yet another option is to keep them as DBF files and use Advantage Database Server for ODBC C/S access. Check that out. Local server ODBC driver is free but you have to pay if you need to use as C/S.

     

    Saturday, March 29, 2008 11:04 AM
  • Make sure you install "Books Online", which is the SQL Server documentation. It is an optional install with SQL Server Client Tools.

     

    In addition to the VFP docs, here are some places to get started. Some of these articles are a bit dated and talk about some technologies that are no longer used (RDS for example), but still contain some good information.

    http://msdn.microsoft.com/archive/en-us/dnarfoxgen/html/msdn_bko01.asp
    http://msdn.microsoft.com/archive/en-us/dnarfoxgen/html/msdn_fpsqlcs.asp
    http://msdn.microsoft.com/archive/en-us/dnarfoxgen/html/msdn_bko02.asp
    http://msdn.microsoft.com/archive/en-us/dnarfoxgen/html/msdn_usevfp.asp
    http://msdn.microsoft.com/library/en-us/dnfoxgen/html/adojump.asp
    http://msdn.microsoft.com/library/en-us/dnsql2k/html/sql_embeddingmsde.asp
    http://www.spacefold.com/colin/archive/articles/sql_backend/backend.htm


    There are also some sample chapters you can download from http://www.hentzenwerke.com/catalog/csvfp.htm

     

    Saturday, March 29, 2008 4:55 PM