who use PostgreSQL with Fivewin ?

[Jimmy]

hi,

i like to know who is using PostgreSQL with Fivewin

it seems me nobody use PostgreSQL but MySQL / MariaDB, or

---

if nobody is using PostgreSQL i like to ask for "new Concept"
Alaska have a Concept for PostgreSQL so all Xbase++ User will follow it when use PgDBE

it would be a big Argument if Fivewin can use PostgreSQL "the same Way" as PgDBE

[Natter]

I used (4-5 years ago)

FW_SetUnicode( .t. )
oPQ:= TPQServer():New( cHost, BaseName, TableName, password, , "public")

[Jimmy]

hi Natter,

YES, i mean TPQServer Constribution which can be used with Fivewin

i have "Problem" with PRIMARY KEY made by Xbase++ Concept of PgDBE
Alaska seems to use PRIMARY KEY in "other" Way so it does not work correct with "SavePQQ()"

---

i got a Workaround*** from Rao, but if "nobody" is using PostgreSQL yet we can prepare it for all Xbase++ User
https://forums.fivetechsupport.com/viewtopic.php?t=43733

all Xbase++ User, who are using PgDBE, will have "same" PRIMARY KEY which FUNCTION GetSerialCol() can "identify"
if is in c:\fwh\source\function\pgsuport.prg but it is "Comment out"

i can not use it "outside" PRG while it include #xtranslate and STATIC FUNCION PGLinked()
it will not "hurt" anybody when "enable" FUNCTION GetSerialCol(). PLEASE

---

to enhance SavePQQ() i check if PRIMARY KEY is FIELD "__record" -> Xbase++ PdDBE
there are also some "Trigger"which i must "update" else Data will be "corrupt" for Xbase++ Apps using PgDBE

but the Main work is : how to emulate ISAM Style

Concept of Xbase++ PgDBE have a lot of Overhead so i look for a harbour / Fivewin Way

---

when build TPQServer LIB there is a File rddcopy.c
it have HB_FUNC( HB_PQCOPYFROMWA )

Question : how to use "PostgreSQL RDBMS low-level (client API) interface code" with CLASS TPQServer

[Willi Quintana]

Hello,
I use postgres, only for reading and updating data.

Code: Select all  Expand view  RUN

Function AdoPostgre()
local oPon, oRs, oData, cString, aLIst, oRec

cString := "Driver={PostgreSQL ANSI};Server=localhost;Port=5432;Database=postgres;Uid=postgres;Pwd=ekatroncito;"
TRY
  oPon := TOleAuto():new("adodb.connection")
CATCH
  Return(.f.)
END
oPon:ConnectionString := cString

TRY
  oPon:Open()
CATCH
  ? "Error en la conexión"
  Return(.f.)
END

oRec := TOleAuto():New("adodb.recordset")

oRec:CursorLocation := 3  // adUseClient
oRec:CursorType := 3 // adOpenStatic
oRec:ActiveConnection:= oPon

oRec:Open("UPDATE test SET name = 'PRUEBA DE UPDDATE' WHERE code <= 20")

oRec:Open("SELECT * FROM test")

xbrowse(oRec)

Return(niL)

 

[Jimmy]

hi,

thx for Answer.

you seems to use ODBC, not sure if have same Problem like CLASS TPQServer()

[richard-service]

Hi Jimmy,

I used MySQL Server with TMySQL connect 3-4 years ago.

[Jimmy]

hi Richard,

i want to use PostgreSQL which seems not used by Fivewin User.

but i have Problem when use PostgreSQL Table which was create by Xbase++

my Fivewin Version 23/07 does NOT work correct with "SavePQQ()"
PRIMARY KEY will NOT correct recognize so WHERE does not work

---

if nobody work with PostgreSQL and TPQServer() Constribution i would like to ask for new "SavePQQ()"
i do have modify "SavePQQ()" and now it work correct with PostgreSQL Table create by Xbase++

[nageswaragunupudi]

my Fivewin Version 23/07 does NOT work correct with "SavePQQ()"

While we totally disagree, we suggest a totally different approach for you.

For the purpose of reading, editing and saving PostGre records, please totally avoid using FWH's TDataRow.
Also totally avoid using any functions of FWPGSUPPORT.PRG for reading, saving and primary keys.

Instead you may directly use methods of TPQquery and TPQrow classes of Harbour's hbpgsql.lib.

This is very simple.

This is the way to go:

Code: Select all  Expand view  RUN

  oQry  := oServer:Query( "select * from states" )
   XBROWSER oQry:aStruct   // structure

   // Getting primary keys
   oQry:SetKey()
   ? oQry:aKeys // --> {"id"} // primary keys array

   oRow  := oQry:GetBlankRow()  // for appending
   // or
   oRow  := oQry:GetRow( nRow ) // for editing

   // oRow has mainly datas aStruct and aRow
   // aRow contains field values
   ? oRow:aRow //--> { 1, "WA", "Washington" }

   // NOW, USING fieldnames from oRow:aStruct
   // and field values from oRow:aRow
   // Build your own dialog and edit the values

   // finally save the changes using:
   oQry:Update( oRow )
   // or
   oQry:Append( oRow )
 

You may read more in "tpostgre.prg" of Harbour.

Now, it is entirely between you and Harbour's library and FWH does not come in your way at all.

If you like to have a working sample, please let us know.

[Jimmy]

hi,

as i can say METHOD SavePQQ() work with Fivewin Sample but NOT with Xbase++ PostgreSQL Table

but when "nobody" is using PostgreSQL under Fivewin yet, why not change / new METHOD SavePQQ() to make it "ready" for Xbase++

i know Concept of PgDBE so i can help to made Fivewin "ready***" for PostgreSQL Table made by Xbase++

you can "identify" PostgreSQL Table, made by Xbase++, while it have these "internal" FIELDs

Code: Select all  Expand view  RUN

     cQuery += " __deleted    boolean NOT NULL DEFAULT false, "
      cQuery += " __record     serial  NOT NULL, "
      cQuery += " __rowversion integer NOT NULL DEFAULT 0, "
      cQuery += " __keyversion integer NOT NULL DEFAULT 0, "
      cQuery += " __lock_owner integer NOT NULL DEFAULT 0, "

and PRIMARY KEY is ALLWAYS

Code: Select all  Expand view  RUN

     cQuery += " CONSTRAINT " + cTable + "_pkey PRIMARY KEY (__record)"

***not talking about "Index" FIELD made by PgDBE

---

as i say it is NOT a Problem of CLASS TPQServer(), it is METHOD SavePQQ() of Fivewin

it is a different Concept but Fivewin already have the Solution GetSerialCol() ... why is it not "active"

this is "try and Error"

Code: Select all  Expand view  RUN

  FOR n := 1 TO LEN( ::aStructPG )
      IF ::aStructPG[ n, 10 ] == "PRI" .AND. ;
                 oQry:TableName == ::aStructPG[ n, 7 ]
         IF cSeq == nil .AND. ::aStructPG[ n, 2 ] == '+'
            cSeq := ::aStructPG[ n, 9 ]
         ENDIF
         IF VALTYPE( ::aData[ n, 2 ] ) == 'C'
            AADD( aKey, { n, ::aStructPG[ n, 8 ], TRIM( ::aOrg[ n, 2 ] ), TRIM( ::aData[ n, 2 ] ) } )
         ELSE
            AADD( aKey, { n, ::aStructPG[ n, 8 ], ::aOrg[ n, 2 ], ::aData[ n, 2 ] } )
         ENDIF
      ENDIF
   NEXT

this is Postgre SQL Way

Code: Select all  Expand view  RUN

  cSql := "select ordinal_position,column_default,column_name from " + ;
           "information_schema.columns where table_name = '" + oQry:TableName + "'" + ;
           " and column_default like 'nextval(%'"

the 2nd Solution will ALLWAYS work correct ( when use nextval() NOT currval() )

[Jimmy]

hi,

this CODE is to Import DBF into PostgreSQL Table "ala Xbase++"
it is different than FWPG_ImportFromDBF() as it include "internal" FIELD which Xbase++ use

after Table is create try use BROWSEr and "edit" it. when "save" you will got the Problem

Code: Select all  Expand view  RUN

FUNCTION DoImportNow( cHostName, cDatabase, cUser, cPassWord, cTable, cDbf )
LOCAL cQuery, i, iMax, nPosi
LOCAL oServer, oTable
LOCAL cField, cType, nLen, nReccount
LOCAL sType
LOCAL dType
LOCAL nHandle
LOCAL nStart, nStop, cLogFile
LOCAL lUseBlob, cPreText, cIns
LOCAL nLenStruc, nLenSum := 0
LOCAL aDbfStruct
LOCAL lCreateTable := .T.
LOCAL nEvery       := 100
LOCAL nCount       := 0
LOCAL lTruncate    := .F.
LOCAL lUseTrans    := .F.
LOCAL nBatchSize   := 20
LOCAL nBatchhave   := 0
LOCAL _cVia        := "DBFCDX"

   SbarText( "" )
   SbarText( "Import DBF " + cDbf )
   SbarText( "to" )
   SbarText( "Hostname " + cHostName )
   SbarText( "Catalog  " + cDatabase )
   SbarText( "Table    " + cTable )
   SbarText( "" )

   lUseBlob := .F.

   cLogFile := RTRIM( cTable ) + ".log"
   // create log file
   IF ( nHandle := FCREATE( cLogFile ) ) == F_ERROR
      MsgInfo( "Cannot create log file" )
      RETURN .F.
   ENDIF

   USE ( cDbf ) VIA TRIM(_cVia) ALIAS "IMPORT" EXCLUSIVE // CODEPAGE TRIM(_cCodepage)
   aDbfStruct := DBSTRUCT()
   nReccount := RECCOUNT()

   oServer := TPQServer() :New( cHostName, cDatabase, cUser, cPassWord )
   IF oServer:NetErr()
      MsgInfo( oServer:ErrorMsg(), "TPQServer" )
      RETURN .F.
   ENDIF

   oServer:lallCols := .F.

   IF lCreateTable
      IF oServer:TableExists( cTable )
         oServer:DeleteTable( cTable )
         IF oServer:NetErr()
            MsgInfo( oServer:ErrorMsg(), "DeleteTable" )
            FWRITE( nHandle, "Error: " + oServer:ErrorMsg() + hb_eol() )
            FCLOSE( nHandle )
            RETURN .F.
         ENDIF
      ENDIF

      // oServer:CreateTable( cTable, aDbfStruct )
      //
      // "own" Way to include "internal" FIELD(s)

      cQuery := "CREATE TABLE " + cTable + " ( "
      SbarText( "Create Table " + cTable )
      SbarText( "" )

      iMax := LEN( aDbfStruct )
      i = 1
      FOR i = 1 TO iMax
         nLenSum += aDbfStruct[ i ] [ DBS_LEN ]
         cQuery += aDbfStruct[ i, DBS_NAME ]

         SbarText( aDbfStruct[ i ] [ DBS_NAME ] )
         DO CASE
            CASE aDbfStruct[ i, DBS_TYPE ] = "C"
               cQuery += " character(" + ALLTRIM( STR( aDbfStruct[ i, DBS_LEN ] ) ) + "), "
               nLenSum += 4
            CASE aDbfStruct[ i, DBS_TYPE ] = "N"
               cQuery += " numeric(" + ALLTRIM( STR( aDbfStruct[ i, DBS_LEN ] ) ) + ',' + ALLTRIM( STR( aDbfStruct[ i, DBS_DEC ] ) ) + "), "
               nLenSum += 2
            CASE aDbfStruct[ i, DBS_TYPE ] = "D"
               cQuery += " date, "
               nLenSum += 4

            CASE aDbfStruct[ i, DBS_TYPE ] = "M"
               // IF lUseBlob = .T.
               //    cQuery += " bytea, "
               // ELSE
               cQuery += " text, "
               // ENDIF
               nLenSum += 4

            CASE aDbfStruct[ i, DBS_TYPE ] = "L"
               nLenSum += 8
               cQuery += " boolean, "

            CASE aDbfStruct[ i, DBS_TYPE ] = "V"
               // store as HEX String
               cQuery += " bytea, "
         ENDCASE
      NEXT

      // add "internal" Xbase++ v2.x ISAM Emulation Fields
      cQuery += " __deleted    boolean NOT NULL DEFAULT false, "
      cQuery += " __record     serial  NOT NULL, "
      cQuery += " __rowversion integer NOT NULL DEFAULT 0, "
      cQuery += " __keyversion integer NOT NULL DEFAULT 0, "
      cQuery += " __lock_owner integer NOT NULL DEFAULT 0, "

      IF nReccount * nLenSum < 4096
         nBatchSize := 1
      ENDIF

      //
      cQuery += " CONSTRAINT " + cTable + "_pkey PRIMARY KEY (__record)"
      cQuery += " )"

      oTable := oServer:Query( cQuery )
      IF oServer:NetErr()
         MsgInfo( oServer:ErrorMsg(), "CreateTable" )
         FWRITE( nHandle, "Error: " + oServer:ErrorMsg() + hb_eol() )
         FCLOSE( nHandle )
         RETURN .F.
      ENDIF
   ENDIF

   IF lTruncate
      oServer:Execute( "truncate table " + cTable )
      IF oServer:NetErr()
         MsgInfo( oServer:ErrorMsg(), "truncate table" )
         FWRITE( nHandle, "Error: " + oServer:ErrorMsg() + hb_eol() )
         FCLOSE( nHandle )
         RETURN .F.
      ENDIF
   ENDIF

   i := 1
   //    SetProperty( "HbImport", "ProgressBar_1", "Value", 0 )
   IF lUseTrans
      oServer:StartTransaction()
   ENDIF

   SbarText( "Start: " + TIME() )
   cPreText := "INSERT INTO " + cTable + " VALUES("

   nEvery := INT( RECCOUNT() / 100 )

   nStart := SECONDS()
   DO WHILE !EOF()
      nCount ++
      lUseBlob := .F.
      cIns := cPreText
      i = 1
      FOR i = 1 TO LEN( aDbfStruct )
         cField := aDbfStruct[ i ] [ DBS_NAME ]
         cType := aDbfStruct[ i ] [ DBS_TYPE ]
         nLen := aDbfStruct[ i ] [ DBS_LEN ]

         DO CASE
            CASE aDbfStruct[ i, DBS_TYPE ] = "C"
               cIns += " '" + STRTRAN( ALLTRIM( FIELDGET( i ) ), "'", '"' ) + "',"

            CASE aDbfStruct[ i, DBS_TYPE ] = 'N'
               cIns += " " + ALLTRIM( STR( FIELDGET( i ), aDbfStruct[ i, DBS_LEN ], aDbfStruct[ i, DBS_DEC ] ) ) + ","

            CASE aDbfStruct[ i, DBS_TYPE ] = 'D'
               IF EMPTY( FIELDGET( i ) )
                  cIns += " NULL,"
               ELSE
                  cIns += " '" + DTOC( FIELDGET( i ) ) + "',"
               ENDIF

            CASE aDbfStruct[ i, DBS_TYPE ] = 'M'
               // if you have Bitmap in Memo
               IF lUseBlob = .T.
                  // cIns += " '\x" + cBin2Hex( FIELDGET( i ) ) + "',"
                  lUseBlob := .T.
               ELSE
                  cIns += " '" + STRTRAN( FIELDGET( i ), "'", '"' ) + "',"
               ENDIF

            CASE aDbfStruct[ i, DBS_TYPE ] = "L"
               cIns += "  " + IF( FIELDGET( i ) = .T., "true, ", "false, " )

            CASE aDbfStruct[ i, DBS_TYPE ] = "V"
               // better use Type "V"
               // cIns += " '\x" + cBin2Hex( FIELDGET( i ) ) + "',"
               lUseBlob := .T.

         ENDCASE
      NEXT

      // add "__deleted" default
      cIns += "false,"                                                // "__deleted"

      // use nextval() for Sequence !
      cIns += "nextval('" + cTable + "___record_seq')" + ","          // use nextval()

      cIns += "0,"                                                    // "__rowversion"
      cIns += "0,"                                                    // "__keyversion"
      cIns += "0 "                                                    // "__lock_owner"
      cIns += ")"

      cIns += ";" + CRLF

*      nBatchhave ++
*      IF nBatchhave >= nBatchSize
*         nBatchhave := 0

         oTable := oServer:Query( cIns )

         IF oServer:NetErr()
            MsgInfo( oServer:ErrorMsg(), "INSERT Record" )
            SbarText( "Error Record: " + STR( RECNO() ) + " " + LEFT( oTable:ErrorMsg(), 70 ) )
            FWRITE( nHandle, "Error at record: " + hb_ntos( RECNO() ) + ;
                    " Description: " + cField + hb_eol() + ;
                    " Type " + cType + hb_eol() + ;
                    " Len  " + STR( nLen ) + hb_eol() + ;
                    oTable:ErrorMsg() + hb_eol() )

            FCLOSE( nHandle )
            GO BOTTOM
         ENDIF

         // Reset
         cIns := ""
*      ELSE
*         cIns += ";" + CRLF
*      ENDIF

      IF ( nCount % nEvery ) == 0
         // nPosi := GetProperty( "HbImport", "ProgressBar_1", "Value" )
         // SetProperty( "HbImport", "ProgressBar_1", "Value", nPosi + 1 )
         // DO EVENTS
         SysRefresh()
         IF lUseTrans
            oServer:commit()
            oServer:StartTransaction()
         ENDIF
      ENDIF

      // IF ::lAbort = .T.
      //    GO BOTTOM
      // ENDIF

      SKIP
   ENDDO
*   IF ( nCount % nEvery ) != 0
*      IF lUseTrans
         oServer:commit()
*      ENDIF
*   ENDIF
   //    SetProperty( "HbImport", "ProgressBar_1", "Value", 0 )
   nStop := SECONDS()

   SbarText( "" )
   SbarText( "End: " + TIME() )
   SbarText( "" )
   SbarText( "records in dbf: " + hb_ntos( RECNO() ) )
   SbarText( "imported recs: " + hb_ntos( nCount ) )
   SbarText( "Sec " + Sec2HMS( nStop - nStart ) )
   SbarText( "Rec/Sec " + hb_ntos( nCount / ( nStop - nStart ) ) )
   SbarText( "" )

   CLOSE
   IF !EMPTY( nHandle )
      FCLOSE( nHandle )
   ENDIF

   oTable:Destroy()
   oServer:Destroy()

   IF FILESIZE( cLogFile ) > 1
      RUN ( "NOTEPAD.EXE " + cLogFile )
   ELSE
      FERASE( cLogFile )
   ENDIF

RETURN .T.

PROCEDURE SbarText( cIn )
   fwlog cIn
RETURN

[nageswaragunupudi]

as i can say METHOD SavePQQ() work with Fivewin Sample but NOT with Xbase++ PostgreSQL Table

as i say it is NOT a Problem of CLASS TPQServer(), it is METHOD SavePQQ() of Fivewin

SavePQQ() is a method of TDataRow class of FWH.
That is the reason, why we advised not to use TDataRow and other FWH functions for reading, editing and modifying the PostGre records and instead directly use TPQquery and TPQrow classes of Harbour's hbpgsql.lib. This helps us to close any further discussion on SavePQQ().

this CODE is to Import DBF into PostgreSQL Table "ala Xbase++

Glad. Now your have your own function to import dbfs.

after Table is create try use BROWSEr and "edit" it. when "save" you will got the Problem

When you import dbf with your own code and "edit" using your own code, FWH does not come into picture.

[jvtecheto]

hi,

i like to know who is using PostgreSQL with Fivewin

it seems me nobody use PostgreSQL but MySQL / MariaDB, or

---

if nobody is using PostgreSQL i like to ask for "new Concept"
Alaska have a Concept for PostgreSQL so all Xbase++ User will follow it when use PgDBE

it would be a big Argument if Fivewin can use PostgreSQL "the same Way" as PgDBE

Hi jimmy

My friend Manu Exposito, creator of the MariaDB, SQLite, etc. HDO access library, among other libs,
has created a mini object-oriented library to access Postgre.

If you want to try it, it's free, it's in
[url]
https://forum.modharbour.app/viewtopic.php?f=20&t=526
[/url]

Regards

Jose.

[Jimmy]

hi RAO,

you do NOT understand what i say: it is not for ME only

it is for Xbase++ User who want to change to Fivewin and use PostgreSQL

---

i also have made a "native" Version for HMG which can be used under Fivewin
but it use GRID not XBROWSEr which is used by Fivewin

so Xbase++ User will ask : why use "extra" CODE when all are include in Fivewin

as i can say it is METHOD SavePQQ(), which are NOT working as it use "your" Concept, which nobody seems to use it
i request a Concept which IS used by Xbase++ User. it need to get "right" PRIMARY KEY which FUNCTION GetSerialCol() does

so i´m asking to "enable" existing FUNCTION GetSerialCol(), thats all

[Jimmy]

hi,

thx for Answer

My friend Manu Exposito, creator of the MariaDB, SQLite, etc. HDO access library, among other libs,
has created a mini object-oriented library to access Postgre.

If you want to try it, it's free, it's in
[url]
https://forum.modharbour.app/viewtopic.php?f=20&t=526
[/url]

i have to Login / Register to get into Mod_harbout Forum ....

---

i do have a "native" Solution to use LibPQ.DLL.
under Xbase++ / HMG is use a GRID but under Fivewin i like to use XBROWSEr

---

Fivewin does have ALL what is need but PRIMARY KEY is "identify" wrong in METHOD SavePQQ()
it does work when use Fivewin Sample but not with PostgreSQL Table made by "other" like Xbase++

but what Sense make a Concept which is not used by any Fivewin User