Alpha Video Training
Results 1 to 30 of 30

Thread: What to use? MySQL, SQLite or SQL Server

  1. #1
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default What to use? MySQL, SQLite or SQL Server

    I am designing an application and will use Zebra web hosting with Alpha Anywhere. It has been recommended that I use SQL format and I don't understand why I need SQL software when Alpha Anywhere has built in drivers. The software for MySQL and SQLite can be obtained free but the software for SQL Server can be expensive. SQLite seems to be used by a number of popular developers including Firefox. Firefox also has an add-on called SQLite Manager (What ever that does). So I'm hoping to get a better understanding of what to do.
    Thank you.

    Dick

  2. #2
    Member
    Real Name
    Allan Barnard
    Join Date
    Feb 2013
    Location
    Austin
    Posts
    212

    Default Re: What to use? MySQL, SQLite or SQL Server

    Dick,

    If you haven't been using a real database (RDBMS) then the choice can seem difficult. Insofar as I use a whack of databases regularly, here's a couple of thoughts:

    First, a recommendation:

    • Build two or three tables initially using the flat-file database format (DBF).
    • The DBF format is from a now-defunct desktop database named "D-Base III".
    • This format was also used by a number of other unrelated databases (e.g. FoxPro)... so it is common and there are a lot of supporting tools
    • Do this simply to get familiar with creating a "connection string" and referring to the tables from within your app (desktop or mobile)
    • But... you likely don't want to build too many DBF tables. Why? Because later, when you might want to use a proper DBMS there can be quite a number of errors in "upgrading" from DBF to any proper database.
    • Why might you ever upgrade to an RDBMS? Why not use DBF forever? For one thing, DBF files can't handle geographic data. And it's really easy and very powerful to include google maps in your desktop or mobile applications. You can use latitude and longitude as separate fields even in a DBF file, but you need a "geo" field in order to really useful stuff like radius calculations ("show me all the locations within this radius").
    • So... it's good to build a few DBF files because they are quick and easy and help get your head around your data fields.
    • But then you should take a breath and move to a proper database.
    • Recommendation: either "MySQL" or "MariaDB".
      • MySQL is open source... but it is still "owned" by Oracle (acquired when they bought Sun Microsystems).
      • MySQL is everywhere... including ZebraHost.
      • But... because Oracle is generally evil (my opinion) and more importantly hasn't been performing updates on MySQL as often as the community expects, many companies are moving to MariaDB (including Google for many applications behind the scenes).
      • MariaDB was formed by the same guy who started/invented MySQL but it is back to the open source roots MySQL was initially founded on.
      • The "LAMP" stack (linux, apache, mysql/mariaDB, Perl/PHP" is probably the most common web-app development suite in the marketplace... and it's all free!



    Other options include:
    • Microsoft SQL Server: well supported, great if a company/client is all-Microsoft-all-the-time... but can be expensive as it is commercial grade and requires a license
    • Microsoft SQL Server Express: similar to "SQL Server" but thinned out a bit. Operates largely the same as true SQL Server so it's a good development platform if you eventually plan to go to SQL Server for a client or for yourself. And... it's also free.
    • SQLlite: been around for a while and exists as a separate/standalone DB for many mobile platforms... and can be built-in to your app.
    • Oracle: good for large commercial, highly scalable, and ridiculously expensive environments.
    • and... that's just the short list!



    Them's be my quick thoughts. Grain of salt added.

  3. #3
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    Thanks for the reply but I don't understand why I need SQL software when Alpha Anywhere has built in drivers. Can this question be answered simply. Thanks for your recommendations.

  4. #4
    Member
    Real Name
    Roger Noguera
    Join Date
    Jul 2010
    Location
    Sant Cugat del Vallès
    Posts
    236

    Default Re: What to use? MySQL, SQLite or SQL Server

    Alpha has the drivers to CONECT to SQL databases. You still need the database motor.

  5. #5
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    Roger,
    What does the database motor do; how do I use it; what are the Alpha functions for? Zebra Host will set up SQL when I set up the service. So what am I missing?
    Dick

  6. #6
    Member
    Real Name
    Roger Noguera
    Join Date
    Jul 2010
    Location
    Sant Cugat del Vallès
    Posts
    236

    Default Re: What to use? MySQL, SQLite or SQL Server

    The motor stores and retrieves the actual data. Alpha5 takes care of the user interface.

  7. #7
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    So in other words I need SQL installed on my computer to do a working preview, etc.

  8. #8
    Member
    Real Name
    Roger Noguera
    Join Date
    Jul 2010
    Location
    Sant Cugat del Vallès
    Posts
    236

    Default Re: What to use? MySQL, SQLite or SQL Server

    Not necesarly in your computer.
    Any network accessible computer will do.

  9. #9
    Member
    Real Name
    Allan Barnard
    Join Date
    Feb 2013
    Location
    Austin
    Posts
    212

    Default Re: What to use? MySQL, SQLite or SQL Server

    Dick,

    Your application will require three things to operate:

    • [1] Your application (AlphaFive, C#, PERL... whatever)
    • [2] A "database engine" (the thing manages tables, stores info, and retrieves info... the real worker)
    • [3] A "database driver" to provide communications between your APPLICATION and your DATABASE.



    You can write your application in virtually any language (from Excel to "C" to AlphaFive).

    You can choose to use almost any database (individual-DBF-files, MySQL, SQLserver, Oracle, etc).

    But... your APP will only be able to speak in the language of your desired database if you have a "database driver". Think of the driver as a Star Trek Language Translator. It's small, doesn't do any of the work, but does allow you to communicate with the database that does in fact do the heavy lifting.

    Why does this matter? Because your app (or many apps at least) may need to work with a DBF file one minute, a SQL Server database the next, and then an Oracle database after that. By simply redirecting your app to point at different databases you can access different data.

    But... each time you do, you need to pick the appropriate communication method (driver).

    The other term you will need to learn is "connection string". A connection string contains all the information your app needs to find a database, choose a driver, and start doing work. The connection string notes which driver is to be used, your app goes and finds it, and then your app/driver/database all work together.

    Alpha Five will build and test your connection strings for you (AlphaDAO connection strings). It's an option off of one of your "tools" menus.


    I think that before you try to make sense of applications, drivers, connection-strings, and databases, you need to spend 30 minutes doing a bit of initial self-education via YouTube.

    Here's a beginning:


  10. #10
    Member
    Real Name
    Allan Barnard
    Join Date
    Feb 2013
    Location
    Austin
    Posts
    212

    Default Re: What to use? MySQL, SQLite or SQL Server

    I should note also that you need to be careful in your use of the term "SQL".

    The term means:

    Structured Query Language


    This is the language used by you and your application to get data or save data in a database.

    Not all SQL languages are the same. There are slight variations depending on which database you choose to use. Think of it like the variations between British, Canadian, Northern U.S. and Southern U.S. forms of English. It's basically the same language but there can be small and annoying variations.

    So a generic "SQL database" is simply one that responds to a form of SQL. This includes Oracle, MySQL, MariaDB, and SQL Server.

    Finally, a LOT of people mistakenly use the simple word "SQL" when what they mean is "Microsoft SQL Server Database".

    So you might want to use these terms for clarity in your posts and in your conversation:


    SQL language

    a SQL-compliant database

    Microsoft SQL Server


    Finally, the thing you need to do is:

    1. Install a SQL-compiant database on your laptop (e.g., MySQL)
    2. Tell AlphaFive how to find and talk to the database by creating a "connection string"
    3. Create a table in the database (which Alpha will "see" via the connection string)
    4. and finally... do something with the tabel in a GRID or in a UX-component "LIST"

  11. #11
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    Quote Originally Posted by rnoguera View Post
    Not necesarly in your computer.
    Any network accessible computer will do.
    But if I'm developing on a desktop not on a network then I need SQL installed on my computer to do a working preview, etc.

  12. #12
    Member
    Real Name
    Allan Barnard
    Join Date
    Feb 2013
    Location
    Austin
    Posts
    212

    Default Re: What to use? MySQL, SQLite or SQL Server

    Yes, then you need to install a SQL-compliant database on your local computer.

    And then build a table in that database (or build a DBF in AlphaFive and use the "upsize genie" to move/convert it to your actual database). Or use one of thousand "table creation tutorial" scripts on the net to create a dummy table.

    Then you need to build a connection string in AlphaFive to connect to it (even though it is on your own computer).

    Seems like a pain... but this is the way all databases work.

  13. #13
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    Allan,

    Quoting you:

    So you might want to use these terms for clarity in your posts and in your conversation:


    SQL language

    a SQL-compliant database

    Microsoft SQL Server


    Finally, the thing you need to do is:

    1. Install a SQL-compiant database on your laptop (e.g., MySQL)
    2. Tell AlphaFive how to find and talk to the database by creating a "connection string"
    3. Create a table in the database (which Alpha will "see" via the connection string)
    4. and finally... do something with the tabel in a GRID or in a UX-component "LIST"

    Thank you for the above information

    Dick

  14. #14
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    When I move my application to a web hosting service do I have to find the tables in the SQL-compliant database and move them to the web hosting service? Then make new "connection strings" to the web hosting service SQL-compliant database?

  15. #15
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,644

    Default Re: What to use? MySQL, SQLite or SQL Server

    Quote Originally Posted by Dick Anderson View Post
    When I move my application to a web hosting service do I have to find the tables in the SQL-compliant database and move them to the web hosting service? Then make new "connection strings" to the web hosting service SQL-compliant database?
    You don't have to find them, but you do have to copy them to the new location. Navicat is great for this (about $200). But there are other ways to do this.

    In Alpha you define an alias for your connection string and you have two (or possibly more) definitions for any given alias - one for your local machine and the other for the server.

  16. #16
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    You would think that all the tables would be contained in the Alpha application and all you would need is a connection to the SQL-compliant database.

  17. #17
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,644

    Default Re: What to use? MySQL, SQLite or SQL Server

    Quote Originally Posted by Dick Anderson View Post
    You would think that all the tables would be contained in the Alpha application and all you would need is a connection to the SQL-compliant database.
    Yes, you would think so, but once you understand SQL you will see why this isn't so. This isn't the fault of Alpha or an Alpha shortcoming, it's the way ALL SQL applications work regardless of platform or UI.

    That said, if you are uncomfortable with the idea of diving into the (vast) world of SQL, you can use dbf which works quite well. OTOH, most people who start using SQL rarely want to go back to dbf - at least those who live, more or less, in the web/mobile universe.

  18. #18
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    Peter,
    Well, I have been convinced that there are too many advantages to using SQL both for the application and the user. Downloaded MySQL and got lost. Tried using MySQL help but something seems to be wrong. Maybe you or someone else could help me get started.
    Dick

  19. #19
    Volunteer Moderator Steve Workings's Avatar
    Real Name
    Steve Workings
    Join Date
    Apr 2000
    Location
    The Dreaded Chair
    Posts
    5,619

    Default Re: What to use? MySQL, SQLite or SQL Server

    Dick - are you a subscriber? Alpha has a tutorial series "Xbasic for the Web" for subscribers (by Andy Meer and myself) that I think gives a pretty good MySQL primer for A5 users.
    -Steve


  20. #20
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    Steve - what is a subscriber? - Dick

  21. #21
    Member
    Real Name
    Allan Barnard
    Join Date
    Feb 2013
    Location
    Austin
    Posts
    212

    Default Re: What to use? MySQL, SQLite or SQL Server

    You can purchase a license for Alpha Five... and separately you can purchase a "subscription" to Alpha five that includes all updates as they come along, access to all video and other resources, etc.

    If you don't live in a commercial software world, these may seem like heavy additional fees. However, if you do work with commercial software, these fees are trivial and necessary to get the requisite help.

    There are LOTS of tools to help you build applications faster. But no matter how you slice it, all of them have fees once you pull the covers back. And... this is as it should be. If you ultimately build a cool app in raw code or via a tool such as Alpha, you will likely want to be compensated.

  22. #22
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    I downloaded MySQL free version. Can I create a data table in MySQL and then add and configure fields in Alpha Anywhere after I establish a connection string?

  23. #23
    Member
    Real Name
    Allan Barnard
    Join Date
    Feb 2013
    Location
    Austin
    Posts
    212

    Default Re: What to use? MySQL, SQLite or SQL Server

    Dick,

    It might help your efforts if you understand why the "database" (or database-engine) is separate from the "application" (or user-interface).

    If you have a few local files (like DBF data files) you could build an app that would work for you and quite a number of users in a very controlled environment. Each time you took any action, your application (e.g. Alpha Five code) would go to the files on disk and read or write data. You could back things up just by copying the files to another disk once per day.

    But... if you truly have a successful app, one serving a company with many user or one that consumers like and you suddenly have 100 or 100,000 users, the file method will experience limitations.

    A dedicated and standalone database will have an entire series of utilities that work to keep indexes repaired, manage table growth (so it doesn't explode all over your hard drives), perform backups, and even grown to include multiple sub-databases, computers, and/or hard drives.

    In the end, they both may do the same exact thing, but a dedicated and standalone database does everything better, faster, and with more management tools.


    • So if you have MySQL, you can access it from a command line (linux or windows). But most people at the very least use a free tool called "phpMyAdmin" to provide a graphic interface.
    • if you have SQL Server, you can access it from code within an application (Alpha Five) or from a front-end tool (like SQL Server Management Studio).
    • Oracle includes "SQL Developer" and also "SQLplus" for interacting with the database. And there are a TON of third party tools for providing a nice interface for working with the database (e.g. TOAD, Benthic Golden, etc).



    Although you can use Alpha Five or any other coding environment to communicate with a database, you will ultimately have to learn the basics of databases. This can be rendered down to:

    • How/where to install it
    • How to check to see if it's working (simple commands that ask "are you alive?")
    • How to log in (user and password)
    • Permissions (every user typically requires permissions... security is important!)
    • CREATE a TABLE (start with a simple table... there are examples everywhere on the net)
    • ADD DATA (the term is "INSERT"... again, SQL INSERT examples abound)
    • UPDATE DATA (the term is in fact "update")
    • COMMIT and ROLLBACK (when you save a change, do you keep it or discard it)
    • DELETE data
    • And of course... HOW TO LOCATE/COMMUNICATE with it (each database is on a computer with an I.P. number, the database has a name, a "driver" is required for each database, and finally there is "user" and "password")


    Sadly there is no "easy button" (like the Staples commercial). You have to learn this stuff.

    And... however complicated it seems, it's not really that bad. Further, it is more complicated if you try to build a full app and have to write everything yourself (interface, file read/write instead of a database, etc). This is why there are databases and application development tools (e.g. Alpha).

    So. You might want to go back and start with my first suggestion:

    • build 2 or 3 simple tables with DBF files
    • no connection string required in this case... Alpha will put them in a local directory
    • build a simple GRID or UX-and-LIST in Alpha Five to talk to them


    And then, when you've got the easy stuff down, do one of two things... or both:

    • install a sql-compliant, standalone database and RECREATE your tables there (for awareness and practice)
    • or, install noted database and use the Alpha "upsize genie" so that Alpha does the work of recreating your tables



    Then you have to add a connection string, which in turn points that the "driver" noted in one of your original posts.

    I'll stop babbling. Back to work myself.

    Good luck. Stick with it.

    Remember... all of this work and complexity is what elevates us above bacteria and politicians!

  24. #24
    Member
    Real Name
    Allan Barnard
    Join Date
    Feb 2013
    Location
    Austin
    Posts
    212

    Default Re: What to use? MySQL, SQLite or SQL Server

    There are a TON of MySQL tutorials on the net and on YouTube.

    The WC3schools stuff is clean and not cluttered with ads. You have to be careful of some of the constant advertising on other sites.


    The link below has some "MySQL... now what?" kinds of links.



    I work with all kinds of databases. But like everyone... I had to hunt this stuff down and get familiar with MySQL at one point also.

    Live. Learn. Build stuff. And then you die. It's the circle of life.

  25. #25
    Member
    Real Name
    Richard Beacham
    Join Date
    Jun 2011
    Location
    Central CA
    Posts
    26

    Default Re: What to use? MySQL, SQLite or SQL Server

    Allan - You just wrote the best synopsis of the evolution of A5, and its' relationship to 'databases', I have ever read. [I read a lot.] Thank you.

  26. #26
    Member
    Real Name
    Allan Barnard
    Join Date
    Feb 2013
    Location
    Austin
    Posts
    212

    Default Re: What to use? MySQL, SQLite or SQL Server

    Richard,

    Glad it helps. My experience with A5 has been bumpy (as it seems to be with any new environment) and it takes a while to sort out the idiosyncrasies. I can imagine it would be harder for someone who doesn't have a hard-core database background because you then must learn both database and Alpha topics simultaneously.

    I figure I will know enough to be really effective right about the time I get too old to remember any of it!

  27. #27
    Member
    Real Name
    Dick Anderson
    Join Date
    May 2006
    Location
    Milwaukee, WI
    Posts
    100

    Default Re: What to use? MySQL, SQLite or SQL Server

    Allan,

    All my experience has been with Alpha4 and Foxpro back in the late 80's. I never did upgrade to Alpha5 because unlike Alpha4 it involved too much programing and there wasn't much information about how Alpha5 worked at the time. I really appreciate all the time you and others spent answering this post.

    Thank you,
    Dick

  28. #28
    Member
    Real Name
    Linda Cobb
    Join Date
    Oct 2011
    Posts
    8

    Default Re: What to use? MySQL, SQLite or SQL Server

    Thank you all for your comments. since this was written some time ago, I would very much like to know the consensus regarding best SQL choices for alpha anywhere, MySQL, SQL Server Express, of which I have some knowledge, or MariaDB, of which I have none. we would have our network support do the installation on the server, but their support would only be software installation. we have tested the conversion of data in both MySQL and SQL Express. Backup and restore options are a concern, as are reporting services. we've been told a batch file would need to be written to backup, our database in dbf format compresses to 20 mb, and the SQL test database less than 10MB, so we feel we do not need SQL Server. we would appreciate your thoughts. thank you.

  29. #29
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,644

    Default Re: What to use? MySQL, SQLite or SQL Server

    Linda,

    MySQL & MariaDB are, for all practical purposes, 100% identical. That being true, I suggest going forward to use MariaDB. Whatever you know in MySQL will work exactly the same in MariaDB.

    Not sure why you were told that you need a batch file to backup. I recommend a good db management tool like Navicat:

    http://www.navicat.com/

    Navicat allows you to run scheduled backups. You can also find a free backup tool here:

    http://mysqlbackupftp.com/

  30. #30
    Member
    Real Name
    Linda Cobb
    Join Date
    Oct 2011
    Posts
    8

    Default Re: What to use? MySQL, SQLite or SQL Server

    many thanks for the prompt reply. appreciate your time.

Similar Threads

  1. MySQL or SQL Server
    By DataTrain in forum Application Server Version 11 - Web/Browser Applications
    Replies: 7
    Last Post: 06-10-2013, 09:42 AM
  2. Painful DbfTransition to SQL SERVER or MYSQL
    By JetLi in forum Application Server Version 11 - Web/Browser Applications
    Replies: 7
    Last Post: 07-06-2012, 10:57 PM
  3. Mysql or Sql Server to Native Alpha DBF
    By JetLi in forum Alpha Five Version 11 - Desktop Applications
    Replies: 12
    Last Post: 03-21-2012, 06:39 PM
  4. MySQL or MS SQL Server ?
    By Jedt3D in forum Application Server Version 10 - Web/Browser Applications
    Replies: 3
    Last Post: 08-28-2010, 05:47 AM

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
  •