Page 1 of 6

PostgreSQL native from Phil Ide

Posted: Wed Apr 05, 2017 4:56 pm
by 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.

Re: PostgreSQL native from Phil Ide

Posted: Sun Aug 20, 2017 11:48 am
by 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

Re: PostgreSQL native from Phil Ide

Posted: Tue Aug 29, 2017 12:47 pm
by 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

Re: PostgreSQL native from Phil Ide

Posted: Tue Aug 29, 2017 1:28 pm
by Auge_Ohr
patito wrote: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 ?

Re: PostgreSQL native from Phil Ide

Posted: Wed Aug 30, 2017 8:37 pm
by 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

Re: PostgreSQL native from Phil Ide

Posted: Thu Aug 31, 2017 9:42 am
by Auge_Ohr
patito wrote: 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

Re: PostgreSQL native from Phil Ide

Posted: Thu Aug 31, 2017 10:18 am
by 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

Re: PostgreSQL native from Phil Ide

Posted: Thu Aug 31, 2017 5:51 pm
by Auge_Ohr
patito wrote: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
PG_working.jpg (200.31 KiB) Viewed 26057 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 ?

Re: PostgreSQL native from Phil Ide

Posted: Fri Sep 01, 2017 10:10 am
by 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

Re: PostgreSQL native from Phil Ide

Posted: Fri Sep 01, 2017 11:20 am
by Auge_Ohr
patito wrote: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