MYSQL via ADO

BenyHapper · August 14, 2007

Eu estou tendo problemas na hora para fazer o update de um registo quando eu nÃ£o modifico nada, mas se eu modificar algo funciona sem problemas, um outro problema Ã© quando eu recupero o conteÃºdo de um campo da base de dados, conseqÃ¼entemente a variavel Ã© dimencionada para o tamanho do conteÃºdo e nÃ£o para o tamanho definido na base de dados, o que atrapalha editar a informaÃ§Ã£o no GET segue abaixo do PRG:

OBS: NÃ£o desejo usar LIBS de Terceiros, pois apesar de muito boas tratam de usar os acessos ao banco conforme o Clipper/Fivewin utilizam e eu por ter conhecimentos em desenvolvimento WEB, prefiro trabalhar diretamente com as instruÃ§Ãµes em SQL e ADO. O problema que enfrento Ã© simplesmente com a integraÃ§Ã£o destes conhecimentos com a liguagem Xharbour + Fivewin + ADO.

AgradeÃ§o a ajuda.

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)))

Bene

E-mail :benyhapper@hotmail.com

FWH705 / Xhar9971 / @Say

MSN: benyhapper@hotmail.com

SÃ£o Paulo/SP

BenyHapper · August 14, 2007