Updating WD_WebPackageKeyword

[NicOla]

We have been hard at work collection descriptions and key search words for our 1500 App Portal Catalog items.

I’m trying to get the SQL updates together so I can run against the DB.

Update Description is easy:

                Update WD_WebPackages Set PackageDesc = 'This is an app description' WHERE PackageID_FK = 1234

My question is how to run updates for the key words. Do we only need to update the WD_WebPackageKeyword table?

Is the update something like this

Update WD_WebPackageKeyword Set Keyword = ‘keyword1 keyword2’ WHERE PackageID_FK = 1234

What if we have more than one key word per catalog item?

What if that catalog item already has a key work in it (and we want to add more)?

I can run the queries past our DBA team also, but thought I should ask here in case there are other tables we need to consider when updating WD_WebPackageKeyword.

Thanks.

[CharlesW]

I can't claim to be a SQL expert, but I don't know of a good way to handle this as a single query. I think that you will need an "insert into statement" for each keyword that you wish to add, and if applicable, for each language. If you know what you are doing, I'm sure that you could create a stored procedure which would break down a comma-delimited list of keywords..

Whatever you choose to do, it might be better to start with a clean slate, if you were to attempt to modify the keywords.. By this, I mean delete existing keywords for the catalog item, and then insert them again.. To delete, you would use something similar to the following:

DELETE FROM WD_WebPackageKeyword WHERE PackageID_FK = <packageID>

You would then need to call an insert into statement which would be similar to the following:
 

sql = "INSERT INTO WD_WebPackageKeyword (PackageID_FK, KeyWord, LanguageID) VALUES (<packageID>, '<keyword>', <languageID>)";

I'm not sure what is "driving" your automation, but if you are using PowerShell, or a program,  it would be a simple enough matter to call the above query in a loop, based on a comma-delimited list of keywords.. 

Again, not sure how you are automating this, or if you are making any web service calls, but in http://localhost/esd/api.asmx, there is a Function named updateKeyWordsOfCatalogItem, which should make things easier...

[jdempsey · Accepted solution]

I would discourage direct database updates when possible.  In this situation, there are SOAP APIs available for updating descriptions and keywords.  The methods you would be interested in are:

• updateCatalogItemBriefDescription
• updateCatalogItemDescription
• updateKeyWordsOfCatalogItem

[NicOla]

Thanks Jim, I will try to use those API's by calling them from a powershell script.

I see the identifier for the APIs are the Catalog Item Title. If I have archived Catalog items with the same Title - will that cause any issues? Will the active Catalog items update correctly?

Cheers.

[jdempsey]

If I'm not mistaken, those API's will only work on non-archived catalog items, and you should not be able to have two non-archived catalog items with the same name (unless you've manipulated things in the database, which might cause problems with the application.