Alpha Video Training
Results 1 to 11 of 11

Thread: questions about converting to SQL

  1. #1
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,842

    Default questions about converting to SQL

    I have to convert an existing A5V9 web app from dBASE tables to MySQL or MS SQLServer2005.

    If anyone has experience with this, can you jot out a short list of todo's and potential problem areas.

    Also, what performance improvements will I see (if any) for searches, screen refresh, etc.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  2. #2
    "Certified" Alphaholic NoeticCC's Avatar
    Real Name
    Andrea Gill
    Join Date
    Nov 2007
    Location
    Golcar, West Yorkshire, UK
    Posts
    2,070

    Default Re: questions about converting to SQL

    I'd stick with mySQL, there are certain issues with row locks on SQL Server which I found difficult to get around, even in read only views, especially if the SQL server database is also used by desktop products (not necessarily Alpha5!).

    I have found conversion to mySQL of several mini-hybrid apps very easy and have found a SQL back end to be more efficient than DBF files, especially as it keeps storage of the data separate from the desktop and web files.

  3. #3
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,714

    Default Re: questions about converting to SQL

    Great topic for a white paper!

    Bill.

  4. #4
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,635

    Default Re: questions about converting to SQL

    Steve,

    I'm about to start on a similar project, so this might be a good place to exhange ideas/problems/solutions.

    So far, I've read three mySQL books and created one small app from scratch. My take on converting is about what you would expect. There is a big difference between just converting and converting well.

    In it's simplest form, you change all your auto increment fields to numerics, export the tables using the Alpha Export, convert your grids to use the new database and you are in business.

    However, if you want to do it well, it's a bit more involved.

    1. Decide how you will restucture your tables. The select capability gives more flexibility on how you structure your data. For example, one suggestion I heard is to split a table into the often changed and the seldom changed fields and make those two different tables. The reason is the cache is flushed for each change, so the seldom changed table can stay in the cache and not have to be reloaded.

    2. Determine the best data type for each field in the table. There are a number of different field types and each takes up a different amount of space. That decision can also change depending on the database engine you use. With mySQL, there are several to choose from, and these can be mixed in a single application.

    3. Decide what you want the database to do vs what Alpha will do. For example, the database includes triggers to perform functions and "stored procedures". So, for example, if you want an audit trail, you can set a trigger on the table and let the database take care of saving the record. The database can also format and concatinate character fields and perform math on numeric fields before they get to Alpha.

    4. Decide if you will use Alpha's built in security. If you do, I believe that the security tables will be dbf, even if everything else is SQL. That may or may not be a problem for you.

    What I miss the most with SQL is being able to open up a table in a browse and enter the fields directly.

    As far as performance comparisons, I have no experience, and it probably depends a lot on how you set up the 1~3 items above.

    Pat

  5. #5
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,714

    Default Re: questions about converting to SQL

    Pat,

    Are there mySQL book titles you would recomend over others?

    Bill.

  6. #6
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,635

    Default Re: questions about converting to SQL

    The three I read were (in order of smallest and simplest to largest and most detailed) were:

    MySQL, second edition, Visual Quickstart Guide. - Larry Ullman. This book is a "get your feet wet" book that introduces the basics, but doesn't cover nearly enough. It is a fast and easy read. Covers 4 and 5.

    MYSQL in a Nutshell, second edition - Russell J.T. Dyer. Calls itself "A desktop Quick Reference. - Organized like a reference manual - sectiions are organized in alphabetical order; good to look up a particular command, with options and examples. Not a good book to start with. When I know the command I want, I use it for syntax.

    MySQL Developer's Library, fourth edition - Paul Dubois - This is a great book. If I could only pick one, I'd buy this one, but it's so big, it's not good if you want to start next week. Takes a long time to read to figure out what you need.

    Then, there is an on-line manual, which is free, and which is organized by version (I'm using 5.1, the latest stable release). This is the definitive source for correct and up-to-date information...I just prefer to write in, dog-ear and highlight the books I use, and didn't want to print it all out.

    Pat

  7. #7
    Member
    Real Name
    David Hwang
    Join Date
    Nov 2005
    Location
    Fremont, CA
    Posts
    4

    Default Re: questions about converting to SQL

    Steve, so how do you convert dbf to mysql? I find there are some dbf2mysql softwares on the web, and I tried the one from Spectra Core, but it seems not quite working for my tables. I wonder how you do it?

    David

  8. #8
    "Certified" Alphaholic NoeticCC's Avatar
    Real Name
    Andrea Gill
    Join Date
    Nov 2007
    Location
    Golcar, West Yorkshire, UK
    Posts
    2,070

    Default Re: questions about converting to SQL

    Quote Originally Posted by hungry4grace View Post
    Steve, so how do you convert dbf to mysql? I find there are some dbf2mysql softwares on the web, and I tried the one from Spectra Core, but it seems not quite working for my tables. I wonder how you do it?

    David
    Just use Alpha5... read up about export operations

  9. #9
    Member
    Real Name
    David Hwang
    Join Date
    Nov 2005
    Location
    Fremont, CA
    Posts
    4

    Default Re: questions about converting to SQL

    Yes, I got it. I can export from file->export data->define connection string->mySQL ...,

    Thanks

  10. #10
    "Certified" Alphaholic NoeticCC's Avatar
    Real Name
    Andrea Gill
    Join Date
    Nov 2007
    Location
    Golcar, West Yorkshire, UK
    Posts
    2,070

    Default Re: questions about converting to SQL

    Quote Originally Posted by hungry4grace View Post
    Yes, I got it. I can export from file->export data->define connection string->mySQL ...,

    Thanks
    Glad - mySQL seems to most-tested and most reliable for this job, I have not done a huuuge deal of DBF work but have done a few smaller projects in DBF first and found it easy to export to mYSQL and adapt both desktop and web interfaces for mySQL.

  11. #11
    Member
    Real Name
    Ron Leunis
    Join Date
    Sep 2005
    Location
    Netherlands
    Posts
    652

    Default Re: questions about converting to SQL

    If you're considering Oracle DB ( f.i. because it is the market leader), then Oracle Express edition is also free for upto 4 GB databases. You only have to leave you email address at oracle.com to download it.

    Then this attached PDF article from Kurt Rayner (Alpha Software) may help in the equation ( on Oracle sequences).

    regards, Ron

Similar Threads

  1. Questions on SQL database features
    By mumfie in forum Application Server Version 9 - Web/Browser Applications
    Replies: 3
    Last Post: 12-31-2008, 05:56 PM
  2. M/S SQL 2000 and "Attach SQL Table"
    By Kevin Benett in forum Alpha Five Version 5
    Replies: 1
    Last Post: 12-11-2003, 05:39 AM
  3. ODBC Access - SQL 6.5 or SQL 2000
    By Dale Kromminga in forum Alpha Five Version 5
    Replies: 8
    Last Post: 10-20-2003, 01:42 PM
  4. Converting to A5 from A4
    By rbierman@iowbar.org in forum Alpha Five Version 4
    Replies: 4
    Last Post: 07-03-2002, 06:17 PM
  5. Converting V4 to V7
    By Paul Lewis in forum Alpha Four Versions 7 and 8
    Replies: 5
    Last Post: 03-05-2002, 05:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •