Odbc Driver For Oracle On Windows 2008 - bicycle-progs61’s diary

21.2.1.1 What is the Oracle ODBC Driver. The Oracle ODBC Driver enables ODBC applications on Microsoft Windows, as well as UNIX platforms like Linux, Solaris, and.

Https://forums.oracle.com/forums/thread.jspa.messageID 10701232

see 897635

I finally got it to work. I ve concluded that the instant client downloads don t work for Win2k8 R2. The following worked:

download the full client for oracle 11.2.0.1.0 for 64 bit windows and install

download the full client for oracle 11.2.0.1.0 for 32 bit windows and install

Use the odbc administrator in the syswow64 directory to create a DSN pointing to oracle.

Works fine.

I created a SSIS package to pull data from Oracle database to SQL server database. I have set up ODBC connection succesfully on my local machine and on the server, i.

21.4.12.1 Unicode Support Within the ODBC Environment

The Microsoft or unixODBC ODBC Driver Manager Driver Manager makes all ODBC drivers, regardless if they support Unicode, appear as if they are Unicode compliant. This allows ODBC applications to be written independent of the Unicode capabilities of underlying ODBC drivers.

The extent to which the Driver Manager can emulate Unicode support for ANSI ODBC drivers is limited by the conversions possible between the Unicode data and the local code page. Data loss is possible when the Driver Manager is converting from Unicode to the local code page. Full Unicode support is not possible unless the underlying ODBC driver supports Unicode. The Oracle ODBC Driver provides full Unicode support.

21.4.12.2 Unicode Support in ODBC API

The ODBC API supports both Unicode and ANSI entry points using the W and A suffix convention. An ODBC application developer need not explicitly call entry points with the suffix. An ODBC application that is compiled with the UNICODE and _UNICODE preprocessor definitions generates the appropriate calls. For example, a call to SQLPrepare is compiled as SQLPrepareW.

The C data type, SQL_C_WCHAR, was added to the ODBC interface to allow applications to specify that an input parameter is encoded as Unicode or to request column data returned as Unicode. The macro SQL_C_TCHAR is useful for applications that must be built as both Unicode and ANSI. The SQL_C_TCHAR macro compiles as SQL_C_WCHAR for Unicode applications and as SQL_C_CHAR for ANSI applications.

The SQL data types, SQL_WCHAR, SQL_WVARCHAR, and SQL_WLONGVARCHAR, have been added to the ODBC interface to represent columns defined in a table as Unicode. Potentially, these values are returned from calls to SQLDescribeCol, SQLColAttribute, SQLColumns, and SQLProcedureColumns.

Unicode encoding is supported for SQL column types NCHAR, NVARCHAR2, and NCLOB. Also, Unicode encoding is also supported for SQL column types CHAR and VARCHAR2 if the character semantics are specified in the column definition.

The ODBC Driver supports these SQL column types and maps them to ODBC SQL data types.

Table 21-8 lists the supported SQL data types and the equivalent ODBC SQL data type.

21.4.12.3 Unicode Functions in the Driver Manager

The Driver Manager performs the following functions when it detects that the underlying ODBC driver does not support Unicode:

Convert Unicode function calls to ANSI function calls before calling the ANSI ODBC driver. String arguments are converted from Unicode to the local code page. For example, a call to SQLPrepareW is converted to call SQLPrepare. The text of the SQL statement parameter is converted from Unicode to the local code page.

Convert return parameters that are character data from the local code page to Unicode. For example, returning the column name through SQLColAttribute.

Convert data from the local code page to Unicode for columns bound as SQL_C_WCHAR.

Convert data from Unicode to the local code page for input parameters bound as SQL_C_WCHAR.

21.4.12.4 SQLGetData Performance

The SQLGetData function allows an ODBC application to specify the data type to receive a column as after the data has been fetched. OCI requires the Oracle ODBC Driver to specify the data type before it is fetched. In this case, the Oracle ODBC Driver uses the knowledge it has about the data type of the column as defined in the database to determine how to best default to fetching the column through OCI.

If a column that contains character data is not bound by SQLBindCol, the Oracle ODBC Driver must determine if it must fetch the column as Unicode or as the local code page. The driver could default to receiving the column as Unicode, however, this may result in as many as two unnecessary conversions. For example, if the data were encoded in the database as ANSI, there would be an ANSI to Unicode conversion to fetch the data into the Oracle ODBC Driver. If the ODBC application then requested the data as SQL_C_CHAR, there would be an additional conversion to revert the data back to its original encoding.

The default encoding of the Oracle client is used when fetching data. However, an ODBC application can overwrite this default and fetch the data as Unicode by binding the column or the parameter as the WCHAR data type.

21.4.12.5 Unicode Samples

As the Oracle ODBC Driver itself was implemented using TCHAR macros, Oracle recommends that ODBC application programs use TCHAR to take advantage of the driver.

The following links are program examples showing how to use TCHAR, which becomes the WCHAR data type in case you compile with UNICODE and _UNICODE.

Example 1: Connection to Database

No difference other than specifying Unicode literals for SQLConnect.

HENV envHnd;

HDBC conHnd

;

HSTMT stmtHnd;

RETCODE rc;

rc SQL_SUCCESS;

// ENV is allocated

rc SQLAllocEnv envHnd ;

// Connection Handle is allocated

rc SQLAllocConnect envHnd, conHnd ;

rc SQLConnect conHnd, _T stpc19, SQL_NTS, _T scott, SQL_NTS, _T tiger,

SQL_NTS ;

if conHnd

SQLFreeConnect conHnd ;

if envHnd

SQLFreeEnv envHnd ;

Example 2: Simple Retrieval

The following example retrieves the employee names and the job titles from the EMP table. With the exception that you must specify TCHAR compliant data to every ODBC function, there is no difference to the ANSI case. If the case is a Unicode application, you have to specify the length of the buffer to the BYTE length when you call SQLBindCol for example, sizeof ename .

Execute SQL, bind columns, and Fetch.

Procedure:

SQLExecDirect

SQLBindCol

SQLFetch

static SQLTCHAR sqlStmt _T SELECT ename, job FROM emp ;

SQLTCHAR ename 50 ;

SQLTCHAR job 50 ;

SQLINTEGER enamelen, joblen;

_tprintf _T Retrieve ENAME and JOB using SQLBindCol 1/n s /n, sqlStmt ;

// Step 1: Prepare and Execute

rc SQLExecDirect stmtHnd, sqlStmt, SQL_NTS ; // select

checkSQLErr envHnd, conHnd, stmtHnd, rc ;

// Step 2: Bind Columns

rc SQLBindCol stmtHnd,

SQL_C_TCHAR,

ename,

sizeof ename,

enamelen ;

job,

sizeof job,

joblen ;

// Step 3: Fetch Data

rc SQLFetch stmtHnd ;

if rc SQL_NO_DATA

break;

_tprintf _T ENAME s, JOB s/n, ename, job ;

while 1 ;

_tprintf _T Finished Retrieval/n/n ;

Example 3: Retrieval Using SQLGetData Binding After Fetch

This example shows how to use SQLGetData. For those who are not familiar with ODBC programming, the fetch is allowed before binding the data using SQLGetData, unlike in an OCI program. There is no difference to the ANSI application in terms of Unicode-specific issues.

SQLGetData

static SQLTCHAR sqlStmt _T SELECT ename,job FROM emp ; // same as Case 1.

_tprintf _T Retrieve ENAME and JOB using SQLGetData/n s /n, sqlStmt ;

if rc. SQL_SUCCESS

_tprintf _T Failed to allocate STMT/n ;

goto exit2;

// Step 2: Fetch

if rc SQL_NO_DAT

// Step 3: GetData

rc SQLGetData st

mtHnd,

SQLPOINTER ename,

NULL ;

rc SQLGetData stmtHnd,

SQL_C_TCHAR,

SQLPOINTER job,

Example 4: Simple Update

This example shows how to update data. Likewise, the length of data for SQLBindParameter has to be specified with the BYTE length, even in the case of a Unicode application.

SQLPrepare

SQLBindParameter

SQLExecute

static SQLTCHAR sqlStmt _T INSERT INTO emp empno,ename,job VALUES. .. ;

static SQLTCHAR empno _T 9876 ; // Emp No

static SQLTCHAR ename _T ORACLE ; // Name

static SQLTCHAR job _T PRESIDENT ; // Job

_tprintf _T Insert User ORACLE using SQLBindParameter/n s /n, sqlStmt ;

// Step 1: Prepar

rc SQLPrepare stmtHnd, sqlStmt, SQL_NTS ; // select

// Step 2: Bind Parameterrc SQLBindParameter stmtHnd,

SQL_PARAM_INPUT,

SQL_DECIMAL,

4, // 4 digit

SQLPOINTER empno,

rc SQLBindParameter stmtHnd,

SQL_CHAR,

lstrlen ename sizeof TCHAR,

lstrlen job sizeof TCHAR,

// Step 3: Execute

rc SQLExecute stmtHnd ;

Example 5: Update and Retrieval for Long Data CLOB

This example may be the most complicated case to update and retrieve data for long data, like CLOB, in Oracle. Because the length of data must be the BYTE length, lstrlen TCHAR data sizeof TCHAR is needed to derive the BYTE length.

SQLParamData

SQLPutData

static SQLTCHAR sqlStmt1 _T INSERT INTO clobtbl clob1 VALUES. ;

static SQLTCHAR sqlStmt2 _T SELECT clob1 FROM clobtbl ;

SQLTCHAR clobdata 1001 ;

SQLTCHAR resultdata 1001 ;

SQLINTEGER ind SQL_DATA_AT_EXEC;

SQLTCHAR bufp;

int clobdatalen, chunksize, dtsize, retchklen;

_tprintf _T Insert CLOB1 using SQLPutData/n s /n, sqlStmt1 ;

// Set CLOB Data

int i;

SQLTCHAR ch;

for i 0, ch _T A ; i sizeof clobdata /sizeof SQLTCHAR ; i, ch

if ch _T Z

ch _T A ;

clobdata i ch;

clobdata sizeof clobdata /sizeof SQLTCHAR -1 _T /0 ;

clobdatalen lstrlen clobdata ; // length of characters

chunksize clobdatalen / 7; // 7 times to put

// Step 1: Prepare

rc SQLPrepare stmtHnd, sqlStmt1, SQL_NTS ;

// Step 2: Bind Parameter with SQL_DATA_AT_EXEC

SQL_LONGVARCHAR,

clobdatalen sizeof TCHAR,

SQLPOINTER clobdata,

ind ;

// Step 4: ParamData initiation

rc SQLParamData stmtHnd, SQLPOINTER bufp ; // set value

for dtsize 0, bufp clobdata;

dtsize clobdatalen;

dtsize chunksize, bufp chunksize

int len;

if dtsize chunksize clobdatalen

len chunksize;

else

len clobdatalen-dtsize;

// Step 5: PutData

rc SQLPutData stmtHnd, SQLPOINTER bufp, len sizeof TCHAR ;

// Step 6: ParamData temination

rc SQLParamData stmtHnd, SQLPOINTER bufp ;

rc SQLFreeStmt stmtHnd, SQL_CLOSE ;

_tprintf _T Finished Update/n/n ;

rc SQLAllocStmt conHnd, stmtHnd ;

// Clear Result Data

memset resultdata, 0, sizeof resultdata ;

chunksize clobdatalen / 15; // 15 times to put

rc SQLExecDirect stmtHnd, sqlStmt2, SQL_NTS ; // select

for dtsize 0, bufp resultdata;

dtsize sizeof resultdata /sizeof TCHAR rc. SQL_NO_DATA;

dtsize chunksize-1, bufp chunksize-1

int len; // len should contain the space for NULL termination

if dtsize chunksize sizeof resultdata /sizeof TCHAR

len sizeof resultdata /sizeof TCHAR -dtsize;

SQLPOINTER bufp,

len sizeof TCHAR,

retchklen ;

if . _tcscmp resultdata, clobdata

_tprintf _T Succeeded../n/n ;

_tprintf _T Failed../n/n ;.

Before running the x64 installer of MySQL ODBC connector on Windows Server 2008 R2 you need to download an install Microsoft Visual C 2010 Redistributable.

21 Using the Oracle ODBC Driver

The Oracle ODBC Driver for Rdb follows the Microsoft ODBC 3.0 Software Development Kit SDK guidelines for manipulating and displaying real and double data types.

Oct 19, 2007 Nitin Aggarwal I work extensively on BI and Data warehousing technologies. Database tuning while managing ETL flows over huge volume databases.

How do I install an oracle ODBC-driver on Windows Server 2008R2?

Accessing ODBC Databases from Windows with Oracle Database Gateway for ODBC DG4ODBC The Oracle Database Gateway for ODBC DG4ODBC allows you to.

Dec 05, 2013 This feature is not available right now. Please try again later. Published on Dec 5, 2013. Category. Education; License. Standard YouTube License.

Is getting to your data slowing the growth of your business. Need an Oracle ODBC driver that is best in class. Our super fast Oracle ODBC driver simply allows any.

Michael McLaughlin s Technical Blog Somebody was trying to query Oracle via Microsoft Excel 2007, and didn t have an Oracle ODBC Data Source that enable them.