Page 1 of 1
I'm looking for some advice/suggestions.
Posted: Thu Jan 14, 2010 8:31 pm
by 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?
Re: I'm looking for some advice/suggestions.
Posted: Thu Jan 14, 2010 8:39 pm
by 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.
Re: I'm looking for some advice/suggestions.
Posted: Thu Jan 14, 2010 11:10 pm
by dutch
Dear Reinaldo,
How do the program speed between both codes, in case of Big and Small Database?
Regards,
Dutch
Re: I'm looking for some advice/suggestions.
Posted: Fri Jan 15, 2010 12:29 am
by 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.
Re: I'm looking for some advice/suggestions.
Posted: Fri Jan 15, 2010 12:51 am
by 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
Re: I'm looking for some advice/suggestions.
Posted: Fri Jan 15, 2010 3:04 pm
by 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.
Re: I'm looking for some advice/suggestions.
Posted: Sat Jan 16, 2010 1:36 am
by fraxzi
Reinaldo,
This is a good example. Sorry for my confusion.
My best regards,
Fraxzi