Powerful Directory Management Tool

[leetch77]

I'm trying to do a substring replacement using the LDAP-SQL component:

UPDATE "ou=0,ou=accounts,erglobalid=00000000000000000000,ou=CFC,ou=tim,ou=idB2B,ou=applications,o=cinfin"
SET $owner=REPLACE($owner,'ou=idb2b','ou=idB2B'))
WHERE $owner = '%ou=idb2b'%'

But the SQL editor doesn't find a REPLACE.

Any suggestions as to how I can do this?

[Support]

Hi. Yes, there is no REPLACE function in LDAP-SQL. Please read our help topic http://www.ldapadministrator.com/resour ... 03s01.html about all available LDAP-SQL statements and functions .

Also, as for your request, if I correctly understand your task, you should modify your query to this:

UPDATE "ou=0,ou=accounts,erglobalid=00000000000000000000,ou=CFC,ou=tim,ou=idB2B,ou=applications,o=cinfin"
SET $owner=SUBSTRING($owner FROM 1 FOR POSITION('ou=idb2b' IN $owner)-1) || 'ou=idB2B' ||
SUBSTRING($owner FROM POSITION('ou=idb2b' IN $owner) + CHARACTER_LENGTH('ou=idb2b'))
WHERE EXISTS $owner AND POSITION('ou=idb2b' IN $owner) > 0;

[leetch77]

I'm trying to do a substring replacement using the LDAP-SQL component:

UPDATE "ou=0,ou=accounts,erglobalid=00000000000000000000,ou=CFC,ou=tim,ou=idB2B,ou=applications,o=cinfin"
SET $owner=REPLACE($owner,'ou=idb2b','ou=idB2B'))
WHERE $owner = '%ou=idb2b'%'

But the SQL editor doesn't find a REPLACE.

Any suggestions as to how I can do this?

Thank you very much. That will work just fine.

[leetch77]

Hi. Yes, there is no REPLACE function in LDAP-SQL. Please read our help topic http://www.ldapadministrator.com/resour ... 03s01.html about all available LDAP-SQL statements and functions .

Also, as for your request, if I correctly understand your task, you should modify your query to this:

UPDATE "ou=0,ou=accounts,erglobalid=00000000000000000000,ou=CFC,ou=tim,ou=idB2B,ou=applications,o=cinfin"
SET $owner=SUBSTRING($owner FROM 1 FOR POSITION('ou=idb2b' IN $owner)-1) || 'ou=idB2B' ||
SUBSTRING($owner FROM POSITION('ou=idb2b' IN $owner) + CHARACTER_LENGTH('ou=idb2b'))
WHERE EXISTS $owner AND POSITION('ou=idb2b' IN $owner) > 0;

Revisiting this.

For the conditions where it is not a match on the 'ou=idb2b', Softerra throws an error:

eruid=ut0108,ou=systemUser,ou=itim,ou=CFC,ou=tim,ou=idB2B,ou=applications,o=cinfin: the length is negative.

When I double-click to locate in source, it points to the POSITION('ou=idb2b' IN $owner)-1) part of the SQL statement. I wouldn't mind this, but what happens is that my change will accrue thousands of these errors and after a while, Softerra seems to lose its mind and freezes up and doesn't perform any changes. I have to kill it and start over.

So my thinking is if I can stop these "length is negative" errors, I'll be better off. Is there any way to do that?

Thanks for your help.

[Support]

Hello,

We confirm this is a bug and it will be fixed in the next release.
For now, please use the query below to workaround the error:

UPDATE "ou=0,ou=accounts,erglobalid=00000000000000000000,ou=CFC,ou=tim,ou=idB2B,ou=applications,o=cinfin"
SET $owner=SUBSTRING($owner FROM 1 FOR ABS(POSITION('ou=idb2b' IN $owner)-1)) || 'ou=idB2B' ||
SUBSTRING($owner FROM POSITION('ou=idb2b' IN $owner) + CHARACTER_LENGTH('ou=idb2b'))
WHERE EXISTS $owner AND POSITION('ou=idb2b' IN $owner) > 0;

[leetch77]

Hello,

We confirm this is a bug and it will be fixed in the next release.
For now, please use the query below to workaround the error:

UPDATE "ou=0,ou=accounts,erglobalid=00000000000000000000,ou=CFC,ou=tim,ou=idB2B,ou=applications,o=cinfin"
SET $owner=SUBSTRING($owner FROM 1 FOR ABS(POSITION('ou=idb2b' IN $owner)-1)) || 'ou=idB2B' ||
SUBSTRING($owner FROM POSITION('ou=idb2b' IN $owner) + CHARACTER_LENGTH('ou=idb2b'))
WHERE EXISTS $owner AND POSITION('ou=idb2b' IN $owner) > 0;

Ah, that's a better work-around than what I came up with. Thanks for the help.

FYI, I tweaked the search string from 'ou=idB2B' which may not always be there to 'ou=id' which does and got rid of the negative length errors. Worked great then.

UPDATE "ou=systemUser,ou=itim,ou=CFC,ou=tim,ou=idb2b,ou=applications,o=cinfin"
SET $owner=SUBSTRING($owner FROM 1 FOR POSITION('ou=id' IN $owner)-1) || 'ou=idB2B' ||
SUBSTRING($owner FROM POSITION('ou=idb2b' IN $owner) + CHARACTER_LENGTH('ou=idb2b'))
WHERE EXISTS $owner AND POSITION('ou=idb2b' IN $owner) > 0;