PureBasic Forums - English

Code: Select all

EnableExplicit

UsePostgreSQLDatabase()     

#Delay      = 5000 ; 5 seconds
#DBHandle   = 10
#FHandle    = 20
#ConfigFile = "pg.config"

; defaults for postgresql server access
Global PG_Config.s   = "host=localhost dbname=Test port=5432 connect_timeout=5",
       PG_User.s     = "postgres",
       PG_Password.s = "postgres"
       
; -----------------------------------------------------------------------       
Procedure OverrideDefaultConfig()
  Protected conf.s, tmp.s, conf_file.s   ;;; file content -> single string:  host=localhost dbname=Test port=5432 connect_timeout=5,user,password  Note: NO quotes!     
         
  ; if we have 'pg.config' next to the app, read it and overwrite the settings of the PG_* variables with it
  conf_file = GetPathPart(ProgramFilename()) + #ConfigFile
  If FileSize(conf_file) > 0 
    If ReadFile(#FHandle, conf_file)
      ReadStringFormat(#FHandle)              ; get over BOM should one be present
      conf = ReadString(#FHandle, #PB_UTF8)
      ;
      If FindString(conf, Chr(34))
        ConsoleError("No quotes allowed in " + #ConfigFile + #CRLF$ + "Server access overrides will be ignored!"): Delay(#Delay): ProcedureReturn
      EndIf  
      tmp  = Trim(StringField(conf, 1, ","))  ; first part of line up to first comma
      If FindString(tmp, "host", #PB_String_NoCase) And FindString(tmp, "dbname", #PB_String_NoCase)  ; these 2 definitely have to be in config string
        PG_Config = tmp
      EndIf  
      tmp  = Trim(StringField(conf, 2, ","))  ; second part of line from first comma up to second comma -> user name
      If tmp > ""   
        PG_User = tmp
      EndIf  
      tmp  = Trim(StringField(conf, 3, ","))  ; third part of line from second comma up to EoL (or third comma)
      If tmp > ""   
        PG_Password = tmp
      EndIf  
    EndIf ; ReadFile
  EndIf   ; FileSize
EndProcedure  
    
; -----------------------------------------------------------------------       
Procedure OpenDB()
  If OpenDatabase(#DBHandle, PG_Config, PG_User, PG_Password)
    PrintN("Connected to PostgreSQL server")
  Else
    ConsoleError("Connection failed: " + DatabaseError() + #CRLF$ + "Terminating ..."): Delay(#Delay)
    End
  EndIf  
EndProcedure  

; -----------------------------------------------------------------------       
; program starts here
; -----------------------------------------------------------------------
Define start.q, stop.q,   ; timing variables
       counter

If Not OpenConsole("PostgreSQL Exec") ; defaults to UTF-8 output
  Print("Failed to open console - will terminate now"): Delay(#Delay)
  End
EndIf
OverrideDefaultConfig()
;start = ElapsedMilliseconds()
OpenDB()
start = ElapsedMilliseconds()
counter = 0
If DatabaseQuery(#DBHandle, ~"select * from \"Contacts\"")   ;  ~"" = raw string so we can escape double-quotes - PostgreSQL likes DB object names quoted
  While NextDatabaseRow(#DBHandle)
    counter + 1
    ; PrintN(GetDatabaseString(#DBHandle, 1))
    ;Print(".")
    If Mod(counter, 1000) = 0
      Print(".")
    EndIf
  Wend
  FinishDatabaseQuery(#DBHandle)
Else
  ConsoleError("Query failed: " + DatabaseError()): Delay(#Delay) 
EndIf  
stop = ElapsedMilliseconds()
PrintN("Scanning of 5 million records took: " + FormatNumber(stop - start) + " ms."): Delay(#Delay)
;
CloseDatabase(#DBHandle)  
CloseConsole()

Code: Select all

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include <time.h>


void do_exit(PGconn *conn) {
    
    PQfinish(conn);
    exit(1);
}

void delay(unsigned int mseconds)
{
    clock_t goal = mseconds + clock();
    while (goal > clock());
}

int main(void) {
    
    PGconn *conn = PQconnectdb("host=localhost dbname=Test port=5432 user=postgres password=iwed" );

    if (PQstatus(conn) == CONNECTION_BAD) {
        
        fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(conn));
        do_exit(conn);
    }
	else
        printf("Connected!\n\n");

    clock_t start = clock(), diff;
    PGresult *res = PQexec(conn, "SELECT * FROM \"Contacts\"");    
    
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {

        printf("No data retrieved\n");        
        PQclear(res);
        do_exit(conn);
    }    
    
    int rows = PQntuples(res);
    printf("Records: %d", rows);
    
    for (int i = 0; i < rows; i ++) {
       if (i % 1000 == 0) {
          printf("%s", "."); 
       }
/*        printf("%s %s %s\n", 
		       PQgetvalue(res, i, 0), 
               PQgetvalue(res, i, 1), 
			   PQgetvalue(res, i, 2));
*/
    }    

    PQclear(res);

    diff = clock() - start;
    int msec = diff * 1000 / CLOCKS_PER_SEC;
    printf("\nTime taken %d seconds %d milliseconds\n\n", msec / 1000, msec % 1000);
    delay(5000);
    
    PQfinish(conn);

    return 0;
}