how postgreSQL connect

Moderator: Rathinagiri

User avatar
AUGE_OHR
Posts: 2060
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany

Re: how postgreSQL connect

Post by AUGE_OHR »

Empty GRID with \POSTGRESQL_1\demo.prg

i have modify Sample and transfer of DBF to PG-Table work. (test with PgAdmin)
now i want to show it but i get a Empty Grid ?

Code: Select all

   rddSetDefault( "DBF" )
   // get Structure for Grid
   USE (cDBF) EXCLUSIVE         // SHARED
   aStrut := DBSTRUCT()
   CLOSE
   iMax := LEN(aStrut)
   FOR i := 1 TO iMax
      AADD(aField,aStrut[i][1])
      AADD(aWidth,aStrut[i][3]*10)
   NEXT

   // connect to Server
   nConnectionHandle := dbPGConnection( cServer + ";" + cDataBase + ";" + cUser + ";" + cPassWord )
   // create Table and transfer DBF
   CreatePGTable( cServer , cDataBase , cUser , cPassWord ,cTable,cDBF)

   * ---------------- SQL RDD ---------------- *

   USE "SELECT * FROM "+cTable+" ;" ALIAS Test NEW VIA "pgrdd" CONNECTION nConnectionHandle

   DEFINE WINDOW Form_1 ;
      AT 0,0 ;
      WIDTH 800 ;
      HEIGHT 510 ;
      TITLE 'Hello World! ' +cTable ;
      MAIN

      DEFINE MAIN MENU
         POPUP 'File'
            ITEM 'Append (Alt+A)'            ACTION Form_1.Grid_1.Append
            ITEM 'Save Last Appended Record (Alt+S)'   ACTION Form_1.Grid_1.Save
            ITEM 'Set RecNo'            ACTION Form_1.Grid_1.RecNo := val(InputBox('',''))
            ITEM 'Get RecNo'            ACTION MsgInfo( Str(Form_1.Grid_1.RecNo) )
            ITEM 'Delete'               ACTION Form_1.Grid_1.Delete
            ITEM 'Recall'               ACTION Form_1.Grid_1.Recall
         END POPUP
      END MENU

// hm ... why is it empty

      @ 10,10 GRID Grid_1 ;
         WIDTH 770 ;
         HEIGHT 440 ;
         HEADERS aField
         WIDTHS aWidth
         ROWSOURCE "Test"

   END WINDOW

   CENTER WINDOW Form_1

   ACTIVATE WINDOW Form_1

Return
need help please
have fun
Jimmy
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

Re: how postgreSQL connect

Post by jairpinho »

AUGE_OHR wrote: Tue Aug 27, 2019 3:00 am Empty GRID with \POSTGRESQL_1\demo.prg

i have modify Sample and transfer of DBF to PG-Table work. (test with PgAdmin)
now i want to show it but i get a Empty Grid ?

Code: Select all

   rddSetDefault( "DBF" )
   // get Structure for Grid
   USE (cDBF) EXCLUSIVE         // SHARED
   aStrut := DBSTRUCT()
   CLOSE
   iMax := LEN(aStrut)
   FOR i := 1 TO iMax
      AADD(aField,aStrut[i][1])
      AADD(aWidth,aStrut[i][3]*10)
   NEXT

   // connect to Server
   nConnectionHandle := dbPGConnection( cServer + ";" + cDataBase + ";" + cUser + ";" + cPassWord )
   // create Table and transfer DBF
   CreatePGTable( cServer , cDataBase , cUser , cPassWord ,cTable,cDBF)

   * ---------------- SQL RDD ---------------- *

   USE "SELECT * FROM "+cTable+" ;" ALIAS Test NEW VIA "pgrdd" CONNECTION nConnectionHandle

   DEFINE WINDOW Form_1 ;
      AT 0,0 ;
      WIDTH 800 ;
      HEIGHT 510 ;
      TITLE 'Hello World! ' +cTable ;
      MAIN

      DEFINE MAIN MENU
         POPUP 'File'
            ITEM 'Append (Alt+A)'            ACTION Form_1.Grid_1.Append
            ITEM 'Save Last Appended Record (Alt+S)'   ACTION Form_1.Grid_1.Save
            ITEM 'Set RecNo'            ACTION Form_1.Grid_1.RecNo := val(InputBox('',''))
            ITEM 'Get RecNo'            ACTION MsgInfo( Str(Form_1.Grid_1.RecNo) )
            ITEM 'Delete'               ACTION Form_1.Grid_1.Delete
            ITEM 'Recall'               ACTION Form_1.Grid_1.Recall
         END POPUP
      END MENU

// hm ... why is it empty

      @ 10,10 GRID Grid_1 ;
         WIDTH 770 ;
         HEIGHT 440 ;
         HEADERS aField
         WIDTHS aWidth
         ROWSOURCE "Test"

   END WINDOW

   CENTER WINDOW Form_1

   ACTIVATE WINDOW Form_1

Return
need help please

how could you compile the demo without this error
C: /Users/LABORA~1/AppData/Local/Temp/hbmk_sn3eg1.dir/demo.o: demo.c :(. Data + 0x38): undefined reference to `HB_FUN_DBPGCONNECTION '
C: /Users/LABORA~1/AppData/Local/Temp/hbmk_sn3eg1.dir/demo.o: demo.c :(. Data + 0x258): undefined reference to `HB_FUN_PGRDD '

attaching the project you're compiling that I can help you gets faster
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br
User avatar
AUGE_OHR
Posts: 2060
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany

Re: how postgreSQL connect

Post by AUGE_OHR »

jairpinho wrote: Sat Aug 31, 2019 1:49 am
AUGE_OHR wrote: Tue Aug 27, 2019 3:00 am Empty GRID with \POSTGRESQL_1\demo.prg

Code: Select all

   * ---------------- SQL RDD ---------------- *
   USE "SELECT * FROM "+cTable+" ;" ALIAS Test NEW VIA "pgrdd" CONNECTION nConnectionHandle

// hm ... why is it empty
      @ 10,10 GRID Grid_1 ;
         WIDTH 770 ;
         HEIGHT 440 ;
         HEADERS aField
         WIDTHS aWidth
         ROWSOURCE "Test"
need help please
how could you compile the demo without this error
C: /Users/LABORA~1/AppData/Local/Temp/hbmk_sn3eg1.dir/demo.o: demo.c :(. Data + 0x38): undefined reference to `HB_FUN_DBPGCONNECTION '
C: /Users/LABORA~1/AppData/Local/Temp/hbmk_sn3eg1.dir/demo.o: demo.c :(. Data + 0x258): undefined reference to `HB_FUN_PGRDD '
attaching the project you're compiling that I can help you gets faster
sorry,
on top i have

Code: Select all

REQUEST PGRDD
so App work now.

my Question is why GRID is Empty :o .
is that Syntax OK :?:

it show Header (aFields) and Column seem to have correct WIDTHS (aWidth).
as i say i have check new create Table (ANSI WIN1252) with PgAdmin.

---

i have modify code so it is limited e.g. UTF8 Table / Server as i use ANSI Win 1252.
do i need Unicode for GRID or can i use ANSI :?:
have fun
Jimmy
User avatar
AUGE_OHR
Posts: 2060
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany

Re: how postgreSQL connect

Post by AUGE_OHR »

hi,

this line seems correct while debugger show it under ALIAS

Code: Select all

USE "SELECT * FROM "+cTable+" ;" ALIAS Test NEW VIA "pgrdd" CONNECTION nConnectionHandle
HMG_GRID.jpg
HMG_GRID.jpg (128.05 KiB) Viewed 4324 times
any Idee why GRID is still empty :?:
have fun
Jimmy
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

Re: how postgreSQL connect

Post by jairpinho »

hello guys after a lot of research on hmg and postgresql I found something according to the liks source below thanks to Mr Crocciari, Daniel( hmglights.wordpress.com) and Cisino Junior, follows an example in a functional attachment with hmg.

source
http://www.pctoledo.com.br/forum/viewto ... 57&t=18661
https://www.youtube.com/user/hmglights
example base https://github.com/CisinoJr/hb-postgre

ide configuration include lines
libs = libpq -> ok

files needed:

comerr32.dll
gssapi32.dll
k5sprt32.dll
krb5_32.dll
libeay32.dll
libiconv-2.dll
libintl-8.dll
libpq.dll
ssleay32.dll
postgres.ch
hmg was created with version 8 dll follow link to download dll
https://ftp.postgresql.org/pub/odbc/ver ... 4_0200.zip


POSTGRESQL.1A

Code: Select all


/*
 HMG Grid PostgreSql Demo
 (c) 2010 Roberto Lopez
*/

#include <hmg.ch> 
#include "postgres.ch"

        
Function Main

   local cQuery
   Private aTable:={}
   public oServer := nil

   public pcFileIni := "config.ini"

   public pcHostName := '127.0.0.1' 
   public pcDataBase := 'data1'
   public pcUserName := 'postgres'
   public pcPassWord := '123456'

	//nConnectionHandle	:= PGConnect( cServer + ";" + cDataBase + ";" + cUser + ";" + cPassWord ) 
   if !pgConnect()
      msgStop("Não foi possível conectar ao servidor de banco de dados, entre em contato com seu suporte!", "DATABASE SERVER ERRO")
		//Exit
   else
        createTable()

   endif


   Load Window Main
   		update_grid()
   Main.Center
   Main.Activate
   

Return

Function CreateTable ()

Local oQuery, oRow, i 
Local cQuery


	if oServer:TableExists("TEST")
		oQuery := oServer:Execute("DROP TABLE Test")
		oQuery:Destroy()
	endif

	cQuery := "CREATE TABLE test("
	cQuery += "     Code integer not null primary key, "
	cQuery += "     Name Varchar(40), "
	cQuery += "     Salary Double Precision, "
	cQuery += "     Creation Date, "
	cQuery += "     Description text ) "

	oQuery := oServer:Query(cQuery)

	if oQuery:neterr()
		MsgStop ( oQuery:Error() )
	endif

	oQuery:Destroy()

	For i := 1 To 10

		cQuery := "INSERT INTO test ( code , name , salary , creation , description ) VALUES ( " + str(i) + " , 'Name " + str(i) + " ' , " + str(i*1000) + " , '2010-01-01' , 'Some Text...' );"

		oQuery := oServer:Query(cQuery)

		if oQuery:neterr()
			MsgStop ( 'error' )
			Exit
		endif

	next i

	oQuery:destroy()

	oServer:Commit()

	oServer:Destroy()

Return nil


function update_grid()
local cQuery, nLimit
local nPos



                  cQuery:="SELECT * FROM test order by code"



      doMethod("Main", "Grid_1","DELETEALLITEMS") // delete all Grid items

      if pgConnect()// conects to the server
        // if pgDatabaseConnect(pcDataBase)
            aTable:=pgSelectQuery(oServer, cQuery) // executes the query
        // endIf
      endif

      if len(aTable) > 0 // add the items into the Grid element
         for nPos:=1 to len(aTable)
             doMethod("Main", "Grid_1", "ADDITEM", aTable[nPos])
         next
      endif

return nil
file postgreSQL.Prg

Code: Select all

#include <hmg.ch>
#include "postgres.ch"

/*
** author = {
** Crocciari, Daniel
** hmglights.wordpress.com
** danielcrocciari@gmail.com
** }
**
** @modificated by: Cisino Junior. 
** @email: cisinojr@gmail.com
**
** @description: Originaly those functions were created by Daniel Crocciari, 
** to allow the use of MySQL/MariaDB as a backend database for softwares writen in harbour(hmg).
** All modifications made by me(Cisino Junior), was made to allow the use of PostgreSQL insted of MySQL.
** That is why I had changed the name of all functions.
** And I only needed to do some tyne changes thanks to Daniel's knowledge.
**
** LICENSE:
** You are allowed to use all those functions, but all the modifications
** that you may want to do is by your own. I'll not provide any directed support.
** I only ask that you do not modify or remove this description of the header.
**
** Acknowledgment:
** All thanks to Daniel for all the content about HMG that he made/make
** for his youtube channel and blog. Thank you for share your knowledge.
**
** @youtube: https://www.youtube.com/user/hmglights
** @blog: http://hmglights.ceuazul.net/
**
** Version: 0.1.0
*/

/*
** @description: This function tries to connect into the PostgreSQL server.
** @param: cHostname
*/
FUNCTION pgConnect(cHostname ,cDatabase, cUsername, cPassword)

	IF( cHostname == nil)
		cHostname := pcHostName
	ENDIF
	
	IF( cDatabase == nil)
		cDatabase := pcDataBase
	ENDIF

	IF( cUsername == nil)
		cUsername := pcUserName
	ENDIF

	IF( cPassword == nil)
		cPassword := pcPassWord
	ENDIF

	oServer := TPQServer():New( cHostname , cDatabase , cUsername , cPassword )
	
	IF( oServer:NETERR() )

	    msgStop("Erro tentando conectar ao servidor de banco de dados { "+oServer:errorMsg()+" }", "Banco de Dados")

	    RETURN .f.
		
	ENDIF

RETURN .t.

/*
** @description: This function destroy the connection with PostgreSQL server.
** @return: .T.
*/
FUNCTION pgDestroy()

         IF(oServer!=nil)
            oServer:DESTROY()
            oServer := nil
	ENDIF

RETURN .t.

/*
** @description: This function tries to connect with de Database PostgreSQL server.
** @param: cDataBase -> database name
** @return: bSucesso
**
** NOTE: I left this function, but I just changed its name because I could not 
**       figure out how to list the PostgreSQL databases.
**
/*
FUNCTION pgDatabaseConnect(cDataBase)
LOCAL aDatabaseList := {}, bSucesso := .f.

    IF( cDataBase == nil )
    	cDataBase := LOWER(pcDatabase)
    ENDIF

    IF( oServer == nil )
    	MSGSTOP("Servidor não conectado!","SQL")
    	RETURN bSucesso
    ENDIF
    
    aDatabaseList := oServer:LISTDBS()
    
    IF( oServer:NETERR() )
    	MSGSTOP("Erro verificando a lista de banco de dados: " + oServer:errorMsg(), "SQL" )
    	RETURN bSucesso
    ENDIF
    
    IF( ASCAN( aDatabaseList, cDataBase ) == 0 )
    	MSGSTOP("Banco de dados " + cDataBase + " não existe!", "SQL")
    	RETURN bSucesso
    ENDIF
    
    oServer:SELECTDB( cDataBase )
    
    IF( oServer:NETERR() )
        MSGSTOP("Erro conectado banco de dados " + cDataBase + ": " + oServer:errorMsg(), "SQL" )
        RETURN bSucesso
    ENDIF
    
    bSucesso := .t.

RETURN bSucesso
*/

/*
** @description: This function tries to execute a Select query and 
**               return an Array with the persisted data.
** @param: oServer -> server object connection ;
**         cQuery -> the query to execute
** @return: aTable
*/
FUNCTION pgSelectQuery(oServer, cQuery)
LOCAL oQuery, oRow, i, j, aTable := {}, aRow := {}

	IF( oServer == nil )
	    MSGSTOP(oServer:errorMsg())
	    return aTable
	ENDIF

	IF( cQuery == nil )
		cQuery := ""
	ENDIF
	
	IF( EMPTY( ALLTRIM(cQuery) ) )
	    MSGSTOP("Erro: query vazia","SQL")
	    RETURN aTable
	ENDIF
	
	oQuery := oServer:QUERY( cQuery )
	
	IF( oQuery:NETERR() )
	    MSGSTOP("Erro: " + oQuery:errorMsg(), "SQL" )
	ELSE
	    IF( oQuery:LASTREC() > 0 )
	    	ASIZE(aTable,0)
                FOR i := 1 TO oQuery:LASTREC()
                    ASIZE(aRow,0)
	    	    oRow := oQuery:GETROW(i)
                    FOR j := 1 TO oQuery:FCOUNT()
	     	    	IF( VALTYPE(oRow:FIELDGET(j))=="C" )
	     	    	    campo := ALLTRIM(oRow:FIELDGET(j))
	     	    	ELSE
	     	    	    campo := oRow:FIELDGET(j)
	    	    	ENDIF
	    	     	AADD( aRow, campo)
                    NEXT j
	    	    AADD( aTable, ACLONE(aRow) )
	    	NEXT i
            ENDIF
	ENDIF
	
RETURN aTable

/*
** @author: Cisino Junior
** @email: cisinojr@gmail.com
** @description: This function tries to execute querys that return TRUE OR FALSE, insert querys,
**               delete querys and update querys and send a response for a element of your choice(like labels).
** @param: cForm        -> the form that has the element to display the message
**         cElementUI   -> the element that the message will be displayed
**         cMessage     -> the error message to display to the user
**         cQuery       -> the query to execute into the database
** @return: .t. (if the query was executed correctly)
*/
function executeQuery(cForm, cElementUI, cMessage, cQuery)
local oQuery

      oServer:StartTransaction()
      
      oQuery := oServer:Query( cQuery )

      if( oQuery:NETERR() )
          setProperty(cForm, cElementUI, "value", cMessage +" " + oQuery:errorMsg())
          setProperty(cForm, cElementUI, "FONTCOLOR",{255, 0, 0})
          return .f.
      endif

      oServer:Commit()
      oQuery:destroy()

return .t.


/*
** @author: Cisino Junior
** @email: cisinojr@gmail.com
** @description: This functions uses the pgSelectQuery function to search for duplicated 
**               registers into a table.
** @param: cForm        -> the form that has the element to display the message
**         cElementUI   -> the element that the message will be displayed
**         cMessage     -> the error message to display to the user
**         cQuery       -> the validation query, ex: cQuery:=select description from produto where description = '"+searchString+"';"
** @vars: aTable : Array -> array that store the data resulted from the query execution
** @return: True if aTable is empty. False if the table is not empty.
**
*/
function validData(cForm, cElementUI, cMessage, cQuery)
local aTable:={}
      aTable := pgSelectQuery(oServer, cQuery)

      if !empty(aTable)
         setProperty(cForm, cElementUI, "value", cMessage)
         setProperty(cForm, cElementUI, "FONTCOLOR",{255, 0, 0})
         return .f.
      endif

return .t.


file postgres.ch

Code: Select all

/*
 * PostgreSQL RDBMS low level (client api) interface code.
 *
 * Copyright 2003 Rodrigo Moreno rodrigo_moreno@yahoo.com
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2, or (at your option)
 * any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this software; see the file COPYING.txt.  If not, write to
 * the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
 * Boston, MA 02111-1307 USA (or visit the web site https://www.gnu.org/).
 *
 * As a special exception, the Harbour Project gives permission for
 * additional uses of the text contained in its release of Harbour.
 *
 * The exception is that, if you link the Harbour libraries with other
 * files to produce an executable, this does not by itself cause the
 * resulting executable to be covered by the GNU General Public License.
 * Your use of that executable is in no way restricted on account of
 * linking the Harbour library code into it.
 *
 * This exception does not however invalidate any other reasons why
 * the executable file might be covered by the GNU General Public License.
 *
 * This exception applies only to the code released by the Harbour
 * Project under the name Harbour.  If you copy code from other
 * Harbour Project or Free Software Foundation releases into a copy of
 * Harbour, as the General Public License permits, the exception does
 * not apply to the code that you add in this way.  To avoid misleading
 * anyone as to the status of such modified files, you must delete
 * this exception notice from them.
 *
 * If you write modifications of your own for Harbour, it is your choice
 * whether to permit this exception to apply to your modifications.
 * If you do not wish that, delete this exception notice.
 *
 */

#ifndef HBPOSTGRES_CH_
#define HBPOSTGRES_CH_

#define CONNECTION_OK                   0
#define CONNECTION_BAD                  1
#define CONNECTION_STARTED              2
#define CONNECTION_MADE                 3
#define CONNECTION_AWAITING_RESPONSE    4
#define CONNECTION_AUTH_OK              5
#define CONNECTION_SETENV               6
#define CONNECTION_SSL_STARTUP          7
#define CONNECTION_NEEDED               8

#define PGRES_EMPTY_QUERY               0
#define PGRES_COMMAND_OK                1
#define PGRES_TUPLES_OK                 2
#define PGRES_COPY_OUT                  3
#define PGRES_COPY_IN                   4
#define PGRES_BAD_RESPONSE              5
#define PGRES_NONFATAL_ERROR            6
#define PGRES_FATAL_ERROR               7

#define PQTRANS_IDLE                    0
#define PQTRANS_ACTIVE                  1
#define PQTRANS_INTRANS                 2
#define PQTRANS_INERROR                 3
#define PQTRANS_UNKNOWN                 4

/* PQmetadata() positions for array returned */
#define HBPG_META_FIELDNAME             1
#define HBPG_META_FIELDTYPE             2
#define HBPG_META_FIELDLEN              3
#define HBPG_META_FIELDDEC              4
#define HBPG_META_TABLE                 5
#define HBPG_META_TABLECOL              6
#define HBPG_META_LEN_                  6

#endif

Attachments
POSTGRESQL.1A.zip
(1.57 MiB) Downloaded 230 times
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br
User avatar
AUGE_OHR
Posts: 2060
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany

Re: how postgreSQL connect

Post by AUGE_OHR »

THX a lot for your Demo. i will learn from.
have fun
Jimmy
User avatar
AUGE_OHR
Posts: 2060
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany

Re: how postgreSQL connect

Post by AUGE_OHR »

hi,

you Sample work perfect but when used other Table it crash :o

Code: Select all

function update_grid()
local cQuery, nLimit
local nPos
// chage this line
*   cQuery:="SELECT * FROM test order by code"
// Table exist 
    cQuery:="SELECT * FROM aaa4"
HMG Errorlog File

------------------------------------
Date:08/31/19 Time: 07:53:48
Error: HMG 3.4.4 Stable (32 bits)
Grid.AddItem (nRowIndex = 1): Item size mismatch. Program Terminated
Called from _ADDGRIDROW(673)
Called from _ADDITEM(906)
Called from DOMETHOD(9265)
Called from UPDATE_GRID(117)
Called from MAIN(43)
------------------------------------
have fun
Jimmy
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

Re: how postgreSQL connect

Post by jairpinho »

AUGE_OHR wrote: Sat Aug 31, 2019 6:02 am hi,

you Sample work perfect but when used other Table it crash :o

Code: Select all

function update_grid()
local cQuery, nLimit
local nPos
// chage this line
*   cQuery:="SELECT * FROM test order by code"
// Table exist 
    cQuery:="SELECT * FROM aaa4"
HMG Errorlog File

------------------------------------
Date:08/31/19 Time: 07:53:48
Error: HMG 3.4.4 Stable (32 bits)
Grid.AddItem (nRowIndex = 1): Item size mismatch. Program Terminated
Called from _ADDGRIDROW(673)
Called from _ADDITEM(906)
Called from DOMETHOD(9265)
Called from UPDATE_GRID(117)
Called from MAIN(43)
------------------------------------
hello, now you will start working and understanding about tables and grid in hmg:

SELECT * FROM test order by code == (*) will fetch all fields from table
If your aaa4 (7) table contains more fields than the test (5) table will give an error because when you use * in a select the pgSelectQuery () command returns all fields, so you have to change the properties in the grid headers. and widths
In this grid is defined as the table test 5 fields
Headers = {'Code', 'Name', 'Salary', 'Creation', 'Description'}
Widths = {100,100,100,100,100}
Your aaa4 table has the same number of grid fields if more error occurred
  You can select only the fields you need in this case in the example grid is prepared to receive up to 5 table fields if you need to modify the properties of the grid.
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

Re: how postgreSQL connect

Post by jairpinho »

AUGE_OHR wrote: Sat Aug 31, 2019 6:02 am hi,

you Sample work perfect but when used other Table it crash :o

Code: Select all

function update_grid()
local cQuery, nLimit
local nPos
// chage this line
*   cQuery:="SELECT * FROM test order by code"
// Table exist 
    cQuery:="SELECT * FROM aaa4"
HMG Errorlog File

------------------------------------
Date:08/31/19 Time: 07:53:48
Error: HMG 3.4.4 Stable (32 bits)
Grid.AddItem (nRowIndex = 1): Item size mismatch. Program Terminated
Called from _ADDGRIDROW(673)
Called from _ADDITEM(906)
Called from DOMETHOD(9265)
Called from UPDATE_GRID(117)
Called from MAIN(43)
------------------------------------
I use in my array forms inside the source code where I manipulate without opening the form, so if I add a field in the table I add in the array, here is an example
Attachments
POSTGRESQL.1B.zip
(1.58 MiB) Downloaded 206 times
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br
User avatar
AUGE_OHR
Posts: 2060
Joined: Sun Aug 25, 2019 3:12 pm
DBs Used: DBF, PostgreSQL, MySQL, SQLite
Location: Hamburg, Germany

Re: how postgreSQL connect

Post by AUGE_OHR »

hi,

thx again for your help an demo code.

i saw that

Code: Select all

        WIDTHS { 100,300,100,150,400}
        HEADERS { 'Code' , 'Name' , 'Salary' , 'Creation' , 'Description' }
are in *.FMG

there are a lot of PRIVATE in your new code and it is still fix on given Table.
i want to use GRID for any Table so i have to modify your code as i did in my 1st Sample.

to get WIDTHS and Header i use this code with Xbase++

Code: Select all

METHOD PGSql:dbStruct( cTable )
LOCAL aStruct    := {}
LOCAL oStruct, i, iMax
LOCAL cField, cType, nLen, nDec
LOCAL cWhere     := ""
LOCAL cData_type := ""

   ::exec( "SELECT column_name, data_type, character_maximum_length, numeric_precision, numeric_scale " + ;
           "FROM information_schema.columns WHERE table_name='" + cTable + "'" + ;
           "ORDER BY ordinal_position" )

   oStruct := ::result
   iMax := oStruct:rows                                   
   FOR i = 1 TO iMax                                       

      cField := oStruct:GetValue( i - 1, 0 )
      cData_type := UPPER( ALLTRIM( oStruct:GetValue( i - 1, 1 ) ) )

      DO CASE
         CASE cData_type = "BOOLEAN"
            cType := "L"
            nLen := 1
            nDec := 0

         CASE cData_type = "CHARACTER"
            cType := "C"
            nLen := oStruct:FieldGet( i - 1, 2 )
            nDec := 0

         CASE cData_type = "DATE"
            cType := "D"
            nLen := 10
            nDec := 0

         CASE cData_type = "TEXT"
*           cType := "M"
            cType := "C"
            nLen := oStruct:FieldGet( i - 1, 2 )
            nDec := 0

         CASE cData_type = "NUMERIC"
            cType := "N"
            nLen := oStruct:FieldGet( i - 1, 3 )
            nDec := oStruct:FieldGet( i - 1, 4 )

         CASE cData_type = "INTEGER"
            cType := "N"
            nLen := oStruct:FieldGet( i - 1, 3 )
            nDec := oStruct:FieldGet( i - 1, 4 )

         CASE cData_type = "SERIAL"
            cType := "N"
            nLen := oStruct:FieldGet( i - 1, 3 )
            nDec := oStruct:FieldGet( i - 1, 4 )

            // byteA -> HEX -> Bitmap
            //
         CASE cData_type = "BYTEA"
            cType := "B"
            nLen := 10
            nDec := 0

            // large Object
            //
         CASE cData_type = "OID"
            cType := "O"
            nLen := 10
            nDec := 0

         OTHERWISE
            MSGBOX( "unknown type " + cData_type )
            cType := "C"
            nLen := 1
            nDec := 0
      ENDCASE

      AADD( aStruct, { cField, cType, nLen, nDec } )
   NEXT

RETURN ( aStruct )
have fun
Jimmy
Post Reply