| Author |
Message
|
| vickas |
 Posted: Wed Nov 11, 2015 8:55 am Post subject: Merge Command in IIB :[Microsoft]SQL nvalid parameter number |
 |
|
Centurion
Joined: 18 Aug 2013
Posts: 126
|
Dear All ,
when i try to execute the merge command in IIB v9.0.0.2 against Microsoft SQL Server 2012 on windows 7 platform i get the following exception :
[Microsoft][ODBC SQL Server Driver]Invalid parameter number followed by [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index exception .
I have created two dummy tables with same definitions( only name of the table is different ) and executed the following esql snippet :
| Code: |
SET SqlMerge = 'merge INTO dbo.EMP1 AS T USING dbo.EMP2 AS S ON T.EMPNO = S.EMPNO
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN
UPDATE SET T.DEPT = ? ';
SET UpdateAFTSQLDBRecs.Recs[] = PASSTHRU(SqlMerge TO Database.{SQLDSNNAME} VALUES('Civil')); |
am able to run the update & delete queries against the tables but not in the merge command .
please guide !! |
|
| Back to top |
|
 |
| mgk |
| Posted: Wed Nov 11, 2015 7:31 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
There are several things wrong here. Firstly, you are using the wrong ODBC driver for SQLServer - you should be using the "SQL Server Native Driver 10.0" or "SQL Server Native Driver 11.0" and not the very much older driver called "SQL Server". The latest Native Driver can be downloaded from the MS website or you can install the one that comes with SQLServer 2012. If I use the older driver as a test then I get the same error you posted below when using MERGE with a parameter marker. Once you switch to the newer driver you will then notice a couple of errors with the syntax of your MERGE statement. Firstly, you are missing a ";" to terminate the statement (in the string SqlMerge). Secondly you will see that you cannot use "UPDATE" in a WHEN NOT MATCHED clause as there is nothing to update! If you change the UPDATE to an INSERT then it should all work fine. I just tried out this syntax below with SQLServer 2014 and it worked just fine with the parameter markers (I only changed the tables and columns names to match ones I had in my test DB):
| Code: |
DECLARE SqlMerge CHAR 'merge INTO dbo.t1 AS T USING dbo.t2 AS S ON T.t1i1 = S.t2i3
WHEN MATCHED THEN UPDATE SET T.t1i2 = ?
WHEN NOT MATCHED THEN
INSERT (t1i1, t1i2) VALUES(?, ?);'; |
I hope this helps.
Kind regards
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| vickas |
| Posted: Wed Nov 11, 2015 11:46 pm Post subject: |
|
|
Centurion
Joined: 18 Aug 2013
Posts: 126
|
Hie mgk , yes made the corrections & it worked . Thanks !!
but Can i use the merge command to compare between two tables of two different database vendors in IIB v9??
knowing that a single compute node doesnt allow to interact with mutliple database VENDORS , do you think its possible ??
if not , can i store the table info in environment as follows ?
not having much idea on how exactly merge command works i did the following
| Code: |
'merge INTO dbo.EMP AS T USING (Select * FROM Environment.EMP1) AS S ON T.DEPT = S.DEPT
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN
INSERT (DEPT, RNO) VALUES(?, ?);'; |
where Environment.EMP1 contains all the columns & as same as EMP table.
EMP1 belongs to Oracle DB with same columns & datatypes
EMP belongs to sql Server DB with same cloumns & datatypes
when i do the above , i get the following exception
[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'Environment.EMP1'.
[Microsoft][SQL Server Native Client 11.0][SQL Server]The batch could not be analyzed because of compile errors. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Nov 12, 2015 12:11 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
When you use PASSTHRU the whole SQLstring is send to the connected Db.
In this case SQLServer knows nothing about the Environment tree.
A simple test of SQL commands when using PASSTHRU is to take the complete CHAR String and paste it into a tools like SQLSever Management Studio and run it.
If it works there then there is a very good chance that it will work when executed from broker.
you have hard coded Environment.EMP1 into the string. There is no way that can work. go on try it and see for yourself.
_________________
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 |
|
|
| mgk |
| Posted: Thu Nov 12, 2015 6:52 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
| Quote: |
| but Can i use the merge command to compare between two tables of two different database vendors in IIB v9?? |
No, because as pointed out, it is SQLServer specific syntax, not ESQL syntax.
| Quote: |
| not having much idea on how exactly merge command works |
MSDN documents MERGE quite well - look there for information about how it works. My advice would be to not use MERGE unless you really know that you need to use it.
| Quote: |
EMP1 belongs to Oracle DB with same columns & datatypes
EMP belongs to sql Server DB with same cloumns & datatypes |
In V9 you will need two compute nodes. First one to SELECT from the first DB into the LocalEnvironment and then in a second compute node select from the second DB and then combine the two results in ESQL in the second node.
| Quote: |
| knowing that a single compute node doesnt allow to interact with mutliple database VENDORS |
This has changed recently. In V10, you can do both SELECTs in the same node as you can talk to multiple DBs of different types in the same compute node in IIB V10 - but you still cannot do a "join" between two different DB types.
Kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| vickas |
| Posted: Wed Nov 18, 2015 1:37 am Post subject: |
|
|
Centurion
Joined: 18 Aug 2013
Posts: 126
|
HIe mgk , thanks for the inputs !!
what can be the syntax of merge command when we interact with Oracle ??
| Code: |
DECLARE SqlMerge CHAR ' MERGE INTO EMP T USING (SELECT RNO,DPT FROM EMP1 WHERE AGE= ?) S ON (T.RNO = S.RNO)
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN
INSERT ( RNO , DPT ) VALUES(?, ?); ' ;
SET SQlMergeRow.Rec[] = PASSTHRU(SqlMerge TO Database.DSN VALUES('20','77799','AC'));
|
I get this Exception when i try with the above syntax ... what might have went wrong ??
| Code: |
Type:INTEGER:5
Text:CHARACTER:HY000
Insert
Type:INTEGER:2
Text:CHARACTER:905
Insert
Type:INTEGER:5
Text:CHARACTER:[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-00905: missing keyword |
|
|
| Back to top |
|
|
| maurito |
| Posted: Wed Nov 18, 2015 2:14 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| vickas wrote: |
HIe mgk , thanks for the inputs !!
what can be the syntax of merge command when we interact with Oracle ??
|
Maybe it is explained in the Oracle documentation , don't you think ? |
|
| Back to top |
|
|
|
|
