FIX: Oracle ODBC Driver Appends Trailing Zero to Numeric Columns (244554)

MORE INFORMATION

DSN=OracleServer;UID=scott;PWD=tiger;StripTrailingZero=1;

Driver=Microsoft ODBC for Oracle;Server=OracleServer;UID=scott;PWD=tiger;StripTrailingZero=1;

Manual Installation

1. Close or stop any applications or services that are using Msorcl32.dll. This may include Internet Information Server (IIS), Microsoft Transaction Server (MTS), and any ODBC, ADO, or OLE DB applications.
2. Download the hotfix version of Msorcl32.dll into a temporary directory.
3. Locate and rename the current version of Msorcl32.dll, which is in the \Winnt\System32 folder for Windows NT computers, and in the \Windows\System folder for Windows 9x computers.
4. Copy the hotfix version of Msorcl32.dll into the same location, and then restart your services and applications.

Steps to Reproduce Behavior

1. Use the ODBC Administrator to configure an Oracle datasource so that the "Truncate insignificant trailing zero when using null or odd scale" checkbox is cleared (not set).
2. Copy the following code into a console application (modifying your Oracle server, username and password as necessary), compile and run it.
3. Notice that several values have extra zeros appended to them. In particular, all of the odd scale fields have extra zeros, even when this puts the number of decimal digits beyond the defined size. Additionally, the no-scale fields will have zeros appended to create an even number of decimal digits.
4. Use the ODBC Administrator to modify the Oracle datasource so that the "Truncate insignificant trailing zero when using null or odd scale" checkbox is checked, and run the code again. Notice that all values appear as they should.

#include "stdafx.h"
#include <windows.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

void CreateTable(HSTMT);

main()
{

	RETCODE rc;
	HENV henv;
	HDBC hdbc;
	HSTMT hstmt;

//	Variables for SQLConnect
	char * dsn = "OracleServer";
	char * uid = "scott";
	char * pwd = "tiger";

//	Variables for SQLBindCol
	char m_charOddScale[20] = {"\0"};
	char m_charNoScale[20] = {"\0"};	
	long sqlnts = SQL_NTS;

//	miscellaneous variables	
	char strOutput[40] = {"\0"};
	char * SQLStr = "select OddScale, NoScale from ScaleTable";


//	Allocate ODBC handles and connect to Oracle
	rc = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
	rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3,0);
	rc = SQLAllocHandle(SQL_HANDLE_DBC,henv, &hdbc);

	rc = SQLConnect(hdbc, (unsigned char *)dsn,
		SQL_NTS, (unsigned char *)uid,
		SQL_NTS, (unsigned char *)pwd, SQL_NTS);

	rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

//	Table creation function
	CreateTable(hstmt);


//	Execute the statement and bind the column as SQL_C_CHAR
	rc = SQLExecDirect(hstmt, (unsigned char *)SQLStr, SQL_NTS);

	rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, m_charOddScale, 20, &sqlnts);
	rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, m_charNoScale, 20, &sqlnts);

	printf("\nOddScale (3) and NoScale as SQL_C_CHAR\n\n");

//	Fetch records and print the results
	while (SQLFetch(hstmt) != SQL_NO_DATA_FOUND)
	{

	memset(strOutput,' ',sizeof(strOutput));
	strncpy(strOutput, m_charOddScale, strlen(m_charOddScale));
	strncpy(&strOutput[15],m_charNoScale, strlen(m_charNoScale)+1);

	printf("\t%s\n",strOutput);

	}

//	Cleanup
	SQLFreeStmt(hstmt, SQL_CLOSE);
	SQLFreeStmt(hstmt, SQL_DROP);
	SQLDisconnect(hdbc);
	SQLFreeConnect(hdbc);
	SQLFreeEnv(henv);
	return(TRUE);
};

//------- CreateTable() ----------------

void CreateTable(HSTMT hstmt)
{

	RETCODE rc = 0;
	char SqlStatements[6][70] = 
	{"Drop table ScaleTable",
	 "Create table ScaleTable (OddScale number(10,3), NoScale number)",
	 "Insert into ScaleTable values (1.1, 1.1)",
	 "Insert into ScaleTable values (1.12, 1.12)",
	 "Insert into ScaleTable values (1.123, 1.123)",
	 "Insert into ScaleTable values (1.005, 1.005)"};

	rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[0], SQL_NTS);
	SQLFreeStmt(hstmt, SQL_CLOSE);
	rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[1], SQL_NTS);
	SQLFreeStmt(hstmt, SQL_CLOSE);
	rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[2], SQL_NTS);
	SQLFreeStmt(hstmt, SQL_CLOSE);
	rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[3], SQL_NTS);
	SQLFreeStmt(hstmt, SQL_CLOSE);
	rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[4], SQL_NTS);
	SQLFreeStmt(hstmt, SQL_CLOSE);
	rc = SQLExecDirect(hstmt, (unsigned char *)SqlStatements[5], SQL_NTS);
	SQLFreeStmt(hstmt, SQL_CLOSE);


}