hmg and oracle

Creative ideas/suggestions for HMG

Moderator: Rathinagiri

User avatar
dragancesu
Posts: 696
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 23 times
Been thanked: 148 times

hmg and oracle

Post by dragancesu » Wed Jun 10, 2015 12:56 pm

I'm trying to access Oracle database, I looked forum, I tried but I could not. I found this and I wonder if anyone used it? Tips are welcome

http://orclib.sourceforge.net/documentation/

User avatar
mol
Posts: 3219
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 224 times
Been thanked: 125 times
Contact:

Post by mol » Wed Jun 10, 2015 3:09 pm

I have an access to oracle db at my work.I'll try tomorrow

User avatar
lalacas
Posts: 11
Joined: Fri Sep 19, 2014 8:27 am
Location: Spain

Post by lalacas » Thu Jun 11, 2015 10:09 am

I hope this code will be useful

Code: Select all

 cConstr := 'DSN=MyBase;UID=USER;PWD=password'

 dsFun2 := TODBC():New(cConStr) 
 cError := dsFun2:SQLErrorMessage()
 IF cError <> 'Error 00000 - '
    MsgExclamation(cError + cCRLF + 'Conexión: ' + cConstr)
    RETURN cDato
 ENDIF
 
 nRegi := 0
 nOk   := 0
 nModi := 0

 WITH dsFun2 DO

      WAIT WINDOW 'Buscando datos' NOWAIT

      cSQL := 'select des_login, des_nombre, des_apellido' + ;
                 ' from adpr_tusuario' + ;
                 ' order by des_login'    
      
      dsFun2:SetSQL(cSQL)
      dsFun2:Open()
      
      nRegTot := dsFun2:RECCOUNT()
      IF nRegTot > 0
         dsFun2:First()
  
         DO WHILE .T.
            IF (dsFun2:EOF()) ; EXIT ; ENDIF   
            nRegi ++
            cLogin     := ALLTRIM(dsFun2:FieldByName('DES_LOGIN'):Value)
            cNombre    := LEFT(ALLTRIM(dsFun2:FieldByName('DES_NOMBRE'):Value)   + SPACE(20), 20)
            cApellido  := LEFT(ALLTRIM(dsFun2:FieldByName('DES_APELLIDO'):Value) + SPACE(30), 30)
            
            IF nRegi == 1
               f_SeqOpen(1, cFichLis, 'e')
               f_SeqWrite(1, 'Usuarios' + SPACE(20) + DTOC(DATE()) + ' ' + TIME())
               f_SeqWrite(1, '')
               f_SeqWrite(1, 'Login OLD  Login New        Nombre                Apellidos              ')
               f_SeqWrite(1, '---------- ---------- -------------------- ------------------------------')
            ENDIF   
            
            cLogNew := SPACE(10)

            *--- Correcto            
            IF LEN(cLogin) == 10 .AND. SUBSTR(cLogin, 1, 4) == 'ES00' .AND. fEsNume(SUBSTR(cLogin, 5, 6))
               cLogNew := 'OK        '
               nOK ++
            ENDIF 

            IF (LEN(cLogin) == 6 .OR. LEN(cLogin) == 8) .AND. fEsNume(cLogin)
               cLogNew := 'ES' + IF(LEN(cLogin) == 6, '00', '') + cLogin
               nModi ++
            ENDIF  
            
            f_SeqWrite(1, LEFT(cLogin + SPACE(10), 10) + ' ' +;
                          cLogNew + ' ' + cNombre + ' ' + cApellido)
            
            dsFun2:Skip()
         ENDDO
      ENDIF   
      dsFun2:Close()

      WAIT CLEAR

 ENDWITH
   
 dsFun2:Destroy()
 
 IF nRegi > 0
    f_SeqWrite(1, '')
    nMal := nRegi - nOK - nModi
    f_SeqWrite(1, 'Leidos: ' + TRAN(nRegi, "@E 999,999"))
    f_SeqWrite(1, 'OK....: ' + TRAN(nOK,   "@E 999,999"))
    f_SeqWrite(1, 'Modifi: ' + TRAN(nModi, "@E 999,999"))
    f_SeqWrite(1, 'Mal...: ' + TRAN(nMal,  "@E 999,999"))
    f_SeqClose(1)
    fReader(cFichLis, .F., 'V')
 ENDIF 

User avatar
mol
Posts: 3219
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Has thanked: 224 times
Been thanked: 125 times
Contact:

Post by mol » Fri Jun 12, 2015 5:38 am

I'm trying to connect to server, but without success :-(

User avatar
lalacas
Posts: 11
Joined: Fri Sep 19, 2014 8:27 am
Location: Spain

Post by lalacas » Fri Jun 12, 2015 7:36 am

I have installed Oracle Client 11g and I've defined an ODBC data source.
http://www.interfaceware.com/manual/odbc_oracle.html

User avatar
dragancesu
Posts: 696
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 23 times
Been thanked: 148 times

Post by dragancesu » Fri Jun 12, 2015 8:02 am

This promise, I'll try

User avatar
Ismach
Posts: 94
Joined: Wed Nov 28, 2012 5:55 pm
DBs Used: DBF, mySQL, Mariadb, postgreSQL, Oracle, Db2, Interbase, Firebird, and SQLite
Location: Buenos Aires - Argentina
Been thanked: 7 times

Post by Ismach » Wed Nov 14, 2018 12:58 am

Yo si pude conectar a Oracle.... trabajo en una entidad financiera... y alli no puedo subir el ejemplo
porque tengo restriccciones por todos lados, por un tema de seguridad...
pero tengo varios desarrollo en HMG conectado a Oracle
lo importante a tener en cuenta:
1) usar como nombre del HOST la TNS NAME de la base oracle
en mi caso yo mi TNS NAME de mi base es ARGDESA45
2) User con todos los GRANTs necesarios
3) probar con un cliente tipo PL/SQL dev o Toad


Code: Select all

#include "FileIO.ch"
#include "Minigui.ch"
#include "adordd.ch"

#include "Abc\SysDefs.ch"

SET PROCEDURE TO "Gestab\Class\A012.prg"

static wifmain

static aChItems  := {}
static aErItems  := {}
static aMtItems  := {}
static aCbItems  := {}

static cFecha                 // FECHA DE LA TABLA DE FECHA CONTABLE.

static bConectado              // ESTADO DE APLICACION (TRUE/FALSE)

static cTerm                   // TERMINAL DEL USUARIO
static cUser  := "ismach"      // USUARIO ATLAS II
static cPass  := "ismach"      // PASSWORD
static cHost  := "ARGDESA45"   // NOMBRE DEL HOST
static cBase  := "ARGDESA45"   // BASE DE DATOS DEL AMBIENTE
static cProv  := "MSDAORA.1"   // PROVEEDOR DEL API CONNECTOR



static cUsuario               // FECHA DE LA TUSR231
static cSucursal              // SUCURSAL DEL USUARIO
static nSucursal := 0


static oA012
//--------------------------------------------------------------------//
FUNCTION Main()


     Local nMedio := (nWIDTH/2)-180
     Private fClr := { || if ( This.CellRowIndex/2 == int(This.CellRowIndex/2) , BLUE, NEGRO ) }
     Private zClr := { || if ( This.CellRowIndex/2 == int(This.CellRowIndex/2) , BEIGE, AGUA ) }

     Public oOra              // OBJETO DE ADOCONEXION

     SET EXCLUSIVE OFF
     SET DELETE ON
     SET DATE ITALIAN
     SET CENTURY ON
     SET EXACT ON
     SET NAVIGATION EXTENDED
     SET EPOCH TO YEAR(DATE()) - 50

     REQUEST HB_LANG_ES
     SET LANGUAGE TO SPANISH
     SET MULTIPLE OFF WARNING

     SET INTERACTIVECLOSE ON

     //SET DATE FORMAT TO 'dd/mm/yyyy'
     GetConfig()

     Gb_Oracle()

     DEFINE WINDOW wifmain AT 00, 00 ;
            WIDTH nWIDTH HEIGHT nHEIGHT ;
            TITLE SISTEMA ;
            ICON "PINO";
            NOCAPTION ;
            BACKCOLOR {000,128,000} ;
            NOSIZE ;
            MAIN ;
            ON INIT Gb_LoginUser()

        ON KEY ALT+F4 ACTION Salir()
                  .
                  .


FUNCTION Gb_Oracle()

     oOra := TOLEAuto():New('ADODB.Connection')

     oOra:Open("Provider=" + cProv + ";" + ;
               "Data Source= " + cHost + ";" + ;
               "User ID=" + cUser + ";" + ;
               "Password=" + cPass + ";" )

RETURN NIL


User avatar
Ismach
Posts: 94
Joined: Wed Nov 28, 2012 5:55 pm
DBs Used: DBF, mySQL, Mariadb, postgreSQL, Oracle, Db2, Interbase, Firebird, and SQLite
Location: Buenos Aires - Argentina
Been thanked: 7 times

Post by Ismach » Wed Nov 14, 2018 1:03 am

Podes probar esta funcion para ver cono es tu string de conexion:

Code: Select all

****************************************************************************
// MSDAORA.1 es el provider OLE estandar de Microsoft para acceder a una
// base de datos Oracle, aunque el mismo Oracle tiene otro provider-OLE
// propio. Si usas la función siguiente (cortesía de Biel), puedes
// probar si la conexión es correcta, usando el Datalink de Microsoft.
// *************************************************************************

static FUNCTION FDATALINK (oCon)

     LOCAL oDataLink := TOleAuto():New("Datalinks"), ;
     oConn := oDataLink:PromptNew()
     msginfo (oConn:ConnectionString)
     oCon:CLOSE()

RETURN nil
****************************************************************************

User avatar
Ismach
Posts: 94
Joined: Wed Nov 28, 2012 5:55 pm
DBs Used: DBF, mySQL, Mariadb, postgreSQL, Oracle, Db2, Interbase, Firebird, and SQLite
Location: Buenos Aires - Argentina
Been thanked: 7 times

Post by Ismach » Wed Nov 14, 2018 1:04 am

Ahi va ele ejemplo con el que empecé

Code: Select all

****************************************************************************
FUNCTION MAIN()

    Public cProv := "MSDAORA.1"
    Public cHost := "b32atlas1"
    Public cUser := "atlas"
    Public cPass := "atlas"
    Public cConn := Nil

    cProv := "MSDAORA.1"
    cHost := "b32atlas1"  // tambien puedes poner el nombre de la BD
    cUser := "atlas"
    cPass := "atlas"

    GetConfigApps()

    Oracle_Connect()
    If !Conectado
        MsgInfo("No se Puede Conectar a Base de Datos")
        Release Window All
        Quit
    EndIf

    Load Window WinMain
    Activate Window WinMain


RETURN NIL

static FUNCTION Oracle_Connect()

    cConn := "Provider=" + cProv + ";" + ;
             "Data Source= " + cHost + ";" + ;
             "User ID=" + cUser + ";" + ;
             "Password=" + cPass + ";"


    oCon := TOLEAuto():New('ADODB.Connection')
    TRY
        oCon:Open("Provider=" + cProv + ";" + ;
                  "Data Source= " + cHost + ";" + ;
                  "User ID=" + cUser + ";" + ;
                  "Password=" + cPass + ";" )
    CATCH
         MSGSTOP ("Provider: " + cProv + CRLF + ;
                  "Host: " + cHost + CRLF + ;
                  "Usuario: " + cUser + CRLF + ;
                  "Clave: " + cPass, "Error de Conexión")

         RETURN nil
    END

RETURN NIL
****************************************************************************

User avatar
Ismach
Posts: 94
Joined: Wed Nov 28, 2012 5:55 pm
DBs Used: DBF, mySQL, Mariadb, postgreSQL, Oracle, Db2, Interbase, Firebird, and SQLite
Location: Buenos Aires - Argentina
Been thanked: 7 times

Post by Ismach » Wed Nov 14, 2018 1:14 am

Buscando un RECORDSET

Code: Select all

&&--------------------------------------------------------------------&&
&&--------------------------------------------------------------------&&
STATIC FUNCTION CA_BuscaCaja()

    LOCAL cSuc
    LOCAL cRaiz
    LOCAL oRs
    LOCAL cSQL

    LOCAL k, nLen

    LOCAL cFila0, cFila1,  cFila2, cFila3, cFila4, cFila5
    LOCAL cFila6, cFila7,  cFila8, cFila9, cFila10, cFila11
    LOCAL cFila12, cFila13, cFila14, cFila15, cFila16
    LOCAL cFila17, cFila18, cFila19

    IF  EMPTY(fmCajas.txtA0090.Value)
        MsgInfo('Ingrese al menos la Sucursal')
        fmCajas.txtA0090.SetFocus
        RETURN NIL
    ENDIF
    //-----------------------------------------------------------//
    cSuc := fmCajas.txtA0090.Value
    IF  LEN(cSuc) > 0
        cSuc := ALLTRIM(cSuc)
    ENDIF
    //-----------------------------------------------------------//
    cRaiz := fmCajas.txtA1010.Value
    IF  LEN(cRaiz) > 0
        cRaiz := ALLTRIM(cRaiz)
    ENDIF
    //-----------------------------------------------------------//
    IF  LEN(cRaiz) > 0
        cSQL := SQLSelCACliente(cSuc,cRaiz)
    ELSE
        cSQL := SQLSelCASucursal(cSuc)
    ENDIF

    CA_BusCliente()


    oRs := TOleAuto():New("adodb.recordset")
    oRs:ActiveConnection(oOra)
    oRs:CursorLocation(3)
    oRs:CursorType := adOpenStatic
    oRs:CursorLocation := adUseClient
    oRs:LockType := adLockOptimistic

    oRs:Open(cSQL, oOra ,adOpenKeyset, adLockOptimistic)
    IF  oRs:RecordCount = 0

        MsgInfo( "Producto Caja de Seguridad Inexistente", SISTEMA )
        fmCajas.txtA0090.Setfocus
        RETURN NIL

    ENDIF

    nLen:=oRs:RecordCount
    fmCajas.gCheq12.DeleteAllItems

    FOR k:=1 TO nLen Step 1

         cFila0  := ALLTRIM(oRs:Fields(0):value)     //  0  Suc.(Ky)
         cFila1  := ALLTRIM(oRs:Fields(1):value)     //  1  Raiz(Ky)
         cFila2  := oRs:Fields(2):value                   //  2  Prod(Ky)
         cFila3  := ALLTRIM(oRs:Fields(3):value)     //  3  Nro.(Ky)
         cFila4  := oRs:Fields(4):value     //  4  Valor Real
         cFila5  := oRs:Fields(5):value     //  5  Cod.Com.
         cFila6  := oRs:Fields(6):value     //  6  Bonif.
         cFila7  := oRs:Fields(7):value     //  7  Vto.Bonif.
         cFila8  := oRs:Fields(8):value     //  8  ComCobro
         cFila9  := oRs:Fields(9):value     //  9  Cuota 1
         cFila10 := oRs:Fields(10):value    //  10 NroCuota
         cFila11 := oRs:Fields(11):value    //  11 Año
         cFila12 := ALLTRIM(oRs:Fields(12):value) + "-" + ;   //  12 SucDeb
                    ALLTRIM(oRs:Fields(13):value) + "-" + ;
                    ALLTRIM(oRs:Fields(14):value) + "-" + ;
                            oRs:Fields(15):value
         cFila16 := ALLTRIM(oRs:Fields(16):value)    //  16 ModoFac
         cFila17 := oRs:Fields(17):value    //  17 NumeroCaja
         cFila18 := ALLTRIM(oRs:Fields(18):value)    //  18 Estado
         cFila19 := oRs:Fields(19):value    //  19 Suc.de la Caja

         fmCajas.gCheq12.ADDITEM( {cFila0,cFila1,cFila2,cFila3,cFila4,cFila5, ;
                                   cFila6,cFila7,cFila8,cFila9,cFila10,cFila11, ;
                                   cFila12,cFila16,cFila17,cFila18,cFila19})
         oRs:MoveNext()

    Next k
    oRs:=Nil

RETURN NIL

Post Reply