Passing a 1 uses the ODBCDBE
Passing a 2 uses the PGDBE
The ODBCDBE out-performs the PGDBE by about 40% on the average.
These tests are based on SQL SELECT statements, not ISAM.
This example retrieves data from SENDMAIL.DBF which has 271,454 records.
The cursor is returned in .64 seconds (ODBCDBE) or 1.03 seconds (PGDBE).
The output to the "output pane" for the same statement using pgAdmin III is about 1.18 seconds.
Code: Select all
#INCLUDE "dcdialog.ch"
#include "pgdbe.ch"
#INCLUDE "odbcdbe.CH"
#INCLUDE "sqlcmd.CH"
// Execute a statement (ODBCDBE)
#xcommand PGSQL <(x)> [VIA <session>] [TO <y>] ;
=> [<y> :=] PG_SqlStmtExec(<(x)>,<session>)
FUNCTION Main( cDbe )
LOCAL cConnect, oSession, i, oBrowse, aStru, GetList[0], GetOptions, ;
cSQL1, cSQL2, lStatus, nDbe, cAlias, oStmt, nSeconds, nTime
DEFAULT cDbe := '1'
nDbe := Val(cDbe)
IF nDbe == 2
IF(!DbeLoad("pgdbe"))
DCMSGBOX "Unable to load the PostgreSQL DatabaseEngine"
QUIT
ENDIF
cConnect := "DBE=pgdbe;SERVER=localhost;DB=medallion;UID=postgres;PWD=" + GetEnv('POSTGRESQL_PASSWORD')
ELSEIF nDbe == 1
IF(!DbeLoad("odbcdbe"))
DCMSGBOX "Unable to load the ODBC DatabaseEngine"
QUIT
ENDIF
cConnect := "DBE=odbcdbe;DRIVER={PostgreSQL Unicode};"
cConnect += "SERVER=localhost;PORT=5432;Database=medallion;UID=postgres;PWD=" + GetEnv('POSTGRESQL_PASSWORD')
ENDIF
SET NULLVALUE OFF
dbSetNullValue(.f.)
oSession := DacSession():New( cConnect )
IF .NOT. oSession:isConnected()
DCMSGBOX oSession:GetLastMessage(), ;
"Unable to establish connection to server"
QUIT
ENDIF
TEXT INTO cSQL1 WRAP
SELECT date, time, subject, mail_to, __record from sendmail
where error_code >= 0
order by ? ;
ENDTEXT
cSQL2 := DC_ApplySQLParams( cSQL1, {'[__record]'} )
nSeconds := Seconds()
IF nDbe == 1
PGSQL (cSQL2) VIA (oSession) TO lStatus
IF !lStatus
RETURN .f.
ENDIF
ELSE
oStmt := DacSqlStatement():fromChar(cSQL2)
oStmt:build():query()
ENDIF
nTime := Seconds() - nSeconds
cAlias := Alias()
wtf reccount(), cAlias
@ 0,0 DCBROWSE oBrowse ALIAS cAlias SIZE 120,25 ;
RESIZE DCGUI_RESIZE_RESIZEONLY
aStru := (cAlias)->(dbStruct())
FOR i := 1 TO Len(aStru)
DCBROWSECOL DATA FieldWBlock(aStru[i,1],cAlias) ;
HEADER DC_CapFirst(aStru[i,1]) WIDTH Min(aStru[i,3],10) PARENT oBrowse ;
SORT SortBlock(aStru[i,1],oSession,@oBrowse,cAlias,nDbe,cSQL1) ;
PICTURE IIF( aStru[i,1] == '__record','9999999',nil)
NEXT
DCGETOPTIONS RESIZE
DCREAD GUI FIT ;
TITLE 'PostGreSQL test (' + IIF(nDbe==1,'ODBCDBE','PGDBE') + ;
') (' + Alltrim(Str(nTime)) + ')';
OPTIONS GetOptions
// Disconnect from server
oSession:disconnect()
RETURN nil
* -----------
PROCEDURE Appsys ; RETURN
* -----------
STATIC FUNCTION SortBlock( cFieldName, oSession, oBrowse, cAlias, nDbe, cSQL )
RETURN {||Resort(cFieldName,oSession,oBrowse,cAlias,nDbe,cSQL)}
* -----------
STATIC FUNCTION ReSort( cFieldName, oSession, oBrowse, cAlias, nDbe, cSQL )
LOCAL oStmt, lStatus, nSeconds
(cAlias)->(dbCloseArea())
cSQL := Strtran(cSQL,'?',cFieldName)
nSeconds := Seconds()
IF nDbe == 2
oStmt := DacSqlStatement():fromChar(cSQL)
oStmt:build():query()
ELSE
PGSQL (cSQL) VIA (oSession) TO lStatus
ENDIF
wtf Seconds() - nSeconds
oBrowse:refreshAll()
RETURN nil
* ---------
FUNCTION PG_SqlStmtExec(cSql,oSession)
LOCAL oError, lStatus := .f., GetList[0], GetOptions, ;
bError := ErrorBlock({|oError|break(oError)}), ;
aStack[0], aStackList[0], i := 1, cSqlError
BEGIN SEQUENCE
// Execute a statement
lStatus := SqlStmtExec(cSql,oSession,,,.F.)
RECOVER USING oError
DO WHILE .t.
AAdd( aStackList, "Called from " + Trim(ProcName(i)) + ;
"(" + Alltrim(Str(ProcLine(i))) + ")" )
i++
IF Empty(ProcName(i))
EXIT
ENDIF
ENDDO
cSqlError := OdbcDebug(oSession)[15,2]
@ 0,0 DCSAY 'Error in SQL statement:' SAYSIZE 0 FONT '10.Lucida Console' ;
RESIZE DCGUI_RESIZE_REPOSONLY_Y
@ 1,0 DCMULTILINE cSql SIZE 120,18 FONT '10.Lucida Console' NOHSCROLL ;
RESIZE DCGUI_RESIZE_RESIZEONLY
@ 20,0 DCMULTILINE cSqlError SIZE 120,3 FONT '8.Lucida Console' NOHSCROLL ;
RESIZE DCGUI_RESIZE_REPOSY_RESIZEX
FOR i := 1 TO Len(aStackList)
@ 23+i,0 DCSAY aStackList[i] FONT '10.Lucida Console' SAYSIZE 0 ;
RESIZE DCGUI_RESIZE_REPOSONLY_Y
NEXT
DCGETOPTIONS RESIZE
DCREAD GUI FIT TITLE 'SQL Error' BUTTONS DCGUI_BUTTON_OK ;
MODAL OPTIONS GetOptions
END SEQUENCE
ErrorBlock(bError)
RETURN lStatus
... hm ... how to create a "big" Reference DBF so we can testdrive with same data ...