PostgreSQL native from Phil Ide

This forum is for ideas and or code to be contributed for general use.
Message
Author
bwolfsohn
Posts: 648
Joined: Thu Jan 28, 2010 7:07 am
Location: Alachua, Florida USA
Contact:

Re: PostgreSQL native from Phil Ide

#31 Post by bwolfsohn »

And, Phil is on facebook..
Brian Wolfsohn
Retired and traveling around the country to music festivals in my RV.
OOPS.. Corona Virus, so NOT traveling right now...
http://www.breadmanrises.com
FB travel group: The Breadman Rises

User avatar
PedroAlex
Posts: 229
Joined: Tue Feb 09, 2010 3:06 am

Re: PostgreSQL native from Phil Ide

#32 Post by PedroAlex »

bwolfsohn wrote:And, Phil is on facebook..
why he´s out programming?
Pedro Alexandre

User avatar
digitsoft
Posts: 451
Joined: Thu Jan 28, 2010 1:33 pm
Location: Republica Dominicana
Contact:

Re: PostgreSQL native from Phil Ide

#33 Post 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
Attachments
LIB.ZIP
(169.44 KiB) Downloaded 1002 times
TEST.ZIP
(322.27 KiB) Downloaded 999 times
LIBDLL.ZIP
(1.79 MiB) Downloaded 990 times
Nolberto Paulino
Regards

User avatar
hz_scotty
Posts: 107
Joined: Thu Jan 28, 2010 8:20 am
Location: Wr.Neustadt / Österreich

Re: PostgreSQL native from Phil Ide

#34 Post by hz_scotty »

Look in your .ZIP - there are ?????~1 Files
what is the right Filename?
best regards
Hans

User avatar
digitsoft
Posts: 451
Joined: Thu Jan 28, 2010 1:33 pm
Location: Republica Dominicana
Contact:

Re: PostgreSQL native from Phil Ide

#35 Post by digitsoft »

Connection Error when Creating Table, corrected


hz_scotty wrote:Look in your .ZIP - there are ?????~1 Files
what is the right Filename?
Attachments
LIB.ZIP
(1.79 MiB) Downloaded 1038 times
TEST.ZIP
(172.43 KiB) Downloaded 1004 times
Nolberto Paulino
Regards

User avatar
Auge_Ohr
Posts: 1405
Joined: Wed Feb 24, 2010 3:44 pm

Re: PostgreSQL native from Phil Ide

#36 Post 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
greetings by OHR
Jimmy

User avatar
digitsoft
Posts: 451
Joined: Thu Jan 28, 2010 1:33 pm
Location: Republica Dominicana
Contact:

Re: PostgreSQL native from Phil Ide

#37 Post 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
Nolberto Paulino
Regards

User avatar
digitsoft
Posts: 451
Joined: Thu Jan 28, 2010 1:33 pm
Location: Republica Dominicana
Contact:

Re: PostgreSQL native from Phil Ide

#38 Post by digitsoft »

Hello
I attach the files to be able to compile.
Attachments
TEST.ZIP
(1006.25 KiB) Downloaded 963 times
LIB.ZIP
(1.79 MiB) Downloaded 973 times
Nolberto Paulino
Regards

User avatar
hz_scotty
Posts: 107
Joined: Thu Jan 28, 2010 8:20 am
Location: Wr.Neustadt / Österreich

Re: PostgreSQL native from Phil Ide

#39 Post by hz_scotty »

See my Picture, that ist from your attached file
look at the filenames!
Attachments
Z02.jpg
Z02.jpg (140.33 KiB) Viewed 21064 times
Z01.jpg
Z01.jpg (252.93 KiB) Viewed 21064 times
best regards
Hans

User avatar
digitsoft
Posts: 451
Joined: Thu Jan 28, 2010 1:33 pm
Location: Republica Dominicana
Contact:

Re: PostgreSQL native from Phil Ide

#40 Post 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
Attachments
lib.rar
(1.46 MiB) Downloaded 991 times
test.rar
(802.55 KiB) Downloaded 981 times
Nolberto Paulino
Regards

Post Reply