November 7, 2003 at 7:19 am
My company is finally making the transition from FoxPro to SQL Server 2000. Those of us supporting the legacy databases are just now being familiarized with the new relational database design created by a separate team. The plan is to have virtually no direct access to the data via Tsql ( query analzyer etc ). In theory this is a good idea, but since I'm involved in daily data corrections/updates at the request of our 350 customers, I'm wondering how the programming team is going to be able to quickly develop tools for such updates.
I realize that updates that we do now, involving usually one large, poorly normalized table ( sometimes one or two other tables also ) will be much more difficult in the new database environment.
The challenge will be that customers don't want to wait a month for a new "tool" to be programmed and then the QA process, change control board, etc.
Is it unrealistic to think of using TSQL for direct data updates/insertions in a complex relational database environment?
Randy Petty
Edited by - rpetty on 11/07/2003 7:08:22 PM
November 7, 2003 at 10:21 pm
Test the queries first. Complicated tsql updates can easily destroy data. I've shot myself in the foot more than once with a complicated update join.
WEs
November 8, 2003 at 8:59 am
Seems to me it's just a question of knowing the business rules and testing first. If you know exactly which fields and tables need to be updated/inserted, and test first, should be workable without needing some custom GUI tool to be programmed for each such data correction.
Any data corrections that fall into a standard, repeating pattern could justify the creation of a tool though.
Randy
November 9, 2003 at 11:26 pm
Hi,
I have done some conversion from Clipper (xbase) to SQL using C# and VB.
My suggestions are as follows...
1. Do NOT use C# for large apps.
2. The problem of Legacy dbf data is HUGE and could consume half as much time as the programming does.
3. Try to bring in the new system without making too many changes to the database structures.
4. Write a foxpro crawler program that reads a foxpro log file and writes csv files. Then write a VB program that reads the csv and writes the SQL. That way you can bring in the new system on the legacy data.
Hope this helps...
November 10, 2003 at 5:38 am
I couldn't agree more with PhilBest (having also done Clipper upgrades to VB/SQL2K). One thing more that you should get familiar with are the datatype and size changes you'll be faced with. Get familiar with them before hand and get your books ready. I'd suggest Robert Vieita's WROX book 'SQL Server 2000 Programming'
November 10, 2003 at 6:33 pm
I have done several VFP and SQL Server conversions and found that I can access SQL Server tables using VFP and the ODBC drivers for SQL Server, including the running of SWL Server Stored Procedures (calling stored procedures works best due to the 255 character limit on pass-thru SQL via VFP).
If you want/need more details, contact me offline at arden@dmv.com
Arden
November 10, 2003 at 7:20 pm
quote:
My suggestions are as follows...1. Do NOT use C# for large apps.
....
PhilBest,
This is off topic, but would you please share your experience with C# when building a large app ?
Stanislav Petkov
Stanislav Petkov
November 12, 2003 at 9:53 pm
You are very lucky to have Foxpro at your disposal. Foxpro makes a much better front end to SQL than VB. You can use VFP to create a connection to SQL and execute any T-SQL statements your permissions allow you to. ( I have never encountered any 255 character limit in my work. I am not sure what limit that refers to, but I send much larger SPT procedures to SQL Server). You should find the TEXT/ENDTEXT structure especially useful in writing SPT. If you have access to QA, its great to copy your generated SQL code to QA so you can easily debug it. You may want to create a mapping of your current Foxpro data to new Foxpro tables which imitate the SQL tables. Use Foxpro routines to update the new Foxpro tables. Then the updates to SQL server will be very simple. With Foxpro, only your imagination will limit you.
I have created a Foxpro data class with methods to make routine SQL and Fox/SQL tasks easy. I have also created a Fox DLL which can be used by almost any programming language to access the data class method. I use it in Access and in TSQL. You are well equipped.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply