Oracle ODBC

[melliott42]

Hello,

I am considering HMG for some large projects. I have been working with ...\SAMPLES\RDD.SQL\mysql and don't quite have it.

1. Does anyone have an actual working code snippet of HMG connecting to an Oracle database using ODBC?

2. Where are the docs for RDDINFO, RDDI_CONNECT and DBUSEAREA() with ODBC?

Thanks very much,

Michael

[melliott42]

I have taken the basic hello world window tutor code and am adding the ODBC code into it to find where it breaks.

The moment I add "REQUEST SDDMY, SQLMIX" it no longer compiles nor throws a compiler error.

Any suggestions?


[My Complete Code]
#include "hmg.ch"
#include "dbinfo.ch"
#include "error.ch"

#define DBI_QUERY 1001

#define RDDI_CONNECT 1001
#define RDDI_DISCONNECT 1002
#define RDDI_EXECUTE 1003
#define RDDI_ERROR 1004
#define RDDI_ERRORNO 1005
#define RDDI_NEWID 1006
#define RDDI_AFFECTEDROWS 1007
#define RDDI_QUERY 1008

REQUEST SDDMY, SQLMIX

Function Main
DEFINE WINDOW Win_1 ;
AT 0,0 ;
WIDTH 400 ;
HEIGHT 200 ;
TITLE 'Hello ODBC!' ;
MAIN
END WINDOW
ACTIVATE WINDOW Win_1
Return

[melliott42]

In looking at the examples it seems I am way off the mark. I think maybe a derivative of the MS Access example is closer.

You know guys if we get a good HMG example using Oracle this could be a really big thing.

[kleinki]

2. Where are the docs for RDDINFO, RDDI_CONNECT and DBUSEAREA() with ODBC?

Sorry, melliott42, I am not going to answer your questions, but rather add my question"to yours/"vote for yours to be answered".

So to all the gurus out there (e.g. Roberto, Sudip, Rathi, Mol, etc.), where do I (and others like melliott42) find a manual/info on RDD* & DBUseArea() with ODBC?

Please, I am also struggling to access an ODBC data source with intention of doing CRUD (CReate, Update, Delete) operations on it.

Thanking you all in advance for any information/help provided.

Regards

[apais]

Extracted from Harbour sources...

Code: Select all

/*
 * $Id: test1.prg 14271 2010-04-05 09:16:23Z vszakats $
 */

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

REQUEST SDDODBC, SQLMIX

PROC main()
   RDDSETDEFAULT( "SQLMIX" )
   SET( _SET_DATEFORMAT, "yyyy-mm-dd" )
   ? "Connect:", RDDINFO( RDDI_CONNECT, { "ODBC", "DBQ=" + hb_DirBase() + "..\..\hbodbc\tests\test.mdb;Driver={Microsoft Access Driver (*.mdb)}" } )
   ? "Use:", DBUSEAREA( .T.,, "select * from test", "test" )
   ? "Alias:", ALIAS()
   ? "DB struct:", HB_VALTOEXP( DBSTRUCT() )
   INKEY( 0 )
   BROWSE()

   INDEX ON FIELD->SALARY TO salary
   DBGOTOP()
   BROWSE()
   DBCLOSEAREA()
RETURN

and...

Code: Select all

/*
 * $Id: test2.prg 14028 2010-03-01 01:34:52Z vszakats $
 */

#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

Hope this is usefull for you.
I'm not an odbc user.

Angel

[apais]

This other exxample comes fromHarbour sources also.
It doesn't use odbc but a direct connection method called OCI.

Code: Select all

/*
 * $Id: test1.prg 14580 2010-05-25 11:36:12Z vszakats $
 */

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

REQUEST SDDOCI, SQLMIX

PROCEDURE Main()
   LOCAL tmp

   RDDSETDEFAULT( "SQLMIX" )
   SET( _SET_DATEFORMAT, "yyyy-mm-dd" )

   AEVAL( rddList(), {| X | QOut( X ) } )

   ? "-1-"
   ? "Connect:", tmp := RDDINFO( RDDI_CONNECT, { "OCILIB", "ORCL", "scott", "tiger" } )
   IF tmp == 0
      ? "Unable connect to the server"
   ENDIF
   ? "-2-"
   ? "Use:", DBUSEAREA( .T.,, "select * from emp", "emp" )
   ? "-3-"
   ? "Alias:", ALIAS()
   ? "-4-"
   ? "DB struct:", HB_VALTOEXP( DBSTRUCT() )
   ? "-5-"
   FOR tmp := 1 TO FCount()
      ? FIELDNAME( tmp ), HB_FIELDTYPE( tmp ), HB_FIELDLEN( tmp ), HB_FIELDDEC( tmp )
   NEXT
   ? "-6-"
   INKEY( 0 )
   BROWSE()

   INDEX ON FIELD->SAL TO salary
   DBGOTOP()
   BROWSE()
   DBCLOSEAREA()

   RETURN

HTH
Angel

[melliott42]

I think it is really a good thing for HMG that we tackle Oracle. If we can get it stable and functional I'll include it in my next Oracle book.

I think the OCI option may be the most potent but at this point I'll any that works...so far I am struggling to get ANY of them to work. Not for lack of effort.

Thanks for all your posts so far guys!

[melliott42]

I have bee using variations of the ODBC option without success.

nConnection := RDDINFO( RDDI_CONNECT, { "ODBC", "Server=SRV1;Driver={Microsoft ODBC for Oracle};dsn=DB1;User=test;" } )

I get the below error from the above line:
Microsoft ODBC for Oracle
The Oracle client and networking components were not found. ...

I have also tried entering the database and other DSN info. I have verified that the DSN info matches the ODBC.ini (registry) settings. I know the ODBC DSN is working because I can connect using it with Access 2007. I have also tried Oracle ODBC driver. This is a Windows 7 system.

Any suggestions?

Thanks,

Michael

[melliott42]

I am connecting fine using VB6 too. Tried using variations of my VB6 connection string without success though (boo).

Hopefully the lack of docs and working Oracle examples can be addressed. I'd be glad to supplement Oracle example code once we get the first one working.

FYI VB6 converted snippet:

Code: Select all

    LOCAL sUserName, sPassword, sHost, sSID, sPort, sProvider,sSource, sConnection

    sUserName    := "scott"
    sPassword    := "tiger"
    sHost        := "192.168.1.42"
    sSID         := "DB1"
    sPort        := "1521"
    sProvider    := "MSDAORA"
    sSource      := "(description=(address=(protocol=TCP)(host=" + sHost + ")(port=" + sPort + "))(connect_data=(sid=" + sSID + ")))"
    sConnection := "Provider=" + sProvider + ";Password=" + sPassword + ";User ID=" + sUserName + ";Data Source=" + sSource

    //msgbox(sConnectionString)

    RDDSETDEFAULT( "SQLMIX" )
    SET( _SET_DATEFORMAT, "yyyy-mm-dd" )
    nConnection := RDDINFO( RDDI_CONNECT, {"ODBC", sConnection} )

[melliott42]

Going once, going twice...

I don't want to use .net. PLEASE!

How 'bout just the URL to the docs on RDDINFO()?