SQL - advice

[Jeff Barnes]

Hi All,

After many many years of using DBF files I would like to start using SQL.

Since I know nothing about SQL I am looking for some starting advice.

I would like to use something that can all be installed via my installer (I use Inno Setup).
The less the end user has to do the better.

What would you recommend as an SQL database?
I've seen that SQLite is a single file database, has anyone some comments on this one (advantages/limitations)?

Any "getting started with SQL" advice is welcomed.

[reinaldocrespo]

Hi, Jeff;

I can't think it can be any easier than with ADS as you don't have to fully jump into SQL until you are ready. With ADS you can continue to work ISAM + SQL, you can also continue to work DBF/CDX + newer table types with extended field types, you can continue to place your tables on a shared directory or hide your tables in a non-shared directory on the server and only your compiled-ADS-enabled app will be able to access data.

Yes, there is a learning curve, especially when you want to take advantage of everything SQL has to offer, but that learning curve is not as steep with ADS. There is a cost but I find it is the least expensive SQL on the market that also has support. I pass on the cost of the SQL to my customers. I suppose that is the only drawback against mysql or sqlite. However, ADS is free for up to 5 users by using the ALS (Advantage Local Server) which only works with shares just like traditional dbfs do, thus db operations are not as fast as a real client-server.

Here is a roadmap to get started:

Phase #1:
1. "Request" ADS rdd on your main .prg
2. Make ADSRDD the default RDD
3. Re-index your tables with ADSCDX or ADSNTX in case you are still using ntxs.
4. Use only ADSCDX to work with your tables.

Phase #2:
1. Create an ADS Datadictionay and bind all your tables to the DD.
2. Have the user log into the DD as the first thing when your program starts
3. start replacing your ISAM business logic for SQL.
4. replace your .dbfs for .adts and start taking advantage of extended field types

If what you are looking for is a free SQL, I would think MySQL is the best choice and Daniel's TDolphin class seems very well done.


Hope that helps,



Reinaldo

[nageswaragunupudi]

You can use MS Access on any Windows PC without any setup at all. Even MS Access need not be installed. This is also single file Database.

With proper care, you can make code that is portable to Microsoft MSSQL. Even to use MSSQL, free version of SQLEXPRESS can be used. But this requires set up of SQLEXPRESS. Larger clients can use priced MSSQL.

The main point is from MSAccess to SQLEXPRESS to MSSQL, it is possible to make portable code with proper care.

FWH ado functions offer great facilities for writing portable code. The only reason why FWH is offering ado functions is to help programmers to write portable code.

This way a programmer can prepare a software package that works from no-installation MsAccess to very high-end MSSQL.

[reinaldocrespo]

Mr. Rao;

This sounds very interesting. I have debated with myself the pros and cons of moving from ADS to MS-SQL. I think the major CON is the price for bigger customers -or bigger db. If I'm not mistaken the MSSQL express version (the one that is free) works for up to 30gigs of data and after that you'd have to install the paid-for SQL. If this is so, I'm pretty sure the price you pay for MSSQL is 10x the price of ADS. So if you ever envision bigger customers or smaller ones that also store blob data as part of your market, then MSSQL is much more expensive.

I think of this because I store a lot of blob data (images, documents, ids, xml, audit-log of all transactions) which make the db grow very fast.

On the other hand, bigger customers (bigger data) probably wouldn't mind paying for MSSQL and you wouldn't have to work too hard to sell it as they already accept the cost of M$. You probably wouldn't even sell the SQL, you'd just tell them they have to purchase it with the server.

What are your thoughts?


Reinaldo.

[Adolfo]

I would give a try to the absolutely FREE MARIADB.

Is 1000% Mysql compatible, no charge $$ no matter if you use it in commercial app, faster than Mysql, all FW, HB classes are compatible with MariaDb.

You can use it in a Windows, Linux, web server (cloud), locally as in a embeded server with only one file.

From the original creator of Mysql.

Have a look ... https://mariadb.org/

[Rick Lipkin]

Jeff

There is no good one answer .. Reinaldo has a good suggestion, Rao and I are on the same page as other possibilities are Using ADO and your Sql table could be MS Access ( .mdb ), MS Sql Server ( free edition ), Oracle, etc.

I like Ms Access and Sql server because everything you need to connect ( client ) to the database is already included in every windows OS starting with XP thru 8.1 and probably 10 ( untested ).

Have a look at the \samples and compile AdoRick.prg. This is a beginners guide to creating an Access database from scratch and how to use ADO to manage your data entry.

As an added bonus .. ADO is a standard MS Class that once coded, you can easily port the same code to any other Ansi SQL database ( Oracle, MS Sql Server ) and the only change is the connection string.

Rick Lipkin

[reinaldocrespo]

Adolfo;

Is there a getting started with MariaDB from (x)harbour guide or link anywhere? Like Rick said, there is plenty of help to get started with ADO + MSSQL. How about the solution you are suggesting? How can anyone on this forum learn how to get started?

Thank you,


Reinaldo.

[hmpaquito]

How can anyone on this forum learn how to get started?

Perhaps, using at begining TDolphin from Daniel García

[Adolfo]

Reinaldo..

All the info available on the web about Mysql 5.x.x can help, since MAriadb is a fork of Mysql, but much more powerful. The syntax, commands ect etc are the same.
Most of the great web service providers are changing to MariaDb since there is no payment required for licenses, and remenber Mysql now is owned by ORACLE. A lot of bug fixes, new characteristics and improvements are there waiting for being done on Mysql... why?

You can use tDolphin, tMysql, Ado to connect to MariaDb.

I change 3 big clients to MariaDb with NO PROBLEM AT ALL, no changes in my code, just delete the current install of Mysql and put MAriaDb instead.

Hope it helps.

[fraxzi]

Just my thoughts,

I've been using ADS for years... the power of DBF to SQL in one package.. A very short learning curve.
I am also opt to a cheaper free SQL like the open source postgre, mysql.. etc.

The best and the greatest that FW classes supports both world (commercial or free). Kudos!