Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » In Oracle9 and higher, TIMESTAMP data type will cause ORA-1402 exception w/ Oracle8 Oci8.cpp
In Oracle9 and higher, TIMESTAMP data type will cause ORA-1402 exception w/ Oracle8 Oci8.cpp [message #30313] Fri, 24 December 2010 01:35 Go to next message
alendar is currently offline  alendar
Messages: 47
Registered: January 2010
Location: Idaho, USA
Member
Hi,

I was getting a "ORA-01406: fetched column value was truncated" when I used the Oracle8 session driver, but not with the Oracle7 code. I am connecting to an Oracle 11 database.

The bug appears when a column of datatype TIMESTAMP is referenced in an Execute command, and a Fetch command is called.
Here's the client code I wrote that triggered the exception:

if (!cursor.Execute(sql)) {
	HandleDbError(ACTNDB_EXECSEL, cursor, &sql);
	return false;
}

int colCount = cursor.GetColumns();

for (int i = 0; i < colCount; i++) {
	const SqlColumnInfo& ci = cursor.GetColumnInfo(i);
	int w = GetTextSize(ci.name, StdFont()).cx + 14;
	outputGrid->AddColumn(ci.name).Width(w);
	cw[i] = w;
}

Progress pi;
pi.SetText("Fetched %d line(s)");

outputGrid->Ready(false);

while(cursor.Fetch()) {

...

}

if (cursor.WasError()) {
	HandleDbError(ACTNDB_EXECSEL, cursor, &sql);
	return false;
}



The error occurs at the Fetch, not at the Execute.

Test SQL was:
select t.SRCS_FD_TS from ...


I traced the DLL calls to:

GetProcAddress(0x025D0000 [ORACLIENT10.DLL], "OCIDefineByPos") called from "OCI.DLL" at address 0x02572E69 and returned 0x025D68CE.
GetProcAddress(0x025D0000 [ORACLIENT10.DLL], "OCIStmtFetch") called from "OCI.DLL" at address 0x02572F95 and returned 0x025D6962.
GetProcAddress(0x025D0000 [ORACLIENT10.DLL], "OCIErrorGet") called from "OCI.DLL" at address 0x02573295 and returned 0x025D6B1E.

OCIDefineByPos is called in Oracle\Oci8.cpp in OCI8Connection::GetColumnInfo().

Snippet from Oci8.cpp:
	...
	switch(type) {
	...
	case SQLT_CLOB:
		ii.type = ORA_CLOB_V;
		AddColumn(SQLT_CLOB, sizeof(OCILobLocator *));
		blob = true;
		break;
	case SQLT_RDD:
		ii.type = STRING_V;
		AddColumn(SQLT_STR, 64);
		break;

	default:
		ii.type = STRING_V;
		AddColumn(SQLT_STR, ii.width ? ii.width + 1 : longsize);
		break;
	}

	Item& c = column.Top();
	oci8.OCIDefineByPos(stmthp, &c.define, errhp, i,
	blob ? (void *)&c.lob : (void *)c.Data(), blob ? -1 : c.total_len,
	c.type, c.ind, c.len, NULL, OCI_DEFAULT);
}


The column type returned from
oci8.OCIAttrGet(pd, OCI_DTYPE_PARAM, &type, NULL,  OCI_ATTR_DATA_TYPE, errhp);

is 187, or SQLT_TIMESTAMP.

Since type 187 is not trapped, the default action is to treat as string, which is fine, except ii.width returns 11, which is probably the binary width of the data. The string width varies according to Locale settings. On mine the output length is 28, but with a 4-digit year it would be 30.

Here's a sample value:
03-DEC-10 12.00.00.000000 AM

I added a case path and it corrected the problem:
	case SQLT_TIMESTAMP: // type 187
		ii.type = STRING_V;
		AddColumn(SQLT_STR, 30); 
		break;


My Oracle client is 10.2.0.1 (dated 9/30/2007 8:09a) on Windows XP SP3.

Please add patch if you think it would help anyone else.


cd7651feeb698f6ac6cec1f6deda5e5b
Re: In Oracle9 and higher, TIMESTAMP data type will cause ORA-1402 exception w/ Oracle8 Oci8.cpp [message #30346 is a reply to message #30313] Sat, 25 December 2010 20:26 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Patch applied, thank you!

Mirek
Re: In Oracle9 and higher, TIMESTAMP data type will cause ORA-1402 exception w/ Oracle8 Oci8.cpp [message #30434 is a reply to message #30346] Thu, 30 December 2010 21:27 Go to previous message
alendar is currently offline  alendar
Messages: 47
Registered: January 2010
Location: Idaho, USA
Member
Thank you for applying it. I'm not comfortable in the svn yet, so I won't be applying any patches myself for a while.

cd7651feeb698f6ac6cec1f6deda5e5b
Previous Topic: Strange exception raising with SQL.ExecuteX()
Next Topic: SQLite3, ODBC and UTF-8
Goto Forum:
  


Current Time: Thu Mar 28 10:49:23 CET 2024

Total time taken to generate the page: 0.01365 seconds