Question

changing metadata and folder names in sql

Laserfiche Version 9

Updated March 4, 2016

asked on November 19, 2014

I have a user and they have a bunch of data that was imported over a few months. The data had ".." in the folder name as well as in one of the metadata fields in the template. Is there a way to make these changes inside the SQL DB rather than going in and fixing hundreds of files manually?

0 0

Answer

APPROVED ANSWER

replied on November 19, 2014

One option would be to use Workflow to look for these folders with bad names or entries with bad field data and fix the values there.

However, if the customer is comfortable with modifying the database directly, then you can run a query using the replace function on the name column in the toc table and the str_val and short_str_val columns in the propval table. It goes without saying, but please backup the database before making any changes.

0 0

replied on November 19, 2014

Alex-

Thanks for the reply. I will have them backup the database and give this a go. I hope this works out for them!

Thanks,

Chad

0 0

Replies

replied on November 19, 2014 • Show version history

Chad,

For reference here are two SQL UPDATE statements that accomplish your goal. As Alex shares, please make sure they have a good backup before running the SQL statements. (I actually like the idea of the workflow model!)

Note: In both statements we are replacing the '..' with an empty string (''), perhaps a better replacement of the '..' would be a space?

To update the folder names;

UPDATE dbo.toc SET dbo.toc.name = REPLACE(dbo.toc.name, '..','')
WHERE dbo.toc.etype = 0 AND dbo.toc.name LIKE '%..%'

To update the metadata fields;

UPDATE dbo.propval 
SET dbo.propval.str_val = REPLACE(dbo.propval.str_val,'..',''), 
dbo.propval.short_str_val = REPLACE(dbo.propval.short_str_val,'..','')
WHERE dbo.propval.str_val LIKE '%..%' OR dbo.propval.short_str_val LIKE '%..%'

0 0

replied on March 4, 2016

Have a similar issue here... I have some find/replace corrections which need to be done on just a specific field. How can this be accomplished on just a single field?

Thanks!!

0 0

replied on March 4, 2016

Just add a condition on the prop_id to the WHERE clause in Cliff's query above. You can get the prop_id value from the propdef table based on the field name.

0 0

replied on March 4, 2016

You're the best Miruna!

0 0

You are not allowed to follow up in this post.

Question

Question

changing metadata and folder names in sql

Answer

Replies

Sign in to reply to this post.