topic-43642

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

PostgreSQL and Fivewin

Posted: Fri Aug 25, 2023 04:10 AM

hi,

i have Problem with "existing" PostgreSQL Table when use with Fivewin

it seems to work only when FWPG_ImportFromDBF() DBF Data using "Fivewin Way" where FIELD "ID" was add
FIELD "ID" is used as PRIMARY KEY and work "autoincrement" but METHOD SavePQQ() use currval() AFTER (!) INSERT when APPEND

so Fivewin Sample c:\fwh\samples\testpgre.prg FAIL when try to APPEND new Data

---

i have learn to use Nextval() when APPEND Data to Table but TABLE must be create using

Code (fw): Select all Collapse

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

when "edit"

cPreText := "INSERT INTO " + cTable + " VALUES("

and when APPEND add

Code (fw): Select all Collapse

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

---

there is FUNCTION FWPG_PrimaryKeys() in c:\fwh\source\function\pgsuport.prg
in same PRG are (working) static function GetSerialCol() which is not used ( WHY ? )

i have search for

FWPG_XBrSaveData()

SetPostGreCol()
SetPostGre()
XbrwSetDataSource()

but found no Sample how to use it
so what do i miss and how to use same PostgreSQL Table create by Xbase++ :?:

greeting,

Jimmy

Jimmy

Posts: 1772

Joined: Thu Sep 05, 2019 05:32 AM

Re: PostgreSQL and Fivewin

Posted: Fri Aug 25, 2023 08:11 AM

hi,

i have change

Code (fw): Select all Collapse

METHOD SavePQQ() CLASS TDataRow
...
#ifdef use_Original                              // Jimmy
   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
fwlog ::aStructPG[ n, 10 ], ::aStructPG[ n, 7 ], cSeq
         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

#else

   // Xbase++ use nextval() for PRIMARY KEY so this is the right Function
   cSeq := GetSerialCol( oQry, ::aStructPG )
fwlog var2char( cSeq )

   n := ASCAN( ::aStructPG, { | x | x[ 1 ] = cSeq } )
   IF n > 0
      AADD( aKey, { n, ::aStructPG[ n, 1 ], ::aOrg[ n, 2 ], ::aData[ n, 2 ] } )
   ENDIF
#endif

   IF EMPTY( aKey )
      RETURN .t.
   ENDIF

fwlog aKey

   FOR n := 1 TO LEN( ::aModiData )
      nFld := ::aModiData[ n, 3 ]
      uVal := ::aModiData[ n, 2 ]
      IF VALTYPE( uVal ) == 'C'
         uVal := TRIM( uVal )
      ENDIF
      //         AADD( aSave, { ::aStructPG[ nFld, 8 ], uVal } ) // wrong ???
      AADD( aSave, { ::aModiData[ n ] [ 1 ], ::aModiData[ n ] [ 2 ] } )
   NEXT

fwlog var2char( ::aModiData )
fwlog var2char( aSave )

   IF EMPTY( aSave )
      RETURN .t.
   ENDIF
   
...

Code (fw): Select all Collapse

FUNCTION GetSerialCol( oQry, aStruct )
LOCAL cSql, res
LOCAL nAt, nCol, cCol, cSeq

   //    if !PGLinked()
   //       return .f.
   //    endif

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

   res := PQExec( oQry:pDB, cSql )
   IF PQresultStatus( res ) == PGRES_TUPLES_OK
      nCol := PQgetvalue( res, 1, 1 )
      IF VALTYPE( nCol ) == 'C'
         nCol := VAL( nCol )
      ENDIF
      cSeq := PQgetvalue( res, 1, 2 )
      cSeq := AfterAtNum( "'", BeforAtNum( "'", cSeq ) )
      cCol := PQgetvalue( res, 1, 3 )
   ENDIF

   IF !EMPTY( nCol )
      nAt := ASCAN( aStruct, { | a | a[ 6 ] == nCol } )
      IF nAt > 0
         aStruct[ nAt, 2 ] := "+"
         AADD( aStruct[ nAt ], cSeq )
      ENDIF
   ENDIF

RETURN cCol

this Way i got right Result when use Xbase++ PostgreSQL Table

p.s. still have to use FW_SetUnicode( .F. ) else "Input" when "Edit" get Problem with "Umlaute"

greeting,

Jimmy

FiveTech Support Forums

PostgreSQL and Fivewin

Re: PostgreSQL and Fivewin

Continue the discussion