I'm looking for some advice/suggestions.

[MichaelMo]

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]

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: Select all

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: Select all

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

Both will work the same with ADS.


Reinaldo.

[dutch]

Dear Reinaldo,

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

Regards,
Dutch

[reinaldocrespo]

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]

...

Would work with ADS, as well as:

Code: Select all

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

...

Hello Reinaldo,

Code: Select all

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

[reinaldocrespo]

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: Select all

*-------------------------------------------------------------------------------------------------------------------------------
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]

Reinaldo,

This is a good example. Sorry for my confusion.


My best regards,
Fraxzi