Page 1 of 1

MYSQL via ADO

Posted: Tue Aug 14, 2007 1:35 pm
by pauloperes
I am having problems in the hour to make update of a register when I do not modify nothing, but if I modify something functions without problems, another problem is when I recoup the content of a field of the data base, therefore it dimenciona variavel for the size of the content and not for the size defined in the data base, follows below the PRG

#include "FiveWin.ch"
#include "TSButton.ch"
#include "Report.ch"
#include "InKey.ch"
#include "winapi.ch"
#Include "Struct.Ch"

FUNCTION Main()

PUBLIC oFontSay
PUBLIC oFontSayB
PUBLIC oFontGet
PUBLIC oFontBut
PUBLIC oFontTit
PUBLIC oFontChk
PUBLIC oFontLis
PUBLIC oBrushTela
PUBLIC USER
PUBLIC ACESSO
PUBLIC CODIGO
PUBLIC CONEXAO
PUBLIC TABELA

MFONTES()

SET TALK OFF
SET DATE FRENCH
SET BELL OFF
SET STAT OFF
SET SCORE OFF
SET WRAP ON
SET CURSOR OFF
SET DELETED ON
SET EPOCH TO 2000
SET DECIMAL TO 2
SET CENTURY ON
SET EXCLUSIVE OFF
SETHANDLECOUNT(250)
CONTINUAR=.F.
ORDLIST=0

DEFINE BRUSH oBrush COLOR nRGB(255,255,255)
DEFINE ICON oIcon FILE "WORKER.ICO"

DEFINE WINDOW oWnd FROM 0,40 to 40,120 TITLE "Administração de Clientes e Contatos";
BRUSH oBrush;
ICON oIcon;

@ 65,30 SBUTTON PIXELS PROMPT "&Entrar" TEXT ON_CENTER NOBOX FILE "OFF.FIG","ON.FIG" ADJUST FONT oFontbut OF oWnd SIZE 40,12 ACTION (ListMunicipios())

SET MESSAGE OF oWnd to "EXATUS.NET" CENTERED
oItem := TMsgItem():New( oWnd:oMsgBar, dtoc(date()), 80,oFontsay,"R/W",,.T. )
oItem := TMsgItem():New( oWnd:oMsgBar, Alltrim(user), 80,oFontsay,"R/W",,.T. )
oItem := TMsgItem():New( oWnd:oMsgBar, "Versão: 11/05/2007", 140,oFontsay,"R/W",,.T. )

WNDCENTER( oWnd:hWnd )
ACTIVATE WINDOW oWnd
SysRefresh()
RETURN NIL

PROCEDURE MFONTES()
DEFINE FONT oFontsay NAME "Verdana" SIZE 0,-11
DEFINE FONT oFontsayB NAME "Verdana" SIZE 0,-10 BOLD
DEFINE FONT oFontGet NAME "Verdana" SIZE 0,-9
DEFINE FONT oFontBut NAME "Verdana" SIZE 0,-11
DEFINE FONT oFontTit NAME "Verdana" SIZE 0,-11
DEFINE FONT oFontChk NAME "Verdana" SIZE 0,-11
DEFINE FONT oFontLis NAME "Verdana" SIZE 0,-9
DEFINE BRUSH oBrushTela COLOR nRGB(236,233,216)
RETURN

PROCEDURE AbreConexao()
Try
Conexao:=CreateObject("ADODB.Connection")
Conexao:Open("driver=MySQL ODBC 3.51 Driver;server=localhost;uid=Teste;pwd=teste;database=clientsexatus")
Catch e
MsgStop("Operacao: "+E:operation+" - "+"Descrição: "+E:Description+chr(10))
End
Try
Tabela:=CreateObject("ADODB.Recordset")
Tabela:ActiveConnection = Conexao
Catch e
MsgStop("Operacao: "+E:operation+" - "+"Descrição: "+E:Description+chr(10))
End
RETURN

PROCEDURE FechaConexao()
Try
IF Tabela:State()<>0
Tabela:Close()
ENDIF
Catch e
MsgStop("Operacao: "+E:operation+" - "+"Descrição: "+E:Description+chr(10))
End
Try
IF Conexao:State()<>0
Conexao:Close()
ENDIF
Catch e
MsgStop("Operacao: "+E:operation+" - "+"Descrição: "+E:Description+chr(10))
End
RETURN

FUNCTION Browse(aq, cTitle, cListName, bNew, bModify, bDelete, bSearch, bList, bEdit )
LOCAL oDlg, oFont, oBrush
LOCAL btnNew, btnModify, btnDelete, btnSearch, btnList, btnEnd
LOCAL n,BORDEM
LOCAL CONTINUA:=.T.
LOCAL oReg,nReg
PUBLIC AQS
PUBLIC oLbx
AQS=AQ
MFONTES()

DEFAULT cTitle := "Browse", cListName := "Registros",;
bDelete := {|| oLbx:Setfocus()},;
bList := {|| oLbx:Setfocus()},BEDIT:=.F.

DEFINE DIALOG oDlg FROM 1, 5 TO 27, 79 TITLE cTitle FONT oFontTit BRUSH oBrushTela STYLE WS_CAPTION

@ 0.2,1.4 SAY "Total de Registros: " FONT oFontsay COLOR CLR_BLACK,nRGB(248,248,248) SIZE 58,7
@ 0.2,11 SAY oReg VAR nReg FONT oFontsay COLOR CLR_BLACK,nRGB(248,248,248) SIZE 216,7

IF AQ=1
nReg=STR(Tabela:RecordCount(),10)
oReg:Refresh()
Tabela:MoveLast()
FOR I=1 TO 16
IF !Tabela:BOF()
Tabela:Move(-1)
ENDIF
NEXT I
@ 1,1 LISTBOX oLbx FIELDS STR(Tabela:Fields("codigo"):Value,10),Tabela:Fields("uf"):Value,Tabela:Fields("municipio"):Value ;
HEADERS "Código","UF","Municipio" SIZE 275,137 OF oDlg

oLbx:aColSizes :={60,100,200}
oLbx:aJustify :={.T.,.F.,.F.}
ENDIF
oLbx:nLineStyle :=3
oLbx:nClrBackFocus:=RGB(227,255,104)
oLbx:nClrForeFocus:=RGB(0,0,0)
oLbx:nClrPane :={|| IIF((oLbx:cAlias)->(ordlist())%2==0,nRGB(240,255,255),nRGB(230,230,230))}
oLbx:bLogicLen :={|| Tabela:RecordCount}
oLbx:bGoTop :={|| Tabela:MoveFirst()}
oLbx:bGoBottom :={|| Tabela:MoveLast()}
oLbx:bSkip :={| nSkip | Skipper(Tabela,nSkip)}

@ 156,027 SBUTTON PIXELS TEXT ON_BOTTOM FONT oFontBut COLOR 0,nRGB(255,255,255) PROMPT "&Inclui" btnNew FILE "INCLUI.FIG" SIZE 35,35 OF oDlg
@ 156,067 SBUTTON PIXELS TEXT ON_BOTTOM FONT oFontbut COLOR 0,nRGB(255,255,255) PROMPT "&Altera" btnModify FILE "ALTERA.FIG" SIZE 35,35 OF oDlg
@ 156,107 SBUTTON PIXELS TEXT ON_BOTTOM FONT oFontbut COLOR 0,nRGB(255,255,255) PROMPT "&Exclui" btnDelete FILE "EXCLUI.FIG" SIZE 35,35 OF oDlg
@ 156,147 SBUTTON PIXELS TEXT ON_BOTTOM FONT oFontbut COLOR 0,nRGB(255,255,255) PROMPT "&Procura" btnSearch FILE "PROC.FIG" SIZE 35,35 OF oDlg
@ 156,187 SBUTTON PIXELS TEXT ON_BOTTOM FONT oFontbut COLOR 0,nRGB(255,255,255) PROMPT "I&mprimir" btnList FILE "IMPRIME.FIG" SIZE 35,35 OF oDlg
@ 156,227 SBUTTON PIXELS TEXT ON_BOTTOM FONT oFontbut COLOR 0,nRGB(255,255,255) PROMPT "&Sair" btnEnd FILE "SAIR1.FIG" SIZE 35,35 OF oDlg

btnNew:bAction = If(bNew != nil,{|| Eval(bNew) ,oLbx:Refresh(),oLbx:SetFocus()},)
btnModify:bAction = If(bModify != nil,{|| Eval(bModify),oLbx:Refresh(),oLbx:SetFocus()},)
btnDelete:bAction = If(bDelete != nil,{|| Eval(bDelete),oLbx:Refresh(),oLbx:SetFocus()},)
btnSearch:bAction = If(bSearch != nil,{|| Eval(bSearch),oLbx:Refresh(),oLbx:SetFocus()},)
btnList:bAction = If(bList != nil,{|| Eval(bList) ,oLbx:Refresh(),oLbx:SetFocus()},)
btnEnd:bAction = {||oDlg:End(),SysRefresh()}

odlg:lhelpicon:=.f.
ACTIVATE DIALOG oDlg CENTERED
Conexao:Execute("Commit")
FechaConexao()
RETURN NIL

STATIC FUNCTION SKIPPER(oRsx,nSkip)
LOCAL nRec := oRsx:AbsolutePosition
oRsx:Move(nSkip)
IF oRsx:EOF; oRsx:MovePrevious(); ENDIF
IF oRsx:BOF; oRsx:MoveNext(); ENDIF
RETURN(oRsx:AbsolutePosition() - nRec)

FUNCTION ORDLIST()
IF ORDLIST=0
ORDLIST=1
RETURN 1
ELSE
ORDLIST=0
RETURN 2
ENDIF
RETURN 1

FUNCTION ListMunicipios()
MFONTES()
AbreConexao()
Tabela:CursorType = 1
Tabela:CursorLocation = 3
Tabela:LockType = 2
MSGRUN("Aguarde - Carregando Dados","Aguarde...",{||Tabela:Open("SELECT codigo,uf,municipio FROM uf USE INDEX(uf) ORDER BY uf")})
Browse(1,"Lista de Municipios","Lista de Municipios",{||NewMunicipios("I")},{||NewMunicipios("A")},{||EraseMunicipios(Tabela:Fields("codigo"):Value)})
SysRefresh()
RETURN

FUNCTION NewMunicipios(tipo)
PUBLIC codigo,uf,municipio

IF tipo="A"
codigo=Tabela:Fields("codigo"):Value
uf=ESPANTES(Tabela:Fields("uf"):Value,Tabela:Fields("municipio"):DefinedSize)
municipio:=ESPANTES(Tabela:Fields("municipio"):Value,Tabela:Fields("municipio"):DefinedSize)
ENDIF
IF tipo="I"
codigo=0
uf=SPACE(2)
municipio=SPACE(30)
ENDIF

DEFINE DIALOG oDlg FROM 1,1 TO 12,39 TITLE "Cadastro de UF/Municipio" FONT oFontTit BRUSH oBrushTela STYLE WS_CAPTION
@ 0.35,0.35 SAY " " RAISED BOX COLOR CLR_BLACK,nRGB(248,248,248) SIZE 148,50 OF oDlg
@ 0.9,2 SAY "Código" FONT oFontsay COLOR CLR_BLACK,nRGB(248,248,248) SIZE 25,10
@ 1.8,2 SAY "UF" FONT oFontsay COLOR CLR_BLACK,nRGB(248,248,248) SIZE 25,10
@ 2.7,2 SAY "Municipio" FONT oFontsay COLOR CLR_BLACK,nRGB(248,248,248) SIZE 40,10

@ 1.0,5 GET codigo FONT oFontGet size 30,11
@ 2.0,5 GET uf FONT oFontGet size 20,11
@ 3.0,5 GET municipio FONT oFontGet size 100,11

@ 65,30 SBUTTON PIXELS PROMPT "&Confirma" TEXT ON_CENTER NOBOX FILE "OFF.FIG","ON.FIG" ADJUST FONT oFontbut OF oDlg SIZE 40,12 ACTION (SaveMunicipios(tipo),oLbx:Refresh(),oDlg:End()) DEFAULT
@ 65,85 SBUTTON PIXELS PROMPT "C&ancela" TEXT ON_CENTER NOBOX FILE "OFF.FIG","ON.FIG" ADJUST FONT oFontbut OF oDlg SIZE 40,12 ACTION (oDlg:End())
odlg:lhelpicon:=.f.
ACTIVATE DIALOG oDlg CENTERED
RETURN NIL

FUNCTION SaveMunicipios(tipo)
IF tipo="A"
Tabela:Fields("uf"):Value:=ALLTRIM(uf)
Tabela:Fields("municipio"):Value:=ALLTRIM(municipio)
Tabela:Update()
ENDIF
IF tipo="I"
Tabela:AddNew()
Tabela:Fields("uf"):Value:=ALLTRIM(uf)
Tabela:Fields("municipio"):Value:=ALLTRIM(municipio)
Tabela:Update()
ENDIF
RETURN NIL

FUNCTION EraseMunicipios(cod)
IF MSGYESNO( "Deseja Excluir este Registro ?","AVISO DE EXCLUSÃO")
Tabela:Delete()
Tabela:UpdateBatch()
Tabela:MoveNext()
IF Tabela:EOF; Tabela:MovePrevious(); ENDIF
ENDIF
RETURN NIL

procedure espantes(VARIAVEL,TAMANHO)
RETURN ALLTRIM(VARIAVEL)+SPACE(TAMANHO-LEN(ALLTRIM(VARIAVEL)))



What it can be wrong?

Posted: Fri Aug 17, 2007 8:09 pm
by Daniel Segura
Olá Paulo,

MSGRUN("Aguarde - Carregando Dados","Aguarde...",{||Tabela:Open("SELECT codigo,uf,municipio FROM uf USE INDEX(uf) ORDER BY uf",Conexao,2,3)})

Daniel Segura
ciacpd@terra.com.br