| Author |
Message
|
| prasad.edlabadkar |
 Posted: Mon May 25, 2009 6:53 am Post subject: Using Database in ESQL without Compute Node |
 |
|
Novice
Joined: 10 Feb 2006
Posts: 21
Location: Pune, India
|
Hello,
I am trying to build an ESQL library which fetches data from database. Is there any way where I can call database from the code. Please note that there is no Message Flow. It's a plain ESQL library.
Thanks in advance
Prasad Edlabadkar |
|
| Back to top |
|
 |
| Vitor |
| Posted: Mon May 25, 2009 6:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
What method are you using to run ESQL without / outside a Compute node and/or without a message flow?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| prasad.edlabadkar |
| Posted: Mon May 25, 2009 8:59 pm Post subject: |
|
|
Novice
Joined: 10 Feb 2006
Posts: 21
Location: Pune, India
|
| Vitor wrote: |
| What method are you using to run ESQL without / outside a Compute node and/or without a message flow? |
It's been called from another ESQL library which is ultimately called from a message flow's compute node. But I don't want the compute node to know anything about the database. Please help!
Thanks
Prasad
_________________
Thanks and Regards
Prasad Edlabadkar
Tech Lead - Websphere Business Integration |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue May 26, 2009 12:20 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| prasad.edlabadkar wrote: |
| But I don't want the compute node to know anything about the database. |
Ok, so my follow up questions are these:
- Why are you designing this "backwards", as it were? Typically a Compute node would contain the environment-specific information (as it could be deployed to a number of different locations) and call a generic (and environment blind) routine. What's the requirement here?
- In the absense of a Compute node, how are you providing environmental context (i.e. database name, connection details, etc)?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| prasad.edlabadkar |
| Posted: Tue May 26, 2009 2:04 am Post subject: |
|
|
Novice
Joined: 10 Feb 2006
Posts: 21
Location: Pune, India
|
| Vitor wrote: |
| prasad.edlabadkar wrote: |
| But I don't want the compute node to know anything about the database. |
Ok, so my follow up questions are these:
- Why are you designing this "backwards", as it were? Typically a Compute node would contain the environment-specific information (as it could be deployed to a number of different locations) and call a generic (and environment blind) routine. What's the requirement here?
- In the absense of a Compute node, how are you providing environmental context (i.e. database name, connection details, etc)? |
Let me describe what I am trying to do. Then I'll elaborate on the problem.
I am trying to build a ESQL logging framework using log4j which get initialized via database (using custom configurator). The logging will done by every sub-flow within the project using ESQL procedure call. This procedure internally needs to call database to load the configuration details and invoke Java APIs to configure log4j.
Now the problem is, there is no subflow here. It's all ESQL based calls (also going to Java for log4j). To load the configuration from the database, I need to get handle to the database connection. I know the schema name, I know tables to use. I just need handle to the database connection so that I can fire some queries.
I don't want individual compute nodes using this library to know that I am interacting with database to load the configuration. Thus, I don't want to put a restriction on them by asking them to specify the DSN everytime they want to use logging.
I hope the problem would be clear now.
_________________
Thanks and Regards
Prasad Edlabadkar
Tech Lead - Websphere Business Integration |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue May 26, 2009 2:05 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| There are a variety of options that can be specified in the Database stanza of ESQL database statements. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue May 26, 2009 8:57 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hello.
You need to be aware that all the ESQL you write will always be run in the context of one of the ESQL nodes, E.g. Compute or Database. Also, although you can specify a DSN directly within a DB statement (for example a SELECT) that DSN must be compatible with the DSN specified in the "Datasource" property on the node. What this means is that any ESQL node that wants to access a DB must specify a DSN on the node in the Datasource property which is compatible with any dynamic DSN specified in individual statements. In practice this this means that in your case each ESQL node that calls one of the library functions you provide that talks to a DB will need a DSN specified. That DSN will be connected to when the node is deployed and "interogated" as to what ODBC statements it can support. However, as long as all dynamic DSNs you use are of the same type (for example all DB2 or all Oracle etc) then there is no requirement to actually use the node's DSN in your nodes logic.
Hope this helps.
_________________
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 |
|
|
| mqjeff |
| Posted: Tue May 26, 2009 9:13 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| mgk wrote: |
| this means that in your case each ESQL node that calls one of the library functions you provide that talks to a DB will need a DSN specified. |
This is an important point, so I'm repeating it.
You may also find that ESQL nodes that call routines in the same *schema* as your database library functions may end up needing a DSN set on them. |
|
| Back to top |
|
|
| prasad.edlabadkar |
| Posted: Wed May 27, 2009 2:11 am Post subject: |
|
|
Novice
Joined: 10 Feb 2006
Posts: 21
Location: Pune, India
|
| mqjeff wrote: |
| mgk wrote: |
| this means that in your case each ESQL node that calls one of the library functions you provide that talks to a DB will need a DSN specified. |
This is an important point, so I'm repeating it.
You may also find that ESQL nodes that call routines in the same *schema* as your database library functions may end up needing a DSN set on them. |
Thanks a lot guys for your help! I think I have no other option but to ask flows to set DSN in the compute node.
_________________
Thanks and Regards
Prasad Edlabadkar
Tech Lead - Websphere Business Integration |
|
| Back to top |
|
|
| jonasb |
| Posted: Thu Jun 04, 2009 4:20 am Post subject: |
|
|
Apprentice
Joined: 20 Dec 2006
Posts: 49
Location: Sweden
|
Hi,
One possible solution would be to call an external java procedure to get the data you want from the configuration database?
CREATE FUNCTION getLog4jParameter(IN paramname CHARACTER)
RETURNS CHARACTER
LANGUAGE JAVA
EXTERNAL NAME "com.yourcompany.messagebroker.Log4jparameterfetcher.getParameter";
<The java code then calls jdbc to get the data>
Your framework will no longer be pure ESQL (You will need to ship a jar-file as well). But you would not have to fill in the DSN in all esql nodes using the framework anymore.
/contact admin
_________________
contact admin |
|
| Back to top |
|
|
|
|
