| Author |
Message
|
| abhyyy |
 Posted: Wed Jul 24, 2013 12:15 am Post subject: Solved: MS SQL select query problem |
 |
|
Voyager
Joined: 29 Sep 2011
Posts: 83
|
Hi Friends,
I am new to WMB to MS SQL integration.
I have recently started using WMB version 9 (now called IIB9.0) and trying to create a test flow to select from an MS SQL table.
The normal covention I know of goes as
SELECT * FROM Database.Datasource.SchemaName.Table
But the Problem is :
Where do I specify the MS SQL database name here. Considering that I have a datasource as 'MSSQL_ODBC' a database name as 'CED' a schemaname as 'dbo' and a table name as 'testtable'. using normal convention I have made the below mentioned query
SELECT * FROM Database.MSSQL_ODBC.dbo.testtable
and some other combinations but I am still receiving the error as
Text:CHARACTER:[Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
Experts please advise. Many Thanks.
_________________
----------------------
NeVeR StOp LeaRnInG.
Last edited by abhyyy on Thu Oct 17, 2013 9:01 pm; edited 1 time in total |
|
| Back to top |
|
 |
| dogorsy |
| Posted: Wed Jul 24, 2013 12:18 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| I think you need to read in the infocenter the properties for the nodes that handle databases. Or go to a course. |
|
| Back to top |
|
|
| abhyyy |
| Posted: Wed Jul 24, 2013 12:31 am Post subject: |
|
|
Voyager
Joined: 29 Sep 2011
Posts: 83
|
If u had read the infocenter or gone for a course then u would have replied back with a one line correct query, but your response shows your knowledge.
Better not waste your time here on such responses and utilize on infocenter !
Sorry for sounding rude here, but I would have appreciated your response on query rather than just.....
_________________
----------------------
NeVeR StOp LeaRnInG. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jul 24, 2013 12:35 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
No. The forum is not for writing your code. If you do not know the basic ESQL statements and the compute node properties, then you should go and read about it.
You are rude, and ignorant. |
|
| Back to top |
|
|
| abhyyy |
| Posted: Wed Jul 24, 2013 12:43 am Post subject: |
|
|
Voyager
Joined: 29 Sep 2011
Posts: 83
|
I dint know that dumbs like you decided the rules for what the forum is and isn't for.
Members like you dont even belong here. Here is how it works ::
If you know can or want to help in the post then respond to post else
dont respond !! So you know which of the 2 categories u belong to : 'NONE' coz u are the third kind who don't even know and wanna show off !!
Go do something productive in life.
_________________
----------------------
NeVeR StOp LeaRnInG. |
|
| Back to top |
|
|
| abhyyy |
| Posted: Wed Jul 24, 2013 1:29 am Post subject: |
|
|
Voyager
Joined: 29 Sep 2011
Posts: 83
|
Hi Friends,
To avoid any more such replies... Here is what Infocenter says
Infocenter says as FROM clause inn select query can be as below :::::
FROM clause
FROM clause expressions can contain multiple database references, multiple message references, or a mixture of the two. You can join tables with tables, messages with messages, or tables with messages.
FROM clause FieldReferences can contain expressions of any kind (for example, Database.{DataSource}.{Schema}.Table1).
You can calculate a field, data source, schema, or table name at run time.
but stilll dosnt say anything about what if we want to access a specific database in MSSQL server 2012.
_________________
----------------------
NeVeR StOp LeaRnInG. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jul 24, 2013 2:22 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| well, maybe you are right. But I DO KNOW THE ANSWER and you don't !! |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 24, 2013 3:32 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| abhyyy wrote: |
I dint know that dumbs like you decided the rules for what the forum is and isn't for.
Members like you dont even belong here. Here is how it works ::
If you know can or want to help in the post then respond to post else
dont respond !! So you know which of the 2 categories u belong to : 'NONE' coz u are the third kind who don't even know and wanna show off !!
Go do something productive in life. |
The issue here is you are asking about basic database access which is really not a proper venue to do so here.
The product is complete and the documentation is clear on how to access any database which is supported. Since (I assume) you have not taken the REQUIRED training, then you are not entitled to support. If you were to take the REQUIRED training, your instructor will show you how to access a database table using several types of nodes.
Really REALLY, the onus is on you.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fac07000_.htm
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fak05810_.htm
| Quote: |
SET OutputRoot.XML.Test.Result[] =
(SELECT T.Column1 AS Column1, T.Column2 AS Column2
FROM Database.USERTABLE AS T);
This example produces the output
<Test>
<Result>
<Column1>value1</Column1>
<Column2>value2</Column2>
</Result>
<Result>
<Column1>value3</Column1>
<Column2>value4</Column2>
</Result>
</Test> |
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.samples.simplifieddbrouting.doc%2Fdoc%2Foverview.htm
| Quote: |
| The Simplified Database Routing sample also demonstrates how to access databases by using JDBC, and how to use values held in an acquired result set from a database query, to either dynamically route messages or update their content. |
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.samples.routing.doc%2Fdoc%2FrunningDatabase.htm
| Quote: |
Running the Message Routing sample is split into two scenarios:
Using a direct connection to the database table to retrieve routing information for each message that is processed
Using shared variables to store a copy of the database table in the message flow that can be made use of by each message that is processed
These two scenarios are described separately. All of the test messages that are used in this section are stored in the Test messages directory in the Message routing sample message flows Message Broker project. |
http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.samples.datawarehouse.doc%2Fdoc%2Fintroduction.htm
| Quote: |
About the Data Warehouse sample
The Data Warehouse sample is a message flow sample application that demonstrates a scenario in which a message flow is used to perform the archiving of data, such as sales data, into a database. The data is stored for later analysis by another message flow or application.
Because the sales data is analyzed at a later date, the storage of the messages is organized in a way that makes it easy to select records for specified times. When the message is inserted into the database, the date and time at which the WebSphere MQ message containing the sales record was written are stored as separate column values. The database table contains four columns:
The message data. The payload of the WebSphere MQ message stored as a BLOB.
The date and time when the WebSphere MQ message was created.
A time stamp that is created by the database to record the time when the record was inserted. |
Do you really think these samples do not work? They may not work for you, simply because you have not yet acquired the information you need. What's your plan to acquire the right information?
Ensure you are using a supported version of SQLServer :
| Quote: |
Microsoft SQL Server
Define a data source for Microsoft SQL Server:
Select the driver for the version of SQL Server that you are using:
SQL Native Client for SQL Server 2005.
SQL Native Client 10.0 for SQL Server 2008.
Specify a name and description.
Select the correct server from the list.
Click Finish to save your definition.
Click OK to close the ODBC Data Source Administrator. |
Then make sure your runtime ODBC.ini file is correct:
| Quote: |
[SQLSERVERDB]
Driver=<Your Broker install directory>/ODBC/V6.0/lib/UKmsss24.so
Description=DataDirect 6.0 ODBC SQL Server Wire Protocol
Address=<Your SQLServer Machine Name>,<Your SQLServer Port Number>
;# Alternative way to locate server using a named instance
;# Address=<Your SQLServer Machine Name>\<Your SQLServer Instance Name>
AnsiNPW=Yes
Database=<Your Database Name>
QuotedId=No
ColumnSizeAsCharacte |
or make the corresponding entries in the SYSTEM DSN table on Windows.
Now that we have spelled it out for you, let's revisit your original post:
| abhyyy wrote: |
| Hi Friends, |
Hello.
| abhyyy wrote: |
| I am new to WMB to MS SQL integration.I have recently started using WMB version 9 (now called IIB9.0) |
Congratulations. IIB promises to be an exciting product to learn and use.
| abhyyy wrote: |
trying to create a test flow to select from an MS SQL table. The normal convention I know of goes asSELECT * FROM Database.Datasource.SchemaName.Table But the Problem is :
Where do I specify the MS SQL database name here. Considering that I have a datasource as 'MSSQL_ODBC' a database name as 'CED' a schemaname as 'dbo' and a table name as 'testtable'. using normal convention I have made the below mentioned query SELECT * FROM Database.MSSQL_ODBC.dbo.testtable and some other combinations but I am still receiving the error as Text:CHARACTER:[Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. Experts please advise. Many Thanks. |
Reading the InfoCentre will get you where you want to be. If you have difficulty reading or understanding what the InfoCentre is discussing, then your next option is some coaching or mentoring resource at your site. Next after that is attending the required training. Next after that is trying out the samples, which have been proven to work. If you still can't figure things out after that, post here. Don't post here if you have not done your own work. If you are not motivated enough to read, be coached, or attend the training, we won't help you.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jul 24, 2013 5:12 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
the problem is that he did not even bother reading and trying to understand the error message.
The statements could not be PREPARED, meaning is not even trying to execute the SELECT.
From looking at the select he constructed , it is pretty obvious he has no clue as what he is doing. |
|
| Back to top |
|
|
| abhyyy |
| Posted: Wed Jul 24, 2013 7:53 am Post subject: |
|
|
Voyager
Joined: 29 Sep 2011
Posts: 83
|
@ lancelotlinc
Well, to start with.. YOU ASSUMED WRONGLY, just beacause you know a bit more doesnt mean that you can widen your tone !! Thanks anyways for all the links, I went thru them (AGAIN) and dint find anything that could solve my problem.
Speaking of onus, I have done my part. When I came to this forum I hoped that u also would (not that its ur job) with that tone, but anyways..bask in the glory of whatever..
Just for your info,before coming to this forum I had completed following steps :
1. Used 64 bit driver and created system DSN.
2. Created MS sQL server instance and a database.
3. set mqsisetdbparms.
4. verified successfully using mqsicvp.
5. Created a test flow and used system DSN as Datasource in compute node property.
6. Used SELECT * FROM Database.MSSQL_ODBC.dbo.testtable as query in esql code of compute node. (which does work)
7. I had thoroughly gone through teh Infocenter for finding the solution for the posted problem.
@ dogorsy
Kid, "SELECT * FROM Database.MSSQL_ODBC.dbo.testtable" The same query has worked !! eat your words... and find some other job.
I hope u had some clue that what u were doing here, I bet u dont have anything better to do.... Oh yes , I remember !! you are the THIRD KIND !!
@ lancelotlinc , @ dogorsy
For your Info, I was able to solve my problem and below mentioned is the solution.
Now go ahead and claim that you both knew it and become instructors.
The problem was that the new database created (in question here) was not being set as default database and that is why such error was coming. One I changed the default database, the code worked fine !!
_________________
----------------------
NeVeR StOp LeaRnInG. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 24, 2013 8:09 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| abhyyy wrote: |
| The problem was that the new database created (in question here) was not being set as default database and that is why such error was coming. One I changed the default database, the code worked fine !! |
And what were your troubleshooting steps to arrive at this conclusion?
| abhyyy wrote: |
| Just for your info,before coming to this forum I had completed following steps |
If you had posted that at the first, you may have received a different response.
| abhyyy wrote: |
| YOU ASSUMED WRONGLY, |
So you have been to training or not? I'm referring to the classes specified in the developer's skills roadmap:
https://www-304.ibm.com/jct03001c/services/learning/us/pdfs/roadmaps/wmb_v8_dev.pdf
Which of these had you been to ?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| abhyyy |
| Posted: Wed Jul 24, 2013 8:31 am Post subject: |
|
|
Voyager
Joined: 29 Sep 2011
Posts: 83
|
| Quote: |
| And what were your troubleshooting steps to arrive at this conclusion? |
Checked mqsicvp command again and found that it was referring to wrong default database name.
| Quote: |
| If you had posted that at the first, you may have received a different response. |
If any of you guys had been patient enough to clarify, I would have prompty responded, but seems like guys were like to be sarcastic and ASSUME too much.
I work one of the IBM BPs and we have had a classroom training for version 7.
I hope you would be a little patient and ask one line question before assuming and typing a 40 line response which is not of use. (not that I am saying that its ur job but if u want to respond and really wanna help)
_________________
----------------------
NeVeR StOp LeaRnInG. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jul 24, 2013 8:31 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| abhyyy wrote: |
@ lancelotlinc
Well, to start with.. YOU ASSUMED WRONGLY, just beacause you know a bit more doesnt mean that you can widen your tone !! Thanks anyways for all the links, I went thru them (AGAIN) and dint find anything that could solve my problem.
Speaking of onus, I have done my part. When I came to this forum I hoped that u also would (not that its ur job) with that tone, but anyways..bask in the glory of whatever..
Just for your info,before coming to this forum I had completed following steps :
1. Used 64 bit driver and created system DSN.
2. Created MS sQL server instance and a database.
3. set mqsisetdbparms.
4. verified successfully using mqsicvp.
5. Created a test flow and used system DSN as Datasource in compute node property.
6. Used SELECT * FROM Database.MSSQL_ODBC.dbo.testtable as query in esql code of compute node. (which does work)
7. I had thoroughly gone through teh Infocenter for finding the solution for the posted problem.
@ dogorsy
Kid, "SELECT * FROM Database.MSSQL_ODBC.dbo.testtable" The same query has worked !! eat your words... and find some other job.
I hope u had some clue that what u were doing here, I bet u dont have anything better to do.... Oh yes , I remember !! you are the THIRD KIND !!
@ lancelotlinc , @ dogorsy
For your Info, I was able to solve my problem and below mentioned is the solution.
Now go ahead and claim that you both knew it and become instructors.
The problem was that the new database created (in question here) was not being set as default database and that is why such error was coming. One I changed the default database, the code worked fine !! |
if you search this same forum, you will find that I answered exactly the same question not long ago, so you eat your stupid words. And I will not tell you what kind you are, I think everyone reading this forum already know that. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jul 24, 2013 8:34 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| abhyyy wrote: |
| Quote: |
| And what were your troubleshooting steps to arrive at this conclusion? |
Checked mqsicvp command again and found that it was referring to wrong default database name.
| Quote: |
| If you had posted that at the first, you may have received a different response. |
If any of you guys had been patient enough to clarify, I would have prompty responded, but seems like guys were like to be sarcastic and ASSUME too much.
I work one of the IBM BPs and we have had a classroom training for version 7.
I hope you would be a little patient and ask one line question before assuming and typing a 40 line response which is not of use. (not that I am saying that its ur job but if u want to respond and really wanna help) |
We will help you in the future. Let's find a way to expedite the solution.
We can't play twenty questions. You have to be detailed enough to post the needful information at the first.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| abhyyy |
| Posted: Wed Jul 24, 2013 8:35 am Post subject: |
|
|
Voyager
Joined: 29 Sep 2011
Posts: 83
|
| Quote: |
| if you search this same forum, you will find that I answered exactly the same question not long ago, so you eat your stupid words. And I will not tell you what kind you are, I think everyone reading this forum already know that. |
Ohhh.. dont cry now ..not now that I have figured out the answer which u so knew but chose to brag all day instead of helping
| Quote: |
well, maybe you are right. But I DO KNOW THE ANSWER and you don't !! |
This also shows what kind you are !!
_________________
----------------------
NeVeR StOp LeaRnInG. |
|
| Back to top |
|
|
|
|
