PostgresSQL from core-master\contrib\hbpgsql
Posted: Fri May 26, 2017 9:53 am
I tested with PostgreSQL versi 9:
Best Regards
Fafi
Code: Select all
#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
Fafi