| Author |
Message
|
| akill |
 Posted: Mon Aug 07, 2017 11:31 am Post subject: Exception with MSSQL Query using passthru . |
 |
|
Novice
Joined: 26 Jun 2017
Posts: 16
|
Hi all ,
I'm getting the below exception while executing select query using passthru .
Exception :
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation .
Here i am adding the code which i have used in my ESQL :
| Code: |
SET query = 'SELECT *
FROM '||TableName||' AS C
WHERE C.card_id =
(SELECT max(C1.card_id)
FROM '||TableName||' AS C1
WHERE C1.branch_code = ?
AND C1.customer_no = ?
AND C1.cardprogram = ?)';
SET DUPREC.CardID[] = PASSTHRU(query TO Database.{DSNNAME} VALUES('SSS','AAA','CCCC')); |
Note : It is working fine if i give values directly instead of questionmark(?) i.e.,Not Using VALUES in PASSTHRU.
Thanks in advance . |
|
| Back to top |
|
 |
| hotshot |
| Posted: Tue Aug 08, 2017 5:49 am Post subject: |
|
|
Novice
Joined: 04 Jan 2013
Posts: 20
|
Hi akill,
To make sure the fine gentlemen here haste in providing their expertise and knowledge, you should try to include in your posts as much as possible of the relevant information for your current issue, like the version and platform you are working on, what you are trying to achieve, what is the exact broker error message you are getting and what have you done already to try and investigate it. Code tags are helpful for readability.
That being said, there isn't something obviously incorrect about your SQL which would yield the noted error. I would advise that you activate and read the user trace for your flow to find out how the SQL that the broker is trying to execute actually looks like. Take that SQL and execute it from an SQL client to see whether it shows more details for that error.
If that doesn't help, you could also try and activate the odbc trace at the broker level and see what additional bits of info you can find there.
Have fun!
Cheers,
B |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Aug 08, 2017 5:55 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| hotshot wrote: |
Hi akill,
To make sure the fine gentlemen here |
Of whatever gender they choose to have, by accident of birth or otherwise.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| hotshot |
| Posted: Tue Aug 08, 2017 5:59 am Post subject: |
|
|
Novice
Joined: 04 Jan 2013
Posts: 20
|
| mqjeff wrote: |
| hotshot wrote: |
Hi akill,
To make sure the fine gentlemen here |
Of whatever gender they choose to have, by accident of birth or otherwise. |
Oh yes, this stands true for the fine ladies here as well. |
|
| Back to top |
|
|
| timber |
| Posted: Tue Aug 08, 2017 8:26 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
Maybe hotshot was expecting the gentlemen to be mostly picky and reluctant, but the ladies to be automatically helpful.
( I've always had a good imagination ) |
|
| Back to top |
|
|
| akill |
| Posted: Tue Aug 08, 2017 11:45 am Post subject: |
|
|
Novice
Joined: 26 Jun 2017
Posts: 16
|
| hotshot wrote: |
Hi akill,
To make sure the fine gentlemen here haste in providing their expertise and knowledge, you should try to include in your posts as much as possible of the relevant information for your current issue, like the version and platform you are working on, what you are trying to achieve, what is the exact broker error message you are getting and what have you done already to try and investigate it. Code tags are helpful for readability.
That being said, there isn't something obviously incorrect about your SQL which would yield the noted error. I would advise that you activate and read the user trace for your flow to find out how the SQL that the broker is trying to execute actually looks like. Take that SQL and execute it from an SQL client to see whether it shows more details for that error.
If that doesn't help, you could also try and activate the odbc trace at the broker level and see what additional bits of info you can find there.
Have fun!
Cheers,
B |
Hi , As it was my first question i confused may be , Here are exception and broker version :
Exception :
Text:CHARACTER:[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
DatabaseException
File:CHARACTER:F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp
Line:INTEGER:3899
Function:CHARACTER:ImbOdbcStatement::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:42000
Insert
Type:INTEGER:2
Text:CHARACTER:0
Insert
Type:INTEGER:5
Text:CHARACTER:[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
Version :
Am using IIB V10.0.08
Please let me know if i miss anything , Thanks |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Aug 08, 2017 11:56 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| akill wrote: |
| Please let me know if i miss anything , Thanks |
What happens if you run the query without hard coded values but through a standard client?
What does the ODBC trace say?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| timber |
| Posted: Tue Aug 08, 2017 11:58 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
I agree with hotshot. Take a user trace to see what IIB is actually doing with your SQL and its parameters. If you don't know how, you'll probably find some posts on this forum that list the required commands.
I can't see anything obviously wrong with your code. |
|
| Back to top |
|
|
| hotshot |
| Posted: Wed Aug 09, 2017 5:48 am Post subject: |
|
|
Novice
Joined: 04 Jan 2013
Posts: 20
|
| timber wrote: |
Maybe hotshot was expecting the gentlemen to be mostly picky and reluctant, but the ladies to be automatically helpful.
( I've always had a good imagination ) |
What can I say, we are each a product of our own experiences 
| timber wrote: |
| Take a user trace to see what IIB is actually doing with your SQL and its parameters. If you don't know how, you'll probably find some posts on this forum that list the required commands. |
Some pointers to get you started on this: mqsichangetrace, run your test, mqsireadlog + mqsiformatlog. You would then see in it what the SQL looks like when the broker before it is about to run it - right before the error. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Aug 09, 2017 6:31 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| hotshot wrote: |
| timber wrote: |
Maybe hotshot was expecting the gentlemen to be mostly picky and reluctant, but the ladies to be automatically helpful.
( I've always had a good imagination ) |
What can I say, we are each a product of our own experiences |
For the record, I've never been a gentleman (even before the surgery) and if exerk claims to be, I want DNA verification of species.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Aug 09, 2017 8:41 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| Vitor wrote: |
For the record, I've never been a gentleman (even before the surgery) and if exerk claims to be, I want DNA verification of species. |
What surgery? Did you do a reverse Jenner? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
