Welcome to Database Developer
Home
Global-Sample....
(DOWNLOAD SOURCE)
/***************************************************/ /** SAMPLE SQL Connection **************************/ /***Database SQLite ********************************/ #include "Tdsn.ch" FUNCTION OpenConn( cDSN ) LOCAL oDsn Local cpath := CURDRIVE()+":\"+CURDIR()+"\SQLITE.DAT" // For SQLite Database Local nTypeDsn := 2 // System DSN oDsn := TMyDsn():New( cDSN ) IF oDsn:nStatus == TDSN_NO_ERROR oDsn:Del() ENDIF oDsn:Close() // Create( cDSN, cDescription, cODBCDriver, cServer, cDataBase, cUser, nDsnType, cDriver, nPort ) #ifdef SQL_LITE oDsn:Create( ; cDSN, ; "XXX Connection", ; "SQLite ODBC Driver", "", ; cpath) #Elseif MYSQL oDsn:Create( ; cDSN, ; "XXX Connection", ; "MySQL ODBC 3.51 Driver",; "xxx.x.x.xxx","parcel" ) // IP - xxx.x.x.xxx or 'localhost' #Elseif SQL_SERVER oDsn:Create( ; cDSN, ; "xxx Connection", ; "SQL Server", ; "Server", ; "Database" ) #EndIf oDsn:Close() RETURN NIL FUNCTION CloseConn( cDSN ) LOCAL oDsn oDsn := TMyDsn():New( cDSN ) IF oDsn:nStatus == TDSN_NO_ERROR oDsn:Del() ENDIF RETURN NIL /****************************************************************/ /** SAMPLE List data and Search Data ****************************/ /****************************************************************/ #include "MYHEADER.CH" FUNCTION CUSTOMER() LOCAL cQRY Local oBtnAdd, oBtnEdt, oBtnDel, oBtnSch, oBtnMbl, oBtnCancel, oBtnPrint PRIV oDlg, oDAT, oBRW CURSORWAIT() oODBC:EXECUTE( datatemp('zTmp') ) cQryX0 := "delete from ztmp;" oODBC:EXECUTE( cQryX0 ) cQryX0 := "insert into ztmp (custid, tipex) select distinct custid, 'AKTIF' from workorder" oODBC:EXECUTE( cQryX0 ) cQRY := "select a.*, "+; "b.tipex "+; "from customer a "+; "left join zTmp b on a.custid = b.custid" oDAT := TDBODBC():NEW( cQry, oODBC ) oDAT :AddPrimaryKey("CUSTID") oDAT :SETORDER("custnm, alamat") DEFINE DIALOG oDlg RESOURCE "LISTCUST" TITLE "Customer List" REDEFINE BROWSE oBrw OF oDlg ID 201 UPDATE ON DBLCLICK oBtnEdt:click() oBrw:SEToOBJ( oDat ) ADD COLUMN TO oBRW DATA oBRW:nAT PICTURE "999,999" HEADER "No." ALIGN DT_RIGHT ADD COLUMN TO oBRW DATA oDAT:CUSTNM PICTURE "@S30" HEADER "Customer" ADD COLUMN TO oBRW DATA oDAT:ALAMAT PICTURE "@S40" HEADER "Alamat" ADD COLUMN TO oBRW DATA oDAT:TEL_MBL PICTURE "@S20" HEADER "HP" ADD COLUMN TO oBRW DATA oDAT:TEL_RMH PICTURE "@S20" HEADER "Rumah-Phn" ADD COLUMN TO oBRW DATA oDAT:TEL_KTR PICTURE "@S20" HEADER "Kantor-Phn" ADD COLUMN TO oBrw DATA oDat:tipex HEADER "Status" SIZE 050 oBrw:Bkeydown := { |nKey| ; if( nkey==ASC("M"), oBtnMbl:click(), NIL ), ; if( nkey==VK_INSERT, oBtnAdd:click(), NIL ), ; if( nkey==VK_RETURN, oBtnEdt:click(), NIL ), ; if( nkey==VK_DELETE, oBtnDel:click(), NIL ), ; if( nkey==VK_ESCAPE, oDLG:END(), NIL ) } oBrw:SetColor({ 1,2,3,4 },{ CLR_BLACK ,{ || if( oBrw:nAT % 2 = 0,CLR_WHITE, CLR_LZSOFT )}, CLR_BLACK, CLR_LZHEADER }) cItem := "Customer" cCariMsg := "Customer" cCariDat := oDAT:CUSTNM REDEFINE COMBOBOX oCbx VAR cItem ITEMS { "Customer", "Alamat", "HP", "Rumah-Phn", "Kantor-Phn", "Status" } ; ID 110 OF oDlg ; ON CHANGE ( URUTDATA( oCbx:nAt ) ) REDEFINE BUTTON oBtnCancel ID 999 OF oDlg ACTION oDlg:END() REDEFINE BUTTON oBtnAdd ID 901 OF oDlg ACTION SHOWCUSTOMER( oDAT, .T. ) REDEFINE BUTTON oBtnEdt ID 902 OF oDlg ACTION SHOWCUSTOMER( oDAT ) REDEFINE BUTTON oBtnDel ID 903 OF oDlg ACTION HAPUS( oDAT ) REDEFINE BUTTON oBtnSch ID 905 OF oDlg ACTION ( CARIDATA(), oBRW:SETFOCUS(.T.) ) oDlg:SETCONTROL( oBRW ) oDLG:lHELPICON := .F. ACTIVATE DIALOG oDlg CENTER VALID .T. oDAT:END() RELE ALL LIKE * RETURN NIL STATIC FUNCTION URUTDATA( nPlh ) // { "Customer", "Alamat", "HP", "Rumah-Phn", "Kantor-Phn", "Status" } LOCAL nAT := oBRW:nAT DO CASE CASE nPLH = 1 cCariMsg := "Customer" cCariDat := oDat:Custnm cVar := ALLTRIM(oDat:Custnm) oDat:SETORDER("Custnm, alamat") CASE nPLH = 2 cCariMsg := "Alamat" cCariDat := oDat:Alamat cVar := ALLTRIM(oDat:Alamat) oDat:SETORDER("Alamat, custnm") CASE nPLH = 3 cCariMsg := "HP" cCariDat := oDat:Tel_mbl cVar := ALLTRIM(oDat:Tel_mbl) oDat:SETORDER("Tel_mbl, custnm") CASE nPLH = 4 cCariMsg := "Rumah-Phn" cCariDat := oDat:Tel_rmh cVar := ALLTRIM(oDat:Tel_rmh) oDat:SETORDER("Tel_rmh, custnm") CASE nPLH = 5 cCariMsg := "Kantor-Phn" cCariDat := oDat:Tel_Ktr cVar := ALLTRIM(oDat:Tel_Ktr) oDat:SETORDER("Tel_Ktr, custnm") CASE nPLH = 6 cCariMsg := "Status" cCariDat := oDat:tipex cVar := ALLTRIM(oDat:tipex) oDat:SETORDER("tipex, custnm") ENDCASE IF oDat:SEEK( cVar ) nAT := oDat:Recno() oBRW:nAt := nAT ELSE oDat:GotoID( nAT ) ENDIF oBrw:oBugUp() oDlg:UPDATE() oBrw:Setfocus() RETURN NIL STATIC FUNCTION CARIDATA() LOCAL nAT := oBRW:nAT STATIC xxVar cCariDat := uValBlank( cCariDat ) IF MSGGET( "PENCARIAN DATA", cCariMsg, @cCariDat ) IF ! EMPTY( cCariDat ) xxVAR := cCariDat IF VALTYPE( cCariDat ) = "C" xxVAR := ALLTRIM(cCariDat) ENDIF CURSORWAIT() IF oDat:SEEK( xxVAR ) nAT := oDat:Recno() oBRW:nAt := nAT oBrw:oBugUp() oDlg:UPDATE() ELSE ALERT("Data "+cCariMsg+" = "+cValToChar( xxVAR )+" Tidak Ada !!!") oDat:GotoID( nAT ) ENDIF ENDIF ENDIF RETURN NIL STATIC FUNCTION HAPUS( oDAT ) Local nAt := oBrw:nAt if UPPER(oDat:Tipex) = "AKTIF" ALERT("Data ini AKTIF, tidak dapat dihapus !!!") RETURN NIL ENDIF IF MSGYESNO("Record ini akan dihapus ?") CURSORWAIT() oODBC:EXECUTE("DELETE FROM MOBIL WHERE CUSTID = "+ALLTRIM(STR(oDAT:CUSTID)) ) oDAT:DELETE() oDAT:Refresh() if nAt <> oDAT:Recno() .and. oDat:Reccount() > 0 if nAt > oDat:Recno() nAt := oDat:Reccount() endif oDat:GotoID( nAt ) oBRW:nAt := nAT endif oBrw:oBugUp() oDLG:UPDATE() ENDIF RETURN NIL FUNCTION SHOWCUSTOMER( oDAT, lADD ) LOCAL oFONT Local oBtnSave, oBtnCancel LOCAL oGET[6] local nAt := oBrw:nAT LOCAL cTMP LOCAL cSEL := SELECT() DEFAULT lADD := .F. IF lADD cTitle:= "NEW CUSTOMER" SCATNEWSQL( oDAT, 'o' ) ELSE cTitle:= "EDIT CUSTOMER" SCATTERSQL( oDAT, 'o' ) ENDIF priv oBrwMbl, oDlg1 SELECT 11 /******This Sample Using TdbOdbcDirect****************/ cQRY2 := "SELECT MOBILID, NOPOL, MODEL, TYPE, TAHUN, WARNA, NOMESIN, NORANGKA, CUSTID FROM MOBIL WHERE CUSTID = "+GENSQL(oCUSTID)+" ORDER BY NOPOL" oRsX := oODBC:QUERY( cQRY2 ) oRsX :OPEN() oRsX :PRIMARYKEYS("MOBILID") INDE ON FIELD_0002 TAG "NOPOL" oRsX :COMPLETE() /*****************************************************/ DEFINE FONT oFONT NAME GetSysFont() SIZE 0, -12 BOLD DEFINE DIALOG oDlg1 RESOURCE "CUSTOMER" TITLE cTITLE REDEFINE GET oGET[1] VAR oCUSTID PICTURE "999999999" ID 100 OF oDlg1 UPDATE oGET[1]:DISABLE() oGET[1]:SETFONT( oFONT ) REDEFINE GET oGET[2] VAR oCUSTNM PICTURE "@!" ID 101 OF oDlg1 UPDATE REDEFINE GET oGET[3] VAR oALAMAT PICTURE "@!" ID 102 OF oDlg1 UPDATE REDEFINE GET oGET[4] VAR oTEL_MBL ID 103 OF oDlg1 UPDATE REDEFINE GET oGET[5] VAR oTEL_RMH ID 104 OF oDlg1 UPDATE REDEFINE GET oGET[6] VAR oTEL_KTR ID 105 OF oDlg1 UPDATE REDEFINE BROWSE oBrwMbl OF oDlg1 ID 200 GRID ALIAS (oRsX:cAlias) UPDATE ADD COLUMN TO oBrwMbl DATA oRsX:FIELDGET(2) ; PICTURE "@!" ; EDITABLE MOVE DT_MOVE_NEXT ; VALID { |cVAR| IIF( EditBrow( 2, cVAR ), .T., .F. ) }; HEADER "No.Pol" SIZE 70 ADD COLUMN TO oBrwMbl DATA oRsX:FIELDGET(3) ; PICTURE "@!" ; VALID { |cVAR| IIF( EditBrow( 3, cVAR ), .T., .F. ) }; EDITABLE MOVE DT_MOVE_NEXT ; HEADER "Model" SIZE 111 ADD COLUMN TO oBrwMbl DATA oRsX:FIELDGET(4) ; EDITABLE MOVE DT_MOVE_NEXT ; VALID { |cVAR| IIF( EditBrow( 4, cVAR ), .T., .F. ) }; HEADER "Type" SIZE 111 ADD COLUMN TO oBrwMbl DATA oRsX:FIELDGET(5) ; EDITABLE MOVE DT_MOVE_NEXT ; VALID { |cVAR| IIF( EditBrow( 5, cVAR ), .T., .F. ) }; HEADER "Tahun" ADD COLUMN TO oBrwMbl DATA oRsX:FIELDGET(6) ; EDITABLE MOVE DT_MOVE_NEXT ; VALID { |cVAR| IIF( EditBrow( 6, cVAR ), .T., .F. ) }; HEADER "Warna" SIZE 100 ADD COLUMN TO oBrwMbl DATA oRsX:FIELDGET(7) ; EDITABLE MOVE DT_MOVE_NEXT ; VALID { |cVAR| IIF( EditBrow( 7, cVAR ), .T., .F. ) }; HEADER "No.Mesin" SIZE 100 ADD COLUMN TO oBrwMbl DATA oRsX:FIELDGET(8) ; EDITABLE MOVE DT_MOVE_NEXT ; VALID { |cVAR| IIF( EditBrow( 8, cVAR ), .T., .F. ) }; HEADER "No.Rangka" SIZE 100 oBrwMbl:SetAppendMode( .T. ) // oBrwMbl:SetDeleteMode( .T., .T., {|| ( oRsx:Delete(), oRsx:Refresh(.t.), oBrwMbl:BugUp() ) } ) // ( lOnOff, lConfirm, bDelete) // oBrwMbl:Bkeydown := { |nKey| if( nkey==VK_ESCAPE, oDlg1:END(), NIL ) } oBrwMbl:Bkeydown := { |nKey| ; if( nkey==VK_DELETE, HAPUSDTL(oRsX), NIL ), ; if( nkey==VK_ESCAPE, oDLG1:END(), NIL ) } oBrwMbl:SetColor({ 1,2,3,4 },{ CLR_BLACK ,{ || if( oBrw:nAT % 2 = 0,CLR_WHITE, CLR_LZSOFT )}, CLR_BLACK, CLR_LZHEADER }) IF lADD oBrwMbl:DISABLE() ENDIF REDEFINE BUTTON oBtnSave ID 901 OF oDlg1 ACTION IIF( DATAOK( oGET ), ( EditMode( oDat, lADD ), oDlg1:END() ), ) REDEFINE BUTTON oBtnCancel ID 902 OF oDlg1 ACTION oDlg1:END() oDlg1:lHELPICON := .F. ACTIVATE DIALOG oDlg1 CENTER oRsX:END() UNGATHERSQL( oDAT, "o" ) RELE ALL LIKE * oDlg:Update() oBrw:SetFocus() RETURN NIL STATIC FUNCTION HAPUSDTL( oRsx ) LOCAL cXXX IF oRSX:MOBILID <> 0 cQry := "select count(*) from workorder where mobilid = "+GENSQL(oRsx:mobilID) IF oODBC:QueryData( cQry ) > 0 ALERT("Data ini AKTIF, tidak dapat dihapus !!!") RETURN NIL ENDIF ENDIF IF MSGYESNO("Record ini akan dihapus ?") CURSORWAIT() oRsx:DELETE() oRsx:REFRESH() oDLG1:UPDATE() ENDIF RETURN NIL STATIC FUNCTION EditBrow( nField, Cvar ) LOCAL lADD := .F. LOCAL nAT := ( oRsX:cALIAS )->( RECNO() ) local mVar := iif( nField=2, upper(alltrim(strtran(ALLTRIM(cVar)," ",""))), upper(alltrim(cVar)) ) local mStb := iif( nField=2, upper(alltrim(strtran(ALLTRIM(cVar)," ",""))), alltrim(oRsX:FIELDGET(2)) ) IF nFIELD = 2 IF ! CHKMOBIL( mVar ) RETURN(.F.) ENDIF ENDIF if EMPTY( oRsX:FIELDGET(1) ) oRsX:ADDNEW() oRsX:FIELDPUT(3, SPACE(40)) oRsX:FIELDPUT(4, SPACE(40)) oRsX:FIELDPUT(5, SPACE(4)) oRsX:FIELDPUT(6, SPACE(20)) oRsX:FIELDPUT(7, SPACE(20)) oRsX:FIELDPUT(8, SPACE(20)) oRsX:FIELDPUT(9, oDAT:CUSTID) lADD := .T. ELSE oRsX:EDIT() ENDIF oRsX:FIELDPUT(nField, mVar) oRsX:UPDATE() oRsX:REFRESH() IF lADD IF ( oRsX:CALIAS )->( DBSEEK( mStb ) ) nAt := oRsx:Recno() oBrwMbl:nAt := nAT oBrwMbl:UPSTABLE() ELSE ( oRsX:CALIAS )->( DBGOTO( nAT ) ) ENDIF oBrwMbl:BugUp() ENDIF oDLG1:UPDATE() RETURN(.T.) STATIC FUNCTION CHKMOBIL(uVAR) LOCAL lRET := .T., cQRY cQry := "select count(*) from mobil where nopol = "+GENSQL( uVar)+" and mobilid <> "+GENSQL(oRsX:MOBILID) IF oODBC:QueryData( cQry ) > 0 ALERT("NOMOR POLISI = "+uVar+" sudah ada!!!") lRet := .F. ENDIF RETURN lRET STATIC FUNCTION DATAOK( oGET ) LOCAL cQry Local lRET := .T., I := nCtr := 0 AEVAL( oGet, {|x| ++nCtr, if(((nCtr = 2 .OR. nCtr = 3) .AND. Empty(x:Varget()) .AND. lRET), ( lRET := .F., I := nCtr ), nil ) } ) IF !lRET ALERT("Field tidak boleh kosong !!! " ) oGET[I]:SETFOCUS() RETURN lRET ENDIF IF lRET cQry := "select count(*) from customer where "+; "custnm = "+GENSQL( alltrim( strtran( oGET[2]:VARGET()," "," " ) ) )+" "+; "and alamat = "+GENSQL( alltrim( strtran( oGET[4]:VARGET()," "," " ) ) )+" "+; "and custid <> "+GENSQL(oCUSTID) IF oODBC:QueryData( cQry ) > 0 ALERT("CUSTOMER sudah ada!!!") oGET[2]:SETFOCUS() lRet := .F. ENDIF ENDIF RETURN lRET STATIC FUNCTION EditMode( oDBF, lADD ) LOCAL lSTABILIZE := .F. LOCAL nAT := oBRW:nAT CURSORWAIT() oCUSTNM := alltrim( strtran( oCUSTNM," "," " ) ) oALAMAT := alltrim( strtran( oALAMAT," "," " ) ) oTEL_MBL := alltrim( strtran( ALLTRIM(oTEL_MBL)," ","" ) ) oTEL_RMH := alltrim( strtran( ALLTRIM(oTEL_RMH)," ","" ) ) oTEL_KTR := alltrim( strtran( ALLTRIM(oTEL_KTR)," ","" ) ) aflds := { "CUSTNM","ALAMAT","TEL_MBL","TEL_RMH","TEL_KTR" } avars := { oCUSTNM, oALAMAT, oTEL_MBL, oTEL_RMH, oTEL_KTR } if lADD sQueryX := genqry( 1, 'CUSTOMER', aflds, avars ) lADD := .T. ELSE sQueryX := genqry( 2, 'CUSTOMER', aflds, avars, { { "CUSTID", oCUSTID } } ) ENDIF oDBF:EXECUTE(sQueryX) oDBF:REFRESH() IF nAT > 0 if nAt <> oDbf:Recno() .and. oDbf:Reccount() > 0 if nAt > oDbf:Recno() nAt := oDbf:Reccount() endif oDbf:GotoID( nAt ) oBRW:nAt := nAT endif ENDIF oBRW:oBUGUP() oDLG:UPDATE() oBRW:SETFOCUS() RETURN