hmg and oracle

Creative ideas/suggestions for HMG

Moderator: Rathinagiri

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

Re: hmg and oracle

Post by Ismach » Fri Mar 01, 2019 4:23 pm

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



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 » Fri Mar 01, 2019 4:40 pm

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

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

Post by dragancesu » Tue Mar 05, 2019 12:44 pm

It's OK

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

User avatar
jairpinho
Posts: 314
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Has thanked: 3 times
Been thanked: 10 times
Contact:

Post by jairpinho » Tue Mar 05, 2019 3:25 pm

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
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br

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

Post by dragancesu » Tue Mar 05, 2019 4:46 pm

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

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 » Mon Mar 11, 2019 6:50 pm

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;"

User avatar
nguyenchiduc
Posts: 78
Joined: Sat Nov 13, 2010 7:27 am
Been thanked: 2 times

Post by nguyenchiduc » Mon May 13, 2019 1:47 pm

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

Post Reply