PostgreSQL native from Phil Ide

[Auge_Ohr]

hi,

this is the Original Source from Phil Ide to access PostgreSQL native via libpq.dll from 2004 (!!!)

it is still working with latest v9.x PostgreSQL Version but you have to include some more DLL (from PostgreSQL\BIN) into your Path/Folder

Code: Select all

libpq.dll     // all PostgreSQL Version
// v9.x DLL need for Xbase++
libeay32.dll
libecpg.dll
libecpg_compat.dll
libiconv-2.dll
libintl-8.dll
libpgtypes.dll
libxml2.dll
libxslt.dll
ssleay32.dll
zlib1.dll

XBPGSQL.DLL  // Phils Xbase++ Wrapper

i also include Pablos Version using ot4xb to call libpq.dll Function.

[Auge_Ohr]

hi,

about Phil Ide Code :
that Code is from 2003 using Xbase++ v1.8x and NoiVAR.

it seems not work with Xbase++ v1.9x so you need to modify Codeblock

Code: Select all

   oBr:addColumn( MakeFieldBlock( oPGResult, cField ), , cField )

Function MakeFieldBlock( o, c )
*  return {|| o:&(c) }       // Xbase++ v1.8 NoiVAR
RETURN { || o:DataBlock( c ) }

than add a new Method into PGSQL.PRG

Code: Select all

METHOD PGResult:DataBlock( cFname )                         // Jimmy : for PGbrowse Codeblock
LOCAL aFields
LOCAL nPosi   := 0
LOCAL xRet    := ""
LOCAL aClone
LOCAL PgType

   DEFAULT cFname TO ""

   IF VALTYPE( cFname ) = "N"
      // this IS 0 Zero - based !!!
      nPosi := cFname - 1
   ELSE
      // NOT 0 Zero - based any more
      nPosi := PQfnumber( ::resID, cFname )
   ENDIF

   IF nPosi >= 0
      // NOT 0 Zero - based any more
      //
      xRet := ::getValue( ::curTuple, nPosi )
   ENDIF
RETURN xRet

[patito]

Hi Jimmy

Browse does not work, skipper does not recover the value of the data that should appear in the browse screen.
If possible, perform a test to verify the problem.

Best Regard
Hector Pezoa

[Auge_Ohr]

Browse does not work, skipper does not recover the value of the data that should appear in the browse screen.
If possible, perform a test to verify the problem.

hm ... i have test it with Phils original CLASS myPGSQLBrowse when wrote last Msg

have you include new Code in CLASS PGResult, not CLASS PGSql, found in PGSQL.PRG ?
what is inside your query0.sql ?

[patito]

Hi Jimmy

This query displays the empty browse screen, without field and data names.
There is no error in the query, one can with debug see the correct answer

Best Regard
Hector

dbname := "pruebas"
user := "postgres"
pwd := "+++"
cConnect := "host=localhost dbname="+dbName+" user="+user+" password="+pwd
oPG := PGSql():new()
// if FExists(cQueryFile) .and.

if !oPG:connect( cConnect )
msgbox( "PG- Fallo de Coneccion main:")
return
endif
cSQL := "SELECT * FROM notes ORDER BY notes_id "
if oPG:exec(cSQL)
oHRTimer:stop()
oRes := oPG:result

msgBox("Done!"+CRLF+;
"rows="+LTrim(Str(oRes:rows))+CRLF+;
"Time="+LTrim(Str(oHRTimer:duration,15,12))+" secs","SQL Test")

aFNames := oRes:getFieldnames() Ok No error

[Auge_Ohr]

This query displays the empty browse screen, without field and data names.
There is no error in the query, one can with debug see the correct answer

Code: Select all

         oRes := oPG:result
         msgBox("Done!"+CRLF+;
                "rows="+LTrim(Str(oRes:rows))+CRLF+;
                "Time="+LTrim(Str(oHRTimer:duration,15,12))+" secs","SQL Test")
         aFNames := oRes:getFieldnames()    Ok  No error

if aFNames include right Names it must work with

Code: Select all

   oBr:setup(oPGResult)
   for i := 1 to oPGResult:cols
      cField := oPGResult:fname(i-1)
      oBr:addColumn( MakeFieldBlock( oPGResult, cField ), , cField )
   next

Function MakeFieldBlock( o, c )
RETURN { || o:DataBlock( c ) }

and Phils myPGSQLBrowse()

---

perhaps we have Problem with your Table so try to import DBF to create a new SQL Table with PGU.EXE
http://bb.donnay-software.com/donnay/vi ... f=15&t=884

[patito]

Hi Jimmy

Please run this example, and you will see that the query is correct, but the browse does not work.
1.- CREATE DATABASE pruebas (example)
2.-
cSql := "CREATE TABLE notes("
cSql += " notes_id serial , "
cSql += " First Varchar(20) not null, "
cSql += " Last Varchar(20), "
cSql += " Street Varchar(30), "
cSql += " City Varchar(30) not null, "
cSql += " State Char(2) , "
cSql += " Zip Char(10), "
cSql += " Hiredate date, "
cSql += " Married Boolean, "
cSql += " Age Integer, "
cSql += " Salary Integer, "
cSql += " Notes Varchar(30), "
cSql += " CONSTRAINT notes_pk PRIMARY KEY(notes_id) ) "

oPG:exec(cSQL)
? 'create table notes'
? 'One Moment insert datas table notes' , nSeconds := seconds()

3.-
for i := 1 to 10
cQuery := "INSERT INTO notes(notes_id, first, last, street, city, state, zip, hiredate, married,age,salary,notes) " +;
"VALUES( " + str(i) + ",'Test1', 'Test2','London', 'SE','UK','41700','2003-12-28' ,'F',42, 50000,'My First record' )"
oPG:exec( cQuery )

next

4.- File query -- "SELECT * FROM public.notes ORDER BY notes_id " --query0.sql


5.- then run the test with the pqtest.prg. the phil ide

Best Regard
Hector

[Auge_Ohr]

Please run this example, and you will see that the query is correct, but the browse does not work.

as i say : it does work on my PC

PG_working.jpg (200.31 KiB) Viewed 32804 times

Code: Select all

   cConnect := "host=localhost dbname="+dbName+" user="+user+" password="+pwd
   oPG := PGSql():new()

   IF oPG:connect( cConnect )

      cSql := "CREATE TABLE notes("
      cSql += " notes_id serial , "
      cSql += " First Varchar(20) not null, "
      cSql += " Last Varchar(20), "
      cSql += " Street Varchar(30), "
      cSql += " City Varchar(30) not null, "
      cSql += " State Char(2) , "
      cSql += " Zip Char(10), "
      cSql += " Hiredate date, "
      cSql += " Married Boolean, "
      cSql += " Age Integer, "
      cSql += " Salary Integer, "
      cSql += " Notes Varchar(30), "
      cSql += " CONSTRAINT notes_pk PRIMARY KEY(notes_id) ) "
      oPG:exec(cSQL)

      for i := 1 to 10
         cQuery := "INSERT INTO notes(notes_id, first, last, street, city, state, zip, hiredate, married,age,salary,notes) " +;
         "VALUES( " + str(i) + ",'Test1', 'Test2','London', 'SE','UK','41700','2003-12-28' ,'F',42, 50000,'My First record' )"
         oPG:exec( cQuery )
      NEXT

      cQuery := "SELECT * FROM public.notes ORDER BY notes_id "
      oHRTimer:start()
      if oPG:exec(cQuery)
         oHRTimer:stop()
         oRes := oPG:result

         msgBox("Done!"+CRLF+;
                "rows="+LTrim(Str(oRes:rows))+CRLF+;
                "Time="+LTrim(Str(oHRTimer:duration,15,12))+" secs","SQL Test")

         // uncomment this block to test using dynamic column names
         // you'll also need to change the column names used (_header, _name) to
         // whatever is suitable for your query.
         //

         aFNames := oRes:getFieldnames()

/****************************************
this stuff use NoiVAR

         // the lazy way...
         //
         nH := FCreate('result1.txt')
         while !oRes:eof()
            cLine := ''
            for i := 1 to Len(aFNames)
               cLine += oRes:&(aFNames[i])+'  '
            next
            FWrite( nH, cLine+CRLF )
            oRes:skip() // could be oRes:skip(-1)
         enddo
         FClose(nH)

         oRes:goTop() // go top!

         // the hard way...using dynamic column names
         //
         nH := FCreate('result2.txt')
         while !oRes:eof()
            cLine := oRes:_header+'  ' // field names are NOT case-sensitive!
            cLine += oRes:_name+CRLF
            FWrite( nH, cLine )
            oRes:skip()
         enddo
         FClose(nH)

         nH := FCreate('result3.txt') // now produce results in reverse order
         while !oRes:bof()
            cLine := ''
            for i := 1 to Len(aFNames)
               cLine += oRes:&(aFNames[i])+'  '
            next
            FWrite( nH, cLine+CRLF )
            oRes:skip(-1)
         enddo
         FClose(nH)
****************************************/

         Browse(oRes)

         oRes:destroy()
      endif
      oPG:disconnect()
    endif
return

p.s. which PostgreSQL Version do you use ?

[patito]

Hi Jimmy

Version 9.5
Problem solved
Error in loadquery (), I will see the reason why it does not read the query correctly the query

Best Regard
Hector

[Auge_Ohr]

Problem solved
Error in loadquery (), I will see the reason why it does not read the query correctly the query

i use PgAdmin3.EXE to write a SQL-Query and test it until it work.
than i use copy/paste into my source

if a SQL Query fail you will find Error in LOG File. look into

Code: Select all

X:\Program Files\PostgreSQL\9.5\data\pg_log\

you can test it this Way in you code

Code: Select all

         // send Query
         //
         IF ::oPG:Exec( ::cQuery )
            ::oResult := ::oPG:result
            ::nRows := ::oResult:rows
            IF ::nRows > 0