MariaDb delete

[mauri.menabue]

Hi all
when you execute the delete command is also done skip ?

Code: Select all  Expand view  RUN

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


 

[nageswaragunupudi]

Code: Select all  Expand view  RUN

oRs:gotop()
do while .not. oRs:eof()
    oRs:delete()  <----- the pointer on next record ?
enddo
 

Yes.

[mauri.menabue]

Thank you Master !
Maurizio Menabue

[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:

Code: Select all  Expand view  RUN

DELETE FROM <tablename> ;
 

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 view  RUN

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 view  RUN

    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.

[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

Code: Select all  Expand view  RUN

DELETE FROM <table> [ WHERE <cond>]

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.

[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.

[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.