Skip to Content

How to use SQL in script

Hi experts,

I am making a to database pass and for a column, I need to run a SQL to get a 'MAX +1' value.

So I made a script and used it with FUNCTION as below.

And I got error.

Could any one give me an advice?

regards,

dongsu

pass.png (15.3 kB)
script.png (9.6 kB)
error.png (37.3 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Jun 25, 2019 at 07:23 AM

    Hi Dongsu,

    I assume that your table has been created by another user context than mxmc_rt. Please note that mxmc_ stands for the default db-prefix and needs to be replaced by your chosen prefix.

    The script is executed by dispatcher in context of mxmc_rt. Therefore you should test the query in this context and get it working e.g. using db-schema prefixes and/or grant select for mxmc_rt on the table needed.

    Within a toDB-pass you can establish a db-connection in another db-context than mxmc_rt using the connection wizard (three dots). There it would be possible to connect to db using the context you have use to create the table. From your screenshot above I guess that you tried it using db-context ylims. Is that the context where you created the table?

    Establishing a db-connection within JavaScript is possible but needs some Java-Classes to be imported in order to use them. But I think this is not necessary in your case and you should get the contexts right to access your table.

    Best regards,

    Alex

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 26, 2019 at 12:18 PM

    Hello Dongsu,

    if you need a db-connection with another user (since the default rt-user has no permission to use that table), you need to add the other user and its password to your string.

    I used a local job constant to define the connection and used the constant in the pass, since it tended to get wiped out when I changed something in the settings and I was tired of re-creating it time after time.

    So I use the local constant in the pass itself:

    And in the constant the connection string (for MSSQL) follows this syntax:

    jdbc:sqlserver://<ip of db-server>:<port>;databasename=<databasename>;user=<databasename-user with the correct permission like oper>;password=<password of that databasename-user with the correct permission like oper>


    Oh and I think the connection string in the toDatabase pass is not used in the script. It's just for the pass itself. The script would probably use the rt-user and its permissions and that one (as you pointed out) can't run the SQL statement on the database.

    Regards,

    Steffi.


    ms3fi.png (2.8 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 25, 2019 at 01:15 AM

    Hi Dongsu,

    Does this work in Oracle? Also please make sure that the rt user has access to the YLIMS.USER_INFO table (view?)

    Matt

    Add a comment
    10|10000 characters needed characters exceeded

    • So you're saying when you hard code the connection string it works, but not from a constant? If this is the case, what type of constant is it? I'm assuming a repository one which would be preferred.

      Scripts should be able to use any system parameter, but I beleieve they have to be in single quotes '%ddm.something%'

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.