Jump to content
Fivewin Brasil

exemplo de uso da contrib HBPGSQL para Postgresql

Wellington Vieira

By Wellington Vieira
in Programação

 Share

Recommended Posts

Wellington Vieira Rookie

Wellington Vieira

Posted
Posted (edited) 
#include "fivewin.ch"
#include "xbrowse.ch"


#require "hbpgsql"

#include "DbStruct.ch"

REQUEST DBFNTX
REQUEST DBFCDX
REQUEST DBFFPT
REQUEST DBFDBT


static aData,oBrwData


PROCEDURE Main( cHost, cDatabase, cUser, cPass )

   LOCAL oServer, oQuery, oRow, i, x, aTables, aStruct

   LOCAL cQuery, lAppend, cFileOrig, cFileDEST
   
   cHost     := "localhost"
   cDatabase := "mydata"
   cUser     := "postgres"
   cPass     := "mypass"
   
   
   oServer := TPQServer():New( cHost, cDatabase, cUser, cPass )

   IF oServer:NetErr()
      ?"Query 1 : "+oServer:ErrorMsg()
      QUIT
   ENDIF

   oServer:SetVerbosity( 2 )
   oServer:traceon( "simple.log" )
   
   
   cFileORIG := "customer.dbf"
   cFileDEST := "mst_customer"  
   lAppend := .t.
   if !oServer:TableExists( cFileDEST )
   
             ConvertToSQL(cFileORIG,cFileDEST,lAppend,oServer)
             
             CreateIndexSQL('mst_customer', 'recno_key', 'recno_key',oServer)
             
             CreateIndexSQL('mst_customer', 'first', 'first',oServer)
             
             CreateIndexSQL('mst_customer', 'city', 'city',oServer)
             
             CreateIndexSQL('mst_customer', 'first_city', 'first, city',oServer)
     
     endif
       
     BrowseData(oServer)
   
   oServer:Destroy()
   
return nil  
   
   

static function ConvertToSQL(cFileORIG,cFileDEST,lAppend,oServer)

   local cComm, apCode, cOut
   local nErr, nPos
   LOCAL vEmp := {}
   Local nCnn, s,oSql
   local aReturn := {}
   local aReturnX := {}
   cFileORIG  := lower(alltrim(cFileORIG))
   cFileDEST  := lower(alltrim(cFileDEST))

   define dialog oDlgStock from 1,1 to 40,400 pixel style nOR( WS_CAPTION ) title "Tunggu Sebentar"
   activate dialog oDlgStock centered nowait
   
   oDlgStock:cTitle := cFileDEST
   SysRefresh()

   dbCloseAll()
   cSql := "DROP TABLE IF EXISTS "+cFileDEST
   oQuery := oServer:Execute( cSql )
     
      IF oQuery:NetErr()
              ? "Query 2 : "+oQuery:ErrorMsg()
              QUIT
           ENDIF
           
       oQuery:Destroy()
     
   

   dbCloseAll()
   use (cFileORIG) new shared alias orig

 
 
  aStruct := orig->(DbStruct())
 
  cField := ""
 
  for i := 1 to len(aStruct)
     cFieldName := alltrim(aStruct[i][DBS_NAME])
     if lower(cFieldName) == "index"
         cFieldName := "XINDEX"
     endif
     cType      := aStruct[i][DBS_TYPE]
     cLen       := alltrim(Str( aStruct[i][DBS_LEN ],  3 ))
     cDec       := alltrim(Str( aStruct[i][DBS_DEC ],  3 ))
     cOke := ""
     
     
     if cType == "C"
        cOke := "  "+cFieldName+" CHAR ("+cLen+") , "
     endif
     
     if cType == "M"
        cOke := "  "+cFieldName+" CHAR (150) , "
     endif
     
     if cType == "N"
        if val(cDec) == 0
           cOke := "  "+cFieldName+" NUMERIC ( "+cLen+", 0) , "
        else
           cOke := "  "+cFieldName+" NUMERIC ( "+cLen+", 2) , "
        endif
     endif
     
     if cType == "D"
        cOke := "  "+cFieldName+" DATE, "
     endif
     
     if cType == "L"
        cOke := "  "+cFieldName+" BOOLEAN, "
     endif
     cField += cOke
  next
 
  cField := upper(cField)

   cSql := "CREATE TABLE "+cFileDEST+" ( recno_key    serial primary key, "
   
   
   cSQL += cField
   cSQL += " edited_date DATE, edited_time CHAR(8) "
   
   
     cSQL += " );"
   
   
   
        ?cSql
   
   
   
        oQuery := oServer:Execute( cSql )
       
     
      IF oQuery:NetErr()
              ? "Query 3 : "+oQuery:ErrorMsg()
              QUIT
           ENDIF
           
       oQuery:Destroy()
        
   
   

if lAppend
   dbCloseAll()
   use (cFileORIG) new shared alias orig
   
           nMulai  := 0
           nPersen := 0
           nRecord := orig->(lastrec())
           
           
           orig->(dbGotop())  
           do while !orig->(eof())
           
              ++nMulai
              nPersen := ( nMulai / nRecord ) * 100
              oDlgStock:cTitle := cFileDEST +" "+str(nMulai,12)+"/"+str(nRecord,12)+"="+str(nPersen,12)+"%"
              SysRefresh()
              cFieldJalan := alltrim(orig->(FieldName(1)))
              cDatanya := orig->&cFieldJalan
              if valtype(cDatanya) == "N"
                 cDatanya := alltrim(str(cDatanya,14,2))
              else
                 cDatanya := "'"+upper(alltrim(cDatanya))+"'"
              endif
             
              cSQL := "INSERT INTO "+alltrim(cFileDEST)+" ( "+cFieldJalan+" ) VALUES (  "+cDatanya+"  )"
             
              oQuery := oServer:Execute( cSql )
     
              IF oQuery:NetErr()
                 ? "Query 4 : "+oQuery:ErrorMsg()
                 QUIT
              ENDIF
           
         oQuery:Destroy()
         
         for x := 2 to orig->(fcount())
                     cFieldJalan := alltrim(orig->(FieldName(x)))
                     cDatanya := orig->&cFieldJalan
                     
                          if valtype(cDatanya) == "N"
                         cDatanya := alltrim(str(cDatanya,14,2))
                      endif
                     
                      if valtype(cDatanya) == "D"
                         cTahun   := strzero(year(cDatanya),4)
                         cBulan   := strzero(month(cDatanya),2)
                         cTgl     := strzero(day(cDatanya),2)
                         cDatanya := cTahun+"-"+cBulan+"-"+cTgl
                      endif
                     
                      if valtype(cDatanya) == "L"
                         if cDatanya
                            cDatanya := "1"
                         else
                            cDatanya := "0"  
                         endif
                      endif
                         
                      if valtype(cDatanya) == "C"
                         cChar := ""
                         for xx := 1 to len(cDatanya)
                            cOke := subs(cDatanya,xx,1)
                            if cOke == "'"
                               cOke := ""
                            endif
                            cChar += cOke
                         next
                         cDatanya := "'"+upper(alltrim(cChar))+"'"
                      endif
                     
                      if valtype(cDatanya) == "M"
                         cDatanya := "'"+upper(alltrim(cDatanya))+"'"
                      endif
                     
                        cSql :=  "UPDATE "+alltrim(cFileDEST)+" SET "+cFieldJalan+" = "+cDatanya+" WHERE recno_key = "+alltrim(str(nMulai,12))
                       
                              oQuery := oServer:Execute( cSql )
                 
                      IF oQuery:NetErr()
                             ? "Query 4a : "+oQuery:ErrorMsg()
                             QUIT
                          ENDIF
             
                oQuery:Destroy()
                 
                 next            
           
             orig->(dbSkip())
             
           enddo
         
       dbCloseAll()
   

endif
**********************

       
oDlgStock:End()

return nil   


static function BrowseData(oServer)

local oDlgRekening,lAmbil := .f.

oQuery := oServer:Query( "SELECT * from mst_customer order by recno_key" )

aData := {}

DO WHILE ! oQuery:Eof()
   aField := {}
   for nField := 1 to oQuery:FCount()
      cData := oQuery:FieldGet( nField )
      if valtype(cData) == "C"
         if len(cData) == 0
            cData := spac(oQuery:FieldLen( nField ))
         endif
      endif
     
      aadd(aField,cData  )
   next
   
   aadd(aData,aField )
   
   oQuery:Skip()
   
enddo

oQuery:Destroy()


   define dialog oDlgRekening from 1,1 to 500,900 TITLE "My Data" pixel
   
   @ 4,1 XBROWSE oBrwData size 440,200 pixel of oDlgRekening ARRAY aData ON DBLCLICK ( lAmbil := .t. ,oDlgRekening:End() )
   
   
   oBrwData:nMarqueeStyle       := MARQSTYLE_HIGHLCELL
   oBrwData:nColDividerStyle    := LINESTYLE_BLACK
   oBrwData:nRowDividerStyle    := LINESTYLE_BLACK
   oBrwData:lColDividerComplete := .t.
   
   

   oCol := oBrwData:AddCol()
   oCol:cHeader       := "Record"
   oCol:bEditValue    := { || aData[oBrwData:nArrayAt][1] }
   oCol:cEditPicture  := "@!"
   oCol:nWidth        := 100
   oCol:nHeadStrAlign := 2        
   oCol:bClrStd       := {|| { CLR_BLACK,nRGB(194,233,235) } }        
   
   
   oCol := oBrwData:AddCol()
   oCol:cHeader       := "First Name"
   oCol:bEditValue    := { || aData[oBrwData:nArrayAt][2] }
   oCol:cEditPicture  := "@!"
   oCol:nWidth        := 150
   oCol:nHeadStrAlign := 2        
   oCol:bClrStd       := {|| { CLR_BLACK,nRGB(194,233,235) } }        
   oCol:nEditType      := 1
   oCol:bOnPostEdit   := { |o, varInput, nLastKey  | iif( nLastKey == 13, ;
      ( aData[oBrwData:nArrayAt][2] := varInput                                                                  , ;
        SimpanData( "first","'"+alltrim(varInput)+"'",oServer),oBrwData:goRight() ) ;
        , .t. ) }
   
   
   
   
   oCol := oBrwData:AddCol()
   oCol:cHeader       := "Last Name"
   oCol:bEditValue    := { || aData[oBrwData:nArrayAt][3] }
   oCol:cEditPicture  := "@!"
   oCol:nWidth        := 150
   oCol:nHeadStrAlign := 2        
   oCol:bClrStd       := {|| { CLR_BLACK,nRGB(194,233,235) } }        
   oCol:bOnPostEdit   := {|o, v, n| iif( n != VK_ESCAPE, ( aData[oBrwData:nArrayAt][3] := v , SimpanData( aData[oBrwData:nArrayAt][1] ,"last","'"+alltrim(v)+"'",oServer)   ), ),oBrwData:Refresh() }  
   oCol:nEditType      := 1
   
   
   oCol := oBrwData:AddCol()
   oCol:cHeader       := "Street"
   oCol:bEditValue    := { || aData[oBrwData:nArrayAt][4] }
   oCol:cEditPicture  := "@!"
   oCol:nWidth        := 300
   oCol:nHeadStrAlign := 2        
   oCol:bClrStd       := {|| { CLR_BLACK,nRGB(194,233,235) } }        
   oCol:bOnPostEdit   := {|o, v, n| iif( n != VK_ESCAPE, ( aData[oBrwData:nArrayAt][4] := v , SimpanData( aData[oBrwData:nArrayAt][1] ,"street","'"+alltrim(v)+"'",oServer)   ), ),oBrwData:Refresh() }  
   oCol:nEditType      := 1
   
   
   
   oBrwData:nHeaderHeight := 45
     
   oBrwData:nRowHeight := 30
   
   oBrwData:l2007 := .t.
   
   oBrwData:nFreeze := 2
   
   oBrwData:nHeaderLines := 2
     
   oBrwData:CreateFromCode()
   
   nRow := 220
   nCol := 10
   
   @nRow,nCol button "Top" size 30,14 of oDlgRekening pixel action ( oBrwData:Gotop(),oBrwData:SetFocus(),oBrwData:Refresh() )
   nCol += 35
   @nRow,nCol button "Bottom" size 30,14 of oDlgRekening pixel action ( oBrwData:GoBottom(),oBrwData:SetFocus(),oBrwData:Refresh() )
   nCol += 35
   @nRow,nCol button "Find First Name" size 60,14 of oDlgRekening pixel action ( CariNamaDepan(oServer) )
   nCol += 65
   @nRow,nCol button "Sort by First Name" size 60,14 of oDlgRekening pixel action ( NamaDepan(oServer) )
   nCol += 65
   @nRow,nCol button "Add 1 Record" size 60,14 of oDlgRekening pixel action ( LastRecord(oServer) )
   nCol += 65
   @nRow,nCol button "Add 500 Record" size 60,14 of oDlgRekening pixel action ( AddData("customer.dbf","mst_customer" ,oServer) )
   nCol += 65
   @nRow,nCol button "Delete Record" size 60,14 of oDlgRekening pixel action ( HapusData(aData[oBrwData:nArrayAt][1],oServer) )
   nCol += 65
   @nRow,nCol button "Exit" size 30,14 of oDlgRekening pixel action ( oDlgRekening:End() )


   ACTIVATE DIALOG oDlgRekening ON INIT ( oBrwData:gotop(),oBrwData:SetFocus(), oBrwData:Refresh() )
   

return lAmbil


static function CariNamaDepan(oServer)

local cCari := spac(20)

if MsgGet("Find First name","First Name",@cCari)
   
    cCari := upper(alltrim(cCari))
   
        oQuery := oServer:Query( "SELECT * from mst_customer where first like '%"+cCari+"%' order by recno_key" )
       
               
        aData := {}
       
        DO WHILE ! oQuery:Eof()
           aField := {}
           for nField := 1 to oQuery:FCount()
              cData := oQuery:FieldGet( nField )
              if valtype(cData) == "C"
                 if len(cData) == 0
                    cData := spac(oQuery:FieldLen( nField ))
                 endif
              endif
              aadd(aField,cData  )
           next
           
           aadd(aData,aField )
           
           oQuery:Skip()
           
        enddo
       
        oBrwData:SetArray(aData)
       
        oQuery:Refresh()
        oBrwData:Gotop()
        oBrwData:Refresh()

endif

return nil

static function SimpanData(cField,cDatanya,oServer)

nRecordSimpan := aData[oBrwData:nArrayAt][1]

cFileDEST := "mst_customer"

cSql :=  "UPDATE "+alltrim(cFileDEST)+" SET "+cField+" = "+cDatanya+" WHERE recno_key = "+alltrim(str(nRecordSimpan,12))

   
   oQuery := oServer:Execute( cSql )
     
      IF oQuery:NetErr()
              ? "Query 22a : "+oQuery:ErrorMsg()
              QUIT
           ENDIF
           
       oQuery:Destroy()
       

return nil


static function HapusData(nRecordSimpan,oServer)

if !MsgYesNo("Are you sure ?")
   return nil
endif

cFileDEST := "mst_customer"

cSql :=  "DELETE from "+alltrim(cFileDEST)+" WHERE recno_key = "+alltrim(str(nRecordSimpan,12))
   
       oQuery := oServer:Execute( cSql )
     
       IF oQuery:NetErr()
              ? "Query 22a : "+oQuery:ErrorMsg()
              QUIT
           ENDIF
           
       oQuery:Destroy()
       
       oQuery := oServer:Query( "SELECT * from mst_customer order by recno_key" )

       IF oQuery:NetErr()
              ? "Query 22a : "+oQuery:ErrorMsg()
              QUIT
           ENDIF

aData := {}

DO WHILE ! oQuery:Eof()
   aField := {}
   for nField := 1 to oQuery:FCount()
      cData := oQuery:FieldGet( nField )
      if valtype(cData) == "C"
         if len(cData) == 0
            cData := spac(oQuery:FieldLen( nField ))
         endif
      endif
      aadd(aField,cData  )
   next
   
   aadd(aData,aField )
   
   oQuery:Skip()
   
enddo

oQuery:Destroy()

oBrwData:SetArray(aData)
oBrwData:Gotop()
oBrwData:Refresh()


return nil


static function NamaDepan(oServer)

oQuery := oServer:Query( "SELECT * from mst_customer order by first" )

aData := {}

DO WHILE ! oQuery:Eof()
   aField := {}
   for nField := 1 to oQuery:FCount()
      cData := oQuery:FieldGet( nField )
      if valtype(cData) == "C"
         if len(cData) == 0
            cData := spac(oQuery:FieldLen( nField ))
         endif
      endif
      aadd(aField,cData  )
   next
   
   aadd(aData,aField )
   
   oQuery:Skip()
   
enddo

oBrwData:SetArray(aData)
oBrwData:Gotop()
oBrwData:Refresh()

return nil




static function LastRecord(oServer)
local nRecord := 0
cSql :=  "SELECT recno_key "
cSql +=  " FROM mst_customer "
cSql +=  " ORDER BY recno_key DESC "
cSql +=  " LIMIT 1 "

       oQuery := oServer:Execute( cSql )
     
       IF oQuery:NetErr()
              ? "Query 22a : "+oQuery:ErrorMsg()
              QUIT
           ENDIF
   
    nRecord := oQuery:FieldGet( 1 )

oQuery:Destroy()

oQuery:Refresh()
   
++nRecord

cFileDEST   := "mst_customer"
cFieldJalan := "first"
cDatanya    := "'ZZZZ'"

          cSQL := "INSERT INTO "+alltrim(cFileDEST)+" ( "+cFieldJalan+" ) VALUES (  "+cDatanya+"  )"
             
              oQuery := oServer:Execute( cSql )
     
          IF oQuery:NetErr()
                 ? "Query 4 : "+oQuery:ErrorMsg()
                 QUIT
              ENDIF
           
         oQuery:Destroy()
         
         oServer:Commit()

oQuery := oServer:Query( "SELECT * from mst_customer order by recno_key DESC LIMIT 1" )

aData := {}

DO WHILE ! oQuery:Eof()
   aField := {}
   for nField := 1 to oQuery:FCount()
      cData := oQuery:FieldGet( nField )
      if valtype(cData) == "C"
         if len(cData) == 0
            cData := spac(oQuery:FieldLen( nField ))
         endif
      endif
      aadd(aField,cData  )
   next
   
   aadd(aData,aField )
   
   oQuery:Skip()
   
enddo

oQuery:Destroy()

oBrwData:SetArray(aData)

oBrwData:GoBottom()
oBrwData:Refresh()
   
return nil



/*

CREATE OR REPLACE FUNCTION create_index(table_name text, index_name text, column_name text) RETURNS void AS $$
declare
   l_count integer;
begin
  select count(*)
     into l_count
  from pg_indexes
  where schemaname = 'public'
    and tablename = lower(table_name)
    and indexname = lower(index_name);

  if l_count = 0 then
     execute 'create index ' || index_name || ' on ' || table_name || '(' || column_name || ')';
  end if;
end;
$$ LANGUAGE plpgsql;

usage: select create_index('my_table', 'my_index_name', 'id');

*/

static function CreateIndexSQL(cTabelName,cIndexName,cFieldName,oServer)
       cQuery := "SELECT create_index('"+lower(cTabelName)+"', '"+lower(cTabelName)+"_"+lower(cIndexName)+"', '"+lower(cFieldName)+"');"
       oQuery := oServer:Query( cQuery )
           IF oQuery:NetErr()
              ? "4"+oQuery:ErrorMsg()
           ENDIF
       oQuery:Destroy()
return nil      


****
static function AddData(cFileORIG,cFileDEST,oServer)

if MsgYesNo("Add 500 Record ?")

                cSql := "SELECT recno_key "+;
                "FROM mst_customer "+;
                "ORDER BY recno_key DESC "+;
                "LIMIT 1"
               
                  oQuery := oServer:Execute( cSql )
     
          IF oQuery:NetErr()
                 ? "Query 4 : "+oQuery:ErrorMsg()
                 QUIT
              ENDIF
             
              nLastRecord := oQuery:FieldGet( 1 )
             
              //?str(cData,12)
              oQuery:Destroy()
             
             

       dbCloseAll()
       
       define dialog oDlgStock from 1,1 to 40,400 pixel style nOR( WS_CAPTION ) title "Tunggu Sebentar"
       activate dialog oDlgStock centered nowait
   
   
       use (cFileORIG) new shared alias orig

           nMulai  := 0
           nPersen := 0
           nRecord := orig->(lastrec())
           
           
           orig->(dbGotop())  
           do while !orig->(eof())
           
              ++nMulai
              nPersen := ( nMulai / nRecord ) * 100
              oDlgStock:cTitle := cFileDEST +" "+str(nMulai+nLastRecord,12)+"/"+str(nRecord+nLastRecord,12)+"="+str(nPersen,12)+"%"
              SysRefresh()
              cFieldJalan := alltrim(orig->(FieldName(1)))
              cDatanya := orig->&cFieldJalan
              if valtype(cDatanya) == "N"
                 cDatanya := alltrim(str(cDatanya,14,2))
              else
                 cDatanya := "'"+upper(alltrim(cDatanya))+"'"
              endif
             
              cSQL := "INSERT INTO "+alltrim(cFileDEST)+" ( "+cFieldJalan+" ) VALUES (  "+cDatanya+"  )"
             
              oQuery := oServer:Execute( cSql )
     
          IF oQuery:NetErr()
                 ? "Query 4 : "+oQuery:ErrorMsg()
                 QUIT
              ENDIF
           
         oQuery:Destroy()
         
         for x := 2 to orig->(fcount())
                     cFieldJalan := alltrim(orig->(FieldName(x)))
                     cDatanya := orig->&cFieldJalan
                     
                          if valtype(cDatanya) == "N"
                         cDatanya := alltrim(str(cDatanya,14,2))
                      endif
                     
                      if valtype(cDatanya) == "D"
                         cTahun   := strzero(year(cDatanya),4)
                         cBulan   := strzero(month(cDatanya),2)
                         cTgl     := strzero(day(cDatanya),2)
                         cDatanya := cTahun+"-"+cBulan+"-"+cTgl
                      endif
                     
                      if valtype(cDatanya) == "L"
                         if cDatanya
                            cDatanya := "1"
                         else
                            cDatanya := "0"  
                         endif
                      endif
                         
                      if valtype(cDatanya) == "C"
                         cChar := ""
                         for xx := 1 to len(cDatanya)
                            cOke := subs(cDatanya,xx,1)
                            if cOke == "'"
                               cOke := ""
                            endif
                            cChar += cOke
                         next
                         cDatanya := "'"+upper(alltrim(cChar))+"'"
                      endif
                     
                      if valtype(cDatanya) == "M"
                         cDatanya := "'"+upper(alltrim(cDatanya))+"'"
                      endif
                     
                        cSql :=  "UPDATE "+alltrim(cFileDEST)+" SET "+cFieldJalan+" = "+cDatanya+" WHERE recno_key = "+alltrim(str(nMulai+nLastRecord,12))
                       
                              oQuery := oServer:Execute( cSql )
                 
                      IF oQuery:NetErr()
                             ? "Query 4a : "+oQuery:ErrorMsg()
                             QUIT
                          ENDIF
             
                oQuery:Destroy()
                 
                 next            
           
             orig->(dbSkip())
             
           enddo
         
       dbCloseAll()
       oDlgStock:End()
       
   
   
   
    oQuery := oServer:Query( "SELECT * from mst_customer order by recno_key" )
       
    aData := {}
       
        DO WHILE ! oQuery:Eof()
           aField := {}
           for nField := 1 to oQuery:FCount()
              cData := oQuery:FieldGet( nField )
              if valtype(cData) == "C"
                 if len(cData) == 0
                    cData := spac(oQuery:FieldLen( nField ))
                 endif
              endif
              aadd(aField,cData  )
           next
           
           aadd(aData,aField )
           
           oQuery:Skip()
           
        enddo
       
        oBrwData:SetArray(aData)
       
        oQuery:Refresh()
        oBrwData:Gotop()
        oBrwData:Refresh()
       
       
       
       
endif
   
return nil





// O mesmo exemplo acima só que navegando na própria linha do postgresql.......................................................................................
#include "fivewin.ch"

REQUEST DBFCDX

static cPassword := <yourpassword>

//----------------------------------------------------------------------------//

function Main()

   local oServer, cSql, oQry
   local aStates, n, a

   oServer   := TPQServer():New( "localhost", "fwh", "postgres", cPassword )
   if oServer:NetErr()
      ? oServer:ErrorMsg()
      QUIT
   endif
   ? "connected"

   ? "import customer"
   if oServer:TableExists( "customer" )
      PQExec( oserver:pdb, "DROP TABLE customer" )
   endif
   ? PG_ImportFromDBF( oServer, "c:\fwh\samples\customer.dbf" )
   ? oserver:tableexists( "customer" )

   oQry  := oServer:Query( "select * from customer order by recno" )
   oQry:SetKey()
   xbrowser oQry:aKeys
   ? oqry:nlastrec
   ? "Browse"
   BrowsePG( oQry )

   oServer:Destroy()

return nil

//----------------------------------------------------------------------------//

static function BrowsePG( oQry )

   local oWnd, oBrw, oFont

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE WINDOW oWnd
   oWnd:SetFont( oFont )

   @ 0,0 XBROWSE oBrw OF oWnd CELL LINES NOBORDER FASTEDIT

   SetXbrPG( oBrw, oQry )

   WITH OBJECT oBrw
      :nEditTypes    := EDIT_GET
      :CreateFromCode()
   END

   oWnd:oClient := oBrw

   oWnd:nWidth    := 800
   oWnd:nHeight   := 600

   ACTIVATE WINDOW oWnd CENTERED
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

static function SetXbrPG( oBrw, oQry )

   local oCol, n

   WITH OBJECT oBrw
      :oDbf          := oQry
      :bGoTop        := {|| oBrw:oDbf:GoTo( 1 ) }
      :bGoBottom     := {|| oBrw:oDbf:GoTo( oBrw:oDbf:nLastRec ) }
      :bSkip         := {| n | If( oBrw:oDbf:Skip( @n ), n, 0 ) }
      :bBof          := {|| oBrw:oDbf:Bof() }
      :bEof          := {|| oBrw:oDbf:Eof() }
      :bBookMark     := { |u| If( PCount() == 0, oBrw:oDbf:RecNo(), oBrw:oDbf:GoTo( u ) ) }
      :bKeyNo        := { |n| If( n == nil, oBrw:oDbf:RecNo(), oBrw:oDbf:GoTo( n ) ) }
      :bKeyCount     := { || oBrw:oDbf:nLastRec }
      :nDataType     := DATATYPE_ODBF
      :bOnRowLeave   := { || nil }
   END

   for n := 1 to oQry:FCount()
      WITH OBJECT oBrw:AddCol()
         :Cargo        := n

         :cHeader       := oQry:FieldName( n )
         :cExpr         := oQry:FieldName( n )
         :bOnPostEdit   := { |o,x,n| If( n == VK_ESCAPE,, PGSaveData( o, x ) ) }
         :cDataType     := oQry:FieldType( n )
         :nDataLen      := oQry:FieldLen( n )
         :nDataDec      := oQry:FieldDec( n )
         if :cDataType == 'C'
            :bEditValue    := { |x,o| PadR( oQry:FieldGet( o:Cargo ), o:nDataLen ) }
         else
            :bEditValue    := { |x,o| oQry:FieldGet( o:Cargo ) }
         endif
         :bClrEdit      := { || { CLR_BLACK, CLR_YELLOW } }
      END
   next

return nil

//----------------------------------------------------------------------------//

static function PGSaveData( oCol, xValue )

   local oBrw     := oCol:oBrw
   local oQry     := oBrw:oDbf
   local cWhere, cSql, u, nRec

   if ValType( xValue ) == 'C'
      xValue   := RTrim( xValue )
   endif

   u           := oQry:FieldGet( oQry:FieldPos( oQry:aKeys[ 1 ] ) )
   cWhere      := oQry:aKeys[ 1 ] + " = " + FW_ValToSQL( u )
   cSql        := "UPDATE " + oQry:TableName + " SET " + oQry:FieldName( oCol:Cargo )
   cSql        += " = " + FW_ValToSQL( xValue )
   cSql        += " WHERE " + cWhere

   u           := PQExec( oQry:pDB, cSql )
   u           := nil
   nRec        := oQry:RecNo()
   oQry:Refresh()
   oQry:GoTo( nRec )
   oBrw:Refresh()

return nil

//----------------------------------------------------------------------------//

function PG_ImportFromDBF( oCn, cDbf, cTable )

   local cSql, res

   DEFAULT cTable    := cFileNoExt( cDBF )
   cTable            := Lower( cTable )

   if oCn:TableExists( cTable )
      return .f.
   endif

   USE ( cDbf ) NEW SHARED READONLY ALIAS SRC
   if !USED()
      return .f.
   endif

   cSql     := PG_CreateTableSQL( cTable, SRC->( DBSTRUCT() ) )
   res      := PQExec( oCn:pDB, cSql )
   res      := nil

   SRC->( PG_AppendFromAlias( oCn, cTable ) )
   CLOSE SRC

return .T.

//----------------------------------------------------------------------------//

function PG_CreateTableSQL( cTable, aStruct )

   local cSql, aCol

   if AScan( aStruct, { |a| a[ 2 ] == '+' } ) == 0
      AIns( aStruct, 1, { "recno", '+', 6, 0 }, .t. )
   endif

   cSql  := "CREATE TABLE " + Lower( cTable ) + " (" + CRLF
   for each aCol in aStruct
      cSql  += aCol[ 1 ] + " "
      cSql  += HB_DeCode( aCol[ 2 ], "+", "SERIAL PRIMARY KEY", "C", "VARCHAR(", "D", "DATE", "L", "BOOLEAN", "M", "TEXT", ;
               "N", "NUMERIC(", "T", "TIMESTAMP", "=", "TIMESTAMP" )
      if aCol[ 2 ] $ "CN"
         cSql  += cValToChar( aCol[ 3 ] )
         if aCol[ 2 ] == "N" .and. aCol[ 4 ] > 0
            cSql  += "," + cValToChar( aCol[ 4 ] )
         endif
         cSql  += ")"
      endif
      cSql  += "," + CRLF
   next
   cSql  := Left( cSql, Len( cSql ) - 3 )
   cSql  += " )"

return cSql

//----------------------------------------------------------------------------//

function PG_AppendFromAlias( oCn, cTable )

   local nBatchSize     := 100
   local aCols          := ArrTranspose( DBSTRUCT() )[ 1 ]
   local n, u, bLine, cLine, cCols
   local cSql

   bLine    := ""
   for n := 1 to Len( aCols )
      if n > 1
         bLine    += ","
      endif
      if FIELDTYPE( n ) == 'C'
         bLine    += "TRIM(" + aCols[ n ] + ")"
      else
         bLine    += aCols[ n ]
      endif
   next
   bLine    := &( "{ || { " + bLine + " } }" )
   cCols    := FW_ArrayAsList( aCols )

   n     := 0
   do while !Eof()
      cLine    := PG_ValToSQL( Eval( bLine ) )
      if Empty( cSql )
         cSql  := "INSERT INTO " + cTable + " (" + cCols + ") VALUES "
      else
         cSql  += ", "
      endif
      cSql     += cLine
      n++
      if n >= nBatchSize
         u  := PQExec( oCn:pDB, cSql )
         cSql  := nil
         n     := 0
      endif
      DBSKIP( 1 )
   enddo
   if ! Empty( cSql )
      u  := PQExec( oCn:pDB, cSql )
      cSql  := nil
   endif

return nil

//----------------------------------------------------------------------------//

function PG_ValToSQL( uVal )

   local cVal

   if ValType( uVal ) == 'A'
      AEval( uVal, { |u,i| uVal[ i ] := PG_ValToSql( u ) } )
      cVal  := FW_ArrayAsList( uVal )
      cVal  := "( " + cVal + " )"
      return cVal

   elseif ValType( uVal ) == 'L'
      return If( uVal, "TRUE", "FALSE" )
   endif

return FW_ValToSQL( uVal )

//----------------------------------------------------------------------------//

 

Edited by Wellington Vieira
alteração do título
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
 Share
Go to topic listing
×
×
  • Create New...