SQL REPLACE

General Discussion about LDAP Administrator

Moderator: Support

SQL REPLACE

Postby leetch77 » Mon Sep 10, 2012 9:32 pm

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?
leetch77
 
Posts: 6
Joined: Mon Sep 10, 2012 8:47 pm

Re: SQL REPLACE

Postby Support » Tue Sep 11, 2012 11:27 am

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;
Support
 
Posts: 887
Joined: Sun Aug 12, 2001 12:00 am

Re: SQL REPLACE

Postby leetch77 » Tue Sep 11, 2012 7:48 pm

leetch77 wrote: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
 
Posts: 6
Joined: Mon Sep 10, 2012 8:47 pm

Re: SQL REPLACE

Postby leetch77 » Tue Oct 16, 2012 5:40 pm

Support wrote: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.
leetch77
 
Posts: 6
Joined: Mon Sep 10, 2012 8:47 pm

Re: SQL REPLACE

Postby Support » Wed Oct 17, 2012 11:57 am

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;
Support
 
Posts: 887
Joined: Sun Aug 12, 2001 12:00 am

Re: SQL REPLACE

Postby leetch77 » Wed Oct 17, 2012 4:41 pm

Support wrote: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;
leetch77
 
Posts: 6
Joined: Mon Sep 10, 2012 8:47 pm


Return to General Discussion

Who is online

Users browsing this forum: No registered users and 1 guest

cron