Page 3 of 3

Re: hmg and oracle

Posted: Fri Mar 01, 2019 4:23 pm
by Ismach
hello, can you chek this code ? Ojo! Your Table, Base, User and Pass

Code: Select all


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

static cHost  := "DBINDIO53"  // NOMBRE DE LA BASE OJO VER TNS NAMES
static cUser  := "scot"       // USER
static cPass  := "scot"       // PASS
static cProv  := "MSDAORA.1"  // PRIVIDER OF WINDOW

//--------------------------------------------------------------------//
FUNCTION Main()

     PRIVATE aItemDept :={}

     Public oOra    AS OBJECT

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

     SET INTERACTIVECLOSE ON

     Oracle_Connect()

     DEFINE WINDOW wifmain AT 10, 10 ;
            WIDTH 800 HEIGHT 600 ;
            TITLE  "Table DEPT" ;
            MAIN

            ON KEY ALT+F4 ACTION wifmain.release

            @050,020 GRID Grid_1 WIDTH 500 HEIGHT 400 ;
                     WIDTHS {60,200,200};
                     HEADERS {'Dept','Dname','Loc'}

	END WINDOW

	LoadData()
	ACTIVATE WINDOW wifmain

RETURN

FUNCTION Oracle_Connect()

     oOra := TOLEAuto():New('ADODB.Connection')
     oOra:ConnectionString="Provider=" + cProv + "; " + "Data Source=" + cHost + "; " + "User ID=" + cUser + "; " + "Password=" + cPass + ";"
     oOra:Open()

RETURN NIL

FUNCTION LoadData()

   LOCAL oRs
   LOCAL cFila0,cFila1,cFila2

   IF ( oRs := TOleAuto():New("ADODB.Recordset") ) != NIL
        oRs:Open( "SELECT CODUSER, NAMEUSER, PASSWRD FROM TCCUSER", oOra )
        DO WHILE ! oRs:EOF

	   cFila0  := oRs:Fields(0):value
           cFila1  := oRs:Fields(1):value
           cFila2  := oRs:Fields(2):value

           wifmain.grid_1.additem( {cFila0,cFila1,cFila2} )

           oRs:MoveNext()
        ENDDO
	oRs:Close()
   ENDIF

RETURN NIL



Re: hmg and oracle

Posted: Fri Mar 01, 2019 4:40 pm
by Ismach
In oracle sometimes don't works

Code: Select all

oRs:Open( "SELECT * FROM TCCUSER", oOra )
you must change to:

Code: Select all

oRs:Open( "SELECT column1, column2, column3, column4,column6,columnblablabla FROM TCCUSER", oOra )
If you define that oRs is public, it is advisable not to declare it as local

Re: hmg and oracle

Posted: Tue Mar 05, 2019 12:44 pm
by dragancesu
It's OK

My problem is: work with 10g, don't work with 11xe

Re: hmg and oracle

Posted: Tue Mar 05, 2019 3:25 pm
by jairpinho
dragancesu wrote:
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/
what type of database will access

Re: hmg and oracle

Posted: Tue Mar 05, 2019 4:46 pm
by dragancesu
http://vrogier.github.io/ocilib/

Oracle can be connected in several ways

OCILIB is natural
ODBC and ADO are over MS drivers

p.s. I tried several ways, but the combination of HMG-Oracle does not work properly (harbur 3.2 only supports ocilib)
HMG-MySQL works fine, the connection in a natural way, you do not need MS layer driver

Re: hmg and oracle

Posted: Mon Mar 11, 2019 6:50 pm
by Ismach
Well... I use win7 client and Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
mounted on Unix Aix and it works very well with ADODB with msdaora.dll._

Do not use window 10 yet, but I think the connector is:

Code: Select all

    ccConnString := "Provider=OraOleDb.Oracle;" + ;
                    "Data Source=MyInstance;" + ;
                    "User Id=MyLogin;" + ;
                    "Password=MyPass;" + ;
                    "PLSQLRSet=1;"

Re: hmg and oracle

Posted: Mon May 13, 2019 1:47 pm
by nguyenchiduc
Hello everybody!

In my experience use ODBC as fast as Oracle SQL Developer. But use Sqlplus by command slow than ODBC and Oracle SQL Developer

My HMG program export database from Oracle (LAN) to .CSV format and then import to Mysql (Internet). Every 30 minutes, Each time export 150000 records, about 50 to 60 seconds for one time update from Oracle to Mysql. My computer is Intel Core i3 CPU - 4G RAM @ 3.20GHz, Window 32 bit

(I used to buy an tool export from Oracle (LAN) to Mysql (Internet) running very slowly, so I wrote it myself in HMG)

------------------------------------------------------------
// Connect to Oracle not use tnsnames.ora
nConnection := RDDINFO( RDDI_CONNECT, { "ODBC", "Driver={Oracle in OraClient12Home1};dbq=XXX.XXX.XXX.XXX:1521/vbs;UID=user;PWD=password;" } )
query := "select * from bill.customer"
dBUseArea( .T., "SQLMIX",query,"result", .T., .F.)

...
...
write all records to .csv file
...
...

// IMPORT TO MYSQL
connect := connect2db("XXX.XXX.XXX.XXX", "user", "password","database")
query := "CREATE TABLE database.temp ..."
result := miscsql(connect,query)
query := "LOAD DATA LOCAL INFILE 'C:\\update\\file\\bill.csv' INTO TABLE temp CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '|' LINES TERMINATED BY '\r\n'"
result := miscsql(connect,query)
...
...