FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour Choosing SQL database ...
Adolfo
Posts: 883
Joined: Tue Oct 11, 2005 11:57 AM
Re: Choosing SQL database ...
Posted: Tue Oct 27, 2009 09:53 PM

Reinaldo...

Did you get an error message, Does it connect,is there a MSGALERT()... please send me the error.log...

If not.. it should be an UPX problem, cause I compress the exe with it...

Gonna upload it tomorrow...

.. and as I told you.. I USE INCREMENTAL SEARCH WITH ADO AND MYSQL... See my first post referencing it.

Adolfo

;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Asus TUF F15, 32GB Ram, 2 * 1 TB NVME M.2, GTX 1650
James Bott
Posts: 4840
Joined: Fri Nov 18, 2005 04:52 PM
Re: Choosing SQL database ...
Posted: Tue Oct 27, 2009 09:56 PM

Adolfo,

I took a look at your TADOBase class. It seems you are using a recordset so that you can also use methods like skip(). Granted this will work and can be used to do incremental searching.

The issue the Enrico was making is that you have to download the entire recordset in order to do incremental searching. Except for very small recordsets (less than a browseful) this will be slower than using a DBF. When browsing a DBF you only read as many records as it takes to fill the browse. When using a recordset you have to read every record in the table before you can start the browse (or at least all the records that you plan to be able to browse through).

Let's look at an example. Say you have a database containing 10,000 records and a browse showing 10 records. With SQL and recordsets you will have to read all 10,000 records across the network before you can display the first 10 records in the browse. With DBFs you only have to read 10 records across the network, then you can display the browse.

Reinaldo was making the point that with ADS you can use SQL when it is the better solution and still use DBF syntax when it is the better solution--both with the same database and in the same application.

Regards,
James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
James Bott
Posts: 4840
Joined: Fri Nov 18, 2005 04:52 PM
Re: Choosing SQL database ...
Posted: Tue Oct 27, 2009 11:05 PM

Adolfo,

Perhaps I was hasty in my assumption that you were reading the whole database into the recordset. I did see in the New method that the default was to read the entire database.

I have looked at your TADOBase class some more and it appears that you are using the LIMIT and OFFSET clauses of the SELECT statement to create smaller recordsets. Is that true? Are these recordsets the size of the browse, so that when you page-up and page-down (in the browse) you get a new recordset? Perhaps you could explain this in more detail.

I guess for an incremental search you could do a SELECT... with a LIMIT the size of the browse and thus create a new recordset for each keystroke of the search.

For a regular browse, for each up and down arrow (causing a movement to a record outside the browse) you would have to create a new recordset. Is this what you are doing?

I did work similar to this some time ago when I discovered the LIMIT and OFFSET clauses. Unfortunately not all SQL languages support these clauses.

Regards,
James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
reinaldocrespo
Posts: 990
Joined: Thu Nov 17, 2005 05:49 PM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 12:20 AM
Adolfo; I tested the app. Thank you for sending it and for trying to share. I'm not sure if I should answer. I think you are missing the point. Which is ok. I often do myself. But, I’m afraid there might be some readers being mislead about what SQL is good and not so good for. I'll leave it at that.

Now, on the subject of posting some sample SQL. Here is a trigger that I'm currently using with ADS (local and remote) imbedded into a table. It executes “On-Update” of any record:
Code (fw): Select all Collapse
    declare @is_email LOGICAL;
    declare @email CHAR(100);

    declare @is_hosp_email LOGICAL;
    declare @hosp_email CHAR(100);
    
    declare @old cursor as select * from __old; 
    declare @new cursor as select * from __new;

    open @old; 
    fetch @old;
    open @new; 
    fetch @new;
         
    if @new.signed = TRUE AND @old.signed = FALSE then
 
        @is_email = ( SELECT is_email from doctors where doctors.id = @new.refer_id ); 
        @email = ( SELECT email from doctors where doctors.id = @new.refer_id );

        if @is_email = TRUE AND @email IS NOT NULL then
         
            INSERT INTO plmail ( pathno, doc_id, send_to ) 
                   VALUES ( @new.pathno, @new.refer_id,
                          ( SELECT email FROM doctors 
                            WHERE doctors.id = @new.refer_id 
                            AND doctors.is_email = TRUE 
                            AND doctors.email IS NOT NULL) ); 
        
            INSERT INTO plmail ( pathno, doc_id, send_to ) 
                   SELECT c.pathno, c.copyto, doc.email FROM copyto c 
                      LEFT JOIN doctors doc ON doc.id = c.copyto
                      WHERE c.pathno = @new.pathno 
                      AND doc.is_email = TRUE 
                      AND doc.email IS NOT NULL ;
  
         endif ;

        if @new.facility IS NOT NULL then 
            @is_hosp_email = ( SELECT TOP 1 is_email from hospital where hospital.name = @new.facility ); 
            @hosp_email = ( SELECT TOP 1 e_mail from hospital where hospital.name = @new.facility );

            if @is_hosp_email = TRUE AND @hosp_email IS NOT NULL then  
                INSERT INTO plmail ( pathno, doc_id, send_to )
                    SELECT @new.pathno, h.passwd, h.e_mail from hospital h
                        WHERE h.name = @new.facility ;
            endif;
        endif;
    endif;

    if @new.signed = FALSE AND @old.signed = TRUE then
         DELETE FROM plmail 
                WHERE pathno = @new.pathno 
                AND DateTime_Sent IS NULL ;

         DELETE FROM pthreps 
                WHERE pathno = @new.pathno;

     endif ;


It inserts a record into a table that acts as a queue any time a pathology report is signed. And removes the record from the queue if the pathology is "un-signed" (there is a grace period during which a Doctor may "un-signed" a pathology report). And now the ISAM-xharbour translation. I'll to avoid using a tdbf class (which would make it even simpler):
Code (fw): Select all Collapse
if isSigned .and. !(cArea)->isSigned
    (doc)->( dbseek( cid ) )

    if (doc)->( found() ) .and. ( doc)->isEmail and. !empty( ( doc )->eMail )
        ( plMail )->( dbappend() )
        ( plMail )->( dbRecLock() )
        ( plMail )->PathNo := cPathNo
        ( plMail )->doc_id := cId
        ( plMail )->Send_to := (doc)->email 
        ( PlMail )->( dbUnlock() )
    endif

endif

if !isSigned .and. (cArea)->isSigned

   ( plMail )->( dbseek( cPathNo ) )

    if ( PlMail )->( found() )
        ( PlMail )->( dbDelete() )
    endif

    (pthreps)->( dbseek( cPathNo ) )

    if (pthreps)->( found() )
        ( pthreps)->( dbdelete() )
     endif

endif


I'm probably forgetting to translate a few lines. But you can see what I mean. With ADS, triggers can only be written using SQL. So I had no choice here. And again, triggers are valid using the "local" (free) version also.

-Adolfo; one last thing before I go. Please send me the source code to your sample app. Thank you: reinaldo punto crespo en gmail
Take care,



Reinaldo.
anserkk
Posts: 1335
Joined: Fri Jun 13, 2008 11:04 AM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 04:36 AM

Hi all,

Adolfo's ADO Incremental sample is working fine here for me.

Regards
Anser

Rimantas
Posts: 437
Joined: Fri Oct 07, 2005 12:56 PM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 06:16 AM
Adolfo wrote:
My class is also available.. see on the spanish forums.
Adolfo


Adolfo , can you direct to this your class ?

Regards !
Rimantas U.
Adolfo
Posts: 883
Joined: Tue Oct 11, 2005 11:57 AM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 11:23 AM
James Bott wrote:...With DBFs you only have to read 10 records across the network, then you can display the browse.


James.. are you absolutely sure about this.... ?
Remember that DBF's are read completely from disk, so if you have a browse , you will have to read ALL the 10.000's records together with ALL its fields, and also all the supporting DBF's related with the main one.
With SQL .. only the fields you want to show, then you can LOAD whatever you want... so NET traffic is reduced dramatically....

In the sample I do...
select rut,nombre,field1,field2,field3 from clientes
and "clientes" TABLE has 35 FIELDS, when I dobleclick the record, I do a new RECORDSET containing all the methods of the Complete recordset, but only the present record (with all its fields) to edit, append, delete... etc etc


Regarding TADOBASE...
I have implemented Paging with that, no matter if the DB has it or not. I read ::nPageSize records each time, and if you see the Methods, I can control when each "miniRecordset" ends to load another...

You can downloaded from here
http://200.72.140.34/privado/adobase/

AS I STATED...
I CAN do incremetal searching with ADO and SQL, the TEST shows it.

Adolfo
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Asus TUF F15, 32GB Ram, 2 * 1 TB NVME M.2, GTX 1650
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 11:34 AM
Adolfo wrote:Remember that DBF's are read completely from disk, so if you have a browse , you will have to read ALL the 10.000's records together with ALL its fields, and also all the supporting DBF's related with the main one.


Absolutely not! Sorry, but it looks like you don't know of what you're speaking of.

EMG
Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
Adolfo
Posts: 883
Joined: Tue Oct 11, 2005 11:57 AM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 12:00 PM
Enrico Maria Giordano wrote:
Adolfo wrote:Remember that DBF's are read completely from disk, so if you have a browse , you will have to read ALL the 10.000's records together with ALL its fields, and also all the supporting DBF's related with the main one.


Absolutely not! Sorry, but it looks like you don't know of what you're speaking of.

EMG


Enrico..

SO when you use a browse to look at a DBF, YOU DONT READ THE WHOLE DBF ?
Explain it to me please... maybe I miss something

From Chile
Adolfo
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Asus TUF F15, 32GB Ram, 2 * 1 TB NVME M.2, GTX 1650
Marcelo Via Giglio
Posts: 1088
Joined: Fri Oct 07, 2005 03:33 PM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 12:24 PM

Hello,

I am following this topic with many interest.

Adolfo you can create a DBF or ADT (ads) table (file) with 1.000.000 records, then open and put it in a browse, the time for it is the same if you are opening a dbf with 100 records, then you can go to the last record or to the middle of the table and this don't take any time. Then you can ask for the table size (record) and you obtain this inmediatly, I don't expert in ADO like you but this is not easy with it, I think.

But yes I like SQL very much, it is really ease and flexible for this razon I use ADS (we have the both worlds), BUT YES AGAIN, this is really expensive, I hope in the future the progess of LETODB go in this direction, another think is to build a TCP layer to SQLite (like Real SQL Server)

saludos

Marcelo

Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 12:35 PM
Adolfo wrote:SO when you use a browse to look at a DBF, YOU DONT READ THE WHOLE DBF ?


Exactly. If you want to access one record you have to read only that record.

EMG
Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
James Bott
Posts: 4840
Joined: Fri Nov 18, 2005 04:52 PM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 01:08 PM
>>...With DBFs you only have to read 10 records across the network, then you can display the browse.

James.. are you absolutely sure about this.... ?
Remember that DBF's are read completely from disk, so if you have a browse , you will have to read ALL the 10.000's records together with ALL its fields, and also all the supporting DBF's related with the main one.
With SQL .. only the fields you want to show, then you can LOAD whatever you want... so NET traffic is reduced dramatically....


Yes, I am positive. With DBFs you only ever have one record in memory at a time. With a browse you read each record then display it and continue until all the visible records are displayed.

The only time you would ever read all records is if you were doing some kind of processing of all records (like finding a total, maybe), but still only one record is in memory at any time.

With SQL you work with recordsets which as you know are basically arrays. They may contain one or more records. If you do a SELECT *... then you ARE going to read all the records into memory (into the recordset) at one time. If you then browse the recordset you are displaying records from the recordset instead of reading them from disk like when you using a DBF. The browse display will be very slightly faster with a recordset but probably not detectable by the user.

Note that using scopes you could create an array from a DBF and use it just like a recordset. Using scopes you are only reading the records that are within the scope. So, this would probably be very similar in performance to SQL.

SQL will have an advantage when you are selecting records that you do not have an index for, thus you cannot use scopes. In this case the server reads all the records an only sends those within the parameters of the SELECT statement. When using a DBF you would have to read all the records across the network (but you would still only have one record in memory at a time).

In the sample I do...
select rut,nombre,field1,field2,field3 from clientes
and "clientes" TABLE has 35 FIELDS, when I dobleclick the record, I do a new RECORDSET containing all the methods of the Complete recordset, but only the present record (with all its fields) to edit, append, delete... etc etc


SQL does have the advantage of reading only select fields. You cannot do this with DBFs.

Regarding TADOBASE...
I have implemented Paging with that, no matter if the DB has it or not. I read ::nPageSize records each time, and if you see the Methods, I can control when each "miniRecordset" ends to load another...


I still do not understand exactly how you are doing this. Do you load a new miniRecordset with each keystroke of an incremental search (which contains just enough records to fill the browse)?

AS I STATED...
I CAN do incremetal searching with ADO and SQL, the TEST shows it.


I think is agreed that it can be done, but the question was, can it be done without reading all the records in the table into a recordset?
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
Adolfo
Posts: 883
Joined: Tue Oct 11, 2005 11:57 AM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 01:30 PM

Marcelo..

Thanks for answering...

Personally I think ADS is a good product, but very expensive to buy and to maintain, dedicated servers, the imposibility to hire a host with it, so little base knowledge in comparison with MYSQL, SQLITE, MS SQL, ORACLE, POSTGRES etc etc.and a performance not superior to any SQL engine, cause I don't think you can compare it to MYSQL SPEED or SQL SERVER.

Now if you see the test I uploaded, or the class (TADOBASE ) referenced in the spanish forum, you can have a little range of all ADO festures combined to the power of SQL.

PD: You are invited to try it, comment and suggest, Im going to have a copy with latest changes available to the ones who want to use it.

JAMES

"Yes, I am positive. With DBFs you only ever have one record in memory at a time. With a browse you read each record then display it and continue until all the visible records are displayed."

SO you have to READ EVERY record ( to build a browse ), then reading the whole DBF with all its fields.. or Am i wrong. Now the size of traffic in that operation is bigger than having only the seleted fields send by the SQL server ina forma of a RECORDSET.

In mi case... for incremental searching, I already have the recordset in memory, so I do the search in it. When you want o look at the whole RECORD, I call a function that retrieves the record with all its fields ( is a RECORDSET with only one record in it )
About Paging... If you see the class, I use as many recordsets as needed to navigate thru the recorset.

   ::nTotalPages:=Round(::nCount/::nPageSize,0)

and I keep track of what page I'm on...
See, the source menu.prg, function PAGES_CLIENTES(3) option 3

Hope I clarified this.

From Chile
Adolfo

;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Asus TUF F15, 32GB Ram, 2 * 1 TB NVME M.2, GTX 1650
James Bott
Posts: 4840
Joined: Fri Nov 18, 2005 04:52 PM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 01:41 PM
Adolfo,

>"Yes, I am positive. With DBFs you only ever have one record in memory at a time. With a browse you read each record then display it and continue until all the visible records are displayed."

SO you have to READ EVERY record ( to build a browse ), then reading the whole DBF with all its fields.. or Am i wrong. Now the size of traffic in that operation is bigger than having only the seleted fields send by the SQL server ina forma of a RECORDSET.


No, for a browse displaying 10 records, you only read those ten VISIBLE records, one at a time. Read and display the first record, then read and display the second one, etc. You stop after reading the 10th record.

In mi case... for incremental searching, I already have the recordset in memory


Which recordset, the whole table?

James
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
Adolfo
Posts: 883
Joined: Tue Oct 11, 2005 11:57 AM
Re: Choosing SQL database ...
Posted: Wed Oct 28, 2009 01:47 PM
James Bott wrote:Adolfo,

>"Yes, I am positive. With DBFs you only ever have one record in memory at a time. With a browse you read each record then display it and continue until all the visible records are displayed."

SO you have to READ EVERY record ( to build a browse ), then reading the whole DBF with all its fields.. or Am i wrong. Now the size of traffic in that operation is bigger than having only the seleted fields send by the SQL server ina forma of a RECORDSET.


No, for a browse displaying 10 records, you only read those ten VISIBLE records, one at a time. Read and display the first record, then read and display the second one, etc. You stop after reading the 10th record.

In mi case... for incremental searching, I already have the recordset in memory


Which recordset, the whole table?

James


No James.. not the whole table.. just the recordset ( select id, name, address from clientes) not ( select * from clientes ) because I have tables with more than 50 fields, most of them codes or ids to other tables. So in mi case I try to show the records on which I can do the search, and of course those who are present in any of the keys of the table.

Xbrowse does that...? reads only the records that fits in the browse and then reads the others ?.. didm't know that.. sorry

Adolfo
;-) Ji,ji,ji... buena la cosa... "all you need is code"

http://www.xdata.cl - Desarrollo Inteligente
----------
Asus TUF F15, 32GB Ram, 2 * 1 TB NVME M.2, GTX 1650
Static archive · New replies & topics via GitHub Discussions