Test program for SQL queries

[rdonnay]

Here is a test program that can be used to compare performance of ODBCDBE (postgreSQL), PGDBE (postgreSQL) and ADSDBE (streamlineSQL).

Syntax:

SQLQuery [nDbe] [cDatabase]

nDbe
------

1 = ODBCDBE
2 = PGDBE
3 = ADSDBE

cDatabase is either the postgreSQL database or the ADS dictionary.

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, cDatabase )

LOCAL cConnect, oSession, i, oBrowse, aStru, GetList[0], GetOptions, ;
      cSQL := '', lStatus, nDbe, cAlias, oStmt, nSeconds, nTime, ;
      cDbeName

DEFAULT cDbe := '1', ;
        cDatabase := 'northwind'

nDbe := Val(cDbe)

IF nDbe == 3

  IF cDatabase == 'northwind'
    cDatabase := GetEnv('USERPROFILE') + ;
     '\Documents\Xbase++\source\samples\data\northwind\dbf\northwind.add'
  ENDIF
  cDbeName := 'ADSDBE'
  IF (!DbeLoad("adsdbe"))
    DCMSGBOX "Unable to load the Advantage DatabaseEngine"
    QUIT
  ENDIF

  cConnect := "DBE=adsdbe;SERVER=" + cDatabase + ";DbfsUseNull=FALSE"

ELSEIF nDbe == 2

  cDbeName := 'PGDBE'
  IF (!DbeLoad("pgdbe"))
    DCMSGBOX "Unable to load the PostgreSQL DatabaseEngine"
    QUIT
  ENDIF

  cConnect := "DBE=pgdbe;SERVER=localhost;DB=" + cDatabase + ";UID=postgres;PWD=" + GetEnv('POSTGRESQL_PASSWORD')

ELSEIF nDbe == 1

  cDbeName := 'ODBCDBE'
  IF (!DbeLoad("odbcdbe"))
    DCMSGBOX "Unable to load the ODBC DatabaseEngine"
    QUIT
  ENDIF

  cConnect := "DBE=odbcdbe;DRIVER={PostgreSQL ANSI};"
  cConnect += "SERVER=localhost;PORT=5432;Database=" + cDatabase + ";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

DO WHILE .t.

  @ 0,0 DCSAY 'SQL Statement:' SAYSIZE 0
  @ 1,0 DCMULTILINE cSQL SIZE 100,10 FONT '10.Lucida Console'

  DCREAD GUI FIT ADDBUTTONS TITLE 'SQL Query (' + cDbeName + ') (' + ;
    'DB='+cDatabase+')' ;
    MODAL TO lStatus

  IF !lStatus
    EXIT
  ENDIF

  nSeconds := Seconds()

  IF nDbe == 1

    PGSQL (cSQL) VIA (oSession) TO lStatus
    IF !lStatus
      LOOP
    ENDIF

  ELSEIF nDbe == 2

    oStmt := DacSqlStatement():fromChar(cSQL)
    oStmt:build():query()

  ELSEIF nDbe == 3

    lStatus := DC_AdsRunSql( cSQL,, oSession )

  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 ;
      PICTURE IIF( aStru[i,2] $ 'IN','9999999',nil)
  NEXT

  DCGETOPTIONS RESIZE

  DCREAD GUI FIT ADDBUTTONS ;
   TITLE 'PostGreSQL test (' + cDbeName + ;
          ') (' + Alltrim(Str(nTime)) + ')';
   OPTIONS GetOptions ;
   TO lStatus

   dbCloseAll()

   IF !lStatus
     EXIT
   ENDIF

ENDDO

// Disconnect from server
oSession:disconnect()

RETURN nil

* -----------

PROCEDURE Appsys ; RETURN

* -----------

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

[rdonnay]

Using the test program, I have made PGDBE crash on SELECT statements that work just fine with ODBCDBE and ADSDBE.

Specifically, a SELECT * from sendmail crashes PGDBE.
Sendmail.dbf is 271454 records with 21 fields.

If I select only 7 columns, then PGDBE doesn't crash.
This will work:

SELECT Date, Time, User_id, User_name, mail_to, mail_from, subject from sendmail

Based on my preliminary tests, I cannot recommend using PGDBE for SQL SELECT statements that could potentially overload the DBE. I have not done these tests in ISAM mode. That comes next.

On the other hand, postgreSQL seems to work very well with the ODBCDBE.