METHOD SavePQQ() need "id" or how to use own PRIMARY KEY ?

[Jimmy]

hi,

when try to use "existing" PostgreSQL Table i have Problem with METHOD SavePQQ()
it seems to work when use FWPG_ImportFromDBF() to create a "new" SQL Table

---

in METHOD SavePQQ() it is "WHERE"

Code: Select all | Expand

   FOR n := 1 TO LEN( aKey )
      IF n == 1
         cSql += " WHERE "
      ELSE
         cSql += " AND "
      ENDIF
      cSql += aKey[ n, 2 ] + " = " + FW_ValToSQL( aKey[ n, 3 ] )
   NEXT

it get those aKey from ::aStructPG
but ::aStructPG does NOT contain "my" PRIMARY KEY

var2char(::aStructPG[1]) = "{artnr, C, 5, 0, 16820, 1, artikel, __lock_owner, NIL, NIL}"

p.s. __lock_owner is "last" FIELD ... i guess while not found "id"

so it did not work when PRIMARY KEY was create this Way

Code: Select all | Expand

      // all FIELD before 
        
      // 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, "

      // Alaska have this
      // CONSTRAINT artikel_pkey PRIMARY KEY (__record)
      //
      cQuery += " CONSTRAINT " + cTable + "_pkey PRIMARY KEY (__record)"
      cQuery += " )" 

      oTable := oServer:Query( cQuery )

and was "filled"

Code: Select all | Expand

      cIns += "false,"                                                // "__deleted"
      cIns += "nextval('" + cTable + "___record_seq')" + ","          // use nextval() for Sequence !
      cIns += "0,"                                                    // "__rowversion"
      cIns += "0,"                                                    // "__keyversion"
      cIns += "0 "                                                    // "__lock_owner"
      cIns += ")"

---


so my Question is : how to use own PRIMARY KEY

[nageswaragunupudi]

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

In Oracle, we use sequences to generate unique IDs and use NEXTVAL(..) for the next value.
Is there a similar facility in PostGre? Can you please explain how it works? ... (for my learning purposes)

I will look into your main question.

[Jimmy]

hi,

Yes i think it is a similar under PostgreSQL

have a look at

9.15. Sequence Manipulation Functions

---

i use Concept from Alaska with some "internal" FIELD ("__xxx") which are "usefull"

PRIMARY KEY is based on "__record"

Code: Select all | Expand

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

when INSERT it increment next UNIQUE number this Way

Code: Select all | Expand

   cIns += "nextval('" + cTable + "___record_seq')" + ","  

---

i found in c:\fwh\source\function\pgsuport.prg

function FWPG_PrimaryKeys( oQry, cTable )

or

#ifdef UNUSEDFUNCS
static function GetSerialCol( oQry, aStruct )

it would be nice if METHOD SavePQQ() use these Function for WHERE

[Jimmy]

hi,,

forgot to say ;

it happens when PostgreSQL Table is UTF8
it work when Table use WIN1252

wrong using UTF8 Table

TDATAROW:SAVEPQQ( 1787 ) var2char(::aStructPG[1]) = "{artnr, C, 5, 0, 16820, 1, artikel, __lock_owner, NIL, NIL}"
TDATAROW:SAVEPQQ( 1848 ) cSql = "UPDATE public.artikel SET vkgesamt = 'sss' WHERE __record = 0.00"

right using ANSI Table

TDATAROW:SAVEPQQ( 1787 ) var2char(::aStructPG[1]) = "{artnr, C, 5, 0, 19174, 1, artikel, artnr, NIL, NIL}"
TDATAROW:SAVEPQQ( 1848 ) cSql = "UPDATE public.artikel SET bestand = 222.00 WHERE __record = 88"

p,s, have try FW_SetUnicode( .t. / .f.) and CLIENT_ENCODING "UTF8" / "WIN1252"

[nageswaragunupudi]

WHERE __record

First thing is that the function is identifying the primary key column correctly.

The issue according to you is when client and server encodings are different,

[Jimmy]

hi,
thx for Answer

First thing is that the function is identifying the primary key column correctly.

it seems Function to identify PRIMARY KEY work correct with UFT8-Table

TDATAROW:SAVEPQQ( 1795 ) var2char(aTest) = "__record"
TDATAROW:SAVEPQQ( 1804 ) ::aStructPG[ n, 10 ] = "PRI" ::aStructPG[ n, 7 ] = "artikel" cSeq = "artikel___record_seq"

The issue according to you is when client and server encodings are different,

hm ... how can encoding change FIELD Position

this line "bestand" is ok

TDATAROW:SAVEPQQ( 1827 ) var2char(::aModiData) = "{{bestand, 221.00, 6}}"

but aSave have wrong FIELD Name "mindest"

TDATAROW:SAVEPQQ( 1828 ) var2char(aSave) = "{{mindest, 221.00}}"

so i get this Error

TDATAROW:SAVEPQQ( 1862 ) cSql = "UPDATE public.artikel SET mindest = 221.00 WHERE __record = 0.00"

Code: Select all | Expand

METHOD SavePQQ() CLASS TDataRow

LOCAL lAppend  := ( ::RecNo == 0 )
LOCAL oQry     := ::uSource
LOCAL aModi    := ::aModiData
LOCAL aKey     := {}
LOCAL aSave    := {}
LOCAL cSeq, nCurVal
LOCAL n, v, nFld, cSql, uVal, cWhere, nRows, nRow
LOCAL lRefresh := .f.
LOCAL aTest

fwlog oQry:TableName
fwlog var2char(::aStructPG[1])

* aTest := FWPG_PrimaryKeys( oQry, oQry:TableName )
* fwlog var2char(aTest)

aTest := GetSerialCol( oQry, ::aStructPG )
fwlog var2char(aTest)

   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

   IF EMPTY( aKey )
      RETURN .t.
   ENDIF

   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 ???
      NEXT

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

[nageswaragunupudi]

This a small tip about using fwlog.
No need to use var2char()

Just write

Code: Select all | Expand

fwlog aSave

fwlog function takes care of converting the array as string.
This also works for small arrays

Code: Select all | Expand

? aSave

I will look into the main issue and get back.
Please tell me how to find the encoding of a specific table?

[Jimmy]

hi,

i wonder why not :aModiData is used for aSave

Code: Select all | Expand

*        AADD( aSave, { ::aStructPG[ nFld, 8 ], uVal } ) // wrong for UTF8 Table ???
        AADD( aSave, { ::aModiData[n][1], ::aModiData[n][2] } )

---
i have "dump" (big) Array of PG-Structure ( have split it for better reading)

var2char(::aStructPG) = "{
{artnr, C, 5, 0, 16820, 1, artikel, __lock_owner, NIL, NIL},
{artikel, C, 30, 0, 16820, 2, artikel, bpreis3, NIL, NIL},
{verpackung, C, 10, 0, 16820, 3, artikel, lagerein, NIL, NIL},
{einheit, C, 3, 0, 16820, 4, artikel, lageraus, NIL, NIL},
{apreis, N, 9, 2, 16820, 5, artikel, ktkgegal, NIL, NIL},
{bestand, N, 9, 2, 16820, 6, artikel, mindest, NIL, NIL},
{datletzab, C, 8, 0, 16820, 7, artikel, aufmonate, NIL, NIL},
{warengrupe, N, 3, 0, 16820, 8, artikel, lastmhd, NIL, NIL},
{mwst, N, 2, 0, 16820, 9, artikel, __deleted, NIL, NIL},

{vkgesamt, +, 9, 2, 16820, 10, artikel, __record, artikel___record_seq, PRI},

{epreis, N, 9, 2, 16820, 11, artikel, __rowversion, NIL, NIL},
{datletzzu, C, 8, 0, 16820, 12, artikel, __keyversion, NIL, NIL},
{kkpreis, N, 9, 2, 16820, 13, artikel, apreis, NIL, NIL},
{steinh, C, 3, 0, 16820, 14, artikel, bestand, NIL, NIL},
{ststueck, N, 8, 2, 16820, 15, artikel, warengrupe, NIL, NIL},
{stpreis, N, 9, 2, 16820, 16, artikel, mwst, NIL, NIL},
{code, C, 10, 0, 16820, 17, artikel, vkgesamt, NIL, NIL},
{gewicht, N, 8, 2, 16820, 18, artikel, epreis, NIL, NIL},
{orgbestand, N, 11, 2, 16820, 19, artikel, kkpreis, NIL, NIL},
{diff, N, 11, 2, 16820, 20, artikel, ststueck, NIL, NIL},
{wert, N, 14, 2, 16820, 21, artikel, stpreis, NIL, NIL},
{neubestand, N, 9, 2, 16820, 22, artikel, gewicht, NIL, NIL},
{bpreis1, N, 9, 2, 16820, 23, artikel, orgbestand, NIL, NIL},
{bpreis2, N, 9, 2, 16820, 24, artikel, diff, NIL, NIL},
{bpreis3, N, 9, 2, 16820, 25, artikel, wert, NIL, NIL},
{lagerein, N, 11, 2, 16820, 26, artikel, neubestand, NIL, NIL},
{lageraus, N, 11, 2, 16820, 27, artikel, bpreis1, NIL, NIL},
{ktkgegal, L, 1, 0, 16820, 28, artikel, bpreis2, NIL, NIL},
{lastref, C, 9, 0, 16820, 29, artikel, artikel, NIL, NIL},
{chinaart, C, 30, 0, 16820, 30, artikel, verpackung, NIL, NIL},
{mindest, N, 3, 0, 16820, 31, artikel, einheit, NIL, NIL},
{aufmonate, N, 2, 0, 16820, 32, artikel, lastplatz, NIL, NIL},
{lastplatz, C, 4, 0, 16820, 33, artikel, steinh, NIL, NIL},
{lastmhd, D, 8, 0, 16820, 34, artikel, datletzab, NIL, NIL},

{__deleted, L, 1, 0, 16820, 35, artikel, artnr, NIL, NIL},
{__record, +, 9, 0, 16820, 36, artikel, lastref, NIL, NIL, artikel___record_seq},
{__rowversion, N, 9, 0, 16820, 37, artikel, code, NIL, NIL},
{__keyversion, N, 9, 0, 16820, 38, artikel, chinaart, NIL, NIL},
{__lock_owner, N, 9, 0, 16820, 39, artikel, datletzzu, NIL, NIL}}"

you can see that 10th Element have "artikel___record_seq" which is wrong and "dupe"
so i got

aKey = {{10,"__record",0.00,0.00}}

Question : what is 8th Element of ROW from ::aStructPG for

[Jimmy]

hi,

i have used from c:\fwh\source\function\pgsuport.prg

function GetSerialCol( oQry, aStruct )

to get PRIMARY KEY

and change to

Code: Select all | Expand

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

i´m not sure why it use a FOR / NEXT loop for aKey

Code: Select all | Expand

      FOR n := 1 TO LEN( aKey )
         IF n == 1
            cSql += " WHERE "
         ELSE
            cSql += " AND "
         ENDIF

i don´t understand why it can be "more" that 1 x Key == WHERE while it is a single ROW to "save"
how did a Sample using AND look like

---

now it work with ANSI ans UTF8 Table

p.s. have not try APPEND yet

Code: Select all | Expand

   IF lAppend .AND. !EMPTY( cSeq )
      nCurVal := FWPG_Execute( oQry, "SELECT currval( '" + cSeq + "' )" ) [ 1, 1 ]
   ENDIF

i wonder about currval() ... i "think" it must be nextval() when APPEND

[nageswaragunupudi]

i don´t understand why it can be "more" that 1 x Key == WHERE while it is a single ROW to "save"
how did a Sample using AND look like

We can create a table defining primary key to be a combination of more than one field.

[Jimmy]

hi,

thx for Answer

We can create a table defining primary key to be a combination of more than one field.

hm ...

calling METHOD SavePQQ() is for "single" Record
FIELD for PRIMARY KEY are Type "Int" and UNIQUE

as i understand PRIMARY KEY should work for us like RECORD() to "identify"

you can have 2 x "Int" like this

Code: Select all | Expand

CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);

but for a "normal" App you don´t have such "bigint" to "identify" ( need 64 Bit )

[nageswaragunupudi]

Primary key can be a combination of any number of fields of different types. Need not all be integer fields only. All RDBMSs support this.
Different programmers have different ideas and preferences.

I personally know such cases of very complex primary keys. Just those programmers' choice.
eg: constraints like

Code: Select all | Expand

PRIMARY KEY (BRANCH,DEPT,ROWID)

where branch and dept are character fields and rowid is an integer.
The programmer feels it is convenient for him to consolidate tables from different branches into the main table at head quarters.

Though I personally prefer using one autoincrement integer field as primary key, when we make FWH library we need to cater to all kinds of programmers and their needs and preferences.

[nageswaragunupudi]

Question : what is 8th Element of ROW from ::aStructPG for

In cases of SQL like this

Code: Select all | Expand

SELECT first AS firstname, ...

Column 1 shows "firstname" and column 8 shows the actual name of the field, i.e., "first"
So, we need to use column 8 only for building update or insert sql.

[nageswaragunupudi]

Mr. Jimmy

You pointed out that primary keys are not correctly recognized by our libraries.
We tested this issue.
You are right partially.

Primary key is correctly recognized when and ONLY WHEN the primary key field is defined as SERIAL. But our libraries are failing on all other cases.

We are looking into this and will fix and provide you with the revised programs very soon. Please wait.

Another issue:
The function FWPG_PrimaryKeys() is always returning zero rows.
We will fix this also.

Please bear with us a little and we will get back to you with proper solution very soon.

[nageswaragunupudi]

The function FWPG_PrimaryKeys() is always returning zero rows.

This is due to a very very silly mistake in the source code;
In the program source\function\pgsuport.prg
in the function FWPG_PrimaryKeys(...)
This buggy line

Code: Select all | Expand

   StrTran( cSql, "<TABLE>", cTable )
 

should be:

Code: Select all | Expand

   cSql := StrTran( cSql, "<TABLE>", cTable )
 

Very embarrassing.
Kindly wait for some more changes