Conectar ao mysql na internet.

[clodoaldomonteiro]

Olá amigos.

Tenho uma aplicação em xHarbour e preciso que essa aplicação de conecte a um bando de dados mysql no meu servidor "187.33.3.199" e queria, se possível, a ajuda dos amigos para fazer essa conexão.

Toda vez que o sistema for aberto na máquina do usuário, ele vai mandar uma informação para o banco de dados com algumas informações do cliente.

Seria possível fazer isso?

Desde já agradeço a ajuda.

[Itamar M. Lins Jr.]

Ola!
Simples teste usando Harbour 2

Código: Selecionar todos

/*
 * $Id: test1.prg 14222 2010-03-24 09:33:58Z vszakats $
 */

#include "dbinfo.ch"
#include "error.ch"

#include "hbrddsql.ch"

REQUEST SDDMY, SQLMIX

ANNOUNCE RDDSYS

FIELD RESIDENTS

PROCEDURE main()
   RDDSETDEFAULT("SQLMIX")

   AEVAL(RDDLIST(), {|X| QOUT(X)})

   IF RDDINFO(RDDI_CONNECT, {"MYSQL", "localhost", "test",, "test"}) == 0
      ? "Unable connect to the server"
      RETURN
   ENDIF

   CreateTable()

   ? "Let's browse table (press any key)"
   INKEY(0)
   DBUSEAREA( .T.,, "SELECT * FROM country", "country" )
   Browse()

   ? "Let's browse table ordered by resident count (press any key)"
   INKEY(0)
   INDEX ON RESIDENTS TAG residents TO country
   Browse()

   DBCLOSEALL()
   RETURN

STATIC PROC CreateTable()
   ? RDDINFO(RDDI_EXECUTE, "DROP TABLE country")
   ? RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME char(50), RESIDENTS int(11))")
   ? RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('LTU', 'Lithuania', 3369600), ('USA', 'United States of America', 305397000), ('POR', 'Portugal', 10617600), ('POL', 'Poland', 38115967), ('AUS', 'Australia', 21446187), ('FRA', 'France', 64473140), ('RUS', 'Russia', 141900000)")
   RETURN

Ou usando ODBC do Harbour.

Código: Selecionar todos

/*
 * $Id: test2.prg 15406 2010-08-26 20:59:54Z snaiperis $
 */

#include "simpleio.ch"
#include "hbrddsql.ch"

REQUEST SQLMIX, SDDODBC

PROC main()
LOCAL nConnection, nI, aI

   RDDSETDEFAULT( "SQLMIX" )
   SET( _SET_DATEFORMAT, "yyyy-mm-dd" )
   nConnection := RDDINFO( RDDI_CONNECT, { "ODBC", "Server=localhost;Driver={MySQL ODBC 5.1 Driver};dsn=;User=test;database=test;" } )
   IF nConnection == 0
      ? "Unable connect to server", RDDINFO( RDDI_ERRORNO ), RDDINFO( RDDI_ERROR )
      RETURN
   ENDIF
   ? nConnection
   ? RDDINFO(RDDI_EXECUTE, "DROP TABLE country")
   ? RDDINFO(RDDI_EXECUTE, "CREATE TABLE country (CODE char(3), NAME char(50), RESIDENTS int(11))")
   ? RDDINFO(RDDI_EXECUTE, "INSERT INTO country values ('LTU', 'Lithuania', 3369600),('USA', 'United States of America', 305397000), ('POR', 'Portugal', 10617600), ('POL', 'Poland', 38115967), ('AUS', 'Australia', 21446187), ('FRA', 'France', 64473140), ('RUS', 'Russia', 141900000)")
   ? DBUSEAREA( .T.,, "SELECT * FROM country", "country" )
   ? "LASTREC:", LASTREC()
   DO WHILE ! EOF()
     aI := ARRAY( FCOUNT() )
     FOR nI := 1 TO FCOUNT()
       aI[nI] := FIELDGET( nI )
     NEXT
     ? RECNO(), HB_VALTOEXP( aI )
     DBSKIP()
   ENDDO
   ? "LASTREC:", LASTREC()
   DBCLOSEALL()
RETURN

Saudações,
Itamar M. Lins Jr.

[rochinha]

Amiguinho Itamar,

Tenho a versão 2.0.0 instalada e configurada, para usar SQLMIX terei de atualizá-la?

Ao compilar os teus exemplos obtive o erro abaixo:

Código: Selecionar todos

Compiling...
Borland C++ 5.5.1 for Win32 Copyright (c) 1993, 2000 Borland
sqlmix2.c:
* 
Linking...
Turbo Incremental Link 5.00 Copyright (c) 1997, 2000 Borland
Error: Unresolved external '_HB_FUN_SQLMIX' referenced from C:\ADMNE\DEVELOP\HARBOUR\TESTS\SQLMIX2.OBJ
Error: Unresolved external '_HB_FUN_SDDODBC' referenced from C:\ADMNE\DEVELOP\HARBOUR\TESTS\SQLMIX2.OBJ

Devo agregar alguma .LIB ou incluir algum código para passar por isto?

[clodoaldomonteiro]

Olá pessoal,
Fiz assim:

Entrei no meu servidor pelo Putty e acessei o mysql. Dentro dele dei permissão para que eu pudesse acessar a base de dados com o comando:

Código: Selecionar todos

mysql> grant all privileges on *.* to root identified by "senha";  

Depois pesquisando os exemplo da mysql.lib fiz a seguinte função:

Código: Selecionar todos

#include "dbstruct.ch"     // utilize as lib's mysql.lib e libmysql.lib (encontrei dentro da contrib)
Procedure RegAcesso
   local aFDatas := Array(11)
   local oServer, oQuery, oRow, i, cRow, aField
   local server := "simplesinformatica.com", user := "root", password := "s1mpl35db"
   local cDataBase:='simplesdb', cTable:='tb_acessos'

   oServer := TMySQLServer():New(server, user, password)
   if oServer:NetErr()
      Alert(oServer:Error())
   endif

   oServer:SelectDB(cDataBase)

   oTable := oServer:Query("SELECT * FROM " + cTable + " LIMIT 1")

   aFDatas[1] := NomeExe               //sistema utilizado
   aFDatas[2] := mEmpAut               //empresa autorizada
   aFDatas[3] := nEmp                  //empresa processada usuaria
   aFDatas[4] := M->PS_CGC             //cnpj da empresa processada
   aFDatas[5] := ''                    //data de inicio de uso do sistema
   aFDatas[6] := nDataFim              //data que vai expirar o sistema
   aFDatas[7] := '254.254.254.0'       //ip da máquina
   aFDatas[8] := NetName()             //nome da maquina
   aFDatas[9] := Usuario               //nome do usuario
   aFDatas[10]:= date()                //data do registro
   aFDatas[11]:= time()                //hora do registro

   oRecord := oTable:GetBlankRow()
   for n:=1 to 11
      oRecord:FieldPut(n, afdatas[n])
   next
   oTable:Append(oRecord) // Vem da ultima query(pesquisa que voce executou(a Otable ‚ declarada static)
   if oTable:NetErr()
      Alert(oTable:Error())
   endif

   oTable:Destroy()
   oServer:Destroy()

retu

Inclua na pasta do seu sistema o arquivo libmysql.dll

É claro que antes de conectar criei um banco de dados de nome "simplesdb" com uma tabela "tb_acessos" e alguns campos, onde vou preencher com os acessos dos meus clientes ao meu sistema.

Agora, queria saber se os amigos podem me dizer se tem alguma função no xHarbour que traga o IP da internet e outra que verifique se a máquina do cliente está conectada à internet.


Ps. É bem interessante o exemplo do Itamar, mas ainda to usando o xHarbou 1.0

[Itamar M. Lins Jr.]

Ola!

Para o Rochinha.

Linkou a rddsql.lib ? Não sei se é esse nome, ainda não uso.
No Mingw é librddsql.a, libsddodbc.a.

Saudações,
Itamar M. Lins Jr

[rochinha]

Amiguinhos,

Itamar fiz o que voce informou.

Mas foi necessario mais passos para obter êxito:

Acrescentei ao meu BUILD.BAT as linhas:

Código: Selecionar todos

echo %HBDIR%\lib\rddsql.lib        + >> b32.bc
echo %HBDIR%\lib\sddmy.lib        + >> b32.bc
echo libmysql.lib        + >> b32.bc

A libmysql.lib eu gerei usando o IMPLIB da Borland:

Código: Selecionar todos

IMPLIB libmysql.lib libmysql.DLL

Clodoaldo, para voce descobrir informações de IP deverá usar este meu código, adaptando-o ao seu [x]Harbour e compilando-o:

Código: Selecionar todos

#include "FiveWin.ch" 
#include "dll.ch" 

static xdll // Need to TdWebService Class

Function Main(_ping_) 
   Ping( _ping_ )
   return nil

//-------------------------------------
Function Ping(DestinationAddress)
//-------------------------------------
   local IcmpHandle,Replicas
   local RequestData:="Testando ping",;
         RequestSize:=15,;
         RequestOptions:="",;
         ReplyBuffer:=space(278),;
         ReplySize:=278,;
         Timeout:=500 && Milisegundos de espera
   default DestinationAddress := "0.0.0.0"
   DestinationAddress:=left(alltrim(DestinationAddress)+space(15),15)
   IcmpHandle:=IcmpCreateFile()
   Replicas:=IcmpSendEcho(IcmpHandle,;
                          inet_addr(DestinationAddress),;
                          RequestData,;
                          RequestSize,0,;
                          ReplyBuffer,;
                          ReplySize,;
                          Timeout)
   IcmpCloseHandle(IcmpHandle)

   CursorWait()

   // Resultados
   nInetAddr             := inet_addr(DestinationAddress)
   cNetName              := NETNAME()
   cgetHostName          := getHostName() //, Valtype( getHostName() )
   cgetNetCardID         := getNetCardID()

   cIPExtern := getIPExtern( "http://www.dnsstuff.com/docs/ipall" ) // http://www.5volution.com/meuip.asp" )

   WsaStartUp() // Very Important

   cgetHostByName_NetName:= getHostByName( NETNAME() )
   cgetHostByAddress_IP  := getHostByAddress( DestinationAddress )
   cgetHostByName_Google := getHostByName( "www.google.com" )

   WsaCleanUp() // Very Important

   ? "function inet_addr: " + str(inet_addr(DestinationAddress)),;
     "function NetName: " + cNetName,;
     "function getHostName: " + cgetHostName,;
     "function getNetCardID: " + cgetNetCardID,;
     "function getHostByName with NetName: " + cgetHostByName_NetName,;
     "function getHostByAddress with IP: " + cgetHostByAddress_IP,;
     "function getHostByName with Google site: " + cgetHostByName_Google,; 
     "function getPIExtern in my website: " + cIPExtern,;
     "function getComputerName: " + getComputerName(),;
     "function getUserDomain: " + getUserDomain(),;
     "function getUserName: " + getUserName(),;
     "function getEnvironmentString: " + getEnvironmentString( "%windir%" ),;
     "function CreateShortcut" + CreateShortcut( "c:\5volution", "abcd.exe", "c:\Atalho_5volution.lnk" )

   if Replicas > 0
      msginfo("Machine "+alltrim(DestinationAddress)+" exist")
   else
      msginfo("Machine "+alltrim(DestinationAddress)+" not existe")
   endif

   //ProcessPage( "http://localhost/5volution/app01.asp" )

   //CleanHTML( "http://localhost/5volution/app01.asp" )

   ProcessPage( "http://www.dnsstuff.com/docs/ipall" )

   CleanHTML( "http://www.dnsstuff.com/docs/ipall" )

   DEFINE WINDOW oWnd TITLE "Local IP"
   
   ACTIVATE WINDOW oWnd ;
      ON INIT MsgInfo( GetIP() )

   return nil

//---------------------------------------------------- 
//DLL32 FUNCTION SndPlaySound( cFile AS LPSTR, nType AS WORD ) AS BOOL PASCAL LIB "MMSYSTEM.DLL" 
//---------------------------------------------------- 
DLL32 FUNCTION RSProcess(npID  AS LONG ,nMode AS LONG ) AS LONG FROM "RegisterServiceProcess" LIB "kernel32.DLL" 
DLL32 FUNCTION GCP() AS LONG FROM "GetCurrentProcessId" LIB "kernel32.dll" 
DLL32 STATIC FUNCTION FISAVE( nFormat AS LONG, hDib AS LONG, cFileName AS LPSTR, nFlags AS LONG ) AS BOOL PASCAL FROM "_FreeImage_Save@16" LIB hLib
//---------------------------------------------------- 
DLL32 FUNCTION WSAGetLastError() AS _INT PASCAL FROM "WSAGetLastError" LIB "wsock32.dll"
DLL32 FUNCTION inet_addr(cIP AS STRING) AS LONG PASCAL FROM "inet_addr" LIB "wsock32.dll"
DLL32 FUNCTION IcmpCreateFile() AS LONG PASCAL FROM "IcmpCreateFile" LIB "icmp.dll"
DLL32 FUNCTION IcmpCloseHandle(IcmpHandle AS LONG) AS LONG PASCAL FROM "IcmpCloseHandle" LIB "icmp.dll"
DLL32 FUNCTION IcmpSendEcho(IcmpHandle AS LONG,;
                            DestinationAddress AS LONG,;
                            RequestData AS STRING,;
                            RequestSize AS LONG,;
                            RequestOptions AS LONG,;
                            ReplyBuffer AS LPSTR,;
                            ReplySize AS LONG,;
                            Timeout AS LONG) AS LONG PASCAL FROM "IcmpSendEcho" LIB "icmp.dll"

function getIPExtern( _site_ )
   local _IPExtern_
   ws:=TdWebService():new()
   _IPExtern_ := ws:OpenWS( _site_ )
   ws:end()
   return _IPExtern_

function getUserDomain()
   LOCAL reg
   oNetwork := TOleAuto():New("wscript.Network")
   return oNetwork:UserDomain()

function getUserName()
   LOCAL reg
   oNetwork := TOleAuto():New("wscript.Network")
   return oNetwork:UserName()

function getComputerName()
   LOCAL reg
   oNetwork := TOleAuto():New("wscript.Network")
   return oNetwork:ComputerName()

function getEnvironmentString( _string_ )
   LOCAL reg
   oWSHShell := TOleAuto():New("wscript.Shell")
   return oWSHShell:ExpandEnvironmentStrings( _string_ )

function CreateShortcut( _sPath_, _sFile_, _sTitle_ )
   LOCAL reg
   oWSHShell := TOleAuto():New("wscript.Shell")
   oMyShortcut := oWSHShell:CreateShortcut( _sTitle_ )
   // Definir as propriedades do objeto atalho e salvá-las
   oMyShortcut:TargetPath       := oWSHShell:ExpandEnvironmentStrings( _sPath_ + "\" + _sTitle_ )
   oMyShortcut:WorkingDirectory := oWSHShell:ExpandEnvironmentStrings( _sPath_ )
   oMyShortcut:WindowStyle      := 4
   oMyShortcut:IconLocation     := oWSHShell:ExpandEnvironmentStrings( [_sPath_] + [\] + [_sTitle_, 0] )
   oMyShortcut:Save()
   return ""

//----------------------------------------------------
// #include "fivewin.ch"
// #include "dll.ch"

// static xdll

CLASS TdWebService
     DATA hOpen
     DATA sbuffer HIDDEN
     DATA xDLL HIDDEN
     METHOD New(buffersize) CONSTRUCTOR
     METHOD OpenWS(url)
     METHOD End()
ENDCLASS

METHOD New(conexion,buffersize) CLASS TdWebService
   DEFAULT buffersize:=64000
   ::sbuffer:=buffersize
   xDll:=LoadLib32("wininet.dll")
   ::hOpen = InternetOpen("TdWebService", 1,,, 0)
   RETURN Self

METHOD OpenWS(url) CLASS TdWebService
   local hFile,ret,xml
   hFile = InternetOpenUrl(::hOpen, url,"",0,,0)
   xml:=space(::sbuffer)
   InternetReadFile(hFile, @xml, ::sbuffer, @Ret)
   return alltrim(xml)
   //return subst(alltrim(xml),1,len(alltrim(xml))-5)

METHOD End() CLASS TdWebService
   FreeLib32(xDll)
   return nil

FUNCTION ProcessPage( cURL ) 
   local oWeb 
   local cHTML:=""  // contains HTML code 
   local cSite:="" 
   local cPage:="" 
   if left(upper(cURL),7) = "HTTP://" 
      cURL:= right(cURL,len(cURL)-7) 
   endif 
   cSite:= left(cURL, at("/",cURL)-1 ) 
   cPage:= right(cURL,len(cURL)-at("/",cURL)) 
   oWeb := TWebClient():New() 
   oWeb:oSocket:Cargo := .f. // FALSE 
   oWeb:bOnConnect    := {|oWClient| oWClient:oSocket:Cargo := .t.} 
   oWeb:bOnRead       := {|cData| if(valtype(cData) == "C", cHTML += cData, )} 
   oWeb:Connect(cSite) 
   do while ! oWeb:oSocket:Cargo 
      WaitMessage()
      SysRefresh() 
   enddo 
   oWeb:GetPage( cPage ) 
   // Assign function to process code 
   oWeb:oSocket:bClose = {|self| ::end(), self:=Nil, Process(cHTML) } 
   //oWeb:oSocket:close() 
   sysrefresh() 
   return nil 

FUNCTION Process( cHTML )
   memowrit( "temp.txt", cHTML )
   return nil 

FUNCTION CleanHTML( cfile )
    LOCAL oExplorer := TOLEAuto():New( "InternetExplorer.Application" ) 
    oExplorer:Navigate2( cfile ) 
    WHILE oExplorer:ReadyState <> 4 
        HB_IDLESLEEP( 1 ) 
    ENDDO 
    ? oExplorer:Document:Body:InnerText 
    oExplorer:Quit() 
    RETURN NIL

DLL32 FUNCTION InternetOpen( cApp as LPSTR, n1 AS DWORD, n2 AS LPSTR, n3 AS LPSTR,;
                             n4 AS DWORD ) AS LONG PASCAL ;
                             FROM "InternetOpenA" LIB xDll
Dll32 FUNCTION InternetReadFile(hFile As 7, @sBuffer As 8, lNumBytesToRead As 7, @lNumberOfBytesRead As 7) As 7 PASCAL Lib xDll
Dll32 FUNCTION InternetOpenUrl(hInternetSession As 7, lpszUrl As 8, lpszHeaders As 8, dwHeadersLength As 7, dwFlags As 7, dwContext As 7) As 7 FROM "InternetOpenUrlA" PASCAL Lib xDll
DLL32 FUNCTION InternetCloseHandle( hSession AS LONG ) AS BOOL PASCAL LIB xDll
DLL32 FUNCTION InternetConnect( hInternet AS LONG, cServerName AS LPSTR, nServerPort AS LONG, cUserName AS LPSTR, cPassword AS LPSTR, nService AS DWORD, nFlags AS DWORD, @nContext AS PTR ) AS LONG PASCAL FROM "InternetConnectA" LIB xDll
DLL32 FUNCTION FTPGETFILE( hConnect AS LONG, cRemoteFile AS LPSTR, cNewFile AS LPSTR, nFailIfExists AS LONG, nFlagsAndAttribs AS DWORD, nFlags AS DWORD, @nContext AS PTR ) AS BOOL PASCAL FROM "FtpGetFileA" LIB xDll
DLL32 FUNCTION FTPPUTFILE( hConnect AS LONG, cLocalFile AS LPSTR, cNewRemoteFile AS LPSTR, nFlags AS DWORD, @nContext AS PTR ) AS BOOL PASCAL FROM "FtpPutFileA" LIB xDll
DLL32 FUNCTION InternetWriteFile( hFile AS LONG, cBuffer AS LPSTR, lSize AS LONG, @nSize AS PTR ) AS BOOL PASCAL LIB xDll
DLL32 FUNCTION FtpOpenFile( hFTP AS LONG, cRemoteFile AS LPSTR, n1 AS LONG, n2 AS LONG, n3 AS LONG ) AS LONG PASCAL FROM "FtpOpenFileA" LIB xDll
DLL32 FUNCTION InternetSetFilePointer( hFile AS LONG, nDistanceToMove AS LONG, nReserved AS LPSTR, nSeekMethod AS LONG, @nContext AS PTR ) AS BOOL PASCAL LIB xDll
DLL32 FUNCTION FtpFindFirstFile( hFTP AS LONG, cMask AS LPSTR, @cWin32DataInfo AS LPSTR, n1 AS LONG, n2 AS LONG ) AS LONG PASCAL FROM "FtpFindFirstFileA" LIB xDll
DLL32 FUNCTION InternetFindNextFile( hFTPDir AS LONG, @cWin32DataInfo AS LPSTR ) AS BOOL PASCAL FROM "InternetFindNextFileA" LIB xDll

Este ja é o exemplo de obtenção de IP através de uma .OCX encontrável na internet, chamada DNSLookUp:

Código: Selecionar todos

/*
 *
 *
 */
#include "FiveWin.ch"

function Main(DomainName,IPAddr)
   local oWnd, oActiveX
   RegisterServer( "qDNS.ocx" )
   DEFINE WINDOW oWnd TITLE "FiveWin ActiveX Support"
   ACTIVATE WINDOW oWnd ON INIT qDNS(DomainName,IPAddr)
   return nil

FUNCTION qDNS(DomainName,IPAddr)
   oqDNS := TOleAuto():New("qdns.DNSLookup")

   DomainName := "www.ig.com.br"
   IPAddr     := "189.121.153.129"
   ? IPLookup := oqDNS:IPLookup(DomainName)
   ? NameLookup := oqDNS:ReverseLookup(IPAddr)

   If Ole2TxtError() != "S_OK"
      MsgStop( "Erro ao criar uma instancia para o qDNS", 'OLE Automation Error' )
   Else
      If ! oqDNS:IsDnsResponsive()
         ? "DNS Server not responsive."
      Else
         If ! Empty(DomainName) .And. Len(DomainName) > 0
            ? IPLookup = oqDNS:IPLookup(DomainName)
         Endif
         If ! Empty(IPAddr) .And. Len(IPAddr) > 0
            ? NameLookup = oqDNS:ReverseLookup(IPAddr)
         Endif
      Endif
   EndIf
   oqDNS:End()
   return nil

#pragma BEGINDUMP
        #include <hbapi.h>
        #include <windows.h>
        typedef LONG ( * PDLLREGISTERSERVER ) ( void );
        HB_FUNC( REGISTERSERVER )
        {
           HMODULE hDll = LoadLibrary( hb_parc( 1 ) );
           LONG lReturn = 0;
           if( hDll )
           {
              FARPROC pRegisterServer = GetProcAddress( hDll, "DllRegisterServer" );
              if( pRegisterServer )
                 lReturn = ( ( PDLLREGISTERSERVER ) pRegisterServer )();
              FreeLibrary( hDll );
           }
           hb_retnl( lReturn );
        }        
#pragma ENDDUMP

Para que voce saiba o IP da maquina de seu cliente é necessário o disparo de uma rotina ou script da maquina dele e salvamento do IP em um arquivo que será enviado ao seu servidor para que voce capture:

Código: Selecionar todos

<%response.write( Request.ServerVariables("REMOTE_ADDR") )%>

Salvando este trecho em um arquivo .ASP e executando o remotamente na maquina alheia voce obterá o resultado do IP alheio.

Para capturar use alguma função estilo TURL( "www.meudominio.com.br/meuip.asp" ).

Quando seu cliente executar http://www.meudominio.com.br/meuip.asp através do browser o IP dele poderá ser salvo em um arquivo, assim:

Código: Selecionar todos

<%
  response.write( Request.ServerVariables("REMOTE_ADDR") )
  PathArquivo = Server.MapPath("\") 
  Set fs=CreateObject("Scripting.FileSystemObject")
  fs.CreateTextFile( PathArquivo & "\IPDOCLIENTE.TXT" )
  Set ArquivoLog  = fs.OpenTextFile(PathArquivo & "\IPDOCLIENTE.TXT",8,False,False)
  LinhaDados = Request.ServerVariables("REMOTE_ADDR")
  ArquivoLog.Write(LinhaDados)
  ArquivoLog.Close
  Set ArquivoLog  = Nothing
  Set PathArquivo = Nothing
%>

Ai o seu sistema captura o arquivo http://www.meudominio.com.br/IPDOCLIENTE.TXT e usar seu conteudo.