How to use SQL in script

[former_member431321]

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

[lbreddemann]

Amazing how nobody points out the problematic pattern to determine the next higher user number (USER_NO) without locking the table. If this function is executed in parallel it's trivial to generate multiple users with identical USER_NO values.

Commonly one would look at using sequences to generate IDs (as they most often allow for gaps without problems).

[Steffi_Warnecke]

Well Lars, that's where you come in. 😉

[former_member431321]

Dear Lars,

Thank for your advice.

It is true that there could be a issue of multiple same USER_NO value when use maxno.

In real environment we will use 'sequence'.

dongsu

[Steffi_Warnecke]

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.

[alexanderbrietz]

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

[former_member2987]

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

[former_member431321]

Hi Matt,

If Iogin as rt user and run the sql, it does not work.

...

It looks like the SQL inside the script can not use Database definition in 'to Database <Destination>' tab.

To use this definition in script, is there any special steps should I follow?

regards,

dongsu

[former_member2987]

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%'