| Author |
Message
|
| rshephard59 |
 Posted: Fri Sep 22, 2006 5:24 am Post subject: Removing hard-coded database name in compute node ESQL |
 |
|
Novice
Joined: 09 Jan 2003
Posts: 10
|
I have a message flow with a compute node that connects to an external database. I have the database name defined in the Data Source field of Compute node properties dialog, but the ESQL statement also seems to require the database name. Example:
"SET Result = THE(SELECT ITEM T.Column FROM Database.DB_NAME.TableName..."
The BAR file allows the properties of various nodes to be changed before deployment. I want to be able to simply change the DB name here when I'm migrating the message from from my DEV environment to QA or PROD, but because the name is also hard-coded into the ESQL, I have to crack open the code and change it each time I migrate to a new environment.
For MQInput and MQOutput nodes I can change the queue names in the BAR file and it works just fine. Does anyone know how I can do this with the DB name in the compute node?
Thanks,
ras |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Fri Sep 22, 2006 5:28 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
That shouldn't be the DATABASE name. That should be the SCHEMA name.
And, yes, in v6 you can use dynamic schema names.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| chargie |
| Posted: Fri Sep 22, 2006 5:44 am Post subject: |
|
|
Novice
Joined: 21 Sep 2006
Posts: 10
|
| how to define and use dynamic schema? |
|
| Back to top |
|
|
| rshephard59 |
| Posted: Fri Sep 22, 2006 5:56 am Post subject: |
|
|
Novice
Joined: 09 Jan 2003
Posts: 10
|
Oops. Sorry for the confusion. It is the schema name. Is there a reserved word that I use in place of the schema name to make it dynamic?
Thanks.
ras |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Sep 22, 2006 6:00 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
|
| Back to top |
|
|
| rshephard59 |
| Posted: Fri Sep 22, 2006 7:50 am Post subject: |
|
|
Novice
Joined: 09 Jan 2003
Posts: 10
|
Thanks. I was searching for Dynamic instead of UDP.
Ok, I created the UDP but my message flow isn't picking up the value for some reason and it's throwing a database exception.
I created an external variable in my ESQL above all of my modules:
DECLARE Schema1 EXTERNAL CHARACTER 'SAGADEV';
I also tried using:
DECLARE Schema1 EXTERNAL NAME 'SAGADEV';
Then I modified my SELECT statement to read:
"SET Result = THE(SELECT ITEM T.Column1 FROM Database.Schema1.Table1..."
Then I added a User-Defined Property named Schema1 to the Message Flow. It has a data type of String and I set the default value to SAGADEV. (I also tried using 'SAGADEV')
Next I re-loaded the message flow into my bar file. On the Configure tab I can see the UDP and it's value.
The bar file deploys successfully but When I run a message through I get a database exception error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Am I missing something?
Thanks again for the help oh grand poobah. 
ras |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Sep 22, 2006 7:53 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You didn't dig down into the documentation on the SELECT statement far enough.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
