| Author |
Message
|
| jonasb |
 Posted: Thu Oct 08, 2009 3:16 am Post subject: error on sql to oracle db ([IBM][ODBC 20101 driver]251) |
 |
|
Apprentice
Joined: 20 Dec 2006
Posts: 49
Location: Sweden
|
I'm new to using oracle with message broker and keep getting an error that i do not understand.
[IBM][ODBC 20101 driver]251
This simple test works fine:
| Code: |
declare test1 integer;
set test1 =
the (select count(*)
from Database.xyz.table1 as t);
|
But this sql
| Code: |
declare test2 character;
set test2 =
the (select item t.column1
from Database.xyz.table1 as t
where t.column2 = '12345');
|
generates an exception with the error:
[IBM][ODBC 20101 driver]251
I have tried over 10 different SQLs, but i keep getting the same error. (Test1 is the only one that works). I also test the SQL in the client before I test them in the broker.
Any tips and hints would be appreciated.
MB 6.1
AIX 5.3
the driver used is:
| Code: |
[XXXXXX]
DRIVER=/wbimb_test/mb61/ODBC64/V5.3/lib/UKora23.so
Description=DataDirect 5.3 64bit Oracle Wire Protocol
HostName=XXXXXXXXXX
PortNumber=XXXX
SID=XXXXXX
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
|
_________________
contact admin |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Oct 08, 2009 4:06 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Two Points
1) in the first example, the 'as t' does nothing as it is not used elsewhere in the select (very minor)
2) More importantly, how many rows could the select in example 2 return.
It is better to put the results into an object that will allow more than one result to be returned.
The 'select count...' will always only ever return one answer.
The 'select .... where...' can return more than one answer.
The broker does not know if the results can be one or more rows when it issues the select.
Generally, I do selects like this into an Environment variable which can take a variable number of items.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Oct 08, 2009 4:20 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| But generally an unintelligible error message like "[IBM][ODBC 20101 driver]251 " indicates that something is not configured correctly. |
|
| Back to top |
|
|
| jonasb |
| Posted: Thu Oct 08, 2009 4:56 am Post subject: |
|
|
Apprentice
Joined: 20 Dec 2006
Posts: 49
Location: Sweden
|
Hi,
First: thanks for the suggestions and comments.
As i mentioned, i have tried a lot of things, e.g.
| Code: |
set OutputLocalEnvironment.sql.row[] =
select t.column1,
t.column2,
from Database.xyz.table1 as t;
|
And yes, test2 only returns one row. (se initial post)
Can it be datatype conversions? When I do count(*) integer value is returned, but the other ones should return character.
Is there anything I need to do datatype conversion wise for oracle db:s?
Also, if anyone knows anything about the error code, it would be appreciated.
Kind Regards,
_________________
contact admin |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Oct 08, 2009 5:36 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
If test 2 returns one row into the Environment folder then is it not working?
If there is only one row (and you can check this using ESQL) then you can take the first row result and cast it into a character as a local variable.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| jonasb |
| Posted: Thu Oct 08, 2009 6:55 am Post subject: |
|
|
Apprentice
Joined: 20 Dec 2006
Posts: 49
Location: Sweden
|
smdavies99 wrote:
| Quote: |
If test 2 returns one row into the Environment folder then is it not working?
If there is only one row (and you can check this using ESQL) then you can take the first row result and cast it into a character as a local variable. |
When i run test2 (an all other SQL but test1) a exception is thrown with the text "[IBM][ODBC 20101 driver]251".
This is what i find strange, if test1 also gave this error I would guess that I had configured something wrong with odbc/driver etc.
If anyone has any pointers on what the error "[IBM][ODBC 20101 driver]251" means it would be appreciated.
Kind Regards,
_________________
contact admin |
|
| Back to top |
|
|
| kiruthigeshwar |
| Posted: Sun Oct 20, 2013 7:22 am Post subject: |
|
|
Acolyte
Joined: 31 Oct 2012
Posts: 50
|
| contact admin wrote: |
smdavies99 wrote:
| Quote: |
If test 2 returns one row into the Environment folder then is it not working?
If there is only one row (and you can check this using ESQL) then you can take the first row result and cast it into a character as a local variable. |
When i run test2 (an all other SQL but test1) a exception is thrown with the text "[IBM][ODBC 20101 driver]251".
This is what i find strange, if test1 also gave this error I would guess that I had configured something wrong with odbc/driver etc.
If anyone has any pointers on what the error "[IBM][ODBC 20101 driver]251" means it would be appreciated.
Kind Regards, |
Is the issue resolved .. Am facing the same issue.. exact error when I run a nested SELECT query with CASE in it. Also it throws a different erro 'Right Paranthesis Missing' when I use 'AS' keyword, but when I remove it I get the above mentioned error "[IBM][ODBC 20101 driver]251". Please help me with this.
The query returns more than one record and am storing it in LocalEnv variable.
Thanks & Regards,
Kiruthigeshwar N
_________________
Regards,
K |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Oct 20, 2013 7:55 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
ESQL Select statement is not the same as SQLServer Select statement.
If you want to use SQLServer Select statement, you have to use PASSTHRU.
The documentation on both functions is excellent. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Mon Oct 21, 2013 12:26 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
start with a simple select, for example
| Code: |
set OutputLocalEnvironment.sql.row[] =
select t.* from Database.xyz.table1 as t;
|
your example:
| Code: |
set OutputLocalEnvironment.sql.row[] =
select t.column1,
t.column2,
from Database.xyz.table1 as t; |
is incorrect, the coma after column2 should not be there.
and, as this code works for you:
| Code: |
declare test1 integer;
set test1 =
the (select count(*)
from Database.xyz.table1 as t); |
you could try adding to it, for example:
| Code: |
declare test1 integer;
set test1 =
the (select count(*)
from Database.xyz.table1 as t where t.column2 = '1234'); |
|
|
| Back to top |
|
|
|
|
