Page 1 of 1
MariaDb delete
Posted: Thu Jun 01, 2023 8:40 pm
by mauri.menabue
Hi all
when you execute the delete command is also done skip ?
Code: Select all | Expand
oRs:gotop()
do while .not. oRs:eof()
oRs:delete() <----- the pointer on next record ?
enddo
or old way with DBF
oRs:gotop()
do while .not. oRs:eof()
oRs:delete()
oRs:skip() <----- wrong !
enddo
Re: MariaDb delete
Posted: Fri Jun 02, 2023 4:07 am
by nageswaragunupudi
Code: Select all | Expand
oRs:gotop()
do while .not. oRs:eof()
oRs:delete() <----- the pointer on next record ?
enddo
Yes.
Re: MariaDb delete
Posted: Fri Jun 02, 2023 11:34 am
by mauri.menabue
Thank you Master !
Maurizio Menabue
Re: MariaDb delete
Posted: Fri Jun 02, 2023 2:55 pm
by reinaldocrespo
I wonder...
if MariaDB is like any other SQL, then deleting records or executing a While command to traverse a table for any reason would be loosing a lot of benefits that SQL offers.
When you execute a while loop to check each record for a condition, that information travels from the server to the workstation where the condition is evaluated. If instead you allow the server to execute the condition, you save LOTS of time and network traffic, to say the least. That's why you notice it is a lot faster to re-index on the server than on a workstation and I'm probably not saying anything new to anyone.
On the example in this thread, instead of traversing the complete table checking for eof(), you would do this:
Suppose you want to extract certain records from the table based on certain condition. With our traditional ISAM you would do this:
Code: Select all | Expand
WHILE !odbf:eof() //or some other condition
if <condition> //another condition
tbl2:Seek( odbf:KeyValue )
if Tbl2:<condition> ...
...
endif
....
endif
odbf:skip()
END
If you leverage the benefits of SQL you would let the server execute that without the need for each record to travel across the wires:
Code: Select all | Expand
SELECT * FROM <tablename> WHERE <condition>
Or
SELECT * FROM <tbl1> t1
LEFT JOIN <tb2> t2 ON t2.keyField = t1.keyfield
WHERE <condtion>
I say this because I notice people using Maria and MySQL but continue to use ISAM while loosing a great opportunity to start learning SQL.
If Mariadb is an SQL engine, then perhaps we can start asking how to implement some while loop that traverses a table for the equivalent SQL statement.
Re: MariaDb delete
Posted: Fri Jun 02, 2023 4:04 pm
by nageswaragunupudi
His question was about the behavior of our library.
Whether after oRs:Delete() do we need to oRs:Skip() or not.
That point is clarified.
The point of using sql statement
was discussed in his other post.
I say this because I notice people using Maria and MySQL but continue to use ISAM while loosing a great opportunity to start learning SQL.
If you are referring to MySql ISAM vs InnoDB, all our users are using InnoDB but not ISAM.
Re: MariaDb delete
Posted: Fri Jun 02, 2023 7:00 pm
by reinaldocrespo
I had not seen that other post. I have now and you are right my point becomes out of context.
The good thing about that thread is that we can see how to slowly start moving to SQL until we become experts. That was my experience.
I know many people ask --why? and the question is valid when your app manages smaller databases with very few workstations on the network and security is not an issue. When data becomes massive or you have more than 10 users connecting to your database at the same time, SQL becomes invaluable. It is hard to appreciate until you understand everything SQL brings to you.
...and since we are on the subject; I wonder:
1. With ADS I'm able to test my SQL statements using DataArchitect (ARC32). I get to see errors as well as view the actual result that a given statement would yield. Is there a tool to test your SQLs with Maria DB similar to Advantage ARC?
2. Is the fwh ADO class good as well for MSSQL or is this only for MariaDB?
Thank you.
Re: MariaDb delete
Posted: Sat Jun 03, 2023 2:13 am
by nageswaragunupudi
1. With ADS I'm able to test my SQL statements using DataArchitect (ARC32). I get to see errors as well as view the actual result that a given statement would yield. Is there a tool to test your SQLs with Maria DB similar to Advantage ARC?
MySql WorkBench for mysql/mariadb
HeidiSql for mysql,mariadb,mssql,sqlite,PostGre
Microsoft Studio for MSSQL
Oracle sql developer, dbForge studio for Oracle
and many many others
Is the fwh ADO class good as well for MSSQL or is this only for MariaDB?
ALL
eg.,Dbase,FoxPro,MSAccess,Microsoft SQL,SQLite,Oracle,PostGre (natively) and any other RDBMS with connectionstrings.