Page 4 of 6

Re: PostgreSQL native from Phil Ide

Posted: Wed Sep 20, 2017 2:14 pm
by bwolfsohn
And, Phil is on facebook..

Re: PostgreSQL native from Phil Ide

Posted: Wed Sep 20, 2017 3:35 pm
by PedroAlex
bwolfsohn wrote:And, Phil is on facebook..
why he´s out programming?

Re: PostgreSQL native from Phil Ide

Posted: Mon Sep 25, 2017 8:04 am
by digitsoft
Hello everyone
I enclose an example of what I can do with the API of Pablo Botella and the Class of Hector Pezoa with Modified by Nolberto Paulino and a LIB for eXpress
Just copy everything in the same folder and run the TEST.EXE for xBase 1.92.355

Re: PostgreSQL native from Phil Ide

Posted: Mon Sep 25, 2017 11:06 am
by hz_scotty
Look in your .ZIP - there are ?????~1 Files
what is the right Filename?

Re: PostgreSQL native from Phil Ide

Posted: Mon Sep 25, 2017 11:16 am
by digitsoft
Connection Error when Creating Table, corrected


hz_scotty wrote:Look in your .ZIP - there are ?????~1 Files
what is the right Filename?

Re: PostgreSQL native from Phil Ide

Posted: Mon Sep 25, 2017 11:58 am
by Auge_Ohr
hi,

i say your "Import" to PostgreSQL. it does not include Memo/Blob so i can't identify what Type is using.
i recommend to use Alaska PostgreSQL Type ... and "internal" Fields to be compatible to Xbase++ v2.x

Code: Select all

   cQuery := "CREATE TABLE " + xtab + " ( "
   
   i := 1
   aStrut := DBSTRUCT()
   FOR i = 1 TO LEN( aStrut )
      cQuery += aStrut[ i, 1 ]
      DO CASE
         CASE aStrut[ i, 2 ] = "C"
            cQuery += " character(" + ALLTRIM( STR( aStrut[ i, 3 ] ) ) + "), "
         CASE aStrut[ i, 2 ] = "N"
            cQuery += " numeric(" + ALLTRIM( STR( aStrut[ i, 3 ] ) ) + ',' + ALLTRIM( STR( aStrut[ i, 4 ] ) ) + "), "
         CASE aStrut[ i, 2 ] = "D"
            cQuery += " date, "

         CASE aStrut[ i, 2 ] = "M"
            IF ::lBlob = .T.
               cQuery += " bytea, "
            ELSE
               cQuery += " text, "
            ENDIF

         CASE aStrut[ i, 2 ] = "L"
            cQuery += " boolean, "

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

   // add "internal" 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 " + xtab + "_pkey PRIMARY KEY (__record)"

   cQuery += " )"                                           // NEED
also missing : Trigger for Update / Delete

Code: Select all

      //
      // if ISAM Stuff
      //
      IF lAlaskaPGDBE = .T.
         cQuery := "CREATE TRIGGER " + xtab + "_isam_rowversion AFTER UPDATE ON " + ;
                   xtab + " FOR EACH ROW EXECUTE PROCEDURE isam_rowversion_update()"
         oPG:exec( cQuery )
         IF ResultStatus( oPG, oMain )
         ENDIF

         cQuery := "CREATE TRIGGER " + xtab + "_isam_tablemeta AFTER INSERT OR UPDATE OR DELETE ON " + ;
                   xtab + "  FOR EACH ROW EXECUTE PROCEDURE isam_tablemeta_update()"
         oPG:exec( cQuery )
         IF ResultStatus( oPG, oMain )
         ENDIF
      ENDIF

Re: PostgreSQL native from Phil Ide

Posted: Mon Sep 25, 2017 12:12 pm
by digitsoft
Hello
For the photo fields I do not use bytea because I can not record the photo
but I am using CHARACTER VARYING, and what I do is to encode the photo in 64bit to record it and to recover it I decode it.

cSql: = "ALTER TABLE" + oConn: sChema + "." + lower (cTable) + "ADD photo CHARACTER VARYING"
           oConn: Execute (cSql)


__b64dec and __b64enc of ot4xb.lib by Pablo.


Auge_Ohr wrote:hi,

i say your "Import" to PostgreSQL. it does not include Memo/Blob so i can't identify what Type is using.
i recommend to use Alaska PostgreSQL Type ... and "internal" Fields to be compatible to Xbase++ v2.x

Code: Select all

   cQuery := "CREATE TABLE " + xtab + " ( "
   
   i := 1
   aStrut := DBSTRUCT()
   FOR i = 1 TO LEN( aStrut )
      cQuery += aStrut[ i, 1 ]
      DO CASE
         CASE aStrut[ i, 2 ] = "C"
            cQuery += " character(" + ALLTRIM( STR( aStrut[ i, 3 ] ) ) + "), "
         CASE aStrut[ i, 2 ] = "N"
            cQuery += " numeric(" + ALLTRIM( STR( aStrut[ i, 3 ] ) ) + ',' + ALLTRIM( STR( aStrut[ i, 4 ] ) ) + "), "
         CASE aStrut[ i, 2 ] = "D"
            cQuery += " date, "

         CASE aStrut[ i, 2 ] = "M"
            IF ::lBlob = .T.
               cQuery += " bytea, "
            ELSE
               cQuery += " text, "
            ENDIF

         CASE aStrut[ i, 2 ] = "L"
            cQuery += " boolean, "

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

   // add "internal" 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 " + xtab + "_pkey PRIMARY KEY (__record)"

   cQuery += " )"                                           // NEED
also missing : Trigger for Update / Delete

Code: Select all

      //
      // if ISAM Stuff
      //
      IF lAlaskaPGDBE = .T.
         cQuery := "CREATE TRIGGER " + xtab + "_isam_rowversion AFTER UPDATE ON " + ;
                   xtab + " FOR EACH ROW EXECUTE PROCEDURE isam_rowversion_update()"
         oPG:exec( cQuery )
         IF ResultStatus( oPG, oMain )
         ENDIF

         cQuery := "CREATE TRIGGER " + xtab + "_isam_tablemeta AFTER INSERT OR UPDATE OR DELETE ON " + ;
                   xtab + "  FOR EACH ROW EXECUTE PROCEDURE isam_tablemeta_update()"
         oPG:exec( cQuery )
         IF ResultStatus( oPG, oMain )
         ENDIF
      ENDIF

Re: PostgreSQL native from Phil Ide

Posted: Wed Sep 27, 2017 5:03 am
by digitsoft
Hello
I attach the files to be able to compile.

Re: PostgreSQL native from Phil Ide

Posted: Wed Sep 27, 2017 5:37 am
by hz_scotty
See my Picture, that ist from your attached file
look at the filenames!

Re: PostgreSQL native from Phil Ide

Posted: Wed Sep 27, 2017 8:07 am
by digitsoft
Thanks for the info
This is happening because I use the PKZIP from DOS, compressed attachment from winrar from windows

Please confirm that it is ok now
Thank you