| Author |
Message
|
| marco |
 Posted: Thu Sep 17, 2009 6:50 am Post subject: getJDBCType4Connection - MSSQLServer |
 |
|
Apprentice
Joined: 16 Feb 2004
Posts: 46
|
Hi,
I've created a JDBCProvider for my database and successfully retreived data from it using the DatabaseRetrieve Node.
Now I'm trying to do the same using a JavaCompute node, but this fails.
the getJDBCType4Connection returns me :
"Failed to obtain JDBC Connection"
Any idead on what the difference might be ?
Rgds
M |
|
| Back to top |
|
 |
| hopsala |
| Posted: Sun Sep 20, 2009 10:04 pm Post subject: |
|
|
Guardian
Joined: 24 Sep 2004
Posts: 960
|
There are many differences between using native nodes and Java, both in terms of configuration and code. But you didn't give us enough detail to help you:
- which WMB ver?
- which DB ?
- post the JDBCProvider config
- post the relevant code snippet
Oh, and it's always a good practice to check out the database logs and see if you can see a connection-rejected message. This way you'll know if you're reached the db or are failed earlier on. |
|
| Back to top |
|
|
| marco |
| Posted: Mon Sep 21, 2009 3:31 am Post subject: |
|
|
Apprentice
Joined: 16 Feb 2004
Posts: 46
|
Hi,
sorry for the lack of information :
Broker version : v6.1
JDBCProvider config :
| Code: |
TEST
connectionUrlFormat='jdbc:sqlserver://<ip-address>:1433;databaseName=TEST;User=TestUser;Password=test'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='TEST'
databaseType='default_Database_Type'
databaseVersion='default_Database_Version'
description='default_Description'
jarsURL='C:\Temp\Downloads\Microsoft SQL Server JDBC Driver 2.0\sqljdbc_2.0\enu'
portNumber='1433'
securityIdentity='default_User@default_Server'
serverName='<ip-address>'
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
|
This configuration works for the DatabaseRetrieveNode
this line fails :
| Code: |
| Connection conn = getJDBCType4Connection("TEST",JDBC_TransactionType.MB_TRANSACTION_AUTO); |
Database :
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Sep 21, 2009 5:38 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| What's the full error message from the getJDBCType4Connection? |
|
| Back to top |
|
|
| marco |
| Posted: Mon Sep 21, 2009 6:51 am Post subject: |
|
|
Apprentice
Joined: 16 Feb 2004
Posts: 46
|
"Failed to obtain JDBC Connection" is the only thing I'm afraid.
How would I be able to get more info ? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Sep 21, 2009 7:02 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
|
| Back to top |
|
|
| marco |
| Posted: Mon Sep 21, 2009 11:48 pm Post subject: |
|
|
Apprentice
Joined: 16 Feb 2004
Posts: 46
|
This is the result : (sorry for the ugly formatting)
| Code: |
Timestamps are formatted in local time, 120 minutes past GMT.
Trace written by version ; formatter version 6103
2009-09-22 09:45:17.670410 4980 UserTrace BIP2632I: Message received and propagated to 'out' terminal of MQ input node 'CSSID_change.LQ.TEST.00'.
2009-09-22 09:45:17.671916 4980 UserTrace BIP6060I: Parser type ''Properties'' created on behalf of node 'CSSID_change.LQ.TEST.00' to handle portion of incoming message of length 0 bytes beginning at offset '0'.
2009-09-22 09:45:17.672216 4980 UserTrace BIP6061I: Parser type ''MQMD'' created on behalf of node 'CSSID_change.LQ.TEST.00' to handle portion of incoming message of length '364' bytes beginning at offset '0'. Parser type selected based on value ''MQHMD'' from previous parser.
2009-09-22 09:45:17.672472 4980 UserTrace BIP6061I: Parser type ''XMLNSC'' created on behalf of node 'CSSID_change.LQ.TEST.00' to handle portion of incoming message of length '17' bytes beginning at offset '364'. Parser type selected based on value ''XMLNSC'' from previous parser.
2009-09-22 09:45:22.946933 4980 UserTrace BIP3904I: Invoking the evaluate() method of node (class='ComIbmJavaComputeNode', name='CSSID_change#FCMComposite_1_5').
About to pass a message to the evaluate() method of the specified node.
No user action required.
2009-09-22 09:45:47.824073 4980 Error BIP2628E: Exception condition detected on input node 'CSSID_change.LQ.TEST.00'.
The input node 'CSSID_change.LQ.TEST.00' detected an error whilst processing a message. The message flow has been rolled-back and, if the message was being processed in a unit of work, it will remain on the input queue to be processed again. Following messages will indicate the cause of this exception.
Check the error messages which follow to determine why the exception was generated, and take action as described by those messages.
2009-09-22 09:45:47.824100 4980 RecoverableException BIP2230E: Error detected whilst processing a message in node 'CSSID_change.JavaCompute'.
The message broker detected an error whilst processing a message in node 'CSSID_change.JavaCompute'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2009-09-22 09:45:47.824115 4980 RecoverableException BIP6233E: An error occurred in node: 'getJDBCType4Connection' There was a problem establishing a connection to the given database URL: 'TEST' Exception details: ' '
The node was unable to establish a connection to the given database URL.
Contact your WebSphere Message Brokers administrator.
2009-09-22 09:45:48.832782 4980 Error BIP2648E: Message backed out to a queue; node 'CSSID_change.LQ.TEST.00'.
Node 'CSSID_change.LQ.TEST.00' has received a message which has previously been backed out one or more times because of a processing error in the main path of the message flow. The failure terminal is not attached, so the message broker is putting the message directly to the requeue or dead letter backout queue associated with this node. The MQMD 'backoutCount' of the message now equals the 'backoutThreshold' defined for the WebSphere MQ input queue.
Examine previous messages and the message flow to determine why the message is being backed out. Correct this situation if possible. Perform any local error recovery processing required.
2009-09-22 09:45:48.842182 4980 UserTrace BIP2638I: The MQ output node 'CSSID_change.LQ.TEST.00' attempted to write a message to queue ''SYSTEM.DEAD.LETTER.QUEUE'' connected to queue manager ''WBRK_LOCAL''. The MQCC was '0' and the MQRC was '0'.
2009-09-22 09:45:48.842395 4980 UserTrace BIP2615I: The WebSphere MQ input node 'CSSID_change.LQ.TEST.00' has backed out the message to the backout requeue or the dead letter queue.
Message backout processing has been invoked, and the message has either been backed out by being written to the backout requeue or dead letter queue, as determined by the WebSphere MQ queue manager and queue configuration.
No user action required.
Threads encountered in this trace:
4980 |
|
|
| Back to top |
|
|
| paranoid221 |
| Posted: Tue Sep 22, 2009 1:11 am Post subject: |
|
|
Centurion
Joined: 03 Apr 2006
Posts: 101
Location: USA
|
Have you run the necessary commands (mqsicreateconfigurable service)to create the JDBCType4 provider. I think you must also stop and start the broker for the changes to take effect.
Run mqsireportproperties command to check if the result of that command contains entries for ur database name. The entry should start like this:
JDBCProviders/TEST/.....
Last but not the least,
| Code: |
| type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver' |
the value you have there is the driver classname. For type4Datasource ClassName I think it should be:
| Code: |
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource' |
_________________
LIFE is a series of complex calculations, somewhere multiplied by ZERO. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Sep 22, 2009 3:12 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
|
| Back to top |
|
|
| marco |
| Posted: Tue Sep 22, 2009 4:02 am Post subject: |
|
|
Apprentice
Joined: 16 Feb 2004
Posts: 46
|
this is the new config :
| Code: |
JDBCProviders
TEST
connectionUrlFormat='jdbc:sqlserver://<ip>:1433;databaseName=TEST;User=user;Password=pwd'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='TEST'
databaseType='default_Database_Type'
databaseVersion='default_Database_Version'
description='default_Description'
jarsURL='C:\Temp\Downloads\Microsoft SQL Server JDBC Driver 2.0\sqljdbc_2.0\enu'
portNumber='1433'
securityIdentity='default_User@default_Server'
serverName=<ip>
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver' |
this does not throw me an error, but returns me a null value |
|
| Back to top |
|
|
| marco |
| Posted: Tue Sep 22, 2009 4:27 am Post subject: |
|
|
Apprentice
Joined: 16 Feb 2004
Posts: 46
|
sorry
I changed this ofcourse :
| Code: |
| type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource' |
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Sep 22, 2009 4:56 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The connectionURLFormat is still wrong.
It should be *exactly*, with NO CHANGES,
| Code: |
| connectionUrlFormat='jdbc:sqlserver://[serverName]:[portNumber];DatabaseName=[databaseName];user=[user];password=[password]' |
The [value] will be REPLACED with the so named attribute of the JDBCProvider, INSIDE the broker code. |
|
| Back to top |
|
|
| marco |
| Posted: Tue Sep 22, 2009 5:35 am Post subject: |
|
|
Apprentice
Joined: 16 Feb 2004
Posts: 46
|
| what do you mean with inside the broker code ? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Sep 22, 2009 6:11 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I mean that when you run getJDBCType4Connection, the code behind that will do the replacement.
I do not in any way mean you have to *write* any code that will do the replacement.
It's not entirely clear why the DatabaseRetrieve node is able to successfully use this JDBC provider. But that's part of what "unpredictable results" means. |
|
| Back to top |
|
|
| marco |
| Posted: Wed Sep 23, 2009 3:55 am Post subject: |
|
|
Apprentice
Joined: 16 Feb 2004
Posts: 46
|
Ive tried like this :
| Code: |
JDBCProviders
TEST
connectionUrlFormat='jdbc:sqlserver://[serverName]:[portNumber];DatabaseName=[databaseName];user=[user];password=[password]'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='TEST'
databaseType='default_Database_Type'
databaseVersion='default_Database_Version'
description='default_Description'
jarsURL='C:\Temp\Downloads\Microsoft SQL Server JDBC Driver 2.0\sqljdbc_2.0\enu'
portNumber='1433'
securityIdentity='default_User@default_Server'
serverName=<ip>
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver' |
and this :
| Code: |
JDBCProviders
TEST
connectionUrlFormat='jdbc:sqlserver://[serverName]:[portNumber];DatabaseName=[databaseName];user=[user];password=[password]'
connectionUrlFormatAttr1=<ip>
connectionUrlFormatAttr2='1433'
connectionUrlFormatAttr3='TEST'
connectionUrlFormatAttr4=<user>
connectionUrlFormatAttr5=<pwd>
databaseName='TEST'
databaseType='default_Database_Type'
databaseVersion='default_Database_Version'
description='default_Description'
jarsURL='C:\Temp\Downloads\Microsoft SQL Server JDBC Driver 2.0\sqljdbc_2.0\enu'
portNumber='1433'
securityIdentity='default_User@default_Server'
serverName=<ip>
type4DatasourceClassName='com.microsoft.sqlserver.jdbc.SQLServerXADataSource'
type4DriverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver' |
both give me a null value on the connection |
|
| Back to top |
|
|
|
|
