Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Topic-icon Question SQL_CONNECTION for SqlOperation

More
01 Dec 2014 05:01 #1 by mdahlman
SQL_CONNECTION for SqlOperation was created by mdahlman
I'm trying to test out the SqlOperation tool. I'm not understanding something very basic. How can I define the connection I want?

Obviously the answer is, "Use the parameter 'SQL_CONNECTION'". But I don't see how to use it. It's just a text field. Why can't I select one of my Metadata Data Servers? What am I supposed to put here?

It's surely simple... but I just don't see what I need to use. Thanks for any guidance.

Currently I get errors like this:
Engine Exception during connection establishement : Unknown connection manager : SQL_CONNECTION
or
Engine Exception during connection establishement : Unknown connection manager : <Name-of-my-dataserver>
(Minor detail: "establishement" should be "establishment".)
More
01 Dec 2014 05:05 #2 by mdahlman
Replied by mdahlman on topic SQL_CONNECTION for SqlOperation
Also note: I attached a screenshot for clarity... but it was rejected. "The file size (180916) exceeds the maximum set by the administrator." You guys should raise this limit. It was a pretty small partial screenshot.
More
01 Dec 2014 07:38 - 01 Dec 2014 08:46 #3 by Nicolas Verscheure
Replied by Nicolas Verscheure on topic SQL_CONNECTION for SqlOperation
Sure, it's very simple ;)

Just drag'n'drop a metadata from the Projet Explorer on your SQLOperation ! Automatically the SQLOperation will use the connection defined in the metadata linked. Thus you'll see the SQL_CONNECTION parameter colorized in pink.







A good practice is to put all the datastores involved in the query of your SQLOperation as a metadata link (remember just drag'n'drop) and use this syntax to get the datastore :

%x{$YOUR_METADATA/tech:physicalPath()}x%
Applied to screenshoot's example :

%x{$T_ADDRESS/tech:physicalPath()}x%

Result :

     [echo] Get Result
     [echo] Result : HOTEL_MANAGEMENT.T_ADDRESS
Attachments:
Last edit: 01 Dec 2014 08:46 by Nicolas Verscheure.
More
01 Dec 2014 08:43 #4 by Nicolas Verscheure
Replied by Nicolas Verscheure on topic SQL_CONNECTION for SqlOperation

mdahlman wrote: Also note: I attached a screenshot for clarity... but it was rejected. "The file size (180916) exceeds the maximum set by the administrator." You guys should raise this limit. It was a pretty small partial screenshot.


Can you please drop a message in the Site Questions and Suggestions part of this forum ?
More
01 Dec 2014 19:27 #5 by mdahlman
Replied by mdahlman on topic SQL_CONNECTION for SqlOperation
Solved!
Thanks for the tips, that made the idea clearer. I find this aspect of the GUI unintuitive:
  • When I first create the component SQLOperation the value for SQL_CONNECTION is not set. This is expected.
  • When I drag in a Data Schema the SQL_CONNECTION remains unset. But it turns pink. This indicates that it's secretly set.
The end result is exactly what I want. And it's actually quite simple. It's just unintuitive for me to have this parameter indicate that it is not set as shown by the empty square brackets but also indicate that it is set using color.

It turns out that I had done all of this correctly in my earlier attempts. The problem was different. The problem was that the local runtime could not find the Oracle JDBC jar file. I had no problems connecting from the designer, so I had not thought about classpath problems in the runtime. But once I discovered the variable CORE_STACK_TRACE it was easy to identify and correct the problem.

Thanks for the assistance.
More
01 Dec 2014 20:48 - 16 Feb 2015 10:19 #6 by Nicolas Verscheure
Replied by Nicolas Verscheure on topic SQL_CONNECTION for SqlOperation
For your driver's problem, maybe this article could have been useful.

Indeed, you have to set up the driver in the Designer's Preferences - Window | Preferences under Windows OS or Stambia Designer | Preferences under MacOSX.




Just click on "Edit" to set up the driver :



And "Add JARs..." to specify the path of your JAR's driver. All the driver's JAR must be put in the directory stambiaRuntime/lib/jdbc.
Attachments:
Last edit: 16 Feb 2015 10:19 by Thomas BLETON. Reason: (edit: changed link url to reflect new article id)