usar MSSQL e Oracle sem SQLRDD

EDUTEK · August 27, 2020

Gostaria de usar o SQL Server e o Oracle sem usar a SQLRDD , hoje eu uso os dois BD com SQLRDD,

mas gostaria de ficar independente da SQLRDD.

eu ja uso para dar Insert, update e delete os comando SQL com o SQLRDD, mas estou refém do Browse e seleção de indices que usando a SQLRDD fica muito comodo.

como eu faria isso sem utilizar ela.

alguém tem um exemplo ou uma documentação para isso.

se possível algo nativo sem usar ferramentas de terceiros.

infosys2 · August 27, 2020

#include "fivewin.ch"

//----------------------------------------------------------------------------//

function Main()

   local oCn, oRs, nRecs
  oCn := maria_Connect( "localhost", "fwh", "root","senha" ,,, )

 oRs     := oCn:RowSet( "select * from suporte ")
  XBROWSER oRs TITLE "`Custbig`  FASTEDIT
   oRs:Close()
   oCn:Close()
return nil

Poderia ser desta forma !

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

//----------------------------------------------------------------------------//

function Main()

   local oCn, oRs, oDlg, oFont, oBrw

   oCn   := FW_DemoDB()
   if oCn == nil
      return nil
   endif

   oRs   := oCn:RowSet( "SELECT ID,FIRST,CITY,SALARY FROM customer" )

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 600,500 PIXEL TRUEPIXEL FONT oFont   TITLE "MARIADB LOCKED EDIT"

   @ 60,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg  DATASOURCE oRs AUTOCOLS   CELL LINES NOBORDER FASTEDIT

   WITH OBJECT oBrw
      :nEditTypes    := EDIT_GET
      :CreateFromCode()
   END

   @ 10,20 BTNBMP PROMPT "LockedEdit" SIZE 150,40 PIXEL OF oDlg FLAT ACTION LockedEdit( oBrw )

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

static function LockedEdit( oBrw )

   local oRsMain, oRsEdit, oRec, nWait, oCn

   oRsMain  := oBrw:oDbf
   oCn      := oRsMain:oCn
   nWait    := oCn:QueryResult( "SHOW SESSION VARIABLES LIKE 'innodb_lock_wait_timeout'" )[ 2 ]
   oCn:Execute( "SET SESSION innodb_lock_wait_timeout = 1" )
   oCn:BeginTransaction()

   MsgRun( "Reading Record", "WAIT", { || ;
      oRsEdit := oCn:RowSet( "SELECT * FROM customer WHERE ID = ? FOR UPDATE", { oRsMain:ID } ) ;
      } )

   if oCn:nError == 0
      oCn:Execute( "SET SESSION innodb_lock_wait_timeout = " + cValToChar( nWait ) )
      oRec  := TDataRow():New( oRsEdit )
      oRec:Edit()
      oCn:CommitTransaction()
      oRsMain:ReSync()
      oBrw:RefreshCurrent()
   else
      oCn:RollBack()
      oCn:Execute( "SET SESSION innodb_lock_wait_timeout = " + cValToChar( nWait ) )
      ? "Record is locked by other user"
   endif
   oBrw:SetFocus()

return nil

sygecom · August 28, 2020

infosys2,

Esse exemplo seria para MARIA DB e talvez funcione no MYSQL. Nunca testei.

EDUTEK,

Nesse caso pode usar SQLMIX, mas saiba que vai precisar de mudanças tanto de conexão como casos que usa comando xbase como BROWSE que citou, que vai precisar mudar para ARRAY em vez de DATABASE.

Veja o exemplo das contrib no xHarbour:

http://svn.code.sf.net/p/xharbour/code/trunk/xharbour/contrib/sddoci/tests/test1.prg

Por fim, se for se livrar do SQLRDD migre para Harbour.

Jmsilva · August 28, 2020

Dá para usar conexão via Odbc e ADO, o sal server é sem novidades, quanto ao Oracle nunca testei

EDUTEK · August 28, 2020

a ideia é ir para o Harbour mesmo, sair do xharbour

EDUTEK · August 31, 2020

Bom Dia!

Onde acho exemplos de ADO, pois na verdade eu usaria o Mysql via ferramenta do Fivewin mesmo e os acessos oracle e mssql por outro meio ( o ADO por exemplo) pois eu uso só para extrair informações deste bancos, para compor meus relatorios e importar dados.

rochinha · September 1, 2020

Amiguinhos,

Aqui tem algumas dicas sobre uso do ADO com banco de dados.

Vale frisar que ADO necessita de driver ODBC compativel com o banco de dados a ser usado.

Bom trabalho.

Jmsilva · September 1, 2020

Edu, esta classe eu criei e funciona perfeitamente no meu projeto, algumas dicas:

Veja se ajuda!

- http://fivetechsoft.com/wiki/doku.php?id=fwh_ado_api - Lib da fivewin

***====================================================================***
*** Sistema....: WSACE-                                                ***
*** Rotina.....: T_SqlAdo.PRG                                        ***
*** Linguagem..: Harbour/Fivewin                                       ***
*** Programador: JMSILVA                                                 ***
*** Data.......: 13/09/2016                                            ***
***====================================================================***

#INCLUDE "FIVEWIN.CH"
#INCLUDE "ERROR.CH"

//tratamento de erro falta
**=============================================================================
CLASS TSqlADO
**=============================================================================
   DATA     cDBSql,cHost,cDsn   PROTECTED
   DATA     hCon,hStmt HIDDEN
   DATA     aErrors  INIT {}
   DATA     lSuccess INIT .T. PROTECTED
   DATA     nRecCount INIT 0
   DATA     cTitle,cLastErro
   DATA     cFileErro PROTECTED
   DATA     nLast_Insert_ID  INIT 0 // EM TESTE FALTA IMPLEMENTAR

   METHOD   New()                                CONSTRUCTOR
   METHOD   Server(cServer, cBanco, cUser, cPwd) CONSTRUCTOR
   METHOD   MariaDB(cServer, cBanco, cUser, cPwd)  CONSTRUCTOR
   METHOD   MySql(cServer, cBanco, cUser, cPwd)  CONSTRUCTOR
   METHOD   Close()

   METHOD   IsErrorSql()   INLINE !::lSuccess
   METHOD   MsgError()     INLINE  ::cLastErro
   METHOD   IdError()      INLINE IIF(::lSuccess,0,-1)

   METHOD   GetVersion()
   METHOD   PathDB()       INLINE ::cHost+::cDBSql
   METHOD   GetDBase()     INLINE ::cDBSql

   //nao funciona no odbc //begin/roll/commit
   METHOD   SqlTransact()  INLINE ::Exec("Begin Transaction") //SqlTransact(::hEnv,::hDbc)
   METHOD   SqlCommit()    INLINE ::Exec("Commit Transaction") //(::hEnv,::hDbc)
   METHOD   SqlRollBack()  INLINE ::Exec("Rollback Transaction") //SQLTransact(::hEnv,::hDbc,SQL_ROLLBACK) //SQLRollBack(::hEnv,::hDbc)

   METHOD   Exec(cCmdSql,lQuery)
   METHOD   Query(cCmdSql) //feito
   METHOD   QueryRow(cCmdSql)
   METHOD   SqlUseArea(cAlias,cCmdSql)
   METHOD   RecCount(cTable,cWhere)
   METHOD   GetLastId()  INLINE ::nLast_Insert_ID //ultimo registro adicionado
   METHOD   IsRunLastId() INLINE .T.

   METHOD   IsTable( cTable )
   METHOD   ListTables()
   METHOD   ListFields(cTable)

   METHOD   SqlSeek(P1)
   METHOD   IsField(P1,P2)

   METHOD   GravaErro(P1)

   //Exclusiva -ODBC
   METHOD CursorFields(P1) //lSQL
   METHOD SqlGetData(nField) PROTECTED

   /*SqlFreeStmt livra estouro de memoria do servidor sql*/
   METHOD SqlFreeStmt() INLINE ::hStmt:Close() PROTECTED    //ado
   METHOD SetLastId(cSql)
   METHOD SqlFistStmt()
   METHOD SqlLastStmt()

ENDCLASS

**============================================================
METHOD New() CLASS TSqlADO
**============================================================
Return Self

**===============================================================
METHOD Server( cServer, cBanco, cUser, cPwd) CLASS TSqlADO
**===============================================================
  Local cDir := cFilePath( GetModuleFileName( GetInstance() ) )
  Local cFileLog := cDir+"log\Odbc_erro.log"   //fica aberto
  Local oErr

  ::cTitle  := "SqlServer Via ADODB"
  ::cFileErro := cDir+"log\SqlServer.log"
  ::cDBSql   := cBanco
  ::cHost    := cServer
  ::cDsn     := "Driver=SQL Server; Server="+cServer+"; Database="+cBanco+";"+;
                "UID="+cUser+"; PWD="+cPwd+";"
   Begin Sequence
      Try
         //https://www.w3schools.com/asp/ado_ref_connection.asp
         ::hCon :=CreateObject("ADODB.Connection")
         ::hCon:CursorLocation := 3 //adUseClient  //tem haver RecordCount()
         ::hCon:Open(::cDsn)
         //MsgStop(::hCon:Provider) //https://www.w3schools.com/asp/prop_conn_provider.asp
      Catch oErr
         ::lSuccess := .F.
         ::cLastErro := "Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10)
         ::GravaErro("ADODB.Connection")
         FW_ShowAdoError(::hCon,.F.)
         Break
      End
      // MsgStop(FW_RDBMSName( ::hCon )) //e legal
      Try
         ::hStmt:=CreateObject("ADODB.Recordset")
         ::hStmt:ActiveConnection := ::hCon
      Catch oErr
         ::lSuccess := .F.
         ::cLastErro := "Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10)
         ::GravaErro("ADODB.Recordset")
         MsgStop("Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10),"ADODB.Recordset")
         Break
      End
   End Squence

Return Self

**===============================================================
METHOD MariaDB(cServer,cBanco,cUser,cPwd) CLASS TSqlADO
**===============================================================
   Local cDir := cFilePath(GetModuleFileName(GetInstance()))
   Local cFileLog := cDir+"log\Odbc_erro.log"
   Local oErr

   ::cTitle  := "MariaDB Via ADODB"
   ::cFileErro := cDir+"log\MariaDB.log"
   ::cDBSql := cBanco
   ::cHost  := cServer
   ::cDsn   := "Driver={MariaDB ODBC 3.1 Driver }; Server="+cServer+"; Database="+cBanco+";"+;
               "UID="+cUser+"; PWD="+cPwd+";Port=3306;Option=3;"
   Begin Sequence
      Try
         ::hCon :=CreateObject("ADODB.Connection")
         ::hCon:CursorLocation := 3 //adUseClient  //tem haver RecordCount()
         ::hCon:Open(::cDsn)
      Catch oErr
         ::lSuccess := .F.
         ::cLastErro := "Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10)
         ::GravaErro("ADODB.Connection")
         FW_ShowAdoError(::hCon,.T.)
         Break
      End

      Try
         ::hStmt:=CreateObject("ADODB.Recordset")
         ::hStmt:ActiveConnection := ::hCon
      Catch oErr
         ::lSuccess := .F.
         ::cLastErro := "Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10)
         ::GravaErro("ADODB.Recordset")
         MsgStop("Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10),"ADODB.Recordset")
         Break
      End
   End Squence

Return Self


**===============================================================
METHOD MySql(cServer,cBanco,cUser,cPwd) CLASS TSqlADO
**===============================================================
   Local cDir := cFilePath(GetModuleFileName(GetInstance()))
   Local cFileLog := cDir+"log\Odbc_erro.log"
   Local oErr

   ::cTitle  := "MySql Via ADODB"
   ::cFileErro := cDir+"log\Mysql.log"
   ::cDBSql := cBanco
   ::cHost  := cServer
   ::cDsn   := "Driver={MySQL ODBC 3.51 Driver}; Server="+cServer+"; Database="+cBanco+";"+;
               "UID="+cUser+"; PWD="+cPwd+";Port=3306;Option=3;"
   Begin Sequence
      Try
         ::hCon :=CreateObject("ADODB.Connection")
         ::hCon:CursorLocation := 3 //adUseClient  //tem haver RecordCount()
         ::hCon:Open(::cDsn)
      Catch oErr
         ::lSuccess := .F.
         ::cLastErro := "Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10)
         ::GravaErro("ADODB.Connection")
         FW_ShowAdoError(::hCon,.T.)
         Break
      End

      Try
         ::hStmt:=CreateObject("ADODB.Recordset")
         ::hStmt:ActiveConnection := ::hCon
      Catch oErr
         ::lSuccess := .F.
         ::cLastErro := "Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10)
         ::GravaErro("ADODB.Recordset")
         MsgStop("Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10))
         Break
      End
   End Squence

Return Self

**===============================================================
METHOD Close() CLASS TSqlADO
**===============================================================
   ::hCon:Close()
Return Nil

**===============================================================
METHOD IsTable(cName) CLASS TSqlADO
**===============================================================
   Local lTable := Fw_AdoTableExists(cName,::hCon)

Return lTable

**===============================================================
METHOD Exec(cCmdSql,lQuery) CLASS TSqlADO
**===============================================================
   Local oErr,lRet:=.T.
   Default(@lQuery,.F.)

   //este parametro faz funcionar recCount

   TRY
      If lQuery
         ::hStmt:Open(cCmdSql)    //query select
      Else
         ::hCon:Execute(cCmdSql)  //exec insert
      Endif
   CATCH oErr
      lRet := .F.
      ::cLastErro := "Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10)
      MsgStop("Operacao: "+oErr:operation+" - Descrição: "+oErr:Description+Chr(10),"xxx")
      ::GravaErro(cCmdSql)
   End
Return lRet

**============================================================
METHOD RecCount(cTable,cWhere) CLASS TSqlADO
**============================================================
   Local cSql := "Select Count(*) as count From "+cTable

   ::nRecCount := 0
   If PCount() == 0 //sem parametros
      Return Nil
   Endif

   If !Empty(cWhere)
      cSql += "Where "+cWhere
   Endif
   If ::Exec(cSql,.T.)
      ::hStmt:MoveFirst()
      ::nRecCount := ::hStmt:Fields:Item(0):Value
      ::SqlFreeStmt()
   Endif
Return Nil

**=============================================================
METHOD SqlUseArea(cAlias,cCmdSql) CLASS TSqlADO
**=============================================================
   Local aFields:={},nI,nMin:=500

   If !::Exec(cCmdSql,.T.)
      MsgStop("Desculpe! Falha na abertura do arquivo "+cAlias+".","wSace")
      Return .F.
   Endif
   ::nRecCount := ::hStmt:RecordCount()

   aFields := ::CursorFields()
   If Select(cAlias)> 0; (cAlias)->(DBCloseArea()); Endif
   HB_DbCreateTemp(cAlias,aFields,"SIXCDX")

   If ::nRecCount > nMin; Sys_Meter(1,::nRecCount,::cTitle) ; Endif
   If !::hStmt:Bof(); ::hStmt:MoveFirst(); Endif  //da erro se for vazio
   DO WHILE !::hStmt:Eof()
      DBAppend()
      For nI:=1 TO ::hStmt:Fields:Count //começou no zero
          FieldPut(nI,::SqlGetData(nI-1)) //grava no temp     da colocar try catch para gravar erro
      Next
      If ::nRecCount > nMin; Sys_Meter(2); Endif
      ::hStmt:MoveNext()
      //if LastRec() > 100 ; exit; endif //---------------------------------------teste
   Enddo
   If ::nRecCount > nMin; Sys_Meter(3); Endif
   ::SqlFreeStmt() //limpa finaliza hStmt
   ::nRecCount := 0
   DBGoTop()
Return .T.

**=============================================================
METHOD SqlGetData(nField) CLASS TSqlADO
**=============================================================
   Local uData := ::hStmt:Fields:Item(nField):Value

Return uData


**=============================================================
METHOD Query(cCmdSql) CLASS TSqlADO
**=============================================================
   Local aBuffer:={},nLen,nI,nRow:=1

   If !::Exec(cCmdSql,.T.)
      ::SQLFreeStmt() //limpa
      Return {}
   Endif

   nLen := ::hStmt:Fields:Count
   If ::nRecCount > 500; Sys_Meter(1,::nRecCount,::cTitle) ; Endif
   If !::hStmt:Bof(); ::hStmt:MoveFirst(); Endif  //da erro se for vazio
   DO WHILE !::hStmt:Eof()
      AAdd(aBuffer,Array(nLen))
      For nI:=1 TO nLen
         aBuffer[nRow,nI] := ::SqlGetData(nI-1)
      Next
      If ::nRecCount > 500; Sys_Meter(2); Endif
      ::hStmt:MoveNext()
      nRow++
   Enddo
   If ::nRecCount > 500; Sys_Meter(3); Endif
   ::nRecCount := 0
   ::SQLFreeStmt() //limpa
Return aBuffer

**=============================================================
METHOD QueryRow(cCmdSql) CLASS TSqlADO
**=============================================================
   Local aFields,lFound := .T.
   Local nCol,oTupla := HBClass():New("Tupla")

   Begin Sequence
      If !::Exec(cCmdSql,.T.)
         ::SqlFreeStmt() //limpa
         lFound := .F.
         Break
      Endif

      aFields := ::CursorFields()
      For nCol := 1 To Len(aFields)
         oTupla:AddData(aFields[nCol,1],::SqlGetData(nCol-1))   // uma linha
      Next
   End Sequence
   ::SqlFreeStmt() //limpa

   oTupla:AddData("lFound",lFound)
   oTupla:Create()
Return oTupla:Instance()

**=======================================================================
METHOD SetLastId(cCmdSql) CLASS TSqlADO
**=======================================================================
   Local aVet := ::Query(cCmdSql)

   ::nLast_Insert_ID := 0
   If Len(aVet) > 0
      ::nLast_Insert_ID := aVet[1,1]
   Endif
Return Nil

**=======================================================================
METHOD ListFields(cTable) CLASS TSqlADO
**=======================================================================
   Local cSql := "SELECT * FROM "+AllTrim(cTable)+" WHERE 1=0;"
   Local aFields

   If !::Exec(cSql,.T.)  //criar e carrega hStmt se .t.
      ::SqlFreeStmt() //limpa
      Return {}
   Endif

   aFields := ::CursorFields(.T.)
   ::SQLFreeStmt() //limpa

   If Len(aFields) == 0
      Return {}
   Endif

Return aFields

**=============================================================
METHOD CursorFields(lSql) CLASS TSqlADO   //funciona ADODB
**=============================================================
   Local nI,aFields //:= FwAdoStruct(::hStmt)
   Default(@lSql,.F.)

   aFields := FwAdoStruct(::hStmt)
   If !lSql
      For nI:=1 TO Len(aFields)
         aFields[nI] := ASize(aFields[nI],4)
      Next
   Endif
Return aFields

**=======================================================================
METHOD ListTables() CLASS TSqlADO
**=======================================================================
   Local aFiles := Fw_AdoTables(::hCon)
Return aFiles


**=======================================================================
METHOD GetVersion() CLASS TSqlADO
**=======================================================================
   Local cSql := "SELECT @@VERSION;"
   Local aVet := ::Query(cSql),cValue := " "

   If Len(aVet) > 0
      cValue := aVet[1,1]
   Endif

Return cValue

**===================================================================
METHOD SqlSeek(cSQL) CLASS TSqlADO
**===================================================================
   Local oRow

   oRow := ::QueryRow(cSQL)

Return oRow:lFound

**===================================================================
METHOD IsField(t,c) CLASS TSqlADO
** RETORNA= TRUE / FALSE
**===================================================================
   Local lRET:=.F.,aVET:={}

   IF !Empty(c)
      aVET := ::ListFields(t)
      IF Len(aVET) > 0   //RETORNA UMA CELULA VAZIA
        IF AScan(aVET,{|X| Upper(Trim(X[1])) == Upper(c)}) > 0
           lRET :=.T.
        ENDIF
      ENDIF
   ENDIF
Return(lRET)

**============================================================
METHOD SqlFistStmt() CLASS TSqlADO
**============================================================
   ::hStmt:MoveFirst()
Return Nil

**============================================================
METHOD SqlLastStmt() CLASS TSqlADO
**============================================================
   ::hStmt:MoveLast()
Return Nil

**=========================================================
METHOD GravaErro(cMessage) CLASS TSqlADO
**=========================================================
   Local nCt

   cMessage += hb_eol()+::MsgError()+hb_eol()

   // grava mensagem em um arquivo log ////////////////////////////////////////
   SET CONSOLE OFF
   SET PRINTER TO (::cFileErro) ADDITIVE
   SET DEVICE TO PRINTER
   SET PRINTER ON

   QOUT("**==================================================================")
   QOUT("Data:")
   QQOUT(TRANSF(DATE(),"@E"))
   QQOUT(" Hr:")
   QQOUT(TIME())
   QQOut("   Operador: ")
   QQOut(M->pCOD+"-"+M->pOPE)
   QOUT("**==================================================================")

   QOUT(cMessage)
   QOut(::MsgError())
   nCt := 2

   DO WHILE !Empty(ProcName(nCt))
      QOUT("Rotina...", Trim(ProcName(nCt)) + "("+LTrim(Str(ProcLine(nCt)))+")")
      nCt++
   ENDDO

   SET DEVICE TO SCREEN
   SET PRINTER OFF
   SET PRINTER TO
   SET CONSOLE ON

Return NIL

EDUTEK · September 1, 2020

Valeu pessoal

Vou efetuar os testes

Sign In

usar MSSQL e Oracle sem SQLRDD

Recommended Posts

EDUTEK

Link to comment

Share on other sites

infosys2

Link to comment

Share on other sites

sygecom

Link to comment

Share on other sites

Jmsilva

Link to comment

Share on other sites

EDUTEK

Link to comment

Share on other sites

EDUTEK

Link to comment

Share on other sites

rochinha

Link to comment

Share on other sites

Jmsilva

Link to comment

Share on other sites

EDUTEK

Link to comment

Share on other sites

Join the conversation

Browse

Activity