| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Oracle ODBC: Optional feature not implemented querying VIEW |
« View previous topic :: View next topic » |
| Author |
Message
|
| t603 |
 Posted: Wed Nov 14, 2012 5:23 am Post subject: Oracle ODBC: Optional feature not implemented querying VIEW |
 |
|
Voyager
Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe
|
Hello,
I have the problem using WebSphere Message Broker 7.0.x and Oracle 11.2.0.3.0 both running on AIX. I have a simple flow, where I have
| Code: |
| set OutputRoot.XMLNSC.root.step[i].databaseCurrentTime[] = passthru('select ct from RYBARST.myView'); |
I receive immediately the exception, which is below. But if I use
| Code: |
| set OutputRoot.XMLNSC.root.step[i].databaseCurrentTime[] = passthru('select ct from RYBARST.myTable'); |
everything is OK. Please, see the difference - once view, once table. Do You know, where is the problem and how to solve it on the database side? Does it concern with PASSTHRU documentation paragraph "Note: Do not use PASSTHRU to call stored procedures; instead, use the CALL statement because PASSTHRU imposes limitations (you cannot use output parameters, for example)." in http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fak05100_.htm? I hope not, because view definition should be invisible for the Broker and view is for the client the same as table, although current_timestamp used id view definition is not variable, but function. So I hope, that PASSTHRU simply passes string to database and process the ResultSet and not to do any own logic over string SQL statement. I do not want to "register" Oracle current_timestamp function in Broker, if possible. Table and view are defined as
| Code: |
--table
create table "RYBARST".myTable (ct timestamp);
insert into "RYBARST".myTable (ct) select current_timestamp from sys.dual;
--view
create view "RYBARST".myView (ct) as select current_timestamp from sys.dual;
|
One more thing - although in my ESQL code I am using PASSTHRU, there are DEFAULTPASSTHRU statement in Exception message. Hope, that this is integral interpretation of the Broker during "compiling" ESQL code.
Thank You in advance for Your answer. Stepan
| Code: |
ExceptionList
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbDataFlowNode.cpp
Line:INTEGER:1083
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:SRY_ConnectionPoolingPerformanceTest_A#FCMComposite_1_4
Label:CHARACTER:SRY_ConnectionPoolingPerformanceTest_A.Ora_a
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
RecoverableException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp
Line:INTEGER:627
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:SRY_ConnectionPoolingPerformanceTest_A#FCMComposite_1_4
Label:CHARACTER:SRY_ConnectionPoolingPerformanceTest_A.Ora_a
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.SRY_ConnectionPoolingPerformanceTest_Oracle.Main
Insert
Type:INTEGER:5
Text:CHARACTER:19.7
Insert
Type:INTEGER:5
Text:CHARACTER:SET OutputRoot.XMLNSC.root.step[i].databaseCurrentTime[ ] = DEFAULTPASSTHRU('select ct from RYBARST.myView');
DatabaseException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbOdbc.cpp
Line:INTEGER:351
Function:CHARACTER:ImbOdbcHandle::checkRcInner
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2321
Text:CHARACTER:Root SQL exception
Insert
Type:INTEGER:2
Text:CHARACTER:-1
Insert
Type:INTEGER:14
Text:CHARACTER:libbipodbc.a(odbc.so)
DatabaseException
File:CHARACTER:/build/S700_P/src/DataFlowEngine/ImbOdbc.cpp
Line:INTEGER:520
Function:CHARACTER:ImbOdbcHandle::checkRcInner
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2322
Text:CHARACTER:Child SQL exception
Insert
Type:INTEGER:5
Text:CHARACTER:HYC00
Insert
Type:INTEGER:2
Text:CHARACTER:0
Insert
Type:INTEGER:5
Text:CHARACTER:[IBM][ODBC Oracle Wire Protocol driver]Optional feature not implemented.
|
[/url] |
|
| Back to top |
|
 |
| mqsiuser |
| Posted: Wed Nov 14, 2012 6:03 am Post subject: Re: Oracle ODBC: Optional feature not implemented querying V |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
|
| Back to top |
|
|
| t603 |
| Posted: Wed Nov 14, 2012 6:38 am Post subject: |
|
|
Voyager
Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe
|
Hello,
thank You for Your answer. I read the thread You have pointed me twice and related documentation, espacially those on "Sample DataDirect odbc.ini file" http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/topic/com.ibm.etools.mft.doc/bh26070_.htm. Although I am not administrator of our broker, just programmer, I guess, that excerpt from our .odbc70.ini match sample from documentation and I bet, that we are using driver packed with the Broker. Or not?
How can I find, if we are using Oracle ODBC driver packed with the Broker when I am not the administrator (installer of the Broker)?
Or if it is not the matter of used version of drivers, could You please point me to the "switch", which turns "views" on and not only "tables"?
Thank Your. Stepan
Excerpt from our .odbc70.ini, which is used in my flow:
| Code: |
[MQSILOGDBRCE]
Driver=/mqsi70/ODBC/V6.0/lib/UKora24.so
QueryTimeout=300
Description=DataDirect 5.3 64bit Oracle Wire Protocol
HostName=scrambled
PortNumber=1526
SID=LOGDB
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
SQL_ATTR_APP_UNICODE_TYPE=SQL_DD_CP_UTF8 |
|
|
| Back to top |
|
|
| t603 |
| Posted: Wed Nov 14, 2012 7:20 am Post subject: |
|
|
Voyager
Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe
|
I am adding the result of mqsicvp command of used ODBC record an I can not still see the reason why I am receiving the exception. Even timeStampDatatypeName is suppored and inside SQuirreL SQL client Timestamp has 23 characters (less than allowed 29):
| Code: |
BIP8270I: Connected to Datasource 'MQSILOGDBRCE' as user 'RYBARST'. The datasource platform is 'Oracle', version '11.02.0000 Oracle 11.2.0.3.0'.
===========================
databaseProviderVersion = 11.02.0000 Oracle 11.2.0.3.0
driverVersion = 06.00.0266 (B0190, U0090)
driverOdbcVersion = 03.52
driverManagerVersion = 3.52.0000
driverManagerOdbcVersion = 03.52.0000
databaseProviderName = Oracle
datasourceServerName = DBORA14.UNIX.????.?? [scrambled]
databaseName = N/A
odbcDatasourceName = MQSILOGDBRCE
driverName = UKora24.so
supportsStoredProcedures = Yes
procedureTerm = PL/SQL
accessibleTables = Yes
accessibleProcedures = Yes
identifierQuote = "
specialCharacters = None
describeParameter = Yes
schemaTerm = User Name
tableTerm = Table
sqlSubqueries = 31
activeEnvironments = 0
maxDriverConnections = 0
maxCatalogNameLength = 128
maxColumnNameLength = 30
maxSchemaNameLength = 30
maxStatementLength = 0
maxTableNameLength = 30
supportsDecimalType = Yes
supportsDateType = No
supportsTimeType = No
supportsTimeStampType = Yes
supportsIntervalType = No
supportsAbsFunction = Yes
supportsAcosFunction = No
supportsAsinFunction = No
supportsAtanFunction = No
supportsAtan2Function = No
supportsCeilingFunction = Yes
supportsCosFunction = Yes
supportsCotFunction = No
supportsDegreesFunction = No
supportsExpFunction = Yes
supportsFloorFunction = Yes
supportsLogFunction = Yes
supportsLog10Function = Yes
supportsModFunction = Yes
supportsPiFunction = No
supportsPowerFunction = Yes
supportsRadiansFunction = No
supportsRandFunction = No
supportsRoundFunction = Yes
supportsSignFunction = Yes
supportsSinFunction = Yes
supportsSqrtFunction = Yes
supportsTanFunction = Yes
supportsTruncateFunction = Yes
supportsConcatFunction = Yes
supportsInsertFunction = Yes
supportsLcaseFunction = Yes
supportsLeftFunction = Yes
supportsLengthFunction = Yes
supportsLTrimFunction = Yes
supportsPositionFunction = No
supportsRepeatFunction = Yes
supportsReplaceFunction = Yes
supportsRightFunction = Yes
supportsRTrimFunction = Yes
supportsSpaceFunction = Yes
supportsSubstringFunction = Yes
supportsUcaseFunction = Yes
supportsExtractFunction = No
supportsCaseExpression = No
supportsCastFunction = No
supportsCoalesceFunction = No
supportsNullIfFunction = No
supportsConvertFunction = Yes
supportsSumFunction = Yes
supportsMaxFunction = Yes
supportsMinFunction = Yes
supportsCountFunction = Yes
supportsBetweenPredicate = Yes
supportsExistsPredicate = Yes
supportsInPredicate = Yes
supportsLikePredicate = Yes
supportsNullPredicate = Yes
supportsNotNullPredicate = Yes
supportsLikeEscapeClause = Yes
supportsClobType = No
supportsBlobType = No
charDatatypeName = CHAR
varCharDatatypeName = VARCHAR2
longVarCharDatatypeName = CLOB
clobDatatypeName = N/A
timeStampDatatypeName = TIMESTAMP
binaryDatatypeName = RAW
varBinaryDatatypeName = RAW
longVarBinaryDatatypeName = BLOB
blobDatatypeName = N/A
intDatatypeName = NUMBER
doubleDatatypeName = BINARY_DOUBLE
varCharMaxLength = 4000
longVarCharMaxLength = 2147483647
clobMaxLength = 0
varBinaryMaxLength = 2000
longVarBinaryMaxLength = 2147483647
blobMaxLength = 0
timeStampMaxLength = 29
identifierCase = Upper
escapeCharacter = \
longVarCharDatatype = -1
clobDatatype = 0
longVarBinaryDatatype = -4
blobDatatype = 0
BIP8273I: The following datatypes and functions are not natively supported by datasource 'MQSILOGDBRCE' using this ODBC driver: Unsupported datatypes: 'DATE, TIME, INTERVAL, CLOB, BLOB' Unsupported functions: 'ACOS, ASIN, ATAN, ATAN2, COT, DEGREES, PI, RADIANS, RAND, POSITION, EXTRACT, CASE, CAST, COALESCE, NULLIF'
Examine the specific datatypes and functions not supported natively by this datasource using this ODBC driver.
When using these datatypes and functions within ESQL, the associated data processing is done within WebSphere Message Broker rather than being processed by the database provider.
Note that "functions" within this message can refer to functions or predicates.
BIP8071I: Successful command completion.
|
|
|
| Back to top |
|
|
| t603 |
| Posted: Wed Nov 14, 2012 9:01 am Post subject: |
|
|
Voyager
Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe
|
SOLVED!
| Code: |
| select current_timestamp from sys.dual |
returns data type "TIMESTAMP WITH TIME ZONE", while I created myTable as
| Code: |
create table "RYBARST".myTable (ct timestamp);
insert into "RYBARST".myTable (ct) select current_timestamp from sys.dual; |
So there were implicit data type conversion from "TIMESTAMP WITH TIME ZONE" to "TIMESTAMP" when I inserted timestamp to myTable.
And WSMB does not know handle data type "TIMESTAMP WITH TIME ZONE", although it is not mentioned in the result of "mqsicvp" command.
I would like to have functionality of "mqsicvp" command, that it dynamic listed all data types, their precision, functions per datatype etc. of database and append supporting statement. It is common functionality of each JDBC driver (and I hope also ODBC driver) and the result is propagated and displayed to SQL GUI clients (like SQuirreL one).
Also I guess, that WSMB could be more specific in Exception like "Unsupported data type TIMESTAMP WITH TIME ZONE" and not only "Optional feature not implemented".
Anyway, I am happy to have solution. Good evening for everyone. Stepan |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Mon Nov 19, 2012 6:33 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| So you changed your DB-Design ? |
|
| Back to top |
|
|
| t603 |
| Posted: Mon Nov 19, 2012 6:58 am Post subject: |
|
|
Voyager
Joined: 16 Oct 2012
Posts: 88
Location: Prague, the Czech Republic, Europe
|
Yes,
| Code: |
| select cast(current_timestamp as timestamp) from sys.dual; |
solved the problem, while
| Code: |
select current_timestamp from sys.dual; |
caused the problem. The first SQL select returned data type = "TIMESTAMP WITH TIME ZONE", while the second SQL select returned data type = "TIMESTAMP". The WSMB is unable working with "TIMESTAMP WITH TIME ZONE" and then rised the exception "[IBM][ODBC Oracle Wire Protocol driver]Optional feature not implemented.".
I did not know, that above Exception means unsupported data type "TIMESTAMP WITH TIME ZONE", which I claimed here in forum, that there could be better explanation in Exception message. And also in mqsicvp command.
Stepan |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Mon Nov 19, 2012 7:39 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
You only changed the SQL-SELECT (not the DB-Design), that is good.
thanks for responding (so quickly). |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
