||Re: complex indexes vs index key fields
Mervyn Bick <firstname.lastname@example.org>
||Fri, 31 Jul 2020 14:07:46 +0200
On 2020-07-30 07:02, Gaetano De Luisi wrote:
> Hi Mervyn,
> This code loads the CSV data directly into the table using ADO it seems. Thanks a ton for spending the time on this, I really appreciate it. I will give it a try, I have never used ADO before.
You will actually need to learn very little about ADO as such. You will
be using ADOdatabase and ADOquery objects instead of the standard
database and query objects. You will, however, need to learn learn some
The MySQL documentation covers everything you need but it doesn't get
you started in SQL. There are, however, plenty of SQL tutorials on the
Internet. I found https://www.w3schools.com/sql/default.asp useful as
it highlights the fact, and gives examples, that different SQL servers
use different syntax for some operations.
The tutorials do, unfortunately, only cover the basics. The syntax is a
bit different to doing the same things in dBASE but it shouldn't give
you any problems. Once you get a bit deeper into SQL you should be able
to get any help you need in the sql-server newsgroup.
Your ADOquery objects will be read-only i.e with the requestLive
property set false. These will be used to fetch data for display.
Any writing or deletion of data will be done by using the ADOdatabase
object's executeSQL() method to execute INSERT, UPDATE or DELETE
statements. You will get plenty of string building practice crafting
these statements. :-)
All tables MUST have a primary key otherwise you won't be able to UPDATE
or DELETE specific records. This is often an autoinc type field but it
can, in fact, consist of multiple fields. For instance, the primary key
for your energydata could be a combination of siteId and eTimestamp.
Something to bear in mind is that there are no soft deletes in SQL. In
.dbf files records are not physically removed from the table when they
are deleted. They can actually be brought back using the RECALL command
as long as this is done before the table is PACKed. In SQL gone is
gone. It helps to have regular backups "just in case". I don't use
MySQL but Akshat will be able to help you with this.
Accessing the newsgroups using Web-News via the link on the dBASE
website is all very well but it means you have to check regularly for
any replies. If you are going to be spending any time here in the
newsgroups you should consider setting up a NNTP feed. A search for
NNTP in this, the getting-started, newsgroup will give you a message
with enough to get you started.