I'm looking for some advice/suggestions.

Board index FiveWin for Harbour/xHarbour I'm looking for some advice/suggestions.

Posts: 55

Joined: Thu Feb 15, 2007 01:35 AM

I'm looking for some advice/suggestions.
Posted: Thu Jan 14, 2010 08:31 PM

I'd like to upgrade my application from a DBF file structure to an SQL oriented file structure this year, but I'd like to complicate things by keeping it as close to cross platform compatible as possible. My current applications runs on Harbour 1.1 (soon to be upgraded to 2.0) with a January 09 version of Fivewin (also soon to be updated). I really like the ADO classes a lot but what SQL database driver might work on the Mac or Linux platforms?

reinaldocrespo

Posts: 990

Joined: Thu Nov 17, 2005 05:49 PM

Re: I'm looking for some advice/suggestions.
Posted: Thu Jan 14, 2010 08:39 PM

Hi.

ADS runs on Novell, Linux, and Windows servers. You won't have to change much of your current code and will be able to use SQL statements as needed.

For example:

Code (fw): Select all Collapse

odbf:Seek( ckey )
do while odbf:keyfield == cKey .and. !odbf:eof()
   aadd( aresultSet, { odbf:field1, odbf:field2 } )
   odbf:skip()
enddo

Would work with ADS, as well as:

Code (fw): Select all Collapse

cSql := "SELECT field1, field2 from table where keyfield = '" + cKey + "'"
aResultSet := ADSexecuteSQlDirect( cSql )

Both will work the same with ADS.

Reinaldo.

dutch

Posts: 1598

Joined: Fri Oct 07, 2005 05:56 PM

Re: I'm looking for some advice/suggestions.
Posted: Thu Jan 14, 2010 11:10 PM

Dear Reinaldo,

How do the program speed between both codes, in case of Big and Small Database?

Regards,
Dutch

Regards,

Dutch

FWH 2304 / xHarbour Simplex 1.2.3 / BCC73 / Pelles C / UEStudio

FWPPC 10.02 / Harbour for PPC (FTDN)

ADS V.9 / MySql / MariaDB

R&R 12 Infinity / Crystal Report XI R2

(Thailand)

reinaldocrespo

Posts: 990

Joined: Thu Nov 17, 2005 05:49 PM

Re: I'm looking for some advice/suggestions.
Posted: Fri Jan 15, 2010 12:29 AM

Dutch;

Hi.

The size of the queried table does not affect response time (as long as you have an index on the queried field). I would think that it is the size of the result set that would affect how fast you get the response. It has been my experience that even with results sets of a few thousand, response time seems immediate. But if you need to work with a result set of more than a few thousands, then I suppose it would be better to set an optimized filter (AOF -Advantage optimized filter) based on an index (CDX or ADI work better than NTXs).

Here is a link that explains how to use an AOF:

http://devzone.advantagedatabase.com/dz ... ilters.htm

Reinaldo.

fraxzi

Posts: 811

Joined: Tue May 06, 2008 04:28 AM

Re: I'm looking for some advice/suggestions.
Posted: Fri Jan 15, 2010 12:51 AM

...

Would work with ADS, as well as:

Code (fw): Select all Collapse

cSql := "SELECT field1, field2 from table where keyfield = '" + cKey + "'"
aResultSet := ADSexecuteSQlDirect( cSql )

...

Hello Reinaldo,

Code (fw): Select all Collapse

aResultSet := ADSexecuteSQlDirect( cSql )

on which version of ADS this returns an array of recordset?

Im using 8.1 and xHabour 1.21, thi function ADSexecuteSQlDirect( ) returns .T./.F.

Regards,
Frances

Kind Regards,

Frances

Fivewin for xHarbour v18.07

xHarbour v1.2.3.x

BCC 7.3 + PellesC8 ( Resource Compiler only)

ADS 10.1 / MariaDB

Crystal Reports 8.5/9.23 DE

xMate v1.15

reinaldocrespo

Posts: 990

Joined: Thu Nov 17, 2005 05:49 PM

Re: I'm looking for some advice/suggestions.
Posted: Fri Jan 15, 2010 03:04 PM

Fraxi;

Yes you are right. I'm simplifying. I'm sorry.

I have a function based on ADSExecuteSQLDirect() that returns an array instead of a cursor with the values.

Code (fw): Select all Collapse

*-------------------------------------------------------------------------------------------------------------------------------
function ExecuteSQLScript( cScript, lShowProgress )
local cArea
local aStruc, i
local nCount := 1
local a := {}
local xTmp
local isGood := .f.

DEFAULT lShowProgress := .t. 

    if !empty( cScript )
        AdsCacheOpenCursors( 0 )
        DBSELECTAREA(0)

        IF !ADSCreateSQLStatement("SQLarea", ADS_CDX ) //.or. !ADSVerifySQL( cScript )
TRY
            SQLArea -> ( DBCLOSEAREA() )
END
            MsgStop( "AdsCreateSqlStatement() failed with error "+ cValToChar( ADSGetLastError() ) )
            logfile( "SQLError.log", { cScript } )
        Else
            if lShowProgress
                MsgRun( "Running Script...", "Please Wait", { | oDlg | isGood := AdsExecuteSQLDirect( cScript, oDlg ) } )
            else
                isgood := ADSExecuteSQLDirect( cScript )
            endif
            
            if isgood
            CursorWait()
TRY
                cArea := "SQLarea"
                aStruc  := (cArea)->( dbStruct() )
                a       := array( (cArea)->( lastrec() ) )

                while !(cArea)->( eof() )
                    a[ nCount ] := array( len( aStruc ) ) //{}
                    afill( a[nCount], " " )
                    
                    for i := 1 to len( aStruc )                     
                        a[ nCount, i ]:= iif( ( xTmp := (cArea)->( fieldGet( i ) ) ) == Nil, Blank( aStruc[ i, DBS_TYPE] ), xTmp )
                    next i
                    
                    nCount++
                    (cArea)->( dbSkip() )
                End
END
            Else
                logfile( "SQLError.log", { cScript } )
            ENDIF

            CursorArrow()
            aSize( a, nCount-1 )            
            
            AdsCacheOpenCursors( 0 )
            AdsClrCallBack()

            if Select( "sqlarea" ) > 0 ;SQLArea -> ( DBCLOSEAREA() ) ;endif

        Endif

    endif

Return a

So, in the previous sample, instead of using ADSExecuteSQLDirect(), I would use ExecuteSQLScript(). I was only trying to give a bird's eye view of what is possible.

Take care,

Reinaldo.

fraxzi

Posts: 811

Joined: Tue May 06, 2008 04:28 AM

Re: I'm looking for some advice/suggestions.
Posted: Sat Jan 16, 2010 01:36 AM

Reinaldo,

This is a good example. Sorry for my confusion.

My best regards,
Fraxzi

Kind Regards,

Frances

Fivewin for xHarbour v18.07

xHarbour v1.2.3.x

BCC 7.3 + PellesC8 ( Resource Compiler only)

ADS 10.1 / MariaDB

Crystal Reports 8.5/9.23 DE

xMate v1.15

FiveTech Support Forums

Continue the discussion